We are currently preparing a MySQL 4.1 to MySQL 5.0 migration. First tests showed a very nasty problem, however.

One of our test cases incorporates queries against DECIMAL columns that use strings as the queried values. In MySQL 4.1 this works flawlessly. The reason behind this is that in contrast to 4.1 the newer server version does a (in my opinion very stupid) conversion from String to double, which in many cases cannot correctly store the precise value.

This may lead to very subtle bugs, especially when using an optimistic locking approach as we do. We only noticed the problem, because we got a ConcurrentModificationException, as an update query that contained a string-ized BigDecimal did not match any rows.

See MySQL bug reports 23260 and 22290 for more details.

Right …

Today I spent several hours trying to find a problem in our application until I found out there was a problem on the MySQL side. In our setup we have several slave machines that replicate data from a master server. The slaves are configured with a series of replicate-do-table directives in the my.cnf file so that only parts of the schema get replicated. The remaining tables are modified locally, so to avoid conflicts they are not updated with data coming from the master.

We do however have the need to replicate data from the master for some special-case tables. To solve this we usually have a column that indicates whether a record was created on a master or a slave machine and use an appropriate WHERE clause in all updates. This avoids collisions in concurrent INSERTs on the master and the slave. The application knows which of the two records to use.

Due to historical reasons I …

Over on the MySQL Index Analyzer site I have just released version 0.04 as a downloadable package.

This is the first GUI version that allows real analysis and has more features than the command line version.

A quick overview of what is new:

• Swing GUI
• Analysis features as on the command line
• Copying of generated ALTER TABLE statements to the clipboard
• Information on data and index size distribution
• Rudimentary analysis of possible disk space savings

So go have a look :)

Over on my MySQL Index Analyzer blog I just posted two screenshots of the first working GUI drafts. If you are interested, you will have to check this version out from the SVN repository, I will prepare a new distribution package soon.

As did last week's Craig Mullins of dbazine.com I wrote a short post about the LogBuffer recently when I found it in my Blog's referrer list and was promptly asked if I would like to compile one myself.

So here am I am, welcoming all of you to the the 9th issue of the Log Buffer, a Carnival of the Vanities for the DBA community. Once again you will find a plethora of links to all sorts of information on the one thing that keeps many of us both fed and sometimes close to blank despair.

Myself being some sort of a mixture between a software developer and database admin I have had a fair amount of time over the last years to get experience …

I’ve been working with one Proven Scaling customer that has had some interesting issues recently, involving InnoDB corruption, resulting in messages similar to these:

InnoDB: Page checksum 3156980109, prior-to-4.0.14-form checksum 577557610
InnoDB: stored checksum 741279449, prior-to-4.0.14-form stored checksum 577557610
InnoDB: Page lsn 0 2323869442, low 4 bytes of lsn at page end 2323869442
InnoDB: Page number (if stored to page already) 195716,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 2831
InnoDB: (index PRIMARY of table db/table)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 195716.

The problem was encountered when testing hardware for a move from software RAID to hardware RAID using …

I just uploaded the first ready-to-use package of the MySQL Index Analyzer (version 0.02). You can download it via its download site.

I recommend going to the MIA homepage to see what's new and how to use it.

Recently I was reminded painfully of the fact that habits you have taken to once hardly ever get laid off.

I usually consider myself someone who tries to write software after I have thought it through. I do not mean "over-engineering", "over-abstracting" and "over-prepare-for-anything-that-might-ever-come'ing". However I also believe that starting hacking blindly is not a good thing either. And I try to write "nice" code, even though it might be a little more work, as long as it is easier to read or just plain more stable (which is often the same).

Sometimes however, especially under a tight schedule, by force of habit I (and probably any developer out there) tend to do things that upon later review make me feel deeply embarrassed. Just so I did a couple of weeks ago...

I had to write a component that translates data from a legacy system, stored in plain text files, into a relational database, accessed through an object …

Development of a more structured version is in progress. I just committed a set of changes to the repository that changes the current, rather monolithic, single-class design to what will be the first step of a more modular one.

Currently the generation of ALTER TABLE statements has been removed and the output format of the analysis is slightly different. But that is mere cosmetic.

What is much more important is that now the gathering of database schema information is based on a pluggable system, designed around an interface called IndexDescriptorProvider. Up to now I have just ported the MySQL 4 stuff that was already in the first version to this new architecture. Please feel free to have a look at it and tell me what you think.

Next thing I'll do is implement a provider based on the INFORMATION_SCHEMA database available in MySQL 5.x to see if I missed anything.

Go to …

I posted some Basic Documentation for MySQL Index Analyzer including a simple example. This is intented to get started with the tool without having to read the code.

Any ideas and suggestions for improvements are appreciated :)