Bugs after upgrade from 1.1.1 to 1.2.1

When looking at data from the past piwik seems to work fine. When I set it to “today” I get these in various boxes:

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

Unexpected data type to render.

There is no data for this report.

I followed the instructions here:
http://forum.piwik.org/read.php?2,72767,page=1#msg-72790

Note that I did not experience a browser timeout nor did I “double click” the upgrade. The upgrade db script failed. Here is the manual run of the SQL scripts:

mysql> 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 ;
ERROR 1054 (42S22): Unknown column ‘config_md5config’ in 'piwik_log_visit’
mysql> 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 ;
ERROR 1054 (42S22): Unknown column ‘config_md5config’ in 'piwik_log_visit’
mysql> 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 ) ;
ERROR 1060 (42S21): Duplicate column name 'idsite’
mysql> ALTER IGNORE 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 ) ;
ERROR 1060 (42S21): Duplicate column name 'idsite’
mysql> 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 ;
ERROR 1091 (42000): Can’t DROP ‘referer_idvisit’; check that column/key exists
mysql> UPDATE piwik_log_visit SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))), config_id = binary(unhex(substring(config_md5config,1,16))) ;
ERROR 1054 (42S22): Unknown column ‘visitor_idcookie’ in 'field list’
mysql> UPDATE piwik_log_conversion SET idvisitor = binary(unhex(substring(visitor_idcookie,1,16))) ;
ERROR 1054 (42S22): Unknown column ‘visitor_idcookie’ in 'field list’
mysql> ALTER TABLE piwik_log_visit DROP visitor_idcookie, DROP config_md5config ;
ERROR 1091 (42000): Can’t DROP ‘visitor_idcookie’; check that column/key exists
mysql> ALTER TABLE piwik_log_conversion DROP visitor_idcookie ;
ERROR 1091 (42000): Can’t DROP ‘visitor_idcookie’; check that column/key exists
mysql> ALTER TABLE piwik_log_visit ADD INDEX index_idsite_datetime_config (idsite, visit_last_action_time, config_id) ;
ERROR 1061 (42000): Duplicate key name 'index_idsite_datetime_config’
mysql> 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 ;
Query OK, 2 rows affected (0.58 sec)
Rows matched: 235742 Changed: 2 Warnings: 0

mysql> ALTER TABLE piwik_option ADD INDEX ( autoload ) ;
Query OK, 65 rows affected (0.01 sec)
Records: 65 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE piwik_site ADD group VARCHAR( 250 ) NOT NULL;
ERROR 1060 (42S21): Duplicate column name 'group’
mysql> UPDATE piwik_option SET option_value = “1.2-rc2” WHERE option_name = “version_core”;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

I’m not sure if this helps but this problem seems to be limited to only 1 domain: the first one that was created. All of the others seem to work fine. So only the 1 domain, and only for today. I also notice that when clicking on today from another date I see “Oops… problem during the request, please try again” flash for a second.

After midnight and checking in. Still only the 1 problematic website. dates 3/24 and before work fine, 3/25 and after give errors. While all other websites work sometimes when switching between them on the dashboard screen I get the “oops” message. Due to the problems with the 1 website the all websites dashboard has been rendered inoperable for dates after 3/25 as well with error message:

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

My plan is to install open web analytics and run it in parallel for a while to see which product I like better. I certainly hope to see some assistance with my piwik issues at some point.

It sounds like the upgrade didn’t run smoothly. See the post: 301 Moved Permanently

Maybe you should take the time to actually read my post before responding.

I know the instructions in the post I linked are rather complicated & confusing, but it seems you haven’t followed them.

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.

Ok I figured out the problem. I kept thinking that it was clear that visit_server_date had been dropped as part of this upgrade but something kept calling it, and something that not everyone was experiencing. So obviously that meant a plugin. In my case: the SiteSearch plugin. Disabling it has fixed the issues I was running into, other than I still see the “oops” message from time to time switching between sites. But it has fixed the “Unknown column ‘visit.visit_server_date’” problem.

This raises some interesting issues with having an unstable schema and third party plugins. One possibility is to disable 3rd party plugins during the upgrade and allow the user to re-enable them afterward. That would make it clear when one of them caused a problem like this.

Cool, a new version of the plugin was released for 1.2: Plugin Internal search tracking - search analytics reports · Issue #5469 · matomo-org/matomo · GitHub

Yes I have installed the new version and it seems to work fine. Probably worth checking all third party plugins before upgrading in the future to check for compatibility problems.

And yes, I did follow your instructions, it just happens that they’re not a solution to this problem. Also I’m not sure what you think “Repeat until queries are ran properly” means. Adding IGNORE to alter statements is not going to prevent errors (thought I did follow those instructions, though it’s not the log I pasted above so my apologies if that confused you). For example, to take simple case from above:

mysql> ALTER TABLE piwik_log_conversion DROP visitor_idcookie ;
ERROR 1091 (42000): Can’t DROP ‘visitor_idcookie’; check that column/key exists
mysql> ALTER IGNORE TABLE piwik_log_conversion DROP visitor_idcookie ;
ERROR 1091 (42000): Can’t DROP ‘visitor_idcookie’; check that column/key exists

So a person following your instructions will never execute the queries without error. Of course I got it wrong that the upgrade db script caused the problem. The script ran without error so I clearly jumped to conclusions, largely based on what I had read in the linked thread, that it was responsible for my issues.

Checking compatibility with 3rd party plugins will be handled when we roll out the plugin repository and infrastructure.