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_laur...@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_laur...@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_laur...@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_laur...@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_laur...@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_laur...@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 <i...@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 <i...@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> >>>>>>>>> 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 >>>>>>> >>>>>>> >>>>>> >>>>> >>>> >>> >> >
_______________________________________________ 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
