🐛 Bug: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? After upgrade to 0.204.1 (and .2) #7770

christophberger · 2024-03-01T15:02:53Z

Please confirm if bug report does NOT exist already ?

I confirm there is no existing issue for this

Steps to reproduce ?

Note: This issue may be similar to #7263, but the NocoDB version and the stack trace are different.

Start nocodb after upgrading to v0.204.1 or v0.204.2, respectively.

Result:

[Nest] 4869  - 03/01/2024, 11:18:50 AM   ERROR [ExceptionHandler] Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

Full output:

> nocodb
(node:4869) NOTE: We are formalizing our plans to enter AWS SDK for JavaScript (v2) into maintenance mode in 2023.

Please migrate your code to use AWS SDK for JavaScript (v3).
For more information, check the migration guide at https://a.co/7PzMCcy
(Use `nocodb --trace-warnings ...` to show where the warning was created)
[ncXcdbCreatedAndUpdatedSystemFieldsUpgrader 1709288270211] Upgrading base Getting Started(pttzmb21egwz63x,btp3s1hihe3iz6i) (1/1)
[ncXcdbCreatedAndUpdatedSystemFieldsUpgrader 1709288330228] Upgrading model 'Features'(mkecf72talq0b0e) from base 'Getting Started'(pttzmb21egwz63x}) failed
┌ Migration from 0111002 to 0111005 failed ─────────────────────────────────────────────────────────┐│                                                                                                   ││   Error                                                                                           ││   -----                                                                                           ││   KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you      ││   missing a .transacting(trx) call?                                                               ││   at Client_SQLite3.acquireConnection                                                             ││   (/snapshot/pkg-executable/node_modules/knex/lib/client.js:312:26)                               ││   at async Runner.ensureConnection                                                                ││   (/snapshot/pkg-executable/node_modules/knex/lib/execution/runner.js:287:28)                     ││   at async Runner.run                                                                             ││   (/snapshot/pkg-executable/node_modules/knex/lib/execution/runner.js:30:19)                      ││   at async View.clearSingleQueryCache                                                             ││   (/snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1369270)                         ││   at async Column.update                                                                          ││   (/snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1261462)                         ││   at async /snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1995281                  ││   at async Promise.all (index 0)                                                                  ││   at async /snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1994625                  ││   at async /snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1994480                  ││   at async i (/snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1892126)              ││                                                                                                   ││                                                                                                   ││   Please raise an issue in our github by using following link :                                   ││   https://github.com/nocodb/nocodb/issues/new?labels=Type%3A%20Bug&template=bug_report.md         ││                                                                                                   ││   Or contact us in our Discord community by following link :                                      ││   https://discord.gg/5RgZmkW ( message @o1lab, @pranavxc or @wingkwong )                          ││                                                                                                   │└───────────────────────────────────────────────────────────────────────────────────────────────────┘
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [NestFactory] Starting Nest application...
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] EventEmitterModule dependencies initialized +2ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] PassportModule dependencies initialized +0ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] MulterModule dependencies initialized +0ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] MulterModule dependencies initialized +0ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] ConfigHostModule dependencies initialized +0ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] DiscoveryModule dependencies initialized +0ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] ConfigModule dependencies initialized +0ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM     LOG [InstanceLoader] EventEmitterModule dependencies initialized +1ms
[Nest] 4869  - 03/01/2024, 11:18:50 AM   ERROR [ExceptionHandler] Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
    at Client_SQLite3.acquireConnection (/snapshot/pkg-executable/node_modules/knex/lib/client.js:312:26)
    at async Runner.ensureConnection (/snapshot/pkg-executable/node_modules/knex/lib/execution/runner.js:287:28)
    at async Runner.run (/snapshot/pkg-executable/node_modules/knex/lib/execution/runner.js:30:19)
    at async View.clearSingleQueryCache (/snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1369270)
    at async Column.update (/snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1261462)
    at async /snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1995281
    at async Promise.all (index 0)
    at async /snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1994625
    at async /snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1994480
    at async i (/snapshot/pkg-executable/node_modules/nocodb/dist/bundle.js:2:1892126)

