maria-developers team mailing list archive
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
> 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
> 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
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.
> 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,
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.