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

Jump to bottom

SQL error: Field 'visit_total_events' doesn't have a default value #14799

Closed
imrejonk opened this issue Aug 22, 2019 · 6 comments
Closed

SQL error: Field 'visit_total_events' doesn't have a default value #14799

imrejonk opened this issue Aug 22, 2019 · 6 comments
Labels
answered For when a question was asked and we referred to forum or answered it.
Milestone
3.12.0

Comments

@imrejonk
Copy link

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#12672: *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
Copy link
Member

tsteur commented Aug 22, 2019

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
Copy link
Author

imrejonk commented Sep 2, 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 @@SQL_MODE, @@GLOBAL.SQL_MODE;
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
| @@SQL_MODE                                                                                | @@GLOBAL.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
Copy link
Member

tsteur commented Sep 2, 2019

@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.

@tsteur tsteur closed this as completed Sep 2, 2019
@tsteur tsteur added the answered For when a question was asked and we referred to forum or answered it. label Sep 2, 2019
@imrejonk
Copy link
Author

imrejonk commented Sep 2, 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 🤷‍♂️

@mattab mattab added this to the 3.12.0 milestone Oct 27, 2019
@arisada
Copy link

arisada commented Dec 31, 2021

I've been hit with the same weird problem after migrating from a very old piwik installation to the latest matomo. In case someone else needs it, here is the command that fixed it for me:

mysql> ALTER TABLE piwik_log_visit MODIFY visit_total_events smallint unsigned;
Query OK, 833369 rows affected (16.63 sec)
Records: 833369  Duplicates: 0  Warnings: 0

I just hope there are no other unnullable columns waiting to trigger the same bug on a different parameter. So far it seems to work.

@plegall
Copy link

plegall commented Mar 1, 2022

I had to perform the same for location_browser_lang:

ALTER TABLE log_visit MODIFY `location_browser_lang` varchar(20) DEFAULT NULL;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Milestone
3.12.0
Development

No branches or pull requests
5 participants
@tsteur @mattab @arisada @plegall @imrejonk