Blog RSS Feed Subscribe

Jordi Boggiano

Jordi Boggiano Passionate web developer, specialized in web performance and php. Partner at Nelmio, information junkie and speaker.


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

20 Years and 9 Days of PHP

Here is my (slightly late) take on Ben Ramsey's call for stories on how people got started with PHP (although at this point I feel he just did this to get his blog to rank number one on all PHP related searches).

I can't retrace exactly when it was that I got started playing with PHP, but I guess I was around 18. Given that I am 30 that's.. a while ago, and it places it around 2002-2003.

I had been doing web things as plain HTML + some basic flash animations since '99 or 2000. But until PHP my only programming (as in having logic, not just markup) had been writing and publishing mIRC scripts. I guess those were my first open source contributions in a way although it wasn't called that and I wasn't really thinking about it. By some miracle my ISP from back then is still hosting this site 10 years after I haven't sent them a dime, so kudos to them, you can still enjoy the table layout, green on black text, flash menu and other niceties.

PHP Beginnings

Back to PHP because Ben didn't ask for a complete life story; the earliest code I still have, that is also the first big thing I remember building with PHP, was a web forum. phpBB had existed for a few years already, and I am sure other forums did too, but I was probably too silly to even think of looking for existing software. When a forum I was on experienced a big civil war of sorts (online arguments lasted longer than a 1h twitter shitstorm back then) I did the logical thing and started writing my own so we could split and leave these suckers behind.

According to an early db dump I did my first post in there on 2003-09-17 on MySQL 3.23 and PHP 4.2.0 (although 4.3 was out for almost a year, but who ever upgrades PHP right?). I soon learned my first security lessons as well, for example that header("location:index.php"); is NOT a safe way to protect sensitive pages. Maybe that was payback for the IRC takeover scripts I wrote.

For some odd reason looking at that code it seems that I was using crypt() to store hashed passwords. The hash was an undefined constant of course because who needed strings back then, but at least I might have a claim that I never stored passwords in clear text!

Moving on..

After a few years of studying mostly unrelated things, I worked on a PHP framework/CMS for my graduation work. That monstrosity is still running this very website at the time of writing but eh at least it renders pages in a few milliseconds! On the way there I got a few odd summer jobs for local agencies, and spent a summer month (in 2006 I believe) doing absolutely random things for very little money on oDesk or a similar online-freelance site. I guess the highlight there was a job doing new features for the Internet Book List which felt like hacking on iMDB for books, pretty cool at the time but given the design of that site hasn't changed since I am afraid some of my code is still running it.

Open source

In 2008 I got my first fulltime programming job at Liip after graduating. A few months before that to occupy winter holidays I worked on the Dwoo templating engine which I needed for my graduation work CMS and was also my first real open source PHP project. This time around I knew about Smarty but still decided to write it from scratch because I had grand ideas about improvements and Smarty development had pretty much stalled. The old site is still up on but I have since passed on the lib to someone that was interested in taking over so there is a new site and a v2.

In early 2010 I attended the Symfony Live event in Paris where Symfony2 development was announced and I was sold by the promise of leaving all the old framework code behind and starting from scratch (you might notice a pattern by now). I started contributing quite a bit and worked on that as time allowed for a little over a year.

In 2011 I started working on monolog mainly to get rid of the gigantic ZF dependency we had to pull in only to use a couple Zend_Log classes (I can admit it publicly now that we are all friends, right?). It was before Composer and git cloning the whole ZF repo was quite a pain.


A few months later as the release of Symfony 2.0 approached I joined Nils to work on Composer and around the same time quit my job and founded Nelmio which afforded me quite a bit of free time as we had no customers for a while ;) Composer took more and more of that time and I had to distance myself from Symfony as I just could not follow it all anymore.

Now 4 years later I am still working on the same old Composer. While it has been immensely rewarding to see something grow like that and have such an impact on the PHP community at large, my write-something-from-scratch instinct is starting to itch.

Here is to twenty more years!

June 17, 2015 // PHP // Post a comment

Composer hosting improvements

Over the last two weekends I proceeded to upgrade the infrastructure behind and There is now a lot more bandwidth and the DNS hosting was also migrated to DNSMadeEasy which should make it more stable on that front.

All in all this should result in faster composer updates which is great! I also took the chance to do a few upgrades on the config, which now includes the latest PHP/nginx versions as well as OCSP stapling directives. While looking at logs I also noticed the composer installer was not using gzip compression so I fixed that and now installing composer itself should be a bit faster too if you have the zip extension enabled.

While the whole change happened without packagist downtime, there were a few minor issues early last week with the composer homepage. Today I had to re-enable http (i.e. no-TLS) access to as people are still relying on accessing it without the openssl extension. This is not great but the travis 5.3.3 builds are missing openssl so it's not realistic to require it, hopefully in a year or two when 5.3 is a thing of the past we can go full-TLS.

Also note that the GitHub hooks updating packages on packagist are also using http and not https so the hooks using the default domain ( - I sent them a PR to update it to https by default) were not properly updated in the last ~24hours. I am really sorry I missed that but there is not much I can do at this point except wait until next time a hook fires and those affected packages finally get updated.

Please get in touch on github if you spot anything else out of the ordinary (except faster updates;).

And finally a quick shout-out to our sponsors, if you haven't checked Toran Proxy out yet please do so as it allows me to pay for the hardware as well as get some time off work to focus on open source without it always being unpaid leave. Many thanks to all existing Toran customers for their support!

May 03, 2015 // News, PHP // Post a comment

Composer 1.0 alpha9

I tagged Composer's 1.0.0-alpha9 release yesterday and wanted to write down a more detailed update on the highlights of this release. It includes many changes as the last tag was almost one year old. You can also check the full changelog if you want more details.

Requiring packages from CLI just got easier

The require command now guesses the best version constraint for the latest stable release of the package you require, so if you know you need a package but don't know what the latest version is you can just use for example composer require monolog/monolog and it will guess and use a constraint like ~1.11. There is also a new remove command to easily remove dependencies from the CLI without having to touch json.

Installing dependencies on the wrong environment is now possible

The new --ignore-platform-reqs flag for the install and update commands lets you install dependencies even if you have the wrong php version or are missing one of the required php extensions. It's not really recommended but it can be useful sometimes if you want to run composer outside a VM for example and you only have the right extensions installed in the VM where you run the code.

You now get warnings when installing abandoned packages

A few months back Packagist got this feature allowing packages to be marked as abandoned. For example this php markdown fork is not maintained anymore as the original package is now on Packagist as well. Marking it as abandoned lets people know they should ideally not rely on it in new projects, and migrate away if possible. If you install this package Composer now warns about it: Package dflydev/markdown is abandoned, you should avoid using it. Use michelf/php-markdown instead.. This should make it easier to deprecate packages and let your users know about it.

Custom composer commands via scripts

Script handlers defined for a non-existing event are now registered as custom composer commands. So you can for example define a test handler with the command to run your test suite, and then use composer test to call it. All the binaries installed as dependencies like phpunit in this example are made available in the PATH before the command is executed so you can call them easily without using the vendor/bin/ prefix.

Autoloading tests and related files

The new autoload-dev section lets you define autoload rules that only apply when your package is installed in dev mode, much like require-dev. This is mainly useful to split off test autoload rules from the main autoload rules the users of your packages will need.

Performance improvements

In case you missed it last week, we gained a huge performance boost by disabling garbage collection when running the Composer dependency solver. This is great news for everyone but I wanted to stress once again that you probably should not disable GC in your scripts unless you have a very good reason to do so. PHP's GC isn't flawed per se but our use case just falls out of the use cases it's been designed for. Anthony Ferrara wrote a good round-up of the issue in case you want to learn more.

A note on supporting Composer maintenance

