Link to home
Start Free TrialLog in
Avatar of peterharris
peterharrisFlag for United Kingdom of Great Britain and Northern Ireland

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\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


Avatar of pilson66
pilson66
Flag of Ukraine image

try with this combination:

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

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
from mysql console, show tables:

mysql -uroot -ppass
>use referrals;
>show tables;
Also, in database, did you have stored procedures?
If yes - add key "--routines" to mysqldump
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
Read my comments carefully!
>use referrals;
>show tables;
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

ASKER CERTIFIED SOLUTION
Avatar of pilson66
pilson66
Flag of Ukraine image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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).
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
are you sure that database named "referrals"?
are you sure that this database placed in THIS mysql server? Maybe in other?
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