Phpmyadmin: Bad table export in case of generated columns

Created on 27 Apr 2016 · 2Comments · Source: phpmyadmin/phpmyadmin

Steps to reproduce

Assume we have generated column idGenerated in table1 and phpmyadmin 4.6.0 exports it as:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `idGenerated` int(11) GENERATED ALWAYS AS ((`id` > 10)) STORED
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table1` (`id`, `idGenerated`) VALUES
(1, 0),
(11, 1);

When importing the data, database says:
#3105 - The value specified for generated column 'idGenerated' in table 'table1' is not allowed.

Looks very close to #11909.

MySQL 5.7.11

bug

Source

glintik

All 2 comments

Similar issue with generated field: https://github.com/phpmyadmin/phpmyadmin/issues/12518

nijel on 30 Aug 2016

👍1

Yes, same issue, but still not fixed... Currently working on a development- and production-server, but this way i'm unable to export / import data from one to another.

It's unable to backup a table by copying them to 'table1_backup' for example, so I can remove the generated columns from 'table1' to make sure the table is exportable...
It's unable to export selected columns from a table. An export is already the complete table or nothing...

So at this point, the only way to import / export something is to delete all generated columns from my table. Export on server A, import on server B and adding the generated columns (and indexes onto it) back to both tables so our queries will work.

Meanwhile, I have an '.txt'-file in my desktop with all queries which needs to be executed before and after an import, so I can copy-paste it everytime I need to migrate some data, but that's far from ideal. Please fix this!