While many of the above features have been contributed by others, reviewing pull requests and checking on new issues every day does take a lot of my time. I launched Toran Proxy about six months ago to help me get some paid time to work on and maintain Composer and Packagist. Looking at the numbers now it seems about 20% of the time spent was paid for thanks to Toran Proxy customers. I want to thank you all for supporting me and I hope more people will join in! I will try to do updates like this one more regularly to highlight new features and developments in the Composer ecosystem.

December 08, 2014 // News, PHP // Post a comment

My view of PHP version adoption

PHP 5.3 has been out of maintenance for about three months now and it seems like it's time for the community to move on at last. Drupal8 will target 5.4. Symfony announced the results of a poll about which PHP version Symfony3 should target (TL;DR: 5.5 and 5.6 are preferred). And Pascal Martin released yesterday an update on his PHP usage stats based on scanning server headers.

Pascal's number are interesting but I believe they have a bias towards older PHP versions. I would argue that people configuring their servers properly are also those that tend to keep up to date with newer versions, and part of the best practices is to avoid publishing the software versions you are using (i.e. disable expose_php in php.ini). If I am correct here that means early adopters are mis-represented in those numbers.

In any case, I do have another biased dataset to present so here it comes! I looked in the logs of the last fifty days for GET /packages.json which represents a composer update done by someone. Since Composer sends the PHP version it is running with in its User-Agent header, I can use that to see which PHP versions people are using Composer with. Of course this data set is probably biased towards development machine and CI servers and as such it should also be taken with a grain of salt.

PHP usage statistics

I have two datasets, from November 2013 and today, which shows the progression of various versions. Any version below 3% usage has been removed to keep things readable.

November 2013

All versions Grouped
Total 4112760   100.00% Total 4112760   100.00%
PHP 5.3.10 490350 11.92% PHP 5.4 2069021 50.31%
PHP 5.3.3 419871 10.21% PHP 5.3 1533073 37.28%
PHP 5.4.20 387450 9.42% PHP 5.5 510596 12.41%
PHP 5.4.4 274741 6.68%
PHP 5.4.9 198343 4.82%
PHP 5.4.16 180150 4.38%
PHP 5.4.19 167416 4.07%
PHP 5.5.3 166317 4.04%
PHP 5.4.17 160754 3.91%
PHP 5.4.21 144939 3.52%
PHP 5.3.26 131497 3.20%

November 2014

All versions Grouped
Total 11556916   100.00% Total 11556916   100.00%
PHP 5.5.9 2475970 21.42% PHP 5.5 5647892 48.87%
PHP 5.4.4 1022498 8.85% PHP 5.4 3305929 28.61%
PHP 5.5.17 678997 5.88% PHP 5.3 1716653 14.85%
PHP 5.5.16 529227 4.58% PHP 5.6 886260 7.67%
PHP 5.3.3 509101 4.41%
PHP 5.3.10 479750 4.15%
PHP 5.6.0 391633 3.39%

A few observations: 5.3 really is on the way out, 5.5 is now the major platform, 5.6 adoption is lagging behind last year's 5.5 adoption a little bit which is unfortunate but can perhaps be explained by the fact Ubuntu 14.04 ships with 5.5.9

PHP requirements in Packages

A second dataset I looked at is which versions are required by all the PHP packages present on packagist. I split it in two groups: those that required a given version at any point and those that require it in their current master version.

PHP Requirements - Anytime - November 2014

5.2 1204 4.23%
5.3 20780 72.94%
5.4 7953 27.92%
5.5 641 2.25%
5.6 44 0.15%

PHP Requirements - Current Master - November 2014

5.2 1019 3.58%
5.3 19334 67.86%
5.4 7523 26.41%
5.5 573 2.01%
5.6 41 0.14%

A few observations: almost 7% of packages that required 5.3 and 15% of those requiring 5.2 gave these up for higher requirements, that's pretty low but it's a start. 5.4 is getting a good foothold but 5.5/5.6 features are hardly used by OSS packages.


Again these numbers need to be taken with a grain of salt, but looking at the ecosystem from this perspective I would say PHP 5.3 can be safely dropped by most libraries and 5.5 sounds like a promising target!

November 13, 2014 // News, PHP // Post a comment

Toran Proxy and the future of Composer

TL;DR: New shiny product to support Composer development: Toran Proxy

A bit of history

I have spent quite a large part of the last three years working on both Composer and Packagist. This has been great fun for the most part, I learned tons, met a gazillion people both online and offline, got to travel places to talk about it at conferences. One question I get asked frequently is how I find the time to work on this for free, and my answer until recently was along the lines of: "I run my own business, which affords me quite some flexibility".

The problem is that I can not use this answer anymore. We have changed Nelmio's business model a few months ago to focus more on consulting and contracting. While this does not change the amount of time I can decide to spend on open source, it means that if I keep working the way I did in the past I will have to move under a bridge sooner or later.

Open-Source or a Salary?

I have spent quite some time over the last few months evaluating options to get out of the current situation. Having to choose between working on open source or earning a living is not great, and I feel horrible for ignoring GitHub issues and such. So the one thing I settled on is to work on an product around Composer that helps businesses using it but does not take anything away from the regular users.

Introducing Toran Proxy

Toran Proxy does mainly two things at the moment: it proxies Packagist, GitHub and others to make Composer faster and more reliable, and it allows you to host private packages easily. This was already sort of possible with Satis and many people used various amounts of hackery to get there. Toran makes it easier and faster. It integrates better with Composer since it acts as a real proxy. It can fetch/build packages lazily or can build them ahead of time, enabling you to choose between a lower disk usage or more safety against GitHub outages and such.

It comes with a yearly license fee, which includes updates and will hopefully allow me to work full time on improving Composer & Packagist. There is quite a big backlog of issues to look at and pull requests to review, finalize and merge. Packagist also has tons of potential for improvement. I have a million ideas and I really hope I get the chance to work on them. For example improvements in the discoverability of packages alone would benefit most everyone in the PHP community.

Of course Composer and Packagist remain free to use and fully open source. There is no way that will change. I just hope I can continue to work on them, for the community and supported by the community. Check it out!

June 19, 2014 // News, PHP // Post a comment

Authentication management in Composer

Up until today if you run a home-grown package repository serving private packages it was quite a pain to use with Composer. You did not have efficient way to password-protect the repository except by inlining the password in the composer.json or by typing the username/password every single time.

With the merge of PR#1862 and some further improvements you can now remove credentials from your composer.json! The first time Composer needs to authenticate against some domain it will prompt you for a username/password and then you will be asked whether you want to store it. The storage can be done either globally in the COMPOSER_HOME/auth.json file (COMPOSER_HOME defaults to ~/.composer or %APPDATA%/Composer on Windows) or also in the project directory directly sitting besides your composer.json.

You can also configure these by hand using the config command if you need to configure a production machine to be able to run non-interactive installs. For example to enter credentials for one could type:

composer config username password

That will store it in the current directory's auth.json, but if you want it available globally you can use the --global (-g) flag.

The advantage of having it in a separate file is that you can easily add this auth.json to .gitignore and let every developer in your company have their own credentials in there.

And I did not forget the security-minded folks that do not want to store anything on disk and do not want to be prompted every time! You can use composer config -g store-auths false

Altogether these small improvements should make some use cases much easier so that is great news.

May 27, 2014 // News, PHP // Post a comment

PSR-4 autoloading support in Composer

As of today and thanks to a pull request by Andreas Hennings who did the bulk of the work, we have PSR-4 autoloading support in Composer. It is a feature that can have a serious impact on users of your packages so I wanted to detail what it means for everyone.

First of all if you are not familiar with PSR-4 but know about PSR-0 the main difference and benefit is that it allows for flatter directory structures in your git repositories. While you typically had src/Vendor/Lib/Class.php in libraries it is now possible to use a simpler src/Class.php while retaining the namespacing at the code level. The other small difference is there no more support for PEAR-style namespacing of classes using underscores (e.g. Vendor_Lib_Class) so those packages should keep using PSR-0 and not migrate.

