2

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

4
  • Perhaps you could print the value of @sql and try executing it manually to see if it is valid.
    – mustaccio
    Aug 11, 2022 at 12:49
  • It is valid, it returns rows if I use a name that is in the first piggy database, just not any subsequent databases. And I checked the cursor definition returns multiple databases too
    – Chris
    Aug 11, 2022 at 12:54
  • I've added an SQL to recreate the test schemas/tables
    – Chris
    Aug 11, 2022 at 13:16
  • Sorry, yes there is a user in each database starting with "chris". I added an SQL yesterday to recreate the table(s) - find_user("chris") only returns the one from the first database
    – Chris
    Aug 12, 2022 at 8:13

1 Answer 1

3

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.

3
  • FWIW, dbfiddle.uk is a different fiddle site that does preserve DBMS brand and version. Aug 12, 2022 at 15:03
  • Quite right, the back ticks in the cursor were the issue. However, it appears SequelPro doesn't run the procedure correctly. I ran it in the console and it worked fine. So thats great, thank you
    – Chris
    Aug 12, 2022 at 15:21
  • @BillKarwin, thats the site I normally use, but it did not understand the DELIMITER part Aug 12, 2022 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.