Update to 1.2 Critical Error during the update process: [SOLVED!]

jekko · March 3, 2011, 1:25pm

We get the following error when updating the database to 1.2

Critical Error during the update process:

/example.com/piwik/core/Updates/1.2-rc1.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit
DROP visit_server_date,
DROP INDEX index_idsite_date_config,
DROP INDEX index_idsite_datetime_config,
ADD visit_entry_idaction_name INT UNSIGNED NOT NULL AFTER visit_entry_idaction_url,
ADD visit_exit_idaction_name INT UNSIGNED NOT NULL AFTER visit_exit_idaction_url,
CHANGE visit_exit_idaction_url visit_exit_idaction_url INT UNSIGNED NOT NULL,
CHANGE visit_entry_idaction_url visit_entry_idaction_url INT UNSIGNED NOT NULL,
CHANGE referer_type referer_type TINYINT UNSIGNED NULL DEFAULT NULL,
ADD idvisitor BINARY(8) NOT NULL AFTER idsite,
ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_returning,
ADD visitor_days_since_last SMALLINT(5) UNSIGNED NOT NULL,
ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL,
ADD config_id BINARY(8) NOT NULL AFTER config_md5config,
ADD custom_var_k1 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v1 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k2 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v2 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k3 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v3 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k4 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v4 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k5 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v5 VARCHAR(50) DEFAULT NULL
’.
The error was: SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP ‘index_idsite_date_config’; check that column/key exists[SOLVED!]

dany · March 3, 2011, 4:11pm

I have also a Problem from 1.1.1 to 1.2.

With this SQL command


  ALTER TABLE `piwik_log_visit` DROP `visit_server_date`, DROP INDEX `index_idsite_date_config`, DROP INDEX `index_idsite_datetime_config`, ADD `visit_entry_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_entry_idaction_url`, ADD `visit_exit_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_exit_idaction_url`, CHANGE `visit_exit_idaction_url` `visit_exit_idaction_url` INT UNSIGNED NOT NULL, CHANGE `visit_entry_idaction_url` `visit_entry_idaction_url` INT UNSIGNED NOT NULL, CHANGE `referer_type` `referer_type` TINYINT UNSIGNED NULL DEFAULT NULL, ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL AFTER `visitor_returning`, ADD visitor_days_since_last SMALLINT(5) UNSIGNED NOT NULL, ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL, ADD `config_id` BINARY(8) NOT NULL AFTER `config_md5config`, ADD custom_var_k1 VARCHAR(50) DEFAULT NULL, ADD custom_var_v1 VARCHAR(50) DEFAULT NULL, ADD custom_var_k2 VARCHAR(50) DEFAULT NULL, ADD custom_var_v2 VARCHAR(50) DEFAULT NULL, ADD custom_var_k3 VARCHAR(50) DEFAULT NULL, ADD custom_var_v3 VARCHAR(50) DEFAULT NULL, ADD custom_var_k4 VARCHAR(50) DEFAULT NULL, ADD custom_var_v4 VARCHAR(50) DEFAULT NULL, ADD custom_var_k5 VARCHAR(50) DEFAULT NULL, ADD custom_var_v5 VARCHAR(50) DEFAULT NULL ;

I become the message:

Can’t DROP ‘visit_server_date’; check that column/key exists

cool_andy · March 3, 2011, 4:57pm

I have nearly the same problem:

Kritischer Fehler während dem Upgrade-Prozess:
/usr/www/users/xxxxxxxxxxxx/piwik/core/Updates/1.2-rc1.php:
Error trying to execute the query ‘ALTER TABLE piwik_log_visit
DROP visit_server_date,
DROP INDEX index_idsite_date_config,
DROP INDEX index_idsite_datetime_config,
ADD visit_entry_idaction_name INT UNSIGNED NOT NULL AFTER visit_entry_idaction_url,
ADD visit_exit_idaction_name INT UNSIGNED NOT NULL AFTER visit_exit_idaction_url,
CHANGE visit_exit_idaction_url visit_exit_idaction_url INT UNSIGNED NOT NULL,
CHANGE visit_entry_idaction_url visit_entry_idaction_url INT UNSIGNED NOT NULL,
CHANGE referer_type referer_type TINYINT UNSIGNED NULL DEFAULT NULL,
ADD idvisitor BINARY(8) NOT NULL AFTER idsite,
ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL AFTER visitor_returning,
ADD visitor_days_since_last SMALLINT(5) UNSIGNED NOT NULL,
ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL,
ADD config_id BINARY(8) NOT NULL AFTER config_md5config,
ADD custom_var_k1 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v1 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k2 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v2 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k3 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v3 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k4 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v4 VARCHAR(50) DEFAULT NULL,
ADD custom_var_k5 VARCHAR(50) DEFAULT NULL,
ADD custom_var_v5 VARCHAR(50) DEFAULT NULL
’.
The error was: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘config_md5config’ in ‘piwik_log_visit’

