Core error while doing a lot of sqlite queries

[LadyCailinBot]

CMDHELPER-3107 - Reported by redwall_hp

I'm writing a sort of exporter script to fill a new sqlite database with a large quantity of data exported from CommandHelper's persistance.db and a Java serialized store. Functionally, everything is in working order, but when the command runs and all of the functions run in series—executing a lot of sqlite queries in the process—CommandHelper starts throwing errors like this one after 30-60 seconds of work.

I'm fairly certain that it's related to the querying, since I can take an individual one of my functions—any of several—and invoke it excessively with a for loop to trigger the same sort of error. e.g. this one:

proc(_chce_export_channel_bulletins, @channel, @bulletins,
    foreach(@bulletins, @bulletin,
        @result = query("clanchatexport", "INSERT INTO `clanchat_bulletins` (channel, message) VALUES(?, ?);", @channel, @bulletin)
    )
)

As you can see, it's just looping through an array of strings and inserting the lines. Nothing out of the ordinary. (Except I'd use transactions if CommandHelper had an API for that, instead of going about it this inefficiently...) Now, if I fed this function enough data (or cheated and invoked it with the same data and a for loop several times) the errors start coming, and CommandHelper seemingly ceases to process any sqlite queries, even from other packages.

The error in question:

[20:35:44 ERROR]: Uh oh! You've found an error in Core.
This is an error caused while running your code, so you may be able to find a workaround, but is ultimately an error in Core itself.
The line of code that caused the error was this:
@name = '[removed]'
@channel = '[removed]'
@is_manager = 0
@uuid = '[removed]'
query('clanchatexport', 'INSERT INTO `clanchat_members` (channel, uuid, name, manager, subscribed) VALUES(?, ?, ?, ?, ?);', @channel, @uuid, @name, @is_manager, 1)
on or around /CraftBukkit/CH-1.8/plugins/CommandHelper/LocalPackages/ExportClanchat/auto_include.ms:122.
Please report this error to the developers, and be sure to include the version numbers:
Server version: 1.8.8-R0.1-SNAPSHOT;
CommandHelper version: 3.3.1-SNAPSHOT.2949-;
Loaded extensions and versions:
Core (version 3.3.1);
SKCompat (version 1.0.4);
Here's the stacktrace:
java.lang.NullPointerException
    at org.sqlite.NestedDB$CausedSQLException.fillInStackTrace(NestedDB.java:649)
    at java.lang.Throwable.<init>(Throwable.java:250)
    at java.lang.Exception.<init>(Exception.java:54)
    at java.sql.SQLException.<init>(SQLException.java:140)
    at org.sqlite.NestedDB$CausedSQLException.<init>(NestedDB.java:626)
    at org.sqlite.NestedDB._open(NestedDB.java:63)
    at org.sqlite.DB.open(DB.java:86)
    at org.sqlite.Conn.open(Conn.java:140)
    at org.sqlite.Conn.<init>(Conn.java:57)
    at org.sqlite.JDBC.createConnection(JDBC.java:77)
    at org.sqlite.JDBC.connect(JDBC.java:64)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at com.laytonsmith.core.functions.SQL$query.getConnection(SQL.java:136)
    at com.laytonsmith.core.functions.SQL$query.exec(SQL.java:171)
    at com.laytonsmith.core.Script.eval(Script.java:354)
    at com.laytonsmith.core.Script.eval(Script.java:323)
    at com.laytonsmith.core.Script.eval(Script.java:323)
    at com.laytonsmith.core.functions.DataHandling$foreach.execs(DataHandling.java:741)
    at com.laytonsmith.core.Script.eval(Script.java:313)
    at com.laytonsmith.core.Procedure.execute(Procedure.java:214)
    at com.laytonsmith.core.Procedure.cexecute(Procedure.java:165)
    at com.laytonsmith.core.Script.eval(Script.java:282)
    at com.laytonsmith.core.Script.eval(Script.java:323)
    at com.laytonsmith.core.functions.DataHandling$foreach.execs(DataHandling.java:741)
    at com.laytonsmith.core.Script.eval(Script.java:313)
    at com.laytonsmith.core.Procedure.execute(Procedure.java:214)
    at com.laytonsmith.core.Procedure.cexecute(Procedure.java:165)
    at com.laytonsmith.core.Script.eval(Script.java:282)
    at com.laytonsmith.core.Script.eval(Script.java:323)
    at com.laytonsmith.core.MethodScriptCompiler.execute(MethodScriptCompiler.java:1985)
    at com.laytonsmith.core.MethodScriptCompiler.execute(MethodScriptCompiler.java:1942)
    at com.laytonsmith.core.Script.run(Script.java:195)
    at com.laytonsmith.core.AliasCore.alias(AliasCore.java:167)
    at com.laytonsmith.commandhelper.CommandHelperServerListener.onServerCommand(CommandHelperServerListener.java:41)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306)
    at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502)
    at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487)
    at net.minecraft.server.v1_8_R3.DedicatedServer.aO(DedicatedServer.java:407)
    at net.minecraft.server.v1_8_R3.DedicatedServer.B(DedicatedServer.java:375)
    at net.minecraft.server.v1_8_R3.MinecraftServer.A(MinecraftServer.java:653)
    at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:556)
    at java.lang.Thread.run(Thread.java:744)

[LadyCailinBot]

Comment by LadyCailin

CH does have an API for transactions, because SQLite does. See this page: https://www.sqlite.org/lang_transaction.html

As to the rest of the error, I'll take a look.

[LadyCailinBot]

Comment by LadyCailin

How many items are in @bulletins?

[LadyCailinBot]

Comment by redwall_hp

Oh that's right...been working with ORMs too much lately.

The number in @bulletins varies, since the channel handler invokes it and the channelmember function as it processes each channel. But if you fill it with a handful of items and invoke it a couple dozen times, you should run into the issue.

[LadyCailinBot]

Comment by redwall_hp

Hm...transactions don't appear to work either. A simple example:

@result = query("clanchatexport", "BEGIN;")
@result = query("clanchatexport", "DROP TABLE test;")
@result = query("clanchatexport", "DROP TABLE test2;")
@result = query("clanchatexport", "COMMIT;")

Results in the following error:

14:25:07 INFO]: SQLException: [SQLITE_ERROR] SQL error or missing database (cannot commit - no transaction is active)

The line it indicates is the last one, with the COMMIT statement.