0

This is a bit long, but I think the background is important. I am in the process of upgrading from Civi 5.26.2 to 5.33.1 and need to understand whether I have the right environment.

I cloned my 5.26.2 live server to a test server and upgraded to 5.33.1. All looked good after a few errors on the way from me. Next I wanted to produce a new test server from my live server as a direct clone of 5.33.1, which involves a install of 5.33.1 and this fell over to the extent that even Wordpress was inaccessible.

I did find that I could install 5.33.1 on the

I tried a clean install of WordPress (5.6) and Civi 5.33.1 and got exactly the same problem. I did find that I could "clean" install of by installing 5.26.2 and upgrading.

So I am now worried that there may be something wrong with my upgraded live server that my testing didn't show that will come and bite me later when its too late to revert. One worry is that I have MySQL 5.6 and can't upgrade to 5.7 as I am on a shared server and have to wait for hosting service to upgrade. The warning message in the System Status says MySQL 5.7 is needed for Civi 5.34+. The error also says this, but I'm sure that's not in response to this particular error. The release notes for Civi 5.32 (I can't see any upgrade notes for 5.33) say MySQL 5.6 is OK but MySQL 5.7+ needed for 5.34. The overall minimum requirements from the download page say MySQL 5.7.5 and maybe problems with 5.6. So not consistent.

When I do the install I get

Fatal error: Uncaught Civi\Setup\Exception\SqlException: Cannot execute CREATE TABLE `civicrm_extension` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'Local Extension ID', `type` varchar(8) NOT NULL , `full_name` varchar(255) NOT NULL COMMENT 'Fully qualified extension name', `name` varchar(255) COMMENT 'Short name', `label` varchar(255) COMMENT 'Short, printable name', `file` varchar(255) COMMENT 'Primary PHP file', `schema_version` varchar(63) COMMENT 'Revision code of the database schema; the format is module-defined', `is_active` tinyint DEFAULT 1 COMMENT 'Is this extension active?' , PRIMARY KEY (`id`) , UNIQUE INDEX `UI_extension_full_name`( full_name ) , INDEX `UI_extension_name`( name ) ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC: Specified key was too long; max key length is 767 bytes in /home/coma6771/public_html/testcrm/wp-content/plugins/civicrm/ in /home/coma6771/public_html/testcrm/wp-content/plugins/civicrm/civicrm/setup/src/Setup/DbUtil.php on line 204

There has been a critical error on this website. Please check your site admin email inbox for instructions.

And then when I can't access WordPress at all I get

Initialization Error

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => simpleHandler
        )

    [code] => -18
    [message] => DB Error: no such table
    [mode] => 16
    [debug_info] => SELECT id, name, value, domain_id, contact_id, is_domain, component_id, created_date, created_id
FROM civicrm_setting
WHERE (domain_id = 1) AND (is_domain = 1)
 [nativecode=1146 ** Table 'coma6771_wp578.civicrm_setting' doesn't exist]
    [type] => DB_Error
    [user_info] => SELECT id, name, value, domain_id, contact_id, is_domain, component_id, created_date, created_id
FROM civicrm_setting
WHERE (domain_id = 1) AND (is_domain = 1)
 [nativecode=1146 ** Table 'coma6771_wp578.civicrm_setting' doesn't exist]
    [to_string] => [db_error: message="DB Error: no such table" code=-18 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info="SELECT id, name, value, domain_id, contact_id, is_domain, component_id, created_date, created_id
FROM civicrm_setting
WHERE (domain_id = 1) AND (is_domain = 1)
 [nativecode=1146 ** Table 'coma6771_wp578.civicrm_setting' doesn't exist]"]
)


Warning: Invalid argument supplied for foreach() in /home/coma6771/public_html/testcrm/wp-content/plugins/civicrm/civicrm/Civi/Core/SettingsBag.php on line 354

To prepare for CiviCRM v5.34+, please upgrade MySQL. The recommended version will be MySQL v5.7+ or MariaDB v10.1+. 
1

I think your missing some settings in your mysql configuration?

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

Taken from https://docs.civicrm.org/installation/en/latest/general/requirements/#mysql-configuration

I'm not 100% sure they will cure your issue but from how the storage engine/row lengths work in MySQL its a strong possibility they are related.

7
  • The reference you provide says "In order to support a future data migration from utf8 to the utf8mb4 character set, it is recommended, though not yet required, to add the following configuration directives on MySQL 5.5 or 5.6 (these are the default values on MySQL 5.7):" so it should be OK. Also I can't work out where this config should go (maybe I don't have access on this shared server?] – Mick Kahn Jan 14 at 23:22
  • If your on shared hosting then this probably can be set in your control panel. My thinking is in that direction as if you see the error it states the tables are being created with utf8mb4_unicode_ci. This character set uses 4x the space that utf8 (non mb4) uses and unless you flip the database into barracuda mode mysql simply wont be able to cope with the number of characters. Thats my gut feel - the CiviCRM guidance is specific to CiviCRM hence it may not seem to be suggesting its the issue but the error is pretty clear that the table creation is failing. – Parvez Saleh Jan 14 at 23:36
  • I think I'm going to have to back out of my upgrade for now (or at least only go to an earlier version). I don't understand all this enough to be confident. I think that these settings apply at the whole MySQL server level, not just the specific database and I am concerned that I might affect another web site that I don't manage. I'll have to some more research before I come back to this. Your answer sounds very plausible and thanks for your help. – Mick Kahn Jan 15 at 9:30
  • No problem. You are right in that the settings are server wide and as your on shared hosting (assuming your not the admin) then your hands are tied. There was talk of allowing the mb4 setting configurable but I'm not sure if it happened. – Parvez Saleh Jan 15 at 18:31
  • I've been able to try on another hosting service that is at MySQL 5.7 and therefore barracuda and it all installs with no problem. So your answer is correct and the documentation is confusing at least and possibly wrong in that barracuda is needed for 5.33.1. – Mick Kahn Jan 16 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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