distal-attribute
distal-attribute
distal-attribute
distal-attribute

[BUG] [MYSQL] Export table with computed field to another database causes error

rcdmk posted 6 months ago in General

Hello!

When trying to export a table data from one server to another, if the table has a computed field, the export fails for that table:

"SQL Error (3105): The value specified for generated column 'computed_column_name' in table 'table_name' is not allowed."

The table in question has a column with a simple formula: "AS (columnA + columnB * + columnC)"

Servers: MySQL Community Server (GPL) 5.7.11-log linux x86_64

Steps do reproduce:

  1. Create a simple table with one generated/computed field and insert some data in it
  2. Rightclick the table and select "Export database as SQL"
  3. Choose any option but "No data" in "Data" field
  4. Choose another server or database for "Output"
  5. Choose the destination "Database"
  6. Click "Export"

Thanks

frederico posted 2 weeks ago

Hello!

I run into the same problem.

The problem that HeidiSQL generates the following command during the Export process that contains the virtual column and values as well: INSERT INTO table (col_1, virtual_col_1) VALUES ('value for col 1', 'value for virtual column');

According to this link [7 Feb 2016 23:01] Roy Lyseng: bugs_mysql_com/bug_php?id=80293 (please replace _ characters with . character, I cannot post links...) the insert command should look like this (without the virtual column): INSERT INTO table (col_1) VALUES ('value for col 1');

Roy wrote that this is not a MySQL bug so it must be handled on the client (HeidiSQL) side.

For a workaround I need to use triggers to be able to use the Export functionality.

Can we expect a fix for this problem?

Thanks

ansgar posted 1 week ago

Now that HeidiSQL is at GitHub with a nice ticket system, I'd like you to file a bug report there. Thanks!

Please login to leave a reply, or register at first.