27 Сентябрь 2018
Миграция на MySQL 8: проблемы и решения
MySQL 8 migration: issues and solutions
В начале этого года состоялся релиз новой 8 ветки сервера баз данных MySQL. Нумерация была измененена в связи с новыми принципами версионирования MySQL и, по факту, новый выпуск представляет собой версию 5.8.
Среди целого набора новшеств, включённых в новый выпуск, некоторые из них повлияли на обратную совместимость с кодом, использующим MySQL для хранения своих данных.
Ввиду того, что всем рано или поздно придётся развёртывать новые системы или переносить данные со старых установок, автор на своём примере решил осуществить миграцию с предыдущей версии MySQL 5.7 на новый 8 выпуск. С предварительными результатами о замеченными измненениями, оказавшимися критичными для работы прикладного программного обеспечения, а также путями их решения, вас ознакомит эта статья.
Итак, вы решили использовать MySQL 8. Опустим здесь сам процесс установки или обновления предыдущей инсталляции — в целом они стандартны и практически не отличаются от процедур применяемых ранее.
В распоряжении автора была последняя доступная на момент написания статьи версия MySQL 8 развёрнутая в среде FreeBSD 11.2.
root@zotum:~ # mysql --version
mysql Ver 8.0.12 for FreeBSD11.2 on amd64 (Source distribution)
root@zotum:~ # uname -v
FreeBSD 11.2-RELEASE-p3 #0: Thu Sep 6 07:14:16 UTC 2018 root@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERICПрикладной софт, который работает с сервером баз данных, написан на PHP и также использует последнюю версию.
root@zotum:~ # php --version
PHP 7.2.10 (cli) (built: Sep 24 2018 00:28:21) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.2.10, Copyright (c) 1999-2018, by Zend TechnologiesВпрочем, выводы и решения приведённые далее, также применим и к любому другому клиентскому (отношению к серверу баз данных) коду.
1. Новая схема аутентификации
Первым же отмеченным отличием MySQL 8 стало изменение схемы аутентификации по умолчанию. Вместо привычной mysql_native_password её место заняла новая caching_sha2_password в качестве Preferred Authentication Plugin, которая декларируется как более безопасная и надёжая альтернатива старой схемы.
Попытка использования аутентификации неподдерживающим новый тип софтом приводит к появлению соответствующих ошибок.
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the clientОднако, в настоящее время все стандартные драйверы PHP, к примеру, популярный PDO, за исключением девелоперского расширения mysql_xdevapi её не поддерживают. Реализация новой схемы ожидается в выпуске PHP 7.3.
В этой связи, рекомендуется предпринять ряд действий для обеспечения работы текущих версий софта. Первым может стать измнение схемы аутентификации по умолчанию на её традиционный вариант в файле конфигурации MySQL my.cnf путём добавления в секцию [mysqld] соответветствующего параметра.
root@zotum:~ # cat /usr/local/etc/mysql/my.cnf
# $FreeBSD: head/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $
...
[mysqld]
...
default_authentication_plugin = mysql_native_password
...Далее, если вы уже добавили пользователей с новой схемой аутентификации, а, по меньшей мере один из них — root, точно был добавлен на этапе инсталляции, то следует изменить им схему на традиционный вариант.
root@zotum:~ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1745
Server version: 8.0.12 Source distribution
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.
root@localhost [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.01 sec)
...2. Зарезервированные слова
Вторым моментом, с которым некоторым придётся столкнуться, это появление ключевых и зарезервированных слов, которые нельзя использовать без экранирования знаком ` (backtick или знак акцента).
Их актуальный список можно получить непосредственно из базы со схемой данных MySQL information_schema.
...
root@localhost [(none)]> USE information_schema;
Database changed
root@localhost [information_schema]> SELECT WORD FROM INFORMATION_SCHEMA.KEYWORDS WHERE RESERVED = 1;
...В случае, если в SQL-коде в наименовании таблиц и/или полей встретится неэкранированное слово из данного списка, это приведёт к ошибке.
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups WHERE deleted = 0 AND uid = 2 ORDER BY gname ASC' at line 1В данном случае, было использовано зарезервированное слово groups.
Безусловно, экранирование имён полей и таблиц при помощи ` является хорошим тоном оформления кода для MySQL. Однако, проблема возникает тогда, когда тот же SQL-код предполагается использовать и для обращения к другим серверам баз данных, к примеру, PostgreSQL. Использование символа ` является нестандартным и исключительной особенностью MySQL и её форков, поэтому написание и адаптация такого универсального кода под иные серверы может представлять некоторые трудности. Пожалуй, оптимальным в данном случае вариантом будет избегать использовать зарезервированных слов в SQL-коде, а если вы их уже используете, то, вероятно, следует озаботиться изменением наименований для обеспечения совместимости с будущими версиями.
Другим вариантом может быть разрешение использования двойных кавычек ", которые являются стандартным для SQL решением, в MySQL для экранирования имён таблиц и полей. Это реализуется через добавление в режимы работы sql_modes значения ANSI_QUOTES. Это можно сделать путём подачи SQL команды
...
SET SQL_MODE=ANSI_QUOTES;
Query OK, 0 rows affected (0.01 sec)
...и для постоянной установки этого режима добавить соответствующую строку к набору режимов в конфигурационном файле my.cnf
root@zotum:~ # cat /usr/local/etc/mysql/my.cnf
# $FreeBSD: head/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $
...
[mysqld]
...
sql-mode = "ANSI_QUOTES ..."
...3. Исключение query cache
Ещё одним сюрпризом может стать отсутствие в MySQL 8 встроенного механизма кэширования запросов query cache, который широко использовался в предыдущих версиях. Данная функция уже довольно давно значилась как deprecated и вот, наконец, момент настал.
Среди основных причин называется его низкая эффективность из-за чересчур прямолинейных алгоритмов работы, которые, к тому же, затрудняют работу предсказательных механизмов кэширования InnoDB.
В случае, если вы уверены в необходимости кэширования запросов, то вам следует рассмотреть использование сторонних SQL-прокси с кэшированием, к примеру ProxySQL обладающим в разы более высокой эффективностью и настраиваемостью.
4. Выводы
Миграция на новые версии программного обеспечения всегда имеет риски потери совместимости. Адаптация старого софта под новые требования процесс нормальный, стандартный, и требующий отдельного внимания для избежания возможных проблем в обозримой перспективе.
В этой связи, надеюсь, данная статья будет полезна всем разработчикам и системных администраторам в качестве подготовки к повсеместному внедрению MySQL 8.
Если вы заметили неточности или проблемы в процессе аналогичного обновления, прошу добавлять ваши мнения в комментарии ниже.
Заранее признателен за участие!
