CJException: 3878257648 out of range for int

[muescha]

Setup

dolt as database created databse restdb added user restadmin used source https://github.com/springframeworkguru/spring-6-rest-mvc/tree/77-flyway-intit-script run application with profile: localmysql application-localmysql.properties

no tables needed in restdb for this setup to run.

MySQL

no error

Error

com.mysql.cj.exceptions.CJException: 3878257648 out of range for int

dolt log with log level debug:

DEBU[42019] preparing query                               paramsCount=9 query="INSERT INTO `restdb`.`flyway_schema_history` (`installed_rank`, `version`, `description`, `type`, `script`, `checksum`, `installed_by`, `execution_time`, `success`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)" statementId=13
DEBU[42771] Starting query                                connectTime="2024-06-28 13:58:27.503963 +0200 CEST m=+42015.283576001" connectionDb=restdb connectionID=52 query="INSERT INTO `restdb`.`flyway_schema_history` (`installed_rank`, `version`, `description`, `type`, `script`, `checksum`, `installed_by`, `execution_time`, `success`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"

asSql():

INSERT INTO `restdb`.`flyway_schema_history` 
(`installed_rank`, `version`, `description`, `type`, `script`, `checksum`, `installed_by`, `execution_time`, `success`) 
VALUES 
(1, '1', 'init-mysql-database', 'SQL', 'V1__init-mysql-database.sql', -416709648, 'restadmin', 32, 1)

query command:

17 0d 00 00 00 00 01 00     . . . . . . . .
00 00 00 00 01 03 00 fd     . . . . . . . .
00 fd 00 fd 00 fd 00 03     . . . . . . . .
00 fd 00 03 00 01 00 01     . . . . . . . .
00 00 00 01 31 13 69 6e     . . . . 1 . i n
69 74 2d 6d 79 73 71 6c     i t - m y s q l
2d 64 61 74 61 62 61 73     - d a t a b a s
65 03 53 51 4c 1b 56 31     e . S Q L . V 1
5f 5f 69 6e 69 74 2d 6d     _ _ i n i t - m
79 73 71 6c 2d 64 61 74     y s q l - d a t
61 62 61 73 65 2e 73 71     a b a s e . s q
6c f0 83 29 e7 09 72 65     l . . ) . . r e
73 74 61 64 6d 69 6e 20     s t a d m i n  
00 00 00 01                 . . . .

the command creating the error info is running in com.mysql.cj.ServerPreparedQuery.java Line 278:

NativePacketPayload resultPacket = this.session.getProtocol().sendCommand(packet, false, 0);

result packet:

ff 51 04 23 48 59 30 30     . Q . # H Y 0 0
30 33 38 37 38 32 35 37     0 3 8 7 8 2 5 7
36 34 38 20 6f 75 74 20     6 4 8   o u t  
6f 66 20 72 61 6e 67 65     o f   r a n g e
20 66 6f 72 20 69 6e 74       f o r   i n t

PS: I missing this error in the dolt log

[muescha]

Note: if I run this statement direct in the database console (in IntelliJ IDEA) connected to the dolt database there is no error:

INSERT INTO `restdb`.`flyway_schema_history` 
(`installed_rank`, `version`, `description`, `type`, `script`, `checksum`, `installed_by`, `execution_time`, `success`) 
VALUES 
(1, '1', 'init-mysql-database', 'SQL', 'V1__init-mysql-database.sql', -416709648, 'restadmin', 32, 1)

so there must be somehow some differences if the command goes through the mysql - jdbc connection as packet

[muescha]

2024-06-28T14:31:14.536+02:00 DEBUG 64873 --- [  restartedMain] o.f.c.i.s.DefaultSqlScriptExecutor       : 
Executing SQL: CREATE TABLE `restdb`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    CONSTRAINT `flyway_schema_history_pk` PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB

[muescha]

PS: 3878257648 is actually the unsigned interpretation of -416709648

[muescha]

result at MySQL:

installed_rank	version	description	type	script	checksum	installed_by	installed_on	execution_time	success
1	1	init-mysql-database	SQL	V1__init-mysql-database.sql	-416709648	restadmin	2024-06-28 13:04:05	40	1

[fulghum]

Hi @muescha, thank you for the error report! 🙏 Looks like we've got enough to repro here. We'll work on a repro today and see what's going on. My first guess is that the we aren't sending a response metadata bit that the MySQL Connector/J library needs to treat the int value as unsigned. We'll keep you updated with what we find.

[fulghum]

I think I've got a good repro using a simple Connector/J JDBC example. It looks like it's something on the prepared statement codepath. I'll keep digging in to get a tighter repro and get into the debugger and hopefully this will be a quick one we can get fixed for you.

[fulghum]

Well, I spoke a little too soon... The issue I found is a syntax support issue for the EXECUTE statement, but I don't think the code is actually executing through that code path – I think it's using the COM_PREPARE/COM_EXECUTE codepath instead (otherwise I think we'd be seeing a different error). I haven't been able to repro on that codepath yet, so I'm going to see if I can get the full flyway repro working, and will see what we can find from there. I do see a few small differences in the column metadata returned between MySQL and Dolt, so I suspect that is the issue, but getting a repro will tell for sure.

[fulghum]

@muescha – can you help me out with the exact commands you're running for the repro? I've got the spring-6-rest-mvc repo checked out on the 77-flyway-intit-script branch, and I'm guessing the next step is to run flyway migrate, but it's complaining about not finding the migration files. Can you please share some more repro steps there?

[muescha]

After checking out the code and installing the Maven dependencies, just run the application with the localmysql profile. Spring Boot will then perform the Flyway checks at startup. No existing tables are needed, just the database is enough.

Here is my Run Configuration:

[muescha]

I double checked it with the branch (other than my step by step growing tutorial code) and have some notes to run the code:

Notes for Lombok:

• you need to install the Lombok Plugin
• you need to activate the: [x] enable annotation processing:

• but it fails with similar code:

java.sql.SQLException: 3075161371 out of range for int

[muescha]

I tried to isolate the bug with a simple Java program in IssueOutOfRangeInt, but it ran without error.

[fulghum]

Thanks for the extra details. I think I'm getting closer, but unfortunately, I'm still unable to repro this...

It looks like the Spring functionality you're using is only available in IntelliJ Ultimate, so I've started a trial and have been trying to reproduce there. I've installed Lombok and I've ensured annotation processing is enabled. When I run the main Spring class, I get an error about the beer table not existing. When I manually create the beer table (using the SQL from the generated update.sql file at the root of the project), I can start the project up and it doesn't generate any errors.

I think we're going to need more specific repro steps in order to trigger this one and figure out what's going on. Can you start with a fresh check out of the project you're using and a fresh Dolt database, and give us the exact commands to run on a command line to trigger the error you're seeing? For example, can you trigger this with Maven commands? That's going to be the easiest way to ensure we're running the exact same steps you're running.

[muescha]

If the update.sql file is generated, you might have used the default application.properties.

You need to specify the profile application-localmysql.properties in the run dialog by setting "Active Profiles" to localmysql (see screenshot).

Regarding the Maven command: I will be away from my computer for a week. I will provide it as soon as possible.

[fulghum]

Hi @muescha, hope all is well on your side! Just following up to see if you have a chance to get more exact repro steps for us. At your convenience of course – no rush. We'd just like to get this issue figured out and fixed for you. 😄

[muescha]

Sorry for the delayed response.

To run the project without any additional installations, use this command from the root of the project:

JAVA_HOME=/Users/muescha/.asdf/installs/java/openjdk-22 ./mvnw spring-boot:run -Dspring-boot.run.profiles=localmysql

Make sure to point JAVA_HOME to your locally installed openjdk-22.

2024-07-30T16:14:31.584+02:00  WARN 36539 --- [  restartedMain] com.zaxxer.hikari.pool.ProxyConnection   : RestDB-Pool - Connection com.mysql.cj.jdbc.ConnectionImpl@7f30171b marked as broken because of SQLSTATE(HY000), ErrorCode(1105)

java.sql.SQLException: 3075161371 out of range for int
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) ~[mysql-connector-j-8.3.0.jar:8.3.0]

[fulghum]

Thanks @muescha! I've got a repro now 😎

I was able to repro the error using the command above, and after looking at the packets on the wire, I see that the Dolt sql-server is returning the 3075161371 out of range for int error. I was also able to simplify the repro with just preparing a statement that inserts -1219805925 into an int field using a prepared statement. (I also learned that the MySQL JDBC driver won't use real, server-side prepared statements unless you set the useServerPrepStmts=true connection property 🤷🏻‍♂️)

Now that I've got a repro, I'm going to dig in and see what Dolt is doing differently than MySQL. I'll keep you updated with what I find.

[fulghum]

I've got a fix in place for this – just doing some more digging to see if other places are affected and writing some tests. I'll get a PR out today for it.

The issue is in our fork of Vitess, which is parsing the values from the MySQL wire protocol to bind to the prepared statement. It wasn't casting the parsed value to an int32 before it packaged it up into a larger numeric type, so the sign bit didn't get interpreted correctly.

[fulghum]

The fix for this issue has been released in Dolt v1.42.7.

Thanks for taking the time to report this one and for all the help getting us a repro! 🙏 This was a really great find.

I'll go ahead and resolve this, but let us know if you hit any other issues and we'll be happy to help!

[muescha]

Thx a lot - it can confirm it is fixed with 1.42.7