Pages

Friday, October 19, 2007

MySQL : the beauty in beta

Beta or not, here we come. Yea, you'd think I'd learn to check out the known bugs list before letting developers/qa have at the new database servers running in the lab with MySQL 5.1.21-b . I mean, how bad could beta really be?

Well, as we should all know, beta is called beta for a reason, but sometimes we open source fanatics tend to jump on board, dealing with issues along the way. This sometimes ends up causing a lot of heartache tracking down issues that turn out to be 'known bugs' in a beta version.

Lesson learned (again) :

Yesterday I received a note from one of our lead developers, that went something like this:


It seems that our test mysql instance is very picky about honoring desc versus asc queries (qa confirmed it).

If you run this query it will return in asc regardless of the order by.

select id
from registry_service.ALIAS
where (owner_uri_path_id = 700 and owner_uri_ref='Account/3760')
order by id desc;
----data returned ---
107538
107539
107540
107541
107542

Now if you change the column that you are ordering by, it seems to work…

select name
from registry_service.ALIAS persistent0_
where (owner_uri_path_id = 700 and owner_uri_ref='Account/3760')
order by name desc;
---- data returned----
'7SortAliasTest.20071018.141531980.141'
'7SortAliasTest.20071018.141531980.131'
'3SortAliasTest.20071018.141531980.151'
'0SortAliasTest.20071018.141531980.161'
'0SortAliasTest.20071018.141531980.121'

I’m very confused ; can you help me understand what’s going on?



Well it took all of 2 minutes and 1 Google query to get an answer.

Known beta bug ( http://bugs.mysql.com/bug.php?id=31001 ).

Just a little foresight on my part could have saved development and test both a lot of hair pulling and time wasted had I evaluated the known bugs list before letting dev and qa have at the new MySQL instance. They could have know that this issue was there, and it would be fixed in the 5.1.22-rc release, which I already have loaded on our new development servers.

Shoulda, Woulda, Coulda...


Wednesday, October 17, 2007

MySQL innodb plugin

Recently I've spent hours compiling MySQL 5.1.22 and trying to get my cent0s configured for innodb and partitioning. I tried every imaginable way to run configure, use different plugins, etc.

I messed with this forever, then finally looked at the config options, and sure enough, the ./configure expects innobase, not innodb as the plugin name:

from: http://www.innodb.com/support/tips

On MySQL 5.1, ./configure expects a different parameter:

$ ./configure –with-plugins=innobase

Monday, October 15, 2007

SQLServer: Image Replication - Oops

Yea, it's always something, huh?

Better mind your default configuration options with SQLServer 2005 if you plan on transactional replication with LOBs. It's nice that they only replicate the appropriate changes, but you'll get an error if you are using images with sizes > the default replication size allowed.

See the Microsoft article for more info.

Fun... :)

Monday, October 01, 2007

SQLServer: Transactional Replication Issues

After seeing a few friends at the SQLPass 2007 Summit in Colorado and discussing some of the finer 'features' of pier to pier transactional replication and large database systems (my friends work on the replication team at Microsoft), I quickly ran into one of my big grips with P2P replication and SQLServer 2005.

I had noted that being able to set up P2P from a 3rd party backup tool is essential with large databases (> 100GB) because creating a backup with SQLServer 2005 just takes way too long. The problem is, however, that SQLServer must know which LSN the backup was on when it was restored to the replicated server, so that any transactions that have occurred on the main database since the backup was taken can be correctly applied in the correct order.

The only way for SQLServer to get that is by reading the header in the backup file, which can't be done via a third party backup tool (SQLLightspeed, SQLBackup, etc, etc). My friends implied that if I knew the LSN, it could be done. I'm not quite there yet.

However, it wouldn't be that hard for Microsoft to allow a third party backup tool to call a procedure letting replication know what the current LSN is, thus fixing the problem. Hmm...

Maybe one day. For now, we'll have to deal with 5 hour backups and 2 hours restores in order to setup P2P transactional replication from backup, at least until SQLServer 2008 come around and do the backups for us with it's supposedly blazing fast compression, etc.

I'm not holding my breath....