Parser Stack Overflow

[AdamHeavens]

I am getting the above error when trying to create a trigger, this only seems to be the case with complex queries. For example

CREATE TRIGGER delete_oldest_logs AFTER INSERT ON Logs WHEN (SELECT COUNT(*) FROM Logs) > 100000 BEGIN DELETE FROM Logs WHERE id = (SELECT id FROM Logs ORDER BY timestamp LIMIT 1); END; E (2126) LogDatabase: Trigger query preparation failed: parser stack overflow

The above query results in a parser stack overflow, All other queries are working correctly, include creating the table

        void LogDatabase::initDatabaseTable()
        {
            // Create database
            auto query = m_database.query("CREATE TABLE IF NOT EXISTS Logs ("
                                          "[id]    INTEGER,"
                                          "[timestamp] BIGINT,"
                                          "[level] INT UNSIGNED,"
                                          "[file] VARCHAR(64),"
                                          "[message] VARCHAR(1024),"
                                          "PRIMARY KEY([id] AUTOINCREMENT));");
            if (query)
            {
                if (query->exec())
                {
                    ESP_LOGI(TAG, "Logs table created successfully or already exists");
                }
                else
                {
                    ESP_LOGE(TAG, "Could not create Logs table : %s", query->lastError().c_str());
                }
            }
            else
            {
                ESP_LOGE(TAG, "Query preparation failed");
                return;
            }

            std::string trigger_sql =
                "CREATE TRIGGER delete_oldest_logs "
                "AFTER INSERT ON Logs "
                "WHEN (SELECT COUNT(*) FROM Logs) > 100000 "
                "BEGIN "
                "DELETE FROM Logs WHERE id = (SELECT id FROM Logs ORDER BY timestamp LIMIT 1); "
                "END;";

            ESP_LOGI(TAG, "Trigger SQL: %s", trigger_sql.c_str()); // Log the SQL query
            query->reset();
            query = m_database.query(trigger_sql);

            if (query)
            {
                if (query->exec())
                {
                    ESP_LOGI(TAG, "Trigger created successfully or already exists");
                }
                else
                {
                    ESP_LOGE(TAG, "Could not create Trigger : %s", query->lastError().c_str());
                }
            }
            else
            {
                ESP_LOGE(TAG, "Trigger query preparation failed: %s", m_database.lastError().c_str());
            }

            // Create parametrized queries
            m_insert_query = m_database.query("INSERT INTO Logs VALUES (NULL, ?, ?, ?, ?);");
            if (!m_insert_query)
            {
                ESP_LOGE(TAG, "Could not prepare insert query: %s", m_insert_query->lastError().c_str());
            }
        }

I have tried also with the later version of SQLite from the PR (Upgrade to SQLite 3.46.0)

Any help appreciated!

[siara-cc]

@AdamHeavens please see this: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/94 I have kept it pending because I am not sure if increasing YYSTACKDEPTH is the right solution.

[AdamHeavens]

Thank you, I have changed YYSTACKDEPTH to 30 and reverting to version 3.25 has resolved the issue