Desired Behavior

nocodb should start the server.

Project Details

NocoDB used as docker : false
NocoDB version : 0.204.1 and 0.204.2
Database used in NC_DB URL : SQLite
Project was created by clicking : New Project by connecting to external database
Database on which spreadsheet is created : SQLite
OS on which NocoDB is running : macOS
Node.js version if running as node : v21.6.2
Database version : n/a

Attachments

No response

The text was updated successfully, but these errors were encountered:

pranavxc · 2024-03-02T09:05:38Z

We made a fix in following PR #7777. Could you kindly verify it by using following PR docker build?

nocodb/nocodb-timely:0.204.2-pr-7777-20240302-0712

christophberger · 2024-03-02T09:43:46Z

That was quick!
I'll test it when I'm at my desk

christophberger · 2024-03-02T22:43:38Z

It works! The error is gone, and I can start NocoDB.

For some reason, I didn't get my config back. I guess this is because I test with Docker but my previously working setup does not use Docker. I mapped the directory containing my noco.db to /usr/app/data in Docker but to no avail.

At least, the error is gone.

christophberger · 2024-03-08T11:43:34Z

Update for whom it may concern: The latest version 0.204.4 starts fine outside docker (that is, with my standard setup). The error is gone.

However, the config remains lost.

I therefore conclude that the upgrade process has erased all my config (views etc) from NocoDB's meta database.

o1lab · 2024-03-08T11:51:37Z

hey @christophberger : had you set NC_DB env variable and if not had you mounted the volume ? before and after this process.

If not, the metadata was stored in SQLite and is no longer present. Hope that clarifies.

christophberger · 2024-03-08T13:36:49Z

@o1lab Thanks for the reply.
No, NC_DB is not set.
I put noco.db and my data DB into one directory and mounted it to /usr/app/data. NocoDB can access the data DB, hence I assume it also can (and does) access noco.db.

If not, the metadata was stored in SQLite and is no longer present.

Not sure if I understand. Why is it no longer present?

o1lab · 2024-03-08T13:46:51Z

Ok, thanks for the reply. We would like to debug on what is happening here.

@pranavxc will come back on this as he has more context (he is off sick today).

pranavxc · 2024-03-08T13:51:45Z

@christophberger If possible can you share the docker command or docker-compose with the volume mounting arg which you tried?

christophberger · 2024-03-08T14:07:54Z

@pranavxc Sure:

nerdctl run -d --name newsdb \
  -v /<long_path_omitted>/newsdb/:/usr/app/data/ \
   -p 4040:8080 \
   nocodb/nocodb:latest

noco.db and the data DB are both in the /.../newsdb directory.

pranavxc · 2024-03-08T14:57:28Z

@pranavxc Sure:
nerdctl run -d --name newsdb \
  -v /<long_path_omitted>/newsdb/:/usr/app/data/ \
   -p 4040:8080 \
   nocodb/nocodb:latest
noco.db and the data DB are both in the /.../newsdb directory.

Are you connecting sqlite as external source as well ?

christophberger · 2024-03-08T15:17:38Z

Yes, I connect a sqlite DB as external source.

pranavxc · 2024-03-08T15:20:02Z

Yes, I connect a sqlite DB as external source.

That file you should keep in the same path inside docker ( whatever path you had given while creating the connection)

christophberger · 2024-03-08T15:24:16Z

To clarify, what do you mean by connecting as external source? The data DB I mentioned earlier is an external source in a certain way. Both DBs sit in the directory that is mounted at /usr/app/data.

pranavxc · 2024-03-08T15:37:56Z

If the NC_DB environment variable is not defined, we search for the noco.db file in the application's working directory, which is /usr/app/data. If the file is missing, we will create it.
If you have a base and an additional SQLite source added to the same base, then we look for the file based on the path saved in the connection configuration.

And in your case all the bases, users, table, views,… are missing or just the data from these views? If possible can you do a screen record and share, that way it’s easier to understand the real problem you are facing.

christophberger · 2024-03-09T09:33:14Z

I have some news.

In February, I "moved" noco.db to a new location.
Or it seems I rather copied it.
Because I discovered that I still have the original noco.db in the old location.

I copied it over to the new location,
started the v0.204.4 container with it,
and all the views and filters etc. are there again!

Conclusion: The upgrade to v0.204.0/1/2/3 did not remove the config and metadata; something else did.

BUT now NocoDB says it cannot load the data from the external data DB, although the DB's location did not change. The error message is: SQLITE_CANTOPEN: unable to open database file without further details.

However, the metadata of the external DB is available:

This is the Schema Sync dialog box:

For comparison, the same dialog for the newer copy of noco.db, where the data is there but the views, filters, etc. are missing:

So I have

A newer copy of noco.db, with which the external database works but the metadata is no there,
and an older copy of noco.db that has all the metadata but somehow prevents NocoDB from connecting to the external database.

A strange situation!

I compared both noco.dbs in DBeaver:

Structure	noco.db.2024-03-08	noco.db.older-copy
Connection	noco.db.2024-03-08	noco.db.older-copy
Tables	Tables	Tables
Table	nc_j4bt___Features	N/A
Table	N/A	nc_evolutions
Sequences	Sequences	Sequences
Sequence	nc_store	nc_store
Value	5	6
1573 objects compared

I know I'm doing blind guesswork.
Can we draw any conclusions from all that?

pranavxc · 2024-03-09T13:28:56Z

I have some news.

In February, I "moved" noco.db to a new location. Or it seems I rather copied it. Because I discovered that I still have the original noco.db in the old location.

I copied it over to the new location, started the v0.204.4 container with it, and all the views and filters etc. are there again!

Conclusion: The upgrade to v0.204.0/1/2/3 did not remove the config and metadata; something else did.

BUT now NocoDB says it cannot load the data from the external data DB, although the DB's location did not change. The error message is: SQLITE_CANTOPEN: unable to open database file without further details.
However, the metadata of the external DB is available: This is the Schema Sync dialog box: For comparison, the same dialog for the newer copy of noco.db, where the data is there but the views, filters, etc. are missing: So I have

A newer copy of noco.db, with which the external database works but the metadata is no there,

and an older copy of noco.db that has all the metadata but somehow prevents NocoDB from connecting to the external database.

A strange situation!

I compared both noco.dbs in DBeaver:

Structure noco.db.2024-03-08 noco.db.older-copy
Connection noco.db.2024-03-08 noco.db.older-copy
Tables Tables Tables
Table nc_j4bt___Features N/A
Table N/A nc_evolutions
Sequences Sequences Sequences
Sequence nc_store nc_store
Value 5 6
1573 objects compared
I know I'm doing blind guesswork. Can we draw any conclusions from all that?

To make the external source work you have to copy the file to the exact same path(within container) where it currently present.

christophberger · 2024-03-09T16:43:29Z

It is. I did not move it.

pranavxc · 2024-03-09T17:39:53Z

It is. I did not move it.

When you added external source under a base you may provided a path to your sqlite file and within the newly created docker container it will check that path for the external db file since the path is stored in the metadb as you inputed initially. And from the error it’s clear that the file is not exist in that path to confirm you can check for any backend error log in docker as well.

christophberger · 2024-03-09T17:53:22Z

Ok, sorry, I misunderstood your previous comment.

In both cases, I configured the path to the external DB as /usr/app/data/newsletter.sqlite. The path is properly mounted to the local path where the DB file actually lives.

pranavxc · 2024-03-09T17:57:30Z

