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?

6 Comments

Add yours
  1. 1
    Giuseppe Maxia

    You are comparing apples to oranges.

    The only reason MariaDB is doing what you think is the “the right thing” is because sql_mode is unsafe by default, while in MySQL 5.7 it is ‘strict_all_tables’ by default.

    When using a safe mode, both database servers disallow the insertion of values in virtual columns.
    This is a new feature, and with every new feature that affects DDL statements, the tools must eveolve to deal with that. It has happened for triggers, stored routines, events, partitioning , and it must happen for virtual columns.

  2. 2
    peter_laursen

    @Guiseppe .. SQL-mode does not matter with Oracle/MySQL in this case. It also returns an error (“.. not allowed”) in non-strict mode for this particular INSERT to this particular table (ie. “INSERT INTO `vc_test`.`t1` VALUES (1,3);” with `t1` defined as “CREATE TABLE `vc_test`.`t1`(`id` INT NOT NULL, `id3` INT AS ( id*3 ) VIRTUAL);”).

  3. 4
    Jörg Brühe

    Giuseppe is right: Virtual columns are a new feature.
    Their introduction into 5.7 does not break any existing backup tool. If a MySQL instance uses a new feature, it is the DBA’s responsibility to check whether (old) installed tools can handle it.

    That said, IMO your expectations are wrong: It doesn’t make any sense to “insert” a value for a virtual column. What should happen with an “insert … values (1, 4)” in your example? Should the server reject that, or should it silently ignore the value 4, or what?

    IMNSHO, this is a consequence of the old MySQL design principles to allow almost anything: It has led people to become sloppy. Just think of invalid dates, implicit conversion between numbers and strings, listing a non-grouped column in a “select … group by …”, and similar traditions.
    IMO, the MySQL team at Oracle does it right in dropping such sloppiness and step by step making MySQL more strict (and safe) by default. In MariaDB, Mr Widenius’ influence seems to be too strong for such changes.

  4. 5
    peter_laursen

    “does not break any existing backup tool” is definitely wrong. It breaks LOTS OF tools, that people use for backup. The MariaDB solution does not.

    Also I don’t find references to triggers or invalid dates or whatever relevant in the context. That is really “comparing apples to oranges” IMO.

+ Leave a Comment