Re: [Maria-developers] Row-based replication of old DECIMAL to new DECIMAL
![](https://secure.gravatar.com/avatar/39b623a1559cf9c69ac3d9d4fb44e7fe.jpg?s=120&d=mm&r=g)
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.
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(....) Regards, Sergei
![](https://secure.gravatar.com/avatar/2cf3bcc01767a358bf4ec20df6dc8dec.jpg?s=120&d=mm&r=g)
Hi, On 22.12.2014 13:26, 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.
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(....)
For the reporter, there is already a workaround -- he can manually perform the operation which pt-online-schema-change failed to finish -- create a new table with the same DDL, populate it, and then switch the tables. /E
Regards, Sergei
![](https://secure.gravatar.com/avatar/114d2706a98c55129bf9e8aefa9e1d0a.jpg?s=120&d=mm&r=g)
----- 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.
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. 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. -- Daniel Black, Complainer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
![](https://secure.gravatar.com/avatar/68ff00c1752bf8aede68a05f77ebd28b.jpg?s=120&d=mm&r=g)
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.
![](https://secure.gravatar.com/avatar/114d2706a98c55129bf9e8aefa9e1d0a.jpg?s=120&d=mm&r=g)
----- Original Message -----
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.
If its easy, I'm keen. I see anything that breaks because of this less important than not knowing that an old DECIMAL exists.
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
Nice, thanks, I hadn't used that option before.
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.
so ALTER TABLE can convert but RBR can't? Is this because of the lack of metadata?
Right, with SBR it should work without problems.
Thanks for the confirmation and the other work you are putting into this issue. -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
![](https://secure.gravatar.com/avatar/68ff00c1752bf8aede68a05f77ebd28b.jpg?s=120&d=mm&r=g)
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
On Dec 22, Alexander Barkov wrote:
Hi Sergei, Kristian,
So, there are a lot of details in that bug and in the discussion, and I did not get a full overview of the problem, or what you were asking from me, but I'll try to answer as best I can. Do I understand correctly that row-based replication writes directly the internal representation of a DECIMAL column into the binlog event, without marking whether it is the old (pre-5.0) or the new format? That indeed seems unfortunate. But I tend to agree that this is not something to try fixing, giving that this is a problem from 4.x days. I would however suggest that in 10.1, we give an error on an attempt to binlog a row-based event with the old-format DECIMAL. The error should explain that the table needs to be converted to the new format to work with row-based replication.
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.
I think that sounds reasonable. I assume that fixing the internal types requires a full table rebuild? Full table rebuild can be extremely painful on large tables. So it is rather dangerous to add that in a minor stable release. For example, the Debian/Ubuntu packages run mysql_upgrade automatically, and minor stable releases arrive as security fixes. So one could argue for fixing in 10.1 from this point of view. Maybe / hopefully, given that this is a pre-5.0 problem, the issue is not _that_ important one way or the other. - Kristian.
![](https://secure.gravatar.com/avatar/68ff00c1752bf8aede68a05f77ebd28b.jpg?s=120&d=mm&r=g)
Hi Kristian, On 12/23/2014 02:08 PM, Kristian Nielsen wrote:
On Dec 22, Alexander Barkov wrote:
Hi Sergei, Kristian,
So, there are a lot of details in that bug and in the discussion, and I did not get a full overview of the problem, or what you were asking from me, but I'll try to answer as best I can.
Do I understand correctly that row-based replication writes directly the internal representation of a DECIMAL column into the binlog event, without marking whether it is the old (pre-5.0) or the new format?
RBR does mark that this is the old pre-5.0 format. so the slave knows that it is an old DECIMAL. But it does not know which exactly DECIMAL(M,N), because M and N are not available.
That indeed seems unfortunate. But I tend to agree that this is not something to try fixing, giving that this is a problem from 4.x days.
I would however suggest that in 10.1, we give an error on an attempt to binlog a row-based event with the old-format DECIMAL. The error should explain that the table needs to be converted to the new format to work with row-based replication.
Good idea. I'd also do the same with the old TIME/DATETIME/TIMESTAMP types with fractional precision, as they have the same problem.
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.
I think that sounds reasonable. I assume that fixing the internal types requires a full table rebuild?
Yes.
Full table rebuild can be extremely painful on large tables. So it is rather dangerous to add that in a minor stable release. For example, the Debian/Ubuntu packages run mysql_upgrade automatically, and minor stable releases arrive as security fixes. So one could argue for fixing in 10.1 from this point of view.
Maybe / hopefully, given that this is a pre-5.0 problem, the issue is not _that_ important one way or the other.
Perhaps we chould fix this in 10.1, and add an error in earlier versions on attempt to RBR-binlog a table with the old DECIMAL.
- Kristian.
![](https://secure.gravatar.com/avatar/68ff00c1752bf8aede68a05f77ebd28b.jpg?s=120&d=mm&r=g)
Hi Kristian, Sergei. What about this plan: 1. In 10.0: a. Make "ALTER TABLE t1 FORCE" fully rebuild the table and change old DECIMAL to new DECIMAL. b. Keep "CHECK TABLE t1 FOR UPGRADE" to still return a line with Msg_type=status and Msg_text=OK, but also add new lines with Msg_type=note and Msg_text telling something like: "The table 'db.table' has deprecated columns types incompatible with Row-based replication." "REPAIR TABLE is recommended. Note, this can take some time.". c. mysql_upgrade will NOT upgrade tables with the old DECIMAL automatically, because they will still be reported as "OK". But mysql_upgrade should detects and prints the new notes in the "CHECK TABLE t1 FOR UPGRADE" output. 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. Also, make "mysqld --binlog-format=row" disallow queries like: "CREATE TABLE t2 AS SELECT old_decimal FROM t1"; 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). Note, one will have to stop binary logging, upgrade the table, and restart mysqld with binary logging again. e. A SELECT from a table with the old DECIMAL should probably also print a warning, to give the user another chance to know about the problem. f. Make INFORMATION_SCHEMA.COLUMNS somehow print information about the deprecated data types (in DATA_TYPE or COLUMN_TYPE or COLUMN_COMMENT). 2. In 10.1 Change "b" to return Msg_type=error and Mst_text= "Table upgrade required. Please do "REPAIR TABLE `tablename`" or dump/reload to fix it!" This will also force mysql_upgrade to rebuild tables with the old DECIMAL automatically. Looks like a lot of work... On 12/23/2014 02:17 PM, Alexander Barkov wrote:
Hi Kristian,
On 12/23/2014 02:08 PM, Kristian Nielsen wrote:
On Dec 22, Alexander Barkov wrote:
Hi Sergei, Kristian,
So, there are a lot of details in that bug and in the discussion, and I did not get a full overview of the problem, or what you were asking from me, but I'll try to answer as best I can.
Do I understand correctly that row-based replication writes directly the internal representation of a DECIMAL column into the binlog event, without marking whether it is the old (pre-5.0) or the new format?
RBR does mark that this is the old pre-5.0 format. so the slave knows that it is an old DECIMAL.
But it does not know which exactly DECIMAL(M,N), because M and N are not available.
That indeed seems unfortunate. But I tend to agree that this is not something to try fixing, giving that this is a problem from 4.x days.
I would however suggest that in 10.1, we give an error on an attempt to binlog a row-based event with the old-format DECIMAL. The error should explain that the table needs to be converted to the new format to work with row-based replication.
Good idea.
I'd also do the same with the old TIME/DATETIME/TIMESTAMP types with fractional precision, as they have the same problem.
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.
I think that sounds reasonable. I assume that fixing the internal types requires a full table rebuild?
Yes.
Full table rebuild can be extremely painful on large tables. So it is rather dangerous to add that in a minor stable release. For example, the Debian/Ubuntu packages run mysql_upgrade automatically, and minor stable releases arrive as security fixes. So one could argue for fixing in 10.1 from this point of view.
Maybe / hopefully, given that this is a pre-5.0 problem, the issue is not _that_ important one way or the other.
Perhaps we chould fix this in 10.1, and add an error in earlier versions on attempt to RBR-binlog a table with the old DECIMAL.
- Kristian.
![](https://secure.gravatar.com/avatar/114d2706a98c55129bf9e8aefa9e1d0a.jpg?s=120&d=mm&r=g)
----- Original Message -----
Hi Kristian, Sergei.
What about this plan:
1. In 10.0:
a. Make "ALTER TABLE t1 FORCE" fully rebuild the table and change old DECIMAL to new DECIMAL.
This and/or the d) alternative should work.
b. Keep "CHECK TABLE t1 FOR UPGRADE" to still return a line with Msg_type=status and Msg_text=OK, but also add new lines with Msg_type=note and Msg_text telling something like: "The table 'db.table' has deprecated columns types incompatible with Row-based replication." "REPAIR TABLE is recommended. Note, this can take some time.".
^ ALTER TABLE...
c. mysql_upgrade will NOT upgrade tables with the old DECIMAL automatically, because they will still be reported as "OK".
But mysql_upgrade should detects and prints the new notes in the "CHECK TABLE t1 FOR UPGRADE" output.
works for me.
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.
Also, make "mysqld --binlog-format=row" disallow queries like: "CREATE TABLE t2 AS SELECT old_decimal FROM t1";
Alternately, is it possible use the new decimal in the created table, convert in the copy process and push the new decimal format into the RBR? Is this the same code path as CREATE TABLE x LIKE y? And will this generate new decimal? I guess the precision reduction warning previous mentioned needs to come into play here. Does new decimal replicate in rbr to old decimal tables ok?
e. A SELECT from a table with the old DECIMAL should probably also print a warning, to give the user another chance to know about the problem.
If this is easy, sure. Can/should this go to the error log too? Might be easier to find.
f. Make INFORMATION_SCHEMA.COLUMNS somehow print information about the deprecated data types (in DATA_TYPE or COLUMN_TYPE or COLUMN_COMMENT).
Running mysqlcheck --all-database --check-upgrade should expose these with c).
2. In 10.1 Change "b" to return Msg_type=error and Mst_text= "Table upgrade required. Please do "REPAIR TABLE `tablename`" or dump/reload to fix it!"
REPAIR -> ALTER
This will also force mysql_upgrade to rebuild tables with the old DECIMAL automatically.
Looks like a lot of work...
Sorry. Such is the nature of inheriting technical debt. Thanks for your detailed examination of this issue. -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
![](https://secure.gravatar.com/avatar/39b623a1559cf9c69ac3d9d4fb44e7fe.jpg?s=120&d=mm&r=g)
Hi, Alexander! On Dec 23, Alexander Barkov wrote:
Hi Kristian, Sergei. What about this plan:
1. In 10.0:
a. Make "ALTER TABLE t1 FORCE" fully rebuild the table and change old DECIMAL to new DECIMAL.
What about DECIMAL(200,30)? What to convert this to? Perhaps that should always require an explicit manual ALTER TABLE t1 MODIFY f1 DECIMAL(N,M) ?
b. Keep "CHECK TABLE t1 FOR UPGRADE" to still return a line with Msg_type=status and Msg_text=OK, but also add new lines with Msg_type=note and Msg_text telling something like: "The table 'db.table' has deprecated columns types incompatible with Row-based replication." "REPAIR TABLE is recommended. Note, this can take some time.".
Why status OK? So that mysql_upgrade would not upgrade them automatically?
c. mysql_upgrade will NOT upgrade tables with the old DECIMAL automatically, because they will still be reported as "OK".
But mysql_upgrade should detects and prints the new notes in the "CHECK TABLE t1 FOR UPGRADE" output.
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.
Also, make "mysqld --binlog-format=row" disallow queries like: "CREATE TABLE t2 AS SELECT old_decimal FROM t1";
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).
Note, one will have to stop binary logging, upgrade the table, and restart mysqld with binary logging again.
I leave it to Kristian...
e. A SELECT from a table with the old DECIMAL should probably also print a warning, to give the user another chance to know about the problem.
Hmm, I'm not sure it's a good idea. SELECT is perfectly safe. And warnings should rather be used sparingly.
f. Make INFORMATION_SCHEMA.COLUMNS somehow print information about the deprecated data types (in DATA_TYPE or COLUMN_TYPE or COLUMN_COMMENT).
Not COLUMN_COMMENT or COLUMN_COMMENT. But DATA_TYPE or - better - EXTRA - that should be ok.
2. In 10.1 Change "b" to return Msg_type=error and Mst_text= "Table upgrade required. Please do "REPAIR TABLE `tablename`" or dump/reload to fix it!" This will also force mysql_upgrade to rebuild tables with the old DECIMAL automatically.
That's kind of ok, but again, how to auto-convert a DECIMAL(200,30) column? Regards, Sergei
![](https://secure.gravatar.com/avatar/e66085296f0242482a6e3b441d551d7e.jpg?s=120&d=mm&r=g)
Howdy,
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. I should hope this strategy is not taken here, and certainly should not be taken as precedent for how to do things in the future.
e. A SELECT from a table with the old DECIMAL should probably also print a warning, to give the user another chance to know about the problem.
Hmm, I'm not sure it's a good idea. SELECT is perfectly safe. And warnings should rather be used sparingly.
Agreed. No warnings on SELECT. Keep in mind that not everyone ignores warnings, and in some environments they may even cause exceptions to be thrown. You should not use warnings as a subversive communication mechanism to the user, but rather as ... a warning. Regards, Jeremy
![](https://secure.gravatar.com/avatar/39b623a1559cf9c69ac3d9d4fb44e7fe.jpg?s=120&d=mm&r=g)
Hi, Jeremy! On Feb 12, Jeremy Cole wrote:
Howdy,
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. 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.
I should hope this strategy is not taken here, and certainly should not be taken as precedent for how to do things in the future.
Regards, Sergei
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
Sergei Golubchik
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. But I think I need an explanation of the original problem to give an informed opinion. 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? Will this break row-based replication between the two servers? 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)? 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.
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. Document that replicating from old DECIMAL to new DECIMAL of different (M2,N2) type is not supported and can cause problems?
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. Hope this helps, - Kristian.
![](https://secure.gravatar.com/avatar/114d2706a98c55129bf9e8aefa9e1d0a.jpg?s=120&d=mm&r=g)
----- Original Message -----
Sergei Golubchik
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.
![](https://secure.gravatar.com/avatar/2553b14447dd4e8df1b636617daca6d6.jpg?s=120&d=mm&r=g)
On Tue, Dec 23, 2014 at 8:17 AM, Alexander Barkov
RBR does mark that this is the old pre-5.0 format. so the slave knows that it is an old DECIMAL.
But it does not know which exactly DECIMAL(M,N), because M and N are not available.
But aren't M and N available on the slave itself? Although tables in replication do not have to be identical, it is reasonable to expect the precision and scale to be the same between source and target tables. - Davi
participants (7)
-
Alexander Barkov
-
Daniel Black
-
Davi Arnaut
-
Elena Stepanova
-
Jeremy Cole
-
Kristian Nielsen
-
Sergei Golubchik