Restoring MySQL db's from .sql backup failed: mysql.proc table doesn't exist

Question

Windows Server 2008 R2 MySQL 5.5 (msi install)

I made a backup:

mysql -uuser -p --single-transaction --add-drop-database --databases mysql db1 db2 db3 --result-file="C:\ProgramData\mysql\mysql server 5.5\data\backup.sql"

But it's not restoring successfully:

mysql -u root -p --verbose < bakcup.sql
Enter password:
--------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
--------------
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
--------------
--------------
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
--------------
--------------
/*!40101 SET NAMES utf8 */
--------------
--------------
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
--------------
--------------
/*!40103 SET TIME_ZONE='+00:00' */
--------------
--------------
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
--------------
--------------
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
--------------
--------------
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
--------------
--------------
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
--------------
--------------
/*!40000 DROP DATABASE IF EXISTS `mysql`*/
--------------
ERROR 1146 (42S02) at line 22: Table 'mysql.proc' doesn't exist

Now all my tables are gone in the mysql database ... I guess because I used the --add-drop-database option in my backup. I'm not sure why, but fortunately I can still login to the server at this point in spite of it not have any user tables.

EDIT: I believe being able to still login is similar to privileges not changing until they're flushed.

I'm not sure why "'mysql.proc' doesn't exist" is an issue, since it was supposed to be dropped before getting restored.

My guess would be the restore stopped immediately after the error and it didn't drop tables responsible for the auth. — akuzminsky, Jun 30 '14 at 04:00

score 5 · Answer 1 · answered Mar 11 '16 at 21:37

5

I've gotten that same "Table 'mysql.proc' doesn't exist" error, though I was just trying to restore a DB. Anyway, what worked for me was running the following command in the terminal, and then trying again

mysql_upgrade

answered Mar 11 '16 at 21:37

Brian Sweeney

181
2
13

score 0 · Answer 2 · edited May 23 '17 at 11:50

I'm not sure how the above error is related exactly, but the issue stems from the mysql database not being fully dropped. There was files in it's data directory that weren't tables (.frm, etc.).

See related post: Error Dropping Database (Can't rmdir '.test\', errno: 17)

In this case those "files" were hidden Windows .BackupManager directories that seem to exist in every directory under C: on my machine. Deleting them from the data directories that needed to be dropped was the solution.

Restoring MySQL db's from .sql backup failed: mysql.proc table doesn't exist

2 Answers2