What would you do in this situation dealing with saving data to a MySQL database?

[bran92don]

I have been trying to figure out how to save data to my MySQL database and I found this: [url]http://www.facepunch.com/threads/showthread.php?t=1023540[/url]

I managed to fix the errors and fix some security flaws I saw in the code, but when it saves the data to the local database it lags everything on the server for 2 mins every 30 seconds.

I am pretty sure the lag is being generated from the MySQL to SQL function that gets called every 30 seconds. So instead of using that I was debating on not using that function and then overwriting the getPdata function to just simply retrieve the data from the MySQL database.

Since I already fixed this up I was trying to see what I should do from here:

My options:

1: Overwrite the getPdata functions and use that instead of using the MySQL to SQL function.
2: Re-Attempt to write my own MySQL functions

I am trying to find the best option with minimum lag.

Also finally I want to say: [b]I take no credit for this.[/b]

All credit goes to: [b]_Undefined[/b]. All I did was fix some security issues and errors.

[Kingof🅱east]

I'm not sure that MySQL2SQL running on a timer is entirely necessary. You're moving the system to MySQL for a reason, so a function that migrates the MySQL data back into SQLite seems largely unnecessary. As long as you've run the console command on the initial server, you should be fine.

TLDR Remove the timer completely.

[b]Edit:[/b] I noticed that the GetPData function was not overwritten. That was still pulling from SQLite. You should overwrite that function, as well as SetPData because it also still retains the SQLite functionality for no good reason.

[lua]function Player:SetPData(key, value)
-- Don't keep it local why would you keep it local you're globalizing the system for a reason

MsgN("PData [SQL -> MySQL]: Updating '" .. key .. "' for '" .. self:UniqueID() .. "' with '" .. value .. "'!")

