Using Mathematica 11.1 to insert into a MySQL table with this schema...

example    Null         indexkey    Record       4          MEDIUMINT UNSIGNED
example    Null         indexkey    ID           -5         BIGINT

produces the following:

SQLInsert[conn, "indexkey", {"record", "id"}, {6631, 20042069300}];

SQLExecute[conn, "select * from indexkey where record = 6631"]

{{6631, -1432767180}}

However using Mathematica 7.0.1 this is the result:

{{6631, 20042069300}}

To get the same result in Mathematica 11.1 requires ToString, e.g.

SQLInsert[conn, "indexkey", {"record", "id"}, {6631, ToString[20042069300]}];

Is this a bug?

  • $\begingroup$ A clue may be the following truth: 1432767179 == FromDigits[Drop[IntegerDigits[20042069300, 2], 4] /. {1 -> 0, 0 -> 1}, 2]. Looks like only the 32 least significant bits are used of your input. The -1432767179 is the two's complement interpretation of the remaining bit pattern. $\endgroup$ Mar 28, 2017 at 19:38
  • $\begingroup$ So, the next questions would be: Are you accessing the same database in both situations? If not, are you sure you defined the 11.1 ID as BIGINT? If this does not answer it, you might try another driver. According to the documentation 11.1 comes with 3 MySQL drivers. You could try a different one. $\endgroup$ Mar 28, 2017 at 20:03
  • $\begingroup$ @SjoerdC.deVries Yes, it's the same database. Everything's the same except the Matematica version. It looks like a bug to me. $\endgroup$ Mar 28, 2017 at 20:14
  • $\begingroup$ Mathematica has these three connections: JDBCDrivers["MySQL(Connector/J)"], JDBCDrivers["MySQL(Drizzle)"], JDBCDrivers["MySQL(MariaDB)"]. Which one are you using, and did you try one of the others? $\endgroup$ Mar 28, 2017 at 20:17
  • $\begingroup$ What does SQLColumns[conn,"key"] return? $\endgroup$
    – Edmund
    Mar 29, 2017 at 2:31

1 Answer 1


It looks like a bug to me.

Any input larger than 2^31 -1 is truncated to 32 bits, whereas a BIGINT should be able to hold 64 bit numbers. What you see is the interpretation of that remainder in two's complement.

I've checked this for all three available drivers in V11.1 (Connector/J, Drizzle, and MariaDB), so a driver issue seems unlikely.

Furthermore, if you create a MySQL syntax error on purpose, e.g. by using a keyword (KEY) as table name, as I do below, the following error message is returned:

SQLInsert[conn, "key", {"record", "id"}, {6631, 20042069300}];

JDBC::error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

'key (record,id) VALUES (6631,-1432767180)' at line 1

So, it looks like the code generated by DatabaseLink already contained the wrong number before it was send to MySQL.

The fact that a raw SQLExecute works correctly supports the hypothesis that the issue lies with SQLInsert

SQLExecute[conn, "
 insert into aap (record,id)
 VALUES (6631, 20042069300)"]
SQLExecute[conn, "select * from aap where record = 6631"]

{{6631, 20042069300}}

I'd suggest reporting this to [email protected]

  • $\begingroup$ The table name wasn't 'key' in the original instance. $\endgroup$ Mar 28, 2017 at 21:26
  • $\begingroup$ @chris Yes, but that's not what I'm saying. I used a different name in my testing (i.e., in the creation of the table), but using 'key' in the subsequent query leads to a syntax error, the message of which reveals the location of the problem. $\endgroup$ Mar 28, 2017 at 21:30
  • $\begingroup$ The table I created was made with the following code: SQLCreateTable[conndrizzle, "aap", { SQLColumn["Record", "DataTypeName" -> "MEDIUMINT"], SQLColumn["ID", "DataTypeName" -> "BIGINT"] }] $\endgroup$ Mar 28, 2017 at 21:34
  • $\begingroup$ Reported to [email protected] $\endgroup$ Mar 29, 2017 at 11:20
  • $\begingroup$ @chris Small update to show that SQLInsert is the culprit. $\endgroup$ Mar 29, 2017 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.