Each SELECT statement that does not insert into a table or a variable will produce a result set.
If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.
UPDATE
Here are examples of stored procedures.
This stored procedure would return one result set:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
END;;
DELIMITER ;
This stored procedure would return two result sets:
DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
DECLARE local_variable_name INT;
SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
SELECT * FROM table_1;
SELECT * FROM table_2;
END;;
DELIMITER ;
This will work if the Master has a Slave Connected
SELECT COUNT(1) FROM
(SELECT user FROM information_schema.processlist) A
INNER JOIN
(SELECT user FROM mysql.user B WHERE repl_slave_priv = 'Y')
USING (user);
If the answer is...
- > 0, Replication is running (This indicates this is a Master)
- = 0, Replication is not running
You can also check the global status variable 'Slave_running'
SELECT COUNT(1) FROM information_schema.global_status
WHERE variable_name = 'Slave_running'
AND variable_value = 'ON';
If the answer is...
- > 0, Replication is running (This indicates this is a Slave)
- = 0, Replication is not running
You can also check the processlist for 'system user'
SELECT COUNT(1) FROM information_schema.processlist
WHERE user = 'system user';
If the answer is...
- = 2, Replication is running (This indicates this is a Slave)
- = 1, Replication is broken (This indicates this is a Slave)
- = 0, Replication is not running
I hope these queries give you some direction.
CAVEAT
SHOW MASTER STATUS;
just tells you what the current binary log is. If your Slaves have binary logs disabled, then SHOW MASTER STATUS;
return nothing. There is no way to capture this in the information_schema database.
The only obvious way is to either
SHOW SLAVE STATUS\G
- Look for the master.info file in the OS
UPDATE 2011-12-30 12:50 EDT
Here is simply way to identify your master from your slaves: Create a Table whose sole purpose to is to hold the server names that make up your Master. Run these commands on all your DB Servers, (Masters and Slaves)
CREATE TABLE mysql.MasterList
(
hostname VARCHAR(64),
PRIMARY KEY (hostname)
) ENGINE=MyISAM;
INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Now just run this query to determine if it is a Master:
SELECT COUNT(1) INTO @IsThisMaster FROM
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);
Here is a sample run on my PC using MySQL 5.5.12
mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| hostname | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE mysql.MasterList
-> (
-> hostname VARCHAR(64),
-> PRIMARY KEY (hostname)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
-> (SELECT variable_value hostname FROM information_schema.global_variables
-> WHERE variable_name='hostname') A
-> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql> INSERT INTO mysql.MasterList VALUES ('LW-REDWARDS2');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
-> (SELECT variable_value hostname FROM information_schema.global_variables
-> WHERE variable_name='hostname') A
-> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql>
Give it a Try !!!
UPDATE 2011-12-30 13:06 EDT
Here is the Stored Function you will need:
DELIMITER $$
DROP FUNCTION IF EXISTS `mysql`.`Is_This_A_Master` $$
CREATE FUNCTION `mysql`.`Is_This_A_Master` () RETURNS INT
DETERMINISTIC
BEGIN
SELECT COUNT(1) INTO @IsThisReplicationMaster FROM
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);
RETURN @IsThisReplicationMaster;
END $$
DELIMITER ;
Here is a Sample Call:
mysql> select mysql.Is_This_A_Master();
+--------------------------+
| mysql.Is_This_A_Master() |
+--------------------------+
| 1 |
+--------------------------+
UPDATE 2011-12-30 13:12 EDT
Just remember
- Once a Server Becomes a Slave, delete that hostname from mysql.MasterList
- Once a Server Becomes a Master, insert that hostname into mysql.MasterList
Best Answer
I am looking for a way to do this myself (in a way that satisfies me..), but a perfectly valid way to do so is described in this Stack Overflow Q & A:
Calling a Stored Procedure in a Stored Procedure in MySQL
The accepted answer by AhamedMustafaM demonstrates a technique using
OUT
parameters with the following code:I'm sure not always the answer for what you are trying to accomplish, but it is certainly an option. While you couldn't get the direct result of what the procedure called, this would help you get individual values, possibly using multiple
OUT
parameters to get what you need.