@dr0i opened this Issue on April 8th 2022

Importing logs using matomo/misc/log-analytics/import_logs.pysometimes breaks. Webserver's error log reveals a [php7:notice] [...] Mysqli statement execute error : Incorrect string [...].

Searched issues here with similar problems, like e.g. https://github.com/matomo-org/matomo/issues/11136.

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)

Found a solution at https://forum.matomo.org/t/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/21800.

If $name = iconv('UTF-8', 'ASCII//TRANSLIT', $name); is put before
https://github.com/matomo-org/matomo/blob/f8267dfed779e06f7483d9d952700e8c819cafba/core/Tracker/Model.php#L187 the import runs successfully.

Don't have clue about php re impact of that iconv considering performance or if it breaks showing all/some UTF-8 URLs in matomo - but this may give a good hint what to do meanwhile fixes my problem (until the next matomo update).

@MatomoForumNotifications commented on April 11th 2022

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/problem-with-url-context/44978/6

@dr0i commented on April 12th 2022

A bit of background:
This problem came up (incidentally?) just after an update of my matomo 3.* installation (which doesn't ran that smooth for I had to do alter table matomo_log_link_visit_action add pageview_position MEDIUMINT UNSIGNED DEFAULT NULL; manually in the mysqldb.)
Also, I updated mysqld a year or so before from 5.x to 8.x .
Don't know if one/all of these changes require a rewriting of table metadata. See also the discussion at the corresponding PR.

@dr0i commented on April 12th 2022

May be linked to https://github.com/matomo-org/matomo/issues/15711 ? I would have to do ALTER TABLE matomo_log_visit CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; (which I didn't yet - also in the referenced issue this hasn't solved the problem).

@dr0i commented on April 12th 2022

Thx again @sgiehl for the pointer - I think I can go on my own now, since SHOW TABLE STATUS FROM piwik; reveals that
all tables use utf8_unicode_ci as collation( which is pointed out to be problematic in https://github.com/matomo-org/matomo/issues/9785#issuecomment-477437648). So I would have to change the collation of (at least all log* tables) to utf8mb4_unicode_ci

@dr0i commented on April 12th 2022

This should to the trick:

mysql > use piwik;
mysql> ALTER TABLE matomo_log_link_visit_action CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

This may take a long time!
(having several 100GBs of data and count of rows for matomo_log_link_visit_action is > 10^9 I expect the time needed for the conversion ~ 2 days. It just seems to be a matter of CPU time (and IO throughput) though - memory is alright, also file space seems to be fine (meaning not doubling the db space temporarily . Mysql seems to be clever enough to remove converted COLUMNS (batch wise or so)).

Note that it is not enough to only change the DEFAULTS like with:

ALTER TABLE matomo_log_link_visit_action DEFAULT CHARACTER SET utf8mb4 ;

(hoped for this to only take effect on newly inserted data, but that's not the way it is proposed to work (tested it)).
Did this none the less since a 'SHOW TABLE STATUS FROM piwik;' will (only then) show the use of utf8mb4_unicode_ci.

Note also that you may need to update all other matomo_log* tables, but I guess this one table is enough.
Will come back here to report if this works out ok.

@sgiehl commented on April 12th 2022 Member

@dr0i There is actually a command to perform all the required (database) changes to do this: ./console core:convert-to-utf8mb4
See https://matomo.org/faq/how-to-update/how-to-convert-the-database-to-utf8mb4-charset/

@dr0i commented on April 14th 2022

Mysql ended up with :

ERROR 1034 (HY000): Incorrect key file for table 'matomo_log_link_visit_action'; try to repair it

Didn't thought of having enough space left on device for temporary mysql files:

mysql> SELECT * FROM performance_schema.error_log\G
...
DATA: Error number 28 means 'No space left on device'
...

My comment was obviously wrong:

file space seems to be fine (meaning not doubling the db space temporarily . Mysql seems to be clever enough to remove converted COLUMNS (batch wise or so)).

because I only had a look at the mount where the matomo db resides.

I close this issue here because this is a new issue a myslq issue due my misconfiguration having nothing to do with matomo.
Thx for your help again!

This Issue was closed on April 14th 2022
Powered by GitHub Issue Mirror