PDOException while converting cache_block table

#2 is odd. Don't you get a The x:y 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" warning when innodb_large_prefix is turned off?

It does a $connection->utf8mb4IsSupported() check, which should try to create a table with a large prefix, and show the warning. I wonder if you either missed the warning, or if this code is incorrect?

  public function utf8mb4IsSupported() {
    // Ensure that the MySQL driver supports utf8mb4 encoding.
    $version = $this->getAttribute(PDO::ATTR_CLIENT_VERSION);
    if (strpos($version, 'mysqlnd') !== FALSE) {
      // The mysqlnd driver supports utf8mb4 starting at version 5.0.9.
      $version = preg_replace('/^\D([\d.]).*/', '$1', $version);
      if (version_compare($version, '5.0.9', '<')) { 
        return FALSE;    
      }         
    }           
    else {      
      // The libmysqlclient driver supports utf8mb4 starting at version 5.5.3.
      if (version_compare($version, '5.5.3', '<')) { 
        return FALSE;    
      }         
    }           
                
    // Ensure that the MySQL server supports large prefixes and utf8mb4.
    try {       
      $this->query("CREATE TABLE {drupal_utf8mb4_test} (id VARCHAR(255), PRIMARY KEY(id(255))) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ROW_FORMAT=DYNAMIC");
    }           
    catch (Exception $e) { 
      return FALSE;    
    }           
    $this->query("DROP TABLE {drupal_utf8mb4_test}");
    return TRUE;
  }

In any case, besides showing the warning, it should also *not try to convert* of course (it did previously), so committing a fix for that now.

Just to add to this, we now convert all tables to InnoDB automatically.

@geerlingguy just so I can evaluate whether to mark this as fixed, could you perhaps answer #6 by reverting to the old mysql settings/database and seeing if you can still reproduce the error in the OP in the latest version of the project? (Alpha4 I think)

@geerlingguy, that doesn't sound right :)

We *want* an error to appear when innodb_large_prefix is turned off. Is that what happens now?

Thanks a lot for the blog post!

I'm getting a similar error on Pantheon:

17:bin pk$ terminus drush 'utf8mb4-convert-databases -y' --site=MYSITE --env=dev
[2016-07-12 03:47:19] [info] Running drush utf8mb4-convert-databases -y on MYSITE-dev
    cmd: 'utf8mb4-convert-databases -y'
    site: 'MYSITE'
    env: 'dev'
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error     [error]
or access violation: 1071 Specified key was too long; max key length
is 767 bytes' in
/srv/bindings/d767913ee30941f3847c9361d2d787ba/code/includes/database/database.inc:2204
Stack trace:
#0
/srv/bindings/d767913ee30941f3847c9361d2d787ba/code/includes/database/database.inc(2204):
PDOStatement->execute(Array)
#1
/srv/bindings/d767913ee30941f3847c9361d2d787ba/code/includes/database/database.inc(683):
DatabaseStatementBase->execute(Array, Array)
#2
/srv/bindings/d767913ee30941f3847c9361d2d787ba/code/includes/database/database.inc(2383):
DatabaseConnection->query('ALTER TABLE {ca...', Array, Array)
#3
/srv/bindings/d767913ee30941f3847c9361d2d787ba/.drush/utf8mb4_convert/utf8mb4_convert.drush.inc(175):
db_query('ALTER TABLE {ca...', Array)
#4
/srv/bindings/d767913ee30941f3847c9361d2d787ba/.drush/utf8mb4_convert/utf8mb4_convert.drush.inc(143):
DrupalCharsetConverter->convertTableFields('cache_block', NULL, NULL)
#5
/srv/bindings/d767913ee30941f3847c9361d2d787ba/.drush/utf8mb4_convert/utf8mb4_convert.drush.inc(116):
DrupalCharsetConverter->convertTable('cache_block', NULL, NULL)
#6
/srv/bindings/d767913ee30941f3847c9361d2d787ba/.drush/utf8mb4_convert/utf8mb4_convert.drush.inc(64):
DrupalCharsetConverter->convertTables()
#7
/srv/bindings/d767913ee30941f3847c9361d2d787ba/.drush/utf8mb4_convert/utf8mb4_convert.drush.inc(201):
DrupalCharsetConverter->convert(Array)
#8 [internal function]: drush_utf8mb4_convert_databases()
#9 /opt/pantheon/drush-8/includes/command.inc(366):
call_user_func_array('drush_utf8mb4_c...', Array)
#10 /opt/pantheon/drush-8/includes/command.inc(217):
_drush_invoke_hooks(Array, Array)
#11 [internal function]: drush_command()
#12 /opt/pantheon/drush-8/includes/command.inc(185):
call_user_func_array('drush_command', Array)
#13 /opt/pantheon/drush-8/lib/Drush/Boot/BaseBoot.php(67):
drush_dispatch(Array)
#14 /opt/pantheon/drush-8/includes/preflight.inc(66):
Drush\Boot\BaseBoot->bootstrap_and_dispatch()
#15 /opt/pantheon/drush-8/drush.php(12): drush_main()
#16 {main}
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: pantheon
Converting table: variable_store
Converting table: block
Converting table: block_role
Converting table: block_custom
Converting table: cache_block

