Upgrade from 0.4.5 to 0.5 fails


(barry_van_dijk) #1

I am experiencing major problems when upgrading from version 0.4.5 to 0.5. In the end I have installed 0.5 as a new environment and after this I performed the following steps:

;backup 0.4.5 database
mysqldump -u root -p db > db.bak.sql

;restore 0.4.5 database to 0.5 database
mysql -h localhost -u root -p piwik < db.bak.sql

;modify the database
mysql -u root
use piwik
;executed this particular database change as a result of the first error message after starting the database upgrade from the web interface
alter table piwik_log_action drop column hash;
;executed this particular database change as a result of the first error messages after starting the database upgrade from the web interface for the second time
alter table piwik_log_visit add visit_exit_idaction integer(11);

;executed the database upgrade from the web interface for the third time and then the following error appears:
I:\Program Files\Apache Software Foundation\Apache2.2\htdocs\piwik_new\core\Updates\0.5.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit CHANGE visit_exit_idaction visit_exit_idaction_url INTEGER(11) NOT NULL;’.
The error was: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘visit_exit_idaction_url’

;visit_exit_idaction_url was indeed added manually to prevent an earlier error message

mysql> describe piwik_log_visit;

+--------------------------+----------------------+------+-----+---------+----------------+
| Field                    | Type                 | Null | Key | Default | Extra          |
+--------------------------+----------------------+------+-----+---------+----------------+
| idvisit                  | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| idsite                   | int(10) unsigned     | NO   | MUL | NULL    |                |
| visitor_localtime        | time                 | NO   |     | NULL    |                |
| visitor_idcookie         | char(32)             | NO   |     | NULL    |                |
| visitor_returning        | tinyint(1)           | NO   |     | NULL    |                |
| visit_first_action_time  | datetime             | NO   |     | NULL    |                |
| visit_last_action_time   | datetime             | NO   |     | NULL    |                |
| visit_server_date        | date                 | NO   |     | NULL    |                |
| visit_entry_idaction_url | int(11)              | NO   |     | NULL    |                |
| visit_total_actions      | smallint(5) unsigned | NO   |     | NULL    |                |
| visit_total_time         | smallint(5) unsigned | NO   |     | NULL    |                |
| visit_goal_converted     | tinyint(1)           | NO   |     | NULL    |                |
| referer_type             | int(10) unsigned     | YES  |     | NULL    |                |
| referer_name             | varchar(70)          | YES  |     | NULL    |                |
| referer_url              | text                 | NO   |     | NULL    |                |
| referer_keyword          | varchar(255)         | YES  |     | NULL    |                |
| config_md5config         | char(32)             | NO   |     | NULL    |                |
| config_os                | char(3)              | NO   |     | NULL    |                |
| config_browser_name      | varchar(10)          | NO   |     | NULL    |                |
| config_browser_version   | varchar(20)          | NO   |     | NULL    |                |
| config_resolution        | varchar(9)           | NO   |     | NULL    |                |
| config_pdf               | tinyint(1)           | NO   |     | NULL    |                |
| config_flash             | tinyint(1)           | NO   |     | NULL    |                |
| config_java              | tinyint(1)           | NO   |     | NULL    |                |
| config_director          | tinyint(1)           | NO   |     | NULL    |                |
| config_quicktime         | tinyint(1)           | NO   |     | NULL    |                |
| config_realplayer        | tinyint(1)           | NO   |     | NULL    |                |
| config_windowsmedia      | tinyint(1)           | NO   |     | NULL    |                |
| config_gears             | tinyint(1)           | NO   |     | NULL    |                |
| config_silverlight       | tinyint(1)           | NO   |     | NULL    |                |
| config_cookie            | tinyint(1)           | NO   |     | NULL    |                |
| location_ip              | bigint(20) unsigned  | NO   |     | NULL    |                |
| location_browser_lang    | varchar(20)          | NO   |     | NULL    |                |
| location_country         | char(3)              | NO   |     | NULL    |                |
| location_continent       | char(3)              | NO   |     | NULL    |                |
| location_provider        | varchar(100)         | YES  |     | NULL    |                |
| location_geoip_continent | varchar(100)         | YES  |     | NULL    |                |
| location_geoip_country   | varchar(100)         | YES  |     | NULL    |                |
| location_geoip_city      | varchar(100)         | YES  |     | NULL    |                |
| location_geoip_latitude  | decimal(7,4)         | YES  |     | NULL    |                |
| location_geoip_longitude | decimal(7,4)         | YES  |     | NULL    |                |
| visit_entry_idaction     | int(11)              | YES  |     | NULL    |                |
| visit_exit_idaction_url  | int(11)              | NO   |     | NULL    |                |
| visit_exit_idaction      | int(11)              | YES  |     | NULL    |                |
+--------------------------+----------------------+------+-----+---------+----------------+
44 rows in set (0.01 sec)

What should I do in order to have this upgrade executed successfully?


(Matthieu Aubry) #2

If you experience issues with updating to Piwik 0.5, try the following. This is a manual process.
Execute the following SQL queries on your Piwik database, using a tool like phpmyadmin.

ALTER TABLE piwik_log_action ADD COLUMN `hash` INTEGER(10) UNSIGNED NOT NULL AFTER `name` 
ALTER TABLE piwik_log_visit  CHANGE visit_exit_idaction visit_exit_idaction_url INTEGER(11) NOT NULL 
ALTER TABLE piwik_log_visit  CHANGE visit_entry_idaction visit_entry_idaction_url INTEGER(11) NOT NULL 
ALTER TABLE piwik_log_link_visit_action CHANGE `idaction_ref` `idaction_url_ref` INTEGER(10) UNSIGNED NOT NULL 
ALTER TABLE piwik_log_link_visit_action CHANGE `idaction` `idaction_url` INTEGER(10) UNSIGNED NOT NULL 
ALTER TABLE piwik_log_link_visit_action ADD COLUMN `idaction_name` INTEGER(10) UNSIGNED AFTER `idaction_url_ref` 
ALTER TABLE piwik_log_conversion CHANGE `idaction` `idaction_url` INTEGER(11) UNSIGNED NOT NULL 
UPDATE piwik_log_action SET `hash` = CRC32(name) 
CREATE INDEX index_type_hash ON piwik_log_action  (type, hash) 
DROP INDEX index_type_name ON piwik_log_action

If you get errors after executing a query, ignore it for now.
Then execute:

UPDATE piwik_option SET option_value = '0.5' WHERE option_name = 'version_core'

Go to the Piwik UI. Do you still experience problems? Are your visitors being tracked?