Beware of ‘virtual columns’ – they may render your backups unusable.

Edit1: With updates in SQLyog 12.1.9 and 12.2.0, SQLyog is now fully safe with Virtual Columns of both MariaDB and MySQL 5.7 (all SQLyog backup-, copy- and sync-tools).

Edit2: There are still problems with mysqldump as of 5.7.11 – refer http://bugs.mysql.com/bug.php?id=80790. I do think it was OK in 5.7.9 so this looks like a regression introduced in 5.7.10 or 5.7.11. But  I may be wrong here. 

 

Virtual columns have existed in MariaDB since version 5.2 and were recently introduced in MySQL 5.7. The recent MySQL 5.7.9 GA release includes this feature.

At first look the implementations in MariaDB and Oracle/MySQL do not seem much different. If you compare documentation here and here you will see only that MariaDB uses the keyword PERSISTENT whereas MySQL uses the keyword STORED for same. There are also some minor differences on how virtual columns may be used in indexes. Those are all the differences you will notice from comparing the documentation of each.

But there is one more and much more important difference – and this simple example illustrates this:

CREATE TABLE `vc_test`.`t1`(
`id` INT NOT NULL,
`id3` INT AS ( id*3 ) VIRTUAL);

INSERT INTO `vc_test`.`t1` VALUES (1,3);

Now .. what will happen is:
1) MariaDB inserts the row and issues a warning “The value specified for computed column ‘id3’ in table ‘t1’ ignored”.
2) MySQL returns an error and nothing is inserted to the table.
(for completeness, the described behavior of MariaDB applies to ‘non-strict’ SQL-mode only – what I think makes little sense).

Further Oracle changed ‘mysqldump’ so that it will now generate INSERT statements like

INSERT INTO `t1` (`id`) VALUES (1);
.. and not as before
INSERT INTO `t1` VALUES (1, 3);

.. so that a dump of tables with virtual columns from 5.7.9 can be restored. However that only applies to the ‘mysqldump’ version shipped with MySQL 5.7.9 – not earlier versions (and on a side-remark, I wonder how much it slows down ‘mysqldump’ to check for virtuality of every column).

Also this behavior breaks ‘restorability’ of backups generated by common GUI tools – including our own SQLyog – with such tables. Probably also phpMyAdmin and similar are now broken. You should verify any backup tool you use, generating backups as SQL scripts, carefully, if you upgrade to MySQL 5.7 and if you plan to use virtual columns. If you are using virtual columns with MariaDB you should ensure that backup scripts generated have set a non-strict sql-mode on top of the script.

I created a bug report about this at http://bugs.mysql.com/bug.php?id=79148. I got the reply that the Oracle solution is compliant with SQL standards. Maybe so, but I find it very thoughtless and unfortunate that MySQL breaks 3rd party tools without any notice to users and vendors and with no transition period.

Is there any community-awareness left in Oracle’s MySQL team at all?