maria-developers team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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