----- 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.

...

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.

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.

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

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

----- 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.