Terminally Incoherent

Here is a little background for this issue. The database used to run on ancient copy of MySQL until very recently we upgraded it to 5.0. Not without some headaches of migrating the database, but it worked. Common problem we had with this database was duplicate entries in printed reports. Why did these happen? Mostly because of user error combined with a lack of foreign key constraints.

The two MyIsam tables that were causing the issue were: report and proforma. Both were used for tracking documents through the review process. Report was the actual field audit report that would be submitted, reviewed and sent to the clients, while proforma was the documentation of expenses that our employees were supposed to submit for each assignment. Both would store dates, comments, notes and other info regarding said documents. The proforma had a 1-1 relationship with report (each proforma was tied to a single report entry). But since the old MySQL version did not support Foreign Key constraints this was not enforced (or rather only enforced by the PHP front end).

However users would continuously find ways to submit multiple proformas for the same report - for example by clicking the “submit” button 7 times. Other fun trick for re-submitting proforma was to send it in as TBA (to be annouced). Upon seeing the TBA the clerical staff in the office would manually associate it with appropriate report, usually without checking if another copy is already in the database. And when we locked that down, they would just continuously send bug reports about it forcing us to sort this out at the DB level.

What happened if the non-existent FK constraint was violated? One of the main reporting sections of the site used a complex join across these two tables. If there were two or more proformas per report, that report would show up on the list multiple times. What do people do when they see duplicates on the list? They start deleting them. Problem is - these were not real duplicates, but the same entry repeated several times. Deleting one copy would hose all of them, causing yet more support request for restoring the entry from the nightly DB dump.

Fun times. So after we switched to a DB engine that was actually developed in this century I immediately switched the tables to InnoDB and put a freaking foreign key constraint on that relationship. This way if someone finds a new loophole in the PHP code they will end up seeing a nice MySQL error instead of creating new duplicates.

But guess what - I just got more bug reports about duplicate entries. Apparently the FK constraint is not retroactive, and MySQL will gleefully allow duplicate keys to exist in your FK column when you apply the constraint. I figured that it would start trashing about and keep giving me errors if that was the case, but no - all it cared about was an index on the key column. So I was left with a task to track down all the duplicate entries in the foreign key column in my proforma table. How do I do this? This problem actually got me scratching my head for a bit, until I had a sudden epiphany:

SELECT 
	id, report_id, count(*)
FROM
	proforma
HAVING
	count(*)>1

I found around 17 separate instances of the foreign key (here report_id) being repeated anywhere from 2 to 8 times. All of these were for older entries that no longer show up on the first page of results, so naturally no one noticed. Still, it shows that the problem was more prevalent than we initially suspected.

What I’m really trying to say here is this: use database constraints to enforce table relationships. This is the only proper and effective way to do it. Trying to enforce constraints in software is just asking for trouble as you create multiple points of failure. Different parts of the code will update any given table at different times, and all of them must check this. Failing to implement proper checks in every piece of code that touches that table may lead to duplicate entries in FK columns and similar undesired side effects. A simple constraint on a column will stop this from happening much more effectively, with much less effort.

I noticed that on one of the servers the MySQL Admin was behaving weird. When you tried to inspect the schema of some tables by double clicking on it’s icon it would generate the following error:

Access violation at address [address in hex] in module ‘libmysqlx.dll’. Read of address 00000000

Here is a screenshot of the error dialog:

Some tables would trigger this error, while some others wouldn’t. Quick investigation revealed that this server was recently upgraded to MySQL Server 5.0. The MySQL Admin apparently was not upgraded and was still at version 1.0.19. I checked the most recent builds of MySQL GUI tools and the version of the Admin application in the bundle was 1.2.12.

I upgraded the package, and the error went away. Apparently the error was caused by some kind of mismatch between what the database was actually doing vs what the old version of Admin expected it to do.

I saw this error being posted in several places online so I figured I will put it here and provide a definite solution: upgrade to the most recent version of MySQL Administrator.

In before “real men use the command line client… On linux!” - I do too. But MySQL Admin is sometimes useful for setting up db dumps, or quickly changing table settings that I don’t remember SQL commands for (like encoding for example). Plus, I can’t stand when shit is not working - even if it’s something I don’t use very often.

This error has been driving me insane all morning. Every time I was trying to create a new InnoDB table with a foreign key constraint I would get the following error:

Error 1005 Can’t create table ‘.\mydb\#company.frm’ (errno: 150)

My SQL looked something like this:

