New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL export includes values for virtual fields #5

Closed
FredMK opened this Issue Nov 8, 2017 · 2 comments

Comments

Projects
None yet
2 participants
@FredMK

FredMK commented Nov 8, 2017

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.

@ansgarbecker ansgarbecker added the bug label Nov 8, 2017

@ansgarbecker ansgarbecker changed the title from MySQL SQL Error (3105) during Export SQL to database when table contains virtual field to SQL export includes values for virtual fields Nov 8, 2017

@ansgarbecker

This comment has been minimized.

Show comment
Hide comment
@ansgarbecker

ansgarbecker Nov 8, 2017

Collaborator

Please update to the latest build and give feedback here.

Collaborator

ansgarbecker commented Nov 8, 2017

Please update to the latest build and give feedback here.

@FredMK

This comment has been minimized.

Show comment
Hide comment
@FredMK

FredMK Nov 8, 2017

Working as expected, thank you for the fix!

FredMK commented Nov 8, 2017

Working as expected, thank you for the fix!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment