peterharris
asked on
mysqldump problems
I am a relative newcomer to mysql and am moving an application from interbase to mysql. It works fine but I just cannot get mysqldump to work. I am trying to extract the schema into an sql file that I can then use to recreate the database when I am installing my application.
I have tried using mysqldump from the command line and also from within MySQL workbench 5.2. Whatever I try I get the error: mysqldump: Got error: 1146: Table xxxxx doesn't exist when using LOCK TABLES. This happens even when I create a brand new database with a single table with a single column in it.
There are loads of suggestions when I google this but nothing works for me.
Here is the error from the Workbench log:
09:29:00 Dumping test (emp)
Running: mysqldump.exe --defaults-extra-file="c:\ users\pete r\appdata\ local\temp \tmpa7ohir .cnf" --no-create-info=FALSE --order-by-primary=FALSE --force=FALSE --no-data=FALSE --tz-utc=TRUE --flush-privileges=FALSE --compress=FALSE --replace=FALSE --host=localhost --insert-ignore=FALSE --extended-insert=TRUE --user=root --quote-names=TRUE --hex-blob=FALSE --complete-insert=FALSE --add-locks=TRUE --port=3311 --disable-keys=TRUE --delayed-insert=FALSE --create-options=TRUE --delete-master-logs=FALSE --comments=TRUE --default-character-set=ut f8 --max_allowed_packet=1G --flush-logs=FALSE --dump-date=TRUE --lock-tables=TRUE --allow-keywords=FALSE --events=FALSE "test" "emp"
mysqldump: Got error: 1146: Table 'test.emp' doesn't exist when doing LOCK TABLES
Operation failed with exitcode 2
09:29:01 Export of D:\Users\peter\Documents\d umps\Dump2 0111019-2 has finished with 1 errors
Can anyone help
I have tried using mysqldump from the command line and also from within MySQL workbench 5.2. Whatever I try I get the error: mysqldump: Got error: 1146: Table xxxxx doesn't exist when using LOCK TABLES. This happens even when I create a brand new database with a single table with a single column in it.
There are loads of suggestions when I google this but nothing works for me.
Here is the error from the Workbench log:
09:29:00 Dumping test (emp)
Running: mysqldump.exe --defaults-extra-file="c:\
mysqldump: Got error: 1146: Table 'test.emp' doesn't exist when doing LOCK TABLES
Operation failed with exitcode 2
09:29:01 Export of D:\Users\peter\Documents\d
Can anyone help
ASKER
This is what I get when I execute the following:
mysqldump --add-drop-table --single-transaction --quick -u root -p[my password] -cB referrals > database.sql
Error: Couldn't read status information for table audit_trail ()
mysqldump: Couldn't execute 'show create table `audit_trail`': Table 'referrals.
audit_trail' doesn't exist (1146)
Peter
mysqldump --add-drop-table --single-transaction --quick -u root -p[my password] -cB referrals > database.sql
Error: Couldn't read status information for table audit_trail ()
mysqldump: Couldn't execute 'show create table `audit_trail`': Table 'referrals.
audit_trail' doesn't exist (1146)
Peter
from mysql console, show tables:
mysql -uroot -ppass
>use referrals;
>show tables;
mysql -uroot -ppass
>use referrals;
>show tables;
Also, in database, did you have stored procedures?
If yes - add key "--routines" to mysqldump
If yes - add key "--routines" to mysqldump
ASKER
mysql> mysql -uroot -pxxxx
-> use referrals
-> show tables
->
->
What is meant to happen because nothing did?
No - I dont have stored procedures - ts a really simple db. As I also said, I created a simple 1 table database to test and that was no different
Peter
-> use referrals
-> show tables
->
->
What is meant to happen because nothing did?
No - I dont have stored procedures - ts a really simple db. As I also said, I created a simple 1 table database to test and that was no different
Peter
Read my comments carefully!
>use referrals;
>show tables;
>use referrals;
>show tables;
ASKER
If I put the ; in I get an error
-> use referrals;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
-uroot -pxxxxx
/s then gives me this:
Connection id: 30
Current database: referrals
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.59-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3311
Uptime: 19 min 59 sec
Threads: 3 Questions: 113 Slow queries: 0 Opens: 87 Flush tables: 1 Open ta
bles: 10 Queries per second avg: 0.94
--------------
Exactly the same happens with my 1table new database
I have got to go out now and will re-look at this later
Peter
-> use referrals;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
-uroot -pxxxxx
/s then gives me this:
Connection id: 30
Current database: referrals
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.59-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3311
Uptime: 19 min 59 sec
Threads: 3 Questions: 113 Slow queries: 0 Opens: 87 Flush tables: 1 Open ta
bles: 10 Queries per second avg: 0.94
--------------
Exactly the same happens with my 1table new database
I have got to go out now and will re-look at this later
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Show databases doesnt produce any output at all.
Use Referrals gives the response "Database changed"
Then show tables gives no output
So I decided to start with a fresh installation of mysql. I uninstalled my previous installation and removed my previous mysql data directory.
When it had installed with default settings I created a simple database called "test" with one table.
This time it all worked OK - both using your instructions with mysql console and also using mysqldump.
So presumably something in my previous database or mysql installation was corrupted.
I would still like to extract that original schema but at least I know what should work!
Thanks for your help
Peter
Use Referrals gives the response "Database changed"
Then show tables gives no output
So I decided to start with a fresh installation of mysql. I uninstalled my previous installation and removed my previous mysql data directory.
When it had installed with default settings I created a simple database called "test" with one table.
This time it all worked OK - both using your instructions with mysql console and also using mysqldump.
So presumably something in my previous database or mysql installation was corrupted.
I would still like to extract that original schema but at least I know what should work!
Thanks for your help
Peter
ASKER
It didnt completely solve my problem but it taught me a lot about administering mysql
If "show tables" gives empty output - this means that ypur database "referrals" is empty (have no tables).
ASKER
Yes I understand that but
a) I can see them in MySQL workbench
b) I can read and write to them using my application
Its all very odd
I will now restart rebuilding my database again using a working base installation - fingers crossed
Peter
a) I can see them in MySQL workbench
b) I can read and write to them using my application
Its all very odd
I will now restart rebuilding my database again using a working base installation - fingers crossed
Peter
are you sure that database named "referrals"?
are you sure that this database placed in THIS mysql server? Maybe in other?
are you sure that this database placed in THIS mysql server? Maybe in other?
ASKER
Its now fully working. I have been able to export the database and re-create it from the exported sql file so its doing what I wanted.
Not sure how I got there in the end but its the result that matters and I have learnt a lot about mysql in the process.
Thanks for your help and support
Peter
Not sure how I got there in the end but its the result that matters and I have learnt a lot about mysql in the process.
Thanks for your help and support
Peter
mysqldump --add-drop-table --single-transaction --quick -u username -ppassword -cB databasename > database.sql