Нови възможности в MySQL 8.0.16 екосистемата

Миналия Четвъртък (2019-04-25), точно преди почивните дни за Православния Великден Оракъл пусна версия 8.0.16 на продуктите в MySQL екосистемата (включваща сървър, рутер, шел, ксъединители и workbench ГПИ). Новите версии на сървъра продължиха традицията установена с MySQL 8 на въвеждане на нови възможности, въпреки че все още са обозначавани като "Издание по поддръжката" ("Maintenance Release"). На последния Pre-FOSDEM MySQL Day Giuseppe Maxia предложи да се ползват семантични версии и аз заставам зад това, защото разбирам и подкрепям семантичните версии.

Start pushing for Semantic Versioning to be adopted at MySQL, and you will avoid this kind of miscommunications

— Giuseppe Maxia (@datacharmer) February 1-st 2019

Това ще бъде наистина полезно, защото версия 8.0.15 на сървъра беше истинско издание по поддръжката оправящо само важен проблем с групова репликация. Следвайки новата схема на номериране на версиите (виж MySQL 8.0: It Goes to 11!) всички други продукти от екосистемата бяха бутнати до 8.0.15 дори без каквито и да било промени. Наистина не съм сигурен, че това е нужно, защото докато се ползва същата голяма (и малка) версия не би трябвало да има проблем със съвместимостта. А и същите номера на версии не осигуряват наистина пълна съвместимост между продуктите както моите бъгове за MySQL Workbench (напр. 90620, 92900, 92908 и 94012) показват.

MySQL Server

E, какво е новото в новата версия на сървъра. Ето това което забелязах.

CHECK ограничения

Лично аз чакам тази възможност от MySQL 4 когато започнаха да добавят големи нови възможности в сървъра и той започна да става повече като по-напредналите бази с данни. Бъг #3465 беше отворен през Април 2004 (и беше обещано да бъде оправен в MySQL 5.1), така че тази SQL възможност най-накрая е направена след повече от 15 години.

До сега (както може би сте забелязали) ключовата дума CHECK в CREATE TABLE беше приемана, но безшумно пренебрегвана. В миналото ползвахме заобиколни решения. Например можеше да се осигурят само неотрицателни стойности в колона с числов тип (цели числа и типове с фиксирана/плаваща запетая) с използването на нестандартния атрибут UNSIGNED (виж Numeric Type Attributes). По-сложни ограничения можеха да бъдат направени с тригери и вдигане на състояния (виж SIGNAL syntax) както показах в представянето ми MySQL 8 for developers на Пролетния семинар на БГПО 2018 г. и подобни все още ще са нужни.

MySQL 8.0.16 вече поддържа и двете ограничения на ниво таблица и колона (виж CHECK constraints) за всички машини за съхранение. Ограничения на ниво таблица се поставят навсякъде в CREATE TABLE извън определенията на колони и могат да се обръщат към една или повече колони дори с предварително споменаване (т.е. на колони определени по-късно в израза). Ограниченията на ниво колона се поставят в определението на колоната и могат да се обръщат само към нея. За съжаление, израза който определя условието за проверка може да ползва само литерали, детерминистични вградени функции и оператори (т.е. вградени процедури, ОПФ/и, променливи и подзаявки не са разрешение). Не могат също да се ползват CHECK ограничения на колони с референтни действия от външни ключове (т.е. ON UPDATE, ON DELETE).

Нещото което трябва да се има в предвид с CHECK ограниченията е, че грешка има само ако условието се изчисли като FALSE. Ако са изчисли UNKNOWN поради NULL стойности грешка няма да има. Това е покрито много добре от Markus Winand в статията му The Three-Valued Logic of SQL, така че горещо ви препоръчвам да я прочетете цялата.

CHECK ограниченията имат име до 64 символа и ако не бъде указано сървъра ще създаде име като [име на таблица]_chk_[пореден номер], защото имената трябва да са уникални за схемата. Полезна възможност е да се създаде, но да не се налага ограничението (т.е. с NOT ENFORCED клауза), което е като разрешаване/забраняване за тригери което е възможност която искам да видя направена в бъдещи версии.

За основна употреба и прости примери за CHECK ограничения в MySQL, моля вижте наръчника и статията MySQL 8.0.16 Introducing CHECK constraint. По-интересен пример за CHECK ограничение върху JSON данни има в статията MySQL 8.0.16: how to validate JSON values in NoSQL with check constraint.

