I posted this blog: http://blog.webyog.com/2015/11/09/beware-virtual-columns-may-render-backups-unusable/.  It should appear on planet.mysql.com shortly. 

-- Peter

On Mon, Nov 9, 2015 at 10:17 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
YES .. it is sql_mode that makes the difference as I realized Friday night here..



CREATE TABLE `vc_test`.`t1`(  
  `id` INT NOT NULL,
  `id3` INT AS ( id*3 ) VIRTUAL
);
SET sql_mode = 'strict_all_tables';
INSERT INTO `vc_test`.`t1` VALUES (1,3);
-- returns: Error Code: 1906 - The value specified for computed column 'id3' in table 't1' ignored
SELECT * FROM t1;
-- returns empty set 

SET sql_mode = '';
INSERT INTO `vc_test`.`t1` VALUES (1,3); -- success
SHOW WARNINGS;
-- 1906  The value specified for computed column 'id3' in table 't1' ignored 

SELECT * FROM t1;
-- returns data 



Is this intentional or an oversight? Either the INSERT should be allowed in strict mode as well or the error message should be changed. Right? 


-- Peter

On Sat, Nov 7, 2015 at 8:28 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
Stupid oversight on my side: it probably depends on sql-mode. I will check on Monday.

But if this is desired behavior (what I don't think) then at least the error message is wrong and confusing in strict mode.

-- Peter

On Fri, Nov 6, 2015 at 10:53 AM, Peter Laursen <peter_laursen@webyog.com> wrote:
There are updates in the bug report (http://bugs.mysql.com/bug.php?id=79148).  Those who are interested in the subject should read it. 

Unfortunately Oracle chose a client-side and not server-side solution to this problem. As a consequence 'mysqldumps' from Oracle/MySQL will restore in MariaDB, but not vice versa. And dumps generated by (probably) all other clients than mysqldump will now fail to restore on Oracle/MySQL if table has virtual columns.

I also would be surprised if replication between a MySQL and MariaDB server is not affected in some scenarios.

This is at least what I can understand.

-- Peter

On Fri, Nov 6, 2015 at 1:41 PM, Peter Laursen <peter_laursen@webyog.com> wrote:
Further research: 



It does not work with the .msi package for Windows:


select version(); 
-- 10.1.8-MariaDB  

CREATE TABLE `t1_virtual_uk` (
  `f1` int(11) DEFAULT NULL,
  `gc` int(11) AS (f1 + 1) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `t1_virtual_uk` values (1,2);
-- Error Code: 1906 The value specified for computed column 'gc' in table 't1_virtual_uk' ignored

select * from `test`.`t1_virtual_uk`; 
-- empty set


It does work on Windows using the .zip package (simply starting server with"mysqld --port = xxxx"), however. 


So the problem seems to be a packaging issue with the .msi. Some old/wrong code seems to go in there. 


-- Peter

On Fri, Nov 6, 2015 at 12:21 PM, Peter Laursen <peter_laursen@webyog.com> wrote:
Correction .. there is no error (except for my copy-paste error). This is perfect and exactly as I think it should be. 



SELECT VERSION(); -- 10.1.8-MariaDB 

CREATE DATABASE vctest;
USE vctest;

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

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

SHOW WARNINGS;
-- 1906  The value specified for computed column 'id3' in table 't1' ignored

SELECT * FROM t1;
/*
    id     id3  
------  --------
     1         3
*/

On Fri, Nov 6, 2015 at 12:10 PM, Peter Laursen <peter_laursen@webyog.com> wrote:
yup .. this is good!

SELECT VERSION(); -- 10.1.8-MariaDB 

CREATE DATABASE vctest;
USE vctest;

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

INSERT INTO `t1` VALUES (1,3);
-- retruns: Error Code: 1906 - The value specified for computed column 'id3' in table 't1' ignored

SHOW WARNINGS;
-- 1906  The value specified for computed column 'id3' in table 't1' ignored

SELECT * FROM t1;
/*
    id     id3  
------  --------
     1         3
*/

On Fri, Nov 6, 2015 at 11:52 AM, Ian Gilfillan <ian@mariadb.org> wrote:
The insert statement returns a warning:

INSERT INTO `test`.`t1` VALUES (1,3);
Query OK, 1 row affected, 1 warning (0.07 sec)

Warning (Code 1906): The value specified for computed column 'id3' in table 't1' ignored



On 06/11/2015 08:09, Peter Laursen wrote:
OK, I should have upgraded. But I am currently travelling an with a small laptop only and an Internet connectivity not fit for downloads (call it a bad excuse if you want! :-) ) 

So it actually INSERTS. That is nice. But does it return an error or warning or nothing? It should not be an error IMO as various clients would 'abort on error' and flood its log with error messages.

-- Peter

On Fri, Nov 6, 2015 at 11:32 AM, Ian Gilfillan <ian@mariadb.org> wrote:

06/11/2015 07:51, Peter Laursen wrote:
I reported this bug report to Oracle: http://bugs.mysql.com/bug.php?id=79148

It is almost the same in MariaDB - but the error message is different, see

SELECT VERSION(); -- 10.1.2-MariaDB-log

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

INSERT INTO `vc_test`.`t1` VALUES (1,3);
-- retruns: Error Code: 1906 - The value specified for computed column 'id3' in table 't1' ignored

SELECT * FROM t1;
-- returns empty set


So here the error message is that "value is ignored" (not that it is "not allowed").  It looks to me like somebody in MariaDB actually identified the problem, but forgot to finish things.

The statement should succeed (maybe raise a warning), and the "specified value should be ignored" as the error message says, but what it in reality is not.

What say?

In 10.1.8, the above returns:
SELECT * FROM t1;
+----+------+
| id | id3  |
+----+------+
|  1 |    3 |
+----+------+



_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp