MySQL Routines

In earlier MySQL versions, if binary logging is enabled, MySQL restricts creation of stored functions and triggers unless you have the SUPER privilege. In MySQL 8.0 you no longer need SUPER if the functions are declared safely.
This restriction exists because unsafe functions (e.g. non-deterministic ones like RAND(), UUID(), or ones that read/write tables) can cause replication inconsistencies.
This restriction can be temporarily lifted by setting the deprecated log_bin_trust_function_creators parameter to 1...SET GLOBAL log_bin_trust_function_creators = 1;Longer term, MySQL is expected to have more granular privileges (e.g. separate CREATE FUNCTION and CREATE PROCEDURE functions) and to further enforce replication-safe coding practices.

Procedures

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...

MySQL Users

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

Functions

CREATE FUNCTION myfunc()

RETURNS CHAR(36)

BEGIN

mycode;

END;

Replication safe functions

Mark functions as DETERMINISTIC if the result only depends on input arguments (e.g. pure math).
Specify SQL characteristics (NO SQL or READS SQL DATA) to make them safe for binary logging.
Avoid Non-deterministic functions (NOW(), UUID(), RAND()).
Avoid functions that read or write tables.
If an unsafe construct is unavoidable, consider moving the logic to application code.
If the divergence is not material (i.e. if the application will continue to give the correct results) then it may be sufficient to clearly document your choice.

To identify existing functions likely to cause replication safety concerns...

SELECT ROUTINE_SCHEMA,

ROUTINE_NAME,

ROUTINE_TYPE,

IS_DETERMINISTIC,

SQL_DATA_ACCESS

FROM information_schema.ROUTINES

WHERE ROUTINE_TYPE = 'FUNCTION';

Check for any functions marked as:

IS_DETERMINISTIC = NO
SQL_DATA_ACCESS = MODIFIES SQL DATA or CONTAINS SQL

This script gives a suggested rewrite for existing functions.

NOTE that any function that modifies SQL data will never be considered replication-safe even after making the suggested changes.i.e. Adding DETERMINISTIC to the function is just an assertion that the function is deterministic, it is up to you to make sure that is true.

SELECT ROUTINE_SCHEMA,

ROUTINE_NAME,

IS_DETERMINISTIC,

SQL_DATA_ACCESS,

CONCAT('-- Suggested rewrite for ', ROUTINE_SCHEMA, '.', ROUTINE_NAME, ' :',

'\nCREATE FUNCTION ', ROUTINE_SCHEMA, '.', ROUTINE_NAME, ' (...)',

'\nRETURNS <datatype>',

CASE

WHEN IS_DETERMINISTIC = 'NO' THEN '\n-- Consider adding DETERMINISTIC if safe'

ELSE '\nDETERMINISTIC'

END,

CASE SQL_DATA_ACCESS

WHEN 'CONTAINS SQL' THEN '\n-- Consider declaring as NO SQL or READS SQL DATA'

WHEN 'MODIFIES SQL DATA' THEN '\n-- WARNING: function modifies data; unsafe for replication'

ELSE CONCAT('\n', SQL_DATA_ACCESS)

END,

'\nBEGIN',

'\n -- function body here',

'\nEND;'

) AS suggested_definition

FROM information_schema.ROUTINES

WHERE ROUTINE_TYPE = 'FUNCTION';

Triggers

Execute

call myProc();

call myProc(myParamValue);

Duplicate

mysqldump

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.

Bibliography

https://www.mysqltutorial.org/listing-stored-procedures-in-mysql-database.aspx https://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspxhttps://dpursanov.medium.com/privileges-for-only-specific-stored-procedures-in-mysql-mariadb-5473a8c319d0
CREATEhttps://dev.mysql.com/doc/refman/8.0/en/create-procedure.htmlhttps://dba.stackexchange.com/questions/126312/is-there-an-alternative-to-create-or-replace-procedure-in-mysqlhttps://stackoverflow.com/questions/54001025/unknown-column-in-where-clause-with-stored-procedure-via-mysql
Duplicatehttps://www.eversql.com/exporting-mysql-schema-structure-to-xml-using-mysql-clients/
Troubleshootinghttps://stackoverflow.com/questions/16752922/mysql-stored-procedure-is-slower-20-times-than-standard-queryhttps://dev.mysql.com/doc/refman/8.0/en/charset-collate.htmlhttps://www.eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4/