Expected behavior

Exporting SQL to other database without error when table contains virtual column.

Current behavior

MySQL SQL Error (3105) error thrown during SQL export to other database when table contains virtual column.

Failure information (for bugs)

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

Snippet for column: is_live TINYINT(1) AS (if(isnull(deleted_at),1,0)) VIRTUAL,

Steps to reproduce

Please provide detailed steps for reproducing the issue.

1. Create a simple table with one generated/computed field and insert some data in it
2. Right-click 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"
7. See error message

Context

• HeidiSQL version: 9.4.0.5185
• Database system + version: MySQL 5.7.18
• Operating system: Windows

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

Possible solution

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 Roy Lyseng 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.

Workaround

Use triggers instead of virtual columns.

Failure Logs

MySQL exception thrown.