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.