Strange behavior with INT-Values in a MySQL-Database

I observed a strange behavior with INT-Values in a MySQL-Database. On my local test-server everything works fine. But when I deploy my webapp as standalone-app on my Live-server, something strange happens.

Instead of receiving the desired value “1” for the user-id, I get a value “4294967297”. I’ve got another id which should also be set to “1”, there I get “5764608175869263873”.

The complete received mysql-data looks like this (I get the result als JSONItem):

{"SEL_LoginCheck":{"4294967297":{"id_user":"4294967297","id_rolle":"1729382909745299457","modifiedTS":"2015-11-09 17:44:42","accountTS":"2015-11-09 16:45:13","user_name":"Frank","user_mail":"somemail@mail.de","passwort":"dcff31c85bde9760c5fd8961a45fd70a","optin":"1","optinTS":"2015-11-09 16:45:13"}}}

It should look like this (and looks like this on my test server)

{"SEL_LoginCheck":{"1":{"id_user":"1","id_rolle":"1","modifiedTS":"2015-11-09 17:44:42","accountTS":"2015-11-09 16:45:13","user_name":"Frank","user_mail":"somemail@mail.de","passwort":"dcff31c85bde9760c5fd8961a45fd70a","optin":"1","optinTS":"2015-11-09 16:45:13"}}}

When I change the format in my mysql database to BIGINT, the desired result shows up: I get the correct numbers.

I don’t think that this is a XOJO issue. Just wanted to give a hint and how I solved it. Gave me some sort of a headache.

Hmmm… isn’t JSON supposed to store integers without the quotation marks? Are you converting it to string/text first and then assigning it to an integer? If so, this could be the issue… as it appears to be having difficulties with the signed/unsigned integer conversions.

EDIT:

Example: … “id_user”:“4294967297” … would handle the 4294967297 as a string/text, where … “id_user”:4294967297 … would handle it as an integer.

Eric, I store the data in a named dictionary:

[code] while not rs.eof
// Daten in Named Dictionary einlesen
d = new Dictionary
for i = 1 to rs.FieldCount
d.Value(rs.IdxField(i).Name) = rs.idxField(i).StringValue

  next i
  
  // Datensatz wird durch den Wert von Feld keyfield eindeutig definiert
  jsonRS.Value(rs.Field(keyfield).StringValue) = d
  
  // Nchster Datensatz
  rs.moveNext
  
wend[/code]

This should not be the problem. After retrieving the data I store it as integer like this:

Session.LoginUserID = jsUser.Value("id_user").IntegerValue

And why does a change from INT to BIGINT solve the problem? That’s why I think it’s not a XOJO issue.

No, you’re doing exactly what I described. Using .StringValue and .IntegerValue… you’re converting between a string and an integer. During that conversion, something is breaking. My guess is going to be during the .IntegerValue, as it could be either 32-bit or 64-bit depending on the platform/build circumstances of your application. BIGINT can hold integer values of 32-bit and 64-bit; however, INT will break if assigned a 64-bit value (as the data rolls over during an overflow).

4294967297 is 1 above the maximum of an (unsigned) Int32.

So somewhere – either when converting the values from JSON to add them to the database or when extracting them from the database and converting them to JSON there is a Int32/UInt32/Int64/UInt64 conversion issue. I would do everything with UInt64, both in Xojo and in MySQL.

I just ran into this and have some more details to share after going insane for many hours.

@Tim Dietrich and I shared screens today and found out that on Linux, Int and MedInt fail, but TinyInt, SmallInt, BigInt, Real, Decimal, Double, and Float work as expected.

With the exact same Xojo Web App Project, I’ve been using Int with a Length of 1 in MySQL. When a Field has a 1 in it:

  • Debug on Mac, I get a 1
  • Compile, Upload, and Run on Linux, I get numbers with a ton of chars like -3128392055999430655, 140419660775425, 140277926854657. The numbers change quite a bit. Once we tried incrementing the 1 that was stored in the MySQL database and the big numbers incremented by the same amount but quitting and relaunching the app changed the base big number.

This really seems like a bug in the Xojo MySQL Linux plugin since it works on Mac Debugging as expected.

Dont use medium int
it has absolutely NO equivalent in Xojo (its 3 bytes long)

For mysql use Int with int32 and bigint with int64
Otherwise in a 64 bit app mysql Int is only 32 bits and Integer in Xojo is 64 bits so you may get truncation (going from Xojo to mysql) or sign extension (I hope) going from mysql to Xojo

That said these should probably be looked into to see what causes issues using them
Have you filed a bug report with suitable steps ?

Not yet I just learned what the problem was. @Tim Dietrich made a nice sample project while we were screen sharing. I’ll clean that up and create an sql dump file too and then create a but report.

Added <https://xojo.com/issue/48491>

I was just running into this issue today. Fought it for several hours… Any update on this? I saw that the feedback case was closed on 2017…

This is how I got the issue solved, in case someone needs it.

It turns out that retrieving INTEGER data fields from a MySQL database and handling them with Xojo corrupts the data somehow.

To prevent that, I had to make sure the INTEGERs were delivered as ‘INT64’ data.

In order to do that, I needed to change the SQL sentences I was using, making them look like this. Consider ‘myfield’ is an integer field:

SELECT CAST(myfield AS UNSIGNED) AS myfield FROM mytable