1054 Database Problem

[SlaPPiE]

Suddenly recieved the following error:

Database error in vBulletin 3.0.7:

Invalid SQL: 
			SELECT threadid AS itemid, forumid,
			IF(postuserid = 1, 'self', 'other') AS starter
			FROM thread AS thread
			LEFT JOIN deletionlog AS delthread ON(delthread.primaryid = thread.threadid AND delthread.type = 'thread')
			WHERE threadid IN(10506)
			AND delthread.primaryid IS NULL
			AND thread.open <> 10
			AND thread.visible = 1
			AND thread.postuserid NOT IN (UserName)
		
mysql error: Unknown column 'UserName' in 'where clause'

mysql error number: 1054

Date: Wednesday 20th of April 2005 12:51:19 AM
Script: http://forums.breakthetrend.com/search.php?searchid=40894
Referer: http://forums.breakthetrend.com/search.php?
Username: Braden
IP Address: 12.109.29.160

UserName represents a user at the forum whose actual display name I did not post due to it being questionably inappropriate.

I recieve this message on execution of the search function only, but other members are complaining that they are not able to access the threadlisting page. They can view the forums listing and individual threads, but not the individual forums.

Not too sure where to go from here, but it looks as if some values ended up where they shouldn't have... No coding changes within the last month, as the forum was working fully operational until this suddenly sprung upon me. Any assistance in resolving this matter will be greatly appreciated.

[Dennis Olson]

USERID is a NUMBER, not a NAME.

[SlaPPiE]

USERID is a NUMBER, not a NAME.

I'm quite aware, yes, but please explain the relevance. I replaced someone's actual username that was appearing in the error with the word UserName.

[SlaPPiE]

Also managed to recieve this... same basic problem but with a different delivery.

Database error in vBulletin 3.0.7:

Invalid SQL: 
            SELECT postid AS itemid, thread.forumid,
            IF(postuserid = 1, 'self', 'other') AS starter
            FROM post AS post
            INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
            LEFT JOIN deletionlog AS delpost ON(delpost.primaryid = post.postid AND delpost.type = 'post')