Ето първия ми пример. Мисля, че доста често има по две дати в таблица (напр. начална и крайна дата), които трябва да представляват началото и края на нещо, така че трябва да се в хронологичен ред. Нека да го направим с таблицата emp от примерната DEPT и EMP схема на Оракъл, която съм пригодил за MySQL.

ALTER TABLE emp
  ADD COLUMN retdate DATE AFTER hiredate,
  ADD CONSTRAINT ret_after_hire CHECK (retdate > hiredate);

Обаче, когато се опитах да обновя ред на служител, който вече има стойност за дата на наемане със следната заявка (датата на пенсиониране предполагаемо идва от грешен потребителски вход и/или лошо приложение):

UPDATE emp
   SET retdate = STR_TO_DATE('1019-04-25', '%Y-%m-%d')
 WHERE empno = 7369;

1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0

заявката неочаквано успя и нямаше грешка за "нарушено ограничение". След обсъждане с Frédéric Deschamps в Slack подадох бъг #95189. Изглежда е свързано с репликация, защото на моя самостоятелен MySQL 8.0.16 сървър под Windows проблема не се възпроизведе и заявката работи както се очаква, но пробата която направих беше на MySQL 8.0.16 сървъра ми работещ под Slackware64 -current, който репликира от моя основен MySQL 5.7.26 сървър.

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

ALTER TABLE emp
  ADD CONSTRAINT emp_chks CHECK (hiredate >= CURDATE() AND sal > 0);

Такова ограничение може да изглежда разумно (ако вземате в предвид само въвеждане на данни), но разбира се резултата е:

Error Code: 3814. An expression of a check constraint 'emp_chks' contains disallowed function: curdate.

защото CURDATE е недетерминистична функция. Такова ограничение е възможно в PostgreSQL (която също поддържа съхранени процедури и ОПФ/и), но не и в Oracle (която поддържа съхранени процедури) и MariaDB (която има горе-долу същите ограничения като MySQL, въпреки че не са ясно описани). Проблемите с това са как ще валидирате ограничението за съществуващите редове и как ще обновявате редове, защото стойността на CURDATE се сменя всеки ден. Решението е да се създаде допълнителна колона запазваща текущата дата при създаването на реда и да се ползва тя за проверка на ограничението.

Ако се опитате директно да създадете колоната и наложите ограничението (т.е. с ALTER TABLE) разбира се ще получите:

Error Code: 3819. Check constraint 'emp_chks' is violated.

защото CHECK ограничението, както другите ограничения (напр. първични и външни ключове, уникални индекси, NOT NULL) се валидират за всички редове при създаване и трябва винаги да остават валидни. Така, че нека пробвам другояче.

Първо, зареждам новата колона от съществуващите данни (напр. въз основа на hiredate колоната тъй като изрази са възможни като стойности по подразбиране от 8.0.13):

ALTER TABLE emp
  ADD COLUMN created DATE DEFAULT (hiredate);

След това, промяна на колоната и добавяне на CHECK ограничение:

ALTER TABLE emp
  MODIFY COLUMN created DATE NOT NULL DEFAULT (CURDATE()),
  ADD CONSTRAINT emp_chks CHECK (hiredate >= created AND sal > 0);

И сега да пробваме заявки:

UPDATE emp SET hiredate = '1979-04-25' WHERE empno = 7369;

INSERT INTO emp
  (empno, ename, job, mgr, hiredate, sal)
VALUES
  (9999, 'MULDER', 'INVESTIG.', 7839, '2019-04-25', 4242);

Очаквано UPDATE заявката предизвиква грешка:

Error Code: 3819. Check constraint 'emp_chks' is violated.

но INSERT заявката минава, което е неочаквано. Явно, стойността на колона created не е заредена със стойността по подразбиране когато CHECK ограничението се валидира. Пробвах същото (само, че с малко по-различен синтаксис) на Oracle XE 18 и работи както се очаква - и UPDATE и INSERT заявките нарушаваха CHECK ограничението.

Има нова таблица CHECK_CONSTRAINTS в INFORMATION_SCHEMA, която предоставя информация за създадените CHECK ограничения във всички схеми. Допълнителна информация за името на таблицата и дали CHECK ограничението е наложено или не може да се получи от таблицата TABLE_CONSTRAINTS чрез прецеждане по новата стойност CHECK на колоната CONSTRAINT_TYPE.

