MySQL Stored Procedures

Check

To show all Stored Procedures...

SHOW PROCEDURE STATUS;

To show all Stored Procedures for a specified database...

SHOW PROCEDURE STATUS WHERE db = 'database_name';

To show all Stored Procedures for a specified database by querying the data dictionary...

SELECT routine_name

  FROM information_schema.routines

 WHERE routine_type = 'PROCEDURE'

   AND routine_schema = 'database_name';

To view the definition of a Stored Procedure...

SHOW CREATE PROCEDURE procedure_name\G;

Create

If you haven't been granted the privileges to create procedures already, then you need...

GRANT CREATE ROUTINE ON myDB.* TO myUser; 

DELIMITER $$

CREATE DEFINER = 'myUser'

       PROCEDURE IF NOT EXISTS myProc(myParam)

       COMMENT 'Procedure to output a row to a text file'

       LANGUAGE SQL

       NOT DETERMINISTIC

       SQL SECURITY DEFINER

BEGIN

  SELECT myRow

    FROM myTable

    INTO OUTFILE 'myOutfile';

END$$

DELIMITER ;

If you don't set the DELIMITER to a different value then any semi-colon in your procedure will cause the CREATE PROCEDURE statement to end with a syntax error.
The 'DEFINER' of the Procedure will be the account you logged in as, unless you explicitly override this.
If your procedure always produces the same result for the same input parameters then it is DETERMINISTIC and you may improve performance by declaring it as such. The default is NOT DETERMINISTIC.
SQL SECURITY defines whether the Procedure runs with the privileges of the DEFINER (default) or the INVOKER.
You can also specify one of the following characteristics, but the documentation suggests they are 'advisory only'. i.e. they may help a human reading the code but don't have any impact on how the engine runs the procedure (but I guess this may change in later versions)... CONTAINS SQL (default. indicates SQL that neither reads nor writes data), NO SQL (contains no SQL statements), READS SQL DATA (e.g. SELECT), MODIFIES SQL DATA (e.g. INSERT, UPDATE, DELETE). Refer to the documentation for more information...https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

Another example showing how to pass and use a date parameter to a stored procedure...

DELIMITER //

DROP PROCEDURE IF EXISTS myProc;

CREATE PROCEDURE myProc(IN myDate DATE)

BEGIN

  SELECT @myCommand := CONCAT("SELECT 'myHeading1',

                                      'myHeading2'

                                UNION (SELECT myCol1,

                                              myCol2

                                 INTO OUTFILE '/tmp/myOutput", DATE_FORMAT(myDate,'%d%m%Y'),'.csv',"'

                                      FIELDS TERMINATED BY ','

                                      LINES  TERMINATED BY '\r\n'

                                 FROM myDB.myTable

                                WHERE DATE_FORMAT(myCol2, '%d/%m/%Y') = DATE_FORMAT(?,'%d/%m/%Y')

                                ORDER BY myCol1)");

  PREPARE stmt FROM @myCommand;

  EXECUTE stmt USING @myDate;

  DEALLOCATE PREPARE stmt;

END//

DELIMITER ; 

If you have saved your DDL to a script file, you can run it using...

source myscript.sql

Make appropriate Grants...

GRANT EXECUTE ON PROCEDURE myDB.myProc TO aUser;

GRANT ALTER ROUTINE ON PROCEDURE myDB.myProc TO aUser;

GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE myDB.myProc TO aUser;

GRANT ALL PRIVILEGES ON PROCEDURE myDB.myProc TO aUser;

aUser will be able to Execute myProc

aUser will be able to ALTER PROCEDURE myProc()

aUser will be able to Execute and Alter myProc

As above

Execute

call myProc();

call myProc(myParamValue);

Duplicate

To dump just stored procedures, functions and triggers for a specific database...

mysqldump -h myhost -u myuser -p --no-create-db --no-data --no-create-info --routines --databases mydatabase > routines.sql

To load them into a new instance (with same usernames)...

For different usernames just edit the routines.sql script before running it

mysql -u root -p

source routines.sql

DROP

DROP PROCEDURE myProc;

DROP PROCEDURE IF EXISTS myProc;

Troubleshooting

Case Study #1

Why does the same SQL statment run more slowly in a stored procedure than when executed standalone?

DELIMITER $$

CREATE DEFINER=`myuser`@`%`

       PROCEDURE `myproc`(IN inid VARCHAR(5))

BEGIN 

   SELECT mycolumn

     FROM mytable 

    WHERE myid = 'inid';

END$$

DELIMITER ;

call myProc(12345);

This executes in approximately 30 seconds

SELECT mycolumn

  FROM mytable 

 WHERE myid = '12345;

This executes in less than 1 second

Unfortunately it's not yet (January 2023) possible to EXPLAIN a stored procedure, so we can't see whether the same execution plan is being used.

However, a fair guess would be that an index is being used standalone but not by the procedure... but why?

If the slow query log is enabled, you might get more hints there...(Let's assume the table contains ~54m rows and you are expecting to return just one of them.)# Query_time: 153.665543 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 54104126The above suggests a full table scan is being performed rather than an index lookup.

Perhaps stackoverflow has the answer?

https://stackoverflow.com/questions/16752922/mysql-stored-procedure-is-slower-20-times-than-standard-query

The accepted answer suggests a bug related to differences in database characterset versus table characterset.

Let's look at table status...

SHOW TABLE STATUS LIKE 'mytable'\G

Let's have a look at the CREATE TABLE statement for this table...

SHOW CREATE TABLE mytable\G

*************************** 1. row ***************************           Name: mytable         Engine: InnoDB        Version: 10     Row_format: Dynamic           Rows: 81772 Avg_row_length: 211    Data_length: 17317888Max_data_length: 0   Index_length: 6864896      Data_free: 4194304 Auto_increment: 82706    Create_time: 2021-03-09 15:37:35    Update_time: 2023-01-26 10:00:44     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:        Comment:1 row in set (0.00 sec)
CREATE TABLE `mytable` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `myid` varchar(5) DEFAULT NULL,  `mycolumn` varchar(50) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `idx_mytable_myid` (`myid`)) ENGINE=InnoDB AUTO_INCREMENT=82706 DEFAULT CHARSET=utf8 COMMENT='     '

So, this table is created with a default characterset of utf8...


Does this differ from our database?...

status

mysql  Ver 8.0.23-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)
Connection id:          48486048Current database:Current user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         8.0.23-commercial MySQL Enterprise Server - CommercialProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    utf8mb4Db     characterset:    utf8mb4Client characterset:    utf8mb4Conn.  characterset:    utf8mb4UNIX socket:            /mysql/mysql.sockBinary data as:         HexadecimalUptime:                 559 days 2 hours 13 min 34 sec
Threads: 5  Questions: 325482610  Slow queries: 7908120  Opens: 1698  Flush tables: 3  Open tables: 1370  Queries per second avg: 6.737

Hmm, could the root cause of this be related to a mismatch between utf8 and utf8mb4?

Changing the table characterset and collation to match the database seems like a good idea but obviously carries some risk... what other impact could changing the characterset of a table have? How did it end up different in the first place, and was this a concious decision?... and, in a production environment, this really should go through Change Management. How can we work around the issue quickly whilst we ponder the above?

Let's force a conversion in our stored procedure....

DELIMITER $$

CREATE DEFINER=`myuser`@`%` 

       PROCEDURE `myproc`(IN inid VARCHAR(5))

BEGIN 

   SELECT mycolumn

     FROM mytable 

    WHERE myid = CONVERT(inid utf8) COLLATE utf8_general_ci;

END$$

DELIMITER ;

call myProc(12345);

This now executes in less than 1 second

This is an ok workaround, but real fix would be to change the table so that the character sets and collation match.

Note that the root cause here is the change in default character set (from utf8 to utf8mb4) between MySQL 5.7 and MySQL 8.0. The utf8 character set is an alias for utf8mb3 which is deprecated in MySQL 8.0. Eventually, utf8 is mooted to become an alias for utf8mb4, so this problem could disappear in later versions.