• MySQL troubles
    7 replies, posted
Hi there, I've used this exact code before and struggle to understand why I am all of a sudden getting issues. From what I am aware I get this odd error for every new person that joins the server. It fails to create a entry in the database for them to save points or inventory items. [code] MySQL Error, Query: INSERT INTO players SET bananas = 0, steamid = 'STEAM_0:1:126816703', id = '4070338178; caused error: Duplicate entry '2147483647' for key 'PRIMARY' [/code] The SteamID and ID number will be different for each person as it should be, but then the ID in the next database entry to be created will be '2147483647' and will then fail to create an entry for anyone else. When it is deleted and someone joins, it will create this entry again. They can not use it for their data due to the fact their ID is '4070338178' for example and the database id is '2147483647' (and it is always this exact number without fail) This is the part that will create and entry to the database. As you can see the ID of the entry will be interpreted by function that will create a UniqueID out of everyones STEAM ID. [code] QueryDBCallback("INSERT INTO player SET bananas = 0, steamid = '"..self:SteamID().."', id = "..self:NumSteamID()..";", function() end); [/code] And this is the function that will create the UniqueID [code] local CacheID={}; local oldUniqueID = pmeta.UniqueID; function pmeta:UniqueID() return CacheID[self] or ( rawset( CacheID,self,oldUniqueID(self) ) )[self]; end pmeta.NumSteamID = pmeta.UniqueID; [/code] I was considering that this could be an issue with MySQLoo or my version of MySQL server, but I just can't see it. I'd appreciate all feedback and recommendations as this is a big problem and it is annoying me how it is only now happening.
The ID is exeeding the maximum 32 bit value, which is 2147483647 (Go ahead and Google the number). You can probably get around this by changing the ID's type from INT to BIGINT in the table structure.
Thank you for your reply. I actually figured this out while having a look through phpMyAdmin. I still don't understand why this is only happening all of a sudden. It was a fairly fresh database as well. Thank you for the BIGINT recommendation though, I didn't know about this. I'll try and update the table.
CRC32 returns an unsigned integer, and MySQL by default uses a signed integer. You can give it the unsigned specifier and it should work. It might have just happened now because all of the current IDs you've stored have had a CRC32 below the limit of a signed int. Side note; I'd suggest against using "UniqueID" as a primary key, or even as a means of identifying a playing at all. It uses a CRC32 of the steam id which will result in conflicts (DarkRP ran into this issue a while ago). I'd suggest using Player/SteamID64 or Player/AccountID as a primary key, I personally use the latter to not have to deal with casting when pulling from a database.
That's quite a good idea. I might use SteamID64. I'll need to set the column as an unsigned specifier still though, won't I?
Yes This part is up to you. There's arguments that you should always use surrogate keys, for example the format of your primary key can change as it's out of your control. I don't think valve is going to change there format any time soon so I'd say you're fine. One thing to remember about using SteamID64 in Gmod is that you're going to need to cast it if you ever pull it from the database. Lua uses double as its number type, thus SteamID64 can't be exactly represented as a number in Lua. This is in part why Player/SteamID64 returns a string instead of a number. MySQLOO makes note of this problem in its thread, Bigint fields now return as numbers. This might be problematic if you want to use a bigint as the primary key and select it from the server (such as steamid64). In these cases make sure to cast the bigint to a string in your SQL query like this: SELECT (CAST steamid64 as 'CHAR') as steamid64 FROM ...
AccountID can run into conflicts with SteamIDs from other universes - may never be a problem for a GMod server but there are still a few legacy accounts from PlayStation account migration.
Instead of using AccountID, I am querying for SteamID of individual players instead of Primary Key. Primary Key ID is now just a number going in single increments. This was how my Kopimi Shop worked, and never had problems with it so I just altered it to do this. Thank you all for your help, I'll post here again when I have seen that it is working properly.
Sorry, you need to Log In to post a reply to this thread.