Best way to update the structure of a Mysql Table

Hey SP,

There is three databases on a website that I’m working on. One is the live_db, one is the dev_deb and one is the test_db. There is also three versions of the site labeled the same (, and www is the live). Right now when I want to update something I over write the test db and site with code from the dev site, if all is good, then I will copy the dev updates to the live site. So far this system is working pretty well and I’ve written some scripts which tell me which scripts are out of date and it’s really made updating a synch! However, I still have to manually alter the table structure and I’m starting to think there must be a way easier way to do this.

I know that I could drop tables altogether and then do an import from the updated database but the strange challenge here is that on the dev_db the structure is newer, but the data is older. So sometimes it’s not even possible to import either way. What I would really love to discover is some kind of handy mysql or php function that looks at the table structure of the dev_db and then removes or adds in missing columns on the test_db and then eventually the live_db. That would then pave the way for a simple data import if need be.

Right now I’m trying to launch some new inventory management features and I have about 30 columns of structure to add to various tables and I’m feeling a bit overwhelmed by it all and thinking there is probably a smarter way of looking at this problem. If you’ve got any ideas please let me know! Thanks a lot.


that sounds like the way to go

before messing around with the test database, drop the test database and import the dev database

that way, after the changes have been made to test, and you’re ready to go to dev, you can drop dev and import test, and the data will ~not~ be newer

So you’re saying.

Drop the test_db, over write it with the dev_db, do any data imports on the dev_db from the live_db, and then when all is good drop the live_db and overwrite it with the test_db?

no :slight_smile:

at no point would i ~ever~ suggest overwriting the live database

So what is it exactly you are suggesting then? I’m confused on the part of getting the dev_db structure onto the live_db with out affecting the live_db data.:eek:

okay, i understand your confusion, sorry

if you make structural changes – new or different columns, new tables, etc. – you will eventually have to make those changes to the live database, yes?

this should not be done with drop-and-restore, not to the live database

it works between live and dev, and between dev and test, but not in the other direction, except in the case of test to dev, where it might be used for convenience, if needed

but eventually you will need to promote the stuctural changes to the live databasem and that should only be done with a script (series of SQL statements that performs the changes) with due regard for the live data in the live tables

i’m sorry if i glossed over this or gave you a wrong impression

Ahh, thank you for clarifying. So is there an easy way to compare two mysql tables together and then somehow generate the changes? I’m sure I could write a PHP Script that could pull that off.

i wouldn’t call it “easy” but of course there has to be a way, and it would involve querying the INFORMATION_SCHEMA database

however, you’re thinking of automating a process that, by definition, is going to be completely different every time you need it

in my experience, i would develop the script by hand every time

Well I gotta say you do have a badge that says “Database Guru”… so I’ll take your advice and write it by hand haha. Thanks r937!