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

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

Open
65Q2M89mBVP53sCK opened this issue Oct 20, 2021 · 6 comments
Open

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

65Q2M89mBVP53sCK opened this issue Oct 20, 2021 · 6 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. Help wanted Beginner friendly issues or issues where we'd highly appreciate community's help and involvement.
Milestone
Backlog (Help wan...

Comments

@65Q2M89mBVP53sCK
Copy link

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.
@Findus23 Findus23 added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Oct 20, 2021
@sgiehl
Copy link
Member

sgiehl commented Oct 20, 2021

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

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

sgiehl commented Oct 20, 2021 •
edited

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:

matomo/plugins/DBStats/API.php

Lines 48 to 52 in 1155273

$siteTableStatus = $this->metadataProvider->getTableStatus('site');
$userTableStatus = $this->metadataProvider->getTableStatus('user');
$siteCount = $siteTableStatus['Rows'];
$userCount = $userTableStatus['Rows'];

Should be quite easy to change.

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

ping @tsteur

@sgiehl sgiehl added Bug For errors / faults / flaws / inconsistencies etc. Help wanted Beginner friendly issues or issues where we'd highly appreciate community's help and involvement. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Oct 20, 2021
@tsteur
Copy link
Member

tsteur commented Oct 20, 2021

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

@tsteur tsteur added this to the Priority Backlog (Help wanted) milestone Oct 20, 2021
@65Q2M89mBVP53sCK
Copy link
Author

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

tsteur commented Oct 22, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc. Help wanted Beginner friendly issues or issues where we'd highly appreciate community's help and involvement.
Projects
None yet
Milestone
Backlog (Help wanted)
Development

No branches or pull requests
5 participants
@tsteur @mattab @sgiehl @Findus23 @65Q2M89mBVP53sCK