...

View Full Version : Mysql timing out issue



sradha
05-21-2011, 09:00 PM
Hi,

I have query like,


SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id WHERE p.program_subscription_program_id =298 AND u.user_company_id =1 UNION SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id LEFT JOIN user_other_company as lu ON u.user_id=lu.user_id WHERE p.program_subscription_program_id =298 AND ( u.user_company_id in (74,97,136,106,108,119,75,76,77,155) OR lu.user_other_company_id in (74,97,136,106,108,119,75,76,77,155)) ORDER BY CASE WHEN user_login_date != '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END ASC LIMIT 0, 5

This query was generating an error -Error Number: 1054.Unknown column 'user_login_date' in 'order clause'. So I modified the query as,


SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id WHERE p.program_subscription_program_id =298 AND u.user_company_id =1 UNION SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id LEFT JOIN user_other_company as lu ON u.user_id=lu.user_id WHERE p.program_subscription_program_id =298 AND ( u.user_company_id in (74,97,136,106,108,119,75,76,77,155) OR lu.user_other_company_id in (74,97,136,106,108,119,75,76,77,155)) ORDER BY CASE WHEN user_last_login != '0000-00-00 00:00:00' AND user_last_login = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END ASC LIMIT 0, 5

Now I am getting an error - #2006 -> MySQL server has gone away .What might be the reason for this error. If the above modified query has no results, then a message "no rows found" will be shown up.If any result is returned then mysql time out error is being shown.Can anyone help me to figure out the issue.
When I referred certain tutorials they were telling to chsnge the settings of mysql so as to overcome the timing out problem. Is there any other way to overcome the issue?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum