См. также MySQL - tips'n'tricks, коллекция советов

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

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

#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.

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

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;

UPD 1/08/2022

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

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

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/

Помогло

mv /etc/systemd/system/mysql.service.d /etc/systemd/system/mariadb.service.d

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

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

[mysqld]
plugin-load-add = auth_socket.so

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

Или так

use mysql;
update user set plugin="mysql_native_password";
flush privileges;

Или см. #1698 - Access denied for user 'root’@’localhost'

Ошибка 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

mysql soft nofile 10240
mysql hard nofile 40960
mysql soft nproc 10240
mysql hard nproc 40960

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

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

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

ИЛИ

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

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

Ошибка 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, а Plesk

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

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

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

Решение

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

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

• 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

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

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

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

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

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

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

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

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

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

Ошибка похожа на 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.

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

su - mysql -c 'ulimit -aHS' -s '/bin/bash'

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

*    soft    nproc     40960
*    hard    nproc     65535
*    soft    nofile    40960
*    hard    nofile    65535

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

mysql    soft    nofile   40960
mysql    hard    nofile   65535
mysql    soft    nproc    40960
mysql    hard    nproc    65535

UPD

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

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

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

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

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

mysqladmin -u root -p flush-hosts

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

# perror 24
OS error code  24:  Too many open files

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

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

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

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

# lsof -u mysql | wc -l

Ошибка

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

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

В 5.0 было так

default-character-set=utf8

а потом трахбах и deprecated

Теперь так

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 

И опять убунта. Что за чудо система. Не даёт скучать. Сиди чини её нескончаемые баги. Впрочем ничего нового.

Делаем

sudo mysql_upgrade -u root -p --force

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

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

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

sudo mkdir /var/lib/mysql-files

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

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

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

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

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

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

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

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.

Пример

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

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

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

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;)

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

Почему так

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

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

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

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

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

Проверить sql_mode

mysql -e "SELECT @@sql_mode;"

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

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

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

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

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

Ошибка

# /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)'

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

[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

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

# 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;

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

# /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.

С приходом systemd жить стало лучше, жить стало веселее.

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

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

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

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

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

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

# 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

# systemctl --system daemon-reload
# systemctl restart mariadb.service

В логах

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.

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

mv ibdata1 ibdata1.bak 
cp -a ibdata1.bak ibdata1

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

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

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

/var/lib/mysql/data/ r,
/var/lib/mysql/data/** rwk,

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

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

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

Ошибка

#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.

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

thread_stack = 256K

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

# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
FATAL ERROR: Upgrade failed

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

mysql_upgrade -u root -p

Ошибка

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

Решение

mysql_upgrade -S /var/lib/mysql/mysql.sock -uUSERNAME -p

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

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

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

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

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

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

Подробнее на stackoverflow.

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

Коды ошибок strerror на память

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

• http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
• Possible reasons for “mysql server has gone away” error (php 5.4, mysqlnd)

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

max_allowed_packet = 64M

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

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

wait_timeout = 3600

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