<p>Perhaps the biggest risk in pushing new functionality to live lies with the database modifications required by the new code. In Rails, I believe they have 'migrations', in which you can programmatically make changes to your development host, and then make the same changes live along with the code that uses the revised schema. And roll both backs if needs be, in a synchronized fashion.</p>

<p>Has anyone come across a similar toolset for PHP/MySQL? Would love to hear about it, or any programmatic or process solutions to help make this less risky...</p>

[{'answer_id': 35061, 'author': 'jcoby', 'author_id': 2884, 'author_profile': 'https://Stackoverflow.com/users/2884', 'pm_score': 2, 'selected': False, 'text': '<p>Symfony has a plugin called sfMigrationsLight that handles basic migrations.  CakePHP also has migrations.</p>\n\n<p>For whatever reason, migration support has never really been a high priority for most of the PHP frameworks and ORMs out there.</p>\n'}, {'answer_id': 35440, 'author': 'yukondude', 'author_id': 726, 'author_profile': 'https://Stackoverflow.com/users/726', 'pm_score': 3, 'selected': False, 'text': "<p>I've never come across a tool that would do the job. Instead I've used individual files, numbered so that I know which order to run them: essentially, a manual version of Rails migrations, but without the rollback.</p>\n\n<p>Here's the sort of thing I'm talking about:</p>\n\n<pre><code>000-clean.sql         # wipe out everything in the DB\n001-schema.sql        # create the initial DB objects\n002-fk.sql            # apply referential integrity (simple if kept separate)\n003-reference-pop.sql # populate reference data\n004-release-pop.sql   # populate release data\n005-add-new-table.sql # modification\n006-rename-table.sql  # another modification...\n</code></pre>\n\n<p>I've never actually run into any problems doing this, but it's not very elegant. It's up to you to track which scripts need to run for a given update (a smarter numbering scheme could help). It also works fine with source control.</p>\n\n<p>Dealing with surrogate key values (from autonumber columns) can be a pain, since the production database will likely have different values than the development DB. So, I try never to reference a literal surrogate key value in any of my modification scripts if at all possible.</p>\n"}, {'answer_id': 35444, 'author': 'John Millikin', 'author_id': 3560, 'author_profile': 'https://Stackoverflow.com/users/3560', 'pm_score': 3, 'selected': False, 'text': '<p>I don\'t trust programmatic migrations. If it\'s a simple change, such as adding a NULLable column, I\'ll just add it directly to the live server. If it\'s more complex or requires data changes, I\'ll write a pair of SQL migration files and test them against a replica database.</p>\n\n<p>When using migrations, <em>always</em> test the rollback migration. It is your emergency "oh shit" button.</p>\n'}, {'answer_id': 35972, 'author': 'UnkwnTech', 'author_id': 115, 'author_profile': 'https://Stackoverflow.com/users/115', 'pm_score': 2, 'selected': False, 'text': '<p>I use <a href="http://www.webyog.com" rel="nofollow noreferrer">SQLyog</a> to copy the structure, and I ALWAYS, let me repeat ALWAYS make a backup first.</p>\n'}, {'answer_id': 87489, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>I\'ve used this tool before and it worked perfectly.</p>\n\n<p><a href="http://www.mysqldiff.org/" rel="nofollow noreferrer">http://www.mysqldiff.org/</a></p>\n\n<p>It takes as an input either a DB connection or a SQL file, and compares it to the same (either another DB connection or another SQL file). It can spit out the SQL to make the changes or make the changes for you.</p>\n'}, {'answer_id': 87595, 'author': 'Lot105', 'author_id': 9801, 'author_profile': 'https://Stackoverflow.com/users/9801', 'pm_score': 2, 'selected': False, 'text': '<p>@[yukondude] </p>\n\n<p>I\'m using Perl myself, and I\'ve gone down the route of Rails-style migrations semi-manually in the same way. </p>\n\n<p>What I did was have a single table "version" with a single column "version", containing a single row of one number which is the current schema version. Then it was (quite) trivial to write a script to read that number, look in a certain directory and apply all the numbered migrations to get from there to here (and then updating the number).</p>\n\n<p>In my dev/stage environment I frequently (via another script) pull the production data into the staging database, and run the migration script. If you do this before you go live you\'ll be pretty sure the migrations will work. Obviously you test extensively in your staging environment.</p>\n\n<p>I tag up the new code and the required migrations under one version control tag. To deploy to stage or live you just update everything to this tag and run the migration script fairly quick. (You might want to have arranged a short downtime if it\'s really wacky schema changes.)</p>\n'}, {'answer_id': 87631, 'author': 'MarkR', 'author_id': 13724, 'author_profile': 'https://Stackoverflow.com/users/13724', 'pm_score': 2, 'selected': False, 'text': "<p>Pretty much what Lot105 described.</p>\n\n<p>Each migration needs an apply and rollback script, and you have some kind of control script which checks which migration(s) need to be applied and applies them in the appropriate order. </p>\n\n<p>Each developer then keeps their db in sync using this scheme, and when applied to production the relevant changes are applied. The rollback scripts can be kept to back out a change if that becomes necessary.</p>\n\n<p>Some changes can't be done with a simple ALTER script such as a tool like sqldiff would produce; some changes don't require a schema change but a programmatic change to existing data. So you can't really generalise, which is why you need a human-edited script.</p>\n"}, {'answer_id': 1047119, 'author': 'Paulo', 'author_id': 68041, 'author_profile': 'https://Stackoverflow.com/users/68041', 'pm_score': 0, 'selected': False, 'text': "<p>I've always preferred to keep my development site pointing to the same DB as the live site. This may sound risky at first but in reality it solves many problems. If you have two sites on the same server pointing to the same DB, you get a real time and accurate view of what your users will see when it goes live. </p>\n\n<p>You will only ever have 1 database and so long as you make it a policy to never delete a column from a table, you know your new code will match up with the database you are using.</p>\n\n<p>There is also significantly less havoc when migrating.  You only need to move over the PHP scripts and they are already tested using the same DB.</p>\n\n<p>I also tend to create a symlink to any folder that is a target for user uploads. This means there is no confusion on which user files have been updated.</p>\n\n<p>Another side affect is the option of porting over a small group of 'beta-testers' to use the site in everyday use.  This can lead to a lot of feedback that you can implement <em>before</em> the public launch.</p>\n\n<p>This may not work in all cases but I've started moving all my updates to this model. It's caused much smoother development and launches.</p>\n"}, {'answer_id': 3488657, 'author': 'Mike Howsden', 'author_id': 227651, 'author_profile': 'https://Stackoverflow.com/users/227651', 'pm_score': 2, 'selected': False, 'text': '<p>The solution I use (originally developed by a friend of mine) is another addendum to yukondude. </p>\n\n<ol>\n<li>Create a schema directory under version control and then for each db change you make keep a .sql file with the SQL you want executed along with the sql query to update the db_schema table.  </li>\n<li>Create a database table called "db_schema" with an integer column named version.</li>\n<li>In the schema directory create two shell scripts, "current" and "update".  Executing current tells you which version of the db schema the database you\'re connected to is currently at.  Running update executes each .sql file numbered greater than the version in the db_schema table sequentially until you\'re up to the greatest numbered file in your schema dir.</li>\n</ol>\n\n<p>Files in the schema dir:</p>\n\n<blockquote>\n<pre><code>0-init.sql \n1-add-name-to-user.sql\n2-add-bio.sql\n</code></pre>\n</blockquote>\n\n<p>What a typical file looks like, note the db_schema update at the end of every .sql file:</p>\n\n<pre><code>BEGIN;\n-- comment about what this is doing\nALTER TABLE user ADD COLUMN bio text NULL;\n\nUPDATE db_schema SET version = 2;\nCOMMIT;\n</code></pre>\n\n<p>The "current" script (for psql):</p>\n\n<pre><code>#!/bin/sh\n\nVERSION=`psql -q -t &lt;&lt;EOF\n\\set ON_ERROR_STOP on\nSELECT version FROM db_schema;\nEOF\n`\n\n[ $? -eq 0 ] &amp;&amp; {\n    echo $VERSION\n    exit 0\n}\n\necho 0\n</code></pre>\n\n<p>the update script (also psql):</p>\n\n<pre><code>#!/bin/sh\n\nCURRENT=`./current`\nLATEST=`ls -vr *.sql |egrep -o "^[0-9]+" |head -n1`\n\necho current is $CURRENT\necho latest is $LATEST\n\n[[ $CURRENT -gt $LATEST ]] &amp;&amp; {\n    echo That seems to be a problem.\n    exit 1\n}\n\n[[ $CURRENT -eq $LATEST ]] &amp;&amp; exit 0\n\n#SCRIPT_SET="-q"\nSCRIPT_SET=""\n\nfor (( I = $CURRENT + 1 ; I &lt;= $LATEST ; I++ )); do\n    SCRIPT=`ls $I-*.sql |head -n1`\n    echo "Adding \'$SCRIPT\'"\n    SCRIPT_SET="$SCRIPT_SET $SCRIPT"\ndone\n\necho "Applying updates..."\necho $SCRIPT_SET\nfor S in $SCRIPT_SET ; do\n    psql -v ON_ERROR_STOP=TRUE -f $S || {\n    echo FAIL\n    exit 1\n    }\ndone\necho OK\n</code></pre>\n\n<p>My 0-init.sql has the full initial schema structure along with the initial "UPDATE db_schema SET version = 0;".  Shouldn\'t be too hard to modify these scripts for MySQL.  In my case I also have </p>\n\n<blockquote>\n<pre><code>export PGDATABASE="dbname"\nexport PGUSER="mike"\n</code></pre>\n</blockquote>\n\n<p>in my .bashrc.  And it prompts for password with each file that\'s being executed.</p>\n'}, {'answer_id': 6504248, 'author': 'chiborg', 'author_id': 130121, 'author_profile': 'https://Stackoverflow.com/users/130121', 'pm_score': 0, 'selected': False, 'text': '<p>In the past I have used <a href="http://www.liquibase.org/" rel="nofollow">LiquiBase</a>, a Java-based tool where you configure your migrations as XML files. You can generate the necessary SQL with it.</p>\n\n<p>Today I\'d use the <a href="http://www.doctrine-project.org/" rel="nofollow">Doctrine 2</a> library which has <a href="http://www.doctrine-project.org/projects/migrations/2.0/docs/reference/managing-migrations/en" rel="nofollow">migration facilities</a> similar to Ruby. </p>\n\n<p>The <a href="http://symfony.com/" rel="nofollow">Symfony 2</a> framework also has a nice way to deal with schema changes - its command line tool can analyze the existing schema and generate SQL to match the database to the changed schema definitions.</p>\n'}]
['https://Stackoverflow.com/questions/35047', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/137/']