Strategy for keeping development in synch with test?

[davidannis]

I am wondering if there is an easier / better way to keep my development and test databases in synch.

 

When I develop, I may add tables or fields to existing tables. When I upload from the development server to the test server I can check and make sure that I include all of the programs that I have edited by using the filesystems last modified date. However, I keep track of new and changed tables in MYSQL manually. Is there an easy way, that I am just missing, to see last modified dates for tables within a db? Is there some other strategy I should be using. I hate missing a table and having the test server give errors on code I debugged once on the development server.

[JustLikeIcarus]

You could use something like MySQL workbench which allows you to generate db diffs and manage migrations. http://dev.mysql.com/downloads/workbench/

[Psycho]

I assume you are going in an manually updating the database in your test environment using your DB management software. You could create scripts to perform all of the modifications that you make from one release to another. Then, when it is time to move a release to production you just run those scripts to perform the updates.

 

During the development phase you can get a copy of your prod database and execute all of the current scripts to update it to the latest version. However, you really shouldn't be using a production database in a test environment if there is any personal data stored in the production DB.

[davidannis]

You could use something like MySQL workbench which allows you to generate db diffs and manage migrations. http://dev.mysql.com/downloads/workbench/

Thanks. I took a look at MySQL workbench but it requires .NET and I am developing on a Mac and deploying to Linux which are not on the supported OS list. Is there an equivalent that you know of which will run under OSX?

[davidannis]

I assume you are going in an manually updating the database in your test environment using your DB management software. You could create scripts to perform all of the modifications that you make from one release to another. Then, when it is time to move a release to production you just run those scripts to perform the updates.

 

During the development phase you can get a copy of your prod database and execute all of the current scripts to update it to the latest version. However, you really shouldn't be using a production database in a test environment if there is any personal data stored in the production DB.

Yes, I am using PhpMyAdmin to change the databases. On this particular project there is no personal data to speak of so that wouldn't be an issue. Writing scripts would be less convenient than using PHPMyAdmin but perhaps that is the solution.

[mac_gyver]

if you goto the mysql workbench page, the select platform drop-down lets you select a variation for mac os x.

[davidannis]

if you goto the mysql workbench page, the select platform drop-down lets you select a variation for mac os x.

Thank you.

[Psycho]

Yes, I am using PhpMyAdmin to change the databases. On this particular project there is no personal data to speak of so that wouldn't be an issue. Writing scripts would be less convenient than using PHPMyAdmin but perhaps that is the solution.

 

Do realize that when you use the UI within PHP Admin to perform an operation that it runs a query and displays that query? You should copy those queries and save them so you can run them in production when you promote a new release to production. Otherwise, how do you know what changes need to be made to the production DB?

 

So, let's say you have a column in a table called DATA for "Status" and you are currently storing textual values such as "Low", "Medium" and "High". And, as a change for the next release it is decided that the list should be user configurable. So, you need to create a separate table to define the status values and in the DATA table change the "Status" column to hold a foreign key value for the status from the other table. You could do something like this:

 

1. Create the new table in PHPMyAdmin using the UI and populate with the default values.

2. Perform an export of the table (including values) and save that query

3. Create an update query to change the text values in the DATA table to be the foreign keys to the status ids. Save that query.

 

When you then promote the new functionality to production you would run the same two queries. Likewise, if you take a backup of production to use in your test environment, you just run all of your alters to update it to the current schema.

Edited July 18, 2014 by Psycho