visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
By: Bernard France  Date: 21/02/2009 08:42:53  English  Points: 20 Status: Answered
Quality : Excellent
Hello,

I'm trying to do something as simple as this :

delimiter // create procedure FixSomething() begin update refdomaines set ... where... ; end; //



and all I get is the error "ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist". Why ?

I'm running a 5.0.27 community edition.

thanks for the help ! merci!
By: VGR Date: 21/02/2009 08:46:34 English  Type : Answer
yo 8-)

In fact, I suspect you upgraded manually from a previous version and didn't run the mysql_fix_privilege_tables.sql script, did you ? ;-))

This said, even in 5.0.22, and probably up to 5.1.23, there is a problem/bug in mysql_fix_privilege_tables.sql so that it "forgets" to create the proc and procs_priv tables, which are required to be able to introduce stored procedures.

To simplify your fix, please find hereafter the SQL statements to create those tables.

HTH

use mysql; 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` char( 64 ) NOT NULL default '', `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' ) NOT NULL default '', `comment` char( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', PRIMARY KEY ( `db` , `name` , `type` ) ) ENGINE = MYISAM DEFAULT CHARSET = utf8 COMMENT = 'Stored Procedures'; DROP TABLE IF EXISTS `procs_priv`; CREATE TABLE `procs_priv` ( `Host` char(60) collate utf8_bin NOT NULL default '', `Db` char(64) collate utf8_bin NOT NULL default '', `User` char(16) collate utf8_bin NOT NULL default '', `Routine_name` char(64) collate utf8_bin NOT NULL default '', `Routine_type` enum('FUNCTION','PROCEDURE') collate utf8_bin NOT NULL, `Grantor` char(77) collate utf8_bin NOT NULL default '', `Proc_priv` set('Execute','Alter Routine','Grant') character set utf8 NOT NULL default '', `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`), KEY `Grantor` (`Grantor`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';


By: sweet_aris08 Date: 25/06/2009 12:30:37 English  Type : Comment

?>php
]="quote">
By: sweet_aris08 Date: 30/06/2009 13:33:34 English  Type : Comment
mysql
By: OpConsole Date: 03/04/2011 19:10:04 English  Type : Comment
force close

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 126.024960 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page