MySQL *

Очередной баг в MySQL.

Нашёл ещё один баг в MySQL. Поскольку Оракл все российские регистрации позакрывал - напишу хоть сюда, что ли...

Баг найден в запросе LOAD XML INFILE. Суть бага - если выполняется загрузка многоуровневого (nested) XML, то после обработки атрибутов нижнего уровня расположенные ниже по тексту атрибуты верхнего уровня не обрабатываются.

Например, имеется такой XML:

<data>
<item col1="1">
  <col2>2</col2>
  <nested col3="3">x</nested>
  <col4>4</col4>
</item>
<item col1="11">
  <col2>22</col2>
  <col4>44</col4>
  <nested col3="33">xx</nested>
</item>
</data>

Выполняем его импорт в таблицу

CREATE TABLE data (
    col1 INT,
    col2 INT,
    col3 INT,
    col4 INT,
	nested VARCHAR(4)
    );

запросом

LOAD XML INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test.xml' 
INTO TABLE data
ROWS IDENTIFIED BY '<nested>';

и видим, что для первой записи, где атрибут col4 расположен после вложенного атрибута nested, импорт col4 не выполняется. А во второй записи всё нормально.

mysql> SELECT * FROM data;
+------+------+------+------+--------+
| col1 | col2 | col3 | col4 | nested |
+------+------+------+------+--------+
|    1 |    2 |    3 | NULL | x      |
|   11 |   22 |   33 |   44 | xx     |
+------+------+------+------+--------+
2 rows in set (0.00 sec)

В общем, если работаете с импортом XML - будьте осторожны. И лучше импортируйте каждый уровень в отдельную таблицу.

Сегодня получилось узнать "что-нибудь новенького"©. А именно - узнал, когда именно проверяется ссылочная целостность при ссылке на себя.

Создадим таблицу:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  FOREIGN KEY (id) REFERENCES test (id)
);

Таблица ссылается сама на себя. Более того, поле ссылается само на себя. Практического смысла - ноль, но это позволяет узнать тонкости. Тип данных SERIAL выбран потому, что он создаёт дополнительный вторичный индекс, необходимый для создания внешнего ключа. Для других типов данных индекс надо создавать явно, причём до создания внешнего ключа.

Итак, получится ли вставить в такую таблицу? При вставке сервер должен вставить запись, обновить вторичный индекс, проконтролировать ссылочную целостность. Вопрос в том, в каком порядке он это сделает.

Пробуем.

INSERT INTO test VALUES (1);
SELECT * FROM test;

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

Всё это можно увидеть, например, на этом fiddle.

Зачем это может понадобиться? не знаю...

Как мы объединили два разных екома в одну CRM

Оба интернет-магазина — назовем их А и Б — годами работали самостоятельно, накопили много контактов и клиентов, а потом объединились в одну компанию. Чтобы выстроить продажи, им нужна была общая база данных. Но стек у магазинов отличался, и понадобилась наша помощь.

Помимо стека, были и другие ограничения:

Срок MVP: на всё про всё — полгода.

Бюджет: лепить огромного отказоустойчивого мастодонта мы не могли.

Удобство: нужен был сервис одного окна с понятным интерфейсом.

Поэтому мы остановились на Bitrix24. Первым делом определили, что должно быть в общей CRM и какие данные нам нужны. Потом на этапе ППО выбрали механизм реализации — процесс ETL (Extract. Transform. Load). Он состоит из трех этапов:

• извлечение данных из имеющихся баз,

• преобразование их под новую бизнес-модель,

• загрузка в новую CRM.

>> Подробно про каждый этап рассказываем в отдельной статье.

В итоге пришли вот к такой архитектуре: 

Как видим, у нас было три экстрактора: общий для магазина А и два отдельных для магазина Б (один для Kafka, другой для Json). Два трансформера — для каждого магазина свой, они выдавали одинаковые DTO и передавали их в лоадер. Дальше лоадер закидывал всё в B2B CRM.

В результате нам удалось выгрузить свыше 170 000 активных компаний и более 264 000 контактов из обоих интернет-магазинов.

Подробнее про кейс читайте в нашем блоге, а заодно подписывайтесь на наш телеграм-канал для тимлидов.

Опечатка в MySQL 8.1 Reference Manual

Читая документацию по процессу апгрейда до версии 8.1, нашёл опечатку.

На странице 2.10.5 Preparing Your Installation for Upgrade имеется пункт 5 с описанием проверки на то, что отсутствуют констрейнты с именами более 64 символов. Приведён текст запроса, который это проверяет. Однако в нём по ошибке вместо таблицы INNODB_FOREIGN написана несуществующая таблица INNODB_SYS_FOREIGN.

