Critical Error Database after Upgrade 1.4 to 1.5

cithrix · June 19, 2011, 10:17am

I’m getting this error, after updating from 1.4 to 1.5:

Kritischer Fehler während der Aktualisierung:
/www/htdocs/w00d3f08/piwik/core/Updates/1.5-b1.php:
Error trying to execute the query 'CREATE TABLE piwik_log_conversion_item (
idsite int(10) UNSIGNED NOT NULL,
idvisitor BINARY(8) NOT NULL,
server_time DATETIME NOT NULL,
idvisit INTEGER(10) UNSIGNED NOT NULL,
idorder varchar(100) NOT NULL,

											  idaction_sku INTEGER(10) UNSIGNED NOT NULL,
											  idaction_name INTEGER(10) UNSIGNED NOT NULL,
											  idaction_category INTEGER(10) UNSIGNED NOT NULL,
											  price FLOAT NOT NULL,
											  quantity INTEGER(10) UNSIGNED NOT NULL,
											  deleted TINYINT(1) UNSIGNED NOT NULL,
											  
											  PRIMARY KEY(idvisit, idorder, idaction_sku),
									          INDEX index_idsite_servertime ( idsite, server_time )
											)  DEFAULT CHARSET=utf8 '.

The error was: SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘piwik_log_conversion_item’ already exists

Can you tell me how to fix it?

Thanks.

nosferatuvn · June 19, 2011, 12:25pm

i have that too now …

Edit:… is was now in database and add the code self…
but i got always errors only 2 codes works the rest not … please help


CREATE TABLE 'piwik_log_conversion_item' ( idsite int(10) UNSIGNED NOT NULL, idvisitor BINARY(8) NOT NULL, server_time DATETIME NOT NULL, idvisit INTEGER(10) UNSIGNED NOT NULL, idorder varchar(100) NOT NULL, idaction_sku INTEGER(10) UNSIGNED NOT NULL, idaction_name INTEGER(10) UNSIGNED NOT NULL, idaction_category INTEGER(10) UNSIGNED NOT NULL, price FLOAT NOT NULL, quantity INTEGER(10) UNSIGNED NOT NULL, deleted TINYINT(1) UNSIGNED NOT NULL, PRIMARY KEY(idvisit, idorder, idaction_sku), INDEX index_idsite_servertime ( idsite, server_time ) ) DEFAULT CHARSET=utf8 ;

   ALTER IGNORE TABLE `piwik_log_visit` ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_last, ADD visit_goal_buyer TINYINT(1) NOT NULL AFTER visit_goal_converted;

   ALTER IGNORE TABLE `piwik_log_conversion` ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_first, ADD idorder varchar(100) default NULL AFTER buster, ADD items SMALLINT UNSIGNED DEFAULT NULL, ADD revenue_subtotal float default NULL, ADD revenue_tax float default NULL, ADD revenue_shipping float default NULL, ADD revenue_discount float default NULL, ADD UNIQUE KEY unique_idsite_idorder (idsite, idorder), MODIFY idgoal int(10) NOT NULL;

   ALTER TABLE `piwik_log_link_visit_action` ADD custom_var_k1 VARCHAR(100) DEFAULT NULL AFTER time_spent_ref_action, ADD custom_var_v1 VARCHAR(100) DEFAULT NULL, ADD custom_var_k2 VARCHAR(100) DEFAULT NULL, ADD custom_var_v2 VARCHAR(100) DEFAULT NULL, ADD custom_var_k3 VARCHAR(100) DEFAULT NULL, ADD custom_var_v3 VARCHAR(100) DEFAULT NULL, ADD custom_var_k4 VARCHAR(100) DEFAULT NULL, ADD custom_var_v4 VARCHAR(100) DEFAULT NULL, ADD custom_var_k5 VARCHAR(100) DEFAULT NULL, ADD custom_var_v5 VARCHAR(100) DEFAULT NULL;

   ALTER TABLE `piwik_log_visit` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL;

   ALTER TABLE `piwik_log_conversion` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL;

   ALTER TABLE `piwik_log_link_visit_action` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL;

   ALTER TABLE `piwik_site` ADD ecommerce TINYINT DEFAULT 0;

   CREATE TABLE `piwik_session` ( id CHAR(32) NOT NULL, modified INTEGER, lifetime INTEGER, data TEXT, PRIMARY KEY ( id ) ) DEFAULT CHARSET=utf8;

   UPDATE `piwik_option` SET option_value = '1.5-rc6' WHERE option_name = 'version_core';

matthieu · June 19, 2011, 8:16pm

This is caused by an update that didn’t finish to execute properly.

Please,

in the update screen, Copy paste all SQL Queries
Execute these queries in order, after ignoring the failing query (which should be first in the list)
After executing all these queries on your database, Piwik will work fine again!

if you can, please use phpmyadmin to execute queries directly. It will work in the end, no data will be lost (except tracking will not work until you fix the issue)

nosferatuvn · June 20, 2011, 6:45am

Hi please help me,

i did now one bye on and get these errors

the code that work i don’t post it, but if i set in the database the version from 1.4 to 1.5 my piwik dont work…

