MySQL upgrade broke stored procedures after 8.0 update

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.