Ok, sorry, I misunderstood your previous comment.

In both cases, I configured the path to the external DB as /usr/app/data/newsletter.sqlite. The path is properly mounted to the local path where the DB file actually lives.

https://www.sqlite.org/rescode.html#cantopen

Ok then it looks like some permission error, can you check the docker container logs and share it if there any error?

christophberger · 2024-03-10T21:00:27Z

Sure. When I attempt to access a table, the docker logs show these errors:

[Nest] 9  - 03/10/2024, 8:57:33 PM   ERROR [GlobalExceptionFilter] SQLITE_CANTOPEN: unable to open database file
Error: SQLITE_CANTOPEN: unable to open database file
[Nest] 9  - 03/10/2024, 8:57:33 PM   ERROR [DatasService] Error: SQLITE_CANTOPEN: unable to open database file

Those errors aren't in the log if I use the other noco.db.

pranavxc · 2024-03-12T06:32:20Z

Sure. When I attempt to access a table, the docker logs show these errors:
[Nest] 9  - 03/10/2024, 8:57:33 PM   ERROR [GlobalExceptionFilter] SQLITE_CANTOPEN: unable to open database file
Error: SQLITE_CANTOPEN: unable to open database file
[Nest] 9  - 03/10/2024, 8:57:33 PM   ERROR [DatasService] Error: SQLITE_CANTOPEN: unable to open database file
Those errors aren't in the log if I use the other noco.db.

Confirm the sqlite file path under Datasources tab.

Screen.Recording.2024-03-12.at.12.00.57.PM.mov

christophberger · 2024-03-12T10:29:05Z

I use /usr/app/data/newsletter.sqlite, and /usr/app/data is mapped to the host directory where the SQLite file lives.

Note that the same path works with the newer version of noco.db (the one that has no metadata), all else being unchanged.

I already tried newsletter.sqlite and ./newsletter.sqlite, with the same result.

I suspect that the problem is rooted somewhere inside the older version of noco.db. But I have no idea where to look.

pranavxc · 2024-03-12T12:04:54Z

The error is happening with Sqlite which couldn't able to open the file and the reason could be any of the following,

Read permission is missing for the newsletter.sqlite file
File is missing in the provided path
Corrupted sqlite file

You can get inside the container and verify that the file exist or not, verify read permission and try to connect it from external tool to verify sqlite file is not corrupted. If the path shown in edit source is /usr/app/data/newsletter.sqlite then it will provide that value to knex package(it uses sqlite3 internally) and knex will do the rest.

For more info you can check this - https://www.sqlite.org/rescode.html#cantopen

christophberger · 2024-03-12T14:33:55Z

A new test brought new insights.

Currently, my NocoDB instance runs with the older version of noco.db, the one with the base that has all the views, filters etc. but cannot open the external DB.

I just created a new base in this instance, with the external DB as datasource.

The new base can open the database without any issues.

So the problem is 100% a problem of metadata in the base that fails to open the external db.

Which of its metadata does a base use for opening and reading an external database? Is there any chance that I can somehow repair that data in a DB app like DBeaver? It took me a while to set up all the views, filters, sorting, column types, etc., and I don't look forward to doing all that again.

pranavxc · 2024-03-12T14:57:44Z

A new test brought new insights.

Currently, my NocoDB instance runs with the older version of noco.db, the one with the base that has all the views, filters etc. but cannot open the external DB.

I just created a new base in this instance, with the external DB as datasource.

The new base can open the database without any issues.

So the problem is 100% a problem of metadata in the base that fails to open the external db.

Which of its metadata does a base use for opening and reading an external database? Is there any chance that I can somehow repair that data in a DB app like DBeaver? It took me a while to set up all the views, filters, sorting, column types, etc., and I don't look forward to doing all that again.

Let's connect over a call, you can ping me in Discord https://discord.gg/5RgZmkW ( user - @pranavxc )

christophberger · 2024-03-13T08:26:55Z

