Fix Vapor MySQL Assertion Failed: Statement Not Closed

Discover why you might encounter an "Assertion failed: Statement not closed" error when working with Vapor and MySQL-NIO, and learn how to resolve this common issue.

Understanding the "Assertion Failed: Statement Not Closed" Error in Vapor MySQL-NIO

If you're a developer using the Vapor web framework with MySQL-NIO, you might occasionally run into a perplexing error message: "Assertion failed: Statement not closed". This usually pops up during the deinitialization of MySQLQueryCommand, indicating that a SQL statement was not properly closed after execution. While it might seem like a minor glitch, it can halt your application's execution and lead to unexpected crashes. This article dives deep into the root causes of this assertion failure, explores the specific conditions that trigger it, and provides practical solutions to ensure your Vapor applications interact smoothly with your MySQL database.

This assertion failure specifically targets the MySQLQueryCommand.swift file within the mysql-nio package, at line 224. The error message itself is quite descriptive: it tells you that a particular SQL statement was left open when it should have been closed. This typically happens when the deinit (deinitializer) of MySQLQueryCommand is called, and it checks if the underlying statement has been finalized. If it hasn't, the assertion fails, and your application crashes. It's a safeguard to ensure that resources are managed correctly and that no dangling database connections or operations are left hanging.

The Scenario: A Specific SQL Query Trigger

The problem often arises not from a general misuse of the database, but from a specific type of SQL query. In the context reported by a user, the problematic query involved several joins and a rather peculiar SELECT clause. Let's break down the provided example to understand why it might be causing this issue:

SELECT
    c.id = 1 -- comparing with one, or almost, any other comparison.
FROM
    t1 as a
    JOIN (SELECT * FROM t2 WHERE status = '1' AND number = 0) as b ON a.id = b.t1_id
    JOIN (SELECT * FROM t1) as c ON c.group_id = a.group_id
ORDER BY
    (a.text LIKE "") DESC

Several key elements in this query are worth noting:

1. Complex Joins: The query uses multiple JOIN operations, including a subquery (SELECT * FROM t2 WHERE status = '1' AND number = 0) and another subquery (SELECT * FROM t1). Complex join conditions can sometimes lead to intricate parsing and execution paths within the database driver.
2. ORDER BY Clause with a Function: The ORDER BY (a.text LIKE "") DESC clause involves a string comparison (LIKE). While not inherently problematic, combined with other factors, it might contribute to the issue.
3. The Crucial Part: SELECT c.id = 1: This is highlighted as the most significant trigger. The SELECT statement is not just retrieving a column but performing a boolean comparison (c.id = 1). The user also noted that using other comparison operators (<, >, <=, >=, !=) resulted in the same problem, whereas arithmetic operators (+, -, *, /) worked fine. This suggests that the way mysql-nio handles the results of comparison expressions within a SELECT list might be where the issue lies.

Reproducing the Error: A Step-by-Step Guide

To effectively debug and fix this, replicating the error is essential. The provided steps offer a clear path:

1. Database Setup: Ensure you have a MySQL database running. The reported version was MySQL 9.4.0, but it's possible other versions might be affected. Create two tables, t1 and t2, with the specified schema:

   CREATE TABLE `t1` (
     `id` int unsigned NOT NULL AUTO_INCREMENT,
     `group_id` int DEFAULT NULL,
     `text` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
   
   CREATE TABLE `t2` (
     `id` int unsigned NOT NULL AUTO_INCREMENT,
     `t1_id` int DEFAULT NULL,
     `status` enum('1','0') DEFAULT NULL,
     `number` int DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
   

2. Populate Data: Insert sample data into these tables:

   INSERT INTO `t1` (`id`, `group_id`, `text`)
   VALUES
       ('1', '1', 'something');
   
   INSERT INTO `t2` (`id`, `t1_id`, `status`, `number`)
   VALUES
       ('1', '1', '1', '0');
   

3. Execute the Query in Vapor: Within a Vapor endpoint, execute the problematic SQL query directly using SQLDatabase.raw():

   let q: SQLQueryString = """
   SELECT
       c.id = 1
   FROM
       t1 as a
       JOIN (SELECT * FROM t2 WHERE status = '1' AND number = 0) as b ON a.id = b.t1_id
       JOIN (SELECT * FROM t1) as c ON c.group_id = a.group_id
   ORDER BY
       (a.text LIKE \"\") DESC
   """
   
   try await (req.db as! any SQLDatabase)
       .raw(q)
       .run()
   

4. Trigger the Crash: Send a request to the endpoint that executes this code. You should then observe the "Assertion failed: Statement not closed" error, followed by a program crash.

The Root Cause: Handling of Comparison Expressions in SELECT

The core of the issue appears to stem from how mysql-nio parses and processes the results of comparison expressions when they are used directly within the SELECT list of a query. When you execute a query like SELECT c.id = 1 FROM ..., the database driver needs to interpret the result of this comparison, which is a boolean value (true or false).

It seems that in certain scenarios, particularly when the query itself returns results (as opposed to when the ON condition in a join might not match any rows, as observed in the original query), the mysql-nio library might not correctly finalize the statement after fetching the boolean comparison result. The deinit method of MySQLQueryCommand performs a check: `assert(self.statement.isClosed,