maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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.