Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xD0_\xD0\xB2\xD1\x82...' for column 'name' at row 1 In query: INSERT INTO piwik_log_action

Support & Bugs
1

Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xD0_\xD0\xB2\xD1\x82…’ for column ‘name’ at row 1 In query: INSERT INTO piwik_log_action (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?)
This error occurs frequently in php-error file my disk space decrease frequently more than 2gb. Any solution to stop this error

Thanks a lot.

2

Hi @tehseen
Which version of Mysql do you use? could you create an issue at: Issues · piwik/piwik · GitHub and post there the HTTP request found in your access log containing piwik.php?... and the request we can use to reproduce this error? thanks,

3

Mysql 5.6.16-Log
piwik 2.11.2

Http Link:

http://localhost/pwk2112/piwik.php?action_name=ShopSave.ru&idsite=1&rec=1&r=713679&h=12&m=43&s=27&url=http%3A%2F%2Fshopsave.ru%2Fcategory%2FÐ_кÑ_еÑ_Ñ_уары%2Fcid-90409%2F%26%26page_yxm%3D18&_id=231bc7088d3af7ec&_idts=1483421432&_idvc=2&_idn=0&_refts=0&_viewts=1483429407&send_image=0&pdf=1&qt=0&realp=0&wma=0&dir=0&fla=1&java=0&gears=0&ag=0&cookie=1&res=1600x900&gt_ms=1776

when i visit this link on local machine the above error occur.

Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xC3__\xC3_\xC2…’ for column ‘name’ at row 1 In query: INSERT INTO piwik_log_action (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?) Parameters: array ( 0 => ‘Аксессуары - цены, отзывы, рейтинги. Сравните цены на Аксессуары на ShopSave.ru’, 1 => ‘Аксессуары - цены, отзывы, рейтинги. Сравните цены на Аксессуары на ShopSave.ru’, 2 => 1, 3 => 0, )

4

Tracked in: Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: · Issue #11136 · matomo-org/piwik · GitHub

5

I have visited to this url : Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: · Issue #11136 · matomo-org/piwik · GitHub

but giving the answer piwik 3.

Is there any solution for version Piwik 2.11.2 for the above issue ?

6

you must upgrade to Piwik 3

7

Hi @matthieu.
If I use 2.17.1 version I still need to upgrade to 3.* ?
Is 3.* version compatible with php 5.* ?

Thanks

8

I have upgraded piwik 3.0.2 and php 7.0.16 x64 nts, but still same type of error is coming in php error file.
Now this error occurs
[12-Apr-2017 04:42:45 UTC] Error in Piwik (tracker): Error query: SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xD0_\xD0\xBB\xD0\xB5…’ for column ‘name’ at row 1 In query: INSERT INTO piwik_log_action (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?) Parameters: array ( 0 => ‘Счетчики Ð_лектроÑ_нергии/?cid=91701’, 1 => ‘Счетчики Ð_лектроÑ_нергии/?cid=91701’, 2 => 1, 3 => 0, )

10

I have the same error and have found the cause.

I’m running Piwik 3.2.0 on Windows 2008 R2 (IIS 7.5) using Log Analytics.

I have a user who is saving filenames with special character quotes in the name “ and ” the kind you get if you enter a quote in Word. It also applies to single quotes and apostrophes. Someone browses to the file. The filename gets entered into the log file. Import_logs.py runs, and the import gets a fatal error when trying to process this line. I removed these entries from the log file I was testing and it then ran successfully.

I’m still searching for a solution, but came across some suggestions on other PHP (non-Piwik) related sites to adjust the character encoding, possibly using the iconv command. If I find a working solution, I will share. And if not, I hope this can at least help someone else find one.

Edit:
I’m not sure if this is the best solution as I not very familiar with the Piwik source code, but import_log.py finished without an error when I did this. I’ll need to review the data closer to make sure this actually worked as intended.
In core/Tracker/Model.php, in function insertNewAction(), before the $db->query() line, insert the following (Leaving out the @ will give a warning message.):
$name = @iconv(‘UTF-8’, ‘ASCII//TRANSLIT’, $name);

1 Like
11

Thanks for pin pointing to code.

A better solution is to add the following to the top of insertNewAction in Model.php:

$name = mb_convert_encoding( $name, 'UTF-8', 'UTF-8' );

@iconv('UTF-8', 'ASCII//TRANSLIT', $name);

converts the $name to basic ASCII, which is limited to something like 128 characters. mb_convert_encoding will remove all characters not in the UTF-8 range.

This issue is caused by four bit Unicode characters that don’t fit in the UTF-8 column.

It appears to be caused by malicious or spam requests to your site.

12 
$name = preg_replace('/(?:^[^\p{L}\p{N}]+|[^\p{L}\p{N}]+$)/u', '', $name );

Update. The mb_convert_encoding function doesn’t work with strings like:

‘Kverh, :de:’

13

Any idea to submit this to the developers for possible inclusion? I have to make this code change every time I upgrade.

1 Like
14

I think that would be best done by GitHub.