MySQL query crashes my server - Printable Version

MySQL query crashes my server - 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 query crashes my server (/showthread.php?tid=573653)

MySQL query crashes my server - dominik523 - 09.05.2015

Hey there! I have one small problem with my query for saving businesses.
When OnGamemodeInit gets called, it loads all businesses and everything works perfectly. But when OnGamemodeExit gets called, it tries to save the businesses and the server crashes.
Here is my saving function:

pawn Код:

SaveBusinesses()
{
    new query[256];
    for(new i; i < MAX_BIZ; i++)
    {
        if(BizInfo[i][Created] == false)
            continue;
            
        mysql_format(mysql, query, sizeof(query), "UPDATE `businesses` SET `Type`='%d', `Owner`='%e', `Name`='%e', `Locked`='%d', `X`='%.2f' WHERE `ID`='%d'",\
        BizInfo[i][Type], BizInfo[i][Owner], BizInfo[i][Name], BizInfo[i][Locked], BizInfo[i][Position][0], BizInfo[i][ID]);
        mysql_tquery(mysql, query, "", "");
        
        mysql_format(mysql, query, sizeof(query), "UPDATE `businesses` SET `Y`='%.2f', `Z`='%.2f', `Price`='%d' WHERE `ID`='%d'",\
        BizInfo[i][Position][1], BizInfo[i][Position][2], BizInfo[i][Price], BizInfo[i][ID]);
        mysql_tquery(mysql, query, "", "");
    }
    return 1;
}

Am I doing something wrong in those formats?
Oh, and here's the mysql log:

pawn Код:

[13:54:21] [DEBUG] mysql_format - connection: 1, len: 256, format: "UPDATE `businesses` SET `Type`='%d', `Owner`='%e', `Name`='%e', `Locked`='%d', `X`='%.2f' WHERE `ID`='%d'"
[13:54:21] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `businesses` SET `Type`='0', `Owner`='The State', `Name`=", callback: "(null)", format: "(null)"
[13:54:21] [DEBUG] mysql_format - connection: 1, len: 256, format: "UPDATE `businesses` SET `Y`='%.2f', `Z`='%.2f', `Price`='%d' WHERE `ID`='%d'"
[13:54:21] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `businesses` SET `Y`='-1722.94', `Z`='13.54', `Price`='42", callback: "(null)", format: "(null)"
[13:54:21] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[13:54:21] [DEBUG] CMySQLConnection::Disconnect - connection was closed
[13:54:21] [DEBUG] CMySQLQuery::Execute[] - query was successfully executed within 39.945 milliseconds
[13:54:21] [DEBUG] CMySQLQuery::Execute[] - no callback specified, skipping result saving

It looks like database disconnects before the queries are over and I have no idea is it suppose to be like that.
Thank you!

Re: MySQL query crashes my server - Luis- - 09.05.2015

Might be cause you're sending to queries at once, you could use strcat and send one.

Re: MySQL query crashes my server - dominik523 - 09.05.2015

I did the same thing with user saving, multiple queries in the same function, and it worked good.

Re: MySQL query crashes my server - Luis- - 09.05.2015

Hmm, you could try doing the query in phpmyadmin to see if anything out of place. That's what I usually do.

Re: MySQL query crashes my server - dominik523 - 09.05.2015

Okay. I've tried with this and it gave no errors:

pawn Код:

UPDATE `businesses` SET `Type`='0', `Owner`='The State', `Name`='', `Locked`='0', `X`='2292.27' WHERE `ID`='1'

UPDATE `businesses` SET `Y`='-1722.94', `Z`='13.54', `Price`='42000' WHERE `ID`='1'

Re: MySQL query crashes my server - Konstantinos - 09.05.2015

Quote:

Originally Posted by dominik523

I did the same thing with user saving, multiple queries in the same function, and it worked good.

There's a different though. You sent those queries for 1 player only while here you save N (N = MAX_BIZ) businesses with 2 queries EACH so it will take some time.

Re: MySQL query crashes my server - dominik523 - 09.05.2015

I've tried with strcat and it's still crashing.

pawn Код:

format(query, sizeof(query), "UPDATE `businesses` SET `Type`='%d', `Owner`='%e', `Name`='%e', `Locked`='%d', `X`='%.2f', ",\
        BizInfo[i][Type], BizInfo[i][Owner], BizInfo[i][Name], BizInfo[i][Locked], BizInfo[i][Position][0]);
        new string[128];
        format(string, sizeof(string), "`Y`='%.2f', `Z`='%.2f', `Price`='%d' WHERE `ID`='%d'",\
        BizInfo[i][Position][1], BizInfo[i][Position][2], BizInfo[i][Price], BizInfo[i][ID]);
        
        strcat(query, string);
        mysql_tquery(mysql, query, "", "");

EDIT: I tried to update only one field with only one query, and it's still crashing.

pawn Код:

mysql_format(mysql, query, sizeof(query), "UPDATE `businesses` SET `Type`='%d' WHERE `ID`='%d'",\
        BizInfo[i][Type], BizInfo[i][ID]);
        mysql_tquery(mysql, query, "", "");

Re: MySQL query crashes my server - Konstantinos - 09.05.2015

Do you have crashdetect installed? If not, load it and post what it prints.

How many businesses are created as that'll be the number of queries you'll execute while the server is closing.
Have you tried saving (let's say) half of them? Does it still crash?

Re: MySQL query crashes my server - dominik523 - 09.05.2015

I have crashdetect installed and it doesn't print out anything.
My samp-server.exe crashes and I get crash window where I have to press close the window(similar to program is not responding).
Right now, only one business is created. I'm just saving that one.

Re: MySQL query crashes my server - Konstantinos - 09.05.2015

It's kind of difficult to figure the problem out without much of information (not output from crashdetect).

Let's first be sure if that is caused by mysql or not. Comment the line you call SaveBusinesses in OnGameModeExit. What happens?

Re: MySQL query crashes my server - dominik523 - 09.05.2015

Okay so when I comment the line with SaveBusinesses, it's not crashing. I've also done this:

pawn Код:

mysql_format(mysql, query, sizeof(query), "UPDATE `businesses` SET `Type`='%d' WHERE `ID`='%d'",\
        BizInfo[i][Type], BizInfo[i][ID]);
        mysql_tquery(mysql, query, "", ""); // when this is in a comment, it's not crashing also

Re: MySQL query crashes my server - Konstantinos - 09.05.2015

I tried to reproduce it and I think I got it.

pawn Код:

Server crashed due to an unknown error

and that's because "mysql_close" was not called in OnGameModeExit. I'm not sure if that is your problem.

I also noticed that if I call mysql_close and before that, I update 1 query only - nothing happens. Nothing is written in mysql logs.

Re: MySQL query crashes my server - PowerPC603 - 09.05.2015

Why do you save upon server exit?
Save your data as soon as it changes.

Then you don't need to re-save EVERY business when the server shuts down.

You also get:
- no dataloss when the server suddenly crashes
- only 1 query (or max 2 if you split them up like this) needs to be sent when data changes
- no massive series of queries at once (if MAX_BIZ is 100, you're sending 200 queries)

When someone buys the business, save.
When someone upgrades it, buys stuff from it or whatever changes there are, save.

Then you don't need to save everything at once, where most of the data could have stayed the same and you're re-saving the exact same data that's already there.

Re: MySQL query crashes my server - dominik523 - 09.05.2015

Quote:

Originally Posted by Konstantinos

I tried to reproduce it and I think I got it.

pawn Код:

Server crashed due to an unknown error

I completely forgot about closing the database upon the exiting of the server. That caused the crash. Thank you very much Konstantinos!

@PowerPC603:
That seems more logical than saving every business, house, garage and all other things at the end of the server. I'll do what you said and update everything as soon as it happens. Thank you!