Duplicate entries in site_user_list_table
vaniavv - July 4, 2007 - 23:36
| Project: | Site User List |
| Version: | 5.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
Hi there,
I rebuilt the site_user_list table today and got a bunch of duplicate entries. I did a bit of debugging and the query producing the problem is:
SELECT DISTINCT u.uid as `@uid`,
t_14.value as `@exec_name`,
t_28.value as `@exec_region`
FROM users as u
LEFT OUTER JOIN profile_values as t_14 on (u.uid = t_14.uid and t_14.fid = 14)
LEFT OUTER JOIN profile_values as t_28 on (u.uid = t_28.uid and t_28.fid = 28)
INNER JOIN users_roles as sul_ur ON (sul_ur.uid = u.uid and sul_ur.rid in (6))
WHERE (u.uid != 0) AND (u.status = 1)I tried executing this manually in MySql and sure enough the duplicate rows were there. Exec_name and exec_region are the two fields I wish to select. I have no duplicate entries in the users table, and you would think the DISTINCT would ensure there are unique ids returned yet this is the output:
@uid @exec_name @exec_region
1 Michelle Waikato
7 Sarah Lower North Island
4 John Smith Waikato
17 John Doe Auckland
17 Auckland
17 John Doe
17
38 James Bond AucklandAs you can see id 17 is present 4 times... I haven't changed anything in the original query. I run MySql 4.0.24.
Any ideas why this won't work?
#1
This is normal behaviour when MYSQL finds more than one row that matches its criteria - it returns every possible combination of the rows. (For example if there are two tables being joined, and the query matches rows A and B in table 1 and C and D from table 2, it will return A|C, A|D, B|C and B|D.) From the combinations here (''|'', John Doe|'', ''|Auckland, John Doe|Auckland) it looks like the query is bringing back blank rows from each profile_values instance as well as the populated values.
I've been experiencing this as well, and sure enough, there are duplicate blank entries in my profile_values table for some fields for some users:
mysql> SELECT * FROM profile_values WHERE uid = 248;+------+------+-------+
| fid | uid | value |
+------+------+-------+
| 1 | 248 | |
| 2 | 248 | |
| 1 | 248 | M |
| 2 | 248 | Clyne |
+------+------+-------+
This only appears to be the case for the first 250 or so users on the site - which I believe equates to the users that were initially imported from the old site using user_import. This does appear to be a known issue with using user_import and profile together (#168635: Record duplication in profile_values table) - I don't know if it ever got resolved.
For now, I believe you can safely address this issue by going to MYSQL and just removing the blank rows:
DELETE FROM profile_values WHERE value = '';This should not impact the profile system negatively even if the profile entry for a user is blank - any join queries for missing fields will still bring back empty values.
Hope that helps
--Andy
#2
FWIW, the 'DISTINCT' clause applies to every field in the query, not just the uid. So while it wouldn't bring back A|B A|B, it will happily return A|B A|C because it's two distinct combinations.