Opened 3 years ago

Closed 3 years ago

Last modified 7 months ago

#2341 closed Bug (fixed)

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

Reported by: matt Owned by: vipsoft
Priority: normal Milestone: 1.4 - Piwik 1.4
Component: Core Keywords:
Cc: Sensitive: no

Description

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 ;)

Change History (19)

comment:1 Changed 3 years ago by Mandrunk

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

comment:2 Changed 3 years ago by vipsoft (robocoder)

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.

comment:3 Changed 3 years ago by matt (mattab)

  • Resolution set to wontfix
  • Status changed from new to closed

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

comment:4 Changed 3 years ago by vipsoft (robocoder)

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

comment:5 Changed 3 years ago by vipsoft (robocoder)

  • Resolution wontfix deleted
  • Status changed from closed to reopened
  • Summary changed from MYSQLI regression to Investigate: MYSQLI regression
  • Type changed from Bug to Task

comment:6 Changed 3 years ago by vipsoft (robocoder)

  • Owner set to vipsoft
  • Status changed from reopened to new

comment:7 Changed 3 years ago by vipsoft (robocoder)

(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)

comment:8 Changed 3 years ago by vipsoft (robocoder)

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

comment:9 Changed 3 years ago by vipsoft (robocoder)

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

comment:10 Changed 3 years ago by vipsoft (robocoder)

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)

LOAD DATA INFILE:

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

comment:11 Changed 3 years ago by vipsoft (robocoder)

  • Summary changed from Investigate: MYSQLI regression to php5.3 and libmysqlclient: Piwik_Exec( LOAD DATA INFILE ) fails with -1
  • Type changed from Task to Bug

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.

comment:12 Changed 3 years ago by vipsoft (robocoder)

  • Resolution set to fixed
  • Status changed from new to closed

(In [4547]) fixes #2341

comment:13 Changed 3 years ago by vipsoft (robocoder)

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.

comment:14 Changed 3 years ago by matt (mattab)

Oh you are good!!! nice one!

comment:15 Changed 3 years ago by vipsoft (robocoder)

  • Summary changed from php5.3 and libmysqlclient: Piwik_Exec( LOAD DATA INFILE ) fails with -1 to php5.3, mysqli, and libmysqlclient: Piwik_Exec( LOAD DATA INFILE ) fails with -1

comment:16 Changed 3 years ago by Cyril (cbay)

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.

comment:17 Changed 3 years ago by vipsoft (robocoder)

Cyril: please try the 1.5 release candidate.

comment:18 Changed 3 years ago by Cyril (cbay)

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!

Note: See TracTickets for help on using tickets.