PDA

View Full Version : SQL down?


pwbv2
09-29-2006, 04:17 AM
It's not working for me.

arenlor
09-29-2006, 04:33 AM
uh how so? show errors etc. http://arenlorcomics.ulmb.com/ that's my site and it's mostly php and mysql run. it's working.

pwbv2
09-29-2006, 06:16 AM
This is what I get when I try to log into phpadmin.

#1203 - User already has more than 'max_user_connections' active connections

Tyler
09-29-2006, 07:39 PM
This is what I get when I try to log into phpadmin.

#1203 - User already has more than 'max_user_connections' active connections
Do you have a script that is connecting but not disconnecting once it is done making a connection to the MySQL server?

UnlimitedMB
09-29-2006, 08:01 PM
You have 5 identical queries running for hours

SELECT `b`.`board_name`, `b`.`board_id`, `t`.`topic_name`, `t`.`topic_id`, MAX(`m`.`message_time`)

They are stuck in "Copying to tmp table" state.

As soon as i kill the connection it starts right back up again, which suggests its freezing every times someone visits your website.

You have an error in your script somewhere.

arenlor
09-29-2006, 08:20 PM
Post your script up here, or PM them to me, I'll see if I can help with the debugging process, save you some time.

Tyler
09-29-2006, 08:37 PM
Make sure you edit out your username & password if you decide to post that portion of your script.

pwbv2
09-29-2006, 09:05 PM
I think this is the portion that's causing the problem.

echo 'Last ten posts';

while ($top_info = $SQL->fetch_assoc($top10)) {

$msg_info = $SQL->fetch_assoc($SQL->query('SELECT COUNT(`m`.`message_id`) AS `messages`, SUBSTRING_INDEX(MAX(CONCAT(`m`.`message_time`, ",", `u`.`user_name`)), ",", -1) AS `poster`, `m`.`message_poster` FROM `messages` `m`, `users` `u` WHERE `m`.`message_topicid` = '.$top_info['topic_id'].' AND `u`.`user_id` = `m`.`message_poster` GROUP BY `m`.`message_topicid` LIMIT 1'));

echo '

'.$top_info['topic_name'].' -- '.$top_info['board_name'].'
'.$msg_info['poster'].'
'.$msg_info['messages'].'
'.make_time($top_info['last_post']).'
';
}



$online_condition = count($allowed_boards) ? ' AND `b`.`board_id` IN ('.implode(', ', $allowed_boards).')' : '';
$top10 = $SQL->query('SELECT `b`.`board_name`, `b`.`board_id`, `t`.`topic_name`, `t`.`topic_id`, MAX(`m`.`message_time`) AS `last_post`
FROM `topics` `t`
LEFT JOIN `boards` `b` ON `b`.`board_id` = `t`.`topic_boardid`
LEFT JOIN `messages` `m` ON `m`.`message_topicid` = `t`.`topic_id`
WHERE `b`.`board_viewlevel` <= '.$_DATA['user']['user_level'].$online_condition.'
GROUP BY `m`.`message_topicid`
ORDER BY `last_post` DESC
LIMIT 10');

pwbv2
09-29-2006, 10:30 PM
Btw can I get a copy of the DB, I wasn't able to back it up before it went down.

arenlor
09-29-2006, 10:48 PM
At quick glance I can tell you that $top_info = $SQL->fetch_assoc($top10) just plain out and out, that is the problem right there, I'm not sure what MAX(`m`.`message_time`) is the MAX part that is, and I can't see what $top_info is but it and $top10 are the same.

UnlimitedMB
09-30-2006, 12:11 AM
Any attempt to access your database was freezing, so i stopped all your queries and run the database repair, it found no errors but your database seems to work now.

I could not check because your site redirects somewhere else.

pwbv2
09-30-2006, 08:19 AM
It went down again, maybe the script wasn't made to work with the version of php/sql this server uses, but thanks for all the help.

UnlimitedMB
09-30-2006, 01:54 PM
The normal operation of mysql is whenever you are running a query your database is "locked" until that query finishes, the problem is your query never finishes, so no other script can access your database.

These queries are also putting a considerable load on our mysql server, if you are unable to fix this problem within 24 hours I will need to cancel your database access.

Please also tell me what script you are trying to use.