?
Solved

mysqldump problems

Posted on 2011-10-19
14
Medium Priority
?
1,183 Views
Last Modified: 2012-06-22
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\peter\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=utf8 --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\dumps\Dump20111019-2 has finished with 1 errors

Can anyone help


0
Comment
Question by:peterharris
  • 7
  • 7
14 Comments
 
LVL 8

Expert Comment

by:pilson66
ID: 36991381
try with this combination:

mysqldump --add-drop-table --single-transaction --quick -u username  -ppassword -cB databasename > database.sql
0
 

Author Comment

by:peterharris
ID: 36991440
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
0
 
LVL 8

Expert Comment

by:pilson66
ID: 36991464
from mysql console, show tables:

mysql -uroot -ppass
>use referrals;
>show tables;
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 8

Expert Comment

by:pilson66
ID: 36991469
Also, in database, did you have stored procedures?
If yes - add key "--routines" to mysqldump
0
 

Author Comment

by:peterharris
ID: 36991526
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
0
 
LVL 8

Expert Comment

by:pilson66
ID: 36991540
Read my comments carefully!
>use referrals;
>show tables;
0
 

Author Comment

by:peterharris
ID: 36991624
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

0
 
LVL 8

Accepted Solution

by:
pilson66 earned 2000 total points
ID: 36992125
If you`re already in the mysql console - you don`t need enter "mysql -uroot -pblablabla"
To show databases - enter "show databases;" - this will show you all available databases in this server.
Next, to select needed database "referrals"- enter "use referrals;".
Next step - show me available tables, with command "show tables;"

All output from mysql console - show me.
0
 

Author Comment

by:peterharris
ID: 36998499
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

0
 

Author Closing Comment

by:peterharris
ID: 36998505
It didnt completely solve my problem but it taught me a lot about administering mysql
0
 
LVL 8

Expert Comment

by:pilson66
ID: 36998813
If "show tables" gives empty output - this means that ypur database "referrals" is empty (have no tables).
0
 

Author Comment

by:peterharris
ID: 36999088
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
0
 
LVL 8

Expert Comment

by:pilson66
ID: 36999109
are you sure that database named "referrals"?
are you sure that this database placed in THIS mysql server? Maybe in other?
0
 

Author Comment

by:peterharris
ID: 36999503
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
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 10 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question