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. Новый X protocol

Также в MySQL теперь по умолчанию через X plugin активируется поддержка нового протокола обмена данными с драйверами X protocol. Среди его главных преимуществ можно отметить, в частности, поддержку асинхронных запросов и сопутствующей этому оптимизации работы с базами данных.

Для обмена данными X protocol использует TCP-порт 33060 который в стандартной конфигурации открыт для доступа всему миру. Если вы планируете его использование только на локальной машине то хорошей идеей будет в целях безопасности ограничить доступ для заданного набора адресов. Например, только для localhost.

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]
...
mysqlx_bind_address             = 127.0.0.1
...

5. Выводы

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

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

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

Заранее признателен за участие!


MySQL  PHP  dev