Howto MySQL : questions fréquentes et erreurs courantes

Pour le guide d’installation et d’usage courant, consultez /HowtoMySQL.

Problème de charset

Lors de migration de bases, ou tout simplement restauration de dump, un des problèmes les plus courants est d’avoir des problèmes d’encodage de caractères. L’encodage de caractères est complexe sous MySQL car il peut être géré à plusieurs niveaux (serveur, base, table, etc.).

Voici donc quelques astuces qui peuvent servir (ou pas) :

Mettre son client MySQL avec le bon encodage (dans votre .my.cnf)

[client]
default-character-set=utf8

Vérifier le type d’encodage du dump

$ file my.dymp
my.dump: UTF-8 Unicode text, with very long lines

Attention, l’outil file n’est pas fiable à 100%… notamment en cas de présence de caractères de plusieurs encodages différents.

Modifier l’encodage d’un dump avec ICONV. Cela sera souvent dans ce sens-là

$ iconv -f iso -t utf8 my.dump > my.dump.utf8

Dans certains cas très tordus, iconv -f utf8 -t utf8 peut avoir une utilité.

Forcer l’encodage lors de la réinjection

$ mysql --default-character-set utf8 foo < my.dump

Forcer l’encodage directement dans le dump

On créera les bases et les tables ainsi :

mysql> CREATE DATABASE foo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE TABLE bar (coin int) DEFAULT CHARSET=utf8;

Modifier l’encodage du dump avec VIM

On peut forcer l’UTF8 avec :set fileencoding=utf8. Cela peut être utile en cas de présence de caractères de plusieurs encodages différents.

Bien sûr, il faut en général se taper des remplacements à la main.

Dans tous les cas, on vérifiera si les fichiers ont été modifiés en calculant la somme MD5 par exemple…

Note : si votre dump fait plusieurs Go, vérifiez que vous avez assez de mémoire pour l’ouvrir entièrement avec Vim ;-)

Si les données sont injectées avec LOAD DATA INFILE

Il faut préciser CHARACTER SET latin1 ou CHARACTER SET utf8 notamment si des tables ont des encodages différents !

Par exemple, si l’on a des dumps .txt de tables au format latin1 et utf8, on fera ainsi :

$ grep CHARSET=latin1 *.sql
$ grep CHARSET=utf8 *.sql

En fonction des résultats, on réinjectera les données .txt ainsi :

$ echo "LOAD DATA INFILE '$PWD/<table>.txt' INTO TABLE <table>"  CHARACTER SET utf8 | mysql <db>; done
$ echo "LOAD DATA INFILE '$PWD/<table>.txt' INTO TABLE <table>"  CHARACTER SET latin1 | mysql <db>; done

Purger une table InnoDB (fichier .ibd)

En utilisant l’option innodb_file_per_table cela crée des fichiers .ibd par table. Lors d’une suppression de lignes, l’espace n’est pas libéré immédiatement, il faut ensuite faire un OPTIMIZE TABLE pour « purger » les fichiers .ibd

Reset mot de passe MySQL

Si vous avez oublié le mot de passe MySQL admin, vous pouvez relancer le démon MySQL ainsi :

$ /usr/bin/mysqld_safe --skip-grant-tables
130314 16:47:10 mysqld_safe Logging to syslog.
130314 16:47:10 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Vous pourrez ainsi accéder à MySQL sans mot de passe, et aller changer le mot de passe admin dans la table mysql.user.

UPDATE user SET Password = PASSWORD('password') WHERE User = 'root';

Indexes désactivés

Si des requêtes « normales » semblent très lentes, vérifier que les indexes ne sont pas désactivés ! En effet, on peut désactiver les indexes avec une requête : ALTER TABLE ... DISABLE KEYS Pour le vérifier, on vérifiera via un SHOW INDEXES FROM <table> Pour le réactiver (cela peut être long) : ALTER TABLE ... ENABLE KEYS

Cache désactivé

MySQL dispose d’un cache, par exemple si vous faites deux fois un SELECT identique (et simple) sur une table qui n’a pas été modifiée, le 2ᵉ SELECT devrait être renvoyé par le CACHE. Pour vérifier que cela fonctionne, vous pouvez observer le compteur de cache hits :

mysql> show status like 'Qcache_hits';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Qcache_hits   | 544667 |
+---------------+--------+
1 row in set (0.00 sec)

Un bug vicieux impacte MySQL 5.1 et 5.5 (corrigé à partir de 5.6.9) : si le nom de la base comporte des caractères spéciaux (le - par exemple) et que les tables sont en InnoDB… le cache ne marche pas !!!!

Voir http://bugs.mysql.com/bug.php?id=64821 et http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-9.html