Fehler: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘piwik_log_conversion_item’ ( idsite int(10) UNSIGNED NOT NULL, idvisitor BINARY’ at line 1

Code:


CREATE TABLE `piwik_log_conversion_item` ( idsite int(10) UNSIGNED NOT NULL, idvisitor BINARY(8) NOT NULL, server_time DATETIME NOT NULL, idvisit INTEGER(10) UNSIGNED NOT NULL, idorder varchar(100) NOT NULL, idaction_sku INTEGER(10) UNSIGNED NOT NULL, idaction_name INTEGER(10) UNSIGNED NOT NULL, idaction_category INTEGER(10) UNSIGNED NOT NULL, price FLOAT NOT NULL, quantity INTEGER(10) UNSIGNED NOT NULL, deleted TINYINT(1) UNSIGNED NOT NULL, PRIMARY KEY(idvisit, idorder, idaction_sku), INDEX index_idsite_servertime ( idsite, server_time ) ) DEFAULT CHARSET=utf8 ;

Fehler: #1060 - Duplicate column name 'visit_goal_buyer’
Code:


ALTER IGNORE TABLE `piwik_log_visit` ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_last, ADD visit_goal_buyer TINYINT(1) NOT NULL AFTER visit_goal_converted;

Fehler: #1054 - Unknown column ‘visitor_days_since_first’ in 'piwik_log_conversion’
Code:

ALTER IGNORE TABLE `piwik_log_conversion` ADD visitor_days_since_order SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_days_since_first, ADD idorder varchar(100) default NULL AFTER buster, ADD items SMALLINT UNSIGNED DEFAULT NULL, ADD revenue_subtotal float default NULL, ADD revenue_tax float default NULL, ADD revenue_shipping float default NULL, ADD revenue_discount float default NULL, ADD UNIQUE KEY unique_idsite_idorder (idsite, idorder), MODIFY idgoal int(10) NOT NULL;

Fehler: #1060 - Duplicate column name 'custom_var_k1’
Code:


ALTER TABLE `piwik_log_link_visit_action` ADD custom_var_k1 VARCHAR(100) DEFAULT NULL AFTER time_spent_ref_action, ADD custom_var_v1 VARCHAR(100) DEFAULT NULL, ADD custom_var_k2 VARCHAR(100) DEFAULT NULL, ADD custom_var_v2 VARCHAR(100) DEFAULT NULL, ADD custom_var_k3 VARCHAR(100) DEFAULT NULL, ADD custom_var_v3 VARCHAR(100) DEFAULT NULL, ADD custom_var_k4 VARCHAR(100) DEFAULT NULL, ADD custom_var_v4 VARCHAR(100) DEFAULT NULL, ADD custom_var_k5 VARCHAR(100) DEFAULT NULL, ADD custom_var_v5 VARCHAR(100) DEFAULT NULL;

Fehler: #1054 - Unknown column ‘custom_var_k1’ in 'piwik_log_conversion’
Code:


ALTER TABLE `piwik_log_conversion` CHANGE custom_var_k1 custom_var_k1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v1 custom_var_v1 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k2 custom_var_k2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v2 custom_var_v2 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k3 custom_var_k3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v3 custom_var_v3 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k4 custom_var_k4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v4 custom_var_v4 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_k5 custom_var_k5 VARCHAR(100) DEFAULT NULL, CHANGE custom_var_v5 custom_var_v5 VARCHAR(100) DEFAULT NULL;

kind regards,
Nosferatuvn

ts77 · June 20, 2011, 6:53am

Which mysql server version are you running? The first query is fully valid!

nosferatuvn · June 20, 2011, 7:01am

Apache/2.2.9 (Debian) DAV/2 SVN/1.5.1 PHP/5.2.14 mod_ssl/2.2.9 OpenSSL/0.9.8g
MySQL-Client-Version: 5.0.51a
PHP Erweiterung: mysql

if i set the 1.4 to 1.5

i got this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘revenue_subtotal’ in ‘field list’

budgierless · June 22, 2011, 6:10pm

still have this problem even when added via phpmyadmin.

'CREATE TABLE piwik_log_conversion_item (
idsite int(10) UNSIGNED NOT NULL,
idvisitor BINARY(8) NOT NULL,
server_time DATETIME NOT NULL,
idvisit INTEGER(10) UNSIGNED NOT NULL,
idorder varchar(100) NOT NULL,

											  idaction_sku INTEGER(10) UNSIGNED NOT NULL,
											  idaction_name INTEGER(10) UNSIGNED NOT NULL,
											  idaction_category INTEGER(10) UNSIGNED NOT NULL,
											  price FLOAT NOT NULL,
											  quantity INTEGER(10) UNSIGNED NOT NULL,
											  deleted TINYINT(1) UNSIGNED NOT NULL,
											  
											  PRIMARY KEY(idvisit, idorder, idaction_sku),
									          INDEX index_idsite_servertime ( idsite, server_time )
											)  DEFAULT CHARSET=utf8 '.

The error was: SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘piwik_log_conversion_item’ already exists

vipsoft · June 26, 2011, 6:44pm

In which case, skip that query, and move on to the next one.