Strange behavior with INT-Values in a MySQL-Database

  1. 3 years ago

    Frank K

    26 Dec 2015 Pre-Release Testers, Xojo Pro Germany

    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.

  2. Eric B

    26 Dec 2015 Pre-Release Testers Addison, Texas
    Edited 3 years ago

    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.

  3. Frank K

    26 Dec 2015 Pre-Release Testers, Xojo Pro Germany

    Eric, I store the data in a named dictionary:

        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
          
          // Nächster Datensatz
          rs.moveNext
          
        wend

    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.

  4. Eric B

    26 Dec 2015 Pre-Release Testers Addison, Texas

    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).

  5. Eli O

    is not verified 26 Dec 2015 Europe (Berlin, Germany)

    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.

  6. 2 years ago

    Hal G

    23 Jun 2017 Pre-Release Testers, Xojo Pro, XDC Speakers CampSoftware.com

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

    @Tim D 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.

  7. Norman P

    23 Jun 2017 Xojo Inc, Pre-Release Testers, Xojo Pro Seeking work. npalardy@great-w...

    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 ?

  8. Hal G

    23 Jun 2017 Pre-Release Testers, Xojo Pro, XDC Speakers CampSoftware.com

    @Norman P Have you filed a bug report with suitable steps ?

    Not yet I just learned what the problem was. @Tim D 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.

  9. Hal G

    24 Jun 2017 Pre-Release Testers, Xojo Pro, XDC Speakers CampSoftware.com

    Added Feedback Case #48491

  10. 5 months ago

    Leonidas B

    Oct 30 Macaé, RJ, Brazil

    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...

  11. Leonidas B

    Nov 1 Macaé, RJ, Brazil

    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

or Sign Up to reply!