Thanks for your comments!
I ran my entire script (DROP DATABASE and all) with the command-line client,
and got no errors. Perhaps there is something with the Query Browser that
is causing this problem.
I added the IF EXISTS and IF NOT EXISTS in appropriate places (although I
can see the database there beforehand, and see it disappear in MySQL
Administrator, it shouldn't be necessary in this case because I know what is
there). Of course, I still get the duplicate index errors in Query Browser,
but I suspect it is that program and not the database server that is causing
this.
The reason I am scripting this manually is because I am trying to learn and
practice MySQL (I come from a MS SQL Server background), and this database
is very small (3 or 4 tables, a view and 2 stored procedures so far). It is
not difficult to make a change and re-run the script. When I get anything
good (and of decent size) going I'll start using the other options; right
now I'm just playing with a throwaway database to get a feel for the syntax.
On 3/13/06, Andreas Krüger [EMAIL PROTECTED] wrote:
Stephen,
the behavior of MySQL server sounds bizarre, as to what information you
give us.
For the DROP DATABASE and DROP TABLE statements, there is an option that
prevents an error, if database or table don' t exist:
DROP DATABASE* IF EXISTS *db_name
DROP TABLE* IF EXISTS *tbl_name
You might also want to have a look at the 5.0 manual:
http://dev.mysql.com/doc/refman/5.0/en/drop-database.html
http://dev.mysql.com/doc/refman/5.0/en/drop-table.html
I am further astonished that you do script files manually for re-creating
databases and tables.*
Why don' t you use mysqldump for dumping a database and its tables mysql
for loading the dumped information?*
You can dump a database by
mysqldump db_name db_name.sql
There are many handy options to mysqldump as --add-drop-table and -all
that you should consider to use
You then can manually update the dump files in a text editor.
mysql db_name db_name.sql
recreates the dumped data with all tables.
Andy
Stephen Cook wrote:
I am scripting out the creation of a database so I can make changes and then
run the script to generate a clean copy (by running it in MySQL Query
Browser).
The script DROPs all the tables, then CREATEs them again along with all the
indices and whatnot. However, if I run the script after having run it once
(if I close the Query Browser and then open it again and reload the script
later), I get error 1061 Duplicate key name on all of the indices.
So I figured I missed something, and I'll just DROP the whole database and
then run the script. I add a DROP DATABASE databasename; and a CREATE
DATABASE databasename; at the start of my script and try to run it again.
Now I get errors 1008 Can't drop database 'databasename'; database doesn't
exist and 1007 Can't create database 'databasename'; database exists,
followed by the index errors above.
If I drop the database from MySQL Administrator (or the command-line
client), and then run the script again I get the same errors. The database
and all of the scripted objects are created (properly as far as I can tell),
but why would I get these errors?
I am using the latest version of MySQL (5.0.19) with InnoDB as the storage
engine on Windows 2000 Professional SP4, but I have the same problem with
5.0.17.
If there is any other information you need I'd be happy to supply it.
