A Little Noise

7Apr/095

Error 1307 creating stored procedure

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';
Filed under: MySQL FAQ Leave a comment
Comments (5) Trackbacks (0)
  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.

  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.

  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

  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

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


Leave a comment

No trackbacks yet.