MySQL lost connection? - Printable Version

MySQL lost connection? - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL lost connection? (/showthread.php?tid=629496)

MySQL lost connection? - Banditul18 - 27.02.2017

Today check the MySQL log and saw this: http://imgur.com/QIeoZke
What even means? I mean yes, lost conection , but why?

Actual query:

PHP код:



mysql_format(g_SQL, query, sizeof(query),"SELECT * FROM `bans` WHERE (`BanName` = '%e' OR `IP` = '%e') LIMIT 1",APlayerData[playerid][PlayerName],IP); 

    mysql_tquery(g_SQL, query, "BanCheck","is",playerid,IP);

The call back:

PHP код:



forward BanCheck(playerid,IP[]);

public BanCheck(playerid,IP[])

{

    new admin[MAX_PLAYER_NAME], pname[MAX_PLAYER_NAME],Reason[60], string[60],query[164],IP1[17];

    if(cache_num_rows() >= 1) 

    {


        for(new row; row < cache_num_rows(); row++) 

        {

              cache_get_field_content(row, "AdminName", admin, g_SQL, sizeof(admin));

              cache_get_field_content(row, "BanName", pname, g_SQL, sizeof(pname));

              cache_get_field_content(row, "Reason", Reason, g_SQL, sizeof(Reason));

              cache_get_field_content(row, "IP", IP1, g_SQL, sizeof(IP1));

          }

          SendClientMessage(playerid, C_RED,"Esti banat pe acest server!");

        SendClientMessage(playerid, C_RED,"___________________");

        format(string, sizeof(string),"Admin: %s", admin);

        SendClientMessage(playerid, C_RED, string);

        format(string, sizeof(string),"Nume: %s", pname);

        SendClientMessage(playerid, C_RED, string);

        format(string, sizeof(string),"Motiv: %s", Reason);

        SendClientMessage(playerid, C_RED, string);

        SendClientMessage(playerid, C_RED,"___________________");

        SendClientMessage(playerid, C_RED, "Daca crezi ca ai primit ban pe nedrept fa o poza(F8) si posteaza-o pe forum la sectiuena serverului.");

        SendClientMessage(playerid, C_RED, "www.gamelive.ro/forum");

        if((strcmp(pName(playerid), pname, false, MAX_PLAYER_NAME) == 0) && (strcmp(IP1, IP, true, sizeof(IP1)) != 0))

        {

            mysql_format(g_SQL, query, sizeof(query), "INSERT INTO `bans` (`BanName`,`AdminName`,`IP`,`Reason`) VALUES ('%e','AdmBot','%e','Ban Evade')",pName(playerid),IP1);

            mysql_tquery(g_SQL, query, "","");

        }

        else if((strcmp(pName(playerid), pname, false, MAX_PLAYER_NAME) != 0) && (strcmp(IP1, IP, true, sizeof(IP1)) == 0))

        {

            mysql_format(g_SQL, query, sizeof(query), "INSERT INTO `bans` (`BanName`,`AdminName`,`IP`,`Reason`) VALUES ('%e','AdmBot','%e','Ban Evade')",pName(playerid),IP1);

            mysql_tquery(g_SQL, query, "","");

        }

        SetTimerEx("_KickPlayerDelayed", 500, false, "i", playerid);

      }

    return 1;

}

Re: MySQL lost connection? - Yousha - 27.02.2017

Give us some info:

OS
MySQL Plugin version
Server.cfg
MySQL net_read_timeout
MySQL max_allowed_packet
MySQL innodb_buffer_pool_size
Do you have blob data in your database?
Are you using connect_timeout? somewhere

You can collect them by using SHOW VARIABLES; query.

Re: MySQL lost connection? - Banditul18 - 27.02.2017

Quote:

Originally Posted by Yousha

Give us some info:

OS
MySQL Plugin version
Server.cfg
MySQL net_read_timeout
MySQL max_allowed_packet
MySQL innodb_buffer_pool_size
Do you have blob data in your database?
Are you using connect_timeout? somewhere

You can collect them by using SHOW VARIABLES; query.

