asked on

MySQL Stored Procedure Error

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

ASKER CERTIFIED SOLUTION

Kalpan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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 Schlecht

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

Tomas Helgi Johannsson

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

Benvor

ASKER

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

Kalpan

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 Schlecht

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?

Benvor

ASKER

There is only one MySQl log: MySQL.txt

And there is no errors in this log file

Benvor

ASKER

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

Benvor

ASKER

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>