Cannot connect to mysql database (error communicating with the server: No such file or directory )

[ebiagi]

hello,

I'm trying to get push_notify working but I'm not getting anywhere. The nextcloud instance resides on the machine A while the mysql DB is on another machine, the communication is encrypted and forced with ALTER USER 'nextcloud'@'IP' REQUIRE SSL ; I already did some issues research, following the advices here

https://github.com/nextcloud/notify_push/issues/88 https://github.com/nextcloud/notify_push/issues/117 https://github.com/nextcloud/notify_push/issues/79

I managed to resolve some other errors but ow I'm stuck.

my config.php file related configs

  'dbname' => 'nextcloud',
  'dbhost' => '***',
  'dbdriveroptions' => [
  \PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/ssl/client-key.pem',
  \PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/ssl/client-cert.pem',
  \PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ssl/ca_cert.pem',
  \PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
  ],
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'nextcloud',
  'dbpassword' => '****',

notify_push:setup result

This setup wizard is intended for use on single server instances
where the nextcloud server, web server/reverse proxy and push daemon all run on the same machine.
If your setup is more complex or involves any kind of load balancing
you should follow the manual setup instruction on the README instead
https://github.com/nextcloud/notify_push
Press enter to continue or ESC to cancel...

🗴 failed to run self-test with auto-generated config.
test output: 
  See the steps in the README for manual setup instructions: https://github.com/nextcloud/notify_push

that leads to the following nextcloud log entry rewind(): stream does not support seeking at /var/www/html/nextcloud/apps/notify_push/lib/SetupWizard.php#147

So I've tried to set up the systemd.service like that

[Unit]
Description = Push daemon for Nextcloud clients
Documentation=https://github.com/nextcloud/notify_push
After=apache2.service php7.4-fpm.service mysql.service redis.service


[Service]
Environment= PORT=7867
Environment= RUST_BACKTRACE=1
Environment= RUST_BACKTRACE=full
Environment= COLORBT_SHOW_HIDDEN=1
Environment= ALLOW_SELF_SIGNED=true
Environment= NEXTCLOUD_URL=https://***
#Environment= DATABASE_URL=mysql://nextcloud:***!$fsf@***:3306 
#REDIS_URL=unix:/var/run/redis/redis.sock 
#Environment= LOG=DEBUG 
ExecStart = /var/www/html/nextcloud/apps/notify_push/bin/x86_64/notify_push /var/www/html/nextcloud/config/config.php
User=www-data



[Install]
WantedBy = multi-user.target

The config dump seems to be right

Started Push daemon for Nextcloud clients.
 Config {
     database: AnyConnectOptions(
         MySql(
             MySqlConnectOptions {
                 host: "***",
                 port: 3306,
                 socket: None,
                 username: "nextcloud",
                 password: Some(
                     "***",
                 ),
                 database: Some(
                     "nextcloud",
                 ),
                 ssl_mode: VerifyCa,
                 ssl_ca: Some(
                     File(
                         "/etc/mysql/ssl/ca_cert.pem",
                     ),
                 ),
                 statement_cache_capacity: 100,
                 charset: "utf8mb4",
                 collation: None,
                 log_settings: LogSettings {
                     statements_level: Info,
                     slow_statements_level: Warn,
                     slow_statements_duration: 1s,
                 },
             },
         ),
     ),
     database_prefix: "oc_",
     redis: [
         ConnectionInfo {
             addr: Unix(
                 "/var/run/redis/redis-server.sock",
             ),
             redis: RedisConnectionInfo {
                 db: 0,
                 username: None,
                 password: None,
             },
         },
     ],
     nextcloud_url: "https://*****/",
     metrics_bind: None,
     log_level: "warn",
     bind: Tcp(
         0.0.0.0:7867,
     ),
     allow_self_signed: true,
     no_ansi: false,
 }

But the systemd service fails with the following error

   0: #033[91mFailed to connect to Nextcloud database#033[0m
   1: #033[91merror communicating with the server: No such file or directory (os error 2)#033[0m
   2: #033[91mNo such file or directory (os error 2)#033[0m
Location:
   #033[35m/tmp/krankerl.EBG47Us7roNI/notify_push/src/storage_mapping.rs#033[0m:#033[35m58#033[0m
  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ BACKTRACE ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   1: #033[91mcolor_eyre::config::EyreHook::into_eyre_hook::{{closure}}#033[0m#033[90m::h6bafa655fe3baaa6#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   2: #033[91m<E as eyre::context::ext::StdError>::ext_report#033[0m#033[90m::h3084f0ac27ad5fca#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   3: #033[91mnotify_push::main::{{closure}}#033[0m#033[90m::h088e18d256045e69#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   4: #033[91mnotify_push::main#033[0m#033[90m::h11cd5f8c20eeb64e#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   5: #033[32mstd::sys_common::backtrace::__rust_begin_short_backtrace#033[0m#033[90m::h1802346691bd2d03#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   6: #033[32mmain#033[0m#033[90m<unknown>#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
Run with COLORBT_SHOW_HIDDEN=1 environment variable to disable frame filtering.

Do you have any clues? Thank you very much

[ebiagi]

I'm still struggling to find a solution. I tried with the notify_push version 3.0.0 but the error is still here.

If I decomment the following line

Environment= DATABASE_URL=mysql://nextcloud:***!$fsf@***:3306

in the systemd.service the log gives me the following error, even if I switch the required SSL off with an ALTER USER 'nextcloud'@'IP' REQUIRE NONE; :

[2021-11-15 19:59:38.930269 +00:00] WARN [rustls::session] /home/runner/.cargo/registry/src/github.com-1ecc6299db9ec823/rustls-0.19.1/src/session.rs:798: Sending fatal alert DecodeError
Error:
   0: #033[91mFailed to connect to Nextcloud database#033[0m
   1: #033[91merror communicating with the server: invalid certificate: BadDER#033[0m
   2: #033[91minvalid certificate: BadDER#033[0m
Location:
   #033[35m/tmp/krankerl.UyibIgt7EFoA/notify_push/src/storage_mapping.rs#033[0m:#033[35m58#033[0m
  ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ BACKTRACE ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
   1: #033[91mcolor_eyre::config::EyreHook::into_eyre_hook::{{closure}}#033[0m#033[90m::h783d19bfe6c48b74#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   2: #033[91m<E as eyre::context::ext::StdError>::ext_report#033[0m#033[90m::h1a8baef3e79949bc#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   3: #033[91mnotify_push::main::{{closure}}#033[0m#033[90m::h19924bb1c8c61490#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   4: #033[91mnotify_push::main#033[0m#033[90m::hbb96f68abc0090e9#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   5: #033[32mstd::sys_common::backtrace::__rust_begin_short_backtrace#033[0m#033[90m::hf80162e503158945#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
   6: #033[32mmain#033[0m#033[90m<unknown>#033[0m
      at #033[35m<unknown source file>#033[0m:#033[35m<unknown line>#033[0m
Run with COLORBT_SHOW_HIDDEN=1 environment variable to disable frame filtering.

the db gives me a warning each time I try to launch it:

Aborted connection 66019 to db: 'unconnected' user: 'unauthenticated' host: '172.*.*.*' (This connection closed normally without authentication)

[toper]

I have the same problem. Any solution ?

[ebiagi]

I've found a solution, the reason was that the certs I was using didn't have any SAN (subjectAltName-Extension) extension specified and rusttls need it to work correctly. So I mixed a couple of websites' solutions to find the correct way to issue the new certificates

websites

• cybercity
• mysql dev
• golinuxcloud

certificates

# Create CA certificate
#----------------------------------
$ openssl genrsa 2048 > ca-key.pem &&\
$ openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
#----------------------------------
$ openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem &&\
$ openssl rsa -in server-key.pem -out server-key.pem &&\
$ openssl x509 -req -in server-req.pem -days 36500 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -extfile v3s.ext -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
#----------------------------------
$ openssl req -newkey rsa:2048 -days 36500 -nodes -keyout client-key.pem -out client-req.pem &&\
$ openssl rsa -in client-key.pem -out client-key.pem &&\
$ openssl x509 -req -in client-req.pem -days 36500 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -extfile v3c.ext -out client-cert.pem

# verify keys
#----------------------------------
$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

ext files

server (v3s.ext)

basicConstraints = CA:FALSE
nsCertType = server
nsComment = "OpenSSL Generated Server Certificate"
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid,issuer
keyUsage = critical, digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth,clientAuth
subjectAltName = @alt_names
[alt_names]
IP.1 = 172.16.x.x
DNS.1 = aa.example.net
DNS.2 = aa

clients (v3c.ext)

basicConstraints = CA:FALSE
nsCertType = client
nsComment = "OpenSSL Generated Server Certificate"
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid,issuer
keyUsage = critical, digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth,clientAuth

systemd

[Unit]
Description = Push daemon for Nextcloud clients
Documentation=https://github.com/nextcloud/notify_push

[Service]
Environment= PORT=7867
Environment= NEXTCLOUD_URL=https://nextcloud.example.com
Environment= DATABASE_URL=mysql://nextcloud:secretpassword@serverdns:3306/nextcloud
ExecStart = /var/www/html/nextcloud/apps/notify_push/bin/x86_64/notify_push /var/www/html/nextcloud/config/config.php
User=www-data

[Install]
WantedBy = multi-user.target

app start

# sudo -u www-data php ./occ notify_push:setup https://nextcloud.example.com/push

app check

https://help.nextcloud.com/t/how-to-verify-notify-push-works-correctly/116685/5

# sudo -u www-data php ./occ notify_push:metrics

Of course the old assumptions are still valid

Note: Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. To avoid any issues, I am setting them as follows. Otherwise, you will get certification verification failed error. Hence set it as follows:
CA common Name : MariaDB admin
Server common Name: MariaDB server
Client common Name: MariaDB client

[client]
ssl-ca=$DIR/ca_cert.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
[server]
ssl_ca=$DIR/ca_cert.pem
ssl_cert=$DIR/server-cert.pem
ssl_key=$DIR/server-key.pem