← Back to team overview

maria-developers team mailing list archive

Re: Virtual columns and 'mysqldump' (and similar)

 

Hi,

> On 6 Nov 2015, at 07:40, 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

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

References