MySQL Stored Procedure Error

Benvor
Benvor used Ask the Experts™
on
Hi Experts

I have very little knowledge of Stored Procedure but I have made a few in another DB platform. I am trying to create a stored proc in MySQL but I keep getting an error:
"MySQl Error Number 1307 Failed to CREATE Procedure test"

Does not matter what I try, I always get this error, even with the default opening stored procedure. How can I solve this problem, or am I doing something wrong?

Any help would be greatly appreciated
CREATE PROCEDURE `transportscheduling_be`.`test` ()
BEGIN

END

'Even this has an error'

DELIMITER $$

DROP PROCEDURE IF EXISTS `transportscheduling_be`.`test` $$
CREATE PROCEDURE `transportscheduling_be`.`test` ()
BEGIN

END $$

DELIMITER ;

'this one as well

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Stored procedures are only allowed to be created with mysql version 5.x ...

i guess you have the mysql verson 4.x or something...please upgrade your version

http://dev.mysql.com/downloads/mysql/5.0.html

http://www.mysql.com/news-and-events/newsletter/2004-01/a0000000297.html

thanks

Kalpan
both of your above pasted ones work with me when i created the database transportscheduling_be with my mysql version 5.1.x

thanks

kalpan
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Have you perhaps upgraded this database from an older installation?  I'm thinking maybe that your mysql.proc table is not set up correctly, which happens if you upgrade your database engine, but don't update the underlying tables.

I'm attaching the CREATE statement for my mysql.proc table.

You might want to try running 'mysql_upgrade' as well to see if that will fix the proc table.
DROP TABLE IF EXISTS `proc`;
CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE') NOT NULL,
  `specific_name` char(64) NOT NULL DEFAULT '',
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` longblob NOT NULL,
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures';

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
         Hi!

Try this
Note that I'm guessing that the transportscheduling_be is a database where the procedure will be in and a_table is a table in the database as well.
Change the "a_table" to a table in your db. :)

DELIMITER //
USE transportscheduling_be //
CREATE PROCEDURE test ()
BEGIN
    SELECT count(*) from a_table;
END //

Regards,
    Tomas Helgi



Author

Commented:
MySQL Version = 5.0.45


I'm not sure where the 'mysql_upgrade' is

@ Tomas
I tried it and replaced a_table with one of my tables and I still get the same error

PS. I don't know if this is of any importance but I am not running a MySQL server but instead I'm using a wamp server on my local machine
Please check the mysql logs under the C:\wamp\logs ....

Please let me know what could be error for creating the stored procedure at tail lines....

thanks

Kalpan
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
mysql_upgrade should be in c:\program files\mysql server or something similar, if memory serves (I use UNIX, not Windows).  You should run it from a command window (Start -> Run -> 'cmd').  However, you path may not be set up correctly, so you should "cd" in the MySQL installation directory (above path) before trying to run it.

You haven't answered my first question though - is this an upgrade of a previous installation or is it a fresh install?

Author

Commented:
There is only one MySQl log: MySQL.txt

And there is no errors in this log file

Author

Commented:
I did not install a MySQL Server, I only Installed a wamp server and the MySQL GUI tools, then I migrated the db's from MS Access to MySQl

Author

Commented:
I hav run the 'mysql_upgrade" and I get this error but I dont think it is serious...

Repairing tables
mysql.general_log
error    : Can't find file: 'general_log' (errno: 2)
mysql.slow_log
error    : Can't find file: 'slow_log' (errno: 2)
Running 'mysql_fix_privilege_tables'...
OK

C:\wamp\bin\mysql\mysql5.0.45\bin>

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial