Posted by stpaultim on Wed, Aug 08, 2018 - 10:20pm

I've run into this problem before moving my database from one environment to another and a colleague helped me make some necessary config changes to MYSQL. It's happening again and I did not document how to fix this. 

Problem is that I'm trying to upload the database on the remote server and I'm getting a Mysql error: "ERROR 1709 (HY000) at line 166: Index column size too large. The maximum column size is 767 bytes." Any help would be appreciated. 

 

This has to do with MySQL Database 4-byte UTF-8 support (see Status report for your site). More info:

Comments

Thu, Aug 09, 2018 - 2:03pm by stpaultim

My colleague tells me that we fixed the problem last time by editing the actual database dump and changing the character type from utf8mb4 to utf8.

In vim: `:%s/utf8mb4/utf8/g`

I've not tested this yet as I found a work-a-round last night. I expect to run into this problem again. 

Wed, May 29, 2019 - 9:36am by laryn

Sounds right -- I've run into this a few times as well when developing on my server (with the 4-byte UTF-8 support activated, e.g. for emojis) and then launching on a separate server that does not have 4-byte UTF-8 support.

In fact it just happened to me and I was going to document it here, but you already have. :)

Thu, May 30, 2019 - 12:54pm by laryn

Interestingly, I have run into this issue now. I did what you described to import the database to a server that didn't support 'utf8mb4' and then found out that this organization had the capability to upgrade their MariaDB installation to a version that supports it. Now trying to run the upgrade script to make the database 'utf8mb4' again errors out:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"Array%"' at line 1: SHOW TABLES LIKE "Array%"; Array ( ) in system_utf8mb4_convert_form_submit() (line 2499 of /path/core/modules/system/system.admin.inc).

I'm hoping nobody has done any major changes since I imported it the other day, in which case I'll just reimport the original again and be all set. Otherwise... ?