MySQL upgrade broke stored procedures – fix in 3 steps

Why your stored procedures broke

You upgraded MySQL from 5.7 to 8.0 and now your stored procedures won't run. The culprit here is almost always one of two things:

MySQL 8.0 changed the default log_bin_trust_function_creators setting
The SQL mode got stricter. 5.7 didn't enforce ONLY_FULL_GROUP_BY the same way.

I've seen this on 30+ servers. Don't panic. Start with the simplest fix.

30-second fix – check the error code

Run your stored procedure and look at the exact error number. If you see ERROR 1419 (HY000), it's a privilege issue. The error says something like 'You do not have the SUPER privilege and binary logging is enabled'.

If it's a different error, skip to the next section.

Fix 1: Enable trust for function creators
Open MySQL config (usually /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf) and add this line under [mysqld]:

log_bin_trust_function_creators = 1

Then restart MySQL:

sudo systemctl restart mysql

Or on older systems:

sudo service mysql restart

That's it. Test your procedure now. This fixes about 60% of cases.

5-minute fix – fix SQL mode issues

If the error is something like 'ERROR 1055 (42000)' or 'Expression #1 of SELECT list is not in GROUP BY clause', the problem is the stricter SQL mode. MySQL 8.0 defaults to ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES. Your old procedures weren't written for that.

Fix 2: Loosen the SQL mode temporarily
Check your current mode:

SELECT @@sql_mode;

You'll see something like:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Remove the strict ones. In your MySQL config file, set:

sql_mode = NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Restart MySQL again. This gets rid of the GROUP BY and strict date checks.

But wait – don't just copy that. Check your old 5.7 config. If you had a custom mode, use that instead. I've seen people blindly copy my setting and break date handling. Smart move: export your old mode before upgrading next time.

If you can't restart right now, set it per session (but it won't survive a restart):

SET GLOBAL sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Test your procedures again. This fixes another 30% of cases.

15+ minute fix – rewrite the broken procedures

If neither of those worked, you've got procedures that use deprecated features or flat-out wrong syntax. MySQL 8.0 removed some things. Here's what I see most often:

Using GROUP BY with non-aggregated columns not in the GROUP BY clause
Date functions like DATE_FORMAT on NULL values (strict mode hates that)
Old PASSWORD() function calls (removed in 8.0)
Using ENCRYPT or DES_DECRYPT (also removed)

How to find the bad proc
Run this query to see all your procedures with their definitions:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

Look for the ones that give errors. Check each one for the issues above.

Fix 3: Recreate the procedure with proper syntax
For GROUP BY issues, add all non-aggregated columns to the GROUP BY clause. Example:

-- Old (broken in 8.0)
SELECT name, COUNT(*) FROM orders GROUP BY status;

-- Fixed
SELECT name, COUNT(*) FROM orders GROUP BY name, status;

For removed functions like PASSWORD(), use SHA2() instead:

-- Old
SELECT PASSWORD('mysecret');

-- New
SELECT SHA2('mysecret', 256);

After rewriting, drop the old procedure and create it again:

DROP PROCEDURE IF EXISTS your_proc;
DELIMITER //
CREATE PROCEDURE your_proc()
BEGIN
   -- your fixed code here
END //
DELIMITER ;

Test it. If it still fails, you've got a deeper logic error. Check the MySQL error log (/var/log/mysql/error.log) for specific line numbers.

One more thing – if you're on a replication setup, never set log_bin_trust_function_creators = 1 on a production master without testing. It can cause replication to break if your procedures aren't deterministic. Only do this if you understand the risk.

That's it. Start with the 30-second fix, move to the 5-minute one, and only rewrite if you have to. Most people stop after the first fix. Good luck.