Bug Report: MySQL INSERT statement includes Generated Columns

Toad Data Point & Toad Intelligence Central
1

I have some data sync for a table with generated columns.

file_path, file_url are actually generated columns based on the previous columns in the same table.

But Toad is generating this migration script, which is failing on execution.

INSERT INTO `www-anrev-org`.`storage_file` (`file_id`, `file_created`, `file_modified`, `file_modified_by`, `file_drive_name`, `file_path_prefix`, `file_url_prefix`, `file_key`, `file_path`, `file_url`, `file_extension`)
VALUES (1, '2017-09-15 03:29:03', '2017-09-15 03:29:03', NULL, 'default', 's3://bs-web-01/default/{$fileKey}', 'https://bs-web-01.s3.amazonaws.com/default/{$fileKey}', 'test.csv', 's3://bs-web-01/default/test.csv', 'https://bs-web-01.s3.amazonaws.com/default/test.csv', 'csv');

Would you please kindly confirm and see if a fix can be applied?

Thank you very much for your kind attention!

2

Hi thomas,
Could you provide more details to reproduce your issue. I could not reproduce your issue.

Regards
Lyla Su

1 Like
3

Consider this structure. Let’s assume source and target DB has the same structure.

CREATE TABLE IF NOT EXISTS `storage_drive` (
  `drive_id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  `drive_key` varchar(510) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `drive_path_template` varchar(510) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `drive_path_prefix` varchar(510) COLLATE utf8mb4_unicode_520_ci GENERATED ALWAYS AS (replace(`drive_path_template`,'{$driveKey}',`drive_key`)) STORED,
  PRIMARY KEY (`drive_id`),
  KEY `drive_path_prefix` (`drive_path_prefix`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Source DB has this record (run this query on source DB)

INSERT INTO `storage_drive` (`drive_id`, `drive_key`, `drive_path_template`) VALUES (NULL, 'cool key', 'tempate and {$driveKey}')

Now compare on Toad Data Point, and you will get this query for Target DB migration script:

/*
Script created by Quest Software Inc. Data Compare at 2018/10/22 13:05:27
This script must be executed against toad-test-target on localhost
This script applies changes to toad-test-target to make it the same as toad-test on localhost
*/

USE `toad-test-target`;

START TRANSACTION;

INSERT INTO `toad-test-target`.`storage_drive` (`drive_id`, `drive_key`, `drive_path_template`, `drive_path_prefix`)
VALUES (4, 'cool key', 'tempate and {$driveKey}', 'tempate and cool key');

COMMIT;

Instead, I expect this:

/*
Script created by Quest Software Inc. Data Compare at 2018/10/22 13:05:27
This script must be executed against toad-test-target on localhost
This script applies changes to toad-test-target to make it the same as toad-test on localhost
*/

USE `toad-test-target`;

START TRANSACTION;

INSERT INTO `toad-test-target`.`storage_drive` (`drive_id`, `drive_key`, `drive_path_template`)
VALUES (4, 'cool key', 'tempate and {$driveKey}');

COMMIT;

(Should not have “drive_path_prefix” in there, because it is a generated column)

4

This is a provider specific usecase. In this case you would export the data as rows and import the rows. This way there is no need to generate the inserts. Try this from the import export window.

image
image.png1025×691 22.1 KB

5

Hello Debbie,

Thanks for your response. However, the above is only an example use-case to show what is happening. In our production, we have a large number of tables like this, and we would need to compare the two schemas to generate such an upgrade file, because we do not know which tables have difference in the data.

In fact, the “Compare” function is really the biggest reason why we need to choose Toad Data Point for.
If I’m just exporting and importing, I can do the same thing on phpMyAdmin very easily.

Thank you very much for your kind assistance and prompt response Debbie, and I look forward to your reply.

6

I have enter a jira number(QAT-13694) for our dev to look at.

Regards
Lyla Su

1 Like