The problem for today goes like this: there is a table in the database, and it has a field foo that holds some information. The field is initially NULL, and there is an update query that changes it to ‘bar’ when the user does something. But we want to update foo only when it’s NULL and leave it alone otherwise. In other words, if it already contains the value ‘baz’ we want to keep it the way it is.

The challenge? Do it without modifying any existing logic, and without introducing a second query to look up the value of foo. How to we accomplish this by simply modifying the existing update query? It’s easy – just use the built in MySQL branching IF statement like this:

UPDATE mytable 
SET foo= IF(foo IS NULL, 'bar', foo)
WHERE id='69'

This will test whether or not foo is NULL, and then set it to ‘bar’ if it is, or re-set it to whatever it is right now otherwise. Now this may seem wasteful (ie. the update on false condition) but imagine this update statement actually sets not just foo but a number of different fields like this:

UPDATE mytable 
SET 
   foo= IF(foo IS NULL, '1', foo),
   bar = '2',
   baz = IF(baz > '10', 'high', 'low')
WHERE id='69'

You can do all kinds of behind-the-scenes magic with a query like that, and make it as simple or as complex as you want.

Anyways, if this post bored you to tears, I apologize. Stuff like this will crop up here every once in a while though. It’s just a fact of life.

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.

[tags]mysql, sql, database, database design, foreign key, key, constraint, php[/tags]

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.

[tags]mysql, sql, mysql administrator, mysql gui tools, gui tools[/tags]

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','ret ailer','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

[tags]mysql, error 1005, errno 150, can’t create table, database, mysql error, innodb, foreign key[/tags]

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.

[tags]dst, mysql, php, daylight saving time, time change, timestamp[/tags]

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. :P

found via homo-adminus blog

[tags]mysq, binaries, windows, linux, databases[/tags]

Since the key is generated upon insertion of a new row, you can never know its value before the SQL statement is executed. Furthermore, it can be problematic to insert a new row into a table, and then use its key as a foreign key in another table. One could potentially follow the insert statement with a selection, but unfortunately it is possible that the primary key is the only unique value that distinguishes several rows. We can never be a 100% sure that we are getting the right one, using a selection statement.

MySQL provides us with a nifty feature that does precisely what we need:

SELECT LAST_INSERT_ID()

Lets say we have two tables tab1(id, value) and tab2(id, value, tab1_id). To insert a new row to tab1, and then new row to tab2 while referencing tab1 we can do this:

INSERT INTO tab1 (name) VALUES ('some value')
INSERT INTO tab2 (value, tab1_id) VALUES ('another value', LAST_INSERT_ID())

If you are working with PHP, this whole thing becomes even easier. The mysql module contains a great function that lets you get the id of the last inserted row:

$last_inserted_row = mysql_insert_id($dblink)

Here, $dblink is a database connection identifier variable (the one returned by mysql_connect statement). The returned key is found based on your unique connection, you can safely assume you are getting the correct value, even in a multi-user server environment. Other users, connecting to the db at the same time will get last unique id’s that match their connection.

[tags]php, mysql, database, primary key, foreign key, sql, insert, last inserted id[/tags]

Some of these are common sense (eg. index stuff, but don’t index everything), while other are little nuggets of awesome DBA knowledge that only come with experience and expertise.

It’s something you should definitely check out if you work with MySQL.

[tags]mysql, performace, database, dba, administration, tips[/tags]

SELECT COUNT(*), WEEK(mydate)
FROM mytable
GROUP BY WEEK(mydate)

The problem here is, that the output is totally meaningless to me as I do not have a clue what does week 36 mean. What I really want is to have on the screen is a nice, human readable date interval – the beginning and ending date of any given week.

Surprisingly, this turns out the be a major pain in the ass. There is no simple function that will yield a week interval (or start/end date of a week) given a week number. You have to find these dates manually. Here is how I did it:

SELECT
    COUNT(*) AS reports_in_week,
    DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY),
    DATE_ADD(mydate, INTERVAL(7-DAYOFWEEK(mydate)) DAY)
FROM
    mytable
GROUP BY
    WEEK(mydate)

How does it work? The DAYOFWEEK() function returns an integer ranging from 1 (Sunday) to 7 (Saturday). So if mydate happens to be Tuesday we get the following statements:

DATE_ADD(mydate, INTERVAL -2 DAY)

which essentially means “subtract 2 days from mydate (which is that week’s Sunday) and also:

DATE_ADD(mydate, INTERVAL 4 DAY)

which yields the date of that week’s Friday.

One would think that there would be a function to accomplish this automatically, but alas there is none. I think this is as simple as it gets. I hope this helps someone, because it took me quite a while to figure this out.

[tags]mysql, weekly, weekly reports, time functions, sql, databases[/tags]

And yes, they specifically asked for Excel format, and the future reports must have “export to Excel” feature.

Anyways, here is why I love MySQL:

SELECT * INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM my_table;

You get a CSV file formated nicely for Excel. Now you can just open it, and save it as xls file if that is what you need.

If your table is to big for Excel you can of course use a WHY clause to cut down the number of results.

[tags]mysql, excel, mysql export, sql, databases[/tags]