PDA

View Full Version : MySQL hang


Puppy
28/07/2015, 10:36 PM
Hello. I am currently working on a rather large, rather complicated gamemode using many MySQL queries throughout it's operation. I have noticed that one query seems to hang the whole server, or is just VERY large of a return but I really don't think it is.

It's been like around 5-10 minutes since I typed the /oadmins command which launches the following query:


mysql_function_query(sqlConnection, "SELECT * FROM `players` ORDER BY `players`.`AdminLevel` ASC", false, "OnAdminRoster", "i", playerid);


It hangs the whole server's MySQL connection, even though it's a completely valid query and doesn't return a ridiculous amount of rows either. What may be wrong? I am using the r33 BlueG MySQL plugin by the way if it matters at all. MySQL isn't responding to any queries right now, and it seems to have happened right after I typed /oadmins, I am sure a simple server restart will help but I am sure if I type the command again the same result is bound to happen.

Evocator
28/07/2015, 10:42 PM
Its a big query to run, it will have to go through all your rows to list them. So basically, you can limit it to 50 (i doubt you have more than 50 admins, do you?). Also it should be desc as you are showing the admins from the highest level to the lowest, or not?

SELECT * FROM `players` ORDER BY `AdminLevel` DESC LIMIT 50;

IstuntmanI
28/07/2015, 11:12 PM
I guess that you want to list all admins. You have to use this query:
mysql_function_query(sqlConnection, "SELECT * FROM `players` WHERE `players`.`AdminLevel` > 0 ORDER BY `players`.`AdminLevel` ASC", false, "OnAdminRoster", "i", playerid);
Also, I recommend you to select only the columns you need (replace that "*" with the needed columns, for example `Key`, `Name`, `AdminLevel`).

Puppy
28/07/2015, 11:17 PM
Maybe that's the problem, but to be honestly fair I had that and must've accidentally left it out when I copied it from phpMyAdmin(I got an error when I wrote out the query by hand, I don't order rows often).

I'll try it out and let you guys know.

Puppy
29/07/2015, 07:20 PM
Okay, this is getting rather frusturating.

Now when I use this query;

SELECT `Username`, `AdminLevel`, `LastLogin`, `SeniorMod` FROM `players` WHERE `players`.`AdminLevel` > 0 ORDER BY `players`.`AdminLevel` ASC


It seems to hang the actual server(not sure about the MySQL server). After typing this command, I can type commands and text for like a few seconds before the game just freezes, and when I disconnect I am still shown on the server and don't get actually disconnected.

Puppy
29/07/2015, 08:57 PM
The same query ends with the same result, I am not sure what's wrong here, it breaks and hangs the whole server!

SELECT * FROM `players` WHERE `players`.`AdminLevel` > 0 ORDER BY `players`.`AdminLevel` ASC

I am not sure I have ever encountered a MySQL query that hung the whole server and not just the MySQL server and or plugin.