none
Creating a replica from a recently created master does not copy the whole stored procedure in mysql schema/database RRS feed

  • Question

  • Hi Guys,

    I am not sure if this is a bug or just by design but it looks to be a bug in my end. So basically, I created a master node (or single node). After that, I created a replica. However, I was surprised when I run this,

    MySQL [mysql]> CALL mysql.az_replication_stop;

    ERROR 1305 (42000): PROCEDURE mysql.az_replication_stop does not exist

    MySQL [mysql]> CALL mysql.az_replication_start;

    ERROR 1305 (42000): PROCEDURE mysql.az_replication_start does not exist

    When I check the mysql schema for its stored procedure, it does not exist on this replica/slave:

    MySQL [mysql]> show procedure status  where `Db`='mysql' and name like 'az_repl%'\G

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

                      Db: mysql

                    Name: az_replication_restart

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:23

                 Created: 2019-10-04 13:55:23

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to restart the replication.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    1 row in set (0.25 sec)

    Definitely, this is a slave:

    MySQL [mysql]> pager egrep -i 'sec|master_(host|user)'; show slave status \G

    PAGER set to 'egrep -i 'sec|master_(host|user)''

                      Master_Host: xxxxx-master.mysql.database.azure.com

                      Master_User: az00f950e0bfc062@s9s-master

            Seconds_Behind_Master: 0

    1 row in set (0.25 sec)

    While in the master, the stored procedures looks complete:

    MySQL [(none)]> show procedure status  where `Db`='mysql' and name like 'az_repl%'\G

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

                      Db: mysql

                    Name: az_replication_change_master

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:25

                 Created: 2019-10-04 13:55:25

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to change the master.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    *************************** 2. row ***************************

                      Db: mysql

                    Name: az_replication_remove_master

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:25

                 Created: 2019-10-04 13:55:25

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to remove the master.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    *************************** 3. row ***************************

                      Db: mysql

                    Name: az_replication_restart

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:23

                 Created: 2019-10-04 13:55:23

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to restart the replication.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    *************************** 4. row ***************************

                      Db: mysql

                    Name: az_replication_skip_counter

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:25

                 Created: 2019-10-04 13:55:25

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to skip one event from the master.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    *************************** 5. row ***************************

                      Db: mysql

                    Name: az_replication_start

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:25

                 Created: 2019-10-04 13:55:25

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to start the replication.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    *************************** 6. row ***************************

                      Db: mysql

                    Name: az_replication_stop

                    Type: PROCEDURE

                 Definer: azure_superuser@localhost

                Modified: 2019-10-04 13:55:25

                 Created: 2019-10-04 13:55:25

           Security_type: DEFINER

                 Comment: Azure MySQL stored procedure to stop the replication.

    character_set_client: utf8

    collation_connection: utf8_general_ci

      Database Collation: utf8mb4_0900_ai_ci

    6 rows in set (0.24 sec)

    How can that be happen? Or something I missed? I can't find anything in the docs but this looks a bug.


    Friday, October 4, 2019 4:12 PM

Answers

  • I believe this answers my question:

    Stopped replicas
    If you stop replication between a master server and a read replica, the stopped replica becomes a standalone server that accepts both reads and writes. The standalone server can't be made into a replica again.

    So basically, the replica created under the portal cannot be administered otherwise if I have to administer it like the way to stop/start replica should follow the "Configure data-in replication" manual, right? Can someone confirm this.

    I wanted to expound as well, I realized that after creating a MariaDB from MySQL replica earlier, it's still the same result and it seems this is the default behavior if you create a replica under the portal. So the only I can do with my replica is to be only the replica and nothing else.

    • Marked as answer by geekgogie Friday, October 4, 2019 6:18 PM
    Friday, October 4, 2019 6:18 PM

All replies

  • Might try asking for help over here.

    https://forums.mysql.com/

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    Friday, October 4, 2019 4:36 PM
    Moderator
  • I believe this answers my question:

    Stopped replicas
    If you stop replication between a master server and a read replica, the stopped replica becomes a standalone server that accepts both reads and writes. The standalone server can't be made into a replica again.

    So basically, the replica created under the portal cannot be administered otherwise if I have to administer it like the way to stop/start replica should follow the "Configure data-in replication" manual, right? Can someone confirm this.

    I wanted to expound as well, I realized that after creating a MariaDB from MySQL replica earlier, it's still the same result and it seems this is the default behavior if you create a replica under the portal. So the only I can do with my replica is to be only the replica and nothing else.

    • Marked as answer by geekgogie Friday, October 4, 2019 6:18 PM
    Friday, October 4, 2019 6:18 PM