| Bug #84521 | Workbench Migration Virtual Filed Insert | ||
|---|---|---|---|
| Submitted: | 16 Jan 2017 18:33 | Modified: | 8 Jun 2017 22:09 |
| Reporter: | John More | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Workbench: Migration | Severity: | S2 (Serious) |
| Version: | 6.3 | OS: | Ubuntu ("Ubuntu 14.04.3 LTS") |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | insert, migration | ||
[16 Jan 2017 20:58]
MySQL Verification Team
Please check using the mysql line command tool if you get the same error with that insert. Thanks.
[31 Jan 2017 21:06]
John More
Miguel,
Sorry for the delay in getting back to you.
I have included the terminal dialogue below. I received no error message on either attempt. The second attempt was with a the fully qualified schema and table names.
In both cases, although there was no error message, neither insert worked.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> INSERT INTO TMMsgSequence (sequenceNumber, timestamp, depotId, depotIdText, deviceId) VALUES (101000000, '2016-11-21 21:05:22','1F30E1EF-DA3E-11E5-B267-00271353FF8D', X'1f30e1efda3e11e5b26700271353ff8d', X'00f9152681145dd4877a9abbd
->
-> INSERT INTO DevicesTelemetry.TMMsgSequence (sequenceNumber, timestamp, depotId, defukkypotIdText, deviceId) VALUES (101000000, '2016-11-21 21:05:22','1F30E1EF-DA3E-11E5-B267-00271353FF8D', X'1f30e1efda3e11e5b26700271353ff8d', X'00f915268114
->
[8 May 2017 22:09]
MySQL Verification Team
Thank you for the feedback.
mysql> INSERT INTO TMMsgSequence (sequenceNumber, timestamp, depotId, depotIdText, deviceId) VALUES (101000000, '2016-11-21 21:05:22','1F30E1EF-DA3E-11E5-B267-00271353FF8D', X'1f30e1efda3e11e5b26700271353ff8d', X'00f9152681145dd4877a9abbd
->
Notice above the insert doesn't works because you have not completed the ' ! X'00f9152681145dd4877a9abbd <-----------------incomplete column miss the ' at the end.
[9 Jun 2017 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: The following error log message was generated during a migration attempt from mySql to another instance of mySql. Same versions. `DevicesTelemetry`.`TMMsgSequence` 12:20:26 [INF][ copytable]: Statement execution failed: The value specified for generated column 'depotIdText' in table 'TMMsgSequence' is not allowed. The table definition follows. I do not have the migration generated insert statement. CREATE TABLE `TMMsgSequence` ( `sequenceNumber` int(10) unsigned NOT NULL, `timestamp` datetime DEFAULT NULL, `depotId` binary(16) DEFAULT NULL, `depotIdText` varchar(36) GENERATED ALWAYS AS (insert(insert(insert(insert(hex(`depotId`),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL, `deviceId` binary(16) DEFAULT NULL, PRIMARY KEY (`sequenceNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; How to repeat: create the table using the above create statement insert a record with the following data INSERT INTO TMMsgSequence (sequenceNumber, timestamp, depotId, deviceId) VALUES (101, '2016-11-21 21:05:22', X'1f30e1efda3e11e5b26700271353ff8d', X'00f9152681145dd4877a9abbd720c697') This should work Try inserting a record with the following data INSERT INTO TMMsgSequence (sequenceNumber, timestamp, depotId, depotIdText, deviceId) VALUES (101, '2016-11-21 21:05:22','1F30E1EF-DA3E-11E5-B267-00271353FF8D', X'1f30e1efda3e11e5b26700271353ff8d', X'00f9152681145dd4877a9abbd720c697') This should fail with the following error ERROR: The value specified for generated column 'depotIdText' in table 'TMMsgSequence' is not allowed. Error Code: 3105 Suggested fix: Because MySql Workbench generates the following statement for an insert on the table INSERT INTO `DevicesTelemetry`.`TMMsgSequence` (`sequenceNumber`, `timestamp`, `depotId`, `depotIdText`, `deviceId`) VALUES (<{sequenceNumber: }>, <{timestamp: }>, <{depotId: }>, <{depotIdText: }>, <{deviceId: }>); I believe migration is also generating the same statement. Both should generate insert statements that do not include VIRTUAL fields.