maria-developers team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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_laursen@xxxxxxxxxx>
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@xxxxxxxxxx>
> 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@xxxxxxxxxx>
>> 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@xxxxxxxxxx>
>>> 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@xxxxxxxxxx> 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@xxxxxxxxxx> 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@xxxxxxxxxxx>
>>>>>> 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@xxxxxxxxxxx>
>>>>>>> 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@xxxxxxxxxxxxxxxxxxx
>>>>>>> Unsubscribe : https://launchpad.net/~maria-developers
>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>