So we can use it in Composer now, and that's cool. I am sure some of you already stopped reading and are pushing changes to your repos to be using the new shiny, but please don't. Not just yet.

The issue is that Composer support for PSR-4 is needed for your packages to autoload properly. If you push this out now, people are going to freak out as soon as they update your package and classes can not be autoloaded anymore. In addition we will for sure have a ton of bogus support requests, and perhaps you too. Therefore I would like to urge everyone to wait at least until February before using this in any semi-popular package. I will tag a new release of Composer soon so that homebrew users for example also get the PSR-4 support in a timely manner.

For the gory details you can head to the Composer documentation, but it's fairly straightforward to upgrade. To only upgrade at the Composer level only you can just update mappings like this:

// before
    "autoload": {
        "psr-0": {
            "Foo\\Bar\\": "src/"

// after
    "autoload": {
        "psr-4": {
            "Foo\\Bar\\": "src/Foo/Bar/"

While if you want to benefit from the shorter paths it is even more simple, you turn the psr-0 into a psr-4 and move all the files from src/Foo/Bar/* into src/*

Finally, a quick note for users of the target-dir property (mostly that is Symfony2 bundle authors as far as I know). The property is now deprecated and using it together with PSR-4 is forbidden since was essentially a hack to support PSR-0 autoloading in non-standard repositories. If you were using that you can easily get rid of it by updating the composer.json as such:

// before
    "autoload": {
        "psr-0": {
            "Foo\\BarBundle\\": ""
    "target-dir": "Foo/BarBundle"

// after
    "autoload": {
        "psr-4": {
            "Foo\\BarBundle\\": ""

Thanks for your attention, and again please refrain from jumping on this too quickly to save everyone some trouble. And while I am at it: Happy new year!

January 03, 2014 // PHP // Post a comment

Upcoming Conferences

Here is a short update on the conferences I will attend in the coming months, in case you want to meet or if you are just looking for a good one to attend:

Next month I am very happy to have the chance to visit Tunisia for the first time and go speak about Composer at the Symfony Tunisia event. It will also be the first time I give a talk in French, which freaks me out a bit since I am so used to speaking about technical matters in English. You can therefore expect a bastardized language known as Frenglish.

Right after that is the first SymfonyCon happening in Europe, which will be in Warsaw, Poland. And I hear it is almost sold out so in case you were thinking of going hurry up and grab a ticket now.

In January I will head back to my mother nation of Belgium (insert "while it is still a country" joke) for two events: first I will speak at PHPBenelux about web application monitoring with Heka. Then the weekend after there is FOSDEM where I hope I can also meet a few people from outside the PHP community.

November 19, 2013 // News, PHP // Post a comment

The pyramid of cluelessness

I met this developer employed by a big company, he seems to be happily sitting at his desk watching time pass by. Enjoying what David Graeber recently described as a Bullshit Job. Cashing in big bucks in a race to the bottom of counter-productivity.

Now I do not want to be judgmental, it is his choice and right to piss his life away in an environment that sounds like intellectual death to me. I can not understand but I will accept. What I want to talk about however is something else he mentioned.

He says he could be doing open source work in the evenings but that he is not good enough to produce meaningful work that would affect the world. And I want to call bullshit on this. I have no pretense that my work is affecting the world at large directly, but one thing I am convinced of is that there is enough world-changing work to be done and not enough people doing it.

I visualize this as a pyramid of cluelessness. At the bottom of the pyramid are many people having no clue how they can help move things forward, and further up those few that do have their mind set on specific goals.

The time people have is a finite resource, but by taking care of the small boring things you can in a way donate time to the system and enable people higher up the pyramid to do more meaningful work. Fix a small issue here and there. Do the crap work so that nobody else has to. If everyone at the bottom did their share the ones on top would have a ton more time to direct real change.

I look at this from the angle of open-source because I am knee-deep in that world. However I imagine it applies to any sort of labor that is not made for personal gain but rather the greater good. So go out there and help someone help the world!

August 23, 2013 // Random // Post a comment

[1] 2 3 4 5 Older entries > Last page