Понятно, что опечатка достаточно очевидна, и поправить не проблема. Исправленный текст запроса выглядит так:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
  (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
               INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
   FROM INFORMATION_SCHEMA.INNODB_FOREIGN
   WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

PS. Багтрак недоступен (учётные записи россиян заблокированы, а от анонимусов репорты не принимаются), напишу хоть здесь, чтобы не потерялось (и так уже с десяток багов протерялся/забылся).

MySQL тоже может выдать невменяемое сообщение об ошибке

Вообще MySQL славен тем, что если в SQL-коде встретилась синтаксическая ошибка, он возвращает сообщение об ошибке, которое точно описывает суть проблемы и точно указывает место в коде, где обнаружена ошибка. Но и на старуху бывает проруха..

Неожиданно в достаточно простом коде сообщение об ошибке совершенно не соответствует проблеме:

CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;

Вопреки ожидаемому сообщению о проблеме с FULL JOIN (MySQL в принципе не поддерживает этот тип связывания) сервер неожиданно выдаёт

ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause'

Для опытного программиста выявить источник проблемы не составит труда - да он и не напишет такое. А вот начинающий, или имеющий опыт в другой DBMS, программист может надолго завязнуть в разбирательстве.

Понятно, что эта проблема связана с парсером. FULL является в MySQL ключевым (но не зарезервированным) словом, и используется в некоторых административных запросах (например, в некоторых SHOW).

Увы, исправить это нельзя. Можно только запомнить.

Невидимые столбцы в MySQL 8

Продолжая писать о малоизвестных возможностях MySQL хочу рассказать о портированном со старшего брата Oracle DB. Так начиная с версии 8.0.23 любой столбец таблицы можно объявить невидимым.

Это можно сделать как при создании таблицы, так и при добавлении новой колонки.

CREATE TABLE test_table (
  a INT,
  b DATE INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE test_table ADD COLUMN c INT INVISIBLE;

Видимость колонки может быть изменена в любое время:

ALTER TABLE test_table CHANGE COLUMN b b DATE VISIBLE;
ALTER TABLE test_table MODIFY COLUMN b DATE INVISIBLE;
ALTER TABLE test_table ALTER COLUMN c SET VISIBLE;

Следующие команды покажут скрытые колонки с флагом INVISIBLE:

SHOW COLUMNS FROM test_table;
SHOW CREATE TABLE test_table;

При выборке всех столбцов (SELECT *) скрытые столбцы не видны, только при явном указании:

TABLE test_table; SELECT * FROM test_table; -- столбец с скрыт

SELECT a, b, c FROM test_table; -- столбец с показан

То же самое при вставке, без явного указания не работает:

INSERT INTO test_table VALUES (1, now(), 33); == ошибка
INSERT INTO test_table () VALUES (1, now(), 33); == тоже ошибка

INSERT INTO test_table VALUES (1, 22); -- вставлен NULL
INSERT INTO test_table (a, b, c) VALUES (1, now(), 33); -- все значения

UPDATE для скрытых столбцов работает как обычно.

Зачем нужно? Безопасность данных (скрыть чувствительные данные). Знаете еще применения - пишите в комментах

Официальная документация

Сайт для тестирования SQL

На днях натолкнулся на дикий баг в мускуле

Оказывается что при использовании короткого синтаксиса внешний ключ не создается

create table test (
 id int primary key,
 first_name varchar(20),
 last_name varchar(30)
);

create table ref1 (
 id int primary key,
 test_id int references test(id)
);

В этом примере внешний ключ для поля `test_id` не создаётся

Подробное описание здесь: https://tproger.ru/articles/staryj-bag-v-mysql-istoriya-detektiva/

MySQL 8: short syntax for select all

Начиная с MySQL 8, вы можете использовать довольно короткий запрос для выбора всех данных из таблицы.
Просто используйте table a; вместо SELECT * FROM a; и получить тот же результат:

mysql> table a;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+---+---+

mysql> table b;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 1 | 3 |
| 3 | 4 |
+---+---+

mysql> table c;
+===+===+
| m | n |
+===+===+
| 1 | 3 |
| 1 | 3 |
| 3 | 4 |
+---+---+

Этот синтаксис также можно использовать с оператором UNION и INTERSECT

mysql> table a union all table b union all table c;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 1 | 2 |
| 1 | 3 |
| 3 | 4 |
| 1 | 3 |
| 1 | 3 |
| 3 | 4 |
+---+---+

mysql> table a intersect table b;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 3 | 4 |
+---+---+

Вы можете проверить этот код на SQLize.online