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_laursen@webyog.com> wrote:
I posted this blog: http://blog.webyog.com/2015/11/09/beware-virtual-columns-may-render-backups-.... 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> >>>> 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 >> >> >