Upgrading from 1.3 to 1.4 error : UPDATE log_visit SET

Support & Bugs
1

Hi there,

I have an error during the command-line updater :

Error trying to execute the query 'UPDATE log_visit SET location_ip = UNHEX (LPAD(HEX (CONVERT(location_ip, UNSIGNED)), 8, '0'))'.
The error was: SQLSTATE[22007]: Invalid datetime format: 1292 Truncated incorrect INTEGER value: 'X²º-'

If somebody has an idea please let me know :wink:

Thanks.

2

Interesting. Well, the error message is a bug since this isn’t a datetime type, or is it what we’re converting to.

I believe Matt sent me your phpMyAdmin login. I just took a quick look and noticed that you’re using MariaDB. While I realize this is supposed to be MySQL-compatible, there’s obviously a difference here in the storage engine that we have not tested.

Can you see if this patch works as a workaround?


Index: trunk/core/Updates/1.4-rc2.php

===================================================================

--- trunk/core/Updates/1.4-rc2.php (revision 4535)

+++ trunk/core/Updates/1.4-rc2.php (revision 4615)

@@ -19,4 +19,5 @@

 	{

 		return array(

+			"SET sql_mode=''" => false,

 			// this converts the 32-bit UNSIGNED INT column to a 16 byte VARBINARY;

 			// _but_ MySQL does string conversion! (e.g., integer 1 is converted to 49 -- the ASCII code for "1")
3

It seems to be OK :wink:

Thanks a lot.


*** Update ***

Database Upgrade Required

Your Piwik database is out-of-date, and must be upgraded before you can
continue.

Piwik database will be upgraded from version 1.3 to the new version 1.4.

The database upgrade process may take a while, so please be patient.

[!] Warning messages:

* <b>File integrity check failed and reported some errors. This is most likely
due to a partial or failed upload of some of the Piwik files. You should
reupload all the Piwik files in BINARY mode and refresh this page until it shows
no error.</b>

* File size mismatch: /data/httpsdocs/webstats/core/Updates/1.4-rc2.php
(expected length: 1403, found: 1436)

The update completed successfuly, however there were issues during the process.
Please read the above descriptions for details. For further help:

* Check the [ Piwik FAQ ] which explains most common errors during update.

* Ask your system administrator - they may be able to help you with the error
4

Well done Anthon you are good! :wink:

5

I had this same error, however I AM using MySQL, 5.5.9 on a Win7 box.


Error trying to execute the query 'UPDATE site_log_visit
				SET location_ip = UNHEX (LPAD(HEX (CONVERT(location_ip, UNSIGNED)), 8, '0'))'.
The error was: SQLSTATE[HY000]: General error: 1292 Truncated incorrect INTEGER value: ''

Anthon’s hack fixed the problem, and Piwik updated in about five seconds.

(note: a couple of spaces added to the error text to prevent smiley insertion)

6

Hi there,
got the same issue 1.2.1 update to 1.4 (MySQL DB). Had to apply this fix to. Worked fine afterwards.

Cheers