[Maria-developers] Virtual columns and 'mysqldump' (and similar)
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?
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 | +----+------+
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
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 <mailto: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 | +----+------+
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
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
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
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
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
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
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 > >
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 >> >> >
Hi,
On 6 Nov 2015, at 07:40, 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
I would argue the behaviour here is broken. You should NOT insert into a virtual column. If you do that then something is going to break. This should probably generate an error. If you want this to be allowed then probably you should do something like INSERT IGNORE …. I think Oracle wants to phase that syntax out of MySQL but for a situation like this INSERT IGNORE may make more sense if all column values are provided. I would also expect that mysqldump would only dump the real column values and thus at least generating a dump file from the _same_ major version should not ever generate any issues as the computed values will never be shown/used. Given the way different people may want to use this I’m inclined to think that some sort of session / global variable would be the best way to make the behaviour clearer and explicit. Simon
participants (3)
-
Ian Gilfillan -
Mudd, Simon -
Peter Laursen