@mattab opened this Issue on April 20th 2011 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 ;)

@anonymous-piwik-user commented on April 20th 2011

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

@robocoder commented on April 20th 2011 Contributor

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.

@mattab commented on April 20th 2011 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...

@robocoder commented on April 20th 2011 Contributor

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...

@robocoder commented on April 24th 2011 Contributor

(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 (http://bugs.php.net/52221)

@robocoder commented on April 25th 2011 Contributor

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

@robocoder commented on April 25th 2011 Contributor

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

@robocoder commented on April 25th 2011 Contributor

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 characterset% settings were the same as yours.

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

@robocoder commented on April 25th 2011 Contributor

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.

@robocoder commented on April 25th 2011 Contributor

(In [4547]) fixes #2341

@robocoder commented on April 25th 2011 Contributor

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.

@mattab commented on April 25th 2011 Member

Oh you are good!!! nice one!

@cbay commented on May 27th 2011 Contributor

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.

@robocoder commented on May 27th 2011 Contributor

Cyril: please try the 1.5 release candidate.

@cbay commented on May 27th 2011 Contributor

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!

@mattab commented on September 23rd 2013 Member

see also this FAQ load data in file piwik

This Issue was closed on September 23rd 2013
Powered by GitHub Issue Mirror