[$40] 'Watching' count doesn't tally with actual watch list

[sdgun]

Steps
Log in as sdguntcqa or adgunvfor users or from any user
Go to watching page
Check the number of watching categories and the actual count

'Watching' count doesn't tally with actual watch list
also for some users, it takes some time to update the count after first log in

[jmgasper]

Challenge https://www.topcoder.com/challenges/45c758f7-5f58-4d34-af71-1ed91b7ef3fa has been created for this ticket.

This is an automated message for ghostar via Topcoder X

[jmgasper]

Challenge https://www.topcoder.com/challenges/45c758f7-5f58-4d34-af71-1ed91b7ef3fa has been assigned to obog.

This is an automated message for ghostar via Topcoder X

[atelomycterus]

@sdgun

** User sdguntcqa**
Total count is equal to 5 (3 categories, 2 bookmarks). The user registered for a challenge (https://vanilla.topcoder-dev.com/group/185), bookmarked 2 discussions and unregistered from the challenge. User unregistered in 20 November. Count of Bookmarks is not correct. Count of Bookmarks should be 0.

We've fixed count of bookmarks after unregistering from challenges in 17 December (#299 (comment)).

Using REST API should fix the count of Bookmarks. (add sdguntcqa to the group and remove from the group). Let me know if you need help with that.

https://vanilla.topcoder-dev.com/discussion/1751/baze-my-discussion
https://vanilla.topcoder-dev.com/discussion/1753

** User sdgunvfor **
It looks like a bug. The new column was added and count of watched categories should be calculated after updating User table.
but the value is null for some users including sdgunvfor. I'll fix it.

Updated: I've checked sql query, no errors. I couldn't reproduce it locally.

@jmgasper Please apply PR-#531. Thanks. I'll verify it after deploying. Count of watched categories should be recalculated if the column value is null.

[atelomycterus]

@jmgasper Vanilla hasn't been started after deploying. I don't see any errors in logs:

2021-04-03 20:31:47,354 INFO stopped: apached (exit status 0)
[03-Apr-2021 20:31:47] NOTICE: Terminating ...
[03-Apr-2021 20:31:47] NOTICE: exiting, bye-bye!
2021-04-03 20:31:47,366 INFO stopped: php-fpmd (exit status 0)
2021-04-03 20:31:48,366 INFO waiting for syslogd to die
[SYSLOG] syslog-ng[43]: syslog-ng shutting down; version='3.13.2'
2021-04-03 20:31:48,468 INFO stopped: syslogd (exit status 0)

Please apply PR-#532. I commented a sql query. This query should be run before Vanilla bootstrap. I am not sure but it might cause an issue.

[jmgasper]

@atelomycterus - Deploying now

[jmgasper]

@atelomycterus - The deploy is done and the app is working agin.

[atelomycterus]

@jmgasper Please apply PR-#534. Thanks! Mysql DB (DEV) uses TRADITIONAL SQL mode, which converts all warnings to errors. I was able to reproduce it and got the same error locally. I've updated a sql query.

[jmgasper]

This ticket was not processed for payment. If you would like to process it for payment, please reopen it, add the tcx_FixAccepted label, and then close it again

This is an automated message for ghostar via Topcoder X

[jmgasper]

Challenge https://www.topcoder.com/challenges/ee1ce89c-adb8-41f7-bdbc-067fc2389896 has been created for this ticket.

This is an automated message for ghostar via Topcoder X

[atelomycterus]

@jmgasper I am verifying this issue. The CountWatchedCategories wasn't updated. I noticed you applied PR, deployed, reverted the commit and deployed again.

Vanilla Bootstrap is done once on the first request. I checked Vanilla logs, there were no requests between 2 deploys, so the update sql wasn't executed.

Please let me know if there were some reasons/issues to revert it.

[jmgasper]

@atelomycterus - After deploying the first time (before the revert), the app failed to respond to requests - it just constantly threw 500 errors in dev. I reverted that commit, redeployed and it worked. Not sure why 🤷

[sdgun]

@sdgun

** User sdguntcqa**
Total count is equal to 5 (3 categories, 2 bookmarks). The user registered for a challenge (https://vanilla.topcoder-dev.com/group/185), bookmarked 2 discussions and unregistered from the challenge. User unregistered in 20 November. Count of Bookmarks is not correct. Count of Bookmarks should be 0.

We've fixed count of bookmarks after unregistering from challenges in 17 December (#299 (comment)).

Using REST API should fix the count of Bookmarks. (add sdguntcqa to the group and remove from the group). Let me know if you need help with that.

@atelomycterus Can you please provide me the Rest API details to add/remove this user?

[atelomycterus]

@jmgasper I've updated Debug plugin. Please apply -topcoder-platform/forums-plugins#83. Thanks!

After deploying the first time (before the revert), the app failed to respond to requests - it just constantly threw 500 errors in dev. I reverted that commit, redeployed and it worked. Not sure why

The update query caused the 500 error. TRADITIONAL SQL mode converts all sql warnings to errors. So we get the 500 error when executing the update sql query. It might be due to some sql modes (NO_UNSIGNED_SUBTRACTION and others). It looks like my local DB settings are different from DEV.

The initial value of CountWatchedCategories should be calculated once for users. Need to parse the preferences keys from string to int. CAST and other sql functions are used. Probably some sql functions create warinings during update:

[atelomycterus]

@sdgun

@atelomycterus Can you please provide me the Rest API details to add/remove this user?

I've added sdguntcqa to https://vanilla.topcoder-dev.com/group/185. I let you know when you can verify other users. The issue with an update query exists.

[atelomycterus]

@jmgasper Please apply PR-#542.

On Dev the previous update query completed with the error. I've rewritten it. Now it's working as expected.

@sdgun You can verify this issue.

[jmgasper]

Payment task has been updated: https://www.topcoder.com/challenges/ee1ce89c-adb8-41f7-bdbc-067fc2389896
Payments Complete
Winner: obog
Copilot: ghostar
Challenge ee1ce89c-adb8-41f7-bdbc-067fc2389896 has been paid and closed.

This is an automated message for ghostar via Topcoder X

[sdgun]

Verified with multiple users in Dev, the watching list is showing the counts correct.

[sdgun]

Verified in production with user CustomerUser, the counts tally with the list.