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.