8
$\begingroup$

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

TABLE_CAT  TABLE_SCHEM  TABLE_NAME  COLUMN_NAME  DATA_TYPE  TYPE_NAME
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?

$\endgroup$
  • $\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$ – Sjoerd C. de Vries Mar 28 '17 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$ – Sjoerd C. de Vries Mar 28 '17 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$ – Chris Degnen Mar 28 '17 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$ – Sjoerd C. de Vries Mar 28 '17 at 20:17
  • $\begingroup$ What does SQLColumns[conn,"key"] return? $\endgroup$ – Edmund Mar 29 '17 at 2:31
7
$\begingroup$

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 support@wolfram.com

$\endgroup$
  • $\begingroup$ The table name wasn't 'key' in the original instance. $\endgroup$ – Chris Degnen Mar 28 '17 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$ – Sjoerd C. de Vries Mar 28 '17 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$ – Sjoerd C. de Vries Mar 28 '17 at 21:34
  • $\begingroup$ Reported to support@wolfram.com $\endgroup$ – Chris Degnen Mar 29 '17 at 11:20
  • $\begingroup$ @chris Small update to show that SQLInsert is the culprit. $\endgroup$ – Sjoerd C. de Vries Mar 29 '17 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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