How to fix - The total number of locks exceeds the lock table size

[CunningStunt2]

I have this in my logs but not sure what the cause is or how to address. Can anyone point me in the right direction please?

I think its an issue with mySQL rather than Zen Cart. A few weeks ago my sever was moved and now has php 7.1 which caused loads of problems.
I've upgraded to the latest zen cart and done some edits to my template (sheffield_responsive) to fix things like arrays as strings instead or variables, but this error seems common in my log files.

[05-Nov-2019 11:47:46 UTC] Request URI: /index.php?main_page=index&cPath=6_24, IP address: 86.163.192.111
#1  trigger_error() called at [/home/thegluep/public_html/includes/classes/db/mysql/query_factory.php:171]
#2  queryFactory->show_error() called at [/home/thegluep/public_html/includes/classes/db/mysql/query_factory.php:143]
#3  queryFactory->set_error() called at [/home/thegluep/public_html/includes/classes/db/mysql/query_factory.php:270]
#4  queryFactory->Execute() called at [/home/thegluep/public_html/includes/functions/sessions.php:77]
#5  _sess_write()
#6  session_write_close() called at [/home/thegluep/public_html/includes/application_bottom.php:16]
#7  require(/home/thegluep/public_html/includes/application_bottom.php) called at [/home/thegluep/public_html/index.php:105]
--> PHP Fatal error: 1206:The total number of locks exceeds the lock table size :: insert into zen_sessions (sesskey, expiry, `value`)
            values ('691546460509a717b2ac8444dd359660', 1572955906, 'c2VjdXJpdHlUb2tlbnxzOjMyOiJiOTcwZTM4MDUxM2E2ZDA2YWJhNzczZjA2N2VjOGY5ZCI7Y3VzdG9tZXJzX2hvc3RfYWRkcmVzc3xzOjQ4OiJob3N0ODYtMTYzLTE5Mi0xMTEucmFuZ2U4Ni0xNjMuYnRjZW50cmFscGx1cy5jb20iO2NhcnRJRHxzOjA6IiI7Y2FydHxPOjEyOiJzaG9wcGluZ0NhcnQiOjEzOntzOjg6ImNvbnRlbnRzIjthOjA6e31zOjU6InRvdGFsIjtpOjA7czo2OiJ3ZWlnaHQiO2k6MDtzOjY6ImNhcnRJRCI7TjtzOjEyOiJjb250ZW50X3R5cGUiO2I6MDtzOjE4OiJmcmVlX3NoaXBwaW5nX2l0ZW0iO2k6MDtzOjIwOiJmcmVlX3NoaXBwaW5nX3dlaWdodCI7aTowO3M6MTk6ImZyZWVfc2hpcHBpbmdfcHJpY2UiO2k6MDtzOjE0OiJkb3dubG9hZF9jb3VudCI7aTowO3M6MjI6InRvdGFsX2JlZm9yZV9kaXNjb3VudHMiO2k6MDtzOjIyOiJkaXNwbGF5X2RlYnVnX21lc3NhZ2VzIjtiOjA7czoyMzoiZmxhZ19kdXBsaWNhdGVfbXNnc19zZXQiO2I6MDtzOjMyOiJmbGFnX2R1cGxpY2F0ZV9xdWFudGl0eV9tc2dzX3NldCI7YTowOnt9fWNoZWNrX3ZhbGlkfHM6NDoidHJ1ZSI7bmF2aWdhdGlvbnxPOjE3OiJuYXZpZ2F0aW9uSGlzdG9yeSI6Mjp7czo0OiJwYXRoIjthOjE6e2k6MDthOjQ6e3M6NDoicGFnZSI7czo1OiJpbmRleCI7czo0OiJtb2RlIjtzOjM6IlNTTCI7czozOiJnZXQiO2E6MTp7czo1OiJjUGF0aCI7czo0OiI2XzI0Ijt9czo0OiJwb3N0IjthOjA6e319fXM6ODoic25hcHNob3QiO2E6NDp7czo0OiJwYWdlIjtzOjEyOiJwcm9kdWN0X2luZm8iO3M6NDoibW9kZSI7czozOiJTU0wiO3M6MzoiZ2V0IjthOjI6e3M6NToiY1BhdGgiO3M6MzoiNl85IjtzOjExOiJwcm9kdWN0c19pZCI7czoyOiIyMyI7fXM6NDoicG9zdCI7YTowOnt9fX1sYW5ndWFnZXxzOjc6ImVuZ2xpc2giO2xhbmd1YWdlc19pZHxzOjE6IjEiO2xhbmd1YWdlc19jb2RlfHM6MjoiZW4iO2xheW91dFR5cGV8czo2OiJsZWdhY3kiO2N1cnJlbmN5fHM6MzoiR0JQIjt0b2RheV9pc3xzOjEwOiIyMDE5LTExLTA1Ijt1cGRhdGVFeHBpcmF0aW9uc3xiOjE7c2Vzc2lvbl9jb3VudGVyfGI6MTtjdXN0b21lcnNfaXBfYWRkcmVzc3xzOjE0OiI4Ni4xNjMuMTkyLjExMSI7')
            ON DUPLICATE KEY UPDATE `value`='c2VjdXJpdHlUb2tlbnxzOjMyOiJiOTcwZTM4MDUxM2E2ZDA2YWJhNzczZjA2N2VjOGY5ZCI7Y3VzdG9tZXJzX2hvc3RfYWRkcmVzc3xzOjQ4OiJob3N0ODYtMTYzLTE5Mi0xMTEucmFuZ2U4Ni0xNjMuYnRjZW50cmFscGx1cy5jb20iO2NhcnRJRHxzOjA6IiI7Y2FydHxPOjEyOiJzaG9wcGluZ0NhcnQiOjEzOntzOjg6ImNvbnRlbnRzIjthOjA6e31zOjU6InRvdGFsIjtpOjA7czo2OiJ3ZWlnaHQiO2k6MDtzOjY6ImNhcnRJRCI7TjtzOjEyOiJjb250ZW50X3R5cGUiO2I6MDtzOjE4OiJmcmVlX3NoaXBwaW5nX2l0ZW0iO2k6MDtzOjIwOiJmcmVlX3NoaXBwaW5nX3dlaWdodCI7aTowO3M6MTk6ImZyZWVfc2hpcHBpbmdfcHJpY2UiO2k6MDtzOjE0OiJkb3dubG9hZF9jb3VudCI7aTowO3M6MjI6InRvdGFsX2JlZm9yZV9kaXNjb3VudHMiO2k6MDtzOjIyOiJkaXNwbGF5X2RlYnVnX21lc3NhZ2VzIjtiOjA7czoyMzoiZmxhZ19kdXBsaWNhdGVfbXNnc19zZXQiO2I6MDtzOjMyOiJmbGFnX2R1cGxpY2F0ZV9xdWFudGl0eV9tc2dzX3NldCI7YTowOnt9fWNoZWNrX3ZhbGlkfHM6NDoidHJ1ZSI7bmF2aWdhdGlvbnxPOjE3OiJuYXZpZ2F0aW9uSGlzdG9yeSI6Mjp7czo0OiJwYXRoIjthOjE6e2k6MDthOjQ6e3M6NDoicGFnZSI7czo1OiJpbmRleCI7czo0OiJtb2RlIjtzOjM6IlNTTCI7czozOiJnZXQiO2E6MTp7czo1OiJjUGF0aCI7czo0OiI2XzI0Ijt9czo0OiJwb3N0IjthOjA6e319fXM6ODoic25hcHNob3QiO2E6NDp7czo0OiJwYWdlIjtzOjEyOiJwcm9kdWN0X2luZm8iO3M6NDoibW9kZSI7czozOiJTU0wiO3M6MzoiZ2V0IjthOjI6e3M6NToiY1BhdGgiO3M6MzoiNl85IjtzOjExOiJwcm9kdWN0c19pZCI7czoyOiIyMyI7fXM6NDoicG9zdCI7YTowOnt9fX1sYW5ndWFnZXxzOjc6ImVuZ2xpc2giO2xhbmd1YWdlc19pZHxzOjE6IjEiO2xhbmd1YWdlc19jb2RlfHM6MjoiZW4iO2xheW91dFR5cGV8czo2OiJsZWdhY3kiO2N1cnJlbmN5fHM6MzoiR0JQIjt0b2RheV9pc3xzOjEwOiIyMDE5LTExLTA1Ijt1cGRhdGVFeHBpcmF0aW9uc3xiOjE7c2Vzc2lvbl9jb3VudGVyfGI6MTtjdXN0b21lcnNfaXBfYWRkcmVzc3xzOjE0OiI4Ni4xNjMuMTkyLjExMSI7', expiry=1572955906 ==> (as called by) /home/thegluep/public_html/includes/functions/sessions.php on line 77 <== in /home/thegluep/public_html/includes/classes/db/mysql/query_factory.php on line 171.

