How do you version your database schemas?

There are quite a lot of different options. On some very small / trivial applications developed by just one person, it might be acceptable to use ad-hoc queries (maybe with a GUI) to manage the schema - but this runs the risk of development and production schemas becoming out of sync (or rather, out of sync with their respective versions of code).

Requirements are typically

• Schema changes are held with the rest of the software in a SCM system
  
• Schema changes can be made in one development environment and will be correctly propogated with code to other development and production environments as required
• Schema changes can be rolled back (if for example, a deployment contains a serious bug)

There doesn't seem to be an easy answer for this one.

Our teams do something like:

…

On Windows® - but I guess it’s the same on another O.S. that does not support cluster - I noticed the following behavior when trying to execute a CREATE LOGFILE GROUP syntax like this:

CREATE
 LOGFILE GROUP `test`
 ADD UNDOFILE ‘test’
 INITIAL_SIZE = 33M
 UNDO_BUFFER_SIZE = 8M
 ENGINE = NDBCLUSTER;

In v.5.1.25-rc, v.5.1.24-rc and v.5.1.23-rc, only SQL warnings are shown:

mysql> CREATE
    ->  LOGFILE GROUP `test`
    ->  ADD UNDOFILE ‘test’
    ->  INITIAL_SIZE = 33M
    ->  UNDO_BUFFER_SIZE = 8M
    ->  ENGINE = NDBCLUSTER;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> SHOW WARNINGS;
…

I had a talk with a friend of mine, yesterday. He recommended to me that I should move away from MySQL and become more proficient with Oracle or MS SQL. The reason for this is that he argues that there is a higher chance I can find a job working in a company that uses a commercial databases (and also higher paid position) then I could for a company that uses an open-source database.

His logic was that:
If a company is already paying for a commercial database, they would have no problem paying some more for someone to maintain it or develop on it.
I started thinking about what he said to see if its true. Not so long ago, I worked on a project for big car manufacturer in China and they used Oracle databases. They had the resources to hire quite a few PL/SQL developers for something relatively simple (but I am pretty sure those PL/SQL developers are still working on it as they get paid by the hour).

On the …

I have a somewhat strange relation to parser since a while. Like everyone I started with writing little parsers by hand and bounced several times against yacc and flex failing to get around their very own syntax.

Then I discovered lemon and used it in lighttpd for the configuration and HTTP parsing, finally a parser syntax I could read. But it still was a split between lexing and parsing.

Along the the way there was ragel with its wonderful dot-output to visualize its state-engine, very neat and a mixed lexer and parser.

2-3 weeks ago I finally stumbled over LPEG and I'm happily writing parsers now. Like a simple one that can parse complex SELECT queries and …

mysqlsla v2 is finally “done” and released. About 3 months ago, when v1.8 was released, I said it would be coming “soon,” but time just flew by and here we are. Oh well. In any case, the v1 branch is dead to me and v2 is all the rave (at least for me). If you don’t care about the differences and all you want is your default top 10 report from a slow log, for example, then all you need to know is: mysqlsla -lt slow SLOW_LOG

For those interested in what has changed to warrant a new major version number, here’s the briefing of changes/overhauls:

1. Almost ALL new command line options (–log-type a.k.a. -lt is the most important); see the documentation
2. Customizable reports. In v1 the report was hard-coded. Now with -rf FILE you can format your own report. See the …

Last November, I wrote an article describing an idea I had for a game/website.
Here is a short recap:

I wanted to make a web-based text game called Shadow People. Shadow people will be a game to do with government agencies and spy organizations.
There will be two different groups of agencies: Government owned/regulated ones and Terrorists/Freelance ones.
If it helps you imagining how it will look like, if anyone saw the series “"La Femme Nikita" with the actress Peta Wilson.
The game revolves round the cool and secret world of small yet highly effective organizations that either try to keep order in the world or cause chaos.
These organizations have the highest skilled personal, best equipment possible and will stop at nothing till they get what they came for.
Since then, I've been.. well.. busy …

The Payment Card Industry Data Security Standard (PCI DSS) has been developed to help organizations that process card payments to prevent credit card fraud, cracking and various other security vulnerabilities and threats.

This has been developed by the major credit card companies such as MasterCard and Visa. If one of the companies that created the standard, Mastercard International uses PCI General for MySQL then you would be confident that the software is of the highest quality to satisfy all requirements.

A few questions to consider.

Q: Why is PCI compliance important?
A: Credit Card companies will start to demand organizations that store credit card numbers have adequate security of their (as in the credit card company) data.

Q: How can I support PCI …

The Payment Card Industry Data Security Standard (PCI DSS) has been developed to help organizations that process card payments to prevent credit card fraud, cracking and various other security vulnerabilities and threats.

I know it's been a very long time since I posted anything, but I felt the itch today. A question came up earlier this week at work: How much is JOIN performance affected when using columns that are not the exact same data type?

This is important for me because entity-attribute-value tables require a lot of self-joins. Let me start by saying that we mitigate one of the common drawbacks to EAVs - mashing diverse data types into a single column - by separating numeric, date, and character data into different tables. However, we mashed a lot of integer data into a DECIMAL(13,4) column right alongside our financial data. I recently noticed that most of the data in this EAV table has no fractional part, and to determine whether it would be worth moving it all into another table - as well as determine what column type to use - I spent an afternoon running …

A web app full of data is often going to be full of tables (at least on the administration end). A listing of users here, all the recently created events there. Often for readability we will alternate rows between two slightly different background colors.This can be handled on the PHP end with a bit of math and a counter, like so:$count = 0;while( $row = mysql_fetch_assoc($res) ) {    if( $count % 2 == 0 )         $background_color = 'white';    else        $background_color = 'gray';    /* OUTPUT TABLE CONTENTS WITH GIVEN BACKGROUND */    $count++;}However, it can also be done entirely in-database, via creative use of the XOR operator: ...