← Back to team overview

maria-developers team mailing list archive

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

 


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


References