MySQL Socket Connection for Stateless Service

[arvindwill]

Trying to deploy the docker container in Google Cloudrun. As the Cloudrun is purely stateless it used MySQL socket connection method to connect to DB.

Connection string used for other application localhost;unix_socket=/cloudsql/stutzen:us-central1:sz-5-7?u=nocodb&p=nocodb&d=nocodb

For when tried to use the same format it fails with below error.

TypeError [ERR_INVALID_URL]: Invalid URL: localhost;unix_socket=/cloudsql/stutzen:us-central1:sz-5-7?u=nocodb&p=nocodb&d=nocodb 
at onParseError (internal/url.js:258:9) 
at new URL (internal/url.js:334:5) 
at Function.metaUrlToDbConfig (/usr/src/app/docker/main.js:2:8643) 
at Function.make (/usr/src/app/docker/main.js:2:4591) 
at new M (/usr/src/app/docker/main.js:2:29844) 
at Function.init (/usr/src/app/docker/main.js:2:30760) at /usr/src/app/docker/main.js:2:319666 
at Object.<anonymous> (/usr/src/app/docker/main.js:2:319787) at a (/usr/src/app/docker/main.js:2:352) at /usr/src/app/docker/main.js:2:1151 {

Is there any way to use MySQL Socket connection for both NC-DB and External DB?

[tituspijean]

I second this. I am using MariaDB, and it has recently started acting up while connecting to it via IP while I get no issue with its default socket connection (with other apps). NocoDB's JDBC URL Format does not allow socket paths.

Warning: got packets out of order. Expected 0 but received 1
╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                                                         ║
║                                                 EXCEPTION OCCURED!! in MysqlClient @ createDatabaseIfNotExists          ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║   MESSAGE:                                                                                                              ║
║   Host '<SERVER PUBLIC IP ADDRESS>' is not allowed to connect to this MariaDB server                                               ║
║                                                                                                                         ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║   CODE:                                                                                                                 ║
║   ER_HOST_NOT_PRIVILEGED                                                                                                ║
║                                                                                                                         ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║   STACK:                                                                                                                ║
║   Error: Host '<SERVER PUBLIC IP ADDRESS>' is not allowed to connect to this MariaDB server                                        ║
║       at Packet.asError (/opt/yunohost/nocodb/node_modules/mysql2/lib/packets/packet.js:728:17)                         ║
║       at ClientHandshake.execute (/opt/yunohost/nocodb/node_modules/mysql2/lib/commands/command.js:29:26)               ║
║       at Connection.handlePacket (/opt/yunohost/nocodb/node_modules/mysql2/lib/connection.js:456:32)                    ║
║       at PacketParser.onPacket (/opt/yunohost/nocodb/node_modules/mysql2/lib/connection.js:85:12)                       ║
║       at PacketParser.executeStart (/opt/yunohost/nocodb/node_modules/mysql2/lib/packet_parser.js:75:16)                ║
║       at Socket.<anonymous> (/opt/yunohost/nocodb/node_modules/mysql2/lib/connection.js:92:25)                          ║
║       at Socket.emit (node:events:390:28)                                                                               ║
║       at Socket.emit (node:domain:475:12)                                                                               ║
║       at addChunk (node:internal/streams/readable:315:12)                                                               ║
║       at readableAddChunk (node:internal/streams/readable:289:9)                                                        ║
║                                                                                                                         ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║                                                                                                                         ║
║                                                                                                                         ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

[stantonius]

Firstly, awesome product NocoDB team.

I was able to get a Cloud Run server to connect to the Cloud SQL MySQL instance by using NC_DB_JSON_FILE instead of NC_DB.

The Dockerfile looks like:

FROM nocodb/nocodb:latest

COPY knex.json /knex.json

ENV NC_DB_JSON_FILE=/knex.json

and the knex.json is just:

{
    "client": "mysql2",
    "connection": {
      "socketPath": "/cloudsql/PROJECT_ID:REGION:INSTANCE",
      "user" : "USERNAME",
      "password" : "PASSWORD",
      "database" : "DB"
    }

}

The NodeJS URL module referenced by metaUrlToDbConfig method in packages/nocodb/src/lib/utils/NcConfigFactory.ts does not accept unix urls (which are required by GCP's cloud proxy service). This is where the Invalid URL error is coming from.

The method metaUrlToDbConfig just parses the url into json that knex.js processes. The sample structure of knex json is found at http://knexjs.org/ (Ctrl F "socket")

Now the only problem is - the connection is extremely slow and drops consistently. I will open a new issue for this.

[Morakhiyasaiyam]

I second this. I am using MariaDB, and it has recently started acting up while connecting to it via IP while I get no issue with its default socket connection (with other apps). NocoDB's JDBC URL Format does not allow socket paths.

Warning: got packets out of order. Expected 0 but received 1
╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                                                                                                         ║
║                                                 EXCEPTION OCCURED!! in MysqlClient @ createDatabaseIfNotExists          ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║   MESSAGE:                                                                                                              ║
║   Host '<SERVER PUBLIC IP ADDRESS>' is not allowed to connect to this MariaDB server                                               ║
║                                                                                                                         ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║   CODE:                                                                                                                 ║
║   ER_HOST_NOT_PRIVILEGED                                                                                                ║
║                                                                                                                         ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║   STACK:                                                                                                                ║
║   Error: Host '<SERVER PUBLIC IP ADDRESS>' is not allowed to connect to this MariaDB server                                        ║
║       at Packet.asError (/opt/yunohost/nocodb/node_modules/mysql2/lib/packets/packet.js:728:17)                         ║
║       at ClientHandshake.execute (/opt/yunohost/nocodb/node_modules/mysql2/lib/commands/command.js:29:26)               ║
║       at Connection.handlePacket (/opt/yunohost/nocodb/node_modules/mysql2/lib/connection.js:456:32)                    ║
║       at PacketParser.onPacket (/opt/yunohost/nocodb/node_modules/mysql2/lib/connection.js:85:12)                       ║
║       at PacketParser.executeStart (/opt/yunohost/nocodb/node_modules/mysql2/lib/packet_parser.js:75:16)                ║
║       at Socket.<anonymous> (/opt/yunohost/nocodb/node_modules/mysql2/lib/connection.js:92:25)                          ║
║       at Socket.emit (node:events:390:28)                                                                               ║
║       at Socket.emit (node:domain:475:12)                                                                               ║
║       at addChunk (node:internal/streams/readable:315:12)                                                               ║
║       at readableAddChunk (node:internal/streams/readable:289:9)                                                        ║
║                                                                                                                         ║
║   - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -   ║
║                                                                                                                         ║
║                                                                                                                         ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Hey can you advise me to setup with mariadb in docker Because official documentation lacking setup for mariadb and i tried with mysql setup and changed image to mariadb but it fails

[tituspijean]

Hey can you advise me to setup with mariadb in docker

I am not using Docker, and your request is unrelated to this issue. NocoDB has a Discord server though. ;)

[4x10m]

So here is the postgresql adulator you were waiting for LUL.

Thanks to @stantonius, i managed to connect nocodb to postgresql using socket by following his example and using NC_DB_JSON_FILE. I did not managed to do that with NC_DB

I don't know if it's linked to that but the homepage win 10 secondes loading after setting up database socket (Total time: from 43s to 33s, DomContentLoaded: 36s to 26s) but note that i test this in production so its dependant of my little 10mbps internet bandwidth.

Json file content exemple: { "client": "pg", "connection": { "socketPath": "/var/run/postgresql/", "user" : "nocodb", "password" : "*****", "database" : "nocodb" } }

Thanks

[o1lab]

Thank you @4x10m - appreciate the help.

[98h398hrpohpoai]

This appears to have broken with postgresql. The same config above now produces [Nest] 9 - 12/13/2023, 8:37:14 AM ERROR [ExceptionHandler] Error: connect ECONNREFUSED 127.0.0.1:5432 and does not render the socket connection. When testing with mysql2, the socket path is read correctly.

Edit: Thanks to 4x10m for providing the original reference above. The corrected config for postgres changes socketPath to host like so:

{
    "client": "pg",
    "connection": {
        "host": "/run/postgresql/",
        "user": "nocodb",
        "password": "****",
        "database": "nocodb"
    }
}