Announcement

Collapse
No announcement yet.

Urgent - MySQL crashing since updated to 5.5.5

Collapse
X
Collapse
 
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
Previous 1 2 template Next
  • #1

    Urgent - MySQL crashing since updated to 5.5.5

    I upgraded to version 5.5.5 from 5.4.3 less than 48 hours ago and in that time the CRM has crashed now 2 times. System was not crashing before upgrade at all using 5.3.4.
    In the error logs it indicates errors with MySQL going on.
    MySQL Version 5.6.33-0ubuntu0.14.04.1
    This error simply repeats, after investigating i increase the max connections for mysql from 100 to 300 after the first crash, the 2nd it crashed time this error came back.
    ---- First time
    [2019-01-16 14:11:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
    [2019-01-16 14:12:05] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[HY000] [2002] No such file or directory" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 2002): SQLSTATE[HY000] [2002] No such file or directory at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []

    ---- Second time
    [2019-01-17 14:31:02] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []

    ---- Third Time (Just now happened while composing this post!
    [2019-01-17 14:56:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []


    Any help would be appreciated.
    Tags: 5.5.5, crashing, error, mysql, upgrade
  • #2
    It appears to be completely random when it happens, I don't see anything that leads up to it.
    Last edited by khopper; 01-17-2019, 03:54 PM.

    Comment

    • #3
      And again this morning. The only thing that happened right before the error and espo became unavailable that I see is that a user tried logging in.
      This has only started happening since upgrading from 5.4.3 to 5.5.5, before that the server would stay up for months on end and never have this issue before.


      [2019-01-18 14:01:40] Espo.ERROR: LDAP: Authentication failed for user [user.name], details: 0x31 (Invalid credentials; 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db0): CN=user name,OU=~FL.other,DC=ost,DC=local [] []
      [2019-01-18 14:02:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
      [2019-01-18 14:03:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
      Last edited by khopper; 01-20-2019, 01:48 AM.

      Comment

      • #4
        Just happened again after doing a reboot, this time after processing a workflow:
        This has only started happening since upgrading from 5.4.3 to 5.5.5, before that the server would stay up for months on end and never have this issue before.

        [2019-01-18 14:22:55] Espo.DEBUG: WorkflowManager: End workflow [afterRecordSaved] for Entity [Account, 5c3f58dd03b26c2b6]. [] []
        [2019-01-18 14:23:01] Espo.DEBUG: BPM: processPendingFlows [] []
        [2019-01-18 14:25:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
        [2019-01-18 14:26:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
        [2019-01-18 14:27:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
        [2019-01-18 14:28:02] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
        [2019-01-18 14:29:02] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
        Last edited by khopper; 01-20-2019, 01:47 AM.

        Comment

        • #5
          You have to increase "max_connections" in your MySQL, more details: https://stackoverflow.com/questions/...ny-connections
          Job Offers and Requests

          Comment

          • khopper
            #5.1
            khopper commented
            01-18-2019, 05:07 PM
            Editing a comment
            I tripled the number of allowed connections in the first post. From 100 to 300. So if it continues just keep upping it?
        • #6
          Try to disable all workflow rules. Perhaps, some of them looping your system.
          Job Offers and Requests

          Comment

          • khopper
            #6.1
            khopper commented
            01-20-2019, 01:46 AM
            Editing a comment
            I have checked the logs and don't see any looping, I never modified any workflows before or after the upgrade from 5.4.3 to 5.5.5.
            The crashing only began immediately after the upgrade. I was working perfectly 100% for months on version 5.4.3 with max_connection set to 100.
            For now using version 5.5.5 I have upped max_connections to 600 and will let you know if it maxes out again after that since it was increased to 300 prior.
            Last edited by khopper; 01-20-2019, 01:56 AM.
        • #7
          tarasm

          Increasing the max_connections to 600 seems to have stopped the following error "SQLSTATE[08004] [1040] Too many connections"
          However only since the upgrade to 5.5.5 from 5.4.3 the system is extremely slow now. All 4 cores keep going to 100% usage and bring the system to a halt.
          There are no loops happening in the log via the workflow either, what else could be going on?

          Again - everything was running perfectly for months running on version 5.4.3. Only since the Espo Upgrades has this all started.
          Not even system updates or changes had been made before this or after, except for the mysql max_connections entry.
          Last edited by khopper; 01-21-2019, 04:52 PM.

          Comment

          • #8
            tarasm

            It happened again this morning, even with max_connections set to 600.
            It appears to only happen after a user tried logging in.


            [2019-01-22 13:28:02] Espo.DEBUG: BPM: processPendingFlows [] []
            [2019-01-22 13:29:02] Espo.DEBUG: BPM: processPendingFlows [] []
            [2019-01-22 13:32:00] Espo.DEBUG: LDAP: user search string: "(&(objectClass=person)(sAMAccountName=tammy.j ean) )" [] []
            [2019-01-22 13:32:00] Espo.DEBUG: User [tammy.jean] is found with this DN [CN=Tammy Jean,OU=~FL.other,DC=ost,DC=local]. [] []
            [2019-01-22 13:32:00] Espo.ERROR: LDAP: Authentication failed for user [tammy.jean], details: 0x31 (Invalid credentials; 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 52e, v1db0): CN=Tammy Jean,OU=~FL.other,DC=ost,DC=local [] []
            [2019-01-22 13:32:00] Espo.INFO: AUTH: Trying to login as user 'tammy.jean' by token but token is not found. [] []
            [2019-01-22 13:32:00] Espo.INFO: AUTH: Trying to login as user 'tammy.jean' by token but token is not found. [] []
            [2019-01-22 13:32:05] Espo.DEBUG: LDAP: user search string: "(&(objectClass=person)(sAMAccountName=tammy.j ean) )" [] []
            [2019-01-22 13:32:05] Espo.DEBUG: User [tammy.jean] is found with this DN [CN=Tammy Jean,OU=~FL.other,DC=ost,DC=local]. [] []
            [2019-01-22 13:45:01] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []
            [2019-01-22 13:46:02] Espo.ERROR: Uncaught Exception PDOException: "SQLSTATE[08004] [1040] Too many connections" at /var/www/espocrm/application/Espo/ORM/EntityManager.php line 157 {"exception":"[object] (PDOException(code: 1040): SQLSTATE[08004] [1040] Too many connections at /var/www/espocrm/application/Espo/ORM/EntityManager.php:157)"} []

            I have tons of these entires/threads running via mysql related to the user.id field.

            | 992611 | bn_espocrm | localhost | bitnami_espocrm | Query | 31 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
            | 992612 | bn_espocrm | localhost | bitnami_espocrm | Query | 31 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
            | 992613 | bn_espocrm | localhost | bitnami_espocrm | Query | 31 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
            | 992614 | bn_espocrm | localhost | bitnami_espocrm | Query | 31 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
            | 992615 | bn_espocrm | localhost | bitnami_espocrm | Query | 31 | Waiting for table level lock | SELECT user.id AS `id`, TRIM(CONCAT(IFNULL(user.first_name, ''), ' ', IFNULL(user.last_name, ''))) A |
            |
            Last edited by khopper; 01-22-2019, 02:04 PM.

            Comment

            • #9
              tarasm I noticed the DB is mostly MyISAM and not Innodb after checking the tables "SHOW TABLE STATUS FROM dbname;"
              Would it be safe to convert all tables to Innodb so that only row level locking occurs and not table level locking?

              Comment

              • khopper
                #9.1
                khopper commented
                01-22-2019, 03:17 PM
                Editing a comment
                Also - how do i find out what Query this is that has been stuck running for over 24 min? (item on the bottom that has been sending data for 1453 seconds.)

                mysql> SHOW PROCESSLIST;
                +-------+------------+--------------------+-----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
                | Id | User | Host | db | Command | Time | State | Info |
                +-------+------------+--------------------+-----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
                | 7589 | khopper | 192.168.4.99:53828 | NULL | Sleep | 3953 | | NULL |
                | 7675 | khopper | 192.168.4.99:53832 | bitnami_espocrm | Sleep | 3440 | | NULL |
                | 8453 | khopper | 192.168.4.99:53889 | bitnami_espocrm | Query | 0 | init | SHOW PROCESSLIST |
                | 42701 | bn_espocrm | localhost | bitnami_espocrm | Query | 1453 | Sending data | SELECT DATE_FORMAT(account.orderdate, '%Y-%m-%d') AS `DAYrderdate`, TRIM(CONCAT(assignedUser.first |
                +-------+------------+--------------------+-----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
                4 rows in set
            • #10
              Try to disable all your Scheduled Jobs in the administration
              Job Offers and Requests

              Comment

              • khopper
                #10.1
                khopper commented
                01-22-2019, 11:51 PM
                Editing a comment
                I disabled some of the unneeded scheduled jobs for now and will see what happens.
            • #11
              Try also to rebuild your database (Administration > Rebuild)
              Job Offers and Requests

              Comment

              • khopper
                #11.1
                khopper commented
                01-22-2019, 11:52 PM
                Editing a comment
                I have already done this after the upgrade. But I will do it again.
            • #12
              What is your MySQL version? We recommend to convert all database tables to InnoDB. But before this process, please create a backup of your database, https://www.espocrm.com/documentatio...p-and-restore/.
              Job Offers and Requests

              Comment

              • khopper
                #12.1
                khopper commented
                01-23-2019, 04:30 PM
                Editing a comment
                MySQL Version 5.6.33-0ubuntu0.14.04.1
            • #13
              It's an old MySQL. You almost will not get any benefits of changing table engine to InnoDB. We recommend to use MySQL 5.7+
              Job Offers and Requests

              Comment

              • #14
                tarasm I have managed to rule it down to reporting, reports in particular when filtering by field "teams".
                If I remove the filed "teams" from the filter the report comes up instantly, less than .5 of a second.
                However when filtering by field "teams" it will hang the entire server and cause all 4 cores of my CPU to hit 100% and espo becomes completely unresponsive and timing out.
                The query when running will literally take 30 minutes to complete if I let it go.
                I have to kill the sql query that I see running to get back to normal operations.

                These reports ran perfectly and fast before upgrading from 5.4.3 to 5.5.5+, could there be a bug?
                1 Photo

                Comment

                • #15
                  We will check it, thanks
                  Job Offers and Requests

                  Comment

                  Previous 1 2 template Next
                  Working...
                  X