Hi,

I have created a embeedded device for work which uses mysql to store a its settings and data in. I have a upgrade facility for the device which I am working on now. I am just looking into the best way to allow my mysql tables to update if I decided to make changes to them (add columns etc).

I am a but unsure what the easiest way is to alter tables which might have changed but still keeping the data.

All the backup features in mysql administrator seem to first drop all the tables before recreating them, thereby loosing the data in them. I am updating the stored procedures using this method which is easy as there is no harm in dropping them and recreating them, even if they havent changed. However how can I do the same for tables?

Sorry if it is a bit unclear of what I am trying to do. To summerize, I basically want a easy way to generate a big SQL statement which will update my tables. It should in other words add a new column if the column does not exist in the current database or update it if it has changed but still keep the data intackt.



Thanks!

It's still a bit confusing. If you talk about update, are you refering to altering the table structures or inserting/updating data or both?

I don't see what the problem is with dropping the table, recreating it and restoring the data (which is what happens when you restore a backup). Can you tell me what you don't like about that?

What about if the column has been renamed, will the data still be able to restore to the correct column but with a new name?

That is exactly what you should do, and I am afraid there is no easy shortcut.

In my experience the best thing to do: I assume you have a development version of your MySQL database, or a test environment or you simply perform tests and changes on a copy of your database.

Create a file, say delta.sql.

In your test version you make the alter table statement. If it is succesful, copy it from the terminal and paste it in your delta.sql. Once it is complete, try it on a backed up copy of your live version:

mysql -u user -ppasswd databasename < delta.sql

That does it.

jlinkels

After renaming a column, you create a new backup.