CiviCRM 5.13.4 - Specified key was too long; max key length is 767 bytes

Since CiviCRM 5.13.4 the following error is being reported on standard CPanel, Plesk and CentOS 6, CentOS 7 hosts using MariaDB 10.1.

May 20 10:26:22 [info] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => exceptionHandler )

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ENGINE=INNODB [nativecode=1071 ** Specified key was too long; max key length is 767 bytes]
[type] => DB_Error
[user_info] => CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ENGINE=INNODB [nativecode=1071 ** Specified key was too long; max key length is 767 bytes]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ENGINE=INNODB [nativecode=1071 ** Specified key was too long; max key length is 767 bytes]"]

)

May 20 10:26:22 [info] $backTrace = #0 /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Core/Error.php(952): CRM_Core_Error::backtrace("backTrace", TRUE) #1 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error)) #2 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #3 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #4 /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...", "DB_Error", TRUE) #5 /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7)) #6 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...", "1071 ** Specified key was too long; max key length is 767 bytes") #7 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError() #8 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #9 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #10 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #11 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(439): DB_DataObject->query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #12 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(1414): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...", TRUE) #13 /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check/Component/Env.php(914): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #14 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check/Component.php(55): CRM_Utils_Check_Component_Env->checkMysqlUtf8mb4() #15 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check.php(209): CRM_Utils_Check_Component->checkAll() #16 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/api/v3/System.php(153): CRM_Utils_Check::checkAll() #17 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(101): civicrm_api3_system_check((Array:3)) #18 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/Civi/API/Kernel.php(168): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9)) #19 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/Civi/API/Kernel.php(99): Civi\API\Kernel->runRequest((Array:9)) #20 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/api/api.php(23): Civi\API\Kernel->runSafe("System", "check", (Array:3), NULL) #21 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Utils/REST.php(316): civicrm_api("System", "check", (Array:3)) #22 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Utils/REST.php(566): CRM_Utils_REST::process((Array:3), (Array:3)) #23 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(277): CRM_Utils_REST::ajax() #24 /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(85): CRM_Core_Invoke::runItem((Array:12)) #25 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3)) #26 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:3)) #27 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/includes/menu.inc(527): civicrm_invoke("ajax", "rest") #28 (closed) /var/www/vhosts/websiteorgau.org.au/httpdocs/index.php(21): menu_execute_active_handler() #29 (closed) {main}

And another example.

May 21 11:40:13 [info] $backTrace = #0 /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/Error.php(952): CRM_Core_Error::backtrace("backTrace", TRUE) #1 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error)) #2 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_utf8mb4 _test (id VARCHAR(255), PRIMARY KEY(id(...") #3 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_utf8mb4_t est (id VARCHAR(255), PRIMARY KEY(id(...") #4 /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TA BLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...", "DB_Error", TRUE) #5 /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7)) #6 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), P RIMARY KEY(id(...", "1071 ** Specified key was too long; max key length is 767 bytes") #7 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError() #8 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id( ...") #9 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(.. .") #10 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY (id(...") #11 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(439): DB_DataObject->query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #12 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(1414): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...", TR UE) #13 /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check/Component/Env.php(914): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(...") #14 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check/Component.php(55): CRM_Utils_Check_Component_Env->checkMysqlUtf8mb4() #15 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check.php(209): CRM_Utils_Check_Component->checkAll() #16 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Utils/Check.php(93): CRM_Utils_Check::checkAll() #17 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/Page.php(218): CRM_Utils_Check->showPeriodicAlerts() #18 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Contact/Page/DashBoard.php(69): CRM_Core_Page->run() #19 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(311): CRM_Contact_Page_DashBoard->run((Array:1), NULL) #20 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(85): CRM_Core_Invoke::runItem((Array:13)) #21 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:1)) #22 (closed) /var/www/vhosts/websiteorgau/httpdocs/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:1)) #23 (closed) /var/www/vhosts/websiteorgau/httpdocs/includes/menu.inc(527): civicrm_invoke() #24 /var/www/vhosts/websiteorgau/httpdocs/index.php(21): menu_execute_active_handler() #25 (closed) {main}

Agileware Ref: CIVICRM-1213

changed the description

@mfb ping

At the moment, these log messages are expected if the check query fails.

But if there is an option to prevent logging an error message when query execution fails, then we could use it here.

@mfb can this check be moved to the daily scheduled job, "CiviCRM Update Check" or similar daily job which can perform sanity checks?

We are seeing the CiviCRM logs fill up with the same error information as the check is repeatedly being performed. The fact that this check is being logged as an error at all is misleading since it is expected behaviour for utf8mb4 test failing. Trouble-shooting other legitimate errors in CiviCRM log is also more difficult as a result.

I would welcome a review of the utf8mb4 test implementation. Also related to this issue is the new CiviCRM ALert #979 (closed)

I think system status check is the right place, but the query needs to be performed in such a way that it can't log an error. Unfortunately I have no idea how to do this but I'll poke around as time permits (note, I am on sabbatical right now so not online every day).

I couldn't figure out a way to disable error logging for CRM_Core_DAO::executeQuery() - maybe someone else knows how. But what we could do as a workaround is use mysqli_query().

added sig:bug triaged labels

@mfb thanks will test the PR and report back

@mfb @eileen PR works for me. No more of these errors being logged.

Thanks for taking on my feedback and providing a solution

Back to my other point, can this check be performed once or only at specific events in the CiviCRM lifecycle. I suggested before moving it to the daily scheduled job, "CiviCRM Update Check" or during the CiviCRM upgrade process?

My understanding is that this database compatibility check is performed repeatedly (possibly on every page load - which would be really excessive).

Happy to open a separate issue to track this line of discussion.

@eileen @mfb thoughts?

closed via commit 6bd3e3c4

@justinfreeman yes - separate issue for the checks big picture discussion - I think there are 2 issues

1. performance costs - it's not every page load - it's once a day so not bad for this one but some of them are not really acceptable ever outside a sched job
2. the noisiness of the messages & user experience.

Will ensure it's discussed in NY

thanks @eileen appreciated. Yes, those are the two issues - I'll lab them and/or link them.

Just a FYI, I've noticed in the logs that this check is being performed multiple times a day. I don't know why it would be doing that at all

Have created #1041 (closed)

once a day per logged in user?

@eileen I am not posting any more comments until that emoji changes to a smile! :)

(except for this one)