WHERE postid IN(385643, 385404, 385394, 384657, 384550, 384195, 384179, 384110, 383832, 383713, 383696, 382041, 382033, 381496, 381444, 381440, 381435, 381064, 380825, 380212, 380203, 379843, 379763, 379099, 378678, 378667, 378660, 378452, 378256, 378254, 378252, 378249, 378007, 377698, 376991, 376734, 376718, 375779, 375523, 375506, 375174, 375173, 374499, 374493, 374022, 374014, 374005, 373993, 373988, 373986, 373980, 373978, 373965, 373943, 373935, 373933, 373737, 373679, 373666, 373546, 372469, 372283, 372254, 372208, 372197, 372196, 371881, 371877, 371867, 370518, 370511, 369908, 369540, 369250, 369248, 369247, 369174, 369168, 368549, 366918, 366557, 366378, 366106, 366102, 366089, 366066, 366049, 366047, 365426, 365093, 364305, 363326, 362359, 361234, 359908, 358980, 358946, 358244, 358170, 356894, 356671, 356543, 356539, 356297, 355965, 355515, 355500, 355428, 354905, 354879, 354873, 354868, 354127, 352057, 350902, 350611, 350609, 349976, 349959, 349643, 349002, 348996, 348993, 346716, 346515, 346477, 346358, 344928, 343050, 341817, 341815, 340856, 338829, 338597, 338226, 338046, 337048, 336796, 336785, 334059, 333528, 333510, 333331, 333330, 328769, 328725, 327218, 327212, 326367, 324001, 323419, 323411, 323253, 323034, 323010, 321415, 321175, 320948, 320947, 318926, 318299, 317480, 317465, 315479, 312992, 312966, 312400, 312012, 311838, 311312, 311155, 311123, 310712, 310368, 310235, 309914, 309728, 306585, 305887, 304285, 303395, 302737, 301309, 299716, 299615, 299427, 298887, 297010, 296449, 295985, 295966, 295261, 293816, 293791, 293548, 293546, 293544, 293357, 292774, 292378, 292374, 292373, 292343, 291885, 291034, 291005, 290941, 290869, 290693, 290127, 290104, 289459, 289455, 289296, 289263, 289231, 289204, 289163, 288914, 288912, 288294, 288292, 288280, 288258, 288244, 287894, 287375, 286581, 286580, 286570, 286565, 285859, 285051, 284953, 284660, 284429, 281795, 281683, 280819, 280487, 280320, 280286, 280285, 279753, 278134, 278114, 277777, 277772, 277767, 276881, 276299, 275661, 275659, 275602, 275441, 275408, 274676, 274666, 274518, 274248, 274195, 273943, 273934, 273927, 273749, 273573, 273567, 273124, 273080, 272997, 272847, 271223, 269763, 269615, 269582, 269580, 269415, 269287, 269284, 269281, 269280, 269235, 268869, 268399, 268178, 266245, 266239, 266229, 265493, 265459, 265442, 265211, 265208, 264118, 264082, 263893, 263862, 263711, 263707, 263706, 263469, 263461, 261306, 261282, 261267, 261244, 261232, 261216, 261147, 260220, 260214, 260212, 260211, 260196, 260119, 260088, 259712, 259567, 259563, 259553, 259547, 259542, 259536, 259530, 259524, 259503, 258931, 257700, 257683, 257677, 257547, 257500, 257319, 257307, 257306, 256725, 256719, 256400, 256161, 255709, 253763, 253661, 253648, 253642, 253501, 253351, 253337, 249859, 249380, 249283, 249217, 249034, 248528, 248517, 248427, 248410, 248070, 247805, 247801, 247103, 247072, 247049, 246126, 245677, 245674, 243911, 243900, 243724, 243718, 243704, 243694, 243659, 243434, 243286, 243060, 242596, 242594, 242519, 242518, 242422, 235453, 235307, 235303, 234397, 233775, 233767, 232784, 232318, 231898, 231893, 231558, 231020, 231016, 231014, 231012, 231009, 230427, 228786, 227815, 227811, 227808, 227515, 227333, 226708, 226567, 226557, 226546, 226531, 226528, 226522, 226147, 225667, 225494, 225481, 225471, 225460, 225446, 225159, 225156, 225153, 225151, 224987, 224981, 224935, 224886, 224567, 224556, 224539, 224535, 224529, 224518, 224516, 224486, 223901, 223898, 223896, 223892, 223885, 223869, 223839, 223836, 223604, 223502, 223348, 223241, 223225, 223156, 223036, 222915, 222912, 222674, 222327, 222195, 221692, 221687, 221369, 221365, 221350, 221088, 220535, 220465, 220296, 220295, 220167, 219622, 218997, 218990, 218983, 218885, 218654, 218651, 218640, 218638, 218635, 218633, 218632, 218126, 218124, 217600, 217593, 215144, 215141, 215138, 215132, 215129, 215117, 215115, 215113, 215109, 212678, 210857, 210848, 210533, 210501, 209439, 208563, 208201, 208183, 208137, 207989, 207555, 206902, 206897, 206717, 206539, 206528, 206525, 206236, 206233, 206226, 206133, 205867, 205215, 205211, 205203, 204976, 204971, 204955, 204271, 204264, 204253, 204151, 203649, 203639, 203086, 202327, 201598, 201596, 201589, 201574, 201540, 201400, 201397, 201395, 201087, 201083, 201073, 200843, 199792, 199791, 199413, 199406, 199120, 199119, 198696, 198694, 198692, 198688, 198628, 198618, 198615, 198610, 198426, 198153, 198033, 198027, 198025, 198020, 198015, 197997, 197992, 196872, 196538, 196519, 196498, 196491, 196475, 196448, 196443, 196439, 196437, 196436, 196164, 196159, 196077, 196074, 196069, 196066, 195700, 195090, 195089, 195081, 195080, 194862, 194191, 193899, 193716, 193299, 193255, 193252, 193245, 193171, 192819, 192427, 192421, 192417, 192401, 192303, 192148, 192144, 192111, 191505, 190417, 190163, 190161, 190037, 189758, 189715, 189709, 189702, 189693, 189686, 189488, 189120, 189009, 188724, 188722, 188521, 188518, 188273, 188269, 188238, 188215, 188008, 188005, 186903, 185552, 182146, 181945, 181675, 181328, 180540, 180249, 179558, 179001, 176909, 176429, 175252, 175133, 175110, 175109, 174443, 174366, 174043, 174037, 174031, 173350, 173346, 173321, 172592, 172541, 172535, 172343, 172342, 172277, 172275, 171762, 171759, 171609, 171473, 170963, 170884, 170370, 170355, 170349, 169930, 169736, 168696, 168678, 168673, 168110, 167975, 167911, 167694, 167564, 166337, 165961, 165652, 165517, 164968, 164967, 164860, 164679, 164132, 164119, 164109, 164108, 163537, 163533, 163268, 163267, 161968, 161650, 161649, 161606, 161601, 160549, 160217, 159073, 159063, 159058, 158636, 158187, 157567, 157347, 157134, 156762, 156760, 156758, 155946, 155938, 155775, 155703, 155683, 155485, 151096, 151089, 150611, 149360, 149238, 148555, 148246, 147948, 146330, 146301, 146255, 141224, 139927, 139922, 138847, 137103, 136089, 135652, 135645, 135355, 135351, 135108, 135107, 134965, 134638, 134437, 133743, 130492, 129711, 129607, 129368, 128459, 127689, 126055, 126054, 123586, 123574, 123572, 123022, 123018, 122615, 121690, 118279, 117534, 117530, 117528, 117523, 117515, 117503, 116470, 115643, 115450, 115449, 114791, 114750, 112683, 112504, 111296, 109120, 109116, 106991, 106918, 106397, 104775, 104562, 103651, 103299, 103289, 103277, 103274, 103269, 102387, 101889, 101884, 101881, 101875, 100783, 98543, 98542, 98010, 97595, 97594, 96144, 72942, 71063, 60975)
            AND delpost.primaryid IS NULL
            AND thread.open <> 10
            AND thread.visible = 1
            AND post.userid NOT IN (UserName)
            AND thread.postuserid NOT IN (UserName)
        
