ERROR 3105 (HY000) at line 554: The value specified for generated column 'column' in table 'table' is not allowed

Written by - 0 comments

Published on - Listed in MySQL Databases MariaDB

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.

ERROR 3105 (HY000) during restore

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.

Problem with virtual generated columns

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

Restore into MySQL 8.x now working

With the virtual values gone, the restore into MySQL 8.x community edition now works:

root@mysql:/backup# cat app.sql | mysql app

No error this time. After restoring the dump, the application worked fine.

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  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   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