Wrong SQL query in SSLcertificate.pm module (core)

  • Hello,


    today we want to move one customer account to a new one, because of migration progresses in our company. I-MSCP got stucked while adding the new domain and we tried to reconfigure imscp to check where a problem is, or the problem is already fixed with that. Unfortunately, the problem is still existing and the reconfiguration failed.


    After we searched the logs and checked a Let's encrypt renewal log, it seems that there was already a problem, which causes the problem now aswell.


    Log from the renewal:

    Code
    1. [Sat Jan 12 00:00:15 2019] [error] main: Couldn't process Let's Encrypt renewal tasks: Modules::SSLcertificate::_loadData: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ssl_certs WHERE cert_id = '98'' at line 1 at /var/www/imscp/gui/plugins/LetsEncrypt/cron/../../../../engine/PerlLib/Modules/SSLcertificate.pm line 256.
    2. ...propagated at /var/www/imscp/gui/plugins/LetsEncrypt/cron/../../../../engine/PerlLib/iMSCP/DbTasksProcessor.pm line 496.


    Log from todays reconfigure:

    Code
    1. [Sat Jan 12 17:15:18 2019] [error] main::setupDbTasks: Modules::SSLcertificate::_loadData: DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ssl_certs WHERE cert_id = '98'' at line 1 at /var/www/imscp/engine/setup/../PerlLib/Modules/SSLcertificate.pm line 256.
    2. ...propagated at /var/www/imscp/engine/setup/../PerlLib/iMSCP/DbTasksProcessor.pm line 496.

    Both errors are pointing to an MYSQL Syntax Error with cert_id 98. We already tried to investigate but could not find the problem yet.


    Does anyone have an idea why the plugin is not able to do it's work? Everything worked before that point.


    Thank you for your time.

  • Good evening,


    That would be great if you followed our reporting rules which are available at Reporting rules - Reminder


    That would be also even better if you updated your i-MSCP installation prior reporting a bug which has been already fixed. As the error say, this has nothing to do with the LetsEncrypt plugin.


    You need update to latest released version: i-MSCP 1.5.3 (build 2018120800) RELEASED


    Thread closed.


    See also:

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

    account_detailed.gif

  • I was able to find and fix the error. We removed one customer with the Domain ID 21 some weeks ago, without revoking the certificate before. We thought that the certificate would be removed aswell and everything was working until today.


    The above cert_id 98 was aimed to domain_id 21, which was deleted some time ago and because of that i-mscp responds correctly with an error. After removing the not needed cert_id 98 from the table ssl_certs, which had the status "toadd", we were able to restart everything and everything works now.


    We can start again to migrate our customers to the new server with i-mscp now.

    I don't know if its a bug, or was just a problem on our side with the not removed mysql entry from the not needed ssl cert after removing the corresponding domain, so I thought I give the answer here aswell.

    The problem is solved.

  • Nuxwin

    Added the Label patched
  • Nuxwin

    Changed the title of the thread from “Let's encrypt error with renewal (MYSQL Syntax Error near cert_id)” to “Wrong SQL query in SSLcertificate.pm module (core)”.
  • Nuxwin

    Closed the thread.
  • I don't know if its a bug, or was just a problem on our side with the not removed mysql entry from the not needed ssl cert after removing the corresponding domain, so I thought I give the answer here aswell.

    Read my previous answer. Basically put, SQL query to remove orphaned SSL cert was wrong. This bug has been fixed as already stated. You need update your i-MSCP instance.

    badge.php?id=1239063037&bid=2518&key=1747635596&format=png&z=547451206

    account_detailed.gif