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 > >