Upgrading from 1.3 to 1.4 error : UPDATE log_visit SET

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:


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")

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

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

Well done Anthon you are good! :wink:

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)

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