Pythian Group

This post originated from a quick discussion we had internally on how to quickly and easily compare schemas between two Oracle databases. I learned about Sheeri Cabral’s post with a quick comparison solution for MySQL databases and I though of using a similar approach for Oracle. I did some testing and it worked quite well.

There certainly are tools in the market, free or not, that do this for us, and even generate scripts to correct differences. The steps below only go as far as to tell you what the differences are. However, they don’t require any additional tool and can be easily executed in any *nix or Windows environment.

The procedure uses the Oracle Data Pump utilities to extract one schema’s metadata and the Unix diff command to compare them. The metadata files generated by Data Pump, though, contain a lot of information about tables and indexes statistics that add too much noise to the diff output, since they usually differ a lot between databases. To eliminate that noise, I used the following small Perl script to pre-process the metadata files: Read the rest of this entry . . .

The easiest way to see the differences between two schemas on a non-Windows machine is to run:

mysqldump -h server1 --no-data --all-databases > file1.sql
mysqldump -h server2 --no-data --all-databases > file2.sql
diff file1.sql file2.sql

However, this will show also trivial differences, such as the value of AUTO_INCREMENT. It also does not give you a way to patch one schema to be like another.

We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.

We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and patch. MySQL workbench does both reverse engineering and “forward engineer” alter script for free in the OSS/Community version.

Step by step, here’s how to do it:

1. First, create a script with the database schema that you want to be the final result. For example, if you want to take an existing production schema and change it to be like the development schema, then your “final result” is the development schema:

Read the rest of this entry . . .

If you work with databases long enough, you run into a certain problem. You have a mysqldump file* of a table or a database, and you need to import it into your new database. However, you need to change something in the file first. Maybe the INSERT statements need to be changed to INSERT REPLACE. You fire up vi and load the file, but when you go to search and replace, vi runs out of memory and doesn’t complete the operation. Or maybe the dump file is just so big it won’t even load in the first place. What do you do in this situation?

Well, one simple solution is to use the sed tool to modify the file. Sed actually stands for “stream editor”. The vi editor would be considered a static editor in that it loads all of the file into memory at once. If you run out of memory, you are out of luck. With sed there is a very limited amount of data in memory at any time because it streams the data “through”, manipulating it as it goes. So sed can work with files that are huge, and only use a minimal amount of memory for processing.

The format of the search and replace also is similar to vi's search and replace. For example:

Read the rest of this entry . . .

Here is the video of “Database Basics”, which I presented at the February 2008 Boston MySQL User Group meeting. The presentation goes over the basics of relations, data, the Entity-Relationship Model, how to choose data types, and how to do basic CREATE statements.

You can download:

the video (Large, 500 MB, or Small, 100 MB)
and
the slides (PDF, 171 Kb).

Or just watch the video:

In a linkage of biblical proportions, Log Buffer #83 pointed me to Tom Kyte’s reiteration which pointed me to Coding Horror’s rant about source controlling the database schema.

Now, for starters, I agree with Tom’s sarcasm and Coding Horror’s rant — the database schema really should be source controlled in the same place as the application code, because otherwise how do you know what changes happened when, and which version of the code goes with which version of the schema.

The problem I have is this — being a purist (and by the way, *not* a programmer so there could very well be concepts I’m missing), I really want to source control the DDL/GRANT statements and whatever that are applied to the production database, because that way anyone can see exactly what was run, and I can do a schema backup and compare to the source controlled version.

But then you have the problem where you have the old schema + the code *to be run* and then you have the new schema, and what do you do with that file of code to be run? It’s now old, but you don’t want to delete it.

If the answer is “don’t source control the change statements, just the differences” then you’re requiring folks to either figure out how to get from point A to point B, or start with sample data only.

Plus, sometimes the DDL/GRANT statements are complex, or there are a lot of them to be run in a particular order, such that I *want* to source control them for backup and revision’s sake — even though SQL is declarative, there are still a few ways to skin each cat. How do I resolve that? (a separate and/or private repository?)

And if that all is the case, what are the security ramifications of having the schema, the code, and the data all in one place (the repository)? I’ve worked at places that have *real* 3-point authentication for a reversible hash — a user-supplied password, that wasn’t anywhere in the code (as far as I know it was passed mouth-to-mouth and not even in documentation); the encrypted data itself; and the code with the encryption/decryption algorithms. Sure, in the repository you might have test scripts, but where and how do you back up the repository? In the same manner as the data?

I guess folks have to be careful about any real data they backup, making sure it’s in separate places that their repository is backed up?