cancel
Showing results for 
Search instead for 
Did you mean: 

[ERROR] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071

[ERROR] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071

Magento 2.2.2.

MySQL 5 MyISAM

PHP 7.1 tried also PHP 7.0

 

Error during install. Stops at 11%

Does anyone know how to fix?

 

Module 'Magento_Ui':
Installing schema... 
[ERROR] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes in /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(93): Zend_Db_Statement_Pdo->_execute(Array)
#2 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#3 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE IF...', Array)
#5 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(517): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE IF...', Array)
#6 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(580): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('CREATE TABLE IF...', Array)
#7 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(2074): Magento\Framework\DB\Adapter\Pdo\Mysql->query('CREATE TABLE IF...')
#8 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/module-ui/Setup/InstallSchema.php(88): Magento\Framework\DB\Adapter\Pdo\Mysql->createTable(Object(Magento\Framework\DB\Ddl\Table))
#9 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Model/Installer.php(867): Magento\Ui\Setup\InstallSchema->install(Object(Magento\Setup\Module\Setup), Object(Magento\Setup\Model\ModuleContext))
#10 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Model/Installer.php(775): Magento\Setup\Model\Installer->handleDBSchemaData(Object(Magento\Setup\Module\Setup), 'schema')
#11 [internal function]: Magento\Setup\Model\Installer->installSchema()
#12 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Model/Installer.php(342): call_user_func_array(Array, Array)
#13 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Controller/Install.php(109): Magento\Setup\Model\Installer->install(Array)
#14 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/Controller/AbstractActionController.php(82): Magento\Setup\Controller\Install->startAction()
#15 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
#16 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#17 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#18 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/Controller/AbstractController.php(118): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
#19 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/DispatchListener.php(118): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
#20 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
#21 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#22 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#23 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/Application.php(340): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
#24 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/index.php(35): Zend\Mvc\Application->run()
#25 {main}

Next Zend_Db_Statement_Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes, query was: CREATE TABLE IF NOT EXISTS `ui_bookmark` (
`bookmark_id` int UNSIGNED NOT NULL auto_increment COMMENT 'Bookmark identifier' ,
`user_id` int UNSIGNED NOT NULL COMMENT 'User Id' ,
`namespace` varchar(255) NOT NULL COMMENT 'Bookmark namespace' ,
`identifier` varchar(255) NOT NULL COMMENT 'Bookmark Identifier' ,
`current` smallint NOT NULL COMMENT 'Mark current bookmark per user and identifier' ,
`title` varchar(255) NULL COMMENT 'Bookmark title' ,
`config` longtext NULL COMMENT 'Bookmark config' ,
`created_at` datetime NOT NULL COMMENT 'Bookmark created at' ,
`updated_at` datetime NOT NULL COMMENT 'Bookmark updated at' ,
PRIMARY KEY (`bookmark_id`),
INDEX `UI_BOOKMARK_USER_ID_NAMESPACE_IDENTIFIER` (`user_id`, `namespace`, `identifier`),
CONSTRAINT `UI_BOOKMARK_USER_ID_ADMIN_USER_USER_ID` FOREIGN KEY (`user_id`) REFERENCES `admin_user` (`user_id`) ON DELETE CASCADE
) COMMENT='Bookmark' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci in /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Statement/Pdo/Mysql.php(93): Zend_Db_Statement_Pdo->_execute(Array)
#1 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('CREATE TABLE IF...', Array)
#4 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(517): Zend_Db_Adapter_Pdo_Abstract->query('CREATE TABLE IF...', Array)
#5 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(580): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('CREATE TABLE IF...', Array)
#6 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(2074): Magento\Framework\DB\Adapter\Pdo\Mysql->query('CREATE TABLE IF...')
#7 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/magento/module-ui/Setup/InstallSchema.php(88): Magento\Framework\DB\Adapter\Pdo\Mysql->createTable(Object(Magento\Framework\DB\Ddl\Table))
#8 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Model/Installer.php(867): Magento\Ui\Setup\InstallSchema->install(Object(Magento\Setup\Module\Setup), Object(Magento\Setup\Model\ModuleContext))
#9 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Model/Installer.php(775): Magento\Setup\Model\Installer->handleDBSchemaData(Object(Magento\Setup\Module\Setup), 'schema')
#10 [internal function]: Magento\Setup\Model\Installer->installSchema()
#11 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Model/Installer.php(342): call_user_func_array(Array, Array)
#12 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/src/Magento/Setup/Controller/Install.php(109): Magento\Setup\Model\Installer->install(Array)
#13 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/Controller/AbstractActionController.php(82): Magento\Setup\Controller\Install->startAction()
#14 [internal function]: Zend\Mvc\Controller\AbstractActionController->onDispatch(Object(Zend\Mvc\MvcEvent))
#15 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#16 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#17 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/Controller/AbstractController.php(118): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
#18 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/DispatchListener.php(118): Zend\Mvc\Controller\AbstractController->dispatch(Object(Zend\Http\PhpEnvironment\Request), Object(Zend\Http\PhpEnvironment\Response))
#19 [internal function]: Zend\Mvc\DispatchListener->onDispatch(Object(Zend\Mvc\MvcEvent))
#20 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(490): call_user_func(Array, Object(Zend\Mvc\MvcEvent))
#21 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-eventmanager/src/EventManager.php(263): Zend\EventManager\EventManager->triggerListeners('dispatch', Object(Zend\Mvc\MvcEvent), Object(Closure))
#22 /mnt/data/accounts/g/glixmanak/data/www/magento/vendor/zendframework/zend-mvc/src/Application.php(340): Zend\EventManager\EventManager->triggerEventUntil(Object(Closure), Object(Zend\Mvc\MvcEvent))
#23 /mnt/data/accounts/g/glixmanak/data/www/magento/setup/index.php(35): Zend\Mvc\Application->run()
#24 {main}

2 REPLIES

Re: [ERROR] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071

As per my experience with this issue so far , it is file folder permission. if you did setup two user permission for your installation , i would suggest trying to go with shared group setting and setup indicated in magento 2 installation doc.

use magento setup:uninstall  and start new .. this will also remove the tables in db. you may have to manually remove some files from folders like generated folder ..

Re: [ERROR] PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071

Magento recommended mysql version is 5.6 and 5.7 http://devdocs.magento.com/guides/v2.2/install-gde/system-requirements-tech.html#database

 

Please check Magento2.2 system requirement http://devdocs.magento.com/guides/v2.2/install-gde/system-requirements-tech.html

Also check http://devdocs.magento.com/guides/v2.2/install-gde/prereq/mysql.html

What do you mean by MySQL 5 MyISAM , I think you should use InnoDB

---
Problem Solved Click Accept as Solution!:Magento Community India Forum