A Little Noise

April 7, 2009

Error 1307 creating stored procedure

Filed under: MySQL FAQ — snoyes @ 10:52 am

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';

5 Comments »

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

    Comment by Patrick — December 21, 2009 @ 10:47 pm

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

    Comment by snoyes — December 22, 2009 @ 9:34 pm

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

    Comment by Darryl — January 22, 2010 @ 9:06 am

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

    Comment by Bob — September 17, 2010 @ 3:40 am

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

    Comment by Steve Morgan — May 28, 2011 @ 5:10 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress