15.01.2015, 01:35
(
Последний раз редактировалось vassilis; 15.01.2015 в 19:44.
)
solved
MYSQL Data problem
15.01.2015, 03:44
of course this will happen if you save the data in OnPlayerDisconnect, puts you at 4 and disconnect puts the variable that you have in the game.
You are overwriting.
You are overwriting.
15.01.2015, 09:57
You didn't give the code where you set the level to 4, can we see that as well?
Also a suggestion:
Make multiple smaller queries to only update the fields that change.
It has no use to ALWAYS update ALL fields when only 1 value changes.
As you see here, I make small functions all over the place and they only update the required fields, not ALL of them.
The first is my /setlevel command, the second is a function which I can use to give the player some money and/or scorepoints.
That last function is called everywhere in the script where the player earns money or scorepoints.
Maintaining such a small query is not a problem, and as soon as it's written, you never have to edit it again, as it's doing whatever it is supposed to be doing.
And the last command updates only the readout-setting to convert his speedometer to kph/mph.
You have to keep updating yours everytime you wanna save an additional field, and because of it's length, you can make mistakes easily.
Updating money doesn't require overwriting the adminlevel, or wanted level, or muted status, or my deaths/kills, or even my banned status, they don't relate to eachother at all.
Imagine that your table will grow ALOT (say to 150 fields), would you rather make one HUGE query to update all 150 fields when only 1 value changes and where the other 149 values just stay the same?
Another comparison:
If you type a big post onto this forum and you make a typo (wrong character or wrong word), would you retype the entire post to fix the typo, or just edit that one character or word to fix the typo?
I guess you choose the second one.
Making such huge queries puts extra stress on your database and harddrive when it's not even needed.
MySQL needs to break up your query in different parts, analyze it word by word, search for every required column, overwrite each field to finish your query.
All this takes time, and the longer your query, the longer it takes.
It can work much faster if you only update the fields that need to be updated.
Leave the rest alone, as it stays the same anyway.
Of course, you'll have multiple queries all over your script instead of only one huge function.
You might as well use files, which NEED to be overwritten completely when only 1 values changes (those might actually be faster compared to updating all fields everytime in a database).
A database is created because you can update whatever you want, when you want and to do it fast, because you have access to each field separately.
That's their main function.
The code is easier to maintain and you can find bugs much faster when you use small queries instead of 1 huge query.
Also a suggestion:
Make multiple smaller queries to only update the fields that change.
It has no use to ALWAYS update ALL fields when only 1 value changes.
pawn Код:
// Sets the admin-level of another player
COMMAND:setlevel(playerid, params[])
{
// If the player has an insufficient admin-level (he needs level 6), exit the command
if (APlayerData[playerid][AdminLevel] < 6) return SendClientMessage(playerid, 0xFFFFFFFF, "{FF0000}Only admins level 6 and higher can use this command");
// Setup local variables
new OtherPlayer, Level, Msg[128], OldLevel, Query[128];
// Split parameters
if (sscanf(params, "ui", OtherPlayer, Level)) return SendClientMessage(playerid, 0xFF0000AA, "Usage: \"/setlevel <OtherPlayer> <AdminLevel>\"");
// If the other player has an invalid ID (sscanf returns 65535 as playerid if the player cannot be found), exit the command
if (OtherPlayer >= MAX_PLAYERS) return SendClientMessage(playerid, 0xFFFFFFFF, "{FF0000}That player is not online");
// If the other player hasn't logged in yet, exit the command
if (APlayerData[OtherPlayer][LoggedIn] == false) return SendClientMessage(playerid, 0xFFFFFFFF, "{FF0000}That player hasn't logged in properly yet");
// If "Level" is invalid, exit the command
if ((Level < 0) || (Level > 6)) return SendClientMessage(playerid, 0xFFFFFFFF, "{FF0000}Use admin-levels ranging from 0 to 6");
// Get the old level of the other player
OldLevel = APlayerData[OtherPlayer][AdminLevel];
// If the level is still the same, exit the command
if (OldLevel == Level) return SendClientMessage(playerid, 0xFFFFFFFF, "{FF0000}Other player's level hasn't been changed");
// Store the new level of the player and save it to MySQL
APlayerData[OtherPlayer][AdminLevel] = Level;
mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET AdminLevel = '%i' WHERE ID = '%i'", APlayerData[OtherPlayer][AdminLevel], APlayerData[OtherPlayer][SQLID]);
mysql_tquery(SQL_db, Query, "", "");
// Check if the player has been promoted or demoted
if (OldLevel < Level)
format(Msg, 128, "Player %s has been promoted to %s by %s: %s", APlayerData[OtherPlayer][PlayerName], AdminLevelName[Level], AdminLevelName[APlayerData[playerid][AdminLevel]], APlayerData[playerid][PlayerName]);
if (OldLevel > Level)
format(Msg, 128, "Player %s has been demoted to %s by %s: %s", APlayerData[OtherPlayer][PlayerName], AdminLevelName[Level], AdminLevelName[APlayerData[playerid][AdminLevel]], APlayerData[playerid][PlayerName]);
SendClientMessageToAll(0x00FF00FF, Msg);
// Let the server know that this was a valid command
return 1;
}
// This function adds the given Money and Score values to the given player
Player_Reward(playerid, Money, Score)
{
new Query[128];
// Add the given Money and Score to the player's account
APlayerData[playerid][PlayerMoney] = APlayerData[playerid][PlayerMoney] + Money;
APlayerData[playerid][PlayerScore] = APlayerData[playerid][PlayerScore] + Score;
// Update money and score for this player in the player's account in MySQL
mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'", APlayerData[playerid][PlayerMoney], APlayerData[playerid][PlayerScore], APlayerData[playerid][SQLID]);
mysql_tquery(SQL_db, Query, "", "");
return 1;
}
// This command allows the player to toggle his speedometer between kph and mph
COMMAND:units(playerid, params[])
{
// Setup local variables
new Query[128];
// Toggle the units for this player
if (APlayerData[playerid][SpeedInMph] == false)
{
// Set the player's speed read-out to mph
APlayerData[playerid][SpeedInMph] = true;
// Save the data to MySQL
mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET SpeedInMph = '1' WHERE ID = '%i'", APlayerData[playerid][SQLID]);
mysql_tquery(SQL_db, Query, "", "");
// Inform the player
SendClientMessage(playerid, 0xFFFFFFFF, "{00FF00}You've set your speedometer read-out to mph");
}
else
{
// Set the player's speed read-out to kph
APlayerData[playerid][SpeedInMph] = false;
// Save the data to MySQL
mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET SpeedInMph = '0' WHERE ID = '%i'", APlayerData[playerid][SQLID]);
mysql_tquery(SQL_db, Query, "", "");
// Inform the player
SendClientMessage(playerid, 0xFFFFFFFF, "{00FF00}You've set your speedometer read-out to kph");
}
// Let the server know that this was a valid command
return 1;
}
The first is my /setlevel command, the second is a function which I can use to give the player some money and/or scorepoints.
That last function is called everywhere in the script where the player earns money or scorepoints.
Maintaining such a small query is not a problem, and as soon as it's written, you never have to edit it again, as it's doing whatever it is supposed to be doing.
And the last command updates only the readout-setting to convert his speedometer to kph/mph.
You have to keep updating yours everytime you wanna save an additional field, and because of it's length, you can make mistakes easily.
Updating money doesn't require overwriting the adminlevel, or wanted level, or muted status, or my deaths/kills, or even my banned status, they don't relate to eachother at all.
Imagine that your table will grow ALOT (say to 150 fields), would you rather make one HUGE query to update all 150 fields when only 1 value changes and where the other 149 values just stay the same?
Another comparison:
If you type a big post onto this forum and you make a typo (wrong character or wrong word), would you retype the entire post to fix the typo, or just edit that one character or word to fix the typo?
I guess you choose the second one.
Making such huge queries puts extra stress on your database and harddrive when it's not even needed.
MySQL needs to break up your query in different parts, analyze it word by word, search for every required column, overwrite each field to finish your query.
All this takes time, and the longer your query, the longer it takes.
It can work much faster if you only update the fields that need to be updated.
Leave the rest alone, as it stays the same anyway.
Of course, you'll have multiple queries all over your script instead of only one huge function.
You might as well use files, which NEED to be overwritten completely when only 1 values changes (those might actually be faster compared to updating all fields everytime in a database).
A database is created because you can update whatever you want, when you want and to do it fast, because you have access to each field separately.
That's their main function.
The code is easier to maintain and you can find bugs much faster when you use small queries instead of 1 huge query.
15.01.2015, 10:06
Quote:
|
Originally Posted by PowerPC603
 You didn't give the code where you set the level to 4, can we see that as well?
