Can not create field called System gives DB error

[Kyle]

Hi,

I think 'system' should be added to the list of reserved words for fields (so a better error message can be displayed) as i get this error when trying to create a field called system (using 6.1.4 on MariaDB):

[2021-04-14 23:55:47] ALERT: Rebuild database fault: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(255) DEFAULT '' COLLATE `utf8mb4_unicode_ci`' at line 1 in /home/oncalltest/public_html/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:82 Stack trace: #0 /home/oncalltest/public_html/vendor/doctrine/dbal/src/Driver/PDO/Connection.php(82): PDO->query('ALTER TABLE sys...') #1 /home/oncalltest/public_html/vendor/doctrine/dbal/src/Connection.php(1035): Doctrine\DBAL\Driver\PDO\Connection->query('ALTER TABLE sys...') #2 /home/oncalltest/public_html/application/Espo/Core/Utils/Database/Schema/Schema.php(204): Doctrine\DBAL\Connection->executeQuery('ALTER TABLE sys...') #3 /home/oncalltest/public_html/application/Espo/Core/Utils/Database/Schema/SchemaProxy.php(27): Espo\Core\Utils\Database\Schema\Schema->rebuild() #4 /home/oncalltest/public_html/application/Espo/Core/DataManager.php(96): Espo\Core\Utils\Database\Schema\SchemaProxy->rebuild(Array) #5 /home/oncalltest/public_html/application/Espo/Core/DataManager.php(67): Espo\Core\DataManager->rebuildDatabase(Array) #6 /home/oncalltest/public_html/application/Espo/Modules/MythradonBase/Controllers/FieldManager.php(61): Espo\Core\DataManager->rebuild(Array) #7 /home/oncalltest/public_html/application/Espo/Core/ControllerManager.php(61): Espo\Modules\MythradonBase\Controllers\FieldManage r->postActionCreate(Object(Espo\Core\Api\RequestWrap per), Object(Espo\Core\Api\ResponseWrapper)) #8 /home/oncalltest/public_html/application/Espo/Core/Api/RouteProcessor.php(58): Espo\Core\ControllerManager->process('FieldManager', 'create', Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #9 /home/oncalltest/public_html/application/Espo/Core/ApplicationRunners/Api.php(158): Espo\Core\Api\RouteProcessor->process('/Admin/fieldMan...', Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper)) #10 /home/oncalltest/public_html/application/Espo/Core/ApplicationRunners/Api.php(86): Espo\Core\ApplicationRunners\Api->processRequest(Array, Object(Espo\Core\Api\RequestWrapper), Object(Espo\Core\Api\ResponseWrapper), Array) #11 /home/oncalltest/public_html/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(43): Espo\Core\ApplicationRunners\Api->Espo\Core\ApplicationRunners\{closure}(Object(Sli m\Psr7\Request), Object(Slim\Psr7\Response), Array) #12 /home/oncalltest/public_html/vendor/slim/slim/Slim/Routing/Route.php(381): Slim\Handlers\Strategies\RequestResponse->__invoke(Object(Closure), Object(Slim\Psr7\Request), Object(Slim\Psr7\Response), Array) #13 /home/oncalltest/public_html/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Slim\Routing\Route->handle(Object(Slim\Psr7\Request)) #14 /home/oncalltest/public_html/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) #15 /home/oncalltest/public_html/vendor/slim/slim/Slim/Routing/Route.php(341): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) #16 /home/oncalltest/public_html/vendor/slim/slim/Slim/Routing/RouteRunner.php(84): Slim\Routing\Route->run(Object(Slim\Psr7\Request)) #17 /home/oncalltest/public_html/vendor/slim/slim/Slim/Middleware/RoutingMiddleware.php(60): Slim\Routing\RouteRunner->handle(Object(Slim\Psr7\Request)) #18 /home/oncalltest/public_html/vendor/slim/slim/Slim/MiddlewareDispatcher.php(140): Slim\Middleware\RoutingMiddleware->process(Object(Slim\Psr7\Request), Object(Slim\Routing\RouteRunner)) #19 /home/oncalltest/public_html/vendor/slim/slim/Slim/Middleware/ErrorMiddleware.php(107): class@anonymous->handle(Object(Slim\Psr7\Request)) #20 /home/oncalltest/public_html/vendor/slim/slim/Slim/MiddlewareDispatcher.php(140): Slim\Middleware\ErrorMiddleware->process(Object(Slim\Psr7\Request), Object(class@anonymous)) #21 /home/oncalltest/public_html/vendor/slim/slim/Slim/MiddlewareDispatcher.php(81): class@anonymous->handle(Object(Slim\Psr7\Request)) #22 /home/oncalltest/public_html/vendor/slim/slim/Slim/App.php(215): Slim\MiddlewareDispatcher->handle(Object(Slim\Psr7\Request)) #23 /home/oncalltest/public_html/vendor/slim/slim/Slim/App.php(199): Slim\App->handle(Object(Slim\Psr7\Request)) #24 /home/oncalltest/public_html/application/Espo/Core/ApplicationRunners/Api.php(68): Slim\App->run() #25 /home/oncalltest/public_html/application/Espo/Core/Application.php(69): Espo\Core\ApplicationRunners\Api->run() #26 /home/oncalltest/public_html/api/v1/index.php(11): Espo\Core\Application->run('Espo\\Core\\Appli...') #27 {main} Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(255) DEFAULT '' COLLATE `utf8mb4_unicode_ci`' at line 1 in /home/oncalltest/public_html/vendor/doctrine/dbal/src/... [] []

[esforim]

Be good if you could also provide a reproducible step.

I tried reproducing it on the demo https://demo.espocrm.com/?lang=en_US

Which seem to work fine. A shortcut step I did.

Admin > Entity Manager > Field > Contacts > Create Field > Varchar > system > Save > rebuild

Not sure if it only related to MariaDB? But yeah, best to write step to reproduce it, else the developer might just ignore this bug.

[yuri]

I managed to reproduce on MariaDB. Could be an issue in Doctrine. I will take a look when get a chance (too busy at the moment). Any help would be appreciated.

[item]

Hello,
it's reserved word.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html. SYSTEM (R); added in 8.0.3 (reserved)

vendor/doctrine/dbal/src/Platforms/Keywords/DB2Keywords.php
vendor/doctrine/dbal/src/Platforms/Keywords/MySQL80Keywords.php
vendor/doctrine/dbal/src/Platforms/Keywords/SQLServer2012Keywords.php

[yuri]

Reserved words can be used as column names. Just need to be quoted. I managed to create a column with name 'system' (directly, not from espo).

[Kyle]

Thanks everyone for the quick responses.