• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2142
  • Last Modified:

MySQL BIGINT with jdbc causing errors when reaching higher values


I have a MySQL database with alot of records (we have an cell experiment running that collects millions of datapoints and pushes them into the database). The database is being updated using a Java program using com.mysql.jdbc. The primary key of a table reached 2147457154 and would need to go even higher. I used a BIGINT for ALL integer values just in case.

The system is a CentOS 5.4 x64 and so is mysql so all are 64bits.

The JDBC part uses perpared statements with getLong  and setLong so that should be ok as BIGINT is 64bit.

Now the problem is when I reach the 2147457154 limit the code throws an exception (see code snippet).

I read that jdbc doesnt handle long values properly. Can someone help how I can continue the data insertion since I still have a lot of data to go through and this is causing alot of downtime.


com.mysql.jdbc.exceptions.jdbc4.MySQLDataException      : '2.147483649E9' in column '1' is outside valid range for the datatype INTEGER.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct      orAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC      onstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.Util.getInstance(Util.java:384)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ResultSetImpl.throwRangeException(ResultSetImpl.java:7      971)
        at com.mysql.jdbc.ResultSetImpl.parseIntAsDouble(ResultSetImpl.java:7200      )
        at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2703)

Open in new window

  • 3
  • 2
2 Solutions
westerlikeAuthor Commented:
I tried restarting MySQL, no luck. I just can't get it past the ID. Maybe using another JDBC source or forcing type conversion?

I am not using JetProfiler.
> I read that jdbc doesnt handle long values properly.

   Can you elaborate? Do you mean a particular driver, implementation, ...?
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

westerlikeAuthor Commented:
Yes. I read somewhere that JDBC had issues with the BIGINT and long conversion as it doesn't know how to determine whether its signed or unsigned and uses INT instead of LONG.

I did some digging around and found the workaround by using unsigned longs on all member variables and it seems to resolve the solution (until it breaks again)

Well glad you figured out a solution.  
westerlikeAuthor Commented:
Thanks for the help. I'll accept both your comments as you guys were part of the brainstorming procedure.


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now