[05-Nov-2019 11:47:46 UTC] Request URI: /index.php?main_page=index&cPath=6_24, IP address: 86.163.192.111
#1  session_write_close()
--> PHP Warning: session_write_close(): Cannot call session save handler in a recursive manner in Unknown on line 0.

[05-Nov-2019 11:47:46 UTC] Request URI: /index.php?main_page=index&cPath=6_24, IP address: 86.163.192.111
#1  session_write_close()
--> PHP Warning: session_write_close(): Failed to write session data using user defined save handler. (session.save_path: /home/thegluep/public_html/cache) in Unknown on line 0.

Thanks

[mc12345678]

Out of curiosity, when in the admin and click on the version link (also available under tools->Server Information) what are the contents of the session area as there may be some setting in the php.ini that may need to be modified/overridden.

[CunningStunt2]

Here's the session section from my php settings

[mc12345678]

Unfortunately nothing jumps out from that for me as needing modification.

A little more searching on the topic has identified some things. Because the session table engine is InnoDB as of Zen Cart 1.5.6, there is a setting for the database server that likely needs to be increased from its default value.

Now I don't know the full reasoning of why the engine type for the sessions was modified, but before it was MyISAM and I don't recall seeing such a similar issue reported. Certainly the default settings for mysql server have changed to go towards InnoDB as the "new" default, but Zen Cart has specifically identified that as the table's default engine.

