Remembering Japanese cars from the past

Tag: MySQL

Down on the Street: Palo Alto Honda Accord CB

Also this photo of a Honda Accord CB was taken in the United States when I was visiting for the MySQL conference. We visited the Wallmart Superstore in Palo Alto as we were a bit disappointed last year by size of the normal Wallmart in Milpitas. We didn’t know there was such a thing as the Wallmart Superstore… They best way to describe it to my fellow Europeans: it is about as big as an Ikea! Yes, that huge!

Anyway, this Accord was parked in the parkinglot and I took three photos in total:
Down on the Street: Honda Accord CB
This photo is the only one that actually made it, the other two as pitch black (yes, so also around that black Accord! ;) )as the camera was being defunct again. Anyway, that problem should be solved now! :)

Down on the Street: Great Mall Nissan 370Z

Last week I attended the MySQL conference in Santa Clara (CA) as a speaker. I didn’t have much time to do fun stuff but I spotted some occasional cars parked around. This Nissan 370Z was one of them:
Down on the Street: Nissan 370Z
The car had a for sale sign at the rear window, so if you are interested you can probably find it somewhere parked at the Great Mall in Milpitas.

The other me

After yesterdays shameless plug I’ll do another one today! ;)

Not many people actually know what I’m doing for a living, so here it is: I’m a database specialist within Spil Games, specialized in high availability systems and MySQL. The latter is of course my main concern during the day.

If maintaining two blogs and a forum isn’t enough already I decided to dedicate another blog on my job and describe, in a high level overview, the challenges I encounter every day.

In case you are a techie and interested in this subject, you can find the blog here:
MySQL Quicksand

Programming: ON DUPLICATE KEY MySQL weirdness

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, 
`average`=(((`average`*`number`)+(1*2))/(`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?? ;)

© 2024 Banpei.net

Theme by Anders NorenUp ↑