How to take mysqldump with Generated column?

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)

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 ...;"

StackExchange Database Administrators Q#240882, answer score: 17