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.
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.
$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).
This issue has been mentioned on Matomo forums. There might be relevant details there:
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.
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).
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
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
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
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.
@dr0i There is actually a command to perform all the required (database) changes to do this:
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!