MySQL's GROUP_CONCAT limitations and cascading bad luck

We had an incident today over at Teamup (where I have worked for the last 9 months by the way:) which is worth a quick blog post if it helps save anyone from having a bad day.

We are using MySQL's GROUP_CONCAT feature to fetch a list of ids to delete when cleaning up old demo calendars. You end up with a list of ids in one row, easy to fetch, split it on commas, and done. So far so good. Then we run a few DELETE ... WHERE id IN (...) queries to clean things up in a few tables. So far so good.

However if you fail to read the fine print on the MySQL docs, you might not have seen this sentence: The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. What this means is that a query that worked just fine in testing conditions, suddenly started failing in production once the data set hit a critical size. Thanks to another stroke of bad luck, it returned a list of ids truncated right after a comma (3,4,5,) so we had an empty id in our WHERE IN (3,4,5,) clause. Unfortunately combined with the fact we had optional relations in some tables (I won't bore you with details) that empty match made it wipe about 60% of the data in those.

Thankfully we have backups on top of the DB replication which let us recover the lost data pretty quickly, and it only affected a small feature in the grand scale of things, but this could have ended much worse so it is worth pointing out a few things:

  • If you use GROUP_CONCAT and expect large amounts of data returned, make sure to increase the limit before executing your query. For example this sets both the max length for the group concat and the max packet length (which caps the former) to 10MB SET SESSION group_concat_max_len = 10485760, SESSION max_allowed_packet = 10485760;. Use more if you think you need more.
  • Maybe for safety using GROUP_CONCAT should be avoided if you don't know how much data to expect, simply fetching ids and then fetching all rows at the program level does the job too.
  • Do snapshot backups even if you have replication in place, it can save your ass!

And now to hope for a more quiet rest of the week!

Edit: There is some good news, MySQL 5.8 might include a fix and turn the current warning for truncation into an error, see

August 11, 2015 // PHP, Web // Post a comment

Writing games for fun

GitHub organized a Game Off - or a game competition - back in November. The contest ran for a month and its only limitations were that the game runs in a browser and relate somewhat to forking/cloning/pushing/pulling. And I am here to tell you that no matter what kind of programmer you are, you should take part in such contests!

I am hardly a game developer. I do spend most of my time writing web things in both PHP and JS, and I had not worked on a game in at least 5 years. This sounded like a good opportunity for a little change. It is easy to get stuck in what you do once you do it well, but just like I enjoy playing with other languages every now and then, working on a different product in a familiar language also offers interesting challenges. Game mechanics, canvas rendering and more visual programming are all things that I am not used to work with.

Long story short, I came up with this small simple Split game that I invite you to try out. It was a lot of fun to write, and given the 1 month deadline you do not have time to mess around and let feature creep take over. You have to get things done fast. It is a great exercise both for programming skills and time management/prioritization.

If you are interested in seeing other entries to the contest, there is a full list available but it does contain quite a lot of incomplete and barely playable games. Having gone through most of the list, I can recommend those few games that I enjoyed, mostly because they went out of the beaten path and are trying something new: Echo, Radiance, Mazeoid and Release Cycle.

Mozilla is running a similar contest until February, so there is your chance to get your hands dirty over the Christmas holidays. You have nothing to lose, and building games is both fun and challenging!

December 02, 2012 // Web, JavaScript // Post a comment

Terminal (Bash) arguments tricks

Reading David DeSandro's last post on how to store strings in variables in terminal, or any bash-y shell (I'd say any unix shell but I'm sure there is a weird one out there that does things differently) for that matter, it struck me that many web developers seem to have a big disconnect with the shell.

Now I'm no expert, but I know that the use case he describes can be solved much more efficiently, so I felt like writing a little follow-up, and hopefully teach you, dear reader, a thing or two. The short story is that you sometimes want to do many operations on the same file. Now the neat trick to do that is to use history expansion, which allows you to reference one of the parameters from the previous commands you typed.

As always with unix stuff, it has simple useful basics, and then it can get really hairy. Here are a few examples, from most commonly useful to those things you just won't remember in five minutes.

# First, the example from DeSandro's post
# !$ references the last argument of the previous command.

mate _posts/2011/2011-04-12-terminal-strings.mdown
git add !$
tumblr !$

# Now more complex, let's copy the second argument
# !! references the last command, and :2 the second arg. 

echo foo bar baz
echo !!:2 # outputs "bar"

# Batshit crazy
# !?baz? references the last command containing baz, :0-1 grabs the two first args

echo !?baz?:0-1 # should output "echo foo"

Now if you've been paying attention, the second example had !! in it that referenced the last command. This one is really useful for all those times you forgot to sudo something. Just type sudo !! like you really mean it, and it will copy your last command after sudo. It does not work if you add cursing to it though.

So read up those history expansion docs, it's really worth if only to know your options, and if you know other related tricks, please do share in the comments.

April 13, 2011 // PHP, Web, JavaScript // Post a comment

Speaking at the IPC and WebTechCon

Next week the International PHP Conference and the WebTechCon will happen both in Mainz, Germany. I will speak at both events over the three days and the good news is that the combined 100 sessions are available for attendees of both conferences.

My only talk as part of the IPC is entitled Of knowledge sharing and the developer quality lifecycle, it's non-technical and will hopefully be more a seeded discussion than a plain presentation. We will talk about the ways to share knowledge within a company in the Gutenberg III room, monday at 11.45.

My second and third talks will be part of the WebTechCon schedule, but I think they are very good fits for PHP devs nonetheless. On tuesday at 10.15 as part of the JavaScript Day I will talk about JS Events and Scopes. Every web developer should understand those concepts so I would highly recommend you attend if you don't know how the this variable is bound in event listeners, or have never heard of variable hoisting.

The final talk will be part of the Web Security Day, and touching on the essentials of web security, the things you just can't afford to ignore. The talk is on wednesday at 9.00am however, so plan ahead and avoid getting too drunk if you want to attend :)

And finally, if anyone wants me to do some informal Symfony2 presentation, I got slides ready and would be very happy to do that, so just come and ask.

October 07, 2010 // PHP, Web, JavaScript // Post a comment

HTML5 my ads

First came Flash. Then came advertisers that thought it'd be great to abuse it. Then came Flashblocktm and friends. The geeks don't like Flash, it's evil.

Now the iPhone & iPad don't have Flash, but they're the shit. Advertisers want a part of the shit of course, and they can put shiny ads in native apps, but not in the open apps that are websites. Thank god there is HTML5, geeks love it, it's all great. So you've got solutions like SmokeScreen developing. It's Flash without Flash, everyone should be happy right?

Have you looked at their demos? Ads. Yes. I'm not saying they won't do more one day, but for now the target market seems to be advertisers, so that they can put out Flash-like ads in HTML5, reach the iDevices and to kill two birds with one stone, they'll reach the geeks that run Flashblock. And all that while interpreting Flash content so it'll most likely be more of a resource hog than Flash is.

What's the solution? CanvasBlock? Noscript? In the end people will find ways to abuse anything.

Of course Apple could come back to the table and implement Flash in the next iPhone OS, instead of having us suck up some half-working slow Flash ersatz on top of completely broken sites.

June 02, 2010 // Web, JavaScript // Post a comment

The joys of user stylesheets

User stylesheets are a way to inject some CSS in all the sites you visit, each browser has his own way of setting it up (if you use opera step 2 there should be replaced with: "Tools > Preferences > Advanced > Content > Style Options > Select your css file in My stylesheet"), but the idea is always the same.

I've recently found a couple of use for these styles so I thought I might as well share :

Changing gmail's font

I like gmail, but losing my dear monospaced font was annoying me - especially when reading code-related mails with snippets in them. So this little hack allows you to choose the font used in the mail body area of the page. It's made for the "old" gmail interface since I don't have the new one yet, but it can probably be adapted if it doesn't work with the new one.

.XoqCub .ArwC7c {
  font:16px proggytinytt, "courier new", courier !important;
  font-size:16px !important;

This uses the proggytinytt font by the way, which is my font of choice for all monospace purposes, however if you don't have it it falls back on courier new/courier.

Saving flickr's images peacefully

Some images on flickr seem to be protected with a file called spaceball.gif that's overlayed onto the actual image, so that when you right-click it to save, you hit the transparent gif and can't save the image. With the help of that great CSS3 selector :nth-child(N), you can make sure you hide the gif if it's there.

.photoImgDiv img:nth-child(2) {
	display:none !important;

If you've anything useful, feel free to post it in the comments.

November 28, 2008 // Web // Post a comment

Installing Habari on Lighttpd

Just a small post about Habari installation over Lighttpd, since it is not really documented anywhere that I could find.

I will assume that you know how to run php scripts on your server, and start from there. So once you have unpacked Habari files in say /home/seld/, all you need to do is add the following to your lighttpd.conf file :

$HTTP["host"] =~ "^(www\.)?domain\.com$" { 
    server.document-root =  "/home/seld/" 
    url.rewrite-once =  ( 
        "^/(?!scripts/|3rdparty/|system/|doc/|user/(?:themes/|files/|plugins/|locales/|sites/))[^?]*(\?(.*))?" => "/index.php/$1"

Update: See also the Habari wiki on how to set that up, they have since then added docs on Lighttpd support

With this setup, your blog must lie in the top level directory ( ), should you want to install it in a subdirectory, you need to add it to the url rewrite, for example to install in you would need to replace line 4 with:

"^/blog/(?!scripts/|3rdparty/|system/|doc/|user/(?:themes/|files/|plugins/|locales/|sites/))[^?]*(\?(.*))?" => "/blog/index.php/$1"

August 02, 2008 // Web // Post a comment