PIWIK 1.3 db upgrade problem - Can't DROP 'index_idsite_datetime_config'; check that column/key exists


#1

#1091 - Can’t DROP ‘index_idsite_datetime_config’; check that column/key exists

I get et above error while trying to upgrade db from 1.2 to 1.3

http://www.askfrank.net/seo-tools/PIWIK/error1.PNG

Manual and auto update gives same error

http://www.askfrank.net/seo-tools/PIWIK/error1-1.PNG

Any idea how to fix this?

I am tracking over 60 websites


(vipsoft) #2

Since we’re trying to drop the index, ignore the error and execute the rest of the SQL statements manually.


#3

Tony, thanks for the tip,
Since the whole statement was :
*
ALTER DATABASE askfrank_piwik DEFAULT CHARACTER SET utf8;
ALTER TABLE piwik_log_visit DROP INDEX index_idsite_datetime_config, DROP INDEX index_idsite_idvisit, ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time), ADD INDEX index_idsite_datetime (idsite, visit_last_action_time);
ALTER TABLE piwik_goal ADD allow_multiple tinyint(4) NOT NULL AFTER case_sensitive;
ALTER TABLE piwik_log_conversion ADD buster int unsigned NOT NULL AFTER revenue, DROP PRIMARY KEY, ADD PRIMARY KEY (idvisit, idgoal, buster);
ALTER TABLE piwik_log_visit ADD INDEX index_idsite_idvisitor (idsite, idvisitor);
UPDATE piwik_option SET option_value = “1.2.5-rc7” WHERE option_name = “version_core”;

Executing it manualy already gave me an error,
What exactly do run manually again?
thanks


(Matthieu Aubry) #4

ignore the very first DROP index_idsite_date_config and leave the rest, so as follows


ALTER DATABASE `askfrank_piwik` DEFAULT CHARACTER SET utf8;
ALTER TABLE `piwik_log_visit` DROP INDEX index_idsite_idvisit, ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time), ADD INDEX index_idsite_datetime (idsite, visit_last_action_time);
ALTER TABLE `piwik_goal` ADD `allow_multiple` tinyint(4) NOT NULL AFTER case_sensitive;
ALTER TABLE `piwik_log_conversion` ADD buster int unsigned NOT NULL AFTER revenue, DROP PRIMARY KEY, ADD PRIMARY KEY (idvisit, idgoal, buster);
ALTER TABLE `piwik_log_visit` ADD INDEX index_idsite_idvisitor (idsite, idvisitor);
UPDATE `piwik_option` SET option_value = "1.2.5-rc7" WHERE option_name = "version_core";

if you get an error for the next bit, then do it again (remove just this bit) and try again, until all bits have been executed and you can be sure that the update was done cleanly.

Next time, consider running the update in the Console shell or make sure you don’t click twice on the button Update.


#5

It worked after I deleted this whole line:

ALTER TABLE piwik_log_visit DROP INDEX index_idsite_idvisit, ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time), ADD INDEX index_idsite_datetime (idsite, visit_last_action_time);

than ks for your help


(Betablocker) #6

tried to do - that. with:

ALTER DATABASE foo DEFAULT CHARACTER SET utf8;
UPDATE piwik_option SET option_value = “1.2.5-rc7” WHERE option_name = “version_core”;

than sql works.
update is ok

but piwik in browser still shows me the update screen.


(Matthieu Aubry) #7

You should run UPDATE piwik_option SET option_value = “1.3” WHERE option_name = “version_core”; but only after all other queries have been run


(Olabre) #8

running the SQL update to 1.3 manually worked for me - I have to admit that almost every update fails for some reason, this is a little fustrating, but Piwik works fine for me


(Matthieu Aubry) #9

Olabre, every update should work fine, if it fails for SQL you should run the update using the shell command as explained on the update screen.


#10

Hello!!
I don’t know what to do after that :

What can I do if ‘config_id’ doesn’t exist in table ?

My Piwik doesn’t publish anything since 24th of March 2011. You can see here : Sign in - Piwik

Thanks in advance for help.
Best regards.


(Matthieu Aubry) #11

Alain, you have missed some queries, in particular you should have seen 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


#12

[quote=matt]
Alain, you have missed some queries, in particular you should have seen 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[/quote]

Piwik-upgrade only write what you report over there on April 18, 2011 06:26AM :

[quote=matt]
ignore the very first DROP index_idsite_date_config and leave the rest, so as follows


ALTER DATABASE `askfrank_piwik` DEFAULT CHARACTER SET utf8;
ALTER TABLE `piwik_log_visit` DROP INDEX index_idsite_idvisit, ADD INDEX index_idsite_config_datetime (idsite, config_id, visit_last_action_time), ADD INDEX index_idsite_datetime (idsite, visit_last_action_time);
ALTER TABLE `piwik_goal` ADD `allow_multiple` tinyint(4) NOT NULL AFTER case_sensitive;
ALTER TABLE `piwik_log_conversion` ADD buster int unsigned NOT NULL AFTER revenue, DROP PRIMARY KEY, ADD PRIMARY KEY (idvisit, idgoal, buster);
ALTER TABLE `piwik_log_visit` ADD INDEX index_idsite_idvisitor (idsite, idvisitor);
UPDATE `piwik_option` SET option_value = "1.2.5-rc7" WHERE option_name = "version_core";

if you get an error for the next bit, then do it again (remove just this bit) and try again, until all bits have been executed and you can be sure that the update was done cleanly.

Next time, consider running the update in the Console shell or make sure you don’t click twice on the button Update.[/quote]
So I only try this with tie error that I mentionned. But if you say so, I will try.


(Matthieu Aubry) #13

So try to execute the full query, then if you get the error "visit_server_date not found" , it measn the first line of the query was alredy successful. Then try again with removing only this part of the query (leaving only


ALTER TABLE  DROP INDEX `index_idsite_date_config`, [...]

)

etc. until all queries are succesful


#14

OK!! The only problem was :


DROP INDEX `index_idsite_datetime_config`,

But now, piwik says (only for stats after 24 march 2011) :


SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_link_visit_action.idvisitor' in 'field list'

and :


Il n'y a pas de données pour ce rapport.

Many thanks!


#15

up.
Any solution? I have 2 piwik with this problem…
Is it possible to do something specific on a table? How can I know which record is doing problem?
Thanks in advance (and sorry for my poor english).


(Matthieu Aubry) #16

It seems you haven’t run the upgrade:

	    'ALTER TABLE `'. Piwik_Common::prefixTable('log_link_visit_action') .'` 
			ADD `idsite` INT( 10 ) UNSIGNED NOT NULL AFTER `idlink_va` , 
			ADD `server_time` DATETIME 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` )

it is part of the file core/Updates/1.2-rc1.php


#17

Hello!

Thank you for your answer. It works for 1 site but now :

And it doesn’t work for the other :

And the current error is :

Any other idea?

Many thanks for the precious help.
Best regards.


(Matthieu Aubry) #18

Sorry, I think it’s best if you try to understand the SQL working, unfortunaly it seems you missed some queries when running the update manually… so the key part is to only execute the sql queries that were not already executed, so if the error is : "Column not found: 1054 Unknown column ‘idvisitor’ in ‘field list’ " remove the bit of SQL query that references ‘idvisitor’… after few tries and making sure you run all queries it will work again!


#19

OK, thank you very much.
Where can I find the queries to execute? and I will do as you suggest.


(Matthieu Aubry) #20

in piwik/core/Updates/ - from the version which you upgraded from.

good luck :wink: