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.
No comments yet.
AWS Android Ansible Apache Apple Atlassian BSD Backup Bash Bluecoat CMS Chef Cloud Coding Consul Containers CouchDB DB DNS Database Databases Docker ELK Elasticsearch Filebeat FreeBSD Galera Git GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Icingaweb Icingaweb2 Influx Internet Java KVM Kibana Kodi Kubernetes LVM LXC Linux Logstash Mac Macintosh Mail MariaDB Minio MongoDB Monitoring Multimedia MySQL NFS Nagios Network Nginx OSSEC OTRS Office PGSQL PHP Perl Personal PostgreSQL Postgres PowerDNS Proxmox Proxy Python Rancher Rant Redis Roundcube SSL Samba Seafile Security Shell SmartOS Solaris Surveillance Systemd TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder