Multiple Line Display for Imported Users

Hi! What happens if you rebuild the query/view/table? That is, go to the Site User List configuration screen and click on the link to rebuild? Does that solve the problem?

Ricky

Do you have any kind of direct access to the database? If you are using a view or a table, could you look at that table (`site_user_list_table` or `site_user_list_view`) and see if the duplication exists there? If so, could you check the `users` table and see if there is duplication there?

Thanks,
Ricky

On admin/settings/site_user_list, do you "Retrieve data" from a query, view, or table?

Hi! Under the "result" section of that page, there is an option to "Display the SQL Query". Could you check that option, as well as the "log rebuild actions" option near the top, visit the site user list, and paste in the query that's being run, as well as the entry into the log file for the rebuilt query?

Thanks,
Ricky

Hey! I'll keep doing that...

SELECT DISTINCT `@uid`, `@profile_datainici`, `@profile_country`, `@name`, `@mail` FROM {site_user_list_table} as cd ORDER BY `@uid` ASC

This one is my query. I changed to table, so let me see it...
The rows with the info is duplicated in site_user_list_table table. As I said, the users I created by hand are NOT duplicated. Well, as I "only" need to import 170 users, I could do it by hand, but I want to know why this is happening and try to solve the problem!

I now changed the settings to subselect query and the result is still the same. And the table was dropped from the DB. The query, in that case, is:

SELECT DISTINCT `@uid`, `@profile_datainici`, `@profile_country`, `@name`, `@mail` FROM (SELECT DISTINCT u.uid as `@uid`, t_5.value as `@profile_datainici`, t_8.value as `@profile_country`, u.name as `@name`, u.mail as `@mail` FROM {users} as u LEFT OUTER JOIN {profile_values} as t_5 on (u.uid = t_5.uid and t_5.fid = 5) LEFT OUTER JOIN {profile_values} as t_8 on (u.uid = t_8.uid and t_8.fid = 8) WHERE (u.uid != 0) AND (u.status = 1)) as cd ORDER BY `@uid` ASC

My users table is NOT duplicated, even for the users I created with node_import.
The table profile_values seems to be ok. No duplicated uid or fields.

What else?
Mmmh... if the users table is not duplicated, and also the profile_values seems to be ok... one may think the problem is with Site User List module, because is the one that is creating the site_user_list_table or the subselect query... but not, because not ALL users are being duplicated... just the ones that had been imported with User_import... so, User_import is (still) the bad guy! ;)

Ok, do you have direct access to your database (your comments imply that you do)?

If so, can you run the following queries and send me the results?

SELECT DISTINCT u.uid, t_5.value as 'datainici'
FROM users as u
     LEFT OUTER JOIN profile_values as t_5 on (u.uid = t_5.uid and t_5.fid = 5);

SELECT DISTINCT u.uid, t_5.value as 'datainici', t_8.value as 'country'
FROM users as u
     LEFT OUTER JOIN profile_values as t_5 on (u.uid = t_5.uid and t_5.fid = 5)
     LEFT OUTER JOIN profile_values as t_8 on (u.uid = t_8.uid and t_8.fid = 8);

Thanks!

Ricky

Blarg! Sorry, there was a typo there.

Try this:

SELECT DISTINCT u.uid, t_5.value as `datainici`
FROM users as u
     LEFT OUTER JOIN profile_values as t_5 on (u.uid = t_5.uid and t_5.fid = 5);

SELECT DISTINCT u.uid, t_5.value as `datainici`, t_8.value as `country`
FROM users as u
     LEFT OUTER JOIN profile_values as t_5 on (u.uid = t_5.uid and t_5.fid = 5)
     LEFT OUTER JOIN profile_values as t_8 on (u.uid = t_8.uid and t_8.fid = 8);

The first one, lists all the uid, with no duplicates (but as we are adding Distinct... that's normal..)
The second one looks bad.

Something like:

0 Spain
1 UK
2 UK

(and then, the duplicates starts, because I just have these 3 users before using user_import).
3
3 UK
4
4 France
...
...

How does it look??

Ups... posted twice...

Hm. Is there anything in the 'datainici' column?

What happens with the following query?

SELECT DISTINCT u.uid, t_8.value as `country`
FROM users as u
     LEFT OUTER JOIN profile_values as t_8 on (u.uid = t_8.uid and t_8.fid = 8);

If this returns weird results, then also run the following query, paying special attention to the number of rows reported to be returned:

SELECT *
FROM profile_values
WHERE uid = 3
  AND fid = 8;

(Or substitute some other UID that actually exists.)

Thanks,
Ricky

First one looks like:

420	
420	Belgium
421	
421	UK
422	
422	Belgium

And the second one, just prints

fid uid value
8 548 
8 548 Spain

No, datainici was suposed to import a date field, but as my input format was "wrong", it's empty. As I'm still testing, I forget to re-import the users with a compatible date, so now it's empty.

It looks like the Country field is twice for the imported users: Once good, once empty.

SELECT *
FROM profile_values
WHERE uid = 548 order by fid ASC

returns

fid	uid	value
1	548	
3	548	Thomas
4	548	Thü Hürlimann
6	548	
7	548	137
8	548	
8	548	Switzerland
9	548	25/10/07

More info:

SELECT * FROM profile_fields p where fid=8
returns

fid	title	name	explanation	category	page	type	weight	required	register	visibility	autocomplete	options
8	Country	profile_country		Personal information	People who live in	selection	0	0	1	3	0	Abkhazia  Afghanistan  Akrotiri  Aland  Albania AND MORE COUNTRIES..

The country field type is "list selection" (not just a single-line textfield).
I'll keep looking at that tomorrow...
Thanks!

So the issue is that there is an extra row with the blank (null? empty string?) value in it.

Is the 'country' field supposed to be a multi-select field — that is, should people be able to select multiple values? (Is it a select field in the first place, or is it a text field?)

Try the following queries:

SELECT count (*)
FROM profile_fields
WHERE fid = 8
  AND value is null;

SELECT count (*)
FROM profile_fields
WHERE fid = 8
  AND value = '';

(This is to determine if the missing value is null or the empty string).

Then, back up your database and try the following:

WARNING: this will change your database; I don't know what the results will be; make a backup first!

DELETE FROM profile_fields
WHERE fid = 8
  AND value = '';

(If in the previous round of queries, you got a count for the is null variant, then that last line should be AND value is null)

See if that solves the problem.

(Note: if you have other fields that have the same problem, you many need to run the query changing fid as appropriate.)

HTH,
Ricky

Solved ;)
But this doesn't fix the bug...
My country field is a select field, with NO multiple values.
Your queries were a bit wrong (profile_fields should be profile_values).
My duplicates were an empty string, so I used

DELETE FROM profile_values
WHERE fid = 8
  AND value = '';

and now my Site User List works great, no duplicates at all.

It's quite easy to fix that, but we should try to find the bug (and solve it) or, at least, write a big note in the project page (in the User_import project page)..

Thanks, Ricky.

Hi! Sorry about the bad queries -- I'm glad you caught the error.

Ricky

For anyone having issues with duplicate entries in the profile_values table for users when using user_import:

I ran some tests and it looks like the issue arises when hook_user is called by the profile module when the user is saved. I don't think it's passed the profile values in the way it expects and so it inserts a blank record for that user, along with the one inserted by user_import.

To solve this, I (temporarily) commented out the case 'insert': line in profile_user in:

/modules/profile/profile.module line 177

<?php
/**
 * Implementation of hook_user().
 */
function profile_user($type, &$edit, &$user, $category = NULL) {
  switch ($type) {
    case 'load':
      return profile_load_profile($user);
    case 'register':
      return profile_form_profile($edit, $user, $category, TRUE);
    case 'update':
      return profile_save_profile($edit, $user, $category);
    case 'insert':
      //return profile_save_profile($edit, $user, $category, TRUE); //comment out this line!
    case 'view':
      return profile_view_profile($user);
    case 'form':
      return profile_form_profile($edit, $user, $category);
    case 'validate':
      return profile_validate_profile($edit, $category);
    case 'categories':
      return profile_categories();
    case 'delete':
      db_query('DELETE FROM {profile_values} WHERE uid = %d', $user->uid);
  }
}
?>

Remember to uncomment the line when you've finished importing or else the profile module will not work properly

I hope it works for you too.

AMAZING!
dude you just saved me hours of work, as I have to import hundreds of users and the views incompatibility was creating a huge problem... I would have had to manually re-save profiles or write a script to do that.

THANKS!!!!!

I second. Thanks a million. Also, i was wondering if there is a plan in place to permanently fix it.