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

Piwik triggering SQL warnings #9971

Open
johannes opened this issue Mar 29, 2016 · 2 comments
Open

Piwik triggering SQL warnings #9971

johannes opened this issue Mar 29, 2016 · 2 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc.

Comments

@johannes
Copy link

Running Piwik 2.16.0 on MySQL 5.7 one can identify a few Warnings being generated. This indicates that data might be inaccurate or other issues.

mysql> select query, exec_count, errors, warnings from sys.x$statements_with_errors_or_warnings WHERE db = 'piwik_database'\G
*************************** 1. row ***************************
     query: INSERT INTO `piwik_log_visit` ( `idvisitor` , `config_id` , `location_ip` , `idsite` , `visit_first_action_time` , `visit_goal_buyer` , `visit_goal_converted` , `visit_last_action_time` , `visitor_days_since_first` , `visitor_days_since_order` , `visitor_returning` , `visitor_count_visits` , `visit_entry_idaction_name` , `visit_entry_idaction_url` , `visit_exit_idaction_name` , `visit_exit_idaction_url` , `visit_total_actions` , `visit_total_searches` , `referer_name` , `referer_type` , `referer_url` , `location_browser_lang` , `config_browser_engine` , `config_browser_name` , `config_browser_version` , `config_device_brand` , `config_device_model` , `config_device_type` , `config_os` , `config_os_version` , `visit_total_events` , `visitor_localtime` , `visitor_days_since_last` , `config_resolution` , `config_cookie` , `config_director` , `config_flash` , `config_gears` , `config_java` , `config_pdf` , `config_quicktime` , `config_realplayer` , `config_silverlight` , 
exec_count: 10
    errors: 0
  warnings: 26
*************************** 2. row ***************************
     query: SELECT `visit_last_action_time` , `visit_first_action_time` , `idvisitor` , `idvisit` , `user_id` , `visit_exit_idaction_url` , `visit_exit_idaction_name` , `visitor_returning` , `visitor_days_since_first` , `visitor_days_since_order` , `visitor_count_visits` , `visit_goal_buyer` , `location_country` , `location_region` , `location_city` , `location_latitude` , `location_longitude` , `referer_name` , `referer_keyword` , `referer_type` , `visit_total_actions` , `visit_total_searches` , `visit_total_events` , `visit_total_time` , `location_ip` , `location_browser_lang` , `custom_var_k1` , `custom_var_v1` , `custom_var_k2` , `custom_var_v2` , `custom_var_k3` , `custom_var_v3` , `custom_var_k4` , `custom_var_v4` , `custom_var_k5` , `custom_var_v5` FROM `piwik_log_visit` WHERE `visit_last_action_time` >= ? AND `visit_last_action_time` <= ? AND `idsite` = ? AND `idvisitor` = ? ORDER BY `visit_last_action_time` DESC LIMIT ? 
exec_count: 18
    errors: 0
  warnings: 17
*************************** 3. row ***************************
     query: INSERT INTO `piwik_log_link_visit_action` ( `idvisit` , `idsite` , `idvisitor` , `idaction_url` , `idaction_url_ref` , `idaction_name_ref` , `server_time` , `time_spent_ref_action` , `idaction_name` , `custom_float` ) VALUES (...) 
exec_count: 18
    errors: 0
  warnings: 17
*************************** 4. row ***************************
     query: INSERT INTO `piwik_log_visit` ( `idvisitor` , `config_id` , `location_ip` , `idsite` , `visit_first_action_time` , `visit_goal_buyer` , `visit_goal_converted` , `visit_last_action_time` , `visitor_days_since_first` , `visitor_days_since_order` , `visitor_returning` , `visitor_count_visits` , `visit_entry_idaction_name` , `visit_entry_idaction_url` , `visit_exit_idaction_name` , `visit_exit_idaction_url` , `visit_total_actions` , `visit_total_searches` , `referer_keyword` , `referer_name` , `referer_type` , `referer_url` , `location_browser_lang` , `config_browser_engine` , `config_browser_name` , `config_browser_version` , `config_device_brand` , `config_device_model` , `config_device_type` , `config_os` , `config_os_version` , `visit_total_events` , `visitor_localtime` , `visitor_days_since_last` , `config_resolution` , `config_cookie` , `config_director` , `config_flash` , `config_gears` , `config_java` , `config_pdf` , `config_quicktime` , `config_realplayer` , 
exec_count: 7
    errors: 0
  warnings: 16
*************************** 5. row ***************************
     query: SELECT `visit_last_action_time` , `visit_first_action_time` , `idvisitor` , `idvisit` , `user_id` , `visit_exit_idaction_url` , `visit_exit_idaction_name` , `visitor_returning` , `visitor_days_since_first` , `visitor_days_since_order` , `visitor_count_visits` , `visit_goal_buyer` , `location_country` , `location_region` , `location_city` , `location_latitude` , `location_longitude` , `referer_name` , `referer_keyword` , `referer_type` , `visit_total_actions` , `visit_total_searches` , `visit_total_events` , `visit_total_time` , `location_ip` , `location_browser_lang` , `custom_var_k1` , `custom_var_v1` , `custom_var_k2` , `custom_var_v2` , `custom_var_k3` , `custom_var_v3` , `custom_var_k4` , `custom_var_v4` , `custom_var_k5` , `custom_var_v5` FROM `piwik_log_visit` WHERE `visit_last_action_time` >= ? AND `visit_last_action_time` <= ? AND `idsite` = ? AND `user_id` IS NULL AND `config_id` = ? ORDER BY `visit_last_action_time` DESC LIMIT ? 
exec_count: 17
    errors: 0
  warnings: 16
*************************** 6. row ***************************
     query: SET `sql_mode` = ? 
exec_count: 7
    errors: 0
  warnings: 7
*************************** 7. row ***************************
     query: UPDATE `piwik_log_visit` SET `idvisitor` = ? , `visit_last_action_time` = ? , `visit_exit_idaction_name` = ? , `visit_exit_idaction_url` = ? , `visit_total_actions` = `visit_total_actions` + ? , `visit_total_time` = ? WHERE `idsite` = ? AND `idvisit` = ? 
exec_count: 1
    errors: 0
  warnings: 1
7 rows in set (0.00 sec)

The 6th row was reported as #9970 the others I haven't identified.

@mattab
Copy link
Member

mattab commented Mar 31, 2016

Hi @johannes

Thanks for the report. Would it be possible to paste here the warnings for the row 1, 2 and 7?

@mattab mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Mar 31, 2016
@mattab
Copy link
Member

mattab commented Jul 14, 2016

Hi @johannes or anyone else experiencing warnings in Mysql 5.7 - it would be helpful if you could paste the SHOW WARNINGS; output from mysql?

@mattab mattab added this to the 2.16.x (LTS) milestone Jul 14, 2016
@mattab mattab modified the milestones: 2.16.x (LTS), Mid term Aug 25, 2016
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.
Projects
None yet
Development

No branches or pull requests

2 participants