Подготовка к обновлению до MySQL 5.7

Linux, MariaDB, mySQL | Amber | 1 Comment

Скоро выйдет в свет новая версия популярной открытой СУБД, MySQL 5.7, которая открывает более широкие возможности масштабируемости. К примеру, MySQL 5.7 масштабируется линейно на 48-ядерных серверах. Однако эта версия требует предварительной настройки MySQL 5.6 к обновлению.

Пиковая производительность сервера MySQL 5.7 – свыше 640K запросов в секунду, а интерфейс memcached, непосредственно взаимодействующий с механизмом хранения InnoDB, способен выдержать более 1,1 млн запросов в секунду.

Но прежде чем запустить mysql_upgrade, нужно подготовить систему, о чём и пойдёт речь в данном руководстве.

Целостность данных

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

Ниже приведены 5 примеров запросов, которые нужно изменить для корректной работы MySQL 5.7.

1: Внесение отрицательного значения в столбец без знака

Создайте таблицу со столбцом unsigned

CREATE TABLE test (
id int unsigned
);

Внесите отрицательное значение.

Поведение предыдущих версий:

INSERT INTO test VALUES (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)

Поведение MySQL 5.7:

INSERT INTO test VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

2: Деление на ноль

Создайте тестовую таблицу:

CREATE TABLE test2 (
id int unsigned
);

Попробуйте поделить на ноль.

Поведение предыдущих версий:

INSERT INTO test2 VALUES (0/0);
Query OK, 1 row affected (0.01 sec)

Поведение MySQL 5.7:

INSERT INTO test2 VALUES (0/0);
ERROR 1365 (22012): Division by 0

3: Внесение строки в 20 знаков в столбец для 10 знаков

Создайте таблицу, ограничив количество знаков одного из столбцов до 10.

CREATE TABLE test3 (
a varchar(10)
);

Попробуйте вставить в него более длинную строку.

Поведение предыдущих версий:

INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');
Query OK, 1 row affected, 1 warning (0.00 sec)

Поведение MySQL 5.7:

INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');
ERROR 1406 (22001): Data too long for column 'a' at row 1

4: Установка нулевой даты в столбце даты и времени

Создайте тестовую таблицу со столбцом datetime:

CREATE TABLE test3 (
a datetime
);

Установите дату 0000-00-00 00:00:00.

Поведение предыдущих версий:

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

Поведение MySQL 5.7:

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

5: Использование GROUP BY и выбор неоднозначного столбца

Такое случается, когда описание не является частью оператора GROUP BY и к нему не применяется агрегатная функция (например, MIN или MAX).

Поведение предыдущих версий:

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;
+----+------------+-------------+
| id | invoice_id | description |
+----+------------+-------------+
| 1 | 1 | New socks             |
| 3 | 2 | Shoes                 |
| 5 | 3 | Tie                   |
+----+------------+-------------+
3 rows in set (0.00 sec)

Поведение MySQL 5.7:

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'invoice_line_items.description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Поведение sql_mode

В MySQL каждое поведение, продемонстрированное в предыдущем разделе, зависит от так называемого механизма sql_mode.

Он появился в MySQL 4.1 (2004), но не был вмонтирован по умолчанию. MySQL 5.7 включает по умолчанию:

• ONLY_FULL_GROUP_BY
• STRICT_TRANS_TABLES
• NO_ENGINE_SUBSTITUTION
• NO_AUTO_CREATE_USER

Кроме того, режим STRICT_TRANS_TABLES (или строгий режим) стал ещё более строгим и теперь включает в себя поведение режимов ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE и NO_ZERO_IN_DATE.

Советы по обновлению

Хорошие новости для пользователей последних версий WordPress, Drupal и Magento – вам не нужно ничего делать! Этим приложениям уже известно о sql_mode, при загрузке MySQL они автоматически откорректируют свои настройки.

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

Пользователи, чьё приложение находится на стадии производства, должны выполнить постепенную и более тщательную настройку. Вот несколько полезных советов:

• Whitelist (или белый список). Включите новую стандартную опцию <tt>sqlmode</tt> в новые части приложения. Например, разрабатываемый cron job для восстановления кэша данных может включить <tt>sqlmode</tt> при следующем подключении к MySQL. Существующий код приложения может использовать поведение, заданное изначально.
• Blacklist (чёрный список). Достигнув некоторых успехов в преобразовании приложений, попробуйте сделать новую функцию <tt>sqlmode</tt> стандартной для сервера. при этом действующие приложения могут использовать начальное поведение, изменив его при следующем подключении к MySQL. Кроме того, во избежание ошибок MySQL поддерживает модификатор IGNORE на индивидуальной основе. К примеру:

