SQL Error after Update to 0.5 Options

[maatien]

After updating Piwik from 0.4.5 to 0.5 there are lots of these errors:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v.visit_entry_idaction' in 'on clause'

Running the SQL queries in /core/Updates/0.5.php manually doesn't help - all index were already generated, visit_exit_idaction cannot be found in piwik_log_visit.

Is there any solution?

[sanoba]

I also have a sql error after updating my piwik.

Error trying to execute the query 'ALTER TABLE piwik_log_action ADD COLUMN `hash` INTEGER(10) UNSIGNED NOT NULL AFTER `name`;'.
The error was: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'hash'

[matthieu]

After updating Piwik from 0.4.5 to 0.5 there are lots of these errors:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v.visit_entry_idaction' in 'on clause'

For this specific error, it must be because somes files were not uploaded properly. Please try reupload all the Piwik files again, making sure the new ones overwrite the old ones.

[maatien]

Reupload doesn't help, too.

//edit:
The complete old data is accessable (only in day view).
Could it help to add the column 'visit_entry_idaction' manually?
//

Running piwik/index.php from shell throws an error:

CODE

(14:26:22) [/***/***/***] php5 piwik/index.php

Notice: Undefined index:  SCRIPT_NAME in /***/***/***/piwik/core/Url.php on line 107


Backtrace -->#0  Piwik_ErrorHandler(8, Undefined index:  SCRIPT_NAME, /***/***/***/piwik/core/Url.php, 107, Array ([url] => )) called at [/***/***/***/piwik/core/Url.php:107]

#1  Piwik_Url::getCurrentScriptName() called at [/***/***/***/piwik/core/Url.php:67]

#2  Piwik_Url::getCurrentScriptPath() called at [/***/***/***/piwik/core/Url.php:55]

#3  Piwik_Url::getCurrentUrlWithoutFileName() called at [/***/***/***/piwik/core/View.php:76]

#4  Piwik_View->__construct(/***/***/***/piwik/plugins/Login/templates/login.tpl) called at [/***/***/***/piwik/core/View.php:264]

#5  Piwik_View::factory(login) called at [/***/***/***/piwik/plugins/Login/Controller.php:53]

#6  Piwik_Login_Controller->login() called at [/***/***/***/piwik/plugins/Login/Controller.php:24]

#7  Piwik_Login_Controller->index() called at [(null):0]

#8  call_user_func_array(Array ([0] => Piwik_Login_Controller Object ([] => Login,[] => 2009-12-08,[] => Piwik_Date Object ([] => 1260226800),[] => Array ([label] => General_ColumnLabel,[nb_visits] => General_ColumnNbVisits,[nb_actions] => General_ColumnNbActions,[max_actions] => General_ColumnMaxActions,[sum_visit_length] => General_ColumnSumVisitLength,[nb_uniq_visitors] => General_ColumnNbUniqVisitors,[nb_actions_per_visit] => General_ColumnActionsPerVisit,[avg_time_on_site] => General_ColumnAvgTimeOnSite,[bounce_rate] => General_ColumnBounceRate,[revenue_per_visit] => General_ColumnValuePerVisit,[goals_conversion_rate] => General_ColumnVisitsWithConversions)),[1] => index), Array ()) called at [/***/***/***/piwik/core/FrontController.php:129]

#9  Piwik_FrontController->dispatch(Login, ) called at [/***/***/***/piwik/core/Controller.php:327]

#10 Piwik_Controller->redirectToIndex(CoreHome, index) called at [/***/***/***/piwik/plugins/CoreHome/Controller.php:32]

#11 Piwik_CoreHome_Controller->redirectToCoreHomeIndex() called at [(null):0]

#12 call_user_func_array(Array ([0] => Piwik_CoreHome_Controller Object ([] => CoreHome,[] => 2009-12-08,[] => Piwik_Date Object ([] => 1260226800),[] => Array ([label] => General_ColumnLabel,[nb_visits] => General_ColumnNbVisits,[nb_actions] => General_ColumnNbActions,[max_actions] => General_ColumnMaxActions,[sum_visit_length] => General_ColumnSumVisitLength,[nb_uniq_visitors] => General_ColumnNbUniqVisitors,[nb_actions_per_visit] => General_ColumnActionsPerVisit,[avg_time_on_site] => General_ColumnAvgTimeOnSite,[bounce_rate] => General_ColumnBounceRate,[revenue_per_visit] => General_ColumnValuePerVisit,[goals_conversion_rate] => General_ColumnVisitsWithConversions)),[1] => redirectToCoreHomeIndex), Array ()) called at [/***/***/***/piwik/core/FrontController.php:129]

#13 Piwik_FrontController->dispatch() called at [/***/***/***/piwik/index.php:100]

[hebbet]

i have the same issue.

[jockel]

Hi,

...
Error trying to execute the query 'ALTER TABLE piwik_log_action ADD COLUMN `hash` INTEGER(10) UNSIGNED NOT NULL AFTER `name`;'.
The error was: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'hash'...

I have exactly the same error message here. The automated update did not run, according to the
already mentioned zip-error. So I uploaded all files manualy. Now starting piwik in Firefox
I get the "upgrade Piwik" button and the error message mentioned above after pushing the
Upgrade button.

Any solutions to this?

Thank you!
Jockel

[matthieu]

This is maybe because the update was taking too long, or because it was triggered multiple times from refreshing the page...

Try the following
- set version_core to 0.5 in the piwik_option table.
- If you still get errors or if stats are not tracked anymore, you would manually set version_core to 0.4.4 in the piwik_option table. Then delete the hash column from the piwik_log_action table, and try again to click the upgrade button in Piwik. Make sure you click only once on the button!

[beutel45009]

mir fällt auf das bei den updates immer irgendetwas zerstört wird.

selbst die simpelsten funktionen wurden bei dem update auf 0.5 nicht geprüft.

das live visitor plugin funktioniert nicht mehr.

weiterhin erscheint permanent der fehler:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v.visit_entry_idaction' in 'on clause'

ich hoffe, dass schnell ein update auf 0.6 released wird da ich ansonsten andere analyse software einsetzen muss.

[beutel45009]

bughttp://img34.imageshack.us/img34/8106/piwik05.jpg

[XSK8]

mir fällt auf das bei den updates immer irgendetwas zerstört wird.

selbst die simpelsten funktionen wurden bei dem update auf 0.5 nicht geprüft.

das live visitor plugin funktioniert nicht mehr.

weiterhin erscheint permanent der fehler:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v.visit_entry_idaction' in 'on clause'

ich hoffe, dass schnell ein update auf 0.6 released wird da ich ansonsten andere analyse software einsetzen muss.

Hey Beutel, first it would be polite not to write in German.
Second i never had problems with updating Piwik and in my opinion it is quite normal that there may be some problems ... keep in mind how many diffent servers and configurations there may be and the system itself is very complex and powerfull...
You should be happy that there are people to work on this for your and our benefit!

[maatien]

- If you still get errors or if stats are not tracked anymore, you would manually set version_core to 0.4.4 in the piwik_option table. Then delete the hash column from the piwik_log_action table, and try again to click the upgrade button in Piwik. Make sure you click only once on the button!

Doesn't help also.
On every try I got this error:

CODE

/piwik/core/Updates/0.5.php:
Error trying to execute the query 'CREATE INDEX index_type_hash ON piwik_log_action (type, hash);'.
The error was: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'index_type_hash'

The column was deleted before.

[sanoba]

i have also a error in the live visitors widget

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v.visit_entry_idaction' in 'on clause'

[jockel]

Hi,
this is just ment as an addon info:

This is maybe because the update was taking too long, or because it was triggered multiple times from refreshing the page...

No, this is definitely not the case. The error happens within 2-3 seconds and script time is set
to something around 50 seconds!

...Try the following
- set version_core to 0.5 in the piwik_option table...

opening the database, table "piwik_option", I could see that most fields "option_value" were set to
0.5! Only version_core was still on 0.4.5 (except the few fields that obviously have different kinds
of values). I set it to 0.5! Starting piwik then gave me another error message in several widgets,
while not a single widget was displayed properly:
"SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2.idaction_url' in 'on clause'".

- If you still get errors or if stats are not tracked anymore, you would manually set version_core to 0.4.4 in the piwik_option table. Then delete the hash column from the piwik_log_action table, and try again to click the upgrade button in Piwik. Make sure you click only once on the button!

Doing this, starting piwik does not announce a new version to be available but asks for a database
upgrade. Running this upgrade I get an other error message:
/kunden/..../webseiten/...../piwik/core/Updates/0.5.php:
Error trying to execute the query 'CREATE INDEX index_type_hash ON piwik_log_action (type, hash);'.
The error was: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'index_type_hash'

Any other ideas?

Jockel

[maatien]

Doing this, starting piwik does not announce a new version to be available but asks for a database
upgrade. Running this upgrade I get an other error message:
/kunden/..../webseiten/...../piwik/core/Updates/0.5.php:
Error trying to execute the query 'CREATE INDEX index_type_hash ON piwik_log_action (type, hash);'.
The error was: SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name 'index_type_hash'

Any other ideas?

Jockel

There remains an index 'index_type_hash'. That index and the column must be deleted.
But it still hangs due to the column 'visit_exit_idaction', that doesn't exist in 'piwik_log_visit'.

[hebbet]

This is maybe because the update was taking too long, or because it was triggered multiple times from refreshing the page...

Try the following
- set version_core to 0.5 in the piwik_option table.
- If you still get errors or if stats are not tracked anymore, you would manually set version_core to 0.4.4 in the piwik_option table. Then delete the hash column from the piwik_log_action table, and try again to click the upgrade button in Piwik. Make sure you click only once on the button!

thanks that helped me and the data a tracked, but i don't see any chart (flash)

Attached thumbnail(s)

 

[vipsoft]

hebbet (and anyone else who modified the piwik_option table manually): If you bypass the update process, you have to manually clear out the cached files in tmp/templates_c/*

[vipsoft]

Matien/beuten/sanoba: disable third party plugins, i.e., EntryPage.

[hebbet]

hebbet (and anyone else who modified the piwik_option table manually): If you bypass the update process, you have to manually clear out the cached files in tmp/templates_c/*

no still not working

[maatien]

Matien/beuten/sanoba: disable third party plugins, i.e., EntryPage.

Now it works. EntryPage was the cause. Thanks!

[Richard]

hebbet (and anyone else who modified the piwik_option table manually): If you bypass the update process, you have to manually clear out the cached files in tmp/templates_c/*

now its woks fine!