Row-based replication of old DECIMAL to new DECIMAL

There was a discussion on maria-developers@ in December 2014 to February 2015 about how to solve the problem with RBR replication of the old DECIMAL. See MDEV-7268 and MDEV-7269 for the problem details.

This is a super-task to solve most of the problems.
Current status: waiting for architecture review.

I. The ideas that most participants agreed on:

1. Make "ALTER TABLE t1 FORCE" fully rebuild the table and change old DECIMAL to new DECIMAL

Target version: 10.0

This change will give DBA a way to upgrade desired tables manually.

This will not touch old DECIMAL(M,N) that do not fit into the new DECIMAL, i.e. those with M>65 and/or N>30, e.g. DECIMAL(200,30). The old DECIMAL column will still require an explicit manual:

ALTER TABLE t1 MODIFY f1 DECIMAL(M,N);

2. Add warnings about the old DECIMAL into mysql_upgrade output

Target version: 10.0

This change will give DBA a way to know about the old DECIMAL columns at upgrade time.

We'll 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.

mysql_upgrade will NOT upgrade tables with the old DECIMAL automatically, because they will still be reported as "OK". But mysql_upgrade will detect and print the new notes from the "CHECK TABLE t1 FOR UPGRADE" output.

3. DONE: Make INFORMATION_SCHEMA.COLUMNS somehow print information about the deprecated data types

This change was done in 5.5.44 by:
MDEV-8267 Add /*old*/ comment into I_S.COLUMN_TYPE for old DECIMAL

Target version: 10.0 or even 5.5

Daniel B. wrote:

I see anything that breaks because of this less important than not knowing that an old DECIMAL exists.

Possible columns that can be used for this purpose:

• COLUMN_TYPE
• DATA_TYPE
• EXTRA
• A new column "DATA_TYPE_ID INT NOT NULL",
  which will report Field::real_type().

Sergei thinks that using COLUMN_TYPE for this purpose as a bad idea, but DATA_TYPE or EXTRA should be fine:

Is it safe to change the meaning of DATA_TYPE from "type" to "real_type"? Might be ok in a major release. As far as I can see, DATA_TYPE currently is the same as COLUMN_TYPE without parentheses. So it is redundant and doesn't provide any new information.

Bar thinks that DATA_TYPE_ID will be needed soon for the pluggable data types anyway, so votes for DATA_TYPE_ID. It should be safe to add DATA_TYPE_ID into 10.0 or even 5.5.

4. Make the slave with the new DECIMAL assume that the old DECIMAL on the master has the same scale and precision.

Target version: 10.0 or 10.1

This idea was proposed by Davi A. and Kristian N.:

Davi A. wrote:

... M and N are 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.

Kristian N. wrote:

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

5. Make mysql_upgrade change the old DECIMAL to the new DECIMAL automatically

Target version: 10.2

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.
Again, columns that do not fit into the new DECIMAL, e.g. DECIMAL(200,30) should not be touched by automatic updrage.

Kristian N. wrote:

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.

II. The ideas that were rejected during the discussion:

1. Refuse binary logging for the old DECIMAL

There was an idea to make the master running with --binlog-format=row refuse any INSERT/UPDATE/ALTER or any other queries that can modify a table with the old DECIMAL, as well as create old DECIMAL columns:

CREATE TABLE t2 AS SELECT old_decimal FROM t1;

But, as Jeremy C. and Sergei G. noticed, this will break replication when both master and slave use old DECIMAL columns of exactly the same precision and scale. Currently RBR replication works without any problems in this scenario, and we should not break this.

So will not go this way.

2. Extend binary log to write metadata for the old DECIMAL

There was an idea to introduce a new row event, or a new type code (e.g. MYSQL_TYPE_OLD_DECIMAL_WITH_METADATA). But perhaps this would be too much efforts for a deprecated data type. This change is currently not planned for any releases.