@dr0i
Some UTF8 strings may stop the import. Webserver's error-log reveals:
[php7:notice] [...] Mysqli statement execute error : Incorrect string [...].
Converting the string to ASCII before importing fixes the issue so that
importing the logs runs successfully.
Fixes #19069.
@sgiehl
Hi @dr0i. Thanks for creating this PR. I don't think we can't merge it that way. Converting UTF-8 to ASCII will simply replace all special characters with a ?. This would fully drop support for any language like Chinese, Thai, ...
Are you able to determine which characters you are having trouble with?
Hi @sgiehl , this is the log snippet:
[php7:notice] [pid 419] [client $IP] Error in Matomo (tracker): Error query: Mysqli statement execute error : Incorrect string value: '\xF0\x9F\x8D\x93 V...' for column 'name' at row 1 In query: INSERT INTO matomo_log_action (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?) Parameters: array ( 0 => '\xf0\x9f\x8d\x93 Valerie want to meet you! Click here: http://inx.lv/0NRD?h=c44e2314db1ea349f44c7a54e810b38d- \xf0\x9f\x8d\x93', 1 => '\xf0\x9f\x8d\x93 Valerie want to meet you! Click here: http://inx.lv/0NRD?h=c44e2314db1ea349f44c7a54e810b38d- \xf0\x9f\x8d\x93', 2 => 8, 3 => NULL, )
That actually doesn't look invalid. But it looks a bit spammy 🙈
Nevertheless \xF0\x9F\x8D\x93 is the embedded hex for 🍓.
Are you able to look up the line in the log file you tried to import?
This is the snippet to be imported:
... "GET /search?location=&q=%F0%9F%8D%93+Valerie+want+to+meet+you%21+Click+here%3A+http%3A%2F%2Finx.lv%2F0NRD%3Fh%3Dc44e2314db1ea349f44c7a54e810b38d-+%F0%9F%8D%93 HTTP/1.1" 200 8619 "$DN/search?location=&q=%F0%9F%8D%93+Valerie+want+to+meet+you%21+Click+here%3A+http%3A%2F%2Finx.lv%2F0NRD%3Fh%3Dc44e2314db1ea349f44c7a54e810b38d-+%F0%9F%8D%93" ...
@dr0i I tried to reproduce that locally, but with the log line you provided it works fine for me. No error occurs and the database contains a record with the 🍓 emoji.
Which database are you using and do you have utf8mb4 support enabled?
@sgiehl thx for taking your time with me! Already feared that this is a mysql configuration problem on my side. Would be grateful if you guide me nonetheless.
As I wrote in the corresponding issue, I use:
Matomo: 4.8.0
Mysql: Ver 8.0.25 for Linux on x86_64 (made sure to have innodb_file_per_table=ON and using utf8mb4 as described in https://github.com/matomo-org/matomo/issues/9785)
but the claim about me using utf8mb4 may be not 100% solid ... So, I am using mysql >=8 and this should mean utf8mb4 is the default, right? Also show collation like 'utf8mb4%'; reveals a lot of entries - I interpreted this as "alright using utf8mb4 ". Also:
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0,01 sec)See https://github.com/matomo-org/matomo/issues/19069#issuecomment-1096277279 for a bit of background re my installations.
Again, I am no prof when it comes to mysql so I appreciate every guidance nailing the problem down.
@dr0i Sorry, but I'm not able to provide deeper support here as my time is very limited. You can try to find some help on our forum.
Btw. to check if the tables have the correct collation you can run a query like SHOW TABLE STATUS FROM `matomo`;