Bug #1037 mysqldump/restore Error 1050 table already exists
Submitted: 12 Aug 2003 22:34 Modified: 13 Aug 2003 11:59
Reporter: DJ Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:4.014 OS:Windows (Windows 2000/XP)
Assigned to: CPU Architecture:Any

[12 Aug 2003 22:34] DJ 
Description:
I am using 4.014 on Windows. When trying to do a restore after having done a mysqldump of the entire database, I get the 'error 1050 at line 11 table 'mytable' already exists'. I see others have had this error. This is new, I had no problems doing database backups/restores using mysqldump prior to 4.014/4.013. Anyone got any ideas?

How to repeat:
Do a mysqldump on an entire database and then try a mysql restore of the same.

Suggested fix:
Make it work. No problems in prior releases.
[12 Aug 2003 22:36] DJ 
Just a change to the synopsis.
[12 Aug 2003 22:50] DJ 
using: mysqldump P 3333 --user=me --password=yahoo database > c:\mydatabase.sql 
will cause the error 1050 when trying to restor the database, however, 
using: mysqldump -P 3333 --user=me --password=yahoo --opt database > c:\mydatabase.sql
and then you can restore the database without any problems. I had to completely unistall and reinstall mysql inorder to restore a database that was dumped using the first method.
[12 Aug 2003 22:51] DJ 
using the "--opt mydatabase" seems to be the key.
[13 Aug 2003 11:59] Indrek Siitan 
Yes, "--opt" is the key. From mysqldump --help:

  --opt               Same as --add-drop-table --add-locks --all --quick
                      --extended-insert --lock-tables --disable-keys

The --add-drop-table (which is included in --opt) is the feature you need.
[22 Jun 2006 23:42] Solomon Chang 
I had this problem not two minutes before writing this answer, and hopefully this comment helps you out.
I was testing my mysqld to act as a cluster node, so I started it up with the --ndbcluster option.  I had forgotten, however, that being a data node prevents you from creating any new tables, and it was giving me exactly your problem message.  I restarted mysqld without any ndb support and Voila!  Problem fixed.
However, this spawns an entirely new bug: a SQL node needs to start with an --ndbcluster option, so how would I create a composite database with a mix of Data Engines?

Solomon Chang