Collapse

Announcement

Collapse
No announcement yet.

Critical Error Could not connect to the database (WHMCS)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Critical Error Could not connect to the database (WHMCS)

    Critical Error Could not connect to the database (WHMCS) pops up randomly on the website.

  • #2
    There could be following reasons associated with the issue.
    1. Check if the username and password associated with the database that you have specified in the configuration file are correct and matches to connect to the correct database.
    2. Check if the database is corrupted or not? If yes, try repairing it. In case, the automatic repair doesn’t work then you will need to restore the database from the most recent backup.
    3. Check the issue at the server end.


    In our case, we checked the logs and found that there were multiple lock processes on one of the table of our WHMCS database.

    To check current running process under MySQL use following commands.

    Code:
    [root@]# mysqladmin proc


    ----------------------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +-------+---------------+-----------+---------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
    | 63002 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28507 | | |
    | 63003 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 0 | Sending data | SELECT * FROM `tblactivitylog` LIMIT 5010100,100 |
    | 63017 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28396 | | |
    | 63018 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28396 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000201', |
    | 63025 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28215 | | |
    | 63026 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28215 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000502', |
    | 63034 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28198 | | |
    | 63035 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28198 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000519', |
    | 63052 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28091 | | |
    | 63053 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28091 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000706', |
    | 63055 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28050 |
    | 66004 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 10232 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008050445', |
    | 66009 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 10216 |
    | 63018 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28396 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000201', |
    | 63025 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28215 | | |
    | 63026 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28215 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000502', |
    | 63034 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28198 | | |
    | 63035 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28198 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000519', |
    | 63052 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28091 | | |
    | 63053 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 28091 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008000706', |
    | 63055 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 28050 |
    | 66004 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 10232 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008050445', |
    | 66009 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 10216 | | |
    | 66010 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 10216 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008050501', |
    | 66024 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 10146 | | |
    | 66025 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 10146 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008050611', |
    | 66057 | 24x7_whmcs | localhost | 24x7_whmcs | Sleep | 9967 | | |
    | 66058 | 24x7_whmcs | localhost | 24x7_whmcs | Query | 9967 | Waiting for table metadata lock | INSERT INTO tblactivitylog (`date`,`description`,`user`,`userid`,`ipaddr`) VALUES ('20181008050910', |

    This caused all the connections being used up by MySQL.

    As you can see the problem is with the queries on table `tblactivitylog` so we went ahead with the investigation process.

    As per WHMCS documentation, activity logs keep track of the activities conducted by who and also gives a clear idea about problems if any, helpful in troubleshooting.

    System log entries can be accessed via Utilities > Activity Log.

    Once the activities have been checked we can easily clear the older logs or set a limit so that they grow up to a certain limit.

    This can be controlled from Setup > General Settings > Limit Activity Log

    You shall be able to delete all entries before a certain date to prune the logs in Utilities > System Cleanup

    You can even check the size or number of rows from logging into phpMyAdmin. As you can see the number of rows was grown up to 5,105,571 which is quite a large number causing the queries to create a lock while processing it.


    Click image for larger version  Name:	whmcs_tablesize_before.png Views:	1 Size:	786.8 KB ID:	840
    We went ahead and pruned the rows from phpMyAdmin , lowering the number of rows of the tableactivitylog table.


    Click image for larger version  Name:	whmcs_tablesize_after.png Views:	1 Size:	804.5 KB ID:	841

    This resolved the issue from reoccurring.

    This way you shall be able to investigate the issue related to DB connection and get rid of it from occurring by applying the relevant fix.

    Hope this article helps
    Last edited by Mandeep; 11-02-2018, 11:43 AM.

    Comment

    Working...
    X