Remove Avatars if file is non-existing in local/filestorage

[Skyrider]

There were over 5.000 broken file avatars within customavatars directory of which I had to remove. But thus far, vBulletin does not support anyway to remove the attached avatars from the users if the file no longer exists on the filesystem (not database). For funsies, I've rebuild the custom avatars, and didn't work at all.

I noticed that kh99 has created a php file here:
https://vborg.vbsupport.ru/showthread.php?t=281878

https://vborg.vbsupport.ru/attachment.php?attachmentid=137958&d=1335042680

But I'm afraid I'm getting a database error with it:

Database error in vBulletin 4.2.3:

Invalid SQL:
DELETE FROM customavatar WHERE userid IN(4165,,4168,4169,4173,4174,4175,4176,4177,4178,4 179,4180,4181,4182,4185,4189,4 190,4192,4193,4195,4196,4198,4199,4201,4203,4204,4 205,4210,4212,4215,4216,4218,4 220,4221,4222,4223,4224,4225,4227,4229,4231,4234,4 235,4236,4238,4241,4242,4243,4 244,4245,4246,4247,4248,4249,4252,4254,4255,4256,4 257,4260,4261,4262,4263,4265,4 266,4268,4269,4270,4271,4272,4273,4275,4276,4278,4 279,4281,4282,4284,4285,4287,4 288,4290,4291,4294,4295,4296,4297,4298,4299,4301,4 302,4304,4306,4307,4309,4310,4 311,4314,4316,4317);

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4168,4169,4173,4174,4175,4176,4177,4178,4179,4180 ,4181,4182,4185,4189,4190,4192 ,' at line 1
Error Number : 1064


and:

PHP Warning: mysqli_query(): (42000/1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4168,4169,4173,4174,4175,4176,4177,4178,4179,4180 ,4181,4182,4185,4189,4190,4192 ,' at line 1 in ..../includes/class_core.php on line 1394

I've already PM'd kh99 regarding this, but I am unsure when he'll visit the forums again or if he would read my pm in regards of the no-pm policy he has. I appreciate all the help I can get :).

[Skyrider]

Anyone? I appreciate all the help I can get.

[squidsk]

Based on the code, it looks like you have entries in the customavatar table that correspond to users that have previously been deleted and those ids are what causing you problems.

If you run the following query it should remove all entries from the customavatar table that do not correspond to existing users.
DELETE FROM customavatar WHERE NOT userid IN (SELECT userid FROM user)

Backup the customavatar table before running the query just in case.

[Skyrider]

Based on the code, it looks like you have entries in the customavatar table that correspond to users that have previously been deleted and those ids are what causing you problems.

If you run the following query it should remove all entries from the customavatar table that do not correspond to existing users.
DELETE FROM customavatar WHERE NOT userid IN (SELECT userid FROM user)

Backup the customavatar table before running the query just in case.
Love, thanks! Using that query and re-running the php file worked like a charm:

Removed 22011 rows from customavatar

[blind-eddie]

Awesome, I will be trying this shortly.