@cherouvim opened this Issue on August 28th 2017

I use piwik 3.0.4 with MySQL 5.7.19 and the following command to import logs:

python /web/piwik/misc/log-analytics/import_logs.py \
  --idsite=1 \
  --url=... \
  --recorders=1 \
  --hostname=... \
  /tmp/rss.log

Sometimes it fails with Error when connecting to Piwik: HTTP Error 500: Internal Server Error and on the apache error log I see:

[:error] [pid 31689] [client ...:55634] Error in Piwik (tracker): 
Error query: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'config_os_version' at row 1 In query: 
INSERT INTO piwik_log_visit (idvisitor, config_id, ...

By doing show create table piwik_log_visit I can see that the length of column config_os_version is 10, so it chokes when it tries to import the text Server 2003 which is 11.

@sgiehl commented on August 30th 2017 Member

Thanks for the report, this indeed seems like something we should change soon (even if Windows Server 2003 should not be a commonly used OS).

@sgiehl commented on August 30th 2017 Member

Ok. Just checked that. Actually the config_os_version should have a length of 100, which should be enough. It used to have a length of 10 in a quite old version.

@cherouvim could you please check if the file linked above looks the same on your Piwik?

@cherouvim commented on September 7th 2017

Yes, they are exactly the same:

$ curl -s https://raw.githubusercontent.com/piwik/piwik/3.x-dev/plugins/DevicesDetection/Columns/OsVersion.php | sha1sum
113fb72e8667272e86a044997e739cdb55bd9b62  -

$ cat /web-pub/piwik/webapp/plugins/DevicesDetection/Columns/OsVersion.php | sha1sum
113fb72e8667272e86a044997e739cdb55bd9b62  -

FYI I've upgraded from 1.12 to 3.0.4 by using the existing old database and a fresh files installation via the exact following commands:

wget https://builds.piwik.org/piwik.zip
unzip piwik.zip
mv piwik webapp

chown www-data:www-data -R /web-pub/piwik/webapp
find /web-pub/piwik/webapp/ -type f -exec chmod 660 {} \;
find /web-pub/piwik/webapp/ -type d -exec chmod 770 {} \;
chmod +x /web-pub/piwik/webapp/console

/web-pub/piwik/webapp/console config:set --section="database" --key="host" --value="127.0.0.1"
/web-pub/piwik/webapp/console config:set --section="database" --key="username" --value="piwik"
/web-pub/piwik/webapp/console config:set --section="database" --key="password" --value="..."
/web-pub/piwik/webapp/console config:set --section="database" --key="dbname" --value="piwik"
/web-pub/piwik/webapp/console config:set --section="database" --key="tables_prefix" --value="piwik_"

/web-pub/piwik/webapp/console core:update --yes

echo $'insert into piwik_user (login, password, alias, email, token_auth, superuser_access) values (\'jc\', \'...\', \'jc\', \'jc@...\', \'123\', 1)' | mysql -u piwik -p... piwik

cd /web-pub/piwik/webapp/misc
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz
gunzip GeoLiteCity.dat.gz
@sgiehl commented on September 10th 2017 Member

@cherouvim could you please run this sql query to check what piwik has stored for this field:

SELECT option_value FROM piwik_option WHERE option_name = 'version_log_visit.config_os_version';
@cherouvim commented on September 10th 2017
mysql> SELECT option_value FROM piwik_option WHERE option_name = 'version_log_visit.config_os_version';
+-----------------------------------------------------------------------------+
| option_value                                                                |
+-----------------------------------------------------------------------------+
| VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
@sgiehl commented on September 10th 2017 Member

That's weird. As it should have been updated to that only after the field was adjusted. Maybe that is a side effect as you updated from a very old version.
Guess the simplest solution is to update the field manually.

@cherouvim commented on September 15th 2017

Thanks. Used this query:

alter table piwik_log_visit modify config_os_version varchar(100);
@mattab commented on September 21st 2017 Owner

it seems this issue was caused by a "partial upgrade" so maybe we can close it as there is no real issue with columns too small?

@cherouvim commented on September 21st 2017

I've solved my problem by executing the custom query.

Regarding whether this issue is reproducible and may affect other people (in the rare occasion that they'll upgrade from a really old piwik installation like I did) I think it does. Whether it is important to exactly pinpoint what may go wrong during the upgrade, is up to you.

FYI I'd been using that old piwik installation without ever doing anything weird on it, such as altering code or executing any direct SQL.

@mattab commented on September 22nd 2017 Owner

Closing as wontfix for now (although we may want to fix this issue in the future, IIRC some other opened issue is similar)

This Issue was closed on September 22nd 2017
Powered by GitHub Issue Mirror