Programming: ON DUPLICATE KEY MySQL weirdness

December 8, 2008 in programming

I had some weirdness with MySQL today… Updating on a duplicate key error works like a charm: it updates the row which violates with the key and everybody is happy!

Well, actually I’m not… I encountered this problem today. I had a table with a number and an average and the table named averages looked like this:

|      id |  number | average |
|       1 |       1 |       0 |
|       2 |       4 |    0.25 |
|       3 |       2 |     0.5 |

Now let’s say I wanted to increment id 1 with number=2 and an average=1, normally I would use the following query:

UPDATE `averages` SET `number`=`number`+2, `average`=(((`average`*`number`)+(1*2))/(`number`+2)) 
WHERE `id`='1';

This would update the table and set the number to 3 and average to 0.6666667. This worked fine till I added this to a cronjob which calculates averages from large quantities of rows. Since I would not know the identifiers upfront I changed the query to an INSERT query with a ON DUPLICATE KEY UPDATE part which updates when we already have a row for the identifier, so I came up with this:

INSERT INTO `averages` VALUES (1, 2, 1) ON DUPLICATE KEY UPDATE `number`=`number`+2, 

But, as I found out later, this doesn’t work the same way as the UPDATE query: the ON DUPLICATE KEY UPDATE does not write itself to a single UPDATE query, but rather to two seperate UPDATE queries. This means that the query will be written to this:

UPDATE `averages` SET `number`=`number`+2 WHERE `id`='1';
UPDATE SET `average`=(((`average`*`number`)+(1*2))/(`number`+2)) WHERE `id`='1';

Can you identify the problem I encountered here? And perhaps guess what the average column was set to?? 😉

PHP and MySQL stored procedures

March 31, 2008 in programming

Another programming entry today. 😉

Basically it isn’t really much, but it’s more or less a short description I encountered last week. Apparently this is very unclear and not well documented. So I thought I’d share it with the world. 🙂

Normally people use the Mysqli interface of PHP to invoke MySQL. Standard usage is open connection, query and then get result rows and close the connection. So you would use the functions Open, Query, Fetch_row, Close.

This scenario works for 99,9999% of the MySQL queries used in the world. However this does not work for stored procedures with multiple result sets!

The Mysqli Query function is capable of invoking the stored procedure, however if the stored procedure consists of a multiple result set it will only run the stored procedure up till the point of the first result set. The stored procedure can only be run properly by using Mysqli Multi_query function along with the Next_result function.

See also here:

And this is how it should be solved:

I hope this helps someone in the future. 🙂

Get video information from Youtube with Ajax

March 10, 2008 in programming

This will be overwritte with the title of the movie after loading the details through Ajax…

I started this small experiment for my work to see what the Youtube api is capable of and I found out that there are some very interesting things in their Api… I found use of getting the titles and stuff to re-use on the AEU86 forum. 🙂