MySQL: The reason for using the “DELIMITER” statement in MySQL routines (stored procedures, functions, triggers)

This short article illustrates how to format your MySQL routines (stored procedures, functions, triggers) for release. We’ll use the following code block to discuss.


DROP PROCEDURE IF EXISTS mytestproc; -- Don't use "dbname.mytestproc"
 
DELIMITER $$ -- Change delimiter from semicolon
 
CREATE PROCEDURE mytestproc() -- Again, don't use "dbname.mytestproc"
BEGIN
# ATS Created test for dbname data 06/20/2013
SELECT 1;
 
END$$ -- Use of new $$ delimiter
 
DELIMITER ; -- Change delimiter back to the default (semicolon). Note that the spacing used here is important!

1) The first step in the script is to drop the existing routine, if it exists. Notice how the procedure name is not prefixed with database name (e.g., dbname.mytestproc). This is so that we can run this same script against multiple databases.

2) The next step is to change the default MySQL script parser’s delimiter from semicolon (;) to double-dollar sign ($$). The reason you do this is so that the semicolons after each statement in the body of the routine are not interpreted by the parser as meaning the end of the CREATE PROCEDURE statement. This is because the entire CREATE PROCEDURE block, from CREATE PROCEDURE to END is actually a single statement that must be executed by itself. Were it not for the delimiter change, the script would break, since there each statement inside BEGIN and END would execute individually. Note that you can use a variety of non-reserved characters to make your own custom delimiter.

3) The next step is to create the routine.

4) Next is the BEGIN statement, indicating the start of the routine body

5) Next are the statements inside the routine, each terminated with semicolon (which will NOT be interpreted as end-of-CREATE PROCEDURE, since you changed the delimiter to $$).

6) Next is the END$$ statement. Notice how END is followed by $$. This actually ends the CREATE PROCEDURE statement.

7) Finally, the default delimiter (semicolon) is restored

MySQL: How to Re-Sync (Refresh) Replication Properly

The following steps illustrate how to refresh MySQL replication in a single master-replica (slave) scenario:

1) Connect to MySQL on the master server

2) Issue the following commands:

RESET MASTER;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Take note of the values reported in the row returned by SHOW MASTER STATUS. In my case they look like this:

File	        Position	Binlog_Do_DB	Binlog_Ignore_DB
bin.000001	107		

3) Use mysqldump to perform a full backup of all databases on the master. For example:

mysqldump -h[master-hostname-or-ip] --routines -uroot -pxxxxxxx --verbose --all-databases > all-databases.sql

If you add the –master-data command-line option to mysqldump, the CHANGE MASTER TO command (described in step 6) is written into the dump file, thus saving execution of that statement after importing the dump file into the replica.

Execute the following command, to allow users to begin using the master again:

UNLOCK TABLES;

4) Execute the dump file you just created (all-databases.sql) against the replica (slave) server. Copy the file to the replica server (for faster execution), or execute it locally (whichever is appropriate for your situation).

For example:

mysql -h[replica-hostname-or-ip] --comments -uroot -pxxxxxxx < all-databases.sql

5) Connect to MySQL on the replica (slave), and execute the following commands, replacing the values bin.000001 and 107 with the values output by the SHOW MASTER STATUS command you executed in step 2 (above):

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=107;

6) Next, execute the following commands on the replica:

START SLAVE;

7) Finally, test that the replica’s IO and SQL replication threads are both working again, by executing (on the replica):

SHOW SLAVE STATUS;

Successful completion of the task will be indicated by the following output:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

MySQL: INSERTing a row IF NOT EXISTS — Yes, it’s possible!

Here’s how:

INSERT INTO mdr_school_new
(PID, PPID, NAME, MSTREET, MCITY, MZIPCODE, id, entity_creation_timestamp, entity_version, last_updated_timestamp) 
SELECT *
FROM (SELECT '00000011', '00000000', 'NGSP School', '5 Maxwell Drive', 'Clifton Park', '12065', UUID(), NOW(), 0, CURRENT_TIMESTAMP()) as T
WHERE NOT EXISTS(SELECT * FROM mdr_school_new WHERE NAME = 'NGSP School');

MySQL:Old-Style JOIN Syntax is Required for Correlated Subqueries

Notice in the query below, the JOIN/ON syntax we’re all comfortable with does NOT work for correlating subqueries. You must use the old WHERE/= style of joining tables. For example:

SELECT    
    (        # The old-style join syntax used here is REQUIRED for the correlated subquery:
            SELECT COUNT(DISTINCT uus.id) as uus_count
            FROM user_unit_subscription uus, sims_organization so, user u, sims_user su         
            WHERE so.id = c.sims_organization_id
            AND uus.license_unit_id = lu.id
            AND u.id = uus.user_id
            AND su.id = u.sims_id
            AND su.enable = 1
            AND so.deleted = 0
            AND su.organization_id = so.id         
            GROUP BY uus.license_unit_id
     ) as correct_sub_count
FROM customer_order_subscription cos
JOIN customer_order co 
ON cos.customer_order_id = co.id
JOIN customer c 
ON co.customer_id = c.id
JOIN license_program lp 
ON co.license_program_id = lp.id
JOIN license_unit lu 
ON cos.license_unit_id = lu.id
JOIN customer_unit_subscription cus 
ON cos.license_unit_id = cus.license_unit_id 
AND cus.customer_id = c.id
WHERE cos.customer_order_id = customer_order_id;

MySQL MERGE and TEMPTABLE Algorithms for Views

I’ve learned a bit more about views in MySQL, and it’s worth mentioning that in some cases they can be far less efficient than their underlying query.

One thing to try to do with any view you build is add the following to the CREATE portion:

CREATE ALGORITHM = MERGE VIEW v_whatever

If you get a warning that MySQL can’t use the MERGE algorithm, be sure to test your view for performance. In this case, it uses the TEMPTABLE algorithm.

Some restrictions on using MERGE are when you have an aggregate in the SELECT, and also when using ORDER BY.

MySQL Transactions — The Right Way

DROP PROCEDURE IF EXISTS mytestproc;
 
DELIMITER $$
 
CREATE PROCEDURE mytestproc()
BEGIN
    START TRANSACTION;
    SAVEPOINT x;
    
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION    
        BEGIN
            ROLLBACK TO SAVEPOINT x; -- Rollback on exception    
            RESIGNAL; -- Rethrow the error (if you want to raise it upstream, i.e., in .NET/java code)
        END;
        
        #SAVEPOINT x; #named savepoint      
        
        SELECT 1;
    #Other statements here
    END;
    
    COMMIT;
    # Clean Up
    RELEASE SAVEPOINT x;
END$$    
 
DELIMITER ;
 
CALL mytestproc();

MySQL How-To: Match a GUID in a MySQL Regex (Regular Expression)

Here’s a handy regular expression you can use to match guids (values generated by MySQL’s UUID function or by other code) in a MySQL column:

SELECT *
FROM your_table
WHERE guid_column REGEXP '[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}';

MySQL How-To: Which SQL statements are running right now?

Here’s the basics:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Now these results can be a bit daunting, so you might want to refine your lookup — especially if you have at least a vague idea of what your expecting to find:

SELECT * 
FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE INFO REGEXP 'FROM too_many_rows_why_did_i_do_this';

As you may know, the output is the same as executing the following:

SHOW FULL PROCESSLIST;

However, unlike the above, you can run full SELECT queries against it.

Note: applies only to MySQL versions >= 5.1.17