CREATE TABLE company (
	`id` int(32) AUTO_INCREMENT NOT NULL,
	`name` varchar(50),
	`cfo` varchar(50),
	`ceo` varchar(50),
	`address` text,
	`tel` varchar(15),
	`fax` varchar(15),
	`email` varchar(50),
	`general_type` enum('broker', 'manufacturer', 'wholesaler', 'service', 'distributor', 'retailer', 'healthcare', 'processor'),
	`specific_type` varchar(50),
	`lender_id` int(32),
	PRIMARY KEY (id),
	CONSTRAINT `fk_lender` FOREIGN KEY (`lender_id`) REFERENCES `client` (`lender_id`) ON UPDATE CASCADE,
  	CONSTRAINT `fk_specific_type` FOREIGN KEY (`specific_type`) REFERENCES `specific_type` (`specific_type`) ON UPDATE CASCADE
) ENGINE = InnoDB

I kept getting that damn error every single time. At first I figured this was a OS level file access permission issue. But this is not the case - it is just a silly, misleading message. I started googling for it and found some good tips on how to avoid this error at VerySimple Dev Blog. I spent over two hours testing all possible solutions listed in that post. Both tables - client and specific_type were InnoDB. The respective keys were the same type. The charset and collate attributes were set to default in both. And then it hit me.

It turns out that my problem was simple - you need to have an index on every field that will be a foreign key. I kinda figured that this would happen automatically, like with primary key, but it doesn’t. You need to create the index manually. My updated query looks like this:

CREATE TABLE `company` (
	`id` int(32) NOT NULL AUTO_INCREMENT,
	`name` varchar(50),
	`cfo` varchar(50),
	`ceo` varchar(50),
	`address` text,
	`city` varchar(50),
	`state` varchar(50),
	`zip` char(2),
	`tel` varchar(15),
	`fax` varchar(15),
	`email` varchar(50),
	`general_type` enum('broker','manufacturer','wholesaler','service','distributor','retailer','healthcare','processor') NOT NULL DEFAULT 'broker',
	`specific_type` varchar(50) NOT NULL,
	`lender_id` int(32) NOT NULL,
	PRIMARY KEY  (`id`),
	KEY `specific_type` (`specific_type`),
	KEY `lender_id` (`lender_id`),
	CONSTRAINT `fk_lender` FOREIGN KEY (`lender_id`) REFERENCES `client` (`id`) ON UPDATE CASCADE,
	CONSTRAINT `fk_specific_type` FOREIGN KEY (`specific_type`) REFERENCES `specific_type` (`specific_type`) ON UPDATE CASCADE
) TYPE=InnoDB

Somehow this escaped my attention. It’s probably because lately was I simply adding foreign key constraints to existing tables after converting them to InnoDB from MyIsam. It worked fine because in most cases these tables already had indexes on the “foreign key” fields - there were just no constraints there.

So next time you are creating a InnoDB table, remember - put index on every field that will become a foreign key, unless of course you like to deal with overly cryptic error messages.

SHOW InnoDB STATUS

Here is a quick and dirty way to see if the “MP” part of your LAMP or WIMP solution is ready for the DST time change. I tested all the production systems here today, and they seem to be working just fine. Here is what you do.

First let’s test MySQL:

SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00')=UNIX_TIMESTAMP('2007-03-11 03:00:00');

The two timestamps should be identical, because of the DST change. So this query should return 1 (one, uno, jeden). If you get anything else, your MySQL is b0rked and you need to patch it.

Same method can be applied for PHP. Paste this code in somewhere and then look at the page:

<?php echo mktime(2, 0, 0, 3, 11, 2007) == mktime(3, 0, 0, 3, 11, 2007); ?>

If you get 1, you are good. Anything else, and you are in a need of a patch.

Note, by default both MySQL and PHP will grab the time from OS so unless you specifically messed with their configuration to change this, chances are you will be just fine. Still, it’s always good idea to check.

This is really sad, but it appears that MySQL will no longer provide free binaries for their Community branch.

From what I understand, the Community version will continue to be released as normal, and distributed under GPL, but MySQL will not provide binaries for download on their own site. Precompiled binaries should still be available in the form of packages from various linux vendors if they choose to include them in their package trees.

In other words nothing really changes for hosts of linux users out there (especially for apt and yum users who can rely on rich repositories to always contain the newest releases). On the other hand Windows users should be relatively upset about this because it means they will need to build from source each time a new version is released.

That is, unless someone takes it upon them to provide windows MySQL binaries to the masses and build some nice installers for them. I imagine that whoever manages to establish themselves as a trustworthy source can probably expect some good traffic and hence add revenue. I’m actually tempted to buy mysql4win.com - if nothing else, just to see if anyone really wants it.

found via homo-adminus blog