Due to a compatibility issue of a Laravel-based application, the underlying MariaDB database needed to be replaced by a MySQL 8 community database. But when trying to restore the dump from MariaDB (10.5) into MySQL 8.x, the restore failed.
In the past years this has always worked between MySQL and MariaDB. But in recent years, MariaDB and MySQL have added more and more changes which are incompatible. Some features only exist on MariaDB, others only on MySQL. This makes it trickier to migrate data into the other database type.
Before replacing MariaDB with MySQL community, the database was dumped from MariaDB 10.5 using mysqldump with default options and saved into a gzipped dump file.
But while restoring the dump into MySQL 8.x, the following error showed up:
root@mysql:/backup# gunzip < app.sql.gz | mysql app
ERROR 3105 (HY000) at line 554: The value specified for generated column ‘description’ in table ‘products’ is not allowed.
That was the first time I personally came across this restore error.
According to this answer on DBA Stackexchange, this is an incompatibility problem between MariaDB and MySQL 8.x:
This is a problem when using mysqldump from MariaDB with virtual generated columns. MariaDB’s mysqldump apparently dumps the generated values, but MySQL only accepts DEFAULT as value for a virtual generated column.
One way to solve this is to manually adjust the MariaDB dump, replacing every occurrence of “GENERATED ALWAYS AS .* VIRTUAL” with “NOT NULL”.
To do this, I unzipped the dump file and used sed:
root@mysql:/backup# gunzip app.sql.gz
root@mysql:/backup# sed -i ‘s/GENERATED ALWAYS AS .* VIRTUAL/NOT NULL/’ app.sql
With the virtual values gone, the restore into MySQL 8.x community edition now works:
root@mysql:/backup# cat app.sql | mysql app
root@mysql:/backup#
No error this time. After restoring the dump, the application worked fine.
Posted by:
https://www.claudiokuenzler.com/blog/1354/mariadb-mysql-dump-restore-error-3105-HY000
You need to login in order to like this post: click here
YOU MIGHT ALSO LIKE
