Solved Lasip why is it varbinary? TFS 1.4.2

[Erikas Kontenis]

What is the reasoning of lastip column being varbinary? How to translate it in human ip readable trough sql? I have tried chatgpt for this issue but I never can translate lastip to regular ip string. I also believe I haven't changed much code of this functionality so maybe lastip parse/set doesn't work properly in general for 1.4.2?

 

[Michael Orsino]

SELECT INET_NTOA(lastip) AS ip_address FROM players WHERE name = "Erikas";

This should work, but I actually ran it against a bunch of players on Dura and it came out with incorrect (but properly formatted) results. Converting in-game does seem to work though, so I guess the methods are different?

Just for fun, this is essentially what the INET_NTOA() function does:

SELECT
  (lastip >> 24) & 0xFF as first_octet,
  (lastip >> 16) & 0xFF as second_octet,
  (lastip >> 8) & 0xFF as third_octet,
  lastip & 0xFF as fourth_octet
FROM
  players
WHERE
  name = "Erikas"

Edit:
Alright so the different between what TFS does and what MySQL wants to do with INET_NTOA() is the byte order (reversed), you can read up about this by researching big & little-endian. Anyway, this is how we can get the correct full IP back in MySQL:

SELECT CONCAT(
  (lastip & 0xFF), '.',
  ((lastip >> 8) & 0xFF), '.',
  ((lastip >> 16) & 0xFF), '.',
  (lastip >> 24)
) AS ip_address
FROM players
WHERE name = "Erikas";

 

[0lo]

I used this website IP converter (https://www.silisoftware.com/tools/ipconverter.php), only you have to read the IP from the end. For example, 127.0.0.1 will be saved in your database as 16777343 (1.0.0.127), at least that's how it was in mine.

 

[Fjorda]

nvm, read the question wrong

 

[Erikas Kontenis]

Thank you a lot for your detailed answer Michael. I probably have fuckedup something with this IP inserting then... Because all my records are 0.0.0.0 ip. I will fix this issue on myself and will submit my experiences here when I'm done

Post automatically merged: Nov 4, 2023

Also, regarding your query. When I debug player save and the lastip line. The
player->lastIP.to_string() returns me "::ffff:127.0.0.1". Apart idk what means ::ffff: it looks so far so good. However your select query doesn't work for my test case like

SELECT CONCAT(
  (INET6_ATON("::ffff:127.0.0.1") & 0xFF), '.',
  ((INET6_ATON("::ffff:127.0.0.1") >> 8) & 0xFF), '.',
  ((INET6_ATON("::ffff:127.0.0.1") >> 16) & 0xFF), '.',
  (INET6_ATON("::ffff:127.0.0.1") >> 24)
) AS ip_address

And in game I see the IP address quite OK, however cannot parse it through SQL. So simple thing makes it so complicated

Post automatically merged: Nov 4, 2023

Allright! So saving the ip address works ok in my side. Finally after 10x attempt of annoying chatgpt prompting she gave me the solution!
SELECT INET_NTOA(CONV(SUBSTRING(Hex(INET6_ATON("::ffff:127.0.0.1")), -8), 16, 10)) AS ip_address
returns 127.0.0.1.
So in result such query retruns all expected results. Thank you a lot everyone for trying to help me. Perhaps it will be useful research for someone else too!

SELECT
INET_NTOA(CONV(SUBSTRING(Hex(lastip), -8), 16, 10)) AS lastip
from onlyfortress.players where name = 'Erikas'