Centos6 or debian7 ( i don't know exactly , but the MySQL r40+ is not working , its hosted on big hoting provider in my country , they said on final of march will upgrade the os so that r40+ will work)

MySQL plugin r39-6

Server cfg: (rcon and hostname doesn't metter)

PHP код:



gamemode0 Lejiune 1

plugins crashdetect.so mysql.so sscanf.so streamer.so

filterscripts mapeNick Mape clock

weburl 

maxnpc 1

onfoot_rate 40

incar_rate 40

weapon_rate 40

stream_distance 300.0

stream_rate 1000

mapname Romana

language Romana

net_read_timeout 30
max_allowed_packet 16777216
innodb_buffer_pool_size 134217728

Do you have blob data in your database?
I don't know what a "blob data" is, but all data i have in database are INT and FLOAT , only the Username , password ,salt and the ip are VARCHAR not another type of data

Are you using connect_timeout?
Nope, not at all

Re: MySQL lost connection? - Sanady - 27.02.2017

What is "%e" standing in your query?

So I took little search on internet, and look what I found about %e.

Код:

%e prints the number in scientific notation, e.g. 3.2165e+2.

It`s in C language. I guess that you want to load string from your query, so make it %s instead of using %e.

PlayerName is string.
IP is string.

Second thing If I would be you, I would use this kind of query:

PHP код:



"SELECT * FROM `bans` WHERE `IP` = '%s' LIMIT 1"

Re: MySQL lost connection? - PowerPC603 - 27.02.2017

%e is the same as %s (a string), but automatically escaped to prevent SQL injection.
Never use %s where you're loading/saving player-inputted text like a name or so.

Re: MySQL lost connection? - Banditul18 - 27.02.2017

Happend again, but aparently happens only for one playйr, and its verry strange

PHP код:



[00:48:51] [ERROR MYSQL]
[00:48:51] Error id: 2013
[00:48:51] Error message: Lost connection to MySQL server during query
[00:48:51] Query: SELECT * FROM `bans` WHERE (`BanName` = 'TheOne' OR `IP` = '188.24.208.101') LIMIT 1
[00:48:51] Callback: BanCheck
[00:48:51]

Re: MySQL lost connection? - Yousha - 28.02.2017

Hmm

1- Update your MySQL include to its last version and re-compile GM. DO THIS (https://github.com/pBlueG/SA-MP-MySQ...ases/tag/R39-6)
2- Try using mysql_static.so instead of mysql.so and see if error fixed.
3- Give us your bans table schema and length of that IP variable.
4- Are you sure its not server/host problems? like poor network connectivity or heavy network traffic

Re: MySQL lost connection? - Banditul18 - 28.02.2017

Quote:

Originally Posted by Yousha

Hmm

1- Update your MySQL include to its last version and re-compile GM. DO THIS (https://github.com/pBlueG/SA-MP-MySQ...ases/tag/R39-6)
2- Try using mysql_static.so instead of mysql.so and see if error fixed.
3- Give us your bans table schema and length of that IP variable.
4- Are you sure its not server/host problems? like poor network connectivity or heavy network traffic

1. Did that
2. Did that
I will wait to see if this will happen again
4. http://imgur.com/cmRjksO
5.I don't think so, maybe , but don't see any conectivity problems , i mean the ping is normal , no lag and another things are executed fine , but i will wait 1-2 days to see if this error happens again , hope not

Re: MySQL lost connection? - CrossUSAAF - 28.02.2017

Always escape user inputs.

Do you connect to "localhost" or "127.0.0.1"?

Re: MySQL lost connection? - PowerPC603 - 09.03.2017

Does the MySQL server run on the same machine as the SAMP server?
That would be recommended for fast access to your database.

Connecting to a remote MySQL server is usually not done.
It may work, but you'll get higher ping rates, slower data transfers and possible dropouts in connectivity.

Re: MySQL lost connection? - Banditul18 - 11.03.2017

Quote:

Originally Posted by PowerPC603

Does the MySQL server run on the same machine as the SAMP server?
That would be recommended for fast access to your database.

Connecting to a remote MySQL server is usually not done.
It may work, but you'll get higher ping rates, slower data transfers and possible dropouts in connectivity.

As far i know it's runing on same machine , i mean there is no slower data transfer , and the ip of database is same as server...And don't think my host provider put the mysql server on another machine becasue they have some experience in this domain, they are active for 6-7 years now....

Sorry for bump the topic, the problem kinda resolve, there was no more lost coenction or something else about it, so t's fine

Thanks for help guys