Any ideas to fix this ? I am not able to run piwik anymore ;-(

dany · March 3, 2011, 5:19pm

I have folowing update:
The error-Message are not e critical error. Only e message that keys for Drop are not found.
I check the SQL-Statement and found all changes was succesfull.
I made every single command in the shell because the DB is large already 1.2 Gb.

Have a nice day

Dany

matthieu · March 3, 2011, 7:41pm

Hello

If you have a SQL error during update to 1.2 here is the solution:

1 - set row with “version_core” to “1.1.1” in piwik_option mysql table


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

2 - go to Piwik, you will see the Update screen
3 - copy paste in a new text document the SQL queries the Update Piwik screen will display
4 - execute the SQL in phpMyAdmin for example, or in the mysql shell, one query then execute it. You must run each query seperately!!!

You might see some errors like “INDEX not found” etc. This means that some of the previous queries were succesfully ran. In this case, remove the query from the start of the list until the failing query, as they have already been executed. You can now run the following ones.

If you see the error:


SQLSTATE[42S22]: Column not found: 1054 Unknown column 'visit.visit_server_date' in 'where clause'

You might also have to delete just a subpart of a query. In this case, the query "

DROP `visit_server_date`,

was already done, so remove it from the main SQL query and run it again. Repeat until queries are ran properly.

4 - modify the queries and add after the ALTER SQL queries the keyword IGNORE, for example:


ALTER IGNORE TABLE piwik_log_visit DROP `visit_server_date`, [.........]

After doing this your Piwik should be working again.

The probleme appeared because the Piwik update only succeeded partially because server timed out or double click on Update.

pmariusz · March 3, 2011, 8:53pm

@matt

Second ALTER TABLE SQL Line is responded as an SQL Error #1062

That’s why following request won’t be successful:


   UPDATE piwik_log_visit SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))), config_id = binary(unhex(substring(config_md5config,1,16))) ;
   UPDATE piwik_log_conversion SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))) ;
   

   ALTER TABLE `piwik_log_visit` ADD INDEX `index_idsite_datetime_config` (idsite, visit_last_action_time, config_id);
   
   UPDATE piwik_log_link_visit_action as action, piwik_log_visit as visit SET action.idsite = visit.idsite, action.server_time = visit.visit_last_action_time, action.idvisitor = visit.idvisitor WHERE action.idvisit=visit.idvisit ;

Any advice?

matthieu · March 3, 2011, 10:12pm

@Mariusz, try to add the word IGNORE after the ALTER such as:

ALTER IGNORE TABLE piwik_log_visit
DROP visit_server_date,
[…]

Adding IGNORE will tell Mysql to ignore duplicate entries and carry on with the schema update. After running all the queries from the Update page list of SQL queries, Piwik will work again as expected.

alejaaandro · March 3, 2011, 10:54pm

[quote=matt]
Hello

If you have a SQL error during update to 1.2 here is the solution:

1 - set row with “version_core” to “1.1.1” in piwik_option mysql table


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

2 - go to Piwik, you will see the Update screen
3 - copy paste in a new text document the SQL queries the Update Piwik screen will display
4 - modify the queries and add after the ALTER SQL queries the keyword IGNORE, for example:


ALTER IGNORE TABLE piwik_log_visit DROP `visit_server_date`, [.........]

5 - execute the SQL in phpMyAdmin for example, or in the mysql shell, one query after the other. Some queries will fail as they have already been executed.

