[mfyvie]

Would you like something that will automatically REMOVE the avatar for those users who already have the same avatar as others?

The following two queries will do this. The first one will report how many duplicates you have, the second will delete the avatars.

WARNING: The second query will actually change your database. I've tested it on my system and am confident that it works. However, if you run this query on a live system and it breaks it, and you didn't back it up - don't come running to me about it!

First query:

select avatarid,count(1) from user
where avatarid>0
group by avatarid
having count(1) > 1
order by 2 desc

This will simply report how many cases of duplication you have. Once you can see the extent of the problem you can run this:

Second query:

update user u1, user u2
set u1.avatarid=0
where u1.avatarid=u2.avatarid
and u1.userid!=u2.userid
and u1.userid>u2.userid
and u1.avatarid>0
and u2.avatarid>0

You can then re-run the first query to confirm that it worked - if it did, you'll get 0 rows returned.

Note that these queries, as well as the one I posted a few days back will work even if you don't have this mod installed (might be a bit slow due to lack of an extra index though).

If there are two users using the same avatar it won't delete both of their avatars - the lowest userID (usually the oldest member) gets to keep theirs, only the "new" guy will lose theirs.

Once you've used this to eliminate all the duplicates you can be sure that no more will happen when you are running this mod.

The only side effect is the users might wonder where their avatar went...

By the way - in case anyone thinks I am any good at making or changing these queries - I'm not. I gave it to some database gurus to do. They had to scratch their heads a bit before coming up with the final solution. They said it wasn't so simple to figure out.