how to restore mysql backup that have generated always as column?

Question

CREATE TABLE `revenue_daily` ( `wallet` varbinary(100) NOT NULL DEFAULT '0',
`tc_access` varbinary(100) NOT NULL DEFAULT '0',
`tc_short` varbinary(100) NOT NULL DEFAULT '0',
`total_toll_collection` varbinary(100) GENERATED ALWAYS AS (`wallet` + `tc_access`) VIRTUAL NOT NULL,
`cash_collection` varbinary(100) GENERATED ALWAYS AS (`total_toll_collection` - `tc_short`) VIRTUAL NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

That table has generated column.I backed up database structure with data and when i am restoring same .sql file then error occur. Error is:-

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

I am using mysql version:-

sunilp@sunilp ~> mysql --version
mysql: [Warning] World-writable config file '/etc/mysql/mysql.conf.d mysqld.cnf' is ignored.
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

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

---

I also posted this answer here.