After doing this your Piwik should be working again.

The probleme appeared because the Piwik update only succeeded partially because server timed out or double click on Update.[/quote]

I never saw the update screen on the first place for some reason (i didn’t do an automatic update but uploaded the file via FTP) so setting “version_core” to “1.1.1” and letting piwik automatically update the database fixed the problem…

Thnx matt

jekko · March 4, 2011, 5:27am

this did not work for us, still cant drop non existent column

MySQL said: Documentation
#1091 - Can’t DROP ‘index_idsite_date_config’; check that column/key exists

SQL query:

ALTER IGNORE TABLE piwik_log_visit DROP visit_server_date ,
DROP INDEX index_idsite_date_config ,
DROP INDEX index_idsite_datetime_config ,
ADD visit_entry_idaction_name INT UNSIGNED NOT NULL AFTER visit_entry_idaction_url ,
ADD visit_exit_idaction_name INT UNSIGNED NOT NULL AFTER visit_exit_idaction_url ,
CHANGE visit_exit_idaction_url visit_exit_idaction_url INT UNSIGNED NOT NULL ,
CHANGE visit_entry_idaction_url visit_entry_idaction_url INT UNSIGNED NOT NULL ,
CHANGE referer_type referer_type TINYINT UNSIGNED NULL DEFAULT NULL ,
ADD idvisitor BINARY( 8 ) NOT NULL AFTER idsite ,
ADD visitor_count_visits SMALLINT( 5 ) UNSIGNED NOT NULL AFTER visitor_returning ,
ADD visitor_days_since_last SMALLINT( 5 ) UNSIGNED NOT NULL ,
ADD visitor_days_since_first SMALLINT( 5 ) UNSIGNED NOT NULL ,
ADD config_id BINARY( 8 ) NOT NULL AFTER config_md5config ,
ADD custom_var_k1 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v1 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k2 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v2 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k3 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v3 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k4 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v4 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k5 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v5 VARCHAR( 50 ) DEFAULT NULL

MySQL said: Documentation
#1091 - Can’t DROP ‘index_idsite_date_config’; check that column/key exists

matthieu · March 4, 2011, 7:42am

In your case you have to remove all operations after the failing one, so for example your query will become:


ALTER IGNORE TABLE `piwik_log_visit` 
DROP INDEX `index_idsite_datetime_config` ,
ADD `visit_entry_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_entry_idaction_url` ,
ADD `visit_exit_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_exit_idaction_url` ,
[...]

Next time, please try only click once. Probably there was a double click or the first query failed for some reason?

jekko · March 4, 2011, 8:52am

We do get nervous when upgrading Piwik! but there was no double click

removing the non existing columns from the sql statement produces the follow error

MySQL said: Documentation
#1054 - Unknown column ‘config_md5config’ in ‘piwik_log_visit’

SQL query:

ALTER IGNORE TABLE piwik_log_visit DROP visit_server_date ,
ADD visit_entry_idaction_name INT UNSIGNED NOT NULL AFTER visit_entry_idaction_url ,
ADD visit_exit_idaction_name INT UNSIGNED NOT NULL AFTER visit_exit_idaction_url ,
CHANGE visit_exit_idaction_url visit_exit_idaction_url INT UNSIGNED NOT NULL ,
CHANGE visit_entry_idaction_url visit_entry_idaction_url INT UNSIGNED NOT NULL ,
CHANGE referer_type referer_type TINYINT UNSIGNED NULL DEFAULT NULL ,
ADD idvisitor BINARY( 8 ) NOT NULL AFTER idsite ,
ADD visitor_count_visits SMALLINT( 5 ) UNSIGNED NOT NULL AFTER visitor_returning ,
ADD visitor_days_since_last SMALLINT( 5 ) UNSIGNED NOT NULL ,
ADD visitor_days_since_first SMALLINT( 5 ) UNSIGNED NOT NULL ,
ADD config_id BINARY( 8 ) NOT NULL AFTER config_md5config ,
ADD custom_var_k1 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v1 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k2 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v2 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k3 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v3 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k4 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v4 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_k5 VARCHAR( 50 ) DEFAULT NULL ,
ADD custom_var_v5 VARCHAR( 50 ) DEFAULT NULL

