Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#1133 closed Bug (fixed)

gzuncompress()

Reported by: lkarolew Owned by: vipsoft
Priority: critical Milestone: Piwik 0.5.5
Component: Core Keywords:
Cc: Sensitive: no

Description

First of all I already tried droping table and it doesn't work.

You still have problem with:
gzuncompress() [<a href='function.gzuncompress'>function.gzuncompress</a>]: data error in /htdocs/piwik/core/Archive/Single.php on line 260

This error is shown for all widgets on Dashboard beside "Last visit graph", I had few page visits. it's been failing from start

I'm using piwik 0.54, PHP Version 5.2.11-0.dotdeb.1, mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 UTF-8.

I can help debug error, just let me know what do you need.
I'm attaching backtrace.

Cheers,
Lukasz

Attachments (6)

backtrace.txt (5.2 KB) - added by lkarolew 4 years ago.
backtrace
logger_error (6.6 KB) - added by lkarolew 4 years ago.
logger_error
logger_exception (11.7 KB) - added by lkarolew 4 years ago.
logger_exception
show_variables.txt (27.4 KB) - added by lkarolew 4 years ago.
show variables
logger_exception.2 (14.7 KB) - added by lkarolew 4 years ago.
logger_exception_hex(value)
logger_exception.3 (16.3 KB) - added by lkarolew 4 years ago.
logger_exception_hex(value)_bin2hex(record->value)

Download all attachments as: .zip

Change History (38)

Changed 4 years ago by lkarolew

backtrace

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

Make sure magic_quotes_runtime is off in php.ini.

Try changing the adapter in config/config.ini.php to PDO_MYSQL.

Drop the affected archive_blob table and see if any of the above makes a difference.

If not, I'll work on a script to collect more info.

comment:2 Changed 4 years ago by lkarolew

magic_quotes_runtime = Off
magic_quotes_gpc = On
adapter = "PDO_MYSQL"
I've droped databases and it didn't solve problem.

I've also tried to delete from all log tables, drop archive and used logging for other site but this didn't help too.

I've also tried this with MYSQLI adapter with same result.

Let me know how I can help.

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

Either there's a bug in gzcompress/gzuncompress, or the blob isn't being stored/fetched properly from the database. We can't test this by configuration changes.

Can you use the mysql client to query the database directly?

SELECT value FROM piwik_archive_blob_2010_01
WHERE idSite='1' AND date1='2010-01-30' AND date2='2010-01-30' AND period='1' AND name='Referers_searchEngineByKeyword'; 

comment:4 Changed 4 years ago by lkarolew

I have direct access to console.
result of your query:

+-----------------------------------+
| value                             |
+-----------------------------------+
| xœK´2´ªÎ´Ò5´ö³® Ä |
| xœK´2´ªÎ´Ò5´ö³® Ä |
+-----------------------------------+

comment:5 Changed 4 years ago by lkarolew

If you want you can reach me on gtalk 'lkarolewski@…'

I've also runned unit tests:
27/28 test cases complete: 975 passes, 0 fails and 0 exceptions.

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

Let's try to narrow down the problem. In core/ArchiveProcessing/Record/Blob.php, change:
{{{   
26     function construct( $name, $value)
27     {
28         $value = gzcompress($value);
29         parent::
construct( $name, $value );
30     }
}}}

to:

   	    function __construct( $name, $value)
	    {
	        try {
	       	        $newValue = gzcompress($value);
	       	        gzuncompress($newValue); // check
	       	        $value = $newValue;
	        } catch(Exception $e) {
	       	        throw new Exception("gzcompress/gzuncompress data error: $value");
	        }	       	        
	        parent::__construct( $name, $value );
	    }

And drop the archive_blob table again.

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

In core/ArchiveProcessing.php, add the following after this code:

513	        $query = "INSERT IGNORE INTO ".$table->getTableName()." (idarchive, idsite, date1, date2, period, ts_archived, name, value)
514	                    VALUES (?,?,?,?,?,?,?,?)";
515	        Piwik_Query($query,
516	                            array(    $this->idArchive,
517	                                    $this->idsite,
518	                                    $this->strDateStart,
519	                                    $this->strDateEnd,
520	                                    $this->periodId,
521	                                    date("Y-m-d H:i:s"),
522	                                    $record->name,
523	                                    $record->value,
524	                            )
525	                    );

add:

	       $db = Zend_Registry::get('db');
	       $value = $db->fetchOne("SELECT value
	                                FROM ".$table->getTableName()."
	                                WHERE idarchive = ?
	                                    AND name = ?",   
	                                array( $this->idArchive , $record->name)
	                            );
       if($value != $record->value) {
             throw new Exception('store/fetch blob mismatch: '.$record->value." vs $value");
//             throw new Exception('store/fetch blob mismatch: '.$record->value." vs $value (".gzuncompress($record->value).')');
       }
 

Changed 4 years ago by lkarolew

logger_error

Changed 4 years ago by lkarolew

logger_exception

comment:8 Changed 4 years ago by lkarolew

I've attached logs, we have store/fetch blob mismatch.

I had to .base64_encode($value) like this :

if($value != $record->value) 
{
    throw new Exception('store/fetch blob mismatch: '.base64_encode($record->value)." vs ".base64_encode($value)." (".gzuncompress($record->value).')');
}

because without it in logger_excetion message was ending on
'store/fetch blob mismatch: '$record->value
(there is \0 at the end ?)

I'm wondering if you properly escape value when passing to insert query...

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

The logger_error should be a quick fix.

