For years, I have been working with number of SQL Databases where if u pass a value to a column which is more than what it can hold. Then, all we expect is to throw an SQL error message..
But this seem to break in MYSQL. I mistakenly created a session_id column with integer and passed System.currentTimeMillis() as its value. Whatever the value from time in milli secs call, session_id always will always this one value “2147483647″. I debugged my code for hours to check where this conversion happens and found it is not happening there.
Suddenly, I remembered that db column is of int datatype and System.currentTimeMillis() will return a long which could be the source of problem. Hence, I changed the column datatype to BIGINT, everything started working fine. After some exploration, I found that when a int column value exceeds it acceptable limit MYSQL will downcast the value to max possible allowed. I don’t know why it has been designed this way but whatever the reason is I call this as a strange behavior when compared to other databases.
If I got an SQL error or at least warning in the beginning, the mistake would have been corrected in the first call itself and saved hell a lot of time. Hope others will remember the issue when working with MYSQL.
