0

MySQL server 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)

I am using this mysqldump version to take dump from the mentioned mysqld version. Data is around 25 gb.

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.

Source and destination mysql server is same version.

Command used to dump,

mysqldump -h mysql -P 3306 -uroot -px xxxxxxx --databases db_name

Total number of characters in Line 82 is around 80k, line starts with,

INSERT INTO table_name (ID, XXXXX, XXXXX, XXXXX, XXXXX, XXXXX, XXXXX, XXXXX, XXXXX, XXXXX, xxxxx, xxxxx, XXXXX) VALUES (5541,1,4852,'2018-11-12 09:30:25.306','{\"id\": 1108

Output from show create table, for the column throwing error:

column_name varchar(45) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(OPTIONS,'$.type'))) VIRTUAL,

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

  • I'm assuming this error is from the import rather than the dump? What mysqldump options did you use? Taking a dump of the system mysql database (or all databases) won't restore to MariaDB-10.3. What is the SQL at line 82 of the import that generated this error? – danblack Jun 19 at 6:48
  • updated my question with more info, interestingly I don't see the column name in insert statement – karthikeayan Jun 19 at 7:03
  • Need more info. "Generated colunns" are a bit different between 5.7 and 10.3. They may even be incompatible. Please provide SHOW CREATE TABLE. – Rick James Jun 19 at 17:41
  • @RickJames i will not be able to show the complete output of "show create table", however updated with my question with the output of "show create table", only for the column gives error – karthikeayan Jun 19 at 20:11
  • Manually test whether the CREATE TABLE is syntactically valid for MariaDB. – Rick James Jun 19 at 20:16
0

MariaDB JSON functions are different from MySQL ones.

  1. Manually create the equivalent table in MariaDB-10.3.
  2. remove the DROP TABLE {tablename} /CREATE TABLE {tablename} from your mysqldump output.
  3. import the edited mysqldump file.

If there are a lot of these generated columns, suggest doing a mysqldump --no-data and getting the syntax of all the tables correct first.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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