MySQL said: Documentation
#1054 - Unknown column ‘config_md5config’ in ‘piwik_log_visit’

matthieu · March 4, 2011, 10:06am

Remove all the “AFTER XXX” bits as they are not important.

jekko · March 4, 2011, 12:07pm

After a bit of trial and error it is working but now we have a problem when activating GEO IP plugin

Fatal error: Call to a member function filter() on a non-object in example.com/piwik/core/ViewDataTable/HtmlTable/AllColumns.php on line 52

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

dany · March 4, 2011, 2:20pm

Update your GeoIP Plugin.

At the bottom: Plugin using GeoIP to accurately detect visitor countries, cities, and continent · Issue #5465 · matomo-org/matomo · GitHub

anurodhp · March 4, 2011, 7:16pm

Upgrading my 1.1.1 to 1.2 i had the same errors mentioned here. I was able to restore from a backup of the database from this morning and then modified the upgrade script to get rid of all the errors. This is the SQL i used it has some of the after and drop statements removed. I hope this helps someone else.


  ALTER TABLE `piwik_log_visit` DROP `visit_server_date`, DROP INDEX `index_idsite_datetime_config`, ADD `visit_entry_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_entry_idaction_url`, ADD `visit_exit_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_exit_idaction_url`, CHANGE `visit_exit_idaction_url` `visit_exit_idaction_url` INT UNSIGNED NOT NULL, CHANGE `visit_entry_idaction_url` `visit_entry_idaction_url` INT UNSIGNED NOT NULL, CHANGE `referer_type` `referer_type` TINYINT UNSIGNED NULL DEFAULT NULL, ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL AFTER `visitor_returning`, ADD visitor_days_since_last SMALLINT(5) UNSIGNED NOT NULL, ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL, ADD `config_id` BINARY(8) NOT NULL AFTER `config_md5config`, ADD custom_var_k1 VARCHAR(50) DEFAULT NULL, ADD custom_var_v1 VARCHAR(50) DEFAULT NULL, ADD custom_var_k2 VARCHAR(50) DEFAULT NULL, ADD custom_var_v2 VARCHAR(50) DEFAULT NULL, ADD custom_var_k3 VARCHAR(50) DEFAULT NULL, ADD custom_var_v3 VARCHAR(50) DEFAULT NULL, ADD custom_var_k4 VARCHAR(50) DEFAULT NULL, ADD custom_var_v4 VARCHAR(50) DEFAULT NULL, ADD custom_var_k5 VARCHAR(50) DEFAULT NULL, ADD custom_var_v5 VARCHAR(50) DEFAULT NULL ;
 
  ALTER TABLE `piwik_log_link_visit_action` ADD `idsite` INT( 10 ) UNSIGNED NOT NULL AFTER `idlink_va` , ADD `server_time` DATETIME NOT NULL AFTER `idsite`, ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, ADD `idaction_name_ref` INT UNSIGNED NOT NULL AFTER `idaction_name`, ADD INDEX `index_idsite_servertime` ( `idsite` , `server_time` ) ;
 
  ALTER TABLE `piwik_log_conversion` DROP `referer_idvisit`, ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL, ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL, ADD custom_var_k1 VARCHAR(50) DEFAULT NULL, ADD custom_var_v1 VARCHAR(50) DEFAULT NULL, ADD custom_var_k2 VARCHAR(50) DEFAULT NULL, ADD custom_var_v2 VARCHAR(50) DEFAULT NULL, ADD custom_var_k3 VARCHAR(50) DEFAULT NULL, ADD custom_var_v3 VARCHAR(50) DEFAULT NULL, ADD custom_var_k4 VARCHAR(50) DEFAULT NULL, ADD custom_var_v4 VARCHAR(50) DEFAULT NULL, ADD custom_var_k5 VARCHAR(50) DEFAULT NULL, ADD custom_var_v5 VARCHAR(50) DEFAULT NULL ;
  
 UPDATE piwik_log_visit SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))), config_id = binary(unhex(substring(config_md5config,1,16))) ;
  
 UPDATE piwik_log_conversion SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))) ;
   ALTER TABLE `piwik_log_visit` DROP visitor_idcookie, DROP config_md5config ;
   ALTER TABLE `piwik_log_conversion` DROP visitor_idcookie ;
   ALTER TABLE `piwik_log_visit` ADD INDEX `index_idsite_datetime_config` (idsite, visit_last_action_time, config_id) ;
   UPDATE piwik_log_link_visit_action as action, piwik_log_visit as visit SET action.idsite = visit.idsite, action.server_time = visit.visit_last_action_time, action.idvisitor = visit.idvisitor WHERE action.idvisit=visit.idvisit ;
   ALTER TABLE `piwik_option` ADD INDEX ( `autoload` ) ;
   ALTER TABLE `piwik_site` ADD `group` VARCHAR( 250 ) NOT NULL;
   UPDATE piwik_option SET option_value = "1.2-rc2" WHERE option_name = "version_core";

jekko · March 5, 2011, 5:24am

thank you everyone for your help

Piwik is now working again

Jek

nosferatuvn · March 6, 2011, 9:43pm

hi,

EDIT: PIWIK work now!

i have some problem …

i get this error (auto update 1.1.1 -> 1.2)
If i use the mysql code for self install, in phpmyadmin i get the same error.


Kritischer Fehler während dem Upgrade-Prozess: /home/www/piwik/core/Updates/1.2-rc1.php:
Error trying to execute the query 'ALTER TABLE `piwik_log_visit` 
			    DROP `visit_server_date`,
			    DROP INDEX `index_idsite_date_config`,
			    DROP INDEX `index_idsite_datetime_config`,
		    	ADD `visit_entry_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_entry_idaction_url`,
			    ADD `visit_exit_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_exit_idaction_url`,
			    CHANGE `visit_exit_idaction_url` `visit_exit_idaction_url` INT UNSIGNED NOT NULL, 
			    CHANGE `visit_entry_idaction_url` `visit_entry_idaction_url` INT UNSIGNED NOT NULL,
			    CHANGE `referer_type` `referer_type` TINYINT UNSIGNED NULL DEFAULT NULL,
			    ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, 
			    ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL AFTER `visitor_returning`,
			    ADD visitor_days_since_last SMALLINT(5) UNSIGNED NOT NULL,
			    ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL,
			    ADD `config_id` BINARY(8) NOT NULL AFTER `config_md5config`,
			    ADD custom_var_k1 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_v1 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_k2 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_v2 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_k3 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_v3 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_k4 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_v4 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_k5 VARCHAR(50) DEFAULT NULL,
    			ADD custom_var_v5 VARCHAR(50) DEFAULT NULL
			   '.
The error was: SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'index_idsite_date_config'; check that column/key exists

and i test it with IGNORE, but it don’t work… please help …


ALTER IGNORE TABLE `piwik_log_visit` DROP `visit_server_date`, 
    DROP INDEX `index_idsite_date_config`, 
    DROP INDEX `index_idsite_datetime_config`, 
    ADD `visit_entry_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_entry_idaction_url`, 
    ADD `visit_exit_idaction_name` INT UNSIGNED NOT NULL AFTER `visit_exit_idaction_url`, 
    CHANGE `visit_exit_idaction_url` `visit_exit_idaction_url` INT UNSIGNED NOT NULL, 
    CHANGE `visit_entry_idaction_url` `visit_entry_idaction_url` INT UNSIGNED NOT NULL, 
    CHANGE `referer_type` `referer_type` TINYINT UNSIGNED NULL DEFAULT NULL, 
    ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, 
    ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL AFTER `visitor_returning`, 
    ADD visitor_days_since_last SMALLINT(5) UNSIGNED NOT NULL, 
    ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL, 
    ADD `config_id` BINARY(8) NOT NULL AFTER `config_md5config`, 
    ADD custom_var_k1 VARCHAR(50) DEFAULT NULL, ADD custom_var_v1 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k2 VARCHAR(50) DEFAULT NULL, ADD custom_var_v2 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k3 VARCHAR(50) DEFAULT NULL, ADD custom_var_v3 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k4 VARCHAR(50) DEFAULT NULL, ADD custom_var_v4 VARCHAR(50) DEFAULT NULL,
    ADD custom_var_k5 VARCHAR(50) DEFAULT NULL, ADD custom_var_v5 VARCHAR(50) DEFAULT NULL ;
    
    ALTER TABLE `piwik_log_link_visit_action` ADD `idsite` INT( 10 ) UNSIGNED NOT NULL AFTER `idlink_va` , 
    ADD `server_time` DATETIME NOT NULL AFTER `idsite`, 
    ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, 
    ADD `idaction_name_ref` INT UNSIGNED NOT NULL AFTER `idaction_name`, 
    ADD INDEX `index_idsite_servertime` ( `idsite` , `server_time` ) ;
    ALTER TABLE `piwik_log_conversion` DROP `referer_idvisit`, 
    ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`, 
    ADD visitor_count_visits SMALLINT(5) UNSIGNED NOT NULL, 
    ADD visitor_days_since_first SMALLINT(5) UNSIGNED NOT NULL, 
    ADD custom_var_k1 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_v1 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k2 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_v2 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k3 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_v3 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k4 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_v4 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_k5 VARCHAR(50) DEFAULT NULL, 
    ADD custom_var_v5 VARCHAR(50) DEFAULT NULL ;
    
    UPDATE piwik_log_visit SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))), config_id = binary(unhex(substring(config_md5config,1,16))) ;
    UPDATE piwik_log_conversion SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))) ;
    ALTER TABLE `piwik_log_visit` DROP visitor_idcookie, DROP config_md5config ;
    ALTER TABLE `piwik_log_conversion` DROP visitor_idcookie ;
    ALTER TABLE `piwik_log_visit` ADD INDEX `index_idsite_datetime_config` (idsite, visit_last_action_time, config_id) ;
    UPDATE piwik_log_link_visit_action as action, piwik_log_visit as visit SET action.idsite = visit.idsite, action.server_time = visit.visit_last_action_time, action.idvisitor = visit.idvisitor WHERE action.idvisit=visit.idvisit ;
    ALTER TABLE `piwik_option` ADD INDEX ( `autoload` ) ;
    ALTER TABLE `piwik_site` ADD `group` VARCHAR( 250 ) NOT NULL;
    UPDATE piwik_option SET option_value = "1.2-rc2" WHERE option_name = "version_core";

Rlyeh · March 9, 2011, 7:37am

matts instructions are not helping…
i did a rollback to a snapshot taken before updating and did everything again - carefully only hitting every button once - that is simply not the issue, because the message is again “SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘visit.visit_server_date’ in ‘where clause’”. i will go back to the snapshot again and wait for you to fix your update - please provide a news/statement whatever when its safe to update your software -.-

i followed the instructions again, inserted every sql command on its own - doesnt fix anything.

matthieu · March 11, 2011, 12:06am

Not sure what version you’re upgrading from, but the upgrade works so the problem might be with your setup. I recommend reinstalling with a clean DB since you are missing a field that we added long time ago

Lucianob · March 12, 2011, 3:04pm

[quote=matt]
Not sure what version you’re upgrading from, but the upgrade works so the problem might be with your setup. I recommend reinstalling with a clean DB since you are missing a field that we added long time ago[/quote]

Its sad. very sad.

I’m getting the same issues from Ryleh (and many others).
I’m upgrading from lastest version before 1.2. I believe it was 1.1.1, and allways did all automatic updates since almost 2 year ago. Piwik allways alerts us at top right screen, you guys did this way remember?
My setup is from DreamHost service.

I did all the instructions from this post, did not work.

I cant drop 2 years of data doing a clean DB setup, and sincerelly I gess its not “a missing field added long time ago” as I said before all updates have been made so far, so the true is the1.2 ugrade didnt works for all, its really bugged.

Im not complain, its just my frustatition and regret after to put so many credit and confidence in this script and have made the mistake of not having done a backup before this upgrade attempt. Yes its my stupid fault, and now looks like I will lost my data…

…If its true and just it (I cant believe) well I am very grateful to Piwik, sponsors and all people commited with this project so far, you guys rocks, but I think I’ll move to another solution.

sincerely

Luciano