Piwik triggering SQL warnings

[johannes]

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]

Hi @johannes

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

[mattab]

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?