How to take mysqldump with Generated column?

Question

I am getting below error while importing the dump,

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

When I describe the table with error, there are some generated columns in it.

option_type | varchar(45) | YES | MUL | NULL | VIRTUAL GENERATED

What is the correct command to use when taking mysql dump for the database contains generated column?

MySQL server source and destination version:

mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))

MariaDB mysqldump CLI version:

mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)

Answer 1

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.

It seems like you need to use MySQL's mysqldump to correctly dump and restore virtual generated columns on a MySQL server.

The bug was also reported here.

---

What I do as a workaround, is replace the virtual column in the dump:

sed -i 's/GENERATED ALWAYS AS .* VIRTUAL/NOT NULL/' mydump.sql

then restore the dump, then drop/add the generated column again:

mysql -e "ALTER TABLE foo DROP COLUMN bar;\
ALTER TABLE foo ADD COLUMN bar VARCHAR(255) AS ...;"

Answer 2

We were able workaround by creating the dump not with mysqldump but https://github.com/Smile-SA/gdpr-dump which is based on a PHP base dump tool.

Answer 3

We managed to do it by splitting the main mysqldump command into steps :

• Drop all tables (if you are not already using the --add-drop-database parameter in your original mysqldump command)

• Add the parameter --ignore-table=your_schema.your_table to your original mysqldump command

• After this one ran, use a new mysqldump command to create only the table structure :

  mysqldump --no-data ... your_schema your_table
  

• Use a mysql command to run the proper ALTER on your_table (you want to change the type for a basic one) for example :

  mysql ... -D your_schema -e "ALTER TABLE your_table MODIFY COLUMN your_column1 boolean default null, MODIFY COLUMN your_column2 boolean default null;"
  

• Finally, use a mysqldump command to only fill the table (not create it) :

  mysqldump --no-create-info ... -e your_schema your_table
  

Hope it helps, have a great day !

Answer 4

Same problem here, and since my provider uses mysqldump for creating backups I have no say in solving the problem at the root: illegal SQL-commands in backup-file.

My WORKAROUND has been to replace all INSERT commands by INSERT IGNORE commands: if the table is created from scratch by the backup (DROP TABLE ..., CREATE TABLE ... resulting in empty tables on start of INSERT commands) then this should be safe.