search

matomo-org / matomo

Empowering People Ethically with the leading open source alternative to Google Analytics that gives you full control over your data. Matomo lets you easily collect data from websites & apps and visualise this data and extract insights. Privacy is built-in. Liberating Web Analytics. Star us on Github? +1. And we love Pull Requests!
https://matomo.org/
GNU General Public License v3.0
19.72k stars 2.63k forks source link

Wrong amount of Users / Websites in Diagnostic > Database Usage > General Information #18189

Open 65Q2M89mBVP53sCK opened 2 years ago

65Q2M89mBVP53sCK commented 2 years ago

After upgrading from 3 to 4, the wrong number of users and websites is displayed in Diagnostics > Database Usage > General Information.

Displayed information General Information

36 Users
51 websites

Real information General Information

39 users
53 websites

Table statistics.piwik_user: SELECT COUNT(*) FROM statistics.piwik_user = 39

Table statistics.piwik_site SELECT COUNT(*) FROM statistics.piwik_site = 53

Any advices how to correct this?

Thanx.

sgiehl commented 2 years ago

Hi @65Q2M89mBVP53sCK The database usage report uses the table metadata to fetch the results. What is the result for SHOW TABLE STATUS LIKE statistics.piwik_user on your database?

65Q2M89mBVP53sCK commented 2 years ago

the whole thing is a little confusing - how can the values differ?

SELECT COUNT(*) FROM statistics.piwik_user 39

SHOW TABLE STATUS LIKE 'piwik_user' 'piwik_user', 'InnoDB', '10', 'Compressed', '36', '227', '8192', '0', '0', '0', NULL, '2021-10-08 17:05:53', '2021-10-15 15:32:24', NULL, 'utf8_general_ci', NULL, 'row_format=COMPRESSED', ''

SELECT COUNT(*) FROM statistics.piwik_site 53

SHOW TABLE STATUS LIKE 'piwik_site' 'piwik_site', 'InnoDB', '10', 'Compressed', '51', '160', '8192', '0', '0', '0', '102', '2021-10-08 17:05:53', NULL, NULL, 'utf8_general_ci', NULL, 'row_format=COMPRESSED', ''

sgiehl commented 2 years ago

The Rows value for InnoDb tables are actually unreliable (See https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html). I guess the plugin was build when only MYISAM was in use, where those numbers were accurate. We maybe should do a select instead for those cases here: https://github.com/matomo-org/matomo/blob/115527353a9e75e01aa4d263408956ae45403bea/plugins/DBStats/API.php#L48-L52 Should be quite easy to change.

Other possibility would be to display some information that for InnoDb those values are inaccurate.

ping @tsteur

tsteur commented 2 years ago

Thanks for reporting this @65Q2M89mBVP53sCK As a workaround you should be able to see the correct number of users and websites on the "Admin home" page in the top left widget.

@sgiehl sounds good to change this eventually for those two tables to do a count instead. Not too important though for now

65Q2M89mBVP53sCK commented 2 years ago

My Workaround - it seems to help Dump/Export statistics.piwik_user and statistics.piwik_site and re-import afterwards. SHOW TABLE STATUS LIKE 'piwik_user' 'piwik_user', 'InnoDB', '10', 'Compressed', '39', '210', '8192', '0', '0', '0', NULL, '2021-10-21 07:01:53', '2021-10-21 07:01:53', NULL, 'utf8_general_ci', NULL, 'row_format=COMPRESSED', ''

SHOW TABLE STATUS LIKE 'piwik_site' 'piwik_site', 'InnoDB', '10', 'Compressed', '53', '154', '8192', '0', '0', '0', '102', '2021-10-21 07:01:52', '2021-10-21 07:01:52', NULL, 'utf8_general_ci', NULL, 'row_format=COMPRESSED', ''

Only in System Summary the display of users irritates me: 38 users

This is identical on all our (test) environments (same number of users / sites in the tables). Is the anonymous user not counted?

tsteur commented 2 years ago

Yes, we don't count the anonymous user so far.