deleteQ = PDataDB:query("DELETE FROM `playerdata` WHERE `uniqueid` = '" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")
deleteQ.onError = DBError
deleteQ:start()

insertQ = PDataDB:query("INSERT INTO `playerdata` (`uniqueid`, `key`, `value`) VALUES ('" .. self:UniqueID() .. "', '" .. key .. "', '" .. value .. "')")
insertQ.onError = DBError
insertQ:start()
end

function Player:GetPData(key, default)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND key` = '" .. key .. "';");
insertQ.onError = DBError
selectQ.onData = function(self, data)
retVal = data.value or default
end
insertQ:start()
insertQ:wait()

return retVal
end[/lua]

^ Untested

[Co2]

[QUOTE=KingofBeast;44504125]

[lua]
deleteQ = PDataDB:query("DELETE FROM `playerdata` WHERE `uniqueid` = '" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")
deleteQ.onError = DBError
deleteQ:start()

insertQ = PDataDB:query("INSERT INTO `playerdata` (`uniqueid`, `key`, `value`) VALUES ('" .. self:UniqueID() .. "', '" .. key .. "', '" .. value .. "')")
insertQ.onError = DBError
insertQ:start()
[/lua]
[/QUOTE]

These two queries could be replaced with a single [URL="https://dev.mysql.com/doc/refman/5.0/en/replace.html"]REPLACE[/URL] query.

[Kingof🅱east]

It won't work properly without unique or primary keys. Could use an INSERT .. ON DUPLICATE clause as well. I figured I'd just keep it simple.

[Co2]

I thought `uniqueid` is a primary key.

[Kingof🅱east]

Here's the structure from his config file:

[quote]CREATE TABLE IF NOT EXISTS `playerdata` (
`uniqueid` varchar(20) NOT NULL,
`key` varchar(100) NOT NULL,
`value` text NOT NULL
);[/quote]

No primary/unique keys

[bran92don]

[QUOTE=KingofBeast;44504125]I'm not sure that MySQL2SQL running on a timer is entirely necessary. You're moving the system to MySQL for a reason, so a function that migrates the MySQL data back into SQLite seems largely unnecessary. As long as you've run the console command on the initial server, you should be fine.

TLDR Remove the timer completely.

[b]Edit:[/b] I noticed that the GetPData function was not overwritten. That was still pulling from SQLite. You should overwrite that function, as well as SetPData because it also still retains the SQLite functionality for no good reason.

[lua]function Player:SetPData(key, value)
-- Don't keep it local why would you keep it local you're globalizing the system for a reason

MsgN("PData [SQL -> MySQL]: Updating '" .. key .. "' for '" .. self:UniqueID() .. "' with '" .. value .. "'!")

deleteQ = PDataDB:query("DELETE FROM `playerdata` WHERE `uniqueid` = '" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")
deleteQ.onError = DBError
deleteQ:start()

insertQ = PDataDB:query("INSERT INTO `playerdata` (`uniqueid`, `key`, `value`) VALUES ('" .. self:UniqueID() .. "', '" .. key .. "', '" .. value .. "')")
insertQ.onError = DBError
insertQ:start()
end

function Player:GetPData(key, default)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND key` = '" .. key .. "';");
insertQ.onError = DBError
selectQ.onData = function(self, data)
retVal = data.value or default
end
insertQ:start()
insertQ:wait()

return retVal
end[/lua]

^ Untested[/QUOTE]

Thanks . I thought redoing the getpdata function to work on MySQL would fix the issue, but apparently it still generates a tiny bit of freezing whenever it is called.

Which sadly happens to be when people are killed. I am actually a little bit confused now as to why this is generating lag. I have seen plenty of other servers use a MySQL database to save information and it doesn't lag theirs.

Are there some internet options I need to mess with or tick rate that I need to know about?

[rejax]

[QUOTE=bran92don;44513439]Thanks . I thought redoing the getpdata function to work on MySQL would fix the issue, but apparently it still generates a tiny bit of freezing whenever it is called.

Which sadly happens to be when people are killed. I am actually a little bit confused now as to why this is generating lag. I have seen plenty of other servers use a MySQL database to save information and it doesn't lag theirs.

Are there some internet options I need to mess with or tick rate that I need to know about?[/QUOTE]

It's because you're sending queries each time.
What you should do:
On player join: query their stats and mirror that locally
On player leave/die (depends how important this information is): update information both through mysql and local

This way, you're not using queries very often

[Kingof🅱east]

The reason for lag is because you're forced to use the query.wait() command in order for GetPData to work as normal. You can eliminate the lag by rewriting GetPData to use a callback instead of directly returning the value.

[bran92don]

[QUOTE=KingofBeast;44514376]The reason for lag is because you're forced to use the query.wait() command in order for GetPData to work as normal. You can eliminate the lag by rewriting GetPData to use a callback instead of directly returning the value.[/QUOTE]

Hey thanks for all the help but I am still not getting this. Like I honestly do not know why i can't get this. It seems like it should be easy but every time I try and code something for this I get some error.

Here is what I have been trying so far, and I am completely confused on callback functions. Like From what I understand it is a function calling another function waiting for input.

I commented out everything that didn't work.

code:
[lua]
function Player:GetPData(key, default)

local retVal = default
local tempretVal = 0
insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'");
insertQ.onError = DBError

insertQ.onSuccess = function(self, data)
//callback( data )
//retVal = data or default
//print(retVal[#retVal])
//return retVal
//retVal = call_wait(retVal)
//return call_wait(retVal)
end

/*
insertQ.onData = function(self, data)
retVal = data.value or default
return retVal
//retVal = call_wait(retVal)
//return call_wait(retVal)
end
*/
//end
/*
insertQ.onData = function(self, data)
retVal = data.value or default
end
*/

//print(insertQ)
//print(retVal)

insertQ:start()

//insertQ:wait()
//retVal = call_wait(retVal)
//print("THE STUFF RETURN IS BELOW!!!!!")
//print(retVal)

return retVal

end

function call_wait(info)
print(info)
return info
end
[/lua]

I need to make the application retrieve all the data and when it is done return it right? The problem is how do I make it wait. I can't do a simple check for values because it spams nill and 0 a bunch of times.

[Blasteh]

insertQ.onSuccess = function(self, data)
    //callback( data )
    NameOfFunction(data)

end

Basically, that will call your function and pass the results of the query in to the function.
This only happens when the query succeeds.

[bran92don]

No offense and I hate asking this but can someone just give me the answer to this.

I honestly am sick and tired of researching and trying 10 billion different ways only for nothing to work.

[Acecool]

Here are some tutorials:

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_mysqloo.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_tmysql.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]

What they're saying with the .wait is that it'll allow you to return in to the function it was called from. This is bad. Normally when its called and you don't wait, returns won't work because the pointer passes that location and is processing something else by the time it returns. Use a callback, so a function inside the query that handles the success of the query. You could even use a hook.Call..

In my tuts, you'll see I use hooks to broadcast when the db is connected, or if it has failed. Using those hooks, other things can be done. I wouldn't recommend using a hook for every single query, but a function callback is wise. Don't develop so that you have to wait on a return. Develop the logic so you can throw the query into the processing pile and when its done it'll change something which will affect something else...

In other words: You do not want to make it wait if you don't want lag. If you want lag/freezing, make it wait.

Edit: Here is an example of a "callback" being used in the real-world: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/debugging/concommand_clearvgui.lua.html[/url]

