[Maria-discuss] Problem in replication with NOW('2012-03-07 17:32:03')
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
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Elena, I can't switch to statement based replication that easy. 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? 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Mit freundlichen Grüßen Jan Kirchhoff Leitung Entwicklung / CTO -- logical line GmbH AID Aktieninformationsdienst Hamburger Allee 23 30161 Hannover HRB 56320/AG Hannover Geschäftsführer: Dr. Rüdiger Lemke www.logicalline.com Fon: +49 511 936 208 - 35 Fax: +49 511 936 208 - 11
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Mit freundlichen Grüßen
Jan Kirchhoff Leitung Entwicklung / CTO
Am 12.02.2013 01:26, schrieb Elena Stepanova:
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).
I think that might be some local installation of a very old phpmyadmin (I guess that by the way the query is written). I just have to find the installation and have somebody get rid of it. No messing around with workarounds. Thanks Jan
participants (2)
-
Elena Stepanova -
Jan Kirchhoff