The logger_exception -- that's what we store in a blob? But I digress... -- doesn't make sense. If it was an escaping issue, I would have expected some similarity (where most characters don't require escaping).

There may be a logic error in the debuging code. Because of the 'IGNORE' in the 'INSERT', we should check the number of affected rows before the fetch.

Otherwise I'll have to look in the Zend code...

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

[1796] - fix incompatible declaration messages with php 5.3 and E_STRICT

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

Small change to only check a successfully inserted blob:

	$db = Zend_Registry::get('db');
	$rowsAffected = mysqli_affected_rows($db->getConnection());
	if($rowsAffected === 1)
	{
		$value = $db->fetchOne("SELECT value
						FROM ".$table->getTableName()."
						WHERE idarchive = ?
						AND name = ?",   
					array( $this->idArchive , $record->name)
				);
		if($value != $record->value) 
		{
			throw new Exception('store/fetch blob mismatch: '.base64_encode($record->value)." vs ".base64_encode($value)." (".gzuncompress($record->value).')');
		}
	}

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

Stumbled upon http://bugs.mysql.com/bug.php?id=37514 ... which raises the question: did you get an installation warning about mismatched MySQL client/server character sets?

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

Scratch that. This is a more likely cause: http://bugs.mysql.com/bug.php?id=36851 -- since newer php versions use mysqlnd.

Are you running 0.5.4 or from our development trunk? (ie does it have the IGNORE keyword in the SELECT? (added in [1767])

comment:14 Changed 4 years ago by lkarolew

Hi,
I've downloaded 0.5.4 version from website - lastet.zip ( and I don't have mentioned IGNORE )
I didn't have any warnings about client server mismath on install.

I'd like to tell you that when i've removed gzcompress from core/ArchiveProcessing/Record/Blob.php and gzuncompress from core/Archive/Single.php piwik works just fine so at least we have a workaround.

I'm just checking out source from svn , i'll do setup and upload logs.
Cheers,
Lukasz

comment:15 Changed 4 years ago by lkarolew

oh after adding
$rowsAffected = mysqli_affected_rows(
I still have store/fetch blob mismatch.

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

Looking at a hex dump of the base64_decode'd strings, the fetch'd string is an encoded version of the store'd string. This is leaning towards a MySQL issue.

Where you check rowsAffected, change the query to:

		$values = $db->fetchOne("SELECT value, hex(value)
						FROM ".$table->getTableName()."
						WHERE idarchive = ?
						AND name = ?",   
					array( $this->idArchive , $record->name)
				);

The hex should tell us when the encoding occurs.

And it would help if you can add a query before throwing the exception.

		var_dump(Piwik_FetchALL('SHOW VARIABLES'));

Changed 4 years ago by lkarolew

show variables

Changed 4 years ago by lkarolew

logger_exception_hex(value)

comment:17 Changed 4 years ago by lkarolew

Current format of exception message:

'store/fetch blob mismatch: '.base64_encode($record->value)." vs ".base64_encode($value)." (".gzuncompress($record->value).') hex:'.$value_hex

Changed 4 years ago by lkarolew

logger_exception_hex(value)_bin2hex(record->value)

comment:18 Changed 4 years ago by lkarolew

in logger_exception.3 i've added bin2hex($record->value) at the end of message,

Please note that bin2hex conversion is done byte-wise with the high-nibble first, i don't know how sql hex() is done.

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

Thanks. The problem is that the blob is stored incorrectly. Binary blobs don't have a character set, but MySQL is mistakenly applying some character set conversion on the field.

See if this is a workaround: drop the archive tables and add 'charset = utf8' to your database settings in config.ini.php.

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

Were you able to test the config change in comment:19 ?

comment:21 Changed 4 years ago by lkarolew

Currently I'm on business trip and i have very limited time. i'll be able to get back to you on Friday.

comment:22 Changed 4 years ago by matt (mattab)

lkarolew, any update?

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

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

At this point, my guess is that the dotdeb build is bad (e.g., missing the force_libmysqlclient_r.patch, perhaps?).

My suggestion would be to try dotdeb's 5.2.12 package to see if the problem has been resolved, rollback to an official debian package (5.2.6), or test drive the squeeze distribution 5.2.12).

comment:24 Changed 4 years ago by lkarolew

Hi, sorry for such delay but I've been extreamly busy lately, for now it should be better with my time.

I'm not admin on the server, and i found it impossible to update mysql configuration files.

What i did is I added Piwik_Query('SET NAMES utf8') before Piwik_Query($query,...) and it solved problem.

But probably it is not the best place it.
Can we put this query after each connection? somewhere in connection constructor? or something to that effect ?

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

Adding 'charset = utf8' to your database connection settings in config.ini.php should accomplish the same effect.

comment:26 Changed 4 years ago by matt (mattab)

vipsoft, could this charset=utf8 be automatically added when the detection showed a mismatch between client and server? Currently I believe this is an information message, but instead can we just add it in the config? thx

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

  • Resolution worksforme deleted
  • Status changed from closed to reopened

I thought the idea of auto-adding it to the config was nixed back in #984.

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

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

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

Just to be clear: the proposed change is to add: charset=utf8 to the config for new installatons only (or where the tables are deleted during a re-install).

comment:30 Changed 4 years ago by lkarolew

just to confirm adding 'charset = utf8' to database connection settings in config.ini.php resolved my issue.

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

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

(In [1881]) fixes #1133 - adds 'charset = utf8' to configuration file on new installs (or reinstalls where tables are deleted).

I also made some changes to address ZF-1743 (PHP 5.2.0) issues.

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

(In [2065]) refs #1133 - simplify logic re: setting the charset when installer (re)generates the config file

Note: See TracTickets for help on using tickets.