BoxLang gobbling DB connections

BoxLang
1

Using ortussolutions/boxlang:miniserver-snapshot and bx-mysql (^1.0.0-SNAPSHOT). Both fresh installed in the last coupla hours, as I know those version numbers aren’t much help.

My DSN config is thus:

this.datasources["dsn1"] = {
    driver = "mysql",
    properties = {
        host = "database.backend",
        port = 3306,
        database = server.system.environment.MARIADB_DATABASE,
        username = server.system.environment.MARIADB_USER,
        password = server.system.environment.MARIADB_PASSWORD,
        custom = {
            useUnicode = true,
            characterEncoding = "UTF-8",
            noAccessToProcedureBodies = true
        }
    }
}

this.datasource = "dsn1"

Every time I call queryExecute, ~10 new connections are opened on the DB, and stay open.

All I’m running is:

SELECT CURDATE() AS `current_date`

After a dozen or so calls, the DB tells me to f*** off, which - I think - is probably legit.

2

Dang! Thanks for the report.

Question: Is this on multiple requests, or within a single request? i.e., if you run two queries on the same datasource within the same request, does it open 10 connections or 20?

BoxLang uses HikariCP for connection pooling, which by default will open 10 connections to populate the connection pool. However, those 10 connections should either close at the end of the request OR get reused in subsequent requests. We clearly have a bit of work to do with our connection pooling.

Finally, we plan to allowing configuring HikariCP from the datasource configuration. It just hasn’t happened yet as I’ve been busy testing and fixing other JDBC items.

3

I wrote this up here, just to keep track of it:

https://ortussolutions.atlassian.net/browse/BL-163

1 Like
4

It was literally one integration test (in one request) causing it. Each run of the test (another request) creates another 10 connections.

I am seeing this via:

SELECT * FROM information_schema.PROCESSLIST;

And getting something like:

13 user1 172.20.0.2:34466 db1 Sleep 4 4209.114 0 0 0.000 79592 80200 0 25 76
12 user1 172.20.0.2:34464 db1 Sleep 4 4240.624 0 0 0.000 79592 80200 0 24 75
11 user1 172.20.0.2:34448 db1 Sleep 4 4272.257 0 0 0.000 79592 80200 0 23 74
10 user1 172.20.0.2:34440 db1 Sleep 4 4303.698 0 0 0.000 79592 80200 0 22 73
9 user1 172.20.0.2:34436 db1 Sleep 4 4335.094 0 0 0.000 79592 80200 0 21 72
8 user1 172.20.0.2:34430 db1 Sleep 4 4366.665 0 0 0.000 79592 80200 0 20 71
7 user1 172.20.0.2:34426 db1 Sleep 4 4398.452 0 0 0.000 79592 80200 0 19 70
6 user1 172.20.0.2:34410 db1 Sleep 4 4430.140 0 0 0.000 79592 80200 0 18 69
5 user1 172.20.0.2:34396 db1 Sleep 4 4524.138 0 0 0.000 79592 80200 0 17 68
4 user1 172.20.0.2:34388 db1 Sleep 4 4531.421 0 0 0.000 79592 80200 0 15 67
3 user1 172.20.0.1:35036 db1 Query 0 Filling schema table SELECT * FROM information_schema.PROCESSLIST LIMIT 0, 100 0.383 0 0 0.000 146392 231008 0 26 … 66

I wondered if it was cos of that DSN misconfig I had (other thread, now resolved), but it’s still doing it.

This is my test class:

import testbox.system.BaseSpec

component extends=BaseSpec {

    function run() {
        describe("Tests the DB", () => {
            it("can connect to the DB", () => {
                var result = queryExecute("SELECT @@VERSION as version")

                expect(result).toHaveLength(1)
                expect(listFindNoCase(result.columnList, "version")).toBeTrue()
                expect(result.version[1]).toContain("mariadb")
            })
		})
    }
}

And the DSN config is now:

this.datasources["dsn1"] = {
    driver = "mysql",
    host = "database.backend",
    port = 3306,
    database = server.system.environment.MARIADB_DATABASE,
    username = server.system.environment.MARIADB_USER,
    password = server.system.environment.MARIADB_PASSWORD,
    custom = {
        useUnicode = true,
        characterEncoding = "UTF-8",
        noAccessToProcedureBodies = true
    }
}

this.datasource = "dsn1"

(note the properties property is gone).

If I duplicate that test case so that it’s making two queryexecute calls in the request, I still only get the ten connections (not 20).

5

Ah should we switch our investigations to there? What I just said in my comment is probably better off on the ticket, yeah?

6

Having info in Jira is certainly better for longevity and tracking. I’m not complaining if we want to converse here, though.

7

I think once we establish it’s an actual issue and not just me being a dick (I always give this 50/50), then Jira is the way to go. I’ve copied my follow-up to there, and am watching the issue.

1 Like