Проблема миграции базы данных с mysql 8 на mariaDB 10?

Question

[Артем Колчин]

В общем беда такая, есть старый сервер где стоит mysql 8
Там сайт на modx (естественно база раздута до 3 ГБ)

через mysqldump стянул базу, rsync перекинул на другой сервер и тут началось веселье.
Сначала ругался на

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Решил данный вопрос с помощью

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g'  db.sql

Дальше начинает импортировать и где то в середине говорит

ERROR 1180 (HY000) at line 3141: Got error 1 "Operation not permitted" during COMMIT

место на сервере есть
max_allowed_packet=256M увеличил до 256М (было 16)
Всё равно та вторая ошибка преследует и не дает базу залить в mariadb

Comments

[alexalexes]

Как у вас построено выражение insert into в дампе, запишите без строковых данных. Если критично, то без данных колонок.
Типа:

insert into (col1, col2, ...) values (1, 2, 3), (4, 5,6).... ;

[Артем Колчин]

alexalexes, да в целом ничего не стандартного

DROP TABLE IF EXISTS `modx_access_policy_template_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `modx_access_policy_template_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `description` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `modx_access_policy_template_groups`
--

LOCK TABLES `modx_access_policy_template_groups` WRITE;
/*!40000 ALTER TABLE `modx_access_policy_template_groups` DISABLE KEYS */;
INSERT INTO `modx_access_policy_template_groups` VALUES (1,'Admin','All admin policy templates.'),(2,'Object','All Object-based policy templates.'),(3,'Resource','All Resource-based policy templates.'),(4,'Element','All Element-based policy templates.'),(5,'MediaSource','All Media Source-based policy templates.'),(6,'Namespace','All Namespace based policy templates.');
/*!40000 ALTER TABLE `modx_access_policy_template_groups` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `modx_access_policy_templates`

[alexalexes]

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

Answer 1

[Akina]

Проблема миграции базы данных с mysql 8 на mariaDB 10?

Проблемы ОБЯЗАНЫ БЫТЬ. MySQL и MariaDB расфоркнулись достаточно давно, изменения и дополнения в синтаксисе они не синхронизируют, так что неудивительно, что чем дальше, тем меньше они совместимы. И соответственно всё меньше шансов. что дамп одной СУБД беспроблемно натянется на другую.

Такой перенос надо делать руками.

1. Выгружаем дамп структуры (без данных) из MySQL.
   
2. Экспортируем данные из MySQL-таблиц в CSV.
   
3. Правим дамп структуры руками, приводим к требованиям синтаксиса MariaDB.
   
4. Загружаем структуру в MariaDB, убеждаемся, что работает. Дропаем.
   
5. Делим дамп на 2 части - в одной только таблицы (и другие критично необходимые для создания таблиц объекты), в другой всё остальное (индексы, представления, триггеры-функции и прочая тряхомудия).
   
6. Грузим первую часть дампа структуры.
   
7. Импортируем данные из CSV в таблицы MariaDB.
   
8. Грузим вторую часть дампа структуры.
   

Comments

[Ипатьев]

А какие там конкретно расхождения в create table и instert-ах?

Я сколько раз переносил с одной на другую - никогда из-за синтаксиса проблем не было.

[Akina]

Ипатьев, большинство проблем - несовместимые кодировки и нераспознаваемые комментарии. Что до именно синтаксических различий - они обычно вылезают в хранимых объектах.

Относительно чистого CREATE TABLE - навскидку не помню, но вроде бы наибольшие проблемы вылезают при партиционировании и при использовании вычисляемых полей. Плюс что-то насчёт автосоздания индексов под внешние ключи... не, точно не помню. А, да - элементарно до недавнего времени отсутствие типа данных JSON в MariaDB.

Answer 2

[ThunderCat]

Как уже написал Akina, полной совместимости между этими версиями нет, но основной синтаксис без каких-то хитропопых движух по типу хранимок и кастомных/новомодных типов полей вполне совместим.
Что можно попробовать:
1) Добавить в начале дампа игнор внешних ключей, судя по всему у вас в транзакции происходит фигня, но что конкретно ее вызывает не видно, возможно таблица требует ссылки на внешний ключ, который еще отсутствует. В конце стоит снова включить контроль ключей. DISABLE KEYS и SET FOREIGN_KEY_CHECKS=0; в начале, и соответственно включить в конце дампа.
2) Если база большая, а инсерты в дампе не разбиты по строкам на адекватные блоки, можно попробовать убрать транзакции и посмотреть что конкретно не нравится системе. Возможно надо разбить инсерты на более мелкие блоки вставки, а возможно есть действительно несовместимый синтаксис.
3) Если такие танцы не помогают - костыли с методом описанным Akina, через csv и потом ручками связи/ключи/индексы/хранимки.

UPD: Народ говорит что может помочь принудительное указание типа движка таблиц и предварительная конвертация:

The tables must be InnoDB. After converting the tables to InnoDB, the database could be migrated

Comments

[Ипатьев]

Вообще-то в вопросе описана конкретная ошибка, а не вот этот вот синтаксис ( ͡° ͜ʖ ͡°)

[ThunderCat]

Ипатьев, "Operation not permitted" during COMMIT говорит только о том что ВНУТРИ транзакции что-то пошло не так и транзакция не прошла. Так что "конкретная ошибка" конечно есть, но в реальности она ничем не поможет.