Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

piwik logs import sometimes fails with "Data too long for column" #11981

Closed
cherouvim opened this issue Aug 28, 2017 · 10 comments
Closed

piwik logs import sometimes fails with "Data too long for column" #11981

cherouvim opened this issue Aug 28, 2017 · 10 comments
Labels
answered For when a question was asked and we referred to forum or answered it. Bug For errors / faults / flaws / inconsistencies etc. Waiting for user feedback Indicates the Matomo team is waiting for feedback from the author or other users.

Comments

@cherouvim
Copy link
Contributor

cherouvim commented Aug 28, 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
Copy link
Member

sgiehl commented Aug 30, 2017

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 sgiehl added the Bug For errors / faults / flaws / inconsistencies etc. label Aug 30, 2017
@sgiehl sgiehl added this to the 3.2.0 milestone Aug 30, 2017
@sgiehl
Copy link
Member

sgiehl commented Aug 30, 2017

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?

@sgiehl sgiehl added the Waiting for user feedback Indicates the Matomo team is waiting for feedback from the author or other users. label Aug 30, 2017
@cherouvim
Copy link
Contributor Author

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
Copy link
Member

sgiehl commented Sep 10, 2017

@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
Copy link
Contributor Author

cherouvim commented Sep 10, 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
Copy link
Member

sgiehl commented Sep 10, 2017

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
Copy link
Contributor Author

Thanks. Used this query:

alter table piwik_log_visit modify config_os_version varchar(100);

@mattab
Copy link
Member

mattab commented Sep 21, 2017

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?

@mattab mattab modified the milestones: 3.2.0, 3.1.2 Sep 21, 2017
@cherouvim
Copy link
Contributor Author

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 mattab removed this from the 3.1.2 milestone Sep 22, 2017
@mattab
Copy link
Member

mattab commented Sep 22, 2017

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

@mattab mattab closed this as completed Sep 22, 2017
@mattab mattab added the answered For when a question was asked and we referred to forum or answered it. label Sep 22, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
answered For when a question was asked and we referred to forum or answered it. Bug For errors / faults / flaws / inconsistencies etc. Waiting for user feedback Indicates the Matomo team is waiting for feedback from the author or other users.
Projects
None yet
Development

No branches or pull requests

3 participants