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