php5.3, mysqli, and libmysqlclient: Piwik_Exec( LOAD DATA INFILE ) fails with -1 #2341

mattab opened this Issue Apr 20, 2011 · 16 comments

4 participants

Piwik Open Source Analytics member

This is probably a MYSQLI bug, but maybe not. I have helped 2 users privately on the forums, who have had only a few reports (ie. blobs) not loading properly, so only some reports were showing "there is no data". After changing to PDO_MYSQL from MYSQLI, and reprocessing archives from logs, it was working again. Reprocessing while using MYSQLI didn't fix it. So the problem lies in MYSQLI.

I have not many details about their setup, but I want to write it here so we don't forget that this can sometimes cause issues.

I will ask them exact version they were using.

Anthon can you think of anything that would cause such issue? that's a long shot ;)


As requested my server details follow (as at time of bug/error):

MySQL server version: 5.1.56
MySQLi version: Client API library version 5.1.56
MySQL charset: UTF-8 Unicode
PHP Version: 5.3.6


It can't hurt to ask what PHP versions everyone is using. In a couple of weeks, I hope to have every supported version of PHP (5.1.3 through 5.3.x) built for matrix testing. But I'm using php 5.3.6 with MySQLi (with MySQL 5.1.41).

I don't know why MYSQLI would have a problem since there's a fallback to plain INSERTs. Are there any reports of segfaults?

The problem is that PHP has bugs. (Just look at the Changelog.) It's amazing Piwik has gotten this far. There was even a php5 bug (now fixed after much troubleshooting) where mysqli would segfault if it got an error, and the mbstring & mssql extensions were also present.

Piwik Open Source Analytics member

I forgot to say, I am pretty confident the code was using plain INSERTs indeed. So the bug maybe lies in the UTF8 change we did somehow? (really does not make sense...)

I think it's a fairly low occurence bug. Let's just remember, that if some reports are missing, (especially blobs), mysqli will be the problem. Closing at wontfix until this becomes a much bigger issue, or we know the problem...


Oh, I just noticed a difference. Mandrunk's mysqli shows "Client API library version" 5.1.56 ... meaning php is linked with libmysql. On my system, mysqli using mysqlnd,

I'll try rebuilding...


(In [4544]) refs #2341 - disable magic_quotes_runtime for consistency with index.php; plus, it's deprecated in php 5.3 and buggy with MYSQLI (


(In [4545]) refs #2341 - unused variables


(In [4546]) refs #2341 - trigger build


re: comment:3

plain INSERTs:

  • doesn't use the escapeString() or the charset mismatch hack
  • uses the default connection charset (overridden by database config's charset= setting)


  • setting the database's default charset (in r4263) affects the creation of new tables where it isn't specified explicitly in the CREATE TABLE statement; it doesn't change the character_set_database setting

In any case, bottion's character_set_% settings were the same as yours.

I've recompiled 5.3.1 and 5.3.6 with libmysqlclient, and will run some tests.


The problem appears in php 5.3.x if the mysql extension is linked with libmysqlclient, and 'LOAD DATA INFILE' fails, because it returns -1 for the number of affected row (instead of 0 or false).

5.1.x and 5.2.x already link with libmysqlclient, so the bug is in php.


(In [4547]) fixes #2341


Normally, we don't care about the return value from Piwik_Exec(), and php5.3.x defaults to using mysqlnd (instead of linking to libmysqlclient), hence the obscurity of this bug.

Piwik Open Source Analytics member

Oh you are good!!! nice one!


On my install (PHP 5.3.6, mysqli with libmysqlclient), LOAD DATA INFILE makes PHP segfaults, pure and simple. I'm not sure if it's a bug in PHP or MySQL - probably the latter based on the traceback, maybe this one.

Let me add that despite mysqlnd being the default in PHP 5.3, that's not the case in Debian/Ubuntu (feature request, probably not going to be solved soon), so that could concern quite a few people.

The segfault doesn't occur everytime, but I knew I had to refresh the page a couple of times when I got the infamous "oops something went wrong with your request". I've patched core/Piwik.php to comment the LOAD DATA LOCAL INFILE code - the LOAD DATA INFILE does work fine (my MySQL server is local.)

I'm not reopening the bug as it's obviously not Piwik's fault.


Cyril: please try the 1.5 release candidate.


vipsoft: I can't really upgrade right now, but I've just had a look at the source code and I see the first attempt uses the non-local LOAD DATA INFILE, which should indeed resolve my issue. Thanks!

Piwik Open Source Analytics member

see also this FAQ load data in file piwik

@mattab mattab added this to the 1.4 - Piwik 1.4 milestone Jul 8, 2014
@robocoder robocoder was assigned by mattab Jul 8, 2014
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment