← Back to team overview

maria-developers team mailing list archive

Re: Row-based replication of old DECIMAL to new DECIMAL

 


----- Original Message -----
> Sergei Golubchik <serg@xxxxxxxxxxx> writes:
> 
> > >     Make it print an error:
> > >     "The table 'db.table' has deprecated columns types incompatible
> > >     with Row-based replication."
> > > 
> > >     (the same text with #b, but this time an error instead of a note).
> > > 
> > 
> > I leave it to Kristian...
> 
> First, note that this is not just --binlog-format=row, if I understand
> correctly. It also seems that --binlog-format=mixed could be affected, if
> some
> particular logging decides to use row-based for a query.

Yes. That was the case.

> But I think I need an explanation of the original problem to give an informed
> opinion.

MDEV-7268


> What happens if for example I take a mysqldump from a table with old DECIMAL
> and apply it on a new version server? Will it create a table with new
> DECIMAL?

It dumps out as DECIMAL and gets recreated as (NEW) Decimal. That's how I initialised the slave in question.

> Will this break row-based replication between the two servers?

Once it got to the situtation of using RBR.

> Or does the problem only happen if eg. replicating between different column
> definitions? Eg. if replicating from old DECIMAL(10,2) to new DECIMAL (20,4)?

Both definations are the same.

> In general, it seems that the first scenario would be pretty bad breakage,
> and something to avoid.
> 
> But having different table definitions on master and slave is a different
> matter, I think, if the user explicitly choose to do this. I would say this
> is not something that is officially supported, even if we sometimes try to make
> it work. So not a top priority to fix. Though if the different definitions
> can somehow arise on its own, due to mysql_upgrade or the like, the matter is of
> course more serious.

The show create table makes them look the same even though they aren't. mysql_upgrade doesn't do it and I'm in favour of a warning with and OK like previously suggested. I don't think mysql_upgrade is expected to do large alter table commands on anything other than system tables.

Alexander pointed me at few tips on displaying the differences.

> >> > d. Make the master running with --binlog-format=row refuse to do any
> >> > >  INSERT/UPDATE/ALTER or any other queries that can modify a table
> >> > >  with the old DECIMAL.
> >> 
> >> Note that currently such DECIMAL columns work just fine with RBR, so
> >> long as the master and slave have the same internal types. So
> >> completely disallowing DECIMAL with RBR seems like a breaking and
> >> unnecessary change to something which apparently works fine for the
> >> user already.
> >
> > Right. That's why I didn't particularly like that idea. Because quite
> > often old DECIMAL works just fine with RBR.
> 
> Right.
> 
> So what is the problem with this: on the slave, if we get a row event for an
> old DECIMAL column, assume it has the same definition (DECIMAL(M1,N1)) as the
> one in the slave table.

I'd be happy with this. Adding this functionality isn't going to cause a compatibility problem as its an improvement on the current break replication situtation.

> Document that replicating from old DECIMAL to new
> DECIMAL of different (M2,N2) type is not supported and can cause problems?

I'm happy with this.

> > On the other hand, the master cannot know whether tables on the slave
> > are compatible with old DECIMAL or not. And by the time the replicated
> > row events reach the slave it's too late to stop them - replication is
> > already broken.
> >
> > At the moment I, frankly, cannot think of any satisfactory solution for
> > this.
> 
> Well, what about introducing new row events? And these new row events contain
> the necessary information for the slave to handle matters correctly?
> 
> But, as from above, I'm not sure if that much effort is warranted.

If the new row event just adds the precision of the old decimal it does seem like a bit too much effort.

> Hope this helps,

Likewise.




-- 
-- 
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.


References