Internal error while getting Lets Encrypt certificate / SQlite database

[AnthoBalitrand]

When trying to get a new certificate from Let's Encrypt, I get an "Internal Error" message.
Using latest docker image (2.4.0).

By looking at the /var/log/letsencrypt/letsencrypt.log file, I can see that the challenges has been successfully completed :

2020-08-26 21:54:18,911:DEBUG:certbot._internal.storage:Creating directory /etc/letsencrypt/archive.
2020-08-26 21:54:18,912:DEBUG:certbot._internal.storage:Creating directory /etc/letsencrypt/live.
2020-08-26 21:54:18,912:DEBUG:certbot._internal.storage:Writing README to /etc/letsencrypt/live/README.
2020-08-26 21:54:18,913:DEBUG:certbot._internal.storage:Archive directory /etc/letsencrypt/archive/npm-2 and live directory /etc/letsencrypt/live/npm-2 created.
2020-08-26 21:54:18,913:DEBUG:certbot._internal.storage:Writing certificate to /etc/letsencrypt/live/npm-2/cert.pem.
2020-08-26 21:54:18,914:DEBUG:certbot._internal.storage:Writing private key to /etc/letsencrypt/live/npm-2/privkey.pem.
2020-08-26 21:54:18,914:DEBUG:certbot._internal.storage:Writing chain to /etc/letsencrypt/live/npm-2/chain.pem.
2020-08-26 21:54:18,914:DEBUG:certbot._internal.storage:Writing full chain to /etc/letsencrypt/live/npm-2/fullchain.pem.
2020-08-26 21:54:18,914:DEBUG:certbot._internal.storage:Writing README to /etc/letsencrypt/live/npm-2/README.
2020-08-26 21:54:19,032:DEBUG:certbot._internal.plugins.selection:Requested authenticator webroot and installer <certbot._internal.cli.cli_utils._Default object at 0x7fc1461d4190>
2020-08-26 21:54:19,033:DEBUG:certbot._internal.cli:Var pref_challs=dns,http (set by user).
2020-08-26 21:54:19,033:DEBUG:certbot._internal.cli:Var authenticator=webroot (set by user).
2020-08-26 21:54:19,033:DEBUG:certbot._internal.cli:Var webroot_path=/data/letsencrypt-acme-challenge (set by user).
2020-08-26 21:54:19,034:DEBUG:certbot._internal.cli:Var webroot_path=/data/letsencrypt-acme-challenge (set by user).
2020-08-26 21:54:19,034:DEBUG:certbot._internal.cli:Var webroot_map={'webroot_path'} (set by user).
2020-08-26 21:54:19,034:DEBUG:certbot._internal.storage:Writing new config /etc/letsencrypt/renewal/npm-2.conf.
2020-08-26 21:54:19,079:DEBUG:certbot._internal.reporter:Reporting to user: Congratulations! Your certificate and chain have been saved at:
/etc/letsencrypt/live/npm-2/fullchain.pem
Your key file has been saved at:
/etc/letsencrypt/live/npm-2/privkey.pem
Your cert will expire on 2020-11-24. To obtain a new or tweaked version of this certificate in the future, simply run certbot again. To non-interactively renew *all* of your certificates, run "certbot renew"

The certificate is then displayed on the certificates list as expired with an expiration date being the current day.

By looking at the certificate file on /etc/letsencrypt/live/..., I can see the certificate being valid :

[root@docker-nginx:/etc/letsencrypt/live/npm-2]# openssl x509 -in cert.pem -text Certificate: Data: Version: 3 (0x2) Serial Number: <removed> Signature Algorithm: sha256WithRSAEncryption Issuer: C = US, O = Let's Encrypt, CN = Let's Encrypt Authority X3 Validity Not Before: Aug 26 20:54:16 2020 GMT Not After : Nov 24 20:54:16 2020 GMT

[AnthoBalitrand]

More information :

After manually editing the expiration date of the generated certificate on my SQlite database file (to make it match the real expiration date of the certificate signed by Let's Encrypt), everything goes well.

It seems that the issue appears when nginx-proxy-manager tries to update the expiration date on the database after the certificate is generated, when using an SQlite database file (didn't tested with a mySQL one).

[RichardDern]

Apparently, it's caused by FROM_UNIXTIME:

[8/29/2020] [10:22:15 PM] [Express  ] › ⚠  warning   update `certificate` set `modified_on` = date('now'), `expires_on` = FROM_UNIXTIME(1606511988) where `certificate`.`id` = 3 - SQLITE_ERROR: no such function: FROM_UNIXTIME

[cristiangauma]

I can confirm, that I also have the problem with 3 - SQLITE_ERROR: no such function: FROM_UNIXTIME and somehow it makes the domain to reach the letsencrypt rate-limit (so in my case until the next week I won't be able to get a new SSL certificate for the domain that I was configuring.).

It worked properly before start using SQLITE.

Related: #557

[AnonJervis]

I can confirm as well showing the same error caused by FROM_UNIXTIME

[d33pjs]

Same Problem for me.

[meichthys]

Same here.
@tg44 Did you also face this issue when implementing SQLite?

[meichthys]

It looks like this may be fixed in #560

[meichthys]

I can confirm this is fixed in v2.5.0 🚀

[tg44]

I dont use certs, probably this is the reason why I didn't catch that...

Yes the problem is the sql function (sqlite is really limited and sonetimes dumb), and the linked PR is the fix for this (I did the same with the current time function anno).

[meichthys]

@jc21 i think we can close this 👍

[docfactory37]

also have the same issue

[meichthys]

@docfactory37 The issue was fixed in v2.5.0
Make sure you're updated to that version. If you're still having the issue, you may want to open a different issue and include some logs for further troubleshooting.

[ionescu77]

Thank you everyone.
I think this issue can be closed.

I've been running on sqlite successfully with letzencrypt (& cloudflare) since 1y (Note: currently on n-p-m docker v3.8.0)

[meichthys]

Agreed. I've also been using SQlite without any issues.
@AnthoBalitrand can you confirm that this issue is fixed? If so, could you close this issue?

[AnthoBalitrand]

I confirm, it's now working properly :-)
Thanks for your help !