A few notes:

• Pantheon runs on nginx.
• I am using 1.x-alpha4 version of this utility. I had to comment out the $connection->utf8mb4IsSupported() check because if I leave the check in I get this warning:

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.

Despite this check, Pantheon's implementation of nginx does support utf8mb4. I know this because I previously used a different patch that ultimately was not committed to core to support emoji on my site. (Since upgrading to 7.50, I have undone this patch.)

Unfortunately although I use Drupal a lot my understanding of databases is limited so I'm not sure how to go about fixing this.

@geerlingguy any feedback on #13?

@ptmkenny, obviously you can't just comment out the support check -- if it fails, then yes, the conversion is going to fail :)

The warning is a bit unclear yes, it's not really true that "MySQL database does not support UTF8MB4"... just rather that your Pantheon etup doesn't seem to comply with some of the listed requirements in order to have UTF8MB4 support in Drupal core (which are stricter than the ones in earlier patches). So I'd check with them.

@stefan.r Thanks for your input. I talked to Pantheon support, and they said that they do support uf8mb4. So I tried to dig into the matter deeper and this is what I found:

When it fails converting a table, the table that failed conversion will be the last one printed out, for example:

Converting table: cache_block

I can then look at that table and run a query on the first column like this:

ALTER TABLE cache_block CHANGE cid cid VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Then I can re-run the command and a different table will fail. For example, commerce_order also failed. In the case of commerce_order, there are several columns:

order_number
type
mail
status
hostname

All of which are VARCHAR with an encoding/collation set, but I only needed to run the ALTER TABLE query manually for order_number, and then utf8mb4_convert was able to keep processing.

I'm not sure how to fix this but please let me know if this is helpful.

Update: I heard back from Pantheon support again and actually at this time their database configuration does not meet all of the new 7.50 requirements.

Hi

as for me I had an old drupal install (4.7), evolved in drupal 7 now.
when doing the code
drush utf8mb4-convert-databases
it gives error:

Converting table: node_access
Cannot modify header information - headers already sent by (output started at /usr/share/php/drush/includes/output.inc:38)    [warning]
bootstrap.inc:1471
PDOException : SQLSTATE[HY000]: General error: 1214 The used table type doesn't support FULLTEXT indexes: ALTER TABLE {node_revision} ROW_FORMAT=DYNAMIC ENGINE=INNODB; Array
(
)
 dans DrupalCharsetConverter->convertTable() (ligne 134 dans /root/.drush/utf8mb4_convert/utf8mb4_convert.drush.inc).
Drush command terminated abnormally due to an unrecoverable error.   

After several try (nothing worked) to change the type of the table node_revision from MyINSAM to InnoDB, I finally compare the table node_revision with another site (younger) where the drush utf8mb4-convert-databases worked, and see the full text was in couple with the similar line.
So I deleted the line with "similar" in red, and make again the drush utf8mb4-convert-databases that finally worked.

Not sure that it is a good solution...?

thanks for advices