planetscale/cli

[Bug] pscale database dump / restore-dump commands do not correctly ignore GENERATED STORED columns for INSERTs

orware opened this issue · 0 comments

orware commented

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:
image