@65Q2M89mBVP53sCK opened this Issue on October 20th 2021

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 on October 20th 2021 Member

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 on October 20th 2021

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 on October 20th 2021 Member

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 on October 20th 2021 Member

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 on October 21st 2021

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 on October 22nd 2021 Member

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

Powered by GitHub Issue Mirror