SHOW SLAVE STATUS in stored procedure

I'm fairly new to mysql stored procedures and I need to be able to access the results of SHOW SLAVE STATUS from within the procedure. I searched the web but can't seem to be able to find it. This is the kind of thing I've been trying:

    CREATE PROCEDURE test()
BEGIN
 DECLARE a VARCHAR(1000);
 DECLARE cur1 CURSOR FOR SHOW SLAVE STATUS;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 OPEN cur1;

 read_loop: LOOP
  FETCH cur1 INTO a;
  IF done THEN
   LEAVE read_loop;
  END IF;
 END LOOP;

 SELECT a;

 CLOSE cur1;
END;

This fails with the following:

ERROR 1064 (42000): 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 'SHOW SLAVE STATUS;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

If there is another way to do this (not using a cursor maybe, as there is only one row returned) that would be great.

Answers


Just in case someone else has this issue.

I found a dirty way around it. I don't get all the info I need but it's a start.

information_schema.processlist (or SHOW PROCESSLIST) has the slave process which I can use in a stored procedure:

+-----+-------------+---------------------+---------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host                | db      | Command | Time  | State                                                                 | Info             |
+-----+-------------+---------------------+---------+---------+-------+-----------------------------------------------------------------------+------------------+
|   1 | system user |                     | NULL    | Connect | 75212 | Waiting for master to send event                                      | NULL             |
|   2 | system user |                     | NULL    | Connect |  2158 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+-----+-------------+---------------------+---------+---------+-------+-----------------------------------------------------------------------+------------------+

Need Your Help

vba Macro - Using ForumulaR1C1 to get data from another sheet

excel vba excel-vba

I'm trying to use vba to automate a simple process by getting a data from another sheet in the same workbook. I'm cannot just copy because the data is updated dynamically, if there is changes to the

MySQL partition drop takes time

mysql database-partitioning

In some cases we use MySQL partitioning by date to store data, keep X days back of info and on a daily basis an automatic process creates partitions ahead and drops old partitions (mind there is no