NOTHING is inserted to the Virtual Column. The value specified for it is ignored. That is the point!
-- Peter On Mon, Nov 9, 2015 at 11:10 AM, Peter Laursen <peter_laur...@webyog.com> wrote: > 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
