5

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)

0

3 Answers 3

17

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 ...;"
1
  • 1
    Please note that the sed workaround above breaks the dump if it contains MySQL's help_topic table. Consider editing the regex by adding the comma: sed -i 's/GENERATED ALWAYS AS .* VIRTUAL,/NOT NULL,/' mydump.sql
    – YTZ
    Dec 12, 2021 at 22:24
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.

0

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 !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.