CODEWITHSUNDEEP
mysql
Vivek Mohan
Vivek Mohan

Reputation: 8366

Error while restoring MYSQL db

Following error occurs when I am trying to restore a DB in MYSQL via putty.

Command: mysql -u root -p db1<dbname.sql ;

ERROR 1 (HY000) at line 7904: Can't create/write to file './dbname/db.opt' (Errcode: 2)

What is the reason?

Upvotes: 13

Views: 14114

Answers (5)

Siddhartha Mukherjee
Siddhartha Mukherjee

Reputation: 6248

I had backup from "db1" and restoring to "db2" so in the dump file had to change "db1" to "db2" with sed.

And all worked fine.

Upvotes: 2

blackwood
blackwood

Reputation: 362

I ran into this error at work when the source database name was different than the target database name. I dumped a database on one server with mysqldump db1 > dumpfile and attempted to import it on a different server with mysql db2 < dumpfile.

Turns out the dumpfile had ALTER TABLE db1 ... statements which were meaningless on the target server where I named the database db2.

There is probably a more elegant solution than this, but I just edited the dumpfile on the target server and replaced db1 with db2.

Upvotes: 10

John Kary
John Kary

Reputation: 7252

Find out what Errcode: 2 means

You can use the perror utility to find what error 2 means:

$ perror 2
OS error code   2:  No such file or directory

More info is at the link @Jocelyn mentioned in their comment: http://dev.mysql.com/doc/refman/5.5/en/cannot-create.html

Find out what path ./ points to

We now know a file doesn't exist (or maybe it can't be written to.) The error message gives us a relative path ./ which makes it tricky... Wouldn't it be helpful if it output a fully-qualified path? Yeah.

So when MySQL imports an SQL file it creates some temp files on the filesystem. The path is usually specified by the "tmpfile" configuration option in the MySQL my.cnf file. You can quickly find the value by executing an SQL query:

$ mysql -h127.0.0.1 -uroot -p
# I assume you're now logged into MySQL
mysql> SHOW VARIABLES LIKE '%tmpdir%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| slave_load_tmpdir | /tmp  |
| tmpdir            | /tmp  |
+-------------------+-------+
2 rows in set (0.00 sec)

Ensure the directory is writeable by mysql user

According to tmpdir this means MySQL was trying to create /tmp/dbnamehere/db.opt. Ensure this directory exists and that it's owned by mysql:mysql. You might have to use sudo to elevate privileges high enough to create some directories.

$ chown -R mysql:mysql /tmp/dbnamehere

Still not working? Try other default tmpdir paths

I hit issues on my system (Ubuntu 12.04 + Vagrant 1.7.2 + Chef 11.something + opscode mysql cookbook 6.0.6) where the value in tmpdir wasn't being considered or wasn't being pulled from where I expected.

MySQL was actually trying to create the temp file at one of the following locations:

  • /var/lib/mysql/dbnamehere
  • /var/lib/mysql-default/dbnamehere

I had to create those directories and change ownership to mysql:mysql.

Upvotes: 2

TomL
TomL

Reputation: 759

This often means that your dump file includes a command that should run against a database that either doesn't exist in your local context, or to which the current user does not have access. Open up the dumpfile and look at the line mentioned in the error to find out what's going on.

Upvotes: 14

Jocelyn
Jocelyn

Reputation: 11413

You'll find help about this error in the MySQL manual: http://dev.mysql.com/doc/refman/5.5/en/cannot-create.html

Upvotes: 0

Related Questions