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
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC
        at java.lang.reflect.Constructor.newInstance(
        at com.mysql.jdbc.Util.handleNewInstance(
        at com.mysql.jdbc.Util.getInstance(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.SQLError.createSQLException(
        at com.mysql.jdbc.ResultSetImpl.throwRangeException(      971)
        at com.mysql.jdbc.ResultSetImpl.parseIntAsDouble(      )
        at com.mysql.jdbc.ResultSetImpl.getInt(

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theGhost_k8Database ConsultantCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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, ...?
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.