maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Daniel,

On 12/22/2014 03:30 PM, Daniel Black wrote:
> ----- Original Message -----
> > 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.
>
> That would be nice. It took diffing a frm file to suspect it was really old decimal.

Unfortunately there is no an easy way to find the old DECIMAL in
all tables of a database.
Perhaps INFORMATION_SCHEMA should provide a way to report the real 
underlying data type.


To find the old DECIMAL in a single table, one can do the following:

1. start mysql like this:

mysql --column-type-info dbname

2. Run "SELECT * FROM t1"

3. See the "Type" column in the metadata printed



> > > 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.
>
> If this could go in the release notes I'd really appreciate it. I'll try remember to write it there too.
>
> A long running alter table on a production upgrade that won't show up on a test slave (if set up from mysqldump) is a rather ugly surprise.
>
> > > 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.
>
> It was 5.5 as per the complaint.
>
> > 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(....)
>
> Given ALTER TABLE ... FORCE didn't change it I'm not why this would.

ALTER..MODIFY goes through a different execution chain.

The parser returns the new data type when it sees the keyword
"DECIMAL", while the table definition contains the old DECIMAL.
So ALTER notices that the data types are actually different and
recreates the table.


> I can copy/paste a show create table however or use pt-online-schema-change/pt-archiver without
> replication with global binlog_format set to SBR in the mean time and hopefully that covers all the buggy edge cases that trigger rather abrupt production failures.

Right, with SBR it should work without problems.