1

I have a database with set of generated stored columns. When i exporting the database, the sql file comes with the generated column also. I'm using the adminer as the mysql client. Now the problem is import section. If i try to import that sql file, it gives such errors like

The value specified for generated column 'field_name' in table 'table_name' is not allowed.

Table creation

  CREATE TABLE `table_name` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `field_name` text,
    `generated_field` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (CONCAT('hello ', `field_name`)) STORED
    );

Error thrown query is

INSERT INTO `table_name` (`id`, `field_name`, `generated_field`) VALUES (1, 'James', 'hello James')

I got the solution by using a simple way. Just i removed the generated column from insert query. Now it's working. Because the generated column only used for gettin the data from the mysql server

INSERT INTO `table_name` (`id`, `field_name`) VALUES (1, 'James')

***But what is the exact solution for gettin the clear export file for import it to the sql server without any errors

Because this is too difficult for the large sql data set

Karthikeyan Ganesan
  • 1,901
  • 20
  • 23
  • I am looking for a solution for exactly the same problem. Did you manage to solve it already? – scr4bble Jun 08 '22 at 17:46
  • Does this answer your question? [how to restore mysql backup that have generated always as column?](https://stackoverflow.com/questions/46127855/how-to-restore-mysql-backup-that-have-generated-always-as-column) – scr4bble Jun 08 '22 at 18:59
  • i found it, adminer is taking the generated columns while we exporting the SQL. and we don't need that. it's automatically happen by the insert query. so I suggest to use the phpmyadmin for getting the clear SQL export without generated data – Karthikeyan Ganesan Jun 09 '22 at 04:49

0 Answers0