maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Sergei,

On 12/22/2014 02:26 PM, Sergei Golubchik wrote:
> Hi, Alexander!
>
> On Dec 22, Alexander Barkov wrote:
> > Hi Sergei, Kristian,
> >
> > I'm looking at:
> >
> > MDEV-7268 Column of table cannot be converted from type 'decimal(0,?)'
> > to type ' 'decimal(10,7)'
> >
> > This bug cannot be fixed in general case, because the old DECIMAL data
> > type does not write its metadata into the binary log.
> >
> > I suggest we don't fix replication of old DECIMAL
> > (neither binlogging, nor slave-side).
>
> I think that's reasonable. But then CHECK TABLE ... FOR UPGRADE
> should issue a warning for old DECIMAL columns.

Sure.


There are two things ...

1. Maximum precision

The old DECIMAL could have precision up to 255 digits,
so something like this was possible:

  CREATE TABLE t1 (a DECIMAL(200,30));

The new DECIMAL supports only 65 digits.

It's dangerous to fix automatically the columns that are outside of
the new DECIMAL supported range. The DBAs should probably
decide "manually" if they really want to change these columns
to new DECIMAL (with a possible data loss),
or say to CHAR/VARCHAR/TEXT, to preserve all data.

I think that CHECK TABLE..FOR UPDATE should report
the problem with long DECIMALs in a warning.
mysql_upgrade should also warn about the problem,
but should not touch these tables.



2. Suppose mysql_upgrade was not run immediately after upgrade.

A new table can be created using something like this:

CREATE TABLE t1 AS SELECT * FROM t1_with_old_decimal;

or an old table can be ALTERed:

ALTER TABLE t1 ADD new_colunm INT;

In this case, frm version is equal to MYSQL_VERSION_ID
and "CHECK FOR UPGRADE" does not check the column types thoroughly
any more:

>   if (table->s->mysql_version < MYSQL_VERSION_ID)
>   {
>     if ((error= check_old_types()))
>       return error;
>     error= ha_check_for_upgrade(check_opt);
>     if (!error && (check_opt->sql_flags & TT_FOR_UPGRADE))
>       return 0;
>   }

I'm adding detection of old DECIMALS into ha_check_for_upgrade().

That means the old DECIMAL have a chance to spread over the
database again (if the DBA has not run mysql_upgrade immediately after
upgrade).

I guess this is Okey, as the same thing can actually happen
for the other parts of ha_check_for_upgrade():

>   if (table->s->frm_version != FRM_VER_TRUE_VARCHAR)
>     return HA_ADMIN_NEEDS_ALTER;
>
>   if ((error= check_collation_compatibility()))
>     return error;

As the issue quite minor, I'll stick to the existing logic.


> > Instead, we fix "ALTER TABLE ... FORCE" (and thus mysql_upgrade)
> > to force changing old DECIMAL to new DECIMAL.
> > Currently, "ALTER TABLE...FORCE" preserve old DECIMAL.
> >
> > Not sure which version is best to fix in.
> > The complainer had problems with 5.5.
>
> I don't know, I'd say 10.0.
>
> For the complainer there's a workaround - I'm sure you can come up with
> an ALTER TABLE that changes columns from old to new DECIMAL, may be
> something like ALTER TABLE xxx MODIFY COLUMN yyy DECIMAL(....)

Right, that should work.

> Regards,
> Sergei
>
> _______________________________________________
> 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