@dmitryd opened this Issue on September 1st 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 commented on September 1st 2015 Owner

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 commented on September 1st 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 commented on September 1st 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 commented on September 1st 2015 Owner

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 commented on September 1st 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 commented on September 1st 2015 Owner

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 commented on September 1st 2015 Contributor

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

@dmitryd commented on September 1st 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 commented on September 1st 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 commented on September 2nd 2015 Owner

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 commented on September 20th 2015 Owner

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

@quba commented on September 21st 2015 Contributor

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 commented on September 21st 2015 Contributor

Reproduced on Piwik 2.14.3.

@tsteur commented on September 21st 2015 Owner

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 @<a class='mention' href='https://github.com/innodb_stats_on_metadata'>@innodb_stats_on_metadata</a>; 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 commented on September 21st 2015 Contributor

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

@mattab commented on November 18th 2015 Owner

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

@mattab commented on February 21st 2017 Owner
This Issue was closed on February 21st 2017
Powered by GitHub Issue Mirror