Erreur avec base InnoDB

InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

The total number of locks exceeds the lock table size

Augmenter la taille des données temporaire pour les tables

Error 'Got error 64 'Temp file write failure' from InnoDB' on query.

Il faut augmenter la valeur de tmp_table_size.

Trop de “locks” lors d’une requête.

The total number of locks exceeds the lock table size

Il faut augmenter la valeur de innodb_buffer_pool_size.

Erreur 24

Si vous obtenez des erreurs de ce type, lors d’un mysqldump par exemple :

mysqldump: Got error: 1016: Can't open file: './foo/bar.frm' (errno: 24) when using LOCK TABLES
mysqldump: Got error: 23: Out of resources when opening file '.\foo\bar.MYD' (Errcode: 24) when using LOCK TABLES

C’est que votre serveur MySQL tente d’ouvrir trop de fichiers simultanément.

Pour augmenter le nombre maximal de fichiers pouvant être ouverts, vous pouvez ajuster le paramètre suivant(dans la limite permise par votre système d’exploitation) :

[mysqld]
open_files_limit = 2048

La valeur par défaut étant de 1024.

Note : inutile de positionner une valeur pour ulimit -n dans les scripts de démarrage, mysqld_safe s’en charge tout seul.

Problème de definer avec les vues

Un problème classique qui peut arriver suite à une migration d’une base de données contenant des vues. Si l’utilisateur qui a créé la vue (le definer) n’existe pas sur le nouveau serveur, la vue sera inutilisable :

mysql> SELECT * FROM myview;
MySQL ERROR 1449 (HY000) : The user specified as a definer ('root'@'localhost') does not exist

Il faut donc soit faire un sed dans le dump MySQL pour remplacer le definer si on est à l’étape de préparation de la migration. Si la migration a déjà été faite et qu’il n’est plus possible de réinjecter un dump, il faut modifier la vue via ALTER VIEW, mais il est nécessaire d’indiquer la définition complète de la vue. Pour la connaitre :

mysql> SHOW CREATE VIEW myview;

Copier ensuite la définition complète, en remplaçant CREATE par ALTER, et bien sûr le definer de la vue par un utilisateur existant qui aura les droits sur la vue.

mysql> ALTER DEFINER=`user`@`localhost` VIEW myview AS […]

Pour lister les vues dans une base :

mysql> SHOW FULL TABLES IN foo WHERE TABLE_TYPE LIKE 'VIEW';

Erreur 2020

Si vous obtenez l’erreur suivante lors d’un mysqldump :

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `mytable` at row: 6542

Augmentez la valeur de max_allowed_packet dans la section [mysqldump] du fichier my.cnf :

[mysqldump]
max_allowed_packet      = 64M

Erreur 2013

Si vous obtenez l’erreur suivante dans les logs du service MySQL lors d’un mysqldump :

[Warning] Aborted connection 49291 to db: 'foo' user: 'foo' host: 'localhost' (Got an error writing communication packets)

Testez la même solution que pour l’erreur 2020 ci-dessus.

Erreur 1267

Si vous obtenez une erreur du type :

ERROR 1267 (HY000): Illegal mix of collations (binary,IMPLICIT) and (utf8_bin,NONE) for operation 'like'

C’est qu’il y a souci entre votre charset client (character_set_client, collation_connection) et votre requête. Vous pouvez les ajuster avec des commandes du type :

mysql> set character_set_client=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set collation_connection=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

Dans certain cas (création d’une vue par exemple), cela peut venir d’une version de MySQL trop ancienne (on a constaté des requêtes qui passaient en 5.1 mais pas en 5.0).

Nombre de colonnes incorrectes pour mysql.proc

Si vous avez des erreurs de ce type :

[ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
[ERROR] mysql.user has no `Event_priv` column at position 29
[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
[ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
[ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode'
[ERROR] Column count of mysql.user is wrong. Expected 45, found 42. The table is probably corrupted

Cela signifie que les tables de la base mysql ne correspondent pas à la version de MySQL en cours. Vous avez sûrement mis à jour MySQL ou réinjecter des données d’une autre base.

Plusieurs solutions : réinjecter les tables incorrectes ou utilisez mysql_upgrade qui va adapter les tables.

Désactiver la complétion

En cas de souci lors de la connexion en ligne de commande MySQL, vous pouvez désactiver la complétion automatique. En effet, cette complétion peut créer de soucis si certaines tables sont corrompues :

$ mysql --skip-auto-rehash

Erreur d’âge du « checkpoint »

mysqld: 120313 12:16:10  InnoDB: ERROR: the age of the last checkpoint is 9433587,
mysqld: InnoDB: which exceeds the log group capacity 9433498.
mysqld: InnoDB: If you are using big BLOB or TEXT rows, you must set the
mysqld: InnoDB: combined size of log files at least 10 times bigger than the
mysqld: InnoDB: largest such row.

Il faut augmenter le log InnoDB, notamment innodb_log_file_size.

Attention, il faudra ensuite stopper MySQL et effacer les fichiers ib_logfile* ! Pour plus de détails, voir : http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ et http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html

Erreur d’index au chargement de la table InnoDB

Si vous obtenez une erreur du type :

InnoDB: Error: trying to load index PRIMARY for table foo/bar
InnoDB: but the index tree has been freed!
121222 11:28:48 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=713031680
read_buffer_size=131072
max_used_connections=31
max_connections=384
threads_connected=29
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1531901 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x84dc0a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
Cannot determine thread, fp=0x84dc0a0, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x84dc0a0, stack_bottom=0x44660000, thread_stack=196608, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort…
thd->query at 0x7f0d9c284ba0  is invalid pointer
thd->thread_id=355
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Cela semble être une table corrompue, et a priori il faut réinstaurer la table concernée. Se connecter en ligne de commande avec l’option mysql --skip-auto-rehash puis supprimer la table concernée (voir ci-dessous si besoin) et réinjecter là.

Diverses astuces sont listées ici : http://dba.stackexchange.com/questions/23296/mysql-innodb-index-in-swap

Souci lors de la création/suppression de table InnoDB

On suppose que vous utilisez bien l’option innodb_file_per_table comme conseillé.

Pour supprimer une table problématique, vous pouvez éteindre MySQL et supprimer le fichier .frm correspondant ! Néanmoins la table sera toujours référencée par InnoDB, et vous devez créer un .frm simple pour tromper le moteur :

mysql> create table foo(foo int) ENGINE=InnoDB;
# /etc/init.d/mysql stop
# cp /var/lib/mysql/foo/baz.frm /var/lib/mysql/foo/bar.frm

Quelques informations supplémentaires sur : http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

Erreur 121

Error 121 : InnoDB : ERROR 1005 (HY000): Can't create table './foo/bar.frm' (errno: 121)`

Il s’agit d’un problème avec les clés. Par exemple, les clés que vous créez sont déjà référencée par InnoDB. Cela peut ainsi se produire si vous avez du supprimer une table InnoDB via son fichier .frm

Une astuce possible est simplement de créer la table sans ses clés. Une fois créée, vous devez voir les clés avec un SHOW CREATE TABLE. À vous de voir si vous devez les modifier/supprimer.

Erreur de démarrage InnoDB

InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA

Si le service MySQL/InnoDB refuse de démarrer à cause d’une erreur du type :

mysqld: InnoDB: Starting in background the rollback of uncommitted transactions
mysqld: 140130 16:01:44  InnoDB: Rolling back trx with id 13B87781, 3 rows to undo
mysqld: 140130 16:01:44  InnoDB: Assertion failure in thread 140516188849920 in file fut0lst.ic line 83
mysqld: InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA

C’est que la base est corrompue…

Il faut sauvegarder le datadir. Puis tenter de redémarrer avec l’option innodb_force_recovery=1 puis innodb_force_recovery=2 puis innodb_force_recovery=3 etc. jusqu’à ce que le service démarre. Attention, à partir de innodb_force_recovery=3 vous devrez sûrement ajouter innodb_purge_threads=0 :

innodb_force_recovery = 3
innodb_purge_threads = 0

Dès que le service démarre (il sera peut-être en read-only), faites un dump de toutes vos bases MySQL.

Vous devrez sûrement repartir de zéro en recréant un datadir tout neuf (mysql_install_db --datadir=/var/lib/mysql.new) et en réinjectant votre dump.

Voir http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Erreur 13

Error 13 : mysqld: #007/usr/sbin/mysqld: File '/var/log/mysql/mysql-bin.00NNNN' not found (Errcode: 13)

Si vous obtenez des erreurs de ce type :

mysqld: #007/usr/sbin/mysqld: File '/var/log/mysql/mysql-bin.005655' not found (Errcode: 13)
mysqld: 130202 19:49:05 [ERROR] Failed to open log (file '/var/log/mysql/mysql-bin.005655', errno 13)
mysqld: 130202 19:49:05 [ERROR] Could not open log file
mysqld: 130202 19:49:05 [ERROR] Can't init tc log
mysqld: 130202 19:49:05 [ERROR] Aborting

C’est que MySQL n’arrive pas à accéder au dernier binlog. Cela peut par exemple être une question de droits. Pour vérifier que tout est correct, faire :

# sudo -u mysql head /var/log/mysql/mysql-bin.00NNNN

Une erreur étrange mais classique est que le fichier binlog prenne les droits de root. On le corrigera ainsi :

# chown mysql:adm /var/log/mysql/mysql-bin.00NNNN

Erreur 130

ERROR 130 (HY000): Incorrect file format '[…]'

Si vous obtenez l’erreur ci-dessus, lors d’un mysqldump par exemple, et que les fichiers ${mysql_datadir}/foo/bar.{MYD,MYI} sont vides mais pas le .frm, il faut réparer la table comme ceci :

# mysqlcheck --auto-repair --check --use-frm foo bar

Trop de connexions

Erreur : is blocked because of many connection errors.

Blocage pour l’IP car nombreuses erreurs sur BD. Lié à la valeur de la variable max_connect_errors. Pour résoudre la situation :

# mysqladmin flush-hosts

Erreur 1290

Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

Lors du passage à la version 5.5.53, la valeur par défaut est passée de vide à /var/lib/mysql-files ce qui casse les mysqldump qui écrivent leurs fichiers ailleurs (malgré des droits systèmes adaptés).

Sur Debian, la nouvelle valeur par défaut est /var/lib/mysql-files. L’utilisation d’un lien symbolique de cet emplacement vers le dossier réel (par exemple /home/mysqldump) ne suffit pas et MySQL continue de refuser le dump.

En attendant de passer à Stretch, nous recommandons de remettre la valeur par défaut précédente (vide) et redémarrer MySQL.

Restriction par bloc IP source

Lorsqu’on souhaite que certains utilisateurs soient restreints en fonction de leur origine de connexion, on utilise le champ Host dans lequel on met autre chose que les valeurs classiques telles que % ou localhost.

Pour une restriction sur une seule IP, c’est simple on l’indique dans ce champ.

Pour une restriction par plage IP il faut donner une valeur de la forme 192.168.2.0/255.255.255.0. La notation CIDR (192.168.2.0/24) n’est pas supportée. De plus il faut indiquer la première IP du bloc ; dans le cas d’un /24 c’est 192.168.2.0 et pas 192.168.2.42.

ERROR 1524 (HY000): Plugin ‘0’ is not loaded

Si vous avez cette erreur quand un utilisateur mysql essaye de se connecter et que vous avez ce genre d’erreur dans les logs :

[Warning] 'user' entry 'foo@bar' has both a password and an authentication plugin specified. The password will be ignored.

C’est que l’utilisateur utilise un plugin obsolète où qui n’existe pas, cela peut se solutionner avec :

update user set plugin='' where User='user';

Your password has expired.

En cas de message :

To log in you must change it using a client that supports expired  passwords.

Il faut se loguer en ligne de commande mysql avec l’utilisateur concerné et changer son mot de passe

mysql -u foo -p

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD FOR 'jdoe'@'localhost' = PASSWORD('PASSWORD');

Note : si vous remettez le même mot de passe que celui utilisé actuellement, cela va fonctionner, il n’y a pas de vérification

Sinon on peut désactiver cette option d’expiration pour les mots de passes. Pour cela, il est peut être nécessaire de se connecter avec les comptes ‘debian’ mysql (si l’expiration des mots de passes est globale).

#mysql -e 'SET GLOBAL default_password_lifetime = 0;'

Côté conf mysql :

[mysqld]
default_password_lifetime=0

Connexion refusée après avoir créé une nouvelle instance mysql.

Faire un ps auwx, pour récupérer toutes les options avec laquelle tourne l’instance MySQL:

Exemple:

# ps auwx |grep mysqld2

root     34419  0.0  0.0  13720  3272 ?        S    Dec13   0:00 /bin/bash /usr/bin/mysqld_safe --user=mysqld2 --bind-address=0.0.0.0 --port=3308 --socket=/var/run/mysqld2/mysqld.sock --pid-file=/var/run/mysqld2/mysqld.pid --datadir=/srv/mysqld2/mysql --long_query_time=5 --slow_query_log=1 --slow_query_log_file=/var/log/mysqld2/slow.log --group_concat_max_len=10000 --max_connections=250 --back_log=100 --max_connect_errors=10 --key_buffer_size=512M --max_allowed_packet=64M --thread_stack=192K --thread_cache_size=80 --table_cache=4096 --table_open_cache=4096 --table_definition_cache=4096 --query_cache_limit=8M --query_cache_size=256M --query_cache_type=1 --max_heap_table_size=1G --tmp_table_size=1G --innodb_file_per_table --innodb_buffer_pool_size=29025M --innodb_additional_mem_pool_size=16M --innodb_thread_concurrency=16 --default-time-zone=-04:00 --tmpdir=/home/mysqld2tmp

Stopper l’instance mysql concernée, par exemple :

# mysqld_multi stop 2

Démarrer l’instance avec toutes les options du ps auwx et ajouter l’option –skip-grant-tables pour désactiver l’authentification :

/bin/bash /usr/bin/mysqld_safe --user=mysqld2 --bind-address=0.0.0.0 --port=3308 --socket=/var/run/mysqld2/mysqld.sock --pid-file=/var/run/mysqld2/mysqld.pid --datadir=/srv/mysqld2/mysql --long_query_time=5 --slow_query_log=1 --slow_query_log_file=/var/log/mysqld2/slow.log --group_concat_max_len=10000 --max_connections=250 --back_log=100 --max_connect_errors=10 --key_buffer_size=512M --max_allowed_packet=64M --thread_stack=192K --thread_cache_size=80 --table_cache=4096 --table_open_cache=4096 --table_definition_cache=4096 --query_cache_limit=8M --query_cache_size=256M --query_cache_type=1 --max_heap_table_size=1G --tmp_table_size=1G --innodb_file_per_table --innodb_buffer_pool_size=29025M --innodb_additional_mem_pool_size=16M --innodb_thread_concurrency=16 --default-time-zone=-04:00 --tmpdir=/home/mysqld2tmp --skip-grant-tables

Se connecter à l’instance, et y attribuer un mot de passe root :

MariaDB [(none)]> UPDATE user SET password=PASSWORD("password") WHERE User='root';

Arrêter l’instance démarrée avec l’option –skip-grant-tables et demarré l’instance normalement :

# kill PID_INSTANCE
# mysqld_multi start 2

Se connecter à l’instance avec l’utilisateur root, créer l’utilisateur mysqladmin et donner les bons droits :

# mysql -u root -P3308

MariaDB [(none)]> CREATE USER mysqladmin@localhost IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALL ON *.* to mysqladmin@localhost IDENTIFIED BY 'password';

Enfin supprimer l’utilisateur root :

MariaDB [(none)]> DELETE FROM mysql.user where User='root';

Connaître la taille des bases

$ mysql mysql
mysql> SELECT table_schema AS "Database Name", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema; 

Connaître la taille des tables d’une base

$ mysql mysql
mysql> SELECT table_name AS "Table Name",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "<NOM_BDD>"
ORDER BY (data_length + index_length) DESC;

Restauration d’une base depuis un dump complet

Voir Restauration d’une table en utilisant en recherche, à la place :

$ grep -n '^USE `' dump.sql

Restauration d’une table depuis un dump complet

Pour extraire une table depuis un dump complet dump.sql, on détermine la liste des tables via grep -n "Table structure", par exemple :

$ grep -n "Table structure" dump.sql

19:-- Table structure for table `wp-cgp`
43:-- Table structure for table `wp_WP_SEO_404_links`
73:-- Table structure for table `wp_WP_SEO_Redirection`
109:-- Table structure for table `wp_WP_SEO_Redirection_LOG`
143:-- Table structure for table `wp_acccess`
170:-- Table structure for table `wp_avant_premiere`
196:-- Table structure for table `wp_commentmeta`
223:-- Table structure for table `wp_comments`
265:-- Table structure for table `wp_icl_string_translations`
294:-- Table structure for table `wp_icl_strings`
322:-- Table structure for table `wp_investments`
396:-- Table structure for table `wp_links`
431:-- Table structure for table `wp_login_redirects`
458:-- Table structure for table `wp_mtouchquiz_answer`
486:-- Table structure for table `wp_mtouchquiz_question`

Si l’on veut extraire la table wp_investments, on détermine où commence et finit la table, ici elle commence à la ligne 322 et finit à la ligne 395 (juste avant la table suivante).

On peut ainsi utiliser sed -n pour extraire la table :

$ sed -n '[numéro_du_début_de_la_ligne],[numéro_de_fin_de_la_ligne] p' dump.sql > table.sql

Exemple avec la table wp_investments :

$ sed -n '322,395 p' dump.sql > wp_investments.sql

command denied to user ’‘@’localhost’

Si PhpMyAdmin n’affiche pas les tables d’une base et que la commande SHOW TABLE STATUS retourne command denied to user ''@'localhost' c’est que c’est peut-être lié à une vue qui contient un mauvais DEFINER.
Voir cet article : https://stackoverflow.com/questions/6527599/mysql-forgets-who-is-logged-in-command-denied-to-user.

On pourra lister les vues et changer les mauvais DEFINER

storage engine FEDERATED

MariaDB n’inclut plus par défaut le moteur FEDERATED comme dans MySQL 5.5. On peut l’installer (en fait c’est un fork nommé FederatedX) ainsi :

MariaDB [(none)]> INSTALL PLUGIN federated SONAME 'ha_federatedx.so';
Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]> show engines;
[…]
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
[…]

Binary logging not possible. Message: Transaction level ‘READ-COMMITTED’ in InnoDB is not safe for binlog mode ‘STATEMENT’

En cas d’erreur du type :

Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

cela signifie que votre BINLOG_FORMAT doit être en ROW car il l’était précédemment. Vous pouvez vérifier que c’est bien le cas en faisant :

MariaDB [(none)]> show variables LIKE 'BINLOG_FORMAT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

Changer le Host d’un utilisateur et d’une base MySQL :

Si on souhaite changer les droits d’un utilisateur sur une base mysql de localhost à ‘%’ pour écouter sur toutes les ips :

mysql> UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='foo' AND Db='bar';

Si on veux le faire sur toutes les bases où l’utilisateur foo à les droits :

mysql> UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='taxes';

Et si on veux appliquer ça aux utilisateurs :

mysql> UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='taxes';

ERROR 1148: The used command is not allowed with this MySQL version

Si cette erreur survient lors d’une requête avec un LOAD DATA LOCAL INFILE il faut vérifié sur la variable local_infile est bien activé :

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

Dans la configuration de mysql mettre :

local-infile=1

Et pour l’activer à chaud :

SET GLOBAL local_infile=ON;

Si une requête LOAD DATA LOCAL INFILE est faite par un script ou un applicatif qui lit la variable global [client], comme le .my.cnf d’un utilisateur, il faut ajouter la variable loose-local-infile dans le .my.cnf :

[client]
loose-local-infile=1

Colonne Create Function à nulle lors de l’appel de procédures stockées

Avec Mysql < 8.x, lors de l’utilisation de SHOW CREATE PROCEDURE ou SHOW CREATE FUNCTION, si la colonne Create Function renvoie NULL, il faut accorder à l’utilisateur les droits SELECT sur la table mysql.proc :

GRANT SELECT ON mysql.proc TO user;

Pour MySQL >= 8.x, les droits SELECT globaux sont nécessaires.

Documentation 5.7

Documentation 8.0

Présence de fichier .BAK dans le datadir

Exemple :

/srv/mysql/foobarcorp/jos_vm_product_related_clicks-190605175656.BAK

Ces fichiers .BAK sont crées parceque l’option myisam-recover-options est positionné à BACKUP par défaut (bien que non présent dans my.cnf).

mysql> SHOW GLOBAL VARIABLES LIKE '%recover_options%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| myisam_recover_options | BACKUP |
+------------------------+--------+

Cela signifie que lorsqu’une table est réparé, si le résultat diffère, MySQL fait une backup.
Plus d’informations sur la documentation.

Erreur 1217

MariaDB [(none)]> drop database `db_name`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Pour contourner ce problème on peut faire :

MariaDB [(none)]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> drop database `db_name`;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

Erreur 1005

si l’on rencontre ce type d’erreur :

ERROR 1005 (HY000) at line 13: Can’t create table ma_base.ma_table (errno: 150 “Foreign key constraint is incorrectly formed”)

Pour contourner ce problème on peut faire :

MariaDB [(none)]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

Ne pas oublier de remettre à “1” car sinon il y a risque de réduction de performances.

Erreur SHOW ENGINE INNODB STATUS >> “#1227 - Access denied; you need (at least one of) the PROCESS privilege(s) for this operation”

Il faut rajouter le droit PROCESS à l’utilisateur concerné :

MariaDB > grant USAGE, PROCESS on *.* to foo@localhost;

“incorrect key file” sur une table InnoDB

Si une action ou une réplication est bloquée avec le message incorrect key file et incitant à réparer la table, c’est une fausse piste (il n’y a pas de REPAIR sur les tables InnoDB). Il se peut que ça soit en fait un tmpdir insuffisant en espace disponible pour accueillir les données temporaires. Il faut alors passer sur un tmpdir plus important (/home/mysql-tmpdir par exemple). Attention, cela nécessite un redémarrage du service.

Lister les tables fragmentés

Souvent on execute un mysqltuner et celui ci indique que mysql contient des tables fragmentés, mais sans indiqués lequelles.

Si on veux lister toutes les tables fragmenté d’une instance MySQL, on peux executer cette requête :

SELECT ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

Cela liste les tables qui ont un différentiel entre al longeur des données et la longeur de l’index, et le nombre de données non utilisé dans chaque table (data_free)

On peux égelement calculer le ratio d’espace non utilisé sur une table, exemple avec cette table champ_supp_contact :

+--------+---------------------------+-------------+--------------+-----------+
| ENGINE | TABLE_NAME                | data_length | index_length | data_free |
+--------+---------------------------+-------------+--------------+-----------+
| InnoDB | champ_supp_contact        |          30 |           27 |        29 |
+--------+---------------------------+-------------+--------------+-----------+

On calcule le ratio de cette manière, (30 + 27) = 57 ; 29/57 = 0.5

C’est à dire que la table contient 50% d’espace vide.

Voici une requêtes SQL qui indique également le ratio de fragmentation de chaque tables :

SELECT ENGINE,   concat(TABLE_SCHEMA, '.', TABLE_NAME) as table_name,   round(DATA_LENGTH/1024/1024, 2) as data_length,   round(INDEX_LENGTH/1024/1024, 2) as index_length,   round(DATA_FREE/1024/1024, 2) as data_free,   (data_free/(index_length+data_length)) as frag_ratio FROM information_schema.tables WHERE DATA_FREE > 0 ORDER BY frag_ratio DESC;

Problème pour créer ou modifié des TRIGGER d’une base

Si un utilisateur mysql veut créer ou modifié un TRIGGER, par default il ne peut pas le faire, et mysql retourne l’erreur suivante :

You do not have the SUPER privilege and binary logging is enabled

Pour qu’un utilisateur puisse créer / modifié des TRIGGER, sans avoir le privilège SUPER, et lorsque les binlogs sont activé, il faut activer la variable log_bin_trust_function_creators de manière globale :

+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+

Calculer la taille adéquate pour innodb_buffer_pool_size

On peux calculer la taille adéquate pour la variable innodb_buffer_pool_size sur un serveur mysql, avec la requête suivante :

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM ( SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;

Il ne faut pas oublié d’augmenté également le innodb_buffer_pool_instances si innodb_buffer_pool_size et suppérieur ou égal à 4Gio. On mets une instances par Gigaoctet pour le innodb_buffer_pool_instances.

Le plugin Munin “mysql2” ne graph rien

Dans les logs de Munin /var/log/munin/munin-node.log on voit des erreurs du style Unknown section: INDIVIDUAL BUFFER POOL INFO at /etc/munin/plugins/mysql_tmp_tables line 1098.

Il faut modifier le plugin /usr/share/munin/plugins/mysql_, dans la foncion parse_innodb_status et ajouter dans la %section_map la ligne suivante :

'INDIVIDUAL BUFFER POOL INFO' => \&skip,

On peut vérifier tout de suite le bon fonctionnement avec la commande munin-run mysql_connections ou n’importe quel autre module géré par ce plugin.

Après une relance de munin-node et attente de 10 minutes (2 passage de munin-node en cron), on devrait avoir des valeurs.

Erreur Row Size Too Large avec InnoDB

Si lors de requêtes SQL il y a des erreurs du type, avec les bases / tables qui utilise le moteur InnoDB :

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

C’est qu’il faut convertir la table en format de ligne dynamique (DYNAMIC rox format) comme ceci :

ALTER TABLE `foo` ROW_FORMAT=DYNAMIC;

## Erreur lors de l’importation d’un dump SQL, date 00-00-0000 non acceptée

Cela peut être parce que la variable sql_mode contient les valeurs NO_ZERO_IN_DATE ou NO_ZERO_DATE.

Pour lister les modes activés :

SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+

Documentation : https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

Chercher dans quelle base se trouve un VUE avec un definer précis

Lister la liste de toutes les vues dans un fichier, comme ceci :

# mysql -e "select * FROM INFORMATION_SCHEMA.VIEWS\G" > find_definer.txt

Puis faire une recherche sur le definer dans le fichier généré.

Extraire des logs dans les binlogs par rapport a une date de début et de fin avec mysqlbinlog

Si l’on veux extraire des requêtes des binlogs par rapport a une date de début et de fin, utile pour voir toutes les écritures qui ont été faite sur un serveur a un moment donner, on peux utilisé mysqldump comme ceci :

mysqlbinlog --no-defaults --start-datetime="2021-12-01 15:40:00" --stop-datetime="2021-12-01 16:05:00" mysql-bin.000022 > requêtes.txt

mysqldump : you need the PROCESS privilege

En cas d’erreur du type :

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Un contournement est d’utiliser l’option --no-tablespaces.

D’autres solutions sur https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error/

Listé les Indexs non utilisé, avec la base information_schema

Voici une requêtes SQL qui se base sur la base information_schema et les tables STATISTICS et INDEX_STATISTICS qui liste les Indexes non utilisés.

Attention, le résultat de cette requête n’est pas fiable à 100%, car ça suppose qu’un index est inutilisé que s’il n’a jamais provoqué IO wait. Les clés primaire et les indexs UNIQUE sont exclus :

SELECT st.TABLE_SCHEMA, st.TABLE_NAME, st.INDEX_NAME
FROM information_schema.STATISTICS st
LEFT JOIN information_schema.INDEX_STATISTICS idx
ON  idx.INDEX_NAME    = st.INDEX_NAME
AND idx.TABLE_NAME    = st.TABLE_NAME
AND idx.TABLE_SCHEMA  = st.TABLE_SCHEMA
WHERE
(idx.INDEX_NAME IS NULL OR idx.ROWS_READ = 0)
AND st.NON_UNIQUE = 1
ORDER BY 1, 2, 3
;

Réduire l’impact de mysqldump lors du process de sauvegarde sur la production

Il existe 2 options dans mysqldump qui peuvent réduire l’impact de process de la sauvegarde sur l’instance en production.

• La première option est --single-transaction :

Elle est recommandée pour les bases de données fréquemment mises à jour ou contenant de grandes quantités de données. En effet, cela garantit que la base de données reste dans un état cohérent tout au long de la sauvegarde.

Cette option est compatible seulement avec les moteurs InnoDB et NDB, elle n’est pas compatible avec le moteur MyISAM. Mais le fait d’utilisé cette option sur des tables avec un moteur non compatible, n’auras aucun effet.

• La deuxième option est de combiner --single-transaction avec --skip-lock-tables :

L’utilisation conjointe des options --single-transaction et --skip-lock-tables peut offrir plusieurs avantages :

• L’option --single-transaction garantit une sauvegarde cohérente de votre base de données en encapsulant l’intégralité de l’opération mysqldump dans une seule transaction. Cela garantit que la sauvegarde reflète un état cohérent de votre base de données au moment du début de la transaction.

• L’option --skip-lock-tables évite le verrouillage des tables pendant le processus de sauvegarde, ce qui peut éviter les temps d’arrêt et l’interruption des opérations actives.

Donc, l’utilisation conjointe des deux options fournit une sauvegarde cohérente et à faible impact de l’instance MySQL, ce qui en fait un choix idéal pour les environnements où la réduction des temps d’arrêt et des interruptions est une priorité.

Le fichier ibdata1 prend trop d’espace disque

C’est le tablespace d’InnoDB, il stocke nottament les undo logs des transactions, et les Rollback segment.

Attention : En MariaDB <= à 10.11 la valeur par défaut de la variable innodb_undo_tablespaces est 0, ce qui signifie que dans le fichier ibdata1 les Rollback segment sont stockés ainsi que toutes les pages d’undo logs.

A partir de MariaDB 10.11 la valeur par défaut de innodb_undo_tablespaces est 3, ce qui signifie que les undo logs sont stocké dans 3 fichiers undo001/002/003, mais les Roolback segment ne sont plus stockés, ce qui réduit la quantités de données occupé par ce fichier.

On peux mettre en place une purge de l’historique des undo logs avec la configuration suivante :

innodb_undo_tablespaces = 6
innodb_undo_directory = /srv/mysql_innodb_undo
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 64
innodb_purge_threads = 6

Pour la variable innodb_purge_threads il faut la configuré en fonction du nombre de CPU sur la machine. Une valeurs trop basse peux généré du purge lag.

La variable innodb_purge_rseg_truncate_frequency est déprécié à partir de MariaDB 10.6.16, pour la purge, seulement la variable innodb_undo_log_truncate est utilisé à partir de cette version.

L’activation de innodb_undo_log_truncate=ON doit se faire avec prudence, car la purge peut généré du lag lorsqu’il y a de nombreuses écriture simultanée, voir innodb_purge_threads.

Si ce fichier prend trop d’espace, et que aucune purge n’avais été mis en place, ou que la variable innodb_undo_tablespaces=0, il est nécessaire de sauvegarder les bases de données, de supprimer le datadir MariaDB et de réinjecter les données, ce qui est assez lourd.

Documentation MariaDB : https://mariadb.com/kb/en/innodb-system-tablespaces/

ERROR 1133 (28000) at line N: Can’t find any matching row in the user table

Cela veut peut être dire que vous faites un GRANT sans avoir créé l’utilisateur au préalable (par exemple avec un GRANT USAGE).

Astuce : pensez-vous à faire un FLUSH PRIVILEGES si vous avez réinjecté des données SQL dans la base “mysql”

Configuration du fuseau horaire (timezone)

Pour MariaDB on configure le fuseau horaire temporairement, par exemple pour utiliser UTS, comme ceci :

# mysql -e "SET GLOBAL time_zone = '+00:00';"
# mysql -e "SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;"
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+

Si on veut rendre le changement persistant :

# echo -e '\ndefault_time_zone = '+00:00'' >> /etc/mysql/mariadb.conf.d/zzz-evolinux-custom.cnf