Query reeking havoc on server

[dfc005]

Hey guys,

Looking for some urgent help if possible, we have one query which is continually causing our mysql server to crash. Any ideas where this query comes from?

SELECT
	userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user.languageid AS saved_languageid,
    IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
    language.phrasegroup_global AS phrasegroup_global,
    language.options AS lang_options,
    language.languagecode AS lang_code,
    language.charset AS lang_charset,
    language.locale AS lang_locale,
    language.imagesoverride AS lang_imagesoverride,
    language.dateoverride AS lang_dateoverride,
    language.timeoverride AS lang_timeoverride,
    language.registereddateoverride AS lang_registereddateoverride,
    language.calformat1override AS lang_calformat1override,
    language.calformat2override AS lang_calformat2override,
    language.logdateoverride AS lang_logdateoverride,
    language.decimalsep AS lang_decimalsep,
    language.thousandsep AS lang_thousandsep
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
WHERE user.userid = 1234

[TigerC10]

Aside from the fact that it's not complete?

WHERE user.userid =

You need something in there.

WHERE user.userid = "$userinfo[userid]"

Or if you don't need to select a single user, but rather multiple, just remove the WHERE all together.

[dfc005]

Sorry, there is a userid in there. I just didn't post it because we're getting hundreds of them.

--------------- Added [DATE]1234595536[/DATE] at [TIME]1234595536[/TIME] ---------------

If anyone knows where or what this query is related too that would help?

[TigerC10]

Google shows several interesting results.


The general consensus is that the database table "usertextfield" does not exist, or is not accessable. Bad forum backups and restores can have this effect.

Alternatively, the MySQL user in your config file does not have the appropriate read/write privilages to the table. This is generally an error with the users table in MySQL's settings which can result as either a bad move from one server to the other, or a bad sector on the server's hard drive.

[Dismounted]

Is there any error, or does it just take too long to complete?

[Marco van Herwaarden]

How do you know this query is crashing the server?
Is there any error message?

[dfc005]

Hey guys, the query was just taking too long and backing up. Thus, was bringing the server to its knees. Talked to our server guy and he stopped the server, did an update and restarted. Been no issues since then so think it was simply a server issue.

Thanks for your help!

[Marco van Herwaarden]

I still doubt that it was this query. More likely there was an update query running before this SELECT query that locked tables and this caused the select query to wait.

[dfc005]

Most likely, but as I said, a restart and update to the server and we've had no issues since then. So it was a server issue rather than any queries.