User count showing as 0 in dashboard after upgrade to 3.8.5

[mkerala]

Just updated my site to 3.8.5. After upgrade I am seeing user count as '0'. However users getting listed when checking users section.

I have attached the screenshot.

 

[mkerala]

Update: Now users are showing 3. This means counter got reset after the update. I had over 10K users before upgrade.

 

[mkerala]

Update: Now its showing '0' users again. So I tested registering a test user and it showed '1' now.

 

[Ricardo]

Hey @mkerala2

Please send me access to inbox@chevereto.com to debug it properly.

Thanks.

 

[mkerala]

Hey @mkerala2

Please send me access to inbox@chevereto.com to debug it properly.

Thanks.

Which access you need? FTP or Chevereto admin?

 

[mkerala]

Details sent.

 

[Rodolfo]

Fixed.



Problem here was concurrency. When the stats table gets populated, the system is intended to be on maintenance mode but some queries being delayed are still hitting the database server. This delay or lag causes an overlap in the stats statements. I've improved the stat queries so anyone having this issue will get the stats back to normal by running these:

TRUNCATE TABLE `%table_prefix%stats`;

INSERT INTO `%table_prefix%stats` (stat_id, stat_date_gmt, stat_type) VALUES ("1", NULL, "total") ON DUPLICATE KEY UPDATE stat_type=stat_type;

UPDATE `%table_prefix%stats` SET
stat_images = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%images`),
stat_albums = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%albums`),
stat_users = (SELECT IFNULL(COUNT(*),0) FROM `%table_prefix%users`),
stat_image_views = (SELECT IFNULL(SUM(image_views),0) FROM `%table_prefix%images`),
stat_disk_used = (SELECT IFNULL(SUM(image_size) + SUM(image_thumb_size) + SUM(image_medium_size),0) FROM `%table_prefix%images`)
WHERE stat_type = "total";

INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_images, stat_image_views, stat_disk_used)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_images, sb.stat_image_views, sb.stat_disk_used
FROM (SELECT "date" AS stat_type, DATE(image_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_images, SUM(image_views) AS stat_image_views, SUM(image_size + image_thumb_size + image_medium_size) AS stat_disk_used FROM `%table_prefix%images` GROUP BY DATE(image_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_images = sb.stat_images;

INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_users)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_users
FROM (SELECT "date" AS stat_type, DATE(user_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_users FROM `%table_prefix%users` GROUP BY DATE(user_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_users = sb.stat_users;

INSERT INTO `%table_prefix%stats` (stat_type, stat_date_gmt, stat_albums)
SELECT sb.stat_type, sb.stat_date_gmt, sb.stat_albums
FROM (SELECT "date" AS stat_type, DATE(album_date_gmt) AS stat_date_gmt, COUNT(*) AS stat_albums FROM `%table_prefix%albums` GROUP BY DATE(album_date_gmt)) AS sb
ON DUPLICATE KEY UPDATE stat_albums = sb.stat_albums;

UPDATE `%table_prefix%users` SET user_content_views = COALESCE((SELECT SUM(image_views) FROM `%table_prefix%images` WHERE image_user_id = user_id GROUP BY user_id), "0");

Note: You need to change %table_prefix% with the table prefix that matches your database.

 

[mkerala]

Thanks. I also like to mention image count also got updated after you run it.

Btw I had 10K users and now its 5K. Is it the new delete unconfirmed users feature in play?

 

[Rodolfo]