To maintain that as the default engine for the sessions table and to reduce the likelihood of those messages, the first suggestion would be to ask your host or similar to increase or set the value of innodb_buffer_pool_size to a value greater than 8MB. I have seen suggestions to make it say 64MB for example, even saw a suggestion to make it 1GB.

Information can be found by searching the Internet for the main body of the error message.

[CunningStunt2]

I'm not sure if it's the cause but I noticed a mismatch in my configuration, my database was set to latin, since changing it to utf8 I have not been able to get this debug message to reappear, still testing.

[CunningStunt2]

Scratch that, still getting the error. My hosting company said they could not increase the mySQL setting as i'm on a shared server

[13-Nov-2019 14:47:21 UTC] Request URI: /index.php?main_page=index&cPath=101, IP address: 195.162.105.81
#1  trigger_error() called at [/home/thegluep/public_html/includes/classes/db/mysql/query_factory.php:171]
#2  queryFactory->show_error() called at [/home/thegluep/public_html/includes/classes/db/mysql/query_factory.php:143]
#3  queryFactory->set_error() called at [/home/thegluep/public_html/includes/classes/db/mysql/query_factory.php:270]
#4  queryFactory->Execute() called at [/home/thegluep/public_html/includes/functions/sessions.php:77]
#5  _sess_write()
#6  session_write_close() called at [/home/thegluep/public_html/includes/application_bottom.php:16]
#7  require(/home/thegluep/public_html/includes/application_bottom.php) called at [/home/thegluep/public_html/index.php:105]
--> PHP Fatal error: 1206:The total number of locks exceeds the lock table size :: insert into zen_sessions (sesskey, expiry, `value`)
            values ('7e143b74f714e7e936e5252825d1127e', 1573657881, 'c2VjdXJpdHlUb2tlbnxzOjMyOiJkNzc0MzliYWRiNWQ0NTAzNzMwNDljMTE1YjlhYTQ3OCI7Y3VzdG9tZXJzX2hvc3RfYWRkcmVzc3xzOjE0OiIxOTUuMTYyLjEwNS44MSI7Y2FydElEfHM6NToiNTQ0NDMiO2NhcnR8TzoxMjoic2hvcHBpbmdDYXJ0IjoxMzp7czo4OiJjb250ZW50cyI7YTozOntzOjM2OiIyNzg6MDdjZGZkMjMzNzNiMTdjNmIzMzcyNTFjMjJiN2VhNTciO2E6MTp7czozOiJxdHkiO2Q6MTt9czozNToiNTI6ZDE2NzBiZjc4NjYyMjk1ODkxZTllZjIyNDE3YTk1NmQiO2E6Mjp7czozOiJxdHkiO2Q6MTtzOjEwOiJhdHRyaWJ1dGVzIjthOjE6e2k6NDtpOjc0O319czozNjoiMjY2OjdiYzI3NjVjZDI3NTczNDhhNjczMjU4MjA1MWVlOGQwIjthOjI6e3M6MzoicXR5IjtkOjE7czoxMDoiYXR0cmlidXRlcyI7YToxOntpOjQ7aToxMTQ7fX19czo1OiJ0b3RhbCI7ZDozNy44Njk5OTk5OTk5OTk5OTc0NDIwNDYxNTEyNjM2MzkzMzA4NjM5NTI2MzY3MTg3NTtzOjY6IndlaWdodCI7ZDowLjYwOTUwMDAwMDAwMDAwMDA0MTc0NDM4NTcyNTkwNTg4NTkxOTkyODU1MDcyMDIxNDg0Mzc1O3M6NjoiY2FydElEIjtzOjU6IjU0NDQzIjtzOjEyOiJjb250ZW50X3R5cGUiO3M6ODoicGh5c2ljYWwiO3M6MTg6ImZyZWVfc2hpcHBpbmdfaXRlbSI7aTowO3M6MjA6ImZyZWVfc2hpcHBpbmdfd2VpZ2h0IjtpOjA7czoxOToiZnJlZV9zaGlwcGluZ19wcmljZSI7ZDowO3M6MTQ6ImRvd25sb2FkX2NvdW50IjtpOjA7czoyMjoidG90YWxfYmVmb3JlX2Rpc2NvdW50cyI7ZDozNy44Njk5OTk5OTk5OTk5OTc0NDIwNDYxNTEyNjM2MzkzMzA4NjM5NTI2MzY3MTg3NTtzOjIyOiJkaXNwbGF5X2RlYnVnX21lc3NhZ2VzIjtiOjA7czoyMzoiZmxhZ19kdXBsaWNhdGVfbXNnc19zZXQiO2I6MDtzOjMyOiJmbGFnX2R1cGxpY2F0ZV9xdWFudGl0eV9tc2dzX3NldCI7YTowOnt9fWNoZWNrX3ZhbGlkfHM6NDoidHJ1ZSI7bmF2aWdhdGlvbnxPOjE3OiJuYXZpZ2F0aW9uSGlzdG9yeSI6Mjp7czo0OiJwYXRoIjthOjE6e2k6MDthOjQ6e3M6NDoicGFnZSI7czo1OiJpbmRleCI7czo0OiJtb2RlIjtzOjM6IlNTTCI7czozOiJnZXQiO2E6MTp7czo1OiJjUGF0aCI7czozOiIxMDEiO31zOjQ6InBvc3QiO2E6MDp7fX19czo4OiJzbmFwc2hvdCI7YTowOnt9fWxhbmd1YWdlfHM6NzoiZW5nbGlzaCI7bGFuZ3VhZ2VzX2lkfHM6MToiMSI7bGFuZ3VhZ2VzX2NvZGV8czoyOiJlbiI7bGF5b3V0VHlwZXxzOjY6ImxlZ2FjeSI7Y3VycmVuY3l8czozOiJHQlAiO3RvZGF5X2lzfHM6MTA6IjIwMTktMTEtMTMiO3VwZGF0ZUV4cGlyYXRpb25zfGI6MTtzZXNzaW9uX2NvdW50ZXJ8YjoxO2N1c3RvbWVyc19pcF9hZGRyZXNzfHM6MTQ6IjE5NS4xNjIuMTA1LjgxIjttZXNzYWdlVG9TdGFja3xzOjA6IiI7Y3VzdG9tZXJfaWR8czo0OiIxMzk4IjtjdXN0b21lcl9kZWZhdWx0X2FkZHJlc3NfaWR8czo0OiIxNjA5IjtjdXN0b21lcnNfYXV0aG9yaXphdGlvbnxzOjE6IjAiO2N1c3RvbWVyX2ZpcnN0X25hbWV8czo0OiJKT25rIjtjdXN0b21lcl9sYXN0X25hbWV8czo1OiJwb3R0cyI7Y3VzdG9tZXJfY291bnRyeV9pZHxzOjM6IjIyMiI7Y3VzdG9tZXJfem9uZV9pZHxzOjM6IjE5MCI7ZnJvbV9jYXJ0fGk6MDt2YWxpZF90b19jaGVja291dHxiOjE7Y2FydF9lcnJvcnN8czowOiIiO25ld19wcm9kdWN0c19pZF9pbl9jYXJ0fHM6MDoiIjtzZW5kdG98czo0OiIxNjA5IjtjYXJ0X2FkZHJlc3NfaWR8czo0OiIxNjA5IjtwYXltZW50fHM6OToicGF5cGFsd3BwIjtzaGlwcGluZ193ZWlnaHR8ZDowLjYwOTUwMDAwMDAwMDAwMDA0MTc0NDM4NTcyNTkwNTg4NTkxOTkyODU1MDcyMDIxNDg0Mzc1O3NoaXBwaW5nfGE6NDp7czoyOiJpZCI7czoxMToidGFibGVfdGFibGUiO3M6NToidGl0bGUiO3M6MjE6IlRhYmxlIFJhdGUgKEJlc3QgV2F5KSI7czo0OiJjb3N0IjtkOjU7czo2OiJtb2R1bGUiO3M6NToidGFibGUiO30=')
            ON DUPLICATE KEY UPDATE `value`='c2VjdXJpdHlUb2tlbnxzOjMyOiJkNzc0MzliYWRiNWQ0NTAzNzMwNDljMTE1YjlhYTQ3OCI7Y3VzdG9tZXJzX2hvc3RfYWRkcmVzc3xzOjE0OiIxOTUuMTYyLjEwNS44MSI7Y2FydElEfHM6NToiNTQ0NDMiO2NhcnR8TzoxMjoic2hvcHBpbmdDYXJ0IjoxMzp7czo4OiJjb250ZW50cyI7YTozOntzOjM2OiIyNzg6MDdjZGZkMjMzNzNiMTdjNmIzMzcyNTFjMjJiN2VhNTciO2E6MTp7czozOiJxdHkiO2Q6MTt9czozNToiNTI6ZDE2NzBiZjc4NjYyMjk1ODkxZTllZjIyNDE3YTk1NmQiO2E6Mjp7czozOiJxdHkiO2Q6MTtzOjEwOiJhdHRyaWJ1dGVzIjthOjE6e2k6NDtpOjc0O319czozNjoiMjY2OjdiYzI3NjVjZDI3NTczNDhhNjczMjU4MjA1MWVlOGQwIjthOjI6e3M6MzoicXR5IjtkOjE7czoxMDoiYXR0cmlidXRlcyI7YToxOntpOjQ7aToxMTQ7fX19czo1OiJ0b3RhbCI7ZDozNy44Njk5OTk5OTk5OTk5OTc0NDIwNDYxNTEyNjM2MzkzMzA4NjM5NTI2MzY3MTg3NTtzOjY6IndlaWdodCI7ZDowLjYwOTUwMDAwMDAwMDAwMDA0MTc0NDM4NTcyNTkwNTg4NTkxOTkyODU1MDcyMDIxNDg0Mzc1O3M6NjoiY2FydElEIjtzOjU6IjU0NDQzIjtzOjEyOiJjb250ZW50X3R5cGUiO3M6ODoicGh5c2ljYWwiO3M6MTg6ImZyZWVfc2hpcHBpbmdfaXRlbSI7aTowO3M6MjA6ImZyZWVfc2hpcHBpbmdfd2VpZ2h0IjtpOjA7czoxOToiZnJlZV9zaGlwcGluZ19wcmljZSI7ZDowO3M6MTQ6ImRvd25sb2FkX2NvdW50IjtpOjA7czoyMjoidG90YWxfYmVmb3JlX2Rpc2NvdW50cyI7ZDozNy44Njk5OTk5OTk5OTk5OTc0NDIwNDYxNTEyNjM2MzkzMzA4NjM5NTI2MzY3MTg3NTtzOjIyOiJkaXNwbGF5X2RlYnVnX21lc3NhZ2VzIjtiOjA7czoyMzoiZmxhZ19kdXBsaWNhdGVfbXNnc19zZXQiO2I6MDtzOjMyOiJmbGFnX2R1cGxpY2F0ZV9xdWFudGl0eV9tc2dzX3NldCI7YTowOnt9fWNoZWNrX3ZhbGlkfHM6NDoidHJ1ZSI7bmF2aWdhdGlvbnxPOjE3OiJuYXZpZ2F0aW9uSGlzdG9yeSI6Mjp7czo0OiJwYXRoIjthOjE6e2k6MDthOjQ6e3M6NDoicGFnZSI7czo1OiJpbmRleCI7czo0OiJtb2RlIjtzOjM6IlNTTCI7czozOiJnZXQiO2E6MTp7czo1OiJjUGF0aCI7czozOiIxMDEiO31zOjQ6InBvc3QiO2E6MDp7fX19czo4OiJzbmFwc2hvdCI7YTowOnt9fWxhbmd1YWdlfHM6NzoiZW5nbGlzaCI7bGFuZ3VhZ2VzX2lkfHM6MToiMSI7bGFuZ3VhZ2VzX2NvZGV8czoyOiJlbiI7bGF5b3V0VHlwZXxzOjY6ImxlZ2FjeSI7Y3VycmVuY3l8czozOiJHQlAiO3RvZGF5X2lzfHM6MTA6IjIwMTktMTEtMTMiO3VwZGF0ZUV4cGlyYXRpb25zfGI6MTtzZXNzaW9uX2NvdW50ZXJ8YjoxO2N1c3RvbWVyc19pcF9hZGRyZXNzfHM6MTQ6IjE5NS4xNjIuMTA1LjgxIjttZXNzYWdlVG9TdGFja3xzOjA6IiI7Y3VzdG9tZXJfaWR8czo0OiIxMzk4IjtjdXN0b21lcl9kZWZhdWx0X2FkZHJlc3NfaWR8czo0OiIxNjA5IjtjdXN0b21lcnNfYXV0aG9yaXphdGlvbnxzOjE6IjAiO2N1c3RvbWVyX2ZpcnN0X25hbWV8czo0OiJKT25rIjtjdXN0b21lcl9sYXN0X25hbWV8czo1OiJwb3R0cyI7Y3VzdG9tZXJfY291bnRyeV9pZHxzOjM6IjIyMiI7Y3VzdG9tZXJfem9uZV9pZHxzOjM6IjE5MCI7ZnJvbV9jYXJ0fGk6MDt2YWxpZF90b19jaGVja291dHxiOjE7Y2FydF9lcnJvcnN8czowOiIiO25ld19wcm9kdWN0c19pZF9pbl9jYXJ0fHM6MDoiIjtzZW5kdG98czo0OiIxNjA5IjtjYXJ0X2FkZHJlc3NfaWR8czo0OiIxNjA5IjtwYXltZW50fHM6OToicGF5cGFsd3BwIjtzaGlwcGluZ193ZWlnaHR8ZDowLjYwOTUwMDAwMDAwMDAwMDA0MTc0NDM4NTcyNTkwNTg4NTkxOTkyODU1MDcyMDIxNDg0Mzc1O3NoaXBwaW5nfGE6NDp7czoyOiJpZCI7czoxMToidGFibGVfdGFibGUiO3M6NToidGl0bGUiO3M6MjE6IlRhYmxlIFJhdGUgKEJlc3QgV2F5KSI7czo0OiJjb3N0IjtkOjU7czo2OiJtb2R1bGUiO3M6NToidGFibGUiO30=', expiry=1573657881 ==> (as called by) /home/thegluep/public_html/includes/functions/sessions.php on line 77 <== in /home/thegluep/public_html/includes/classes/db/mysql/query_factory.php on line 171.

[13-Nov-2019 14:47:21 UTC] Request URI: /index.php?main_page=index&cPath=101, IP address: 195.162.105.81
#1  session_write_close()
--> PHP Warning: session_write_close(): Cannot call session save handler in a recursive manner in Unknown on line 0.

[13-Nov-2019 14:47:21 UTC] Request URI: /index.php?main_page=index&cPath=101, IP address: 195.162.105.81
#1  session_write_close()
--> PHP Warning: session_write_close(): Failed to write session data using user defined save handler. (session.save_path: /home/thegluep/public_html/cache) in Unknown on line 0.