One of the errors encountered while restoring the MySQL backup created using mysqldump is as follows. I encountered it in my EC2 jump server that connects to AWS RDS.
ERROR 3105 (HY000) at line 1238: The value specified for generated column ‘column1’ in table ‘table1’ is not allowed.
My backup command:
[ec99-root@ip-99-9-9-991 ~]$ mysqldump -h mydb_rds_endpoint.rds.amazonaws.com -P 3306 -u MyUserName -pMyPassword –hex-blob –triggers –routines mydbName > mydbDumpFile.sql
My restore command:
[ec99-root@ip-99-9-9-991 ~]$ mysql -f -h mydb_rds_endpoint.rds.amazonaws.com -P 3306 -u MyUserName -pMyPassword newDB < mydbDumpFile.sql
I then encountered the previously described error.
ERROR 3105 (HY000) at line 1238: The value specified for generated column ‘column1’ in table ‘table1’ is not allowed.
ERROR 3105 (HY000) at line 1341: The value specified for generated column ‘column2’ in table ‘table2’ is not allowed.
This occurred because the installed version of MySQL on the EC2 jump server has different configured global variables that are inappropriate for the AWS RDS MySQL database instance. particularly the character set. The character set that is meant to be “utf8mb4” in the dump file is actually a different character set.
Hence while taking the backup, you must specify the character set as shown below.
[ec99-root@ip-99-9-9-991 ~]$ mysqldump -h mydb_rds_endpoint.rds.amazonaws.com -P 3306 -u MyUserName -pMyPassword –default-character-set=utf8mb4 –hex-blob –triggers –routines myDBName > mydbDumpFile.sql
When you run the restore command now, you’ll see that everything went smoothly during the restoration.
If the above solution didn’t help, then it could be because of a version mismatch between the client and server. Ensure that you upgrade the version to avoid this error.
Note that this issue arises when using MariaDB’s mysqldump with virtual generated columns. Although MySQL only takes DEFAULT as a value for a virtual generated column, MariaDB’s mysqldump appears to dump the generated values. To accurately dump and recover virtual generated columns on a MySQL server, use mysqldump from MySQL.
Happy learning!!
Big Data and SQL 
One comment