====== MySQL - коллекция ошибок и фиксов ======

{{ :linux:mysql:mysql_logo.png?nolink|}}

См. также [[linux:mysql:mysql_tnt|MySQL - tips'n'tricks, коллекция советов]]

===== Ошибки =====

==== ERROR 1045 (28000): Access denied ====

Debian 12, MariaDB 10.11

<code bash>
Nov 15 13:16:10 mail /etc/mysql/debian-start[163667]: Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
Nov 15 13:16:10 mail /etc/mysql/debian-start[163667]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Nov 15 13:16:10 mail /etc/mysql/debian-start[163667]: FATAL ERROR: Upgrade failed
</code>

Временное решение - добавить в файл ''/etc/mysql/debian.cnf'' строку с паролем root                                                                                      

<code bash>
[client]
host     = localhost
user     = root
password = 
[mysql_upgrade]
host     = localhost
user     = root
password = 
</code>

При случае проверить это
  * [[https://serverfault.com/questions/1126740/mysql-error-1045-28000-access-denied-for-user-rootlocalhost-although-pa|]]
  * [[https://stackoverflow.com/questions/76605893/failing-to-successfully-run-mariadb-on-macos-with-brew|]]

==== Foreign key / Внешние ключи / Ошибка #1217 ====

Теория в другом месте. Только фикс.

Текст ошибки может быть разным

<code sql>
#1217 - Cannot delete or update a parent row: a foreign key constraint fails\\
#1217 - Невозможно удалить или обновить родительскую строку: проверка ограничений внешнего ключа не выполняется\\
ERROR 1005 (HY000) at line 30: Can't create table (errno: 150)
Create table %table_name% with foreign key constraint failed.
</code>

В mysql cli отключаем проверку внешних ключей, делаем нужный запрос, включаем обратно.

<code sql>
SET FOREIGN_KEY_CHECKS = 0;
# пример, загрузка таблицы
LOAD DATA INFILE '/path/to/table.txt' INTO TABLE table_name;
# пример, загрузка дампа
SOURCE dump.sql;
# пример, удаление таблицы
drop table if exists users;
# включаем проверку внешних ключей
SET FOREIGN_KEY_CHECKS = 1;
</code>

UPD 1/08/2022

можно ещё проще сделать

<code bash>mysql --login-path=user --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < dump.sql</code>

==== Run 'systemctl daemon-reload' to reload units ====

Warning: The unit file, source configuration file or drop-ins of mariadb.service changed on disk. Run 'systemctl daemon-reload' to reload units.

  * https://www.howtoforge.com/community/threads/warning-mysql-service-changed-on-disk-run-systemctl-daemon-reload-to-reload-units.80161/
  * https://forum.directadmin.com/threads/warning-mysqld-service-changed-on-disk-run-systemctl-daemon-reload-to-reload-units.60196/

Помогло

<code>mv /etc/systemd/system/mysql.service.d /etc/systemd/system/mariadb.service.d</code>

==== Can't init tc log ====

<code>
rm -f /var/lib/mysql/tc.log
mv /var/lib/mysql/tc.log /var/lib/mysql/tc.log_BAK
</code>

==== MySQL “Got an error reading communication packet” errors ====

Не успел разобраться с ошибкой - пропала сама. Оставлю ссылку на будущее https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/

==== #1524 - Plugin 'unix_socket' is not loaded ====

<code>
[mysqld]
plugin-load-add = auth_socket.so
</code>

https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

Или так

<code>
use mysql;
update user set plugin="mysql_native_password";
flush privileges;
</code>

Или см. [[linux:mysql:mysql_errors#access_denied_for_user_root_localhost|#1698 - Access denied for user 'root’@’localhost']]

==== Can't create a new thread (errno 11) ====

Ошибка **Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug**

Как всегда ulimit - https://www.percona.com/blog/2013/02/04/cant_create_thread_errno_11/

TL;DR

Лимиты установленные для MySQL в файле ''/etc/security/limits.conf'' будут переопределены файлом ''/etc/security/limits.d/90-nproc.conf''. Поэтому задавать лимиты нужно в ''90-nproc.conf'' или создать отдельный файл ''91-mysql.conf''

<code>
mysql soft nofile 10240
mysql hard nofile 40960
mysql soft nproc 10240
mysql hard nproc 40960
</code>

==== #1698 - Access denied for user 'root’@’localhost' ====

Не работает phpmyadmin под root'ом. Для MySQL 127.0.0.1 и localhost это разные хосты.

Добавляем отдельного пользователя для администрирования

<code>
CREATE USER 'dxdydz'@'%' IDENTIFIED BY 'p4ssw0rd';
GRANT ALL PRIVILEGES ON *.* TO 'dxdydz'@'%' WITH GRANT OPTION;
</code>

ИЛИ

<code>
# mysql -u root
mysql> use mysql;
mysql> update user set plugin='' where User='root';
mysql> flush privileges;
mysql> exit;
</code>

FIXME что-то там с sudo и unix_socket, не разбирался пока, но вариант рабочий.

==== mysqldump: Couldn't execute 'show events' ====

Ошибка **mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start** после перехода на MariaDB с MySQL 56 на cPanel сервере

mysql_upgrade по рекомендациям тоже не работает с ошибкой **mysqldump: Got error: 1102: Incorrect database name '#mysql50#.config'" when selecting the database**

И мне помог не cPanel, а [[https://support.plesk.com/hc/en-us/articles/115002490914-Upgrade-of-MySQL-schema-with-mysql-upgrade-fails-with-the-error-Error-Incorrect-database-name-mysql50-ssh-|Plesk]]

В ''/var/lib/mysql/'' был каталог с точкой в имени.

Чтобы его найти выполним команду

<code># ls -la /var/lib/mysql| grep ^d | egrep '\.'</code>

**Решение**

Удалить/перенести каталог в другой место, выполнить mysql_upgrade.

==== #1214 - The used table type doesn't support FULLTEXT indexes ====

Индексы FULLTEXT поддерживаются в таблицах InnoDB только начиная с MYSQL 5.6, поэтому попробуйте обновить MYSQL и после этого изменить команду таблицы

==== Waiting for table metadata lock ====

  * https://medium.com/@wkoffel/mysql-rails-and-metadata-locks-a-bug-hunt-bedtime-story-c9c4e14deb36
  * https://lxadm.com/MySQL:_avoiding_%22Waiting_for_table_metadata_lock%22_when_executing_%22ALTER_TABLE%22

==== No directory, logging in with HOME=/ ====

<code>
# /etc/init.d/mysql start
No directory, logging in with HOME=/
</code>

Подобная ошибка была в Debian с репозиторием dotdeb.

Надо поправить ''/etc/passwd''

<code>
# cat /etc/passwd | grep mysql
mysql:x:112:117:MySQL Server,,,:/nonexistent:/bin/false
</code>

Должно быть так

<code>mysql:x:106:110:MySQL Server,,,:/nonexistent:/bin/false</code>

==== Can't create thread to kill server (errno= 11) ====

Скорее всего на сервере недостаточно памяти для выбранных настроек в ''my.cnf''.

Т.е. ''key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections'' в итоге получается больше чем RAM на сервере.

Решение - уменьшить max_connections и другие параметры исходя из доступных ресурсов.

==== Can't create a new thread (errno 11) ====

Ошибка похожа на **Can't create thread to kill server** и также связана с лимитами.

  * https://www.percona.com/blog/2013/02/04/cant_create_thread_errno_11/
  * http://dimitrik.free.fr/blog/archives/2010/11/mysql-performance-hitting-error-cant-create-a-new-thread-errno-11-on-a-high-number-of-connections.html

В данном случае нужно увеличить количество открытых файлов и количество процессов (''nofile'' и ''nproc'').

По-умолчанию ''open files'' равен 1024.

Проверим, чтобы удостовериться

<code>su - mysql -c 'ulimit -aHS' -s '/bin/bash'</code>

Добавляем в файл ''/etc/security/limits.conf''

<code>
*    soft    nproc     40960
*    hard    nproc     65535
*    soft    nofile    40960
*    hard    nofile    65535
</code>

Либо устанавливаем лимит только для mysql

<code>
mysql    soft    nofile   40960
mysql    hard    nofile   65535
mysql    soft    nproc    40960
mysql    hard    nproc    65535
</code>

UPD

Нашёл рекомендацию добавить лимиты в отдельный файл ''99-mysql.conf'' в каталоге ''/etc/security/limits.d/''

==== unknown variable 'default-tmp-storage-engine=MyISAM' ====

Вот такая ошибка может возникнуть если бездумно копировать из разных блогов советы бывалых админов

''default-tmp-storage-engine'' появился только в MySQL 5.6 и если использовать опцию в версии 5.5, то MySQL не запустится.

http://www.chriscalender.com/tag/default-tmp-storage-enginemyisam/

==== Host 'a.b.c.d' is blocked because of many connection errors; unblock with 'myscladmin flush-hosts' ====

Ошибка возникает после 10 (по-умолчанию) неудачных соединений с базой.

<code>mysqladmin -u root -p flush-hosts</code>

Подробнее в документации MySQL - https://dev.mysql.com/doc/refman/5.7/en/blocked-host.html

==== Fatal error: Uncaught exception 'Exception' with message 'Error: Can't open file: './ocr/oc_product.frm' (errno: 24) ====

<code>
# perror 24
OS error code  24:  Too many open files
</code>

В логе ''mariadb.log'' нечто подобное

<code>
170710 10:36:01 [ERROR] mysqld: Can't open file: './oc2_test/oc_user.frm' (errno: 24)
170710 10:36:01 [ERROR] mysqld: Can't open file: './oc2_test/oc_user_group.frm' (errno: 24)
170710 10:44:05 [ERROR] Error in accept: Too many open files
</code>

Решение - см. запись ниже //open-files-limit в MariaDB//

Текущее использование открытых файлов можно посмотреть так:

<code># lsof -u mysql | wc -l</code>

==== InnoDB: mmap(137363456 bytes) failed; errno 12 ====

Ошибка

<code>
InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
InnoDB: Completed initialization of buffer pool
InnoDB: Fatal error: cannot allocate memory for the buffer pool
</code>

Решение - уменьшить ''innodb_buffer_pool_size'' или добавить RAM.

==== Правильный UTF-8 ====

В 5.0 было так

<code>default-character-set=utf8</code> а потом трахбах и deprecated

Теперь так

<code>
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake 
</code>

==== #1146 - Table 'data_dictionary.CHARACTER_SETS' doesn't exist ====

И [[https://askubuntu.com/questions/756908/mysql-database-browsers-not-working-after-upgrade|опять убунта]]. Что за чудо система. Не даёт скучать. Сиди чини её нескончаемые баги. Впрочем ничего нового.

Делаем

<code>sudo mysql_upgrade -u root -p --force</code>

И всё начинает работать. До следующего адового бага. Продакшен реди итиху мать.

==== /usr/sbin/mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2) ====

Баг после апгрейда встретился только в Ubuntu

ЕМНИП нужно просто создать каталог /var/lib/mysql-files

<code>sudo mkdir /var/lib/mysql-files</code>

==== 'ERROR 1214 (HY000) at line 784: The used table type doesn't support FULLTEXT indexes ' ====

FULLTEXT INDEX раньше работал только с MyISAM. С версии 5.6 доступен в InnoDB.

Так что либо апгрейд либо ALTER TABLE `yourtable` ENGINE = MyISAM; FIXME

==== Got an error from unknown thread, /builddir/build/BUILD /storage/myisam/mi_write.c:226 ====

Также в логах может быть что-то вроде **Incorrect key file for table 'xyz.MYI'; try to repair it**

Казалось бы следует сделать ''mysqlrepair --auto-repair''. Но обычно это не помогает.

Скорее всего нет инодов или кончилось место или недоступен tmpdir в mysql.

Проверяем ''df -i'' и ''df -h''. Также проверяем значение ''tmpdir'' в ''my.cnf''

==== mysqldump: Got error: (Errcode: 24) when using LOCK TABLES ====

Узнал о крутой утилите **perror**. По коду ошибки покажет, что не так.

<code>
Print a description for a system error code or a MySQL error code.
If you want to get the error for a negative error code, you should use
-- before the first error code to tell perror that there was no more options.
</code>

Пример

<code>
# perror -v 24
OS error code  24:  Too many open files
</code>

Ну и по ошибке выше - попробуйте добавить опцию ''<nowiki>--</nowiki>single-transaction'' к mysqldump

==== Error Number: 1364 ====

Через tcpdump выловил ошибку в php-fpm

<code>
MySQL Error!
MySQL error in file: /engine/modules/tracker/upload.php at line 0
Error Number: <b>1364</b></div>
The Error returned was: Field &#039;author2&#039; doesn&#039;t have a default value
SQL query: INSERT INTO dle_files (news_id, author) values (&#039;0&#039;, &#039;dx&#039;)
</code>

Виной всему старый код и новый (5.7) MySQL.

Почему так

  * при вставке или обновлении данных у поля нет значения по-умолчанию
  * [[http://ru.stackoverflow.com/questions/119731/ошибка-field-us-id-doesnt-have-a-default-value|http://ru.stackoverflow.com/questions/119731/ошибка-field-us-id-doesnt-have-a-default-value]]

Быстрый фикс - выключить так называемый **strict mode** 

Для этого нужно добавить в ''my.cnf''

<code>
[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
</code>

Можно также вынести в отдельный файл ''/etc/mysql/conf.d/disable_strict_mode.cnf''

Проверить **sql_mode**

<code>mysql -e "SELECT @@sql_mode;"</code>

==== #1030 - Got error -1 from storage engine ====

При попытке выполнить SQL запрос в phpmyadmin получаем ошибку **#1030 - Got error -1 from storage engine**

Вероятно включен ''innodb_force_recovery'' в файле ''my.cnf''.

Проверяем логи. Если есть нечто подобное

<code>InnoDB: innodb_force_recovery is on: we do not allow InnoDB: database modifications by the user. Shut down</code>

то значит так оно и есть. Выключаем ''innodb_force_recovery'' и всё снова работает.

==== error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)' ====

Ошибка

<code>
# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [fail]
* Starting MySQL database server mysqld [ OK ]
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
</code>

Смотрим пароль пользователя ''debian-sys-maint'' в файле ''/etc/mysql/debian.cnf''

<code>
[client]
host     = localhost
user     = debian-sys-maint
password = A1b2C3d4E5f6
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = A1b2C3d4E5f6
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr
</code>

Выполняем 2 SQL запроса, чтобы вернуть гражданину debian-sys-maint его привилегии

<code>
# mysql -u root -p
mysql> use mysql;
mysql> GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'A1b2C3d4E5f6';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'A1b2C3d4E5f6' WITH GRANT OPTION;
</code>

Перезапускаем MySQL сервер

<code>
# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
</code>

==== open-files-limit в MariaDB ====

С приходом systemd [[https://ru.wikipedia.org/wiki/Жить_стало_лучше,_жить_стало_веселее|жить стало лучше, жить стало веселее]].

Systemd самостоятельно контролирует, сколько файлов служба (в нашем случае mariadb-server) может открыть, независимо от того, что вы настроили в ''/etc/my.cnf'' или в ''/etc/security/limits.conf''.

Создаём каталог

<code># mkdir -p /etc/systemd/system/mariadb.service.d/</code>

И вносим следующие правки

<code>
# cat /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=10240
</code>

Данные новшества однако документированы. Так что надо просто внимательнее читать release notes и changelog.

<code>
# cat /usr/lib/systemd/system/mariadb.service

# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
#	.include /lib/systemd/system/mariadb.service
#	...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mariadb's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
#	[Service]
#	LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload
</code>

<code>
# systemctl --system daemon-reload
# systemctl restart mariadb.service
</code>

==== Unable to lock ./ibdata1, error: 11 ====

В логах

<code>
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
</code>

Решение в сети, которое якобы некоторым помогает

<code>
mv ibdata1 ibdata1.bak 
cp -a ibdata1.bak ibdata1
</code>

увы не помогает.

Бытует мнение, что виной всему Apparmor т.к. нигде кроме Ubuntu ошибка эта не встречалась

Можно попробовать добавить в ''/etc/apparmor.d/usr.sbin.mysqld''

<code>
/var/lib/mysql/data/ r,
/var/lib/mysql/data/** rwk,
</code>

FIXME надо проверить

==== unknown option '--skip-locking' ====

Опцию ''--skip-locking'' убрали в MySQL 5.5.

Решение: заменить ''skip-locking'' на ''skip-external-locking''

==== Thread stack overrun ====

Ошибка

<code>
#1436 - Thread stack overrun: 8240 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
</code>

Добавляем в секцию [mysqld]

<code>thread_stack = 256K</code>

==== Не работает mysql_upgrade ====

=== 5.1 > 5.5 ===

http://serverfault.com/questions/527422/mysql-upgrade-is-failing-with-no-real-reason-given

<code>
# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed
</code>

Что делать? Указать юзера и пароль

<code>mysql_upgrade -u root -p</code>

=== 5.5 > 5.6 ===

Ошибка

<code>
root@mx:/etc/mysql# mysql_upgrade -u root -p
Enter password: 
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Error: Failed while fetching Server version! Could be due to unauthorized access.
FATAL ERROR: Upgrade failed
</code>

Решение

<code>mysql_upgrade -S /var/lib/mysql/mysql.sock -uUSERNAME -p</code>

==== #23 - Out of resources when opening file ====

{{:linux:mysql:mysql_23.png?nolink&800|}}

#23 - Out of resources when opening file Errcode: 24 - Too many open files

==== #1273 - unknown collation ====

Ошибка **error #1273 - Unknown collation: 'utf8mb4_unicode_ci'**

{{:linux:mysql:mysql_1273.png?nolink|}}

Почему такое произошло? Вероятно вы пытаетесь использовать дамп базы с более новой версии MySQL сервера где уже есть поддержка utf8mb4.

Вероятно вы уже видели в сети совет - открыть дамп через каким-нибудь Notepad++ и заменить все ''utf8mb4_unicode_ci'' на ''utf8_general_ci''

Так делать НЕ НАДО!

Вариантов много - обновить MySQL (в версиях выше 5.5.3 такой проблемы нет) или использовать совместимость со старыми версиями MySQL при дампе (**mysqldump --compatible=mysql4**).

[[http://stackoverflow.com/questions/29916610/1273-unknown-collation-utf8mb4-unicode-ci-cpanel|Подробнее на stackoverflow]].

==== Got error 28 from storage engine ====

Скорее всего у вас просто закончилось свободное место на диске - **strerror(28) = No space left on device**

Коды ошибок [[https://www.opennet.ru/man.shtml?topic=strerror&category=3&russian=0|strerror]] на память

<code>
strerror(0) = Success
strerror(1) = Operation not permitted
strerror(2) = No such file or directory
strerror(3) = No such process
strerror(4) = Interrupted system call
strerror(5) = Input/output error
strerror(6) = No such device or address
strerror(7) = Argument list too long
strerror(8) = Exec format error
strerror(9) = Bad file descriptor
strerror(10) = No child processes
strerror(11) = Resource temporarily unavailable
strerror(12) = Cannot allocate memory
strerror(13) = Permission denied
strerror(14) = Bad address
strerror(15) = Block device required
strerror(16) = Device or resource busy
strerror(17) = File exists
strerror(18) = Invalid cross-device link
strerror(19) = No such device
strerror(20) = Not a directory
strerror(21) = Is a directory
strerror(22) = Invalid argument
strerror(23) = Too many open files in system
strerror(24) = Too many open files
strerror(25) = Inappropriate ioctl for device
strerror(26) = Text file busy
strerror(27) = File too large
strerror(28) = No space left on device
strerror(29) = Illegal seek
strerror(30) = Read-only file system
strerror(31) = Too many links
strerror(32) = Broken pipe
strerror(33) = Numerical argument out of domain
strerror(34) = Numerical result out of range
strerror(35) = Resource deadlock avoided
strerror(36) = File name too long
strerror(37) = No locks available
strerror(38) = Function not implemented
</code>

==== ERROR 2006 (HY000) - MySQL server has gone away ====

  * http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
  * [[http://stackoverflow.com/questions/24895645/possible-reasons-for-mysql-server-has-gone-away-error-php-5-4-mysqlnd|Possible reasons for “mysql server has gone away” error (php 5.4, mysqlnd)]]

Добавить в секцию ''[mysqld]''

<code>max_allowed_packet = 64M</code>

Значение по-умолчанию = 1М

Скорее всего поможет. Если нет, то также в секцию ''[mysqld]''

<code>wait_timeout = 3600</code>

Значение по-умолчанию - 300 FIXME проверить