Error 1307 creating stored procedure

Written by

Trying to create a stored procedure, and getting a cryptic error like this?

ERROR 1307 (HY000): Failed to CREATE PROCEDURE <procName>

Try this simple diagnostic query first:

SELECT 
  IF(
    COUNT(*) XOR (CAST(VERSION() AS decimal(2, 1)) >= 5.1), 
    'Wrong mysql.proc table version. Did you forget to run mysql_upgrade?',
    'The mysql.proc table looks ok. Keep hunting.'
  ) AS troubleshooting 
FROM 
  information_schema.columns 
WHERE 
  table_schema = 'mysql' 
  AND table_name = 'proc' 
  AND column_name LIKE 'body_utf8';

Comments

5 responses to “Error 1307 creating stored procedure”

December 21, 2009

Patrick

Do you have any other information? I am experiencing this same behavior on mysql 5.0.45. when I run mysql_upgrade, I get no change in behavior.
December 22, 2009

snoyes

Find somebody who can create procedures with the same version as you, and check that the output from SHOW CREATE TABLE mysql.proc; is the same on both. Perhaps you’ve somehow run the 5.1 version of mysql_upgrade.
January 22, 2010

Darryl

I had this problem. I’m on Windows, so adjust for *nix OS.

I went to MySQL bin folder, and ran:

mysql_upgrade -u root -p

Entered the password and the upgrade script ran and fixed the problem.

HTH,
Darryl
September 17, 2010

Bob

Upgraded from previous version to 5.1.x
Adding a sinple procedure failed with error 1307
running /usr/bin/upgrade_mysql -uroot solved the problem

Thanks
May 28, 2011

Steve Morgan

If you get an error running this ensure you start CMD as root.