# Mysterious entries in topic\_users table
**URL:** https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080
**Category:** Bug
**Created:** [May 11, 2016, 8:17pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080 "2016-05-11T20:17:39Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 11, 2016, 8:17pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/1 "2016-05-11T20:17:39Z")
I’m running a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query to see which users read a specific post:
```plaintext
-- [params]
-- integer :topic = 0
SELECT *, total_msecs_viewed / (1000 * 60) as mins_read
FROM topic_users
WHERE topic_id = :topic
ORDER BY total_msecs_viewed DESC
```
The results look plausible, except for a mysterious nonexisting user:
This is the result for topic `200`. The user id `200` is not used in my installation (I have fewer users).
The same thing is happening for all other topics, there is always an entry for a “user” whose id matches the topic id.
Am I overlooking a very obvious bug in my SQL code? Is this entry really there? Should it be?
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 11, 2016, 8:22pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/2 "2016-05-11T20:22:39Z")
If you run
```plaintext
SELECT *
FROM users
WHERE id = 200
```
you might be able to spot the problem.
My guess would be a NULL name field
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 11, 2016, 8:24pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/3 "2016-05-11T20:24:12Z")
This returns 0 results, as expected.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 11, 2016, 8:27pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/4 "2016-05-11T20:27:18Z")
> [@fefrei](#):
>
> as expected.
So you know it’s a deleted account?
In that case you might want a more complex query that takes that into it.
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 11, 2016, 9:10pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/5 "2016-05-11T21:10:21Z")
No, the account never existed – I only have about 150 users, so the id has never been used.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 11, 2016, 9:26pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/6 "2016-05-11T21:26:59Z")
Hmmm, is that the MathJax topic id?
```plaintext
SELECT *
FROM topics
WHERE id = 200
```
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 11, 2016, 9:43pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/7 "2016-05-11T21:43:04Z")
> [@fefrei](#):
>
> This is the result for topic 200. The user id 200 is not used in my installation (I have fewer users).The same thing is happening for all other topics, there is always an entry for a “user” whose id matches the topic id.
Yes, it is. As I said above, this happens in multiple (or all?) topics. Topic 201 has a mysterious user 201, and so on.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 11, 2016, 11:07pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/8 "2016-05-11T23:07:01Z")
> [@fefrei](#):
>
> Yes, it is. As I said above
 So you did.
I was getting some “invalid int” errors until I changed “:topic” to “:topicid” so I at first thought it might involve how “params” works.
But even after removing that and hard-coding in the ids, I still got the mysterious result.
I notice it doesn’t seem to happen when the id is 55 or below, but always for ids 56 or greater.
55 seems like an odd value for a switch to be happening.
Are your results consistent with \<=55 OK, \>=56 Not ?
EDIT
Hmmm, 55 is my highest users id + 2
(yes, I have that many test accounts on my localhost site)
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 12, 2016, 6:00am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/9 "2016-05-12T06:00:39Z")
> [@Mittineague](#):
>
> Hmmm, 55 is my highest users id + 2
With the possibility of deleting users, 55 could plausibly be the highest user id.
Are you sure this isn’t happening for lower user IDs? The entries would look quite normal…
I tried to look at all entries where the user id equals the topic id:
```plaintext
SELECT *
FROM topic_users
WHERE user_id != topic_id
ORDER BY user_id
```
Even for low ids, the results look strange: Almost all have `highest_seen_post_number = NULL`, for example. In other rows in this table, this column almost always contains a plausible date.
I’m starting to believe that this is a #bug: It looks like something is inserting rows here and mistakes the topic id for the user id?
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 12, 2016, 6:52am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/10 "2016-05-12T06:52:53Z")
I’ve been looking at topic\_users a bit lately.
I was confused as to why there would be these two fields
```plaintext
last_read_post_number integer null
highest_seen_post_number integer null
```
I mean, if I read it, I saw it, no?
Then I found this in app/models/topic\_user.rb
```plaintext
# In case anyone seens "highest_seen_post_number" and gets confused, like I do.
# highest_seen_post_number represents the highest_post_number of the topic when
# the user visited it. It may be out of alignment with last_read, meaning
# ... user visited the topic but did not read the posts
```
In other words, if a topic has 100 posts, but I only read the first 50, the two will not be the same.
I just ran
```plaintext
SELECT *
FROM topic_users
WHERE last_read_post_number IS NULL
OR highest_seen_post_number IS NULL
```
In _all_ results _both_ read and seen were NULL
and I noticed in _all_ results total\_msecs\_viewed was zero.
In some, it was not the topic id that was used, but the post id
My guess, without having looked more closely into the code yet, is that there is a “divide by 0” error happening somewhere.
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 12, 2016, 7:13am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/11 "2016-05-12T07:13:01Z")
That’s some additional weirdness – thanks for the research!
But even a division through zero doesn’t really explain the nonexistent
user ids… ☹
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 12, 2016, 7:29am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/12 "2016-05-12T07:29:48Z")
> [@fefrei](#):
>
> But even a division through zero doesn’t really explain the nonexistent user ids
I’m not sure if it’s more to do with Discourse or [Data Explorer](https://meta.discourse.org/t/32566?silent=true) or both.
I’ll try running some queries in the postgres console tomorrow (I need to eat and get some sleep) to see if the results are similar.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 13, 2016, 1:58am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/13 "2016-05-13T01:58:46Z")
An update after an interesting morning.
I was wrong about two things
> [@Mittineague](#):
>
> In some, it was not the topic id that was used, but the post id
This was wrong, I was misled by a seemingly unrelated weirdness. (that sent me off on a tangent for a good while)
What I had assumed to be post ids are in fact topic ids.
For whatever unknown reason both my topics and posts tables “skipped” ids.
In the topics table ids are serial from 1 - 166, then skipped to 1796, 1797, then skipped to 1830 and have been sequential serial values since.
Likewise the posts table ids are 1 - 188, skip to 2708, skip to 2747 then sequential since
I don’t know what happened, but I was exploring the differences between
“Global Notice” vs. “Banner Topic” vs. “Global Pinned Topic” vs. “Pinned Topic” so maybe that has something to do with it.
```
Topics
166 Pinned Stats query ^^^^^
2016-05-04 07:12
1796 Banner Poll results
2016-05-06 01:40
1797 Limit Banner Use?
2016-05-06 07:15
1830 Topic for moved PM testing vvvvvvvv
2016-05-06 20:48
Posts
888 Pinned Stats query (3) ^^^^^
2016-05-04 08:42
2708 Banner testing - take two (13) 7d unbannered may 6 unpinned
2016-05-05 06:34
2747 Going off the grid for a while (35) vvvvvvvv
2016-05-05 09:19
```
\* created at datetimes
Anyway, I was also wrong about
> [@Mittineague](#):
>
> I notice it doesn’t seem to happen when the id is 55 or below, but always for ids 56 or greater.
Topic ids were actually being used as user ids, but because there were actual users with those id values it didn’t look obviously wrong at first.
* * *
> [@Mittineague](#):
>
> I’ll try running some queries in the postgres console
The incorrect user ids are in the Postgres table and not a result of how [Data Explorer](https://meta.discourse.org/t/32566?silent=true) works with the data
```plaintext
user_id | topic_id | posted | last_read_post_number | highest_seen_post_number |
---------+----------+--------+-----------------------+--------------------------+
56 | 56 | f | | |
last_visited_at | first_visited_at | notification_level |
----------------------------+----------------------------+--------------------+
2016-05-12 17:47:06.711945 | 2016-03-14 19:18:55.588447 | 1 |
notifications_changed_at | notifications_reason_id | total_msecs_viewed |
--------------------------+-------------------------+--------------------+
| | 0 |
cleared_pinned_at | id | last_emailed_post_number | liked | bookmarked
-------------------+-----+--------------------------+-------+------------
| 434 | | f | f
```
I don’t know how or why the topic ids could be getting used as user ids, but my feeling is it has to do with “no owner” posts. eg.
system = user id -1
“small actions”
anonymized accounts
I was alarmed when I saw that a March “post closed Flagged” message to FluffGuy1 with topic id 52
had RoyalGuy1 user id 52 as a topic\_user.
But luckily RoyalGuy1 has no indication of it’s existence displayed anywhere, he is not included as a user in the message, and going to the URL gives “no permission”.
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 13, 2016, 9:14am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/14 "2016-05-13T09:14:12Z")
Awesome work, thanks!
So this definitely looks like a bug, I’ll move the topic.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 13, 2016, 8:02pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/15 "2016-05-13T20:02:07Z")
Yes, I think it’s a bug somewhere.
I’ve found a differentiation area
```plaintext
SELECT COUNT(dtid)
FROM (SELECT DISTINCT id FROM topics) AS dtid
count = 182
SELECT COUNT(dtuid)
FROM (SELECT DISTINCT topic_id FROM topic_users) AS dtuid
count = 182
SELECT COUNT(dtuntid)
FROM (SELECT DISTINCT topic_id FROM topic_users WHERE last_read_post_number IS NULL) AS dtuntid
count = 170
SELECT COUNT(dtuptid)
FROM (SELECT DISTINCT topic_id FROM topic_users WHERE user_id = topic_id) AS dtuptid
count = 170
```
The difference between the 182 / 170 / 12 ?
My install came with 12 “seeded” topics
```
id title user
1 About the Lounge category system
2 About the Meta category system
3 About the Staff category system
4 Terms of Service system
5 FAQ/Guidelines system
6 Privacy Policy system
7 Assets for the site design system
8 Welcome to Discourse system
9 Welcome to the Lounge system
10 READ ME FIRST: Admin Quick Start Guide system
11 This is a test topic eviltrout
12 Welcome to Discourse! system
```
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 14, 2016, 6:54am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/16 "2016-05-14T06:54:46Z")
I had AdminGuy1 create a new topic in the meta category
I then went directly to the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) and ran
`SELECT * FROM topic_users ORDER BY topic_id DESC LIMIT 10`
members that were Watching / Tracking the meta category were there, and the last id had a user\_id = topic\_id
I then had Mittineague create a new category - Temporary
I did **not** visit the new category’s generated About page
Going to [Data Explorer](https://meta.discourse.org/t/32566?silent=true) and running the same query gave the same results
i.e. no new rows had been added to the topic\_users table
I then **viewed** - aka visited - the new category’s About page but did not edit it.
Going to [Data Explorer](https://meta.discourse.org/t/32566?silent=true) and running the same query showed that 2 rows had been added to the topic\_users table.
The first was Mittineague.
The last was another user\_id = topic\_id
This suggested the “when”. and after poring through lines of code I think I may have found the “where”.
app/models/topic\_user.rb lines 128 - 139 are
```plaintext
def track_visit!(topic,user)
topic_id = topic.is_a?(Topic) ? topic.id : topic
user_id = user.is_a?(User) ? user.id : topic
now = DateTime.now
rows = TopicUser.where(topic_id: topic_id, user_id: user_id).update_all(last_visited_at: now)
if rows == 0
TopicUser.create(topic_id: topic_id, user_id: user_id, last_visited_at: now, first_visited_at: now)
else
observe_after_save_callbacks_for topic_id, user_id
end
end
```
I am far from being a Ruby expert, and I do not know all the inter-relationships in Discourse, but in particular line # 130 looks like the probable culprit. i.e.
`user_id = user.is_a?(User) ? user.id : topic`
That is, if I’m reading the code correctly, the line translates roughly to
if user is a User assign user.id to user\_id else assign topic to user\_id
If this is the problem, I don’t know what the best way to deal with it would be.
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [May 14, 2016, 6:59am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/17 "2016-05-14T06:59:20Z")
_Preeeetty_ sure it should be `user` instead of `topic` here.
Awesome detective work 🔍
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [May 14, 2016, 8:06am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/18 "2016-05-14T08:06:54Z")
Just pushed a fix. Not sure how it got there in the first place ☹
[https://github.com/discourse/discourse/commit/6137bb46d31f0bb3ef3a4488c68a17a750ddb84e](https://github.com/discourse/discourse/commit/6137bb46d31f0bb3ef3a4488c68a17a750ddb84e)
---
### Author:  [@gerhard](https://meta.discourse.org/u/gerhard)
#### Post date: [May 14, 2016, 9:23am UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/19 "2016-05-14T09:23:45Z")
Should there be a migration that removes the invalid records from the table?
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [May 14, 2016, 3:25pm UTC](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080/20 "2016-05-14T15:25:42Z")
I was thinking of that too
First thought would be
WHERE topic\_id = user\_id
except there may be cases where an actual member with the same id as a topic should be there
Adding `notification_level = 1` (assigned as the default) and
```plaintext
last_read_post_number IS NULL
highest_seen_post_number IS NULL
```
would only falsely delete members that had set the category as Normal via the category drop-down which seems like that would be very rare, but is possible I guess.
The safest would be
````plaintext
topic_id > MAX(users.id)
```.
but depending on how many members there are could leave quite a few.
````
[Next page](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080.md?page=2)