CommitTransaction: cannot commit - no transaction is active

[muuvmuuv]

Describe the bug

I am doing a simple db.execute DELETE FROM table; in the browser and got this since a few release (cannot tell since when, sry). Any idea what could cause this? I do a PRAGMA foreign_keys = OFF; before (to easier wipe all data in that case). All other SQL execute just fine before.

Execute Error Error: Execute: ExecuteSQL: ExecuteSQL: CommitTransaction: cannot commit - no transaction is active : RollbackTransaction: cannot rollback - no transaction is active

To Reproduce
Steps to reproduce the behavior:

1. Simple delete from

Expected behavior
More detailed errors...

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: macOS
• Browser: Brave
• Version: latest

Smartphone (please complete the following information):

• Device: [e.g. iPhone6]
• OS: [e.g. iOS8.1]
• Browser [e.g. stock browser, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

[jepiqueau]

@muuvmuuv the PRAGMA foreign_keys is manager directly by the plugin. Can you share a simple app demonstrating the issue

[muuvmuuv]

What do you mean, I don't need to do this manually while using execute? Isn't this a bit antipattern to be on by default?

[jepiqueau]

@muuvmuuv Is it in the iOS part of the plugin that you get the error ? Did you set-up a sync table ? Do you have a sql_deleted field in your table? It would be easier for me to progress if i got a shred example.

[muuvmuuv]

Browser, didn't test on iOS yet, had no time to dig into it further...
We don't have either a sync table or sql_deleted field.

[jepiqueau]

@muuvmuuv are you using typeorm?

[muuvmuuv]

We aren't using any ORM.

[jepiqueau]

@muuvmuuv Are you also use autosave? look at typeorm issue#9100.
Where you create the jeep-sqlite element can you try with autosave = false and do a call to autoSave method only when a certain number of transaction are done and tell me if it works
ie

    const jeepEl = document.createElement("jeep-sqlite");
    document.body.appendChild(jeepEl);
    jeepEl.autoSave = false;

[jepiqueau]

@muuvmuuv The autosave = true each time there is a transaction which is committed an export of the database is done to localforage to store in the jeepSqliteStore. If there is a new transaction started during this process there is a chance that you get that message depending also of the size of the database

[jepiqueau]

@muuvmuuv Can you try with jeep-sqlite@2.3.5 and tell me if it works

[muuvmuuv]

Tried <jeep-sqlite wasmpath="assets" autoSave="false"></jeep-sqlite> but same error.

Tested 2.3.5, same error, but now it is coming earlier on login when data is written.

I will create a REPL now and try to encapsulate it. We have a very large database service with a lot of legacy code, maybe it's just our end.

[muuvmuuv]

It seems to be just the ALTER or DROP:

[muuvmuuv]

Kinda strange, if I reload a few times the errors goes away, I can see that it is running our custom db-revisions step by step but runs into this error after each interation. I wonder if its an async issue.

[jepiqueau]

@muuvmuuv Are you sure that you do not use BEGIN TRANSACTION, COMMIT TRANSACTION or ROLLBACK TRANSACTION in a sql statement?

[jepiqueau]

@muuvmuuv are you using jeep-sqlite alone or through the @capacitor-community/sqlite

[jepiqueau]

@muuvmuuv if it is through @capacitor-community/sqlite can you show me the code where you instantiate 'jeep-sqlite'

[muuvmuuv]

Through @capacitor-community/sqlite:

  async init() {
    try {
      if (isWeb) {
        await customElements.whenDefined("jeep-sqlite")
        const jeepSqliteElement = document.querySelector("jeep-sqlite")
        if (jeepSqliteElement != null) {
          await this.sqlite.initWebStore()
        }
      }

      // Migrate cordova database
      await this.migrateCordovaDatabase()

      // Make sure JavaScript and native connections are in sync
      const connection = await this.sqlite.checkConnectionsConsistency()
      this.log.debug(LogStack.DatabaseService, "Connection consistent", connection.result)

      // Connect to database
      const connected = await this.sqlite.isConnection(this.DB_NAME, false)
      if (connected.result) {
        this.log.info(LogStack.DatabaseService, "Retrieving existing connection...")
        this.#database = await this.sqlite.retrieveConnection(this.DB_NAME, false)
      } else {
        this.log.info(LogStack.DatabaseService, "Creating new DB connection...")
        this.#database = await this.sqlite.createConnection(
          this.DB_NAME,
          false,
          "no-encryption",
          this.DB_VERSION,
          false,
        )
      }
      await this.#database.open()

      // Run echo test
      await this.sqlite.echo("entergon")

      // Execute revisions
      const executedRevisions = await this.getRevisions()
      const revisionFactory = []
      this.environmentInjector.runInContext(() => {
        for (const Revision of revisions) revisionFactory.push(new Revision(executedRevisions))
      })
      for (const revision of revisionFactory) {
        await revision.run()
      }
    } catch (error) {
      this.log.error(LogStack.DatabaseService, `Fatal`, error)
      throw error
    }
  }

[muuvmuuv]

We don't use TRANSACTION but executeSet instead. And we don't call any TRANSACTION calls manually.

[muuvmuuv]

So, I think I got it, it must be something with JavaScript runtime, these (Revision) run in a loop. Each screenshot is a reload. So its executing the SQL for one revision class and fails on the second loop revision entry. Reload will continue where it failed and failed right after...

[jepiqueau]

@muuvmuuv can you modify the first executeSQL which fails by adding the transaction parameter to false. The first error

Execute Error Error: Execute: ExecuteSQL: ExecuteSQL: CommitTransaction: cannot commit - no transaction is active : RollbackTransaction: cannot rollback - no transaction is active

seems to indicate that you run executeSQL and not executeSet
can i see the databaseService it may wrongly log executeSQL in the executeSet method

[jepiqueau]

@muuvmuuv you may look at ionic7-angular-sqlite-starter to look how i instantiate the plugin and the app through the InitializeAppService

[muuvmuuv]

Setting transaction to false on execute not executeSet solved it. Thought that its "false" by default since it accepts not set and undefined?

[muuvmuuv]

Yeah, followed that at the start but our database service has changed a lot since the start of switching to your lib, so might not seen that explicit parameter declaration

[muuvmuuv]

Thanks a lot of getting this sorted step by step! You are amazing man, good problem solver! :)

[jepiqueau]

@muuvmuuv the idea of setting transaction to false on any execute, executeSet and run was made for the use with typeOrm.
it also can be use to able a developer to manage itself the transaction through the BEGIN TRANSACTION, COMMIT TRANSACTION & ROLLBACK TRANSACTION through execute method

[jepiqueau]

@muuvmuuv take care that if you set it to false the execute is made obviously without Transaction process

[muuvmuuv]

Yeah, will keep that in mind, but until now that is fine, we will switch to an ORM in a future version of our app but now its only legacy code so no need to put that energy in.

[jepiqueau]

@muuvmuuv Ok you are welcome