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.