Ошибка 1040 как исправить

Время на прочтение
4 мин

Количество просмотров 16K

И снова ERROR 1040…

Техподдержка получает много жалоб на эту печально известную ошибку: ERROR 1040: Too many connections — слишком много соединений. Проблема очевидна: приложение или пользователи создают больше соединений, чем допускает сервер, то есть текущее число соединений превышает значение переменной max_connections.

Ситуация уже сама по себе проблема для конечных пользователей, но если еще при этом у вас нет доступа к серверу для диагностики и исправления причины, все становится совсем плохо. Обычно приходится завершать экземпляр и перезапускать его, чтобы восстановить.

Root-пользователь тоже не может подключиться! Почему?!

В правильно настроенной среде пользователь с привилегией SUPER сможет получить доступ к экземпляру и диагностировать причину ошибки 1040, из-за которой не хватает соединений. Это описано в руководстве:

mysqld разрешает max_connections + 1 клиентских соединений. Дополнительное соединение зарезервировано для аккаунтов с привилегиями SUPER. Когда эти привилегии предоставляются администраторам, а не обычным пользователям (которым они и не нужны), администратор, у которого есть еще и привилегия PROCESS, может подключиться к серверу и использовать SHOW PROCESSLIST, чтобы диагностировать проблемы, даже если подключено максимальное число клиентов без привилегий.

Но куча людей дают привилегии SUPER своим пользователям приложения или скрипта — из-за требований приложения (опасно!) или незнания последствий, а потом зарезервированное соединение занимает обычный пользователь, а административный пользователь (обычно root) не может подключиться.

Как гарантировать доступ к экземпляру

Можно использовать хорошо известный хак с GDB, который советовал Ауримас лет 100 назад для ошибки 1040, но теперь есть решения получше. Правда сначала их надо включить.
С Percona Server 5.5.29 и выше и MySQL 8.0.14 и выше можно настроить еще один порт с дополнительным числом соединений. Приложение не будет использовать эти интерфейсы. Они только для администраторов баз данных и агентов мониторинга и проверки работоспособности (см. примечание ниже).

Настройка Percona Server

Начиная с Percona Server 5.5.29 можно просто добавить extra_port в my.cnf, и при следующем перезапуске порт будет доступен и будет ожидать данные по тому же bind_address, что и обычные соединения. Если не настроить переменную extra_port, дополнительного порта по умолчанию не будет.

Еще можно определить extra_max_connections, чтобы задать количество подключений, которое будет обрабатывать этот порт. Количество по умолчанию — 1.

Для примера я занял все подключения к порту обычных пользователей у экземпляра, где уже настроил extra_port и extra_max_connections в my.cnf:

результат

Кстати, extra_port удален в Percona Server 8.0.14 и выше, поскольку в MySQL Community реализован admin_port с теми же функциями. Так что отредактируйте my.cnf при апгрейде до Percona Server 8.0.14 или выше, если вы уже определили extra_port.

Как я уже сказал, для этого нужен MySQL 8.0.14, где применен WorkLog 12138.

Чтобы включить админский интерфейс, нужно определить admin_addres, который должен быть единственным и уникальным (без подстановочных символов) IPv4, IPv6, IPv4-сопоставленным адресом или именем хоста, по которому админский интерфейс будет ожидать передачи данных. Если эта переменная не определена, интерфейс не включен.

Еще можно определить порт, но это не обязательно. По умолчанию это порт 33062. Если этот порт свободен, это значение не нужно настраивать. Если настраиваете, то поместите обе переменные в раздел [mysqld] в my.cnf.

Наконец, можно настроить create_admin_listener_thread (отключено по умолчанию), который создает отдельный поток для обработки входящих соединений. Это может пригодиться в некоторых ситуациях.

Еще одно различие — в документации Oracle сказано, что:

Число административных соединений не ограничено.

(А у нас значение по умолчанию — 1). Не уверен, что это значит, но я бы был осторожен, чтобы случайно не установить 1 млн соединений. Они, конечно, не ограничены, но ресурсы-то все равно потребляют.

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

Удобно, что не только люди могут использовать дополнительный интерфейс или порт в экстренной ситуации, когда мы достигли max_connections. К нему может подключиться система мониторинга и проверки работоспособности прокси/балансировщика нагрузки/обнаружения сервисов.

Скрипты мониторинга смогут извлекать данные для диаграмм, чтобы потом вы разобрались, откуда столько соединений. А скрипты проверки работоспособности будут докладывать об ухудшившемся состоянии сервера, и определенный код может указывать, что соединений много, но сервер справляется (то есть может разобраться сам и лучше чуть дольше подождать до отработки отказа).

Обязательно устанавливайте только по одному соединению за раз для мониторинга и проверки работоспособности, чтобы не забивать extra_max_connections в Percona Server и не создать миллион потоков в MySQL. То есть скрипты не должны подключаться снова, если предыдущий запрос или подключение к базе данных еще активны.

Вот тот же пример, но с MySQL.

Для Percona Server 8.0.14 и выше процесс будет тем же, что и для MySQL Community.

Помогите! Мне нужно войти, но все порты заняты!