INSERT IGNORE INTO my_table  . . .`

• Постепенное развертывание. Управляя своим приложением, вы можете внедрить функцию sql_mode для определённых пользователей. Рекомендуется разрешить внутренним пользователям выполнять бета-тестирование.

1: Поиск несовместимых операторов, которые генерируют предупреждения или ошибки

Сначала нужно убедиться, что ни один из текущих запросов не генерирует ошибок или предупреждений. Это очень важно, поскольку MySQL 5.7 иначе реагирует на некоторые запросы (предупреждение в MySQL 5.6 – ошибка в MySQL 5.7).

MySQL предоставляет performance_schema – функцию диагностики, которая включена по умолчанию в MySQL 5.6 и выше. При помощи performance_schema можно написать запрос, возвращающий все операторы, которые вызвали ошибку или предупреждение.

Запрос для отображения операторов, ставших причиной ошибки или предупреждения (MySQL 5.6+):

SELECT
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
performance_schema.events_statements_summary_by_digest
WHERE
((`SUM_ERRORS` > 0) OR (`SUM_WARNINGS` > 0))
ORDER BY
`SUM_ERRORS` DESC,
`SUM_WARNINGS` DESC;

Запрос для отображения операторов, которые генерируют ошибки (MySQL 5.6+):

SELECT
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
performance_schema.events_statements_summary_by_digest
WHERE
`SUM_ERRORS` > 0
ORDER BY
`SUM_ERRORS` DESC,
`SUM_WARNINGS` DESC;

2: Изменение поведения MySQL 5.6 на  MySQL 5.7

Можно также выполнить тестовый запуск MySQL 5.6, изменив поведение СУБД на MySQL 5.7.

Морган Токер (Morgan Tocker) из команды MySQL разрабатывает проект GitHub, где можно найти пример конфигурационного файла для изменения поведения MySQL 5.6.

Использование стандартных настроек  MySQL 5.7 в MySQL 5.6 позволяет протестировать работу приложения и  устранить его зависимость от менее строгого поведения.

Этот файл довольно короткий:

# This makes a MySQL 5.6 server behave similar to the new defaults
# in MySQL 5.7
[mysqld] # MySQL 5.7 enables more SQL modes by default, but also
# merges ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
# into the definition of STRICT_TRANS_TABLES.
# Context: http://dev.mysql.com/worklog/task/?id=7467
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE"
# The optimizer changes the default from 10 dives to 200 dives by default
# Context: http://mysqlserverteam.com/you-asked-for-it-new-default-for-eq_range_index_dive_limit/
eq_range_index_dive_limit=200
# MySQL 5.7 contains a new internal server logging API.
# The setting log_warnings is deprecated in 5.7.2 in favour of log_error_verbosity.
# *But* the default fo log_warnings also changes to 2 as well:
log_warnings=2
# MySQL 5.7.7 changes a number of replication defaults
# Binary logging is still disabled, but will default to ROW when enabled.
binlog_format=ROW
sync_binlog=1
slave_net_timeout=60
# InnoDB defaults to the new Dynamic Row format with Barracuda file format.
# large_prefix is also enabled, which allows for longer index values.
innodb_strict_mode=1
innodb_file_format=Barracuda
innodb_large_prefix=1
innodb_purge_threads=4 # coming in 5.7.8
innodb_checksum_algorithm=crc32
# In MySQL 5.7 only 20% of the pool will be dumped,
# But 5.6 does not support this option
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
# These two options had different names in previous versions
# (binlogging_impossible_mode,simplified_binlog_gtid_recovery)
# This config file targets 5.6.23+, but includes the 'loose' modifier to not fail
# prior versions.
loose-binlog_error_action=ABORT_SERVER
loose-binlog_gtid_recovery_simplified=1
# 5.7 enable additional P_S consumers by default
# This one is supported in 5.6 as well.
performance-schema-consumer-events_statements_history=ON

3: Изменение режима sql_mode (опционально)

Иногда возникает необходимость тестировать или обновлять сервер в несколько этапов. Вместо того чтобы полностью переписывать файл конфигурации сервера MySQL, можно изменить его для одной сессии. К примеру:

CREATE TABLE sql_mode_test (a int);

Установка режима No SQL:

set sql_mode = '';
INSERT INTO sql_mode_test (a) VALUES (0/0);
Query OK, 1 row affected (0.01 sec)

Установка строгого режима SQL:

set sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO sql_mode_test (a) VALUES (0/0);
ERROR 1365 (22012): Division by 0

Заключение

На данном этапе система MySQL готова к обновлению до версии MySQL 5.7. Официальный мануал по обновлению можно найти по этой ссылке.

MySQL 5.7 – это большой шаг вперёд касательно целостности данных и стандартных конфигураций современных приложений.