При работе с базами данных могут встречаться ошибки. Ниже перечислены частые ошибки и меры по их диагностике и устранению.
- Недоступность базы данных
- Повреждены таблицы БД (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, можно выполнить восстановление с его помощью. Для этого необходимо:
- перейти в интерфейс PMA,
- выбрать нужную базу данных в меню слева,
- отметить в списке таблицы, которые нужно восстановить — то есть таблицы, имена которых фигурируют в ошибках,
- в самом низу страницы нажать на выпадающее меню «С отмеченными» и выбрать вариант «Восстановить».
Без 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
The MySQL Incorrect datetime value error (which is also known as ERROR 1292) is triggered when you perform an INSERT statement that contains one or more DATETIME values with the wrong format.
MySQL only accepts DATETIME values in the format of YYYY-MM-DD hh:mm:ss for string type or YYYYMMDDhhmmss for integer type.
To show you an example, suppose you have a table with DATE and DATETIME columns as shown below:
+-------------+--------------+------+
| Field | Type | Null |
+-------------+--------------+------+
| id | int unsigned | NO |
| join_date | date | YES |
| last_update | datetime | YES |
+-------------+--------------+------+
Now suppose you want to INSERT a new value to the last_update column with the following statement:
INSERT INTO example (last_update) values("10-17-2021 15:40:10");
MySQL will throw an error as shown below:
ERROR 1292 (22007): Incorrect datetime value: '10-17-2021 15:40:10'
for column 'last_update' at row 1
This is because the DATETIME value in the statement above uses the DD-MM-YYYY HH:MM:SS format, which is unacceptable by MySQL.
The obvious way to fix the error is to change the formatting of your value into the format that MySQL can accept.
But rather than editing the value manually, you can use the STR_TO_DATE() function to help you convert the string value into date value.
Here’s an example of STR_TO_DATE() function in action:
SELECT STR_TO_DATE("10-17-2021 15:40:10", "%m-%d-%Y %H:%i:%s");
-- 2021-10-17 15:40:10
The STR_TO_DATE() function requires two arguments to run:
- The datetime string that you want to convert
- The format of the datetime string you pass to the function
Because there are many valid datetime formats in the world, it’s impossible for MySQL to guess what format the string value you passed to the function.
The STR_TO_DATE() function uses the same specifiers as the DATE_FORMAT() function that you can see here.
With the STR_TO_DATE() function, the previous INSERT statement won’t cause an error:
INSERT INTO example (last_update)
values(STR_TO_DATE("10-17-2021 15:40:10", "%m-%d-%Y %H:%i:%s"));
And that’s how you can fix the error Incorrect datetime value in MySQL.
Keep in mind that the error can also be triggered when you try to insert a DATE value to a DATE type column as follows:
mysql> INSERT INTO example (join_date) values("10-17-2021");
ERROR 1292 (22007): Incorrect date value: '10-17-2021'
for column 'join_date' at row 1
The error will say Incorrect date value instead of Incorrect datetime value, but they are both the same error.
You can use STR_TO_DATE() to format your date value as shown below:
INSERT INTO example (join_date)
values(STR_TO_DATE("10-17-2021", "%m-%d-%Y"));
Just remember that you need to pass the right format to the STR_TO_DATE() function, or MySQL won’t be able to process your value.
In another example, if you’re using the forward slash / as the separator for your date value, then you need to use the same separator for the format parameter:
INSERT INTO example (join_date)
values(STR_TO_DATE("10/17/2021", "%m/%d/%Y"));
When you have many values that you want to insert to your table, using STR_TO_DATE function will save you from having to edit your values format manually.
But if you only want to insert a single value, then formatting your value manually might be faster.
Now you’ve learned how to fix the invalid datetime value error in MySQL database server. Great job! 👍
MySQL error 1292 occurs if the syntax for the date is incorrectly entered.
Here at Bobcares, we have seen several causes for this error while troubleshooting MySQL issues as part of our Server Management Services for web hosts and online service providers.
Today we’ll take a look at the cause for this error and how to fix it.
Why does MySQL Error 1292 occur
Before we get into the solution part, let us first see what causes this error to occur.
This error normally occurs when the date is entered in an incorrect format. The date value like 0000-00-00 00:00:00 is not allowed with MySQL 5.7 version.
Also, this error can occur when trying to compare a number and a string in a WHERE or ON clause.
For instance, the error appears as below.
How we fix MySQL Error 1292
This error is of different types and can occur due to many reasons and also the solution will differ according to the error. Here are the different errors and the solutions that our Engineers provide to our customers.
1. If a field type is a DATE, then we make sure that the date is entered in the format “yyyy-mm-dd”.
2. Error Code: 1292 – Incorrect date value
Many of our customers use MySQL 5.7. But in this version date value like 0000-00-00 00:00:00 is not allowed. Hence, the above error occurs. In case, if our customers want to allow it, then we update their my.cnf like:
sudo nano /etc/mysql/my.cnfIn this file, we find
[mysqld]Then after that, we add the below line.
sql_mode=”NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”After adding the above line, we restart the MySQL service. For that, we run the below command.
sudo service mysql restart3. #1292 – Truncated incorrect DOUBLE value
Usually, this error message appears when customers try to compare a number and a string in a WHERE or ON clause.
So we make sure that they have similar declarations or convert the number to a string. Also, if we turn off strict mode, the error turns into a warning.
[Need any further assistance in fixing MySQL errors? – We’re available 24*7]
Conclusion
In short, this error can arise with different messages and has its own way to fix it. Today, we saw the resolution to this MySQL error.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
In some settings MySQL / MariaDB treats empty or null ‘datetime’ and ‘timestamp’ fields/columns as simply null, in others it writes out zeros (0s) like 0000-00-00 00:00:00. If you designed your own database, you probably know what you’re dealing with, already. But if you are working on someone else’s database, like migrating data to or from, or just doing development for it, for example, you may encounter the error “Incorrect datetime value: ‘0000-00-00 00:00:00’ for column …”.
This error can become a bit of headache if a sync .sql script or dump file you need to run now and then or on a regular basis contains datetime values filled with zeros instead of null, or if you are like me and using a tool like Navicat to transfer or synchronize entire databases between two servers, you are prone to encounter this error – especially if you have no control on the settings of the remote database server such as that of a shared hosting. The root cause of the error is simply the executed SQL command does not conform the “SQL mode” of the server in dealing with empty date, date time or timestamp values.
While the section 5.1.10 Server SQL Modes section of the MySQL reference manual published at https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date explains what these SQL modes are and how they work, it may at first site look or sound a bit complicated in order to solve simply a date(time) or timestamp value such as ‘0000-00-00 00:00:00’ existing as actual data in one instance of a MySQL / MariaDb can’t simply be put in another instance.
A typical MySQL installation comes with a default mode denoted by MySQL’s ‘@@sqlmode’ notation which is queryable via an SQL command like below:
SELECT @@sql_mode;
and when run, it returns a result such as
@@sql_mode ---------------------------------------------------- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
I have marked the related mode values in bold, above. In such a case as the above setting which has NO_ZERO_DATE and/or NO_ZERO_IN_DATE, a query like the following produces the error (coded 1292) Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘blah_blah’ :
INSERT INTO users (id, email, username, created, modified) VALUES (123, 'rafinyade5a@mail.ru', 'Hermanzef', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
Error produced:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
Temporary, Case-basis Solution :
Add or run the SQL command SET sql_mode = ”; before your INSERT or UPDATE QUERY which contains such date or datetime values as ‘0000-00-00 00:00:00’.
If you need the other settings such as ERROR_FOR_DIVISION_BY_ZERO or NO_ENGINE_SUBSTITUTION and only want to allow zero-filled date / datetime values then you can use something like the following:
SET sql_mode='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Permanent Solution :
If this is something you will encounter more often than once or twice, such as regular database synchronizations with another host or a dumped SQL file to be used more than once, then you may want to set the above SQL mode permanently to avoid the incorrect datetime value error.
In that case, simply edit your mysql.cnf options file, find the section starting with “[mysql]” (without quotes), and then after the [mysql] line at add following:
[mysqld] sql_mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
If your MySQL instance is in MAMP Pro,
- Open the main window of MAMP Pro and via the menu go to File > Edit Templates > MySQL > (your version).
This opens a text editor with your MySQL configuration. - In the configuration file, find the line starting with
[mysqld](be sure to locate thedat the end, and not to confuse with [mysql]). - If sql_mode isn’t set, you can add it under the
[mysqld]heading: Right after this line, add a new line containing:sql_mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
- Save the file, and restart MySQL.
If you don’t have a GUI admin interface or simply just prefer getting things done in a command-line terminal:
$ mysql -u root -p -e "SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';"
If you are not happy with the other modes you can simply set the sql_mode to a blank string like so: sql_mode=”, but again if all we care about is zero-date-time tolerance and to avoid the error 1292, all we need is to take out
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
from the sql_mode of MySQL / MariaDb.
This is an article discussing about how to solve a specific MySQL Error Message. The error message is in the title of this article. To be more specific, the error message is ‘ERROR 1292 (22007) : Incorrect datetime value: ‘0000-00-00 00:00:00′ for column’. It can appear when a restore process of a database fails. It is in the following output of command execution :
[root@hostname ~]# mysql -uroot db_app < db_app.sql ERROR 1292 (22007) at line XXX: Incorrect datetime value: '0000-00-00 00:00:00' for column 'dateupdate' at row 1 [root@hostname ~]#
As in the above output command execution, the error message appears. It happens in the middle of restoring a database. In order to solve the problem, there is a specific setting of the sql_mode. The following is the step to solve it :
1. First, logging in to MySQL command console.
[root@hostname ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
2. Show the value of ‘sql_mode’ variable. Type the following query :
mysql> show variables like 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,01 sec) mysql>
3. Execute the following query to implement the solution temporarily :
mysql> set sql_mode=''; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> show variables like 'sql_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0,00 sec) mysql> quit Bye
4. Unfortunately, after quitting from the MySQL command console, the value return back to the original value. In order to change it permanently, edit the MySQL configuration file. Basically, it is located /etc/my.cnf. Add the following entry :
[mysqld] # # * Basic Settings # ... sql-mode = ""
The most important entry to solve the problem is the ‘sql-mode = “”‘ line.
- Finally, don’t forget to restart MySQL Database Server’s service. Do it as follows :
[root@hostname ~]# systemctl restart mysql [root@hostname ~]#
6. Last but not least, recheck the value of ‘sql-mode’ by typing the following command :
[root@hostname ~]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> show variables like 'sql-mode'; Empty set (0.01 sec) mysql> show variables like 'sql_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.00 sec) mysql>
7. Don’t forget to retry restoring the database after changing the sql_mode value.