DESCRIPTION

I followed the exact steps from https://www.drupal.org/node/2754539 and installed the utf8mb4_convert drush command. I had configured my server as expected and adapted my settings.php file as advised. But an Exception was thrown which marked utf8mb4 as unsupported.

The culprit was the directive "default-storage-engine=MyISAM" in my my.cnf file. When the "drupal_utf8mb4_test" table was to be created, it was trying to be created as MyISAM which is limited to 1000 characters long. When requesting the table to be created as InnoDB everything worked fine.

SOLUTION

Change the SQL command from
CREATE TABLE {drupal_utf8mb4_test} (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC
to
CREATE TABLE {drupal_utf8mb4_test} (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC ENGINE=InnoDB

CommentFileSizeAuthor
#20 2764221-20.patch776 bytesstefan.r
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

vensires created an issue. See original summary.

cilefen’s picture

cilefen CreditAttribution: cilefen commented
Related issues: +#301362: Default to InnoDB in MySQL, +#2278965: Add a warning to the installer for MySQL if an InnoDB-compatible engine is not selected, +#2275535: [policy, no patch] Drop support for non-transactional database engines (including MyISAM)

Note for Drupal8:Drupal 8 no longer supports MyISAM as the default storage engine.

vensires’s picture

vensires CreditAttribution: vensires commented

Regarding to the solution proposed, I also want to remind that in Drupal 7 we don't have the requirements for InnoDB as the primary storage engine as is the case for Drupal 8.

cilefen’s picture

cilefen CreditAttribution: cilefen commented

What piece of code executes that query?

vensires’s picture

vensires CreditAttribution: vensires commented

It is found in includes/database/mysql/database.inc, l.224, function utf8mb4IsSupported().

It is called from Drush command utf8mb4_convert and from Drupal's status report.

cilefen’s picture

cilefen CreditAttribution: cilefen commented

I recommend opening a similar issue in the utf8mb4_convert queue and marking this one a related issue.

vensires’s picture

vensires CreditAttribution: vensires commented

It's not something the maintainers of utf8mb4_convert could fix though. I could open one though just for reference.

cilefen’s picture

cilefen CreditAttribution: cilefen commented

Oh, then I misunderstood #5. Ok, no need then.

David_Rothstein’s picture

David_Rothstein CreditAttribution: David_Rothstein as a volunteer commented
Version: 7.5 » 7.x-dev
Issue tags: +Drupal bugfix target

This needs a patch, but it seems like a reasonable change (I think)... @stefan.r would be a good person to review it.

Fabianx’s picture

Fabianx CreditAttribution: Fabianx as a volunteer and at Tag1 Consulting commented

#9: The problem with that is that if later we try to create UTF8-MB4 tables with MyISAM it would fail in production use, so I am not sure about the patch - unless we specify the engine explicitly in later create table queries as well.

vensires’s picture

vensires CreditAttribution: vensires commented

My proposition is to add the "ENGINE=InnoDB" directive to the SQL command as I initially proposed. utf8mb4_convert converts automatically any MyISAM tables to InnoDB as stated in #2762599-8: PDOException while converting cache_block table and I don't believe this function is used anywhere else or will ever be. It should be added though in both Drupal 7.50's and utf8mb4_convert's documentations that utf8mb4 is only supported with InnoDB database tables.

stefan.r’s picture

stefan.r CreditAttribution: stefan.r commented
Status: Active » Needs work

I don't mind to have InnoDB as a requirement for D7 utf8mb4 support, so the solution proposed in the OP seems fine to me.

Setting to NW as this still needs a patch.

Fabianx’s picture

Fabianx CreditAttribution: Fabianx as a volunteer and at Tag1 Consulting commented

#12: My Concern is would that not just hide the bug? Or would new tables created by Drupal be INNODB then?

I would imagine:

default = MyISAM

We test with specific InnoDB setting, we convert all tables => all is good

User installs 'foo' module, which creates 'foo' table.

Would 'foo' table then not use MyISAM or fail to create horribly?

stefan.r’s picture

stefan.r CreditAttribution: stefan.r commented

Well the combination of default engine = MyISAM and charset = utf8mb4 would not be allowed, and if we don't hard fail we'd probably have to ignore the default engine on table creation indeed (which seems like a better idea than falling back to utf8) and soft fail somehow

vensires’s picture

vensires CreditAttribution: vensires commented

I think Fabianx's point is correct. It seems to be something greater than what it originally seemed. We might have to change some other SQL commands too. For example, if the database configuration in settings.php has the following directives:

$databases['default']['default'] = array (
  'driver' => 'mysql',
  [...]
  'charset' => 'utf8mb4',
  'collation' => 'utf8mb4_general_ci',
);

we could then add Engine="InnoDB" when creating new tables for modules to override the default directive.

Besides, most modules (core or contrib) may not be Database Agnostic (since many assume MySQL is used) but they are definitely MySQL-Engine Agnostic. They don't know and they don't care if MEMORY, ARCHIVE, InnoDB or MyISAM is used.

If a module needs a specific engine, it could then ALTER the table in its hook_install() callback. It won't be something new actually, if you think about how modules set their weight: Drupal gives a weight of 0 and if they need something different, they set it in their hook_install() callback.

Fabianx’s picture

Fabianx CreditAttribution: Fabianx as a volunteer and at Tag1 Consulting commented

In that respect the current behavior of saying that UTF8-MB4 is not supported is correct.

Just the reason is wrong.

Maybe we just add that InnoDB needs to be the default engine to the docs and messages and call it a day?

fietserwin’s picture

fietserwin
Location Le Mont-Dore
CreditAttribution: fietserwin commented

includes/database/mysql/schema.inc does add (since 2009-04-25, line 83):

      'mysql_engine' => 'InnoDB',

So, IMO having it as server default is not a requirement and thus should DatabaseConnection_mysql::utf8mb4IsSupported() also add this to its test statement.

I tested with:

default-storage-engine=MYISAM

Results:
Fail (Error Code: 1071. Specified key was too long; max key length is 1000 bytes):

CREATE TABLE drupal_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC

Success:
CREATE TABLE drupal_utf8mb4_test (id VARCHAR(255), PRIMARY KEY(id(255))) engine InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC

Fabianx’s picture

Fabianx CreditAttribution: Fabianx as a volunteer and at Tag1 Consulting commented

#17: Okay, so the test case is that we already enforce InnoDB - regardless of the set default DB.

Then yes, we should use what is in schema.inc as engine explicitly.

stefan.r’s picture

stefan.r CreditAttribution: stefan.r commented

Can we add ENGINE=InnoDB to the test table creation query?

stefan.r’s picture

stefan.r CreditAttribution: stefan.r commented
Status: Needs work » Needs review
FileSize
2764221-20.patch776 bytes

Untested, but perhaps like this?

Fabianx’s picture

Fabianx CreditAttribution: Fabianx as a volunteer and at Tag1 Consulting commented
Status: Needs review » Reviewed & tested by the community

Looks good to me, RTBC.

David_Rothstein’s picture

David_Rothstein CreditAttribution: David_Rothstein as a volunteer commented

Can someone test the patch to make sure that it works as expected?

fietserwin’s picture

fietserwin
Location Le Mont-Dore
CreditAttribution: fietserwin commented

Configuration:

  • Windows 10, 64 bit
  • WampServer Version 3.0.0 64bit (containing MySql 5.7.9 and "mysqlnd 5.0.11-dev - 20120503 - $Id: 3c688b6bbc30d36af3ac34fdd4b7b5b787fe5555 $")
  • Installed Drupal 7.50 on an existing installation (thus with an existing database).
  • show variables gives(a.o.):
    • innodb_file_format Barracuda
    • innodb_file_format_check ON
    • innodb_file_format_max Barracuda
    • innodb_file_per_table ON
    • innodb_large_prefix ON

The test:

  1. Set in my.ini: default-storage-engine=MYISAM
  2. drush utf8mb4-convert-databases

    This will convert all databases defined in settings.php to utf8mb4. Back up your databases before continuing! Continue?
(y/n): y
Target MySQL database: default:default
The default:default MySQL database does not support UTF8MB4! Ensure that the conditions listed in settings.php related to innodb_large_prefix, the server version, and the MySQL driver version are met. See https://www.drupal.org/node/2754539 for more information.
  3. Apply patch
  4. drush utf8mb4-convert-databases

    This will convert all databases defined in settings.php to utf8mb4. Back up your databases before continuing! Continue?
(y/n): y
Target MySQL database: default:default
Converting database: drupal7_test
Converting table: variable_store
Converting table: block
Converting table: block_role
Converting table: block_custom
Converting table: cache_block
Converting table: webform
...

Conclusion: RTBC, works as expected.

hass’s picture

hass CreditAttribution: hass commented

I see this utf8mb4 support is now limited to InnoDB. Just asking - what about the other available storage engines? Memory storage engine is important I think and so on - they all do not support this utf8mb4? :-(

vensires’s picture

vensires CreditAttribution: vensires commented

To enable utf8mb4, you have to specify it as your character set in your settings.php file as stated in Multi-byte UTF-8 support in Drupal 7 and UTF8MB4 Convert.

For everything to work as expected though, a query is used to validate that the database may actually create indexes with 3072 characters. 3072 characters is the limit for InnoDB tables when "innodb_large_prefix = true" exists in your configuration file. "MEMORY tables", as stated in MySQL documentation, "can have up to 64 indexes per table, 16 columns per index and a maximum key length of 3072 bytes". So, no extra configuration required for Memory engine tables. ARCHIVE tables, on the other hand, don't support indexes at all. The only problem are MyISAM tables which are still limited to 1000 characters and this was the starting point of this whole issue.

Long story short, utf8mb4 is a character set and is supported when your tables support it.

stefan.r’s picture

stefan.r CreditAttribution: stefan.r commented
Assigned: Unassigned » David_Rothstein
David_Rothstein’s picture

David_Rothstein CreditAttribution: David_Rothstein as a volunteer commented
Title: utf8mb4IsSupported() guesses that InnoDB is the default engine » utf8mb4IsSupported() shouldn't guess that InnoDB is the default engine
Assigned: David_Rothstein » Unassigned
Status: Reviewed & tested by the community » Fixed

Yup, this seems like a good improvement which makes this test table creation statement consistent with Drupal's normal table creation statements.

Committed to 7.x - thanks!

bobburns’s picture

bobburns CreditAttribution: bobburns commented

A side note

Until I applied the patch - I could not successfully backup or copy my database for a backup in CPanel or WHM - I would get "Lost connection to server" error messages.

The table drupal_utf8mb4_test got created - but hung phpMyAdmin marked as "in use"

I could not also drop the table but had to rip it out using var/lib/mysql (other systems may have the database table in a different location) as the frm and ibd files in the backend and then use the successful query in #17 to recreate it

It would throw an error "drupal_utf8mb4_test is not in engine"

It should be noted that deleting the table was not sufficient to cause the database inc call to query and re-create it when loading the status report

I have the required support enabled in the my.cnf but not active in the settings.php and I have NOT yet converted the database

The message in the Status report also changed to

Database 4 byte UTF-8 support Disabled
4 byte UTF-8 for mysql is disabled. See the documentation on adding 4 byte UTF-8 support for more information.

from a much longer version that read

"4 byte UTF-8 for mysql is not activated, but it is supported on your system. It is recommended that you enable this to allow 4-byte UTF-8 input such as emojis, Asian symbols and mathematical symbols to be stored correctly. See the documentation on adding 4 byte UTF-8 support for more information."

The point is even if committed to core - even on the next core upgrade one may need to do manual work to fix the issue

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.