Solved TFS 1.2 db.query for changing player storage value

[whitevo]

Why is that if do this:

db.query("UPDATE `player_storage` SET `value` = "..v.." WHERE `key` = "..sv.." AND `player_id` = "..guid)

and then use this:

SV = player:getStorageValue(sv)
print(SV)

I get the PREVIOUS value instead of the VALUE I set with db.query?
When I do this in mysql:

SELECT * FROM `player_storage` WHERE `key` = 10000 AND `player_id` = 1

then it does show the correct VALUE I created with the db.query

On top of it all, if I log out or save character, the storage value reverts back!
Is it bug or intended?

EDIT: Incase you found this thread trough search engine, then you probably looking for something like this:

function setAccountSV(accID, sv, v)
local playerGUIDT = {}
local playerData = db.storeQuery("SELECT id FROM `players` WHERE account_id = "..accID)
   
    if playerData then
        repeat
            local guid = result.getNumber(playerData, "id")
            table.insert(playerGUIDT, guid)
        until not result.next(playerData)
    end
   
    for _, guid in pairs(playerGUIDT) do
        local playerData = db.storeQuery("SELECT value FROM `player_storage` WHERE player_id = "..guid.." AND `key` = "..sv)
        local charName = getPlayerNameByGUID(guid)
       
        if Player(charName) then
            Player(charName):setStorageValue(sv, v)
        elseif playerData then
            db.query("UPDATE `player_storage` SET `value` = "..v.." WHERE `key` = "..sv.." AND `player_id` = "..guid)
        else
            db.query("INSERT INTO `player_storage`(`player_id`, `key`, `value`) VALUES ("..guid..","..sv..","..v..")")
        end
    end
end

 

[Ninja]

It's not a bug. Storage keys are saved to the database when the player either dies or logs out. You have to kick the player from game before you play around with MySQL queries.

 

[whitevo]

It's not a bug. Storage keys are saved to the database when the player either dies or logs out. You have to kick the player from game before you play around with MySQL queries.

Well they are saved to database first, but seems like the game itself has its "own database" which will overwrite the the real one. Right?

EDIT:
O boy seems its even more complicated than that..
If I add new storage values with db.query using 1 character and then I log out and log into another one and then log out with the other one again. It will revert storage values.

So basically does it mean ALL characters on that account must be logged off before I can change storage values??
Why such a restriction?..

EDIT2:
removing the player did not help.
player:remove()

 

[Non Sequitur]

Those aren't restrictions, you're just trying to change a value in the wrong way. You should only change player's values through queries if the player is offline (or if the value you want to change isn't saved when the player logs off, i.e the name column). If you only have the player's GUID, use something like this:

local resultId = db.storeQuery("SELECT `name` FROM `players` WHERE `id` = " .. guid)
if not resultId then
    -- player doesn't exists
end

local name = result.getString(resultId, 'name')
local player = Player(name)
if not player then
    -- player is offline
    db.query("UPDATE `player_storage` SET `value` = " .. v .. " WHERE `key` = " .. sv .. " AND `player_id` = " .. guid)
else
    -- player is online
    player:setStorageValue(sv, v)
end

 

[whitevo]

Those aren't restrictions, you're just trying to change a value in the wrong way. You should only change player's values through queries if the player is offline (or if the value you want to change isn't saved when the player logs off, i.e the name column). If you only have the player's GUID, use something like this:

local resultId = db.storeQuery("SELECT `name` FROM `players` WHERE `id` = " .. guid)
if not resultId then
    -- player doesn't exists
end

local name = result.getString(resultId, 'name')
local player = Player(name)
if not player then
    -- player is offline
    db.query("UPDATE `player_storage` SET `value` = " .. v .. " WHERE `key` = " .. sv .. " AND `player_id` = " .. guid)
else
    -- player is online
    player:setStorageValue(sv, v)
end

check my post 1. Its already like that (and a bit better right now, I will update the post 1 later, when i figure out how to update these values...)
IT DOES NOT WORK.

I'm changing the values on the same account. Even if I force log out the player (with player:remove()) AND then change the values in database.
When he logs in he still has old values On top of it, when he logs out again. They are reversed!

EDIT 3:
I kicked player and changed storage values with delay. Still did not work.
They are reverted back when player logs in.

 

[Non Sequitur]

If you're changing the values through player:setStorageValue there is no reason for them to get "changed back" the next time the player logs in. Are you sure this is ever being called?

Player(charName):setStorageValue(sv, v)

 

[whitevo]

If you're changing the values through player:setStorageValue there is no reason for them to get "changed back" the next time the player logs in. Are you sure this is ever being called?

Player(charName):setStorageValue(sv, v)

wait a sec.
I have to start all over again.
I had a freaking storage value name typo in modal window where i was checking the player status...

 

[whitevo]

Post 1 updated with working code now.
Yeah no logout needed.
I was changing the values, but the reason I though its not working was that I was comparing it with different storage value all together.

But it still feels like restriction, because I don't see any reason why we are not allowed to change online player storage values in database or with queries.

 

[Non Sequitur]

Because accessing the database is way slower than accessing your computer memory. Just imagine if every time the player moved an item the server had to load all the player's items and then move it? Or if every time the player walked, the server had to update the player's position in the database, now multiply that by the hundreds of players online and every action that would ever change anything stored in the database. And it's not just about read & write speed, databases are used to store persistent data and the physical way of storing persistent data (hard-drives or solid-state drives, for example) takes its toll on the hardware. You can only store data so many times, and soon enough your equipment will start failing. On the other hand, flash memory (used on the so called RAM memory) is way faster to read and write and will survive way more R&W operations than their persistent data storing counterparts.

To sum it up, it is not a restriction on what you can do, it is simply putting each type of memory to its best use.

 

[gunz]

You can use this way:
player:setStorageValue(123, 1)
player:save()
SELECT * FROM `player_storage` WHERE `key` = 123 AND `player_id` = 1

You simply should avoid using queries to change storage vlaues.

 

[whitevo]

Because accessing the database is way slower than accessing your computer memory. Just imagine if every time the player moved an item the server had to load all the player's items and then move it? Or if every time the player walked, the server had to update the player's position in the database, now multiply that by the hundreds of players online and every action that would ever change anything stored in the database. And it's not just about read & write speed, databases are used to store persistent data and the physical way of storing persistent data (hard-drives or solid-state drives, for example) takes its toll on the hardware. You can only store data so many times, and soon enough your equipment will start failing. On the other hand, flash memory (used on the so called RAM memory) is way faster to read and write and will survive way more R&W operations than their persistent data storing counterparts.

To sum it up, it is not a restriction on what you can do, it is simply putting each type of memory to its best use.

Its not like I'm going to spam using db.query to make such changes. Right now its seems simply inconvenient to check is the player online or not to forcefully edit DB

 

[Non Sequitur]

Its not like I'm going to spam using db.query to make such changes. Right now its seems simply inconvenient to check is the player online or not to forcefully edit DB

Maybe you won't, but it's a design choice and it makes no sense to program two ways to do the same thing, especially when one of them isn't optimal.

 

[whitevo]

Maybe you won't, but it's a design choice and it makes no sense to program two ways to do the same thing, especially when one of them isn't optimal.

It is optimal when you are trying to do what I just wanted to do xD
But well I let it slide. Since its just extra 2 lines of code.