Replication stopped suddenly

[Manoj Chauhan]

Hi All,

We have Percona Server (GPL) Server version: 5.5.16-55 with Master - slave replication. We are using Mysql default replication. Everything was running fine last 2 weeks but suddenly replication  stopped with below errors. Please suggest

Last_SQL_Errno: 1064
Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b` /* generated by server */' at line 1' on query. Default database: 'test'. Query: 'DROP TABLE IF EXISTS `t_dbd_mysql_t1`,`t_dbd_mysql_t11`,`t_dbd_mysql_t2`,`t_dbd_mysqlat2`,`t_dbd_mysql_a'b`,`t_dbd_mysql_a`b` /* generated by server */'


Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b` /* generated by server */' at line 1' on query. Default database: 'test'. Query: 'DROP TABLE IF EXISTS `t_dbd_mysql_t1`,`t_dbd_mysql_t11`,`t_dbd_mysql_t2`,`t_dbd_mysqlat2`,`t_dbd_mysql_a'b`,`t_dbd_mysql_a`b` /* generated by server */'

Thanks in advance
Manoj

[Bill Karwin]

On May 5, 2012, at 8:16 AM, Manoj Chauhan wrote:

Hi All,

We have Percona Server (GPL) Server version: 5.5.16-55 with Master - slave replication. We are using Mysql default replication. Everything was running fine last 2 weeks but suddenly replication  stopped with below errors. Please suggest

Last_SQL_Errno: 1064
Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b` /* generated by server */' at line 1' on query. Default database: 'test'. Query: 'DROP TABLE IF EXISTS `t_dbd_mysql_t1`,`t_dbd_mysql_t11`,`t_dbd_mysql_t2`,`t_dbd_mysqlat2`,`t_dbd_mysql_a'b`,`t_dbd_mysql_a`b` /* generated by server */'

It looks like you tried to drop a table whose name contains a literal back-tick character.  

You can drop this table successfully on the master like this:

DROP TABLE IF EXISTS `t_dbd_mysql_a``b`;

That is, double the back-tick and it will be interpreted as a single literal back-tick.  Otherwise it's interpreted as the termination of the identifier.

However, apparently statement-based replication doesn't support this.  It tries to execute the following on the slave:

DROP TABLE IF EXISTS `t_dbd_mysql_a`b`;

And that's a syntax error.  The second back-tick terminates the table identifier, and the remaining two characters ( b` ) are just garbage.

This is already reported as a bug in current MySQL 5.1/5.5.  It's currently "in progress"

http://bugs.mysql.com/bug.php?id=57873

But you can work around this bug more simply, by avoiding back-tick characters in your table names.

Regards,

Bill Karwin

[Manoj Chauhan]

Hi Karwin

Thanks for swift response. But we are not using this table `t_dbd_mysql_a`b`; and we have not tried to delete the table.

Thanks
 Manoj 

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

[Bill Karwin]

The slave would not have tried to execute that DROP TABLE statement unless the statement had run on the master without error.  That's the only normal way the statement would appear in the binary log.  So some client must have run that statement successfully.  Therefore we can conclude that the table must have existed (at least on the master).

Regards,

Bill Karwin

[Bill Karwin]

One more thought about this:

The tables in that DROP TABLE statement all start with "t_" which makes me think they're probably temporary tables. You could have an application or framework on your master that creates a number of temporary tables in the course of doing its work, and then cleans them up later. So it could be that no one is *deliberately* creating those tables, but some client code is creating them.

If the code is generating the table names according to some algorithm, the algorithm needs to be fixed so that it doesn't use punctuation characters.

Regards,
Bill Karwin

[Manoj Chauhan]

Thank you very much.

-Manoj

[Manoj]

Hi Bill Karwin

We checked with developer none of our code generates any temporary
tables. We google for "DROP TABLE IF EXISTS `t_dbd_mysql_a``b`;"
then we would find its some internal table by mysql or perconna.

http://lists.mysql.com/commits/20948

Please suggest

Thanks
Manoj

On May 6, 9:26 am, Manoj Chauhan <mchauha...@gmail.com> wrote:
> Thank you very much.
>
> -Manoj
>

[Justin Swanhart]

Hi,

It looks like the commit that you linked to is part of the test suite
for the Perl DBD interface. Perhaps someone ran a set of DBD tests
against your master database, probably when installing DBD or
DBI::MySQL.

From the test:
+ ok($dbh->do(qq{DROP TABLE IF EXISTS t_dbd_mysql_t1, t_dbd_mysql_t11,
+ t_dbd_mysql_t2, t_dbd_mysqlat2,
+ `t_dbd_mysql_a'b`,
+ `t_dbd_mysql_a``b`}),
+ "cleaning up");
+ ok($dbh->do(qq{CREATE TABLE t_dbd_mysql_t1 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE t_dbd_mysql_t11 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE t_dbd_mysql_t2 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE t_dbd_mysqlat2 (a INT)}) and
+ $dbh->do(qq{CREATE TABLE `t_dbd_mysql_a'b` (a INT)}) and
+ $dbh->do(qq{CREATE TABLE `t_dbd_mysql_a``b` (a INT)}),
+ "creating test tables");

--Justin

[Eric Cope]

It sounds like a SQL injection. Is that possible?