Thank you, @pranavxc, for resolving the issue on the call! I never would have been able to track that down, let alone fix, on my own.

For interested readers, the problem was caused by the base's config that pointed to an outdated path. This part of the config is inaccessible in the UI, though; it's an internal config setting. After fixing the path, the base works again! 🎉

pranavxc mentioned this issue Mar 2, 2024

ix: SQLite - upgrader timeout bug #7777

Merged

7 tasks

dstala closed this as completed in #7777 Mar 3, 2024

pranavxc mentioned this issue Mar 13, 2024

🐛 Bug: Updating SQlite source path is not functioning as expected #7844

Closed

1 task

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

🐛 Bug: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? After upgrade to 0.204.1 (and .2) #7770

🐛 Bug: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? After upgrade to 0.204.1 (and .2) #7770

christophberger commented Mar 1, 2024

pranavxc commented Mar 2, 2024 •

edited

christophberger commented Mar 2, 2024

christophberger commented Mar 2, 2024

christophberger commented Mar 8, 2024

o1lab commented Mar 8, 2024 •

edited

christophberger commented Mar 8, 2024

o1lab commented Mar 8, 2024

pranavxc commented Mar 8, 2024 •

edited

christophberger commented Mar 8, 2024

pranavxc commented Mar 8, 2024

christophberger commented Mar 8, 2024

pranavxc commented Mar 8, 2024

christophberger commented Mar 8, 2024

pranavxc commented Mar 8, 2024

christophberger commented Mar 9, 2024 •

edited

pranavxc commented Mar 9, 2024

christophberger commented Mar 9, 2024

pranavxc commented Mar 9, 2024

christophberger commented Mar 9, 2024

pranavxc commented Mar 9, 2024 •

edited

christophberger commented Mar 10, 2024 •

edited

pranavxc commented Mar 12, 2024

christophberger commented Mar 12, 2024

pranavxc commented Mar 12, 2024

christophberger commented Mar 12, 2024

pranavxc commented Mar 12, 2024

christophberger commented Mar 13, 2024

🐛 Bug: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? After upgrade to 0.204.1 (and .2) #7770

🐛 Bug: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? After upgrade to 0.204.1 (and .2) #7770

Comments

christophberger commented Mar 1, 2024

Please confirm if bug report does NOT exist already ?

Steps to reproduce ?

Desired Behavior

Project Details

Attachments

pranavxc commented Mar 2, 2024 • edited

christophberger commented Mar 2, 2024

christophberger commented Mar 2, 2024

christophberger commented Mar 8, 2024

o1lab commented Mar 8, 2024 • edited

christophberger commented Mar 8, 2024

o1lab commented Mar 8, 2024

pranavxc commented Mar 8, 2024 • edited

christophberger commented Mar 8, 2024

pranavxc commented Mar 8, 2024

christophberger commented Mar 8, 2024

pranavxc commented Mar 8, 2024

christophberger commented Mar 8, 2024

pranavxc commented Mar 8, 2024

christophberger commented Mar 9, 2024 • edited

pranavxc commented Mar 9, 2024

christophberger commented Mar 9, 2024

pranavxc commented Mar 9, 2024

christophberger commented Mar 9, 2024

pranavxc commented Mar 9, 2024 • edited

christophberger commented Mar 10, 2024 • edited

pranavxc commented Mar 12, 2024

christophberger commented Mar 12, 2024

pranavxc commented Mar 12, 2024

christophberger commented Mar 12, 2024

pranavxc commented Mar 12, 2024

christophberger commented Mar 13, 2024

pranavxc commented Mar 2, 2024 •

edited

o1lab commented Mar 8, 2024 •

edited

pranavxc commented Mar 8, 2024 •

edited

christophberger commented Mar 9, 2024 •

edited

pranavxc commented Mar 9, 2024 •

edited

christophberger commented Mar 10, 2024 •

edited