[plug] update a mysql script

Sat Dec 31 16:15:19 WST 2005

Jon Miller wrote:
> I have a file that creates a mysql database when I do the following:
> mysql <create.sql
> The database already exists, but I was told I need to re enter the command
 > and now I'm getting the database already exist.  Is there a way to 
running this
 > command with some form or overwrite command or update command.

As far as I know you'd need a different version of the script that's 
designed to update an existing database. MySQL can't just "re-write" an 
existing table definition, it needs to know things like how to handle 
the data that already exist.

Typically you'd use a series of ALTER TABLE and UPDATE statements to 
change the type of fields, create new fields, rename old ones, remove 
unused fields, and shuffle data around.

If the schema hasn't changed too much, you might be able to dump your 
database, drop the tables, run the script, then reload the dumped data 
into the new tables. Be careful doing this, though, and make sure you 
have a good backup.

mysqldump can dump just the data with the -t option. You should probably 
do two dumps - one entirely complete dump with all info, suitable for 
backups, and a second data-only dump that you'll attempt to load into 
the newly regenerated tables.

If that fails, I'd suggest restoring your backup and starting work on 
the upgrade script by comparing the current schema with the ones that'll 
be created by the new DB.

> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
 > [snip blah blah blah]
> <DIV>Jon</DIV></BODY></HTML>

Please turn this off, or failing that convince your mal client to post 
with MIME multipart/alternative so it at least works.

--
Craig Ringer