Percona replica showing Slave_SQL_Running: No

[Asif Iqbal]

REPLICA

-------------

[myreplica ~]$ mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1676

Server version: 5.5.49-37.9-log Percona Server (GPL), Release 37.9, Revision efa0073

Copyright (c) 2009-2016 Percona LLC and/or its affiliates

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.197

                  Master_User: replica

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.001258

          Read_Master_Log_Pos: 3457950

               Relay_Log_File: mysqld-relay-bin.000111

                Relay_Log_Pos: 7301

        Relay_Master_Log_File: mysql-bin.001208

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 1726

                   Last_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 7155

              Relay_Log_Space: 1989661780

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 1726

               Last_SQL_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 10

1 row in set (0.00 sec)

mysql> 

MASTER

-------------

Here is relevant output from mysql master

$ mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 94590

Server version: 5.5.50-0ubuntu0.12.04.1-log (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status \G

*************************** 1. row ***************************

            File: mysql-bin.001258

        Position: 3459410

    Binlog_Do_DB: 

Binlog_Ignore_DB: 

1 row in set (0.00 sec)

mysql> 

Need help fixing this issue.

Thanks

[Roel Van de Paar]

Asif, FYI your master is newer then your slave. It's likely not the problem, though it could be. 'Does not support system tables' is where to research further.

[Asif Iqbal]

I could not find anything that explains a fix with Storage engine 'InnoDB' does not support system tables

Should I upgrade Percona Mysql server (slave) to latest ?

When I built the replica there were no issues until recently. I did not make any change in the master mysql database and replica was working just fine.

[roel.va...@gmail.com]

I can also not immediately find a Google reference for InnoDB/sys tables. Have you ever introduced any newer servers (even for testing) into the replication chain?

Normally I would not advice upgrade unless you know there is a fix, or similar, but in this case I doubt if it would be a good idea to try.

[Asif Iqbal]

End up rebuilding the slave.. all looks good now.. lets hope it stays that way

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.197

                  Master_User: replica

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.001264

          Read_Master_Log_Pos: 15580393

               Relay_Log_File: mysqld-relay-bin.000004

                Relay_Log_Pos: 15580539

        Relay_Master_Log_File: mysql-bin.001264

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 15580393

              Relay_Log_Space: 15580739

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 10

1 row in set (0.00 sec)

[migue...@gmail.com]

On Saturday, September 10, 2016 at 1:23:20 AM UTC+10, Asif Iqbal wrote:
> End up rebuilding the slave.. all looks good now.. lets hope it stays that way
>

It will probably happen to you on the next upgrade. I raised a bug report for this months ago:

https://bugs.launchpad.net/percona-server/+bug/1600056

Looks to me like between the releases 5.5.48-rel37.8-1 and 5.5.49-rel37.9-1, something was changed.

Note the error is on 'CREATE TABLE IF NOT EXISTS' and doesn't specify the engine MyISAM, so it probably tries InnoDB first.

What I think is going on is that that query always ran, but only in that 5.5.49 release did Percona/MySQL get strict about reporting that as an error and aborting.

The slave sees the query in the binary logs from the master and tries to run it, and fails.

So hence why it very well might occur on your next upgrade again.

I note that in the release notes [1] for that Percona release, one bug fixed is 'CREATE TABLE ... LIKE ... could create a system table with an unsupported enforced engine. Bug fixed #1540338.' << could be this caused a regression by tightening that logic.

Would be great if the Percona devs could look into it, my bug has gone unanswered for months!

[1] https://www.percona.com/doc/percona-server/5.5/release-notes/Percona-Server-5.5.49-37.9.html

[Asif Iqbal]

Yep it happended again...

mysql> show slave status \G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.197

                  Master_User: replica

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.001281

          Read_Master_Log_Pos: 6540126

               Relay_Log_File: mysqld-relay-bin.000014

                Relay_Log_Pos: 7301

        Relay_Master_Log_File: mysql-bin.001269

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 1726

                   Last_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 7155

              Relay_Log_Space: 138939392

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 1726

               Last_SQL_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 10

1 row in set (0.00 sec)

[migue...@codeenigma.com]

On Monday, 19 September 2016 23:01:49 UTC+10, Asif Iqbal wrote:
> Yep it happended again...

I think it will be fixed on the next release, it's already fixed in Git.

Cheers

[Roel Van de Paar]

Yes, and more specifically in;

Percona Server 5.5.52-38.3  

​Percona Server 5.6.33-78.2  

[Asif Iqbal]

Rebuilt the mysql replication with upgraded percona server 5.5.52-38.3. It has been running for a day and so far no issue.

Thanks all for your help

On Tuesday, September 20, 2016 at 7:02:21 PM UTC-4, Roel Van de Paar wrote:.

[Asif Iqbal]

On Mon, Oct 17, 2016 at 11:02 AM, Asif Iqbal <vad...@gmail.com> wrote:

Rebuilt the mysql replication with upgraded percona server 5.5.52-38.3. It has been running for a day and so far no issue.

Thanks all for your help

Looks like the issue came back.

I am using 

 Server version: 5.5.52-38.3-log Percona Server (GPL), Release 38.3, Revision 109ad68

 ....

 Slave_IO_Running: Yes

 Slave_SQL_Running: No

 ......

 Last_SQL_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''

Should I need to upgrade the percona mysql server?

I am running it on centos 6.8 x86_64

On Tuesday, September 20, 2016 at 7:02:21 PM UTC-4, Roel Van de Paar wrote:.

Yes, and more specifically in;

Percona Server 5.5.52-38.3  

​Percona Server 5.6.33-78.2  

On Tuesday, September 20, 2016 at 3:34:09 PM UTC+10, migue...@codeenigma.com wrote:

On Monday, 19 September 2016 23:01:49 UTC+10, Asif Iqbal  wrote:
> Yep it happended again...

I think it will be fixed on the next release, it's already fixed in Git.

Cheers

--
You received this message because you are subscribed to a topic in the Google Groups "Percona Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/percona-discussion/2eZtWaqTMPM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to percona-discussion+unsub...@googlegroups.com.
To post to this group, send email to percona-discussion@googlegroups.com.
Visit this group at https://groups.google.com/group/percona-discussion.
To view this discussion on the web visit https://groups.google.com/d/msgid/percona-discussion/efb724fe-ceb2-4f72-8f10-8bc9b0e1f2a0%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

[Asif Iqbal]

On Wed, Mar 15, 2017 at 4:29 PM, Asif Iqbal <vad...@gmail.com> wrote:

On Mon, Oct 17, 2016 at 11:02 AM, Asif Iqbal <vad...@gmail.com> wrote:

Rebuilt the mysql replication with upgraded percona server 5.5.52-38.3. It has been running for a day and so far no issue.

Thanks all for your help

Looks like the issue came back.

I am using 

 Server version: 5.5.52-38.3-log Percona Server (GPL), Release 38.3, Revision 109ad68

 ....

 Slave_IO_Running: Yes

 Slave_SQL_Running: No

 ......

 Last_SQL_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''

Should I need to upgrade the percona mysql server?

I am running it on centos 6.8 x86_64

Is there another upgrade available that addressed this?

[migue...@gmail.com]

On Saturday, March 18, 2017 at 2:00:16 AM UTC+11, Asif Iqbal wrote:
>
> Looks like the issue came back.
>
>
> I am using 
>
>
>  Server version: 5.5.52-38.3-log Percona Server (GPL), Release 38.3, Revision 109ad68
>
>
>  ....
>
>  Slave_IO_Running: Yes
>  Slave_SQL_Running: No
>  ......
>
>  Last_SQL_Error: Error 'Storage engine 'InnoDB' does not support system tables. [mysql.servers]' on query. Default database: 'mysql'. Query: 'CREATE TABLE IF NOT EXISTS servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) NOT NULL DEFAULT '', Db char(64) NOT NULL DEFAULT '', Username char(64) NOT NULL DEFAULT '', Password char(64) NOT NULL DEFAULT '', Port INT(4) NOT NULL DEFAULT '0', Socket char(64) NOT NULL DEFAULT '', Wrapper char(64) NOT NULL DEFAULT '', Owner char(64) NOT NULL DEFAULT '', PRIMARY KEY (Server_name)) CHARACTER SET utf8 comment='MySQL Foreign Servers table''
>
>
> Should I need to upgrade the percona mysql server?
>
>
> I am running it on centos 6.8 x86_64
>
>
>
>
> Is there another upgrade available that addressed this?
>

Personally I have not seen the issue re-occur since 5.5.52-38.3. (on Debian)

My upgrades from 5.5.53-38.5 to 5.5.54-38.6 continued to work without issue.


Maybe it's a regression in the CentOS package only?