Архивы меток:SQL

Заметил однажды при импорте sql файла следующую ошибку:

ERROR 1067 (42000) at line 211: Invalid default value for ‘blablabla’

Она возникает в связи с тем что новые версии MySQL сервера используют строгий режим и такие параметры как NO_ZERO_DATE не позволяют вносить в базу значения даты как например ‘0000-00-00’.

Подключимся к mysql серверу:

mysql -u root -p

Выполним запрос который отобразит значения sql_mode:

show variables like 'sql_mode';

Скопируем строку с этими значениями и выйдем из mysql:

exit

Откроем файл конфигурации например в текстовом редакторе nano (Ctrl+X для выхода, y/n для сохранения или отмены изменений):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

У меня в файле не было sql_mode=, по этому в конце файла вставим строку со скопированными ранее значениями убрав из нее NO_ZERO_IN_DATE,NO_ZERO_DATE, в моё случае получилось следующее:

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Перезапустим mysql чтобы применить изменения:

sudo service mysql restart

Все, теперь при импорте данной ошибки не должно быть.

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

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

Для теста подключимся к MySQL и создадим пару новых баз данных без указания кодировки и с указанием:

mysql -u root -p
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Создадим тестовую таблицу в первой базе и посмотрим её кодировку:

USE test_db1;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status like 'users';

Создадим тестовую таблицу во второй базе и посмотрим её кодировку:

USE test_db2;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status;

Посмотрим также кодировку обеих баз данных:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

Посмотреть кодировку колонки в конкретной таблице можно так:

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

В моём случае таблица в первой базе была с кодировкой latin1_swedish_ci, так как она является стандартной, а во второй utf8_general_ci так как я её заранее указал.

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

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

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

show databases;

Посмотреть существующие таблицы в базе:

USE test_db1;
show tables;

Теперь сменим кодировку первой базы и её таблицы на utf8 и сразу проверим:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

Если нужно изменить кодировку в sql файле, то откроем его в редакторе Notepad++ преобразуем например в UTF-8/без BOM, а также если в начале файла указана кодировка в SET NAMES, изменим её там, после этого можно импортировать файл в базу.