maria-discuss team mailing list archive

[Elena Stepanova <elenst@xxxxxxxxxxxxxxxx>]

Hi Jan,

On 2/12/2013 3:46 AM, Jan Kirchhoff wrote:
> Elena,
> I can't switch to statement based replication that easy.

Even only for the connection(s) that produce the bad statements, if they 
can be easily isolated and modified?
(to row-based replication; statement is what you have now).

> Actually, I
> hope that all that is obsolete if galera works out for me.
> I thought this NOW(timestamp) syntax was a modification coming from the
> binlog somehow to keep the timestamps in sync when using replication,
> but I remember there were some environment variables for that...
> I'll try to figure out where that weird syntax comes from... I am pretty
> sure it is an old phpmyadmin running somewhere...
> Is there an option in 5.2 I could set so the old server refuses that
> syntax at the very beginning?

I'm afraid not... The argument was ignored without as much as a warning, 
so there is no hope for a strict mode or something. At least I can't 
think of any, I hope if anybody has suggestions, they'll jump in.

There is of course slave-skip-errors option which could allow your slave 
to proceed when it hits the error, but it's a potentially dangerous 
configuration, i would only use it as the last resort and only if those 
updates are really unimportant and don't affect further data transformation.


Regards,
Elena

> Thanks
> Jan
>
> > Hi Jan,
> >
> > The function NOW() was never supposed to take a timestamp as an
> > argument, neither in MySQL nor in MariaDB. In earlier versions any
> > argument was ignored. Starting from MariaDB 5.3 (and MySQL 5.6), when
> > microseconds support was implemented, it takes an optional integer
> > argument, which indicates a microsecond precision, so a value which
> > cannot be interpreted as such causes a parsing error.
> >
> > I'd say that the application that produces these statements should be
> > fixed -- depending on where it takes the argument from, best case
> > scenario is that it just does something meaningless, but it's also
> > quite possible that it does not work as expected.
> >
> > If you cannot change the behavior of the application, you might want
> > to consider switching to row-based replication, this way you won't
> > have a problem with the statement on newer servers.
> >
> > Regards,
> > Elena
> >
> >
> > On 2/12/2013 1:56 AM, Jan Kirchhoff wrote:
> > > Hi,
> > >
> > > I set up 2 hosts as a galera cluster, one of them is a slave of my main
> > > database.
> > >
> > > The galera hosts are 5.5.28a-MariaDB-a1~precise-log, all other databases
> > > are
> > >   5.2.12-MariaDB-mariadb115~squeeze-log.
> > >
> > > The 5.5 slave stops with 1064 errors. I figured this is because of
> > > updates using NOW() on timestamp columns, I think they come from a
> > > phpmyadmin frontend.
> > > In the error log the queries look like this:
> > >
> > > UPDATE ..., some_timestamp_field = NOW('2012-03-07 17:32:03') WHERE
> > > ...primary keys...
> > >
> > > when I manually execute the query and just remove the NOW so it is a
> > > simple update some_timestamp_field = '2012-03-07 17:32:03' and then
> > > execute  set global sql_slave_skip_counter=1;slave start; everything is
> > > fine again.
> > >
> > > All 5.2 hosts replicate just fine.
> > >
> > > is this a bug in 5.5?
> > >
> > > Thanks
> > > Jan
> > >
> > >
> > > _______________________________________________
> > > Mailing list: https://launchpad.net/~maria-discuss
> > > Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> > > Unsubscribe : https://launchpad.net/~maria-discuss
> > > More help   : https://help.launchpad.net/ListHelp
>
>
> Mit freundlichen Grüßen
>
> Jan Kirchhoff
> Leitung Entwicklung / CTO