@imrejonk opened this Issue on August 22nd 2019

System information

  • Matomo: Matomo 3.11.0
  • OS: Debian 10.0
  • PHP: PHP-FPM 7.3
  • Database: MariaDB 10.3
  • Web server: NGINX 1.14

We've experienced a database issue after updating to Matomo 3.11.0 from 3.10.0, causing Matomo to stop tracking visits and preventing plugin updates. The database upgrade seemed to be going well, as did our re-run of the database upgrade (as described in the Matomo Update FAQ). Still, visits were not being tracked anymore. Plugin updates failed with "Could not verify the security token on this form". We noticed this error in our NGINX error logs:

2019/08/22 12:02:11 [error] 12672<a href='/12672'>#12672</a>: *137116 FastCGI sent in stderr: "PHP message: Error in Matomo (tracker): Error query: SQLSTATE[HY000]: General error: 1364 Field 'visit_total_events' doesn't have a default value In 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_interactions, 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, visitor_localtime, visitor_days_since_last, config_resolution, config_cookie, config_director, config_flash, config_gears, config_java, config_pdf, config_quicktime, config_realplayer, con" while reading response header from upstream, client: 95.97.74.124, server: stats.bof.nl, request: "GET /piwik.php?action_name=Bits%20of%20Freedom%20%E2%80%93%20Bits%20of%20Freedom%20komt%20op%20voor%20internetvrijheid%20door%20de%20online%20grondrechten%20op%20communicatievrijheid%20en%20privacy%20te%20beschermen.&idsite=2&rec=1&r=897124&h=12&m=2&s=11&url=https%3A%2F%2Fwww.bitsoffreedom.nl%2F&_id=ebe8ce753d6be31d&_idts=1566460057&_idvc=2&_idn=0&_refts=0&_viewts=1566464120&send_image=1&cookie=1&res=1920x1080&gt_ms=538&pv_id=T8ZtGx HTTP/2.0", upstream: "fastcgi://unix:/run/php7.3-fpm-matomo.sock:", host: "stats.bof.nl"

Both issues were resolved after executing this SQL statement against the database:
ALTER TABLE piwik_log_visit MODIFY COLUMN visit_total_events SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0;

Could it be that this ALTER TABLE statement is missing in the Matomo 3.11.0 database migration script?

@tsteur commented on August 22nd 2019 Member

The field should be nullable though see https://github.com/matomo-org/matomo/pull/10492/files#diff-1eae80e84fbec709e6e3c03b237656f9R21

In theory this should have just worked? We have basically pretty much all fields nullable. Does it maybe depend on the sql_mode?

@imrejonk commented on September 2nd 2019

Thanks @tsteur, we've made the field nullable in our database as well. That seems to work just as well.

Not sure what effect sql_mode has on these migrations, but here are our current values:

MariaDB [matomo]> SELECT @<a class='mention' href='https://github.com/SQL_MODE'>@SQL_MODE</a>, @<a class='mention' href='https://github.com/GLOBAL'>@GLOBAL</a>.SQL_MODE;
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| @<a class='mention' href='https://github.com/SQL_MODE'>@SQL_MODE</a>                                                                                | @<a class='mention' href='https://github.com/GLOBAL'>@GLOBAL</a>.SQL_MODE                                                                         |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

Would you say these values are okay?

@tsteur commented on September 2nd 2019 Member

@imrejonk it should have already been nullable so it was likely not due to the SQL mode. It seems for some reason it was not nullable on your installation. Maybe there was an issue a while back with Matomo 3 update or some were manually executed. I'll close this issue again as it really looks like all should be working fine from our side. I recommend you maybe check other fields too if they are nullable.

@imrejonk commented on September 2nd 2019

Thanks for the help @tsteur! I suppose it was just a migration issue on our side. I should've mentioned that we migrated our complete Matomo installation to a new system a couple months back. Database went from some old MySQL to MariaDB 10.3. Guess we're going to check all our fields against the database classes to be sure :man_shrugging:

This Issue was closed on September 2nd 2019
Powered by GitHub Issue Mirror