1

I updated my MAMP site from EE 1.14.1.0 to EE 1.14.2.0. After updating, I also applied the new patch for PHP 7. The site also has all of the required SUPEES. When I try and do a reindexall command inside of terminal, I get the following error an all indexes except for Product Attributes and Tag Aggregation Data:

Catalog Search Index index process unknown error:
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 3057 Incorrect user-level lock name 'dbname.index_process_ee93898b-dd2e-11e8-89b2-d28213db0fb8'.' in lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT IS_USED_...', Array)
#5 lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT IS_USED_...', Array)
#6 app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(83): Varien_Db_Adapter_Pdo_Mysql->query('SELECT IS_USED_...', Array)
#7 app/code/core/Mage/Index/Model/Lock/Storage/Db.php(96): Mage_Index_Model_Resource_Helper_Mysql4->isLocked('dbname...')
#8 app/code/core/Mage/Index/Model/Lock.php(257): Mage_Index_Model_Lock_Storage_Db->isLockExists('index_process_e...')
#9 app/code/core/Mage/Index/Model/Lock.php(227): Mage_Index_Model_Lock->_isLockExistsDb('index_process_e...')
#10 app/code/core/Mage/Index/Model/Process.php(480): Mage_Index_Model_Lock->isLockExists('index_process_e...', NULL)
#11 app/code/core/Mage/Index/Model/Process.php(184): Mage_Index_Model_Process->isLocked()
#12 app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#13 shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#14 shell/indexer.php(216): Mage_Shell_Compiler->run()
#15 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 3057 Incorrect user-level lock name 'dbname.index_process_ee93898b-dd2e-11e8-89b2-d28213db0fb8'., query was: SELECT IS_USED_LOCK(?);' in lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT IS_USED_...', Array)
#4 lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT IS_USED_...', Array)
#5 app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(83): Varien_Db_Adapter_Pdo_Mysql->query('SELECT IS_USED_...', Array)
#6 app/code/core/Mage/Index/Model/Lock/Storage/Db.php(96): Mage_Index_Model_Resource_Helper_Mysql4->isLocked('dbname...')
#7 app/code/core/Mage/Index/Model/Lock.php(257): Mage_Index_Model_Lock_Storage_Db->isLockExists('index_process_e...')
#8 app/code/core/Mage/Index/Model/Lock.php(227): Mage_Index_Model_Lock->_isLockExistsDb('index_process_e...')
#9 app/code/core/Mage/Index/Model/Process.php(480): Mage_Index_Model_Lock->isLockExists('index_process_e...', NULL)
#10 app/code/core/Mage/Index/Model/Process.php(184): Mage_Index_Model_Process->isLocked()
#11 app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#12 shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#13 shell/indexer.php(216): Mage_Shell_Compiler->run()
#14 {main}

In MAMP, I have selected the version of PHP to be 7.2.8.

I have tried to delete the files inside of var/locks.

I have tried applying SUPEE-5984, which was suggested a couple of posts here.

I also tried to truncate the

  1. catalog_product_flat_1 and
  2. catalog_product_flat_cl tables, which was also suggested.

Use Flat Catalog Category is set to 'No' in my configuration.

Improve this question
3
  • What database are you using? I found this issue for the Go SQL driver and this one for Percona.
    – Marisa
    Commented Oct 31, 2018 at 18:24
  • @Mike - I am using Sequel Pro. I will see if there is anything out there about that database.
    – cjochum
    Commented Oct 31, 2018 at 19:14
  • I tried to start the process all over again, by duplicating my EE 1.14.1.0 site. I tested reindexing at the start and it worked. However, when I tried to duplicate it a second time (from the initial database), I got the same error as above. Could this be an issue in Sequel Pro and duplicating a database?
    – cjochum
    Commented Nov 2, 2018 at 12:12

1 Answer 1

Reset to default
0

It looks like the issue was because I had deleted a database in Sequel Pro and then created a database with the same name.

When I created a new database with a name that hadn't been previously used, I no longer had this issue.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.