Let's Encrypt silently fails (SQL error in log)

[Graham]

Hi there,

I'm running an ageing server with RHEL 5.11, and we've recently upgraded Plesk to 12.5. I've installed Let's Encrypt, and have version 2.0.3.

When trying to install a certificate on any domain, Plesk simply returns to the domain list without displaying a success message.

Upon each attempt, Plesk logs the following to /usr/local/psa/admin/logs/panel.log:

[2017-04-18 22:20:43] ERR [1] DB query failed:

SELECT `c`.* FROM `certificates` AS `c`

INNER JOIN `Repository` AS `r` ON r.component_id = c.id WHERE (r.rep_id = ?)


Error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound​

Therefore I believe this problem to be related to some inconsistency in Plesk's database, rather than a disk permissions issue as per a similar recent thread.

Does anyone have advice for resolving it? I can't see anything relating to these tables in Google searches.

Thanks,
Graham

 

[IgorG]

Have you tried repair database with

# plesk repair db

at least?

 

[Graham]

Thanks, I hadn't realised there was a suite of repair tools. I've run that and it has cleared up some issues, and Let's Encrypt is now producing its own error messages. Progress!

For the benefit of anyone else, Let's Encrypt is now giving me this error:

Error: Let's Encrypt SSL certificate installation failed: Invalid response from https://acme-v01.api.letsencrypt.org/acme/new-cert: Error creating new cert :: too many certificates already issued for exact set of domains: [domain list]
Type: urn:acme:error:rateLimited.​

Essentially, a result of trying too many times in too short a period. I think I'll need to wait a week before trying this again. I'll follow-up after that.

Thanks @IgorG.

Graham

 

[Graham]

Unfortunately, I think the rateLimited error I'm getting from Let's Encrypt is occurring BEFORE the Plesk one, so it's merely preventing me from getting to the Plesk SQL error. I think it was co-incidental that this new error happened after I ran the db repair. I'm still getting the original SQL error if I try to install a certificate on another domain.

What else should I look at?

 

[UFHH01]

Hi Graham,

pls. check your current certificates with for example: => crt.sh | %.YOUR-DOMAIN.COM

... if you reached the limits, pls. check as well the official Let's Encrypt documentation about rate limits: => Rate Limits - Let's Encrypt - Free SSL/TLS Certificates

 

[Graham]

Cheers. I'm not so worried about the rate limit, or the certificates. The primary problem is this SQL error:

[2017-04-18 22:20:43] ERR [1] DB query failed:

SELECT `c`.* FROM `certificates` AS `c`

INNER JOIN `Repository` AS `r` ON r.component_id = c.id WHERE (r.rep_id = ?)


Error: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound​

 

[Graham]

Hi guys,

This is still an issue and I'm not sure where to go with it. If anyone has any vague ideas of routes to go down, that would help.

Cheers.

 

[UFHH01]

Hi Graham,

sorry, but there is no "route around the limitations from Let's Encrypt". These limitations are part of the agreements and trying to "trick the system" can as welt result in a long-term ban for your FQDN and IPs.

 

[Graham]

Hi @UFHH01,

The issue is not related to Let's Encrypt's limits.

This is a Plesk database issue...

G.

 

[UFHH01]

Hi Graham,

Invalid response from https://acme-v01.api.letsencrypt.org/acme/new-cert: Error creating new cert :: too many certificates already issued for exact set of domains: [domain list]
Type: urn:acme:error:rateLimited.

I doubt that, because of your error message!
The error states clear, that the API of Let's Encrypt denied to verify an additional certificate for the domain. The Let's Encrypt 's verification process doesn't use the psa - database, it uses it's very own database.


Edit: Pls. read as well:

=> Rate Limits for Let's Encrypt
=> Too many certificates already issued for: domain.org while -d sub.domain.org was requested · Issue #1721 · certbot/certbot · GitHub

 

[Graham]

Hi @UFHH01,

You haven't read what I've written. I said when I posted that message that I knew what it meant, and that it wasn't something I was worried about. I then went on to say that I've realised that my original problem wasn't solved by the db repair.

The original problem, and the only problem, is the SQL error that I've posted... twice.

Let's move on from Let's Encrypt's rate limits. I've been pretty clear that this is not the issue.

Thanks.

 

[UFHH01]

Hi Graham,

if you actually would like to discuss your SQL - error, you should consider to update the error - message to a "most recent one", to make sure, that the issue/error/problem is still existent, after you tried to apply for new certificate.

In addition, pls. make sure, that the missing SNI - support on RHEL 5.x - based systems is not the root cause of your SQL issue here.

 

[Graham]

Hi @UFHH01,

Thanks -- I'll take a look at the SNI situation and let you know.

G.

 

[Bitpalast]

I cannot really solve it, but would like to add some information to maybe make the case clearer: The query cannot be executed, because an empty set of parameters is transferred to the query. The final WHERE clause ends on "WHERE (r.rep_id = )" and will fail, because the spot after the equal sign must not be empty.

Some thoughts on this:
The only parameter that needs to be transferred to this query is the id from the cert_rep_id field of the domains table. I suggest to first check whether that field is present in the domains table structure and has an ID in it.
> describe domains;
must have a line

| cert_rep_id         | int(10) unsigned                           | YES  | MUL | 0                                    |                |

If this is missing, check if the upgrade to 12.5.30 was completed or if in Tools & Settings > Components older versions are shown.

Then check if the cert_rep_id field for the certified domain is different from NULL or '0':
> SELECT cert_rep_id FROM domains WHERE name LIKE 'DOMAIN.TLD';
(with DOMAIN.TLD = your domain name) If the field is missing, the database structure was not updated on the Plesk update. If the field is present, but the ID is 0, the certification process failed in a step before the certificate data is loaded by Plesk. The cert has probably never been created/written to the database.

Then I'd look into the Repository table what it contains:
> SELECT * from Repository;
The rep_id in that table is the cert_rep_id from the domains table. For that dataset get the component_id and look that one up in the certificates table:
> SELECT * FROM certificates WHERE id LIKE 'component_id';
(with component_id = the one you got from the Repository table).

I am guessing that there won't be any matching datasets, because the certificate was never generated and stored in Plesk in a much earlier step. So the issue probably is not really the failed SQL query that you have reported, but a failed certificate creation process. Put in different words: If you don't find the certificate in the certificates table by this method, don't worry about the failed SQL statement, because even if the SQL statement works the SSL configuration won't work.

I have doubts that this can be solved without an inside view of the extension. If I was you I'd file a support ticket for this. But very curious what turns out of this.

 

[LarsenD]

The reason for the problem seems to be that entries in the table "domains" have a "cert_rep_id" of NULL. Setting this to 0, the error didn't show up anymore.

UPDATE domains SET cert_rep_id = 0 WHERE cert_rep_id IS NULL;

 

[Bitpalast]

Did you consider, that "0" could be nothing else than a valid id for some specific cert that exists? "0" does not necessarily mean "no certificate". So when you enter this key it might point to a record that should not really be associated with the entry you are editing. For example "0" could represent a default certificate. As such a certificate with ID "0" exists, referring to that dataset will surely work - until that dataset is removed for some reason. But it can be a false link/association anyway. So this is probably a dirty workaround that could lead to more failures in the future.

 

[openairhoster2]

We had this problem, too, with older domains on a Plesk system and I can confirm that LarsenD's solution solved it . I am faily sure that setting cert_rep_id to 0 wil not cause other problems, since that is what our Plesk 12.5 does with newly-created domains, too.