maria-developers team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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@xxxxxxxxxx>
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_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
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>