Procedure not looping properly

Question

I have several client databases on one server. They all have a prefix. Lets use "piggy_" as the prefix for the purposes of this.

I.e.

piggy_client_a
piggy_client_b
piggy_client_c

Below is the code of the three piggy databases and add a bunch of users. (For testing):

CREATE SCHEMA IF NOT EXISTS piggy_client_a;

CREATE TABLE piggy_client_a.user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  fullname VARCHAR(30) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO piggy_client_a.user (fullname, email) VALUES
("chris davis", "[email protected]"),
("chris td", "[email protected]"),
("bob sinclair", "[email protected]"),
("frank sidebottom", "[email protected]");

CREATE SCHEMA IF NOT EXISTS piggy_client_b;

CREATE TABLE piggy_client_b.user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  fullname VARCHAR(30) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO piggy_client_b.user (fullname, email) VALUES
("fanny adams", "[email protected]"),
("colin furry", "[email protected]"),
("chris grumps", "[email protected]"),
("tracey singer", "[email protected]");

CREATE SCHEMA IF NOT EXISTS piggy_client_c;

CREATE TABLE piggy_client_c.user (
  id INT(11) NOT NULL AUTO_INCREMENT,
  fullname VARCHAR(30) DEFAULT NULL,
  email VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO piggy_client_c.user (fullname, email)VALUES
("mary qos", "[email protected]"),
("curtis jackson", "[email protected]"),
("christine motor", "[email protected]"),
("tom peters", "[email protected]");

Within each piggy database is the same schema. One of those tables is user and I want to find any of these databases where the user exists.

I have written a stored procedure and it works, on the whole. However it only ever processes the first piggy database. I have double checked and if I add a particular user into the piggy_client_c database, it is not returned.

Here is my code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `find_user`$$

CREATE PROCEDURE `find_user`(IN partial_name VARCHAR(20))
BEGIN

    DECLARE database_name VARCHAR(128) DEFAULT '';
    DECLARE non_found INTEGER DEFAULT 0;

    DECLARE database_list 
        CURSOR FOR 
            SELECT SCHEMA_NAME FROM information_schema.SCHEMATA 
            WHERE SCHEMA_NAME LIKE `piggy_client_%` 
            ORDER BY SCHEMA_NAME ASC;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET non_found = 1;

    OPEN database_list;

    check_table: LOOP

        FETCH database_list INTO database_name;

        IF non_found = 1 THEN 
            LEAVE check_table;
        END IF;

        SET @sql = CONCAT('SELECT "',database_name,'" AS db_name, fullname,email FROM ',database_name,'.user WHERE trim(fullname) like "%',partial_name,'%"');

        PREPARE db_statement FROM @sql;
        EXECUTE db_statement;
        DEALLOCATE PREPARE db_statement;

    END LOOP check_table;

    CLOSE database_list;

END$$

DELIMITER ;

I cannot see anything that would cause the function to exit, or not loop through the cursor, but I am fairly new to this.

Or, if I could "step" through the code, that would be useful, or output debug info.

TIA, Chris

Answer 1

In your code there is an error in:

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME LIKE `piggy_client_%` 
ORDER BY SCHEMA_NAME ASC;

Replace backticks with string delimiter as in:

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME LIKE 'piggy_client_%' 
ORDER BY SCHEMA_NAME ASC;

I created a db-fiddle

You also mis-use double quotes as string delimiters. You insert statement should look like:

('fanny adams', '[email protected]'),

But apparently that works, so I have not fixed that.