Пространствени данни

След ST_Distance от 8.0.14, сега ST_Length функцията също поддържа незадължителен втори параметър unit, така че е възможно да се изчисляват дължини в различните поддържани единици както са определени в INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE таблицата.

Сървъра прави цялостно надграждане

Сървъра вече може да надгражда mysql схемата, речника на данните и системните таблици както и PERFORMANCE_SCHEMA, INFORMATION_SCHEMA, sys и потребителски схеми (виж What the MySQL Upgrade Process Upgrades), така че mysql_upgrade командата се пенсионира. Това е важна административна възможност, защото тя ще направи надгражданията по-лесни и по-удобни. В миналото постоянно забравях да пусна командата, което водеше до странни проблеми по-късно.

Във връзка с надграждането имах странен проблем изразяващ се в това, че сървъра не можеше да стартира и печаташе съобщение [ERROR] [MY-013384] [Server] Could not create server upgrade info file at '/var/lib/mysql/data/' в лога въпреки, че правата бяха наред. Успях да намеря този gist mysql will not start after 8.0.15 to 8.0.16 update on Ubuntu 16.04 LTS с Google и след като създадох файла mysql_upgrade_info в /var/lib/mysql/data/ и след като прехвърлих собствеността му на mysql:mysql сървъра успя да запали успешно. Надграждах от 8.0.14 всъщност, но мисля, че може да е бъг. Вероятно сървъра очаква файла да е бил създаден.

Друга интересна нова възможност е избора --validate-config за проверка на конфигурацията на сървъра както администраторите са свикнали с други сървъри (напр. Apache). Това е наистина важно особено за среди в продукция, където неочаквания престой може да е крайно неприятен. Прочетете повече в Server Configuration Validation.

Системни потребители

MySQL сметки вече се категоризират и така разграничават като системни (които притежават SYSTEM_USER привилегия) и обикновени потребители (които не притежават). Това допринася за по-добро разделение на ролите, тъй като само системни потребители могат да извършват определени административни операции върху системни сметки. Преди това всеки потребител с подходящите привилегии можеше например да изтрие всяка сметка или убие всяка връзка принадлежаща на всеки потребител. Възможно е също така да се отнемат глобални привилегии частично за определени схеми (т.е. като изключения) чрез новата системна променлива partial_revokes, което преди това извикваше задаването на права поотделно за всички съществуващи схеми и добавяне на пава за всяка нова схема.

Сигурност

Новите неща са поддръжка за TLS 1.3, възможност за обновяване на SSL сертификатите без рестартиране на сървъра (виж ALTER INSTANCE RELOAD TLS) и информация за сертификатите в таблицата keyring_keys на PERFORMANCE_SCHEMA.

MySQL Router

Рутера вече има HTTP съставка, която му позволява да излага просто web-интерфейс и REST ППИта с цел да осигури по-добра наблюдателност чрез интеграция с външни инструменти за наблюдение и управление. Други значими промени са динамичната смяна между режими с един и много господари и подобрен журнал. Сега има WITH_ROUTER CMake избор за изграждане на рутер заедно с MySQL сървъра, която по подразбиране е ON и която реших да сменя на OFF, защото планирам да продължа да изграждам рутер като отделен пакет.

MySQL Shell

Шела идва с новия Shell Reporting Framework (виж Reporting with MySQL Shell), който позволява записването, показването и наблюдението на потребителски доклади. Нетърпелив съм да я пробвам и ще напиша отделна публикация по-късно. Вижте статията MySQL Shell 8.0.16: User Defined Reports от Jesper Wisborg Krogh. Също така сега вече е възможно да се изпълнява SQL без смяна на режима, тъй като \sql командата вече не сменя режима ако ѝ е подаден SQL израз, което мисля е доста полезно.

MySQL Workbench

MySQL Workbench вече поддържа прозоречни функции в SELECT (виж бъг #90620), изрази в DEFAULT (виж бъг #92900) и като части на ключове (виж бъг #92908) както и ключовата дума LATERAL (виж бъг #94012) - всички те достъпни от предходните 8.0.x версии на сървъра. За съжаление, не виждам каквато и да е поддръжка за CHECK ограничения (виж бъг #95143, който вече беше потвърден), така че отново еднаквите номера на версии не значат нищо.