So, when clearvgui is typed in console, It grabs a main panel, then is prints something, then it calls vgui.ProcessChildren with the panel it grabbed in addition to the callback ( a simple function to be called when the work is done ). For the first callback, we allow one argument _child, and in the callback we remove that child from existence ( clearing the vgui ). We repeat the process with the HUDPanel ( albeit you can't seem to get those parented to hud just yet : bugged ), but it has the same callback. I hope this clarifies what a callback is, its really simple..

[bran92don]

[QUOTE=Acecool;44811601]Here are some tutorials:

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_fallbacks_in_place.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_mysqloo.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/_connecting_with_tmysql.lua.html[/url]

[url]https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua.html[/url]

What they're saying with the .wait is that it'll allow you to return in to the function it was called from. This is bad. Normally when its called and you don't wait, returns won't work because the pointer passes that location and is processing something else by the time it returns. Use a callback, so a function inside the query that handles the success of the query. You could even use a hook.Call..

In my tuts, you'll see I use hooks to broadcast when the db is connected, or if it has failed. Using those hooks, other things can be done. I wouldn't recommend using a hook for every single query, but a function callback is wise. Don't develop so that you have to wait on a return. Develop the logic so you can throw the query into the processing pile and when its done it'll change something which will affect something else...

In other words: You do not want to make it wait if you don't want lag. If you want lag/freezing, make it wait.

Edit: Here is an example of a "callback" being used in the real-world: [url]https://dl.dropboxusercontent.com/u/26074909/tutoring/debugging/concommand_clearvgui.lua.html[/url]

So, when clearvgui is typed in console, It grabs a main panel, then is prints something, then it calls vgui.ProcessChildren with the panel it grabbed in addition to the callback ( a simple function to be called when the work is done ). For the first callback, we allow one argument _child, and in the callback we remove that child from existence ( clearing the vgui ). We repeat the process with the HUDPanel ( albeit you can't seem to get those parented to hud just yet : bugged ), but it has the same callback. I hope this clarifies what a callback is, its really simple..[/QUOTE]

Thank you and here is what I have but it seems to be calling the callback function more then w hat is needed.

I have tried using both on success and on data and the both output this in console:

IT IS RUNNING THE CALLBACK
21
IT IS RUNNING THE CALLBACK
0
IT IS RUNNING THE CALLBACK
0

[lua]function Player:GetPData(key, default, callback)

local retVal = default
local tempretVal = 0
insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")

insertQ.onData = function(self, data)
tempretVal = data.value
//callback( data )
print("IT IS RUNNING THE CALLBACK")
//PrintTable(data)
//print(data[1].value)
return callback(tempretVal,default)
//call_wait(data[1].value,default)
end
/*
insertQ.onSuccess = function(self, data)
tempretVal = data[1].value
//callback( data )
print("IT IS RUNNING THE CALLBACK")
//PrintTable(data)
//print(data[1].value)
callback(tempretVal,default)
//call_wait(data[1].value,default)
end
*/
function callback( data,default)
print(data)
if data != nil then
return data
else
return default
end
end

insertQ.onError = function(DBError)
print(DBError)
end
insertQ:start()
end
[/lua]

[Kingof🅱east]

This should work for you.

Untested, though.

[lua]function Player:GetPData(key, default, callback)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND key` = '" .. key .. "';");
insertQ.onError = DBError
selectQ.onData = function(self, data)
retVal = data.value or default
end
selectQ.onSuccess = function(self)
callback(retVal);
end
insertQ:start()
end

-- usage:
pl:GetPData("cash", 0, function(cash)
pl:ChatPrint("You have $" .. cash .. ".")
end)[/lua]

Why does FP's edit post [B]and[/B] lua code block suck sooo bad?

[bran92don]

[QUOTE=KingofBeast;44852417]This should work for you.

Untested, though.

[lua]function Player:GetPData(key, default, callback)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND key` = '" .. key .. "';");
insertQ.onError = DBError
selectQ.onData = function(self, data)
retVal = data.value or default
end
selectQ.onSuccess = function(self)
callback(retVal);
end
insertQ:start()
end

-- usage:
pl:GetPData("cash", function(cash)
pl:ChatPrint("You have $" .. cash .. ".")
end)[/lua]

Why does FP's edit post [B]and[/B] lua code block suck sooo bad?[/QUOTE]

That returns a nil value and I also ended up doing this so I owuldn't have to constantly keep typign that funciton over and over again:

[lua]
function Player:GetPData(key, default)

testget(self,key,default,function(junk)
print(junk)
return junk
end)

end

function testget(self,key, default, callback)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND key` = '" .. key .. "';");
insertQ.onError = DBError
insertQ.onData = function(self, data)
retVal = data.value or default
print(retVal)
print("--------------------------------------------------------------------------------------------------------------")
insertQ.onSuccess = function(self)
print(retVal)
callback(retVal);
end
insertQ:start()
end

end
[/lua]

What really makes me mad is the fact that it is not only not working but it isn't printing out the values for me to see where the error is occurring.

[Kingof🅱east]

Your ends are in the wrong spots.

[bran92don]

I am starting to feel this way is not possible, because it is not changing the stats of my player on spawn. The print in the testget is printing out a number of 23, but that is all it is printing out. I have a lot more stats being retrieve then just one value, like their xp and rank.

I am using a user message to send the data to the players screen, could there be some lag being generated between this and the query?
I tested my theory out and used setNWInt and it did not change anything the value is still the same.
output:

23

code now:
[lua]function Player:GetPData(key, default)
local retVal = default
testget(self,key,default,function(junk)
print(junk)
retVal = junk
return junk
end)

return retVal

end

function testget(self,key, default, callback)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")
insertQ.onError = DBError
insertQ.onData = function(self, data)
retVal = data.value or default
insertQ.onSuccess = function(self)
callback(retVal);
end
end

insertQ:start()

end[/lua]

[Kingof🅱east]

I get the feeling you're using this incorrectly.

Use exactly what is in [url=http://facepunch.com/showthread.php?t=1384175&p=44852417&viewfull=1#post44852417]my post[/url], and tell me what results that produces.

[bran92don]

[QUOTE=KingofBeast;44885645]I get the feeling you're using this incorrectly.

Use exactly what is in [url=http://facepunch.com/showthread.php?t=1384175&p=44852417&viewfull=1#post44852417]my post[/url], and tell me what results that produces.[/QUOTE]

My result is not weapons on spawn and this error that shouldn't be a factor in the situation because I did it this way when I was using sql and it worked just fine:

[lua]
[ERROR] gamemodes/codmod/gamemode/xp.lua:22: bad argument #1 to 'tonumber' (value expected)
1. tonumber - [C]:-1
2. check_for_nrank - gamemodes/codmod/gamemode/xp.lua:22
3. v - gamemodes/codmod/gamemode/xp.lua:182
4. unknown - lua/includes/modules/hook.lua:84
5. Kill - [C]:-1
6. killswitch - gamemodes/codmod/gamemode/init.lua:76
7. unknown - gamemodes/codmod/gamemode/init.lua:231
8. unknown - lua/includes/modules/concommand.lua:69

WHY ARE YOU RUNNING
PData [SQL -> MySQL]: Updating 'xp' for '2809158571' with '0'!
New player data created!

[ERROR] gamemodes/codmod/gamemode/xp.lua:95: bad argument #1 to 'tonumber' (value expected)
1. tonumber - [C]:-1
2. new_xp_data - gamemodes/codmod/gamemode/xp.lua:95
3. check_xp - gamemodes/codmod/gamemode/xp.lua:164
4. v - gamemodes/codmod/gamemode/xp.lua:187
5. unknown - lua/includes/modules/hook.lua:84
6. Spawn - [C]:-1
7. unknown - gamemodes/codmod/gamemode/init.lua:234
8. unknown - lua/includes/modules/concommand.lua:69

0
[/lua]
This is the current code: *Again to clarify, I did it with two functions like that to prevent me from having to got through my whole code and put a function in it that way. *
[lua]

function Player:GetPData(key, default)
testget(self,key,default,function(junk)
print(junk)
return junk
end)

//print(retval)
//return retVal

end

function testget(self,key, default, callback)
local retVal = default

insertQ = PDataDB:query("SELECT `value` from `playerdata` WHERE `uniqueid`='" .. self:UniqueID() .. "' AND `key` = '" .. key .. "'")
insertQ.onError = DBError
insertQ.onData = function(self, data)
retVal = data.value or default
insertQ.onSuccess = function(self)
callback(retVal);
end
end

insertQ:start()

end
[/lua]

[rejax]

You can't return data like that.

Instead of doing it like this ->
[LUA]local value = ply:GetPData( "key", 0 )
print( value )
> nil[/LUA]

you have to do it like this
[LUA]
local function test( ply )
local value

ply:GetPData( "key", 0, function( data )
value = data
-- ply:ChatPrint( value )
-- do whatever you want with the data
end )
end
[/LUA]

[bran92don]

[QUOTE=rejax;44975640]You can't return data like that.

Instead of doing it like this ->
[LUA]local value = ply:GetPData( "key", 0 )
print( value )
> nil[/LUA]

you have to do it like this
[LUA]
local function test( ply )
local value

ply:GetPData( "key", 0, function( data )
value = data
-- ply:ChatPrint( value )
-- do whatever you want with the data
end )
end
[/LUA][/QUOTE]

Funny thing is I did originally have it that way. I actually typed it that way about 1 or 2 post above the one I had posted recently.

Wait why did he tell me I was wrong then..............

In my head it was working , but the user-messages were not sending the right data.

[bran92don]

Ok, this is EXTREMELY STRANGE.

It is finally working and the second I upload it to the server it starts spewing out incorrect values.

Example:

xp = 100
kill someone
xp = 50
kill someone
xp=0