Если это та самая причина, по которой вы читаете этот пост, используйте безумный хак с GDB (без обид, Ауримас, просто выглядит рисково :-D) или завершите экземпляр. К счастью, экземпляр почти всегда можно аккуратно завершить с помощью SIGTERM (-15) вместо SIGKILL (-9). Так сервер выполнит чистую остановку, и у потоков будет шанс нормально завершить работу. Просто следуйте инструкциям:

1) Получите PID:

marcos.albe in ~/ pgrep -x mysqld;
650

2) Отправьте SIGTERM в этот PID:

marcos.albe in ~/ kill -15 650;

3) Следите в журнале ошибок, как выполняется завершение работы. Это будет выглядеть примерно так:

2019-07-11T13:43:28.421244Z 0 [Note] Giving 0 client threads a chance to die gracefully
2019-07-11T13:43:28.521238Z 0 [Note] Shutting down slave threads
2019-07-11T13:43:28.521272Z 0 [Note] Forcefully disconnecting 0 remaining clients

Это означает начало процесса завершения работы. Экземпляр будет завершен, когда вы увидите подобную строку:

2019-07-11T13:43:31.292836Z 0 [Note] /opt/percona_server/5.7.26/bin/mysqld: Shutdown complete

Error 1040 Too many connections — ошибка, которая возникает при превышении максимального количества возможных подключений к серверу баз данных со стороны скриптов сайта.

Устраняется, как правило, увеличением лимита в конфигурации сервера.

Обычно превышением количества соединений проявляется как прекративший работать сайт. Ошибку можно увидеть попробовав подключиться к базе данных с реквизитами пользователя, от имени которого работает сайт.

mysql -u USERNAME -p

Enter password:

ERROR 1040 (08004): Too many connections

От имени суперпользователя подключиться удастся в случае если сайт работает не от root.

mysql -u root -p

Лимит существует для пользователя, если сайт работает от root (что именно из-за этого является плохой практикой) и появилась такая ошибка — требуется перезапуск службы MySQL через systemctl restart mysql.

Если удалось зайти в консоль MySQL проверяем актуальное значение директивы max_user_connection. В данном случае нас интересует прежде всего оно.

show status like ‘%connected%’;

+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 151 |
+-------------------+-------+
1 row in set (0.01 sec)

А также действующий лимит

show global variables like ‘%connections%’;

+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_connections | 151 |
| max_user_connections | 0 |
+----------------------+-------+
2 rows in set (0.00 sec)

Лимит достигнут, скрипты при этом продолжают делать запросы. Исправить ситуацию можно добавив в /etc/mysql/my.cnf такую строку

max_user_connection=500

Параметр может задавать не только в /etc/mysql/my.cnf, но и в любом файле, который подключается в /etc/mysql/my.cnf. Главное блок, в который добавляются настройки, он должен определяться директивой [mysqld].

Пример приведен на скриншоте

Затем перезапустив MySQL

systemctl restart mysql

Без перезапуска того же результата можно добиться установив новое значение глобальной переменной, от имени root в консоли MySQL это делается таким образом:

set global max_connections = 500;

Значение будет в силе пока работает процесс MySQL — до следующего перезапуска. Установку глобальной переменной в консоли можно использовать как временное решение. На постоянно изменить значение можно только в конфигурационном файле с последующим перезапуском службы.

Лимит теперь увеличен и сайт должен вновь стать доступен.

Часто причиной является большая активность аудитории и длительные запросы на изменение данных. Они протекают с блокировками, если используются таблицы типа MyISAM получить значительное улучшение можно конвертировав их в InnoDB.

Читайте также про оптимизацию настроек MySQL.

This issue occurs mostly when the maximum allowed concurrent connections to MySQL has exceeded.
The max connections allowed is stored in the gloobal variable max_connections.
You can check it by show global variables like max_connections; in MySQL

You can fix it by the following steps:

Step1:

Login to MySQL and run this command: SET GLOBAL max_connections = 100;

Now login to MySQL, the too many connection error fixed. This method does not require server restart.

Step2:

Using the above step1 you can resolve ths issue but max_connections will roll back to its default value when mysql is restarted.

In order to make the max_connection value permanent, update the my.cnf file.

Stop the MySQL server: Service mysql stop

Edit the configuration file my.cnf: vi /etc/mysql/my.cnf

Find the variable max_connections under mysqld section.

[mysql]

max_connections = 300

Set into higher value and save the file.

Start the server: Service mysql start

Note:
Before increasing the max_connections variable value, make sure that, the server has adequate memory for new requests and connections.

MySQL pre-allocate memory for each connections and de-allocate only when the connection get closed. When new connections are querying, system should have enough resources such memory, network and computation power to satisfy the user requests.

Also, you should consider increasing the open tables limit in MySQL server to accommodate the additional request. And finally. it is very important to close the connections which are completed transaction on the server.

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

• Недоступность базы данных
• Повреждены таблицы БД (Table is marked as crashed)
• Ошибка 2006: MySQL server has gone away
• Ошибка 1040: Too many connections
• Ошибка 1292: Incorrect date value

Недоступность базы данных

Необходимо подключиться к серверу по SSH и выполнить следующие проверки:

1. Проверить, запущена ли служба MySQL:

service mysql status

Пример вывода для запущенной службы:

Если в выводе отсутствует слово running, служба не запущена. В этом случае необходимо попытаться ее запустить:

service mysql start

После этого надо проверить работу сайта и сделать следующую проверку, если ошибка сохраняется.

2. Проверить состояние дискового пространства.

Просмотреть общий и занятый объем на диске командой:

df -h

Доступное пространство должно быть на основном разделе. Если свободное пространство закончилось, необходимо освободить место или перейти на тариф выше. Для работы с дисковым пространством можно использовать утилиты ncdu или du.

Если на диске достаточно свободного места, но ошибка сохраняется, надо проверить состояние inodes.

Если не удается решить ошибку самостоятельно, то нужно обратиться в техническую поддержку.

Повреждены таблицы БД (Table is marked as crashed)

При возникновении ошибок вида “Warning: Table … is marked as crashed” необходимо выполнить восстановление таблиц.

Если на сервере установлен phpMyAdmin, можно выполнить восстановление с его помощью. Для этого необходимо:

1. перейти в интерфейс PMA,
2. выбрать нужную базу данных в меню слева,
3. отметить в списке таблицы, которые нужно восстановить – то есть таблицы, имена которых фигурируют в ошибках,
4. в самом низу страницы нажать на выпадающее меню “С отмеченными” и выбрать вариант “Восстановить”.

Без phpMyAdmin можно выполнить необходимые действия при подключении по SSH. Для восстановления одной таблицы нужно выполнить команду:

mysqlcheck -r имя_базы имя_таблицы -uroot -p

Для восстановления всех таблиц в базе используется команда:

mysqlcheck -r имя_базы -uroot -p

Также можно выполнить проверку всех таблиц в базе с помощью команды:

mysqlcheck -r -A -uroot -p

Ошибка 2006: MySQL server has gone away

Ошибка MySQL server has gone away означает, что сервер закрыл соединение. Это происходит, как правило, в двух случаях: превышение таймаута ожидания или получение сервером слишком большого пакета.

В обоих случаях для устранения ошибки потребуется внести правки в конфигурационный файл MySQL. Это делается при подключении к серверу по SSH или с помощью веб-консоли в панели управления.

Конфигурационный файл может располагаться по различным путям, например:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

Чтобы определить, в какой файл необходимо вносить изменения, можно использовать команду вида:

grep -Rl ‘имя_параметра’ /etc/*

Например:
grep -Rl ‘wait_timeout’ /etc/*

или:
grep -Rl ‘max_allowed_packet’ /etc/*

С ее помощью можно выяснить, в каких файлах прописан нужный параметр, и изменить в них его значение.

Таймаут

Чтобы увеличить таймаут ожидания, необходимо скорректировать значение параметра wait_timeout

Нужно открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

Далее нужно изменить значение параметра wait_timeout на более высокое. Значение указывается в секундах: чтобы увеличить время ожидания до 10 минут, необходимо указать значение 600:

wait_timeout = 600

После перезапустить службу MySQL:

service mysql restart

Размер пакетов

Скорректировать максимально допустимый размер пакетов можно увеличением параметра max_allowed_packet.

Нужно открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

Дале нужно изменить значение параметра max_allowed_packet на более высокое (значение указывается в мегабайтах):

max_allowed_packet = 64M

После перезапустить службу MySQL:

service mysql restart

Ошибка 1040: Too many connections

Ошибка “Too many connections” означает, что исчерпан лимит подключений к базе данных. Ошибка связана с медленными запросами, которые выполняются слишком долго (в этом случае требуется оптимизация кода) либо в числе одновременных подключений. В этом случае можно попробовать решить проблему увеличением лимита подключений (параметр max_connections) в конфигурационном файле MySQL.

В пункте выше было описано, как определить расположение файла my.cnf.

Следует открыть конфигурационный файл с помощью редактора, обязательно указав корректный путь к файлу:

nano /etc/mysql/my.cnf

И заменить значение параметра на более высокое, например:

max_connections = 200

После перезапустить службу MySQL:

service mysql restart

Ошибка 1292: Incorrect date value

При попытке добавить данные в таблицу MySQL без указания даты может выдаваться ошибка:

ERROR 1292 (22007): Incorrect date value: ‘0000-00-00’ for column ‘columnname’ at row 1

Из-за этой ошибки может нарушаться работа импорта в 1С.

Для исправления ошибки необходимо:

1.Открыть файл /etc/mysql/my.cnf:

nano /etc/mysql/my.cnf

2. В строке, начинающейся с sql-mode=, удалить следующие значения:

NO_ZERO_IN_DATE

NO_ZERO_DATE

STRICT_ALL_TABLES

3. Выполнить перезагрузку mysql-сервера:

sudo service mysql restart

Примечание:

Если строка вида sql-mode= отсутствует, необходимо:

1. В файл /etc/mysql/my.cnf после параметра [mysqld] добавить строку:

sql-mode=”ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

2. Выполнить перезагрузку mysql-сервера:

sudo service mysql restart
 

---