mysql error: Unknown column 'UserName' in 'where clause'

mysql error number: 1054

Date: Wednesday 20th of April 2005 01:17:33 AM
Script: http://forums.breakthetrend.com/search.php?searchid=40901
Referer: http://forums.breakthetrend.com/member.php?u=97
Username: Braden
IP Address: 12.109.29.160

Once again, UserName replaces an actual member's username.

[Dennis Olson]

AND thread.postuserid NOT IN (UserName)

Should read:

AND thread.postuserid NOT IN (X, Y, Z)

where X, Y and Z are actual userIDs.

Have you installed any hacks?

[SlaPPiE]

Yes, but nothing that SHOULD affect this. Like I said, code modifications have been made, but none recently. This error only started occuring earlier this evening.

[Dennis Olson]

Dunno man. POSTUSERID and username are not compatible.

[Steve Machol]

You have almost certainly placed 'UserName' in a setting designed for userids. One possibility is Tachy Goes to Coventry:

Admin CP -> vBulletin Options -> User Banning Options -> Tachy Goes to Coventry

[SlaPPiE]

Which was leading me to believe something became corrupted. The specified threadid in my first post belonged to a thread that was excessively long. I removed the row in the thread table and proceeded to remove orphaned posts, but was still stuck with the same problem.

[SlaPPiE]

You have almost certainly placed 'UserName' in a setting designed for userids. One possibility is Tachy Goes to Coventry:

Admin CP -> vBulletin Options -> User Banning Options -> Tachy Goes to Coventry

Precisely the problem. I personally didn't put him there, but apparently one of my other admins cannot read the notice of userids only. Thank you for pointing out this error and making me feel like a complete moron, but hey, that's all part of your job.

Once again, thanks.

[calorie]

// note: single quotes around badness
$argh_bad_names = "'f**k','s**t','c**t'";

$query = "SELECT threadid AS itemid, forumid, ".
"IF(postuserid = 1, 'self', 'other') AS starter ".
"FROM thread AS thread ".
"LEFT JOIN deletionlog AS delthread ON(delthread.primaryid = thread.threadid AND delthread.type = 'thread') ".
"WHERE threadid IN(10506) ".
"AND delthread.primaryid IS NULL ".
"AND thread.open <> 10 ".
"AND thread.visible = 1 ".
"AND thread.postusername NOT IN ($argh_bad_names)";

mysql_query($query,$connect);

... or not ...