[SPIGOT-7491] SQLite No Longer Supports Return Generated Keys Created: 25/Sep/23 Updated: 25/Dec/24 Resolved: 02/Oct/23 |
|
| Status: | Resolved |
| Project: | Spigot |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Minor |
| Reporter: | Mark Vogel | Assignee: | Unassigned |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Environment: |
This server is running CraftBukkit version 3888-Spigot-a0f3d48-e62905a (MC: 1.20.2) (Implementing API version 1.20.2-R0.1-SNAPSHOT) |
||
| Version: | 1.20.2 |
| Guidelines Read: | Yes |
| Description |
|
When executing an insert with the updated SQLite driver now yields an error when using RETURN_GENERATED_KEYS. try (PreparedStatement insert = connection.prepareStatement( "INSERT INTO `table_name`(`data`) VALUES (?)", Statement.RETURN_GENERATED_KEYS)) { insert.setObject(1, data); insert.executeUpdate(); try (ResultSet result = insert.getGeneratedKeys()) { // Exception is thrown here if (result.next()) { data.setId(result.getInt(1)); } } } The exception that is thrown is: java.lang.RuntimeException: java.sql.SQLFeatureNotSupportedException: not implemented by SQLite JDBC driver
at com.andavin.images.data.SQLDataManager.save(SQLDataManager.java:88) ~[?:?]
at com.andavin.images.data.SQLiteDataManager.save(SQLiteDataManager.java:39) ~[?:?]
at com.andavin.images.Images.addImage(Images.java:381) ~[?:?]
at com.andavin.images.command.CreateCommand.lambda$onInteract$5(CreateCommand.java:205) ~[?:?]
at org.bukkit.craftbukkit.v1_20_R2.scheduler.CraftTask.run(CraftTask.java:82) ~[spigot-1.20.2-R0.1-SNAPSHOT.jar:3888-Spigot-a0f3d48-e62905a]
at org.bukkit.craftbukkit.v1_20_R2.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:54) ~[spigot-1.20.2-R0.1-SNAPSHOT.jar:3888-Spigot-a0f3d48-e62905a]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
at java.lang.Thread.run(Thread.java:833) ~[?:?]
Caused by: java.sql.SQLFeatureNotSupportedException: not implemented by SQLite JDBC driver
at org.sqlite.jdbc3.JDBC3PreparedStatement.unsupported(JDBC3PreparedStatement.java:448) ~[sqlite-jdbc-3.43.0.0.jar:?]
at org.sqlite.jdbc3.JDBC3Statement.getGeneratedKeys(JDBC3Statement.java:357) ~[sqlite-jdbc-3.43.0.0.jar:?]
at com.andavin.images.data.SQLDataManager.save(SQLDataManager.java:147) ~[?:?]
at com.andavin.images.data.SQLDataManager.save(SQLDataManager.java:86) ~[?:?]
... 8 more
This exception is not thrown in 1.20.1 and looks like a regression in the new version of SQLite and the update to that version is possibly premature. |
| Comments |
| Comment by Phoenix616 [ 02/Oct/23 ] |
|
Well I personally would expect that to be 1.21 but sure, sounds good with me too. |
| Comment by md_5 [ 02/Oct/23 ] |
|
How about the next Minecraft release after the next SQLite release? |
| Comment by Phoenix616 [ 02/Oct/23 ] |
|
I think until 1.21 would be a g good plan and if they haven't adjusted anything until then prominently mention that in the update notes for that Spigot update as I would imagine that lots of people that use SQLite use that method too. |
| Comment by md_5 [ 26/Sep/23 ] |
|
I'm happy to revert it, but only if there is a well defined criteria when it will be updated (ie next Minecraft version after 3.44 is released), not sticking with the outdated driver for an indefinite period |
| Comment by Phoenix616 [ 26/Sep/23 ] |
|
This is a huge issues as tons of libraries expect that method to function as the Java jdbc API specifies it (as in that it returns the generated keys which SQLite does properly support, not sure why they went this route in violating the spec) See my statement on the repo's issue tracker too. More and more issues are popping up on that repo and related libraries using it/SQLite with it as a driver and I strongly doubt they will stick with that change. I personally would appreciate a downgrade to 3.42.0.1 until this issue is resolved or they have said that even after a flood of complaints that they stick with this... |
| Comment by Mark Vogel [ 25/Sep/23 ] |
|
Thanks for looking into that. I looked at the SQLite repository itself and neglected to look at the JDBC driver update. I find it very odd for them to remove a relatively ubiquitous method of retrieving keys (works across many different drivers) even if it wasn't a good way of doing it. Regardless, not a Spigot issue so this can be closed. I'll need to find a backwards compatible and cross-platform way of retrieving inserted IDs in the future. |
| Comment by md_5 [ 25/Sep/23 ] |
|
Looks intentional to me, so I'm not sure what we're supposed to do about this short of never updating SQLite again, unless there is some reason to think this will change.
https://github.com/xerial/sqlite-jdbc/commit/712a8a55f201954756de501a41a403d588b4d245 |