Also a suggestion: Make multiple smaller queries to only update the fields that change. It has no use to ALWAYS update ALL fields when only 1 value changes. pawn Код:
The first is my /setlevel command, the second is a function which I can use to give the player some money and/or scorepoints. That last function is called everywhere in the script where the player earns money or scorepoints. Maintaining such a small query is not a problem, and as soon as it's written, you never have to edit it again, as it's doing whatever it is supposed to be doing. You have to keep updating yours everytime you wanna save an additional field, and because of it's length, you can make mistakes easily. Updating money doesn't require overwriting the adminlevel, or wanted level, or muted status, or my deaths/kills, or even my banned status, they don't relate to eachother at all. Imagine that your table will grow ALOT (say to 150 fields), would you rather make one HUGE query to update all 150 fields when only 1 value changes and where the other 149 values just stay the same? Another comparison: If you type a big post onto this forum and you make a typo (wrong character or wrong word), would you retype the entire post to fix the typo, or just edit that one character or word to fix the typo? I guess you choose the second one. Making such huge queries puts extra stress on your database and harddrive when it's not even needed. MySQL needs to break up your query in different parts, analyze it word by word, search for every required column, overwrite each field to finish your query. All this takes time, and the longer your query, the longer it takes. It can work much faster if you only update the fields that need to be updated. Leave the rest alone, as it stays the same anyway. Of course, you'll have multiple queries all over your script instead of only one huge function. You might as well use files, which NEED to be overwritten completely when only 1 values changes (those might actually be faster compared to updating all fields everytime in a database). A database is created because you can update whatever you want, when you want and to do it fast, because you have access to each field separately. That's their main function. |
If he's updating ALL of them on each function/command, that's another thing and of-course isn't good
Updating MySQL database: all data or in parts?
15.01.2015, 10:15
It's not wrong, but it would make life much easier and your code easier to maintain and fix bugs.
On the other hand, if you only save ALL their data during OnPlayerDisconnect, you can loose data.
If a player logs in and plays for hours in a row, does alot of missions, earns alot of money, whatever...
Say he plays for 10 hours straight, earns millions of dollars, earns hundreds of scorepoints, finishes over 100 missions, killed 150 players, and whatever more.
In case of a sudden server-crash, all that progress is just gone, as nothing has been saved during those 10 hours.
The player who put in hours of playing time to earn his stuff will be furious as he has to start all over again.
If it happens frequently, he might just leave your server (I would) and search for a better one, which DOES save data frequently.
He also might complain on your server's forum that you have to fix bugs, as his data wasn't saved in time.
To the player, he doesn't know your script only saves data when he logs out.
To the player, it appears your script has a problem loading/saving data.
I play MMORPG's and those save data whenever it changes in case of a server-crash, to minimize the dataloss and loss of progress for players.
It's like coding an entire day, writing 5k lines of code and never save, only just before you turn off the pc and go to bed.
When the electricity would fail (electric work by the company, a blown fuse, whatever reason), you would lose your entire script.
You'll have to start over and wonder: why oh why didn't I save earlier?
For all those reasons I stated:
- minimize dataloss
- easier to maintain code (the queries themselves as they are all quite short)
- easier to spot bugs
- less prone to making mistakes as your query-line doesn't span over 10+ lines
I prefer to use small queries all over the place and save data whenever it changes, and I recommend it to everyone.
If my own server would suddenly crash, I have 0 dataloss.
In case the server crashes during execution of a query, there are 2 possibilities:
- either the query was sent properly and the data was updated (server might crash, but mysql is a separate process and might not crash
- either the query failed to be sent to MySQL and only that last value-change was lost
The only thing that might be lost, it when a player already started a mission and is close to the end-point of the mission, in case of a server-crash, the trip he took from start to finish for that mission is lost, as data isn't saved in the middle of a mission.
That's 5-10 mins of playing time that gets lost, but not any data.
On the other hand, if you only save ALL their data during OnPlayerDisconnect, you can loose data.
If a player logs in and plays for hours in a row, does alot of missions, earns alot of money, whatever...
Say he plays for 10 hours straight, earns millions of dollars, earns hundreds of scorepoints, finishes over 100 missions, killed 150 players, and whatever more.
In case of a sudden server-crash, all that progress is just gone, as nothing has been saved during those 10 hours.
The player who put in hours of playing time to earn his stuff will be furious as he has to start all over again.
If it happens frequently, he might just leave your server (I would) and search for a better one, which DOES save data frequently.
He also might complain on your server's forum that you have to fix bugs, as his data wasn't saved in time.
To the player, he doesn't know your script only saves data when he logs out.
To the player, it appears your script has a problem loading/saving data.
I play MMORPG's and those save data whenever it changes in case of a server-crash, to minimize the dataloss and loss of progress for players.
It's like coding an entire day, writing 5k lines of code and never save, only just before you turn off the pc and go to bed.
When the electricity would fail (electric work by the company, a blown fuse, whatever reason), you would lose your entire script.
You'll have to start over and wonder: why oh why didn't I save earlier?
For all those reasons I stated:
- minimize dataloss
- easier to maintain code (the queries themselves as they are all quite short)
- easier to spot bugs
- less prone to making mistakes as your query-line doesn't span over 10+ lines
I prefer to use small queries all over the place and save data whenever it changes, and I recommend it to everyone.
If my own server would suddenly crash, I have 0 dataloss.
In case the server crashes during execution of a query, there are 2 possibilities:
- either the query was sent properly and the data was updated (server might crash, but mysql is a separate process and might not crash
- either the query failed to be sent to MySQL and only that last value-change was lost
The only thing that might be lost, it when a player already started a mission and is close to the end-point of the mission, in case of a server-crash, the trip he took from start to finish for that mission is lost, as data isn't saved in the middle of a mission.
That's 5-10 mins of playing time that gets lost, but not any data.
15.01.2015, 10:29
I thought you're talking about it being totally incorrect for some reason, I guess you're right,
You don't get a server crash every hour, but it happens.
For me, I'll save most data once the player disconnects, and the important/Slow Changing data whenever it changes,
You don't get a server crash every hour, but it happens.
For me, I'll save most data once the player disconnects, and the important/Slow Changing data whenever it changes,
Quote:
|
Originally Posted by Mauzen
A combination of both would be the best I guess.
Theres "slow" data like properties, that barely changes. This could be stored on every change very well. And then theres "fast" data, e.g. weapon data, health, position, etc. As this might change very often it would be quite stupid to save these values on every update of course. So theres no way that works best for everyone. Youll rather have to decide when to store which values. |
15.01.2015, 12:41
Guys first of all thanks for your feedback. Secondly the database was working properly before but yesterday i tried to add skin saving and when i saw it didn't work fine i removed it again and i think by mistake i deleted something that i shouldn't delete(i guess??) so that's my problem..
EDIT: I read whole statement of PPC so you tell me that instead of making a whole function for disconnect it would be better saving each query to each function or command it is neccessary?
EDIT: I read whole statement of PPC so you tell me that instead of making a whole function for disconnect it would be better saving each query to each function or command it is neccessary?
15.01.2015, 12:54
The server may come down by a brute-force attack or DDOS attack, the data-center itself might experience power outages, the script itself may also be a reason for a sudden crash.
It's the programmer's job to ensure minimal dataloss for their players (and for themselves).
Saving all data at once during OnPlayerDisconnect is not wrong, it may be helpful when you forget to update some data when it changes.
I'm simply pointing out what can/may happen when you use huge queries all over the script and what may happen if you only save your data once.
I found it the easiest to update data whenever it changes, for both slow and fast changing data.
As soon as you update the variable in the server's memory, update it in the database as well, and you won't forget anything that way.
As my posted Player_Reward function, I put such things inside a small function which is responsible for updating the data on the server and in the database at once.
I even saved my jail-time every second when a player was in jail.
Everytime the counter went down by a second, the script sent an update-query to the database to adjust the jailtime.
This may be a little overkill on my part, I guess I can change that to every 10 seconds to reduce the amount of queries sent to mysql.
My previous gamemode seemed to have corrupted accounts every now and then by saving during OnPlayerDisconnect.
I guess (but I'm still not sure about it, correct me if I'm wrong) that it was because I wasn't simply getting data from variables, but also from the client as well, like the wanted level.
That wasn't stored on the server and needed to be grabbed from the client during disconnect.
But I guessed the client could be disconnected before all the data was grabbed from the client, resulting in missing/corrupt data.
In my new gamemode, I'm not using OnPlayerDisconnect anymore to save data, just to be on the safe side.
Basically, yes.
I've already stated the benefits above:
- easier to maintain your queries (once finished and working, you don't have to mess with them again)
- small queries are processed faster, so less stress on your system
- easier to spot bugs, as you figured out yourself just now (a huge query takes more time to find the problem)
- least dataloss as data is saved whenever it changes
It's the programmer's job to ensure minimal dataloss for their players (and for themselves).
Saving all data at once during OnPlayerDisconnect is not wrong, it may be helpful when you forget to update some data when it changes.
I'm simply pointing out what can/may happen when you use huge queries all over the script and what may happen if you only save your data once.
I found it the easiest to update data whenever it changes, for both slow and fast changing data.
As soon as you update the variable in the server's memory, update it in the database as well, and you won't forget anything that way.
As my posted Player_Reward function, I put such things inside a small function which is responsible for updating the data on the server and in the database at once.
I even saved my jail-time every second when a player was in jail.
Everytime the counter went down by a second, the script sent an update-query to the database to adjust the jailtime.
This may be a little overkill on my part, I guess I can change that to every 10 seconds to reduce the amount of queries sent to mysql.
My previous gamemode seemed to have corrupted accounts every now and then by saving during OnPlayerDisconnect.
I guess (but I'm still not sure about it, correct me if I'm wrong) that it was because I wasn't simply getting data from variables, but also from the client as well, like the wanted level.
That wasn't stored on the server and needed to be grabbed from the client during disconnect.
But I guessed the client could be disconnected before all the data was grabbed from the client, resulting in missing/corrupt data.
In my new gamemode, I'm not using OnPlayerDisconnect anymore to save data, just to be on the safe side.
Quote:
|
Originally Posted by vassilis
Guys first of all thanks for your feedback. Secondly the database was working properly before but yesterday i tried to add skin saving and when i saw it didn't work fine i removed it again and i think by mistake i deleted something that i shouldn't delete(i guess??) so that's my problem..
EDIT: I read whole statement of PPC so you tell me that instead of making a whole function for disconnect it would be better saving each query to each function or command it is neccessary? |
I've already stated the benefits above:
- easier to maintain your queries (once finished and working, you don't have to mess with them again)
- small queries are processed faster, so less stress on your system
- easier to spot bugs, as you figured out yourself just now (a huge query takes more time to find the problem)
- least dataloss as data is saved whenever it changes
15.01.2015, 12:59
Ok i got it what you mean but i have a problem somehow.
I'm new to mysql so i have 2 questions. Firstly shold i remove the acount save and load? or should i let for secondary saving when he quit?
And secondly what should i do know with the database as it doesnt save??
I'm new to mysql so i have 2 questions. Firstly shold i remove the acount save and load? or should i let for secondary saving when he quit?
And secondly what should i do know with the database as it doesnt save??
15.01.2015, 13:02
Loading stays the same, as you still need to load all data when a player logs in.
Just split up your saving function into smaller queries, put them inside small functions (like my Player_Reward) that update both the data in memory as the database using a small query, and put the function calls in the proper places (everywhere my players earn money or scorepoints, you can find a call to Player_Reward).
Also a problem using huge queries:
When the query fails, none of your data gets saved.
Using smaller queries, if one fails, the other can still succeed, saving at least some of your data and you can spot the problem faster, as you only have a small query/function for each field to deal with.
Just split up your saving function into smaller queries, put them inside small functions (like my Player_Reward) that update both the data in memory as the database using a small query, and put the function calls in the proper places (everywhere my players earn money or scorepoints, you can find a call to Player_Reward).
Also a problem using huge queries:
When the query fails, none of your data gets saved.
Using smaller queries, if one fails, the other can still succeed, saving at least some of your data and you can spot the problem faster, as you only have a small query/function for each field to deal with.
« Next Oldest | Next Newest »
Users browsing this thread: 3 Guest(s)