Repository navigation
How to resolve the "Tried to send an out-of-range integer as a 2-byte value" error #1311
Description
I'm submitting a question about "Tried to send an out-of-range integer as a 2-byte value"
- [ jdbc driver] bug report
- feature request
Introduce
Jdbc execute a long/batch insert sql, like: ```insert into values(?,?,?),(?,?,?),(?,?,?)...```
but client throws an error: "Tried to send an out-of-range integer as a 2-byte value:" ...
When I review the source code, and I find the implementation like below:
Background
Maven pom.xml
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.5</version>
</dependency>
Source Code
[Line: 1442] QueryExecutorImpl.java
// ....
pgStream.sendInteger2(params.getParameterCount());
// ....
[Line: 235] PGStream.java
// ....
public void sendInteger2(int val) throws IOException {
if (val < Short.MIN_VALUE || val > Short.MAX_VALUE) {
throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);
}
_int2buf[0] = (byte) (val >>> 8);
_int2buf[1] = (byte) val;
pg_output.write(_int2buf);
}
....
Describe the issue
A clear and concise description of what the issue is.
I don't know why param count should be greater/equals than Short.MIN_VALUE (-32768)
or less/equals than Short.MAX_VALUE (32767).
When I execute batch sql (especially build lots of columns ),
this error must be occurred. I can not guess the check whether to protect the server side?
I think the jdbc driver client should not limit the length of statement.
We can use MAX_ALLOWED_PACKET parameter which is defined in MySQL server in the server side.
Wait for your response asap !!!
Java Version
java version "1.8.0_161"
Java(TM) SE Runtime Environment (build 1.8.0_161-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)
OS Version
Mac OSX
PostgreSQL Version
EnterpriseDB 9.3.17.42 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
To Reproduce
Steps to reproduce the behaviour:
Expected behaviour
A clear and concise description of what you expected to happen.
And what actually happens
Logs
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding
loggerLevel=TRACE&loggerFile=pgjdbc-trace.log to the connection string
Activity
davecramer commentedon Oct 17, 2018
Well the way PostgreSQL does batches having a batch size this large does not make sense.
Not sure what "We can use MAX_ALLOWED_PACKET parameter which is defined in MySQL server in the server side." has to do with anything?
davecramer commentedon Oct 17, 2018
The problem is that the server does not support batching so while you think you may get better throughput, you won't. Batching is an illusion provided by the driver.
davecramer commentedon Oct 17, 2018
you can use copy, and or use reWriteBatchedInserts https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
vlsi commentedon Oct 17, 2018
Did you know that
what can I dosounds very close to Russianводка найду(English pronunciation isвот кан ай ду) which meansI'll find Vodka?vlsi commentedon Oct 17, 2018
Would you please care to provide the test case then?
My wild guess is you are crafting
insert into table(...) values(?,?,?),(?,?,?),(?,?,?),(?,?,?),...(?,?,?)kind of query and you just hit the limit on the number of params per query.If that is the case, the only viable solution is to make the query shorter (and/or use copy).
vlsi commentedon Oct 17, 2018
Wire protocol uses
int16field to denote the number of binds. You can't fitLong.MAX_VALUEthere.I don't know the exact limit (e.g. see https://stackoverflow.com/a/6582902/1261287 or try yourself), however you won't be able to use more than 65536 binds per SQL anyway.
vlsi commentedon Oct 17, 2018
Welcome to https://www.postgresql.org/list/pgsql-general/ and/or to https://www.postgresql.org/list/pgsql-hackers/
pgjdbc does not specify the protocol. It is the database that specifies it.
jorsol commentedon Oct 17, 2018
@myware-io you should probably try COPY instead of a large INSERT (it's more easy than ask a change in the protocol 😉)
13 remaining items
pwagland commentedon Jun 1, 2022
Thanks @vlsi for the quick fix! I assume that this will be in 42.3.7? Is there any timeline for when this will be released and/or is there a nightly release that I can use to test this fix?
vlsi commentedon Jun 1, 2022
@pwagland , please try 42.3.7-SNAPSHOT.
I would go for releasing is week.
pwagland commentedon Jun 14, 2022
@vlsi , I finally managed to get to testing this, I discovered a small bug in the fix, the error message isn't quite right, as per my comment on the review: #2525 (review)
vlsi commentedon Jun 14, 2022
Thank you. I've fixed it in ec4af11
chore(deps): Update misk-deps-minor-patch
chore(deps): Update misk-deps-minor-patch