Sql-server – How to detect broken stored procedures after a schema change

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

This is fairly easy to test yourself. Let's create two very simple procedures:

CREATE PROCEDURE dbo.sp_mystuff
AS
  SELECT 'x';
GO
CREATE PROCEDURE dbo.mystuff
AS
  SELECT 'x';
GO

Now let's build a wrapper that executes them a number of times, with and without the schema prefix:

CREATE PROCEDURE dbo.wrapper_sp1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_sp2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.sp_mystuff;
      SET @i += 1;
    END
END
GO
CREATE PROCEDURE dbo.wrapper_2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1;
    WHILE @i <= 1000
    BEGIN
      EXEC dbo.mystuff;
      SET @i += 1;
    END
END
GO

Results:

Conclusions:

• using sp_ prefix is slower
• leaving out schema prefix is slower

The more important question: why would you want to use the sp_ prefix? What do your co-workers expect to gain from doing so? This shouldn't be about you having to prove that this is worse, it should be about them justifying adding the same three-letter prefix to every single stored procedure in the system. I fail to see the benefit.

Also I performed some pretty extensive testing of this pattern in the following blog post:

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix