Skip to content

Common MySQL issues and troubleshooting#

Collations/Character Sets#

Every database table and field can be configured with a character set and collation. The character set determines how the text is stored, and the collation changes the way text is compared and sorted. If not specified at table cration time, the charset/collation will be set to the database server default.

Unknown collation: 'utf8mb4_0900_ai_ci'#

For MySQL version 8+, a new collation utf8mb4_0900_ai_ci was made available. Older version of MySQL and other database servers like MariaDB do not support this new collation.

This error is caused by syncing data from a database which uses utf8mb4_0900_ai_ci to a database that does not support it.

The easiest solution is to use a compatible database in each place that needs to be synced. When syncing from a managed database in a cloud environment to a local database in Pygmy, Lando, or DDev, use a MySQL 8 pod for the local database.

When a table is created without an explicit collation, the default collation will be used. Setting an explicit collation that is compatible with all database servers will also help avoid this error. This can be done in Drupal by setting the $databases['default']['default']['collation'] setting. This is done automatically in the amazeeio/drupal_integrations composer package v0.5.0+.

Duplicate entry for key PRIMARY#

The exact error will vary by table and column name. Here is one full example: Duplicate entry '/not/exists' for key 'redirect_404.PRIMARY'.

This error may be caused when syncing data between databases that have different default collations. Since collations change the way primary keys are compared, it's possible that two strings are considered unique in one collation, but the same in another, which causes the second primary key insert to fail.

This is most commonly seen when syncing data from MySQL 5 to MySQL 8. If it's possible to skip syncing the content of tables that are throwing the error, that is one solution. Some common Drupal database tables that can cause this error, and are safe to skip data are search_index, search_total, redirect_404, and the cache tables. The data in the tables can be skipped by passing -structure-tables-list=search_index,search_total,redirect_404,cache,cache_* to drush sql-dump.

PDO::_construct(): Server sent charset (255) unknown to the client.#

For MySQL version 8+, the default character set is utf8mb4 which is not available in very old versions of the Lagoon PHP images. Upgrade to a supported PHP image (7.0.19+, 7.1.5+, 7.2+, 8+).

It may also be possible to configure your application to connect using an older, supported, character set. Consult the documention for your application to find out how.

ERROR at line 1: Unknown command '-'.#

New versions of MariaDB have implemented a sandbox that gets enabled by default when exporting a database. Since this sandbox feature is MariaDB specific, it will cause errors when attempting to import the dump in MySQL.

This error is most commonly seen when attempting to sync a local MariaDB database to a cloud MySQL database. The easiest fix is to use MySQL for the local database also.

It can also be fixed by deleting this line at the begginning of the dump file before importing: /*!999999\- enable the sandbox mode */.

Access denied; you need (at least one of) the SUPER privilege(s) for this operation#

This error can be thrown when a database that contains triggers is synced to another database with different credentials. By default, the database dump will include the user that should be assigned to the trigger, and if that user account doesn't exist on the target database, the error is thrown.

The solution is to update the dump file and remove all instances of DEFINER which look like: /*!50017 DEFINER="pUQbguCnnF"@"%"*/.

When using lagoon-sync to sync databases, this can be done automatically by using a custom syncher to remove DEFINER statements.

Drupal modules know to cause this error:

You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)#

This error can be thrown when a database that contains triggers is synced to another database. If the target database has binary logging enabled, the creation of triggers is not allowed since that action cannot be committed to the binary log.

Binary logging is not enabled by default on local or cloud databases. This scenario should only be encountered in rare occasions, like during an upgrade of the cloud managed databases.

The solution is to wait until binary logging has been disabled again.