[Bug] pscale database dump / restore-dump commands do not correctly ignore GENERATED STORED columns for INSERTs
orware opened this issue · 0 comments
We had a ticket arrive today that provided an example that demonstrated that our current pscale database dump
/ pscale database restore-dump
commands aren't correctly ignoring GENERATED
columns that are also STORED
.
To reproduce this issue, within a test database you can use the following simplified example table definition:
CREATE TABLE `stock_trades` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`unit_price` decimal(13,4) DEFAULT NULL,
`quantity` decimal(13,4) DEFAULT NULL,
`total_value` decimal(15,4) GENERATED ALWAYS AS ((`quantity` * `unit_price`)) STORED,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
And example rows:
INSERT INTO `stock_trades`(`id`,`unit_price`,`quantity`) VALUES
(1,12.1000,2.1000),
(2,74.3200,99.0000),
(3,56.4000,34.0000),
(4,230.3100,22.0000),
(5,133.3300,3.0000),
(6,0.0100,104.0000),
(7,11.2000,12.0000),
(8,131.4400,55.0000),
(9,18.8000,34.0000),
(10,99.2000,2.0000);
And you can observe that if you run a SELECT * from stock_trades;
query from the web console that you can see the generated total_value
column included.
If you run a pscale database dump
command though, the generated schema file for the table above is ok.
However, the file that inserts the rows back into the table is not ok because it includes the stored GENERATED
column as if it is a normal column:
INSERT INTO `stock_trades`(`id`,`unit_price`,`quantity`,`total_value`) VALUES
(1,12.1000,2.1000,25.4100),
(2,74.3200,99.0000,7357.6800),
(3,56.4000,34.0000,1917.6000),
(4,230.3100,22.0000,5066.8200),
(5,133.3300,3.0000,399.9900),
(6,0.0100,104.0000,1.0400),
(7,11.2000,12.0000,134.4000),
(8,131.4400,55.0000,7229.2000),
(9,18.8000,34.0000,639.2000),
(10,99.2000,2.0000,198.4000);
If you attempt to use pscale database restore-dump
after this into another branch using the optional --overwrite-tables
flag, or into a separate database, you will receive the following error message:
Starting to restore database <DATABASE_NAME> from folder <DUMP_FOLDER_LOCATION>
⠴ Restoring database ...2023-03-30T22:09:58-07:00 error error restoring {"error": "target: <DATABASE_NAME>.-.primary: vttablet: rpc error: code = Unknown desc = The value specified for generated column 'total_value' in table 'stock_trades' is not allowed.
As part of my testing process I created a quick variation with a virtual GENERATED
column and observed that the same issue does not occur because in that case the file containing the INSERT
statements does not include the total_value
column.
I also observed that for at least one alternative tool, DBeaver, creating a database export is completed correctly in both the virtual or stored cases and the INSERT
statements there didn't accidentally include the total_value
column in the stored GENERATED
case.
Looking at our CLI code for dumper.go
, within the dumpTable
function there is a call to the generatedFields
method, so this may only require a small correction to that helper function as there may be an incorrect assumption being made on the following lines:
https://github.com/planetscale/cli/blob/main/internal/dumper/dumper.go#L420-L424
Since for the stored GENERATED
column example here, it shows a value of STORED GENERATED
within the Extra
column shown below, which I believe is preventing it from being excluded properly: