Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error: Column not found: 1054 Unknown column 'log_link_visit_action.custom_var_k6' in 'field list' #8683

Closed
dmitryd opened this issue Sep 1, 2015 · 17 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. duplicate For issues that already existed in our issue tracker and were reported previously.

Comments

@dmitryd
Copy link

dmitryd commented Sep 1, 2015

Just installed a fresh Piwik copy (first time in my life). Now getting this error on the current official Piwik build (2.14.0) in every view and many widgets.

The error happens only and only if table prefix is specified during the installation. So this must be the cause. Adding the column manually makes "Visitors" page appear but with an exception which mentions "log_link_visit_action.custom_var_v6" without prefix entered during installation.

It seems some part of Piwik code does not know about table prefix.

@tsteur
Copy link
Member

tsteur commented Sep 1, 2015

I presume you mean Piwik 2.14.3?

Did Piwik work for you right after installation? You did execute the command to add more custom variables right (./console customvariables:set-max-custom-variables)? When does this error occur? Whenever you log in immediately?

@dmitryd
Copy link
Author

dmitryd commented Sep 1, 2015

I got Piwik with wget as described in the installation manual. I checked CHANGELOG.md for version. At line 5 it says "## Piwik 2.14.0".

I did not run any console commands. The installation manual did not mention that.

@dmitryd
Copy link
Author

dmitryd commented Sep 1, 2015

During the first login it told me there is no data. After the first request to the site it started to show blocks but many of them showed an error about this column.

@tsteur
Copy link
Member

tsteur commented Sep 1, 2015

Running a console command was not needed so all good. I was just confused by custom_var_v6 which should not exist. The highest custom-var column by default should be custom_var_v5. Can you let us know the name of some of the blocks that showed an error about this column?

Can you maybe run the command ./console customvariables:info within your Piwik directory in case you have access to the command line and let us know if it detects an error? It will probably suggest to run the command ./console customvariables:set-max-custom-variables 5 to fix this error.

@dmitryd
Copy link
Author

dmitryd commented Sep 1, 2015

$ ./console customvariables:info

****************************************************
  Your Piwik is configured for 5 custom variables.
****************************************************

"Visits over time" said that graphics could not be displayed. Another widget showed an error about custom_var_v6. Also if I went to Visitors, it showed nothing except the exception.

Now I tried to repeat the installation and I have no such problem anymore. I am not sure why it happened before, I repeated step by step exactly.

@tsteur
Copy link
Member

tsteur commented Sep 1, 2015

I looked through the code a couple of times but still not sure how this can happen. There's nothing that you could have done wrong, it must be a random edge case. Somehow it seems like it installed eg the column custom_var_v2, custom_var_v3, custom_var_v4, custom_var_v5, custom_var_v6 but that's not really possible. Did you run the ./console customvariables:info command after installing the second time or before?

@mattab I'm not sure what to do here, I'd suggest to close and see if it happens again as I've never heard of it before and can't really imagine how this is happening.

@quba
Copy link
Contributor

quba commented Sep 1, 2015

It happened once on Piwik Cloud. We were not able to find the cause. It's for sure an edge case.

@dmitryd
Copy link
Author

dmitryd commented Sep 1, 2015

@tsteur I ran the command after the second install. Unfortunately I cleaned up bad installation earlier because I had to progress with the customer's task :(

Thank you both for your efforts!

@dmitryd
Copy link
Author

dmitryd commented Sep 1, 2015

Btw, could you point me to the file or files where creation of these variables happen? I may have a fresh pair of eyes on it :)

@tsteur
Copy link
Member

tsteur commented Sep 2, 2015

I ran the command after the second install.

Cool thx. I was surprised the command did not detect an error :)

Btw, could you point me to the file or files where creation of these variables happen?

Here: https://github.com/piwik/piwik/blob/master/plugins/CustomVariables/Model.php#L124 enjoy :)

@mattab
Copy link
Member

mattab commented Sep 20, 2015

If anyone else experiences this issue and error message, please leave a comment as we will be interested to try reproduce or prevent this error

@mattab mattab added this to the Mid term milestone Sep 20, 2015
@mattab mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Sep 20, 2015
@quba
Copy link
Contributor

quba commented Sep 21, 2015

I was able to reproduce. There are some binlogs from the installation process:

SET TIMESTAMP=1442820189/*!*/;
ALTER TABLE `piwik_log_visit` ADD COLUMN `location_longitude` float(10, 6) DEFAULT NULL, ADD COLUMN `location_latitude` float(10, 6) DEFAULT NULL, ADD COLUMN `location_region` char(2) DEFAULT NULL, ADD COLUMN `visitor_localtime` TIME NOT NULL, ADD COLUMN `location_country` CHAR(3) NOT NULL, ADD COLUMN `location_city` varchar(255) DEFAULT NULL, ADD COLUMN `config_device_type` TINYINT( 100 ) NULL DEFAULT NULL, ADD COLUMN `config_device_model` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ADD COLUMN `config_os` CHAR(3) NOT NULL, ADD COLUMN `config_os_version` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ADD COLUMN `visit_total_events` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `visitor_days_since_last` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `config_quicktime` TINYINT(1) NOT NULL, ADD COLUMN `config_pdf` TINYINT(1) NOT NULL, ADD COLUMN `config_realplayer` TINYINT(1) NOT NULL, ADD COLUMN `config_silverlight` TINYINT(1) NOT NULL, ADD COLUMN `config_windowsmedia` TINYINT(1) NOT NULL, ADD COLUMN `config_java` TINYINT(1) NOT NULL, ADD COLUMN `config_gears` TINYINT(1) NOT NULL, ADD COLUMN `config_resolution` VARCHAR(9) NOT NULL, ADD COLUMN `config_cookie` TINYINT(1) NOT NULL, ADD COLUMN `config_director` TINYINT(1) NOT NULL, ADD COLUMN `config_flash` TINYINT(1) NOT NULL, ADD COLUMN `config_device_brand` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, ADD COLUMN `config_browser_version` VARCHAR(20) NOT NULL, ADD COLUMN `visitor_returning` TINYINT(1) NOT NULL, ADD COLUMN `visitor_days_since_order` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `visitor_count_visits` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `visit_entry_idaction_name` INTEGER(11) UNSIGNED NOT NULL, ADD COLUMN `visit_entry_idaction_url` INTEGER(11) UNSIGNED NOT NULL, ADD COLUMN `visit_first_action_time` DATETIME NOT NULL, ADD COLUMN `visitor_days_since_first` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `visit_total_time` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `user_id` VARCHAR(200) NULL, ADD COLUMN `visit_goal_buyer` TINYINT(1) NOT NULL, ADD COLUMN `visit_goal_converted` TINYINT(1) NOT NULL, ADD COLUMN `visit_exit_idaction_name` INTEGER(11) UNSIGNED NOT NULL, ADD COLUMN `visit_exit_idaction_url` INTEGER(11) UNSIGNED NULL DEFAULT 0, ADD COLUMN `referer_url` TEXT NOT NULL, ADD COLUMN `location_browser_lang` VARCHAR(20) NOT NULL, ADD COLUMN `config_browser_engine` VARCHAR(10) NOT NULL, ADD COLUMN `config_browser_name` VARCHAR(10) NOT NULL, ADD COLUMN `referer_type` TINYINT(1) UNSIGNED NULL, ADD COLUMN `referer_name` VARCHAR(70) NULL, ADD COLUMN `visit_total_actions` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `visit_total_searches` SMALLINT(5) UNSIGNED NOT NULL, ADD COLUMN `referer_keyword` VARCHAR(255) NULL

249376-ALTER TABLE piwik_log_visit ADD COLUMN custom_var_k1 VARCHAR(200) DEFAULT NULL,ADD COLUMN custom_var_v1 VARCHAR(200) DEFAULT NULL
250620-ALTER TABLE piwik_log_visit ADD COLUMN custom_var_k2 VARCHAR(200) DEFAULT NULL,ADD COLUMN custom_var_v2 VARCHAR(200) DEFAULT NULL
250996-ALTER TABLE piwik_log_visit ADD COLUMN custom_var_k3 VARCHAR(200) DEFAULT NULL,ADD COLUMN custom_var_v3 VARCHAR(200) DEFAULT NULL
251626-ALTER TABLE piwik_log_visit ADD COLUMN custom_var_k5 VARCHAR(200) DEFAULT NULL,ADD COLUMN custom_var_v5 VARCHAR(200) DEFAULT NULL
251898-ALTER TABLE piwik_log_visit ADD COLUMN custom_var_k6 VARCHAR(200) DEFAULT NULL,ADD COLUMN custom_var_v6 VARCHAR(200) DEFAULT NULL

The conclusion: in some rare cases/edge case Piwik is altering piwik_log_visit and piwik_log_conversion during the installation process and adding custom var with index 6. This is not the case for piwik_log_link_visit_action.

@quba
Copy link
Contributor

quba commented Sep 21, 2015

Reproduced on Piwik 2.14.3.

@tsteur
Copy link
Member

tsteur commented Sep 21, 2015

This would kinda mean that in some cases not all columns are returned here I think https://github.com/piwik/piwik/blob/2.15.0-b8/core/DataAccess/TableMetadata.php#L28 Is it possible that this is maybe related to a master-master setup or so?

Can you execute SELECT @@innodb_stats_on_metadata; and let us know the result?

Maybe we could try to use SELECT * FROM ... LIMIT 0 instead of SHOW COLUMNS FROM ... see eg http://dba.stackexchange.com/a/94616

@quba
Copy link
Contributor

quba commented Sep 21, 2015

It's set to 0. Regarding master-master - yes we have it enabled, but installator and UI always connect to the same DB.

@mattab mattab modified the milestones: Short term, Mid term Sep 21, 2015
@mattab mattab modified the milestones: Mid term, Short term Nov 18, 2015
@mattab
Copy link
Member

mattab commented Nov 18, 2015

Moving back to Mid term as it's edge case - related to: #9211

@mattab mattab modified the milestones: Long term, Mid term Dec 5, 2016
@mattab
Copy link
Member

mattab commented Feb 21, 2017

Marking as duplicate of #9211

@mattab mattab closed this as completed Feb 21, 2017
@mattab mattab added the duplicate For issues that already existed in our issue tracker and were reported previously. label Feb 21, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc. duplicate For issues that already existed in our issue tracker and were reported previously.
Projects
None yet
Development

No branches or pull requests

4 participants