(Got an error reading communication packets)

Phalanx · Feb 16th 2019

Seit den Updates diese Woche habe ich den Fehler im MySQL-Error-Log. Er tritt je nach Tageszeit ungefähr jede Stunde auf, nachts seltener, am Tag oft häufiger.

Es geht um eine Galerie mit Blog, als Datenbank läuft 1:10.2.22+maria~bionic als drop-in replacement.

Zur Fehleranalyse habe ich dann den General Query Log aktiviert. Über den Vergleich der Uhrzeit der beiden Logs, sowie der Connection-ID ist es möglich den Ort genau zu bestimmen. Jetzt können Entwickler eventuell feststellen was zum Fehler führt und wie er behoben werden kann.

Code: /var/log/mysql/error.log

2019-02-16  7:24:25 140627502085888 [Warning] Aborted connection 17938 to db: 'DomainX-Database' user: 'DomainX-DB-User' host: 'localhost' (Got an error reading communication packets)
2019-02-16  8:10:22 140627492665088 [Warning] Aborted connection 18289 to db: 'DomainX-Database' user: 'DomainX-DB-User' host: 'localhost' (Got an error reading communication packets)
2019-02-16 13:37:14 140627493074688 [Warning] Aborted connection 27945 to db: 'DomainX-Database' user: 'DomainX-DB-User' host: 'localhost' (Got an error reading communication packets)
2019-02-16 14:06:50 140627502085888 [Warning] Aborted connection 29159 to db: 'DomainX-Database' user: 'DomainX-DB-User' host: 'localhost' (Got an error reading communication packets)

Die zwei gefundene Fehler/Abbrüche/unsauber abgebaute oder noch offene Verbindung in /var/log/mysql/mysql.log

Code: Fehler-Uhrzeit 7:24:25 - Connection ID 17938

190216  7:24:25    17938 Connect    Domain-5-database@localhost as anonymous on Domain-5-database
        17938 Query    SET NAMES 'utf8mb4', SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES'
        17938 Prepare    SELECT    *
            FROM    wcf1_session
            WHERE    spiderID = ?
                AND userID IS NULL
        17938 Execute    SELECT    *
            FROM    wcf1_session
            WHERE    spiderID = '32'
                AND userID IS NULL
        17938 Close stmt    
        17938 Prepare    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN (?)
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        17938 Execute    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN ('1509')
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        17938 Close stmt    
        17938 Prepare    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN (?)
        17938 Execute    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN ('1509')
        17938 Close stmt    
        17938 Prepare    SELECT    *
                FROM    gallery1_album
                WHERE    albumID = ?
        17938 Execute    SELECT    *
                FROM    gallery1_album
                WHERE    albumID = '360'
        17938 Close stmt    
        17938 Prepare    UPDATE    gallery1_image
            SET    views = views + ?
            WHERE    imageID = ?
        17938 Execute    UPDATE    gallery1_image
            SET    views = views + '1'
            WHERE    imageID = '1509'
        17938 Close stmt    
        17938 Prepare    SELECT    
                    comment.*
                FROM    wcf1_comment comment
                    
                    WHERE comment.objectTypeID = ? AND comment.objectID = ? AND comment.isDisabled = 0
                    ORDER BY comment.time DESC LIMIT 30
        17938 Execute    SELECT    
                    comment.*
                FROM    wcf1_comment comment
                    
                    WHERE comment.objectTypeID = '265' AND comment.objectID = '1509' AND comment.isDisabled = 0
                    ORDER BY comment.time DESC LIMIT 30
        17938 Close stmt    
        17938 Prepare    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = ? AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND image.isDisabled = ? AND image.isDeleted = ? AND image.userID = ? AND image.imageID <> ?
                ORDER BY image.uploadTime DESC, image.imageID DESC LIMIT 10
        17938 Execute    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = '0' AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN ('29','53','58','59','63','66','69','71','76','30','54','55','60','64','67','70','72','31','56','61','65','68','75','32','57','62','73','44','74','45','2','33','50','35','37','51','36','46','52','40','41','47','42','43','49','48','39','38','34')) AND image.isDisabled = '0' AND image.isDeleted = '0' AND image.userID = '354' AND image.imageID <> '1509'
                ORDER BY image.uploadTime DESC, image.imageID DESC LIMIT 10
        17938 Close stmt    
        17938 Prepare    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN (?,?)
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        17938 Execute    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN ('1266','1148')
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        17938 Close stmt    
        17938 Prepare    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = ? AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND image.isDisabled = ? AND image.isDeleted = ? AND image.albumID = ?
                ORDER BY image.showOrder ASC
        17938 Execute    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = '0' AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN ('29','53','58','59','63','66','69','71','76','30','54','55','60','64','67','70','72','31','56','61','65','68','75','32','57','62','73','44','74','45','2','33','50','35','37','51','36','46','52','40','41','47','42','43','49','48','39','38','34')) AND image.isDisabled = '0' AND image.isDeleted = '0' AND image.albumID = '360'
                ORDER BY image.showOrder ASC
        17938 Close stmt    
        17938 Prepare    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN (?,?,?)
                    ORDER BY image.showOrder ASC
        17938 Execute    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN ('1148','1266','1509')
                    ORDER BY image.showOrder ASC
        17938 Close stmt    
        17938 Prepare    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN (?,?,?)
        17938 Execute    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN ('1148','1266','1509')
        17938 Close stmt    
        17938 Prepare    SELECT        tag.*, tag_to_object.objectID
            FROM        wcf1_tag_to_object tag_to_object
            LEFT JOIN    wcf1_tag tag
            ON        (tag.tagID = tag_to_object.tagID)
            WHERE tag_to_object.objectTypeID = ? AND tag_to_object.objectID IN (?)
        17938 Execute    SELECT        tag.*, tag_to_object.objectID
            FROM        wcf1_tag_to_object tag_to_object
            LEFT JOIN    wcf1_tag tag
            ON        (tag.tagID = tag_to_object.tagID)
            WHERE tag_to_object.objectTypeID = '266' AND tag_to_object.objectID IN ('1509')
        17938 Close stmt    
        17938 Prepare    SELECT        like_object.*, 0 AS liked
                FROM        wcf1_like_object like_object
                WHERE like_object.objectTypeID = ? AND like_object.objectID IN (?)
        17938 Execute    SELECT        like_object.*, 0 AS liked
                FROM        wcf1_like_object like_object
                WHERE like_object.objectTypeID = '267' AND like_object.objectID IN ('1509')
        17938 Close stmt    
        17938 Prepare    SELECT    user_option_value.*,user_avatar.*,user_rank.*, session.pageID, session.pageObjectID, session.lastActivityTime AS sessionLastActivityTime,
                    user_table.*
                FROM    wcf1_user user_table
                     LEFT JOIN wcf1_user_option_value user_option_value ON (user_option_value.userID = user_table.userID) LEFT JOIN wcf1_user_avatar user_avatar ON (user_avatar.avatarID = user_table.avatarID) LEFT JOIN wcf1_user_rank user_rank ON (user_rank.rankID = user_table.rankID) LEFT JOIN wcf1_session session ON (session.userID = user_table.userID)
                WHERE    user_table.userID IN (?)
                    ORDER BY user_table.username
        17938 Execute    SELECT    user_option_value.*,user_avatar.*,user_rank.*, session.pageID, session.pageObjectID, session.lastActivityTime AS sessionLastActivityTime,
                    user_table.*
                FROM    wcf1_user user_table
                     LEFT JOIN wcf1_user_option_value user_option_value ON (user_option_value.userID = user_table.userID) LEFT JOIN wcf1_user_avatar user_avatar ON (user_avatar.avatarID = user_table.avatarID) LEFT JOIN wcf1_user_rank user_rank ON (user_rank.rankID = user_table.rankID) LEFT JOIN wcf1_session session ON (session.userID = user_table.userID)
                WHERE    user_table.userID IN ('354')
                    ORDER BY user_table.username

Display More

Code: Fehler-Uhrzeit 8:10:22 - Connection ID 18289

190216  8:10:22    18289 Connect    Domain-5-database@localhost as anonymous on Domain-5-database
        18289 Query    SET NAMES 'utf8mb4', SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES'
        18289 Prepare    SELECT    *
            FROM    wcf1_session
            WHERE    spiderID = ?
                AND userID IS NULL
        18289 Execute    SELECT    *
            FROM    wcf1_session
            WHERE    spiderID = '32'
                AND userID IS NULL
        18289 Close stmt    
        18289 Prepare    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN (?)
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        18289 Execute    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN ('641')
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        18289 Close stmt    
        18289 Prepare    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN (?)
        18289 Execute    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN ('641')
        18289 Close stmt    
        18289 Prepare    SELECT    *
                FROM    gallery1_album
                WHERE    albumID = ?
        18289 Execute    SELECT    *
                FROM    gallery1_album
                WHERE    albumID = '186'
        18289 Close stmt    
        18289 Prepare    UPDATE    gallery1_image
            SET    views = views + ?
            WHERE    imageID = ?
        18289 Execute    UPDATE    gallery1_image
            SET    views = views + '1'
            WHERE    imageID = '641'
        18289 Close stmt    
        18289 Prepare    SELECT    
                    comment.*
                FROM    wcf1_comment comment
                    
                    WHERE comment.objectTypeID = ? AND comment.objectID = ? AND comment.isDisabled = 0
                    ORDER BY comment.time DESC LIMIT 30
        18289 Execute    SELECT    
                    comment.*
                FROM    wcf1_comment comment
                    
                    WHERE comment.objectTypeID = '265' AND comment.objectID = '641' AND comment.isDisabled = 0
                    ORDER BY comment.time DESC LIMIT 30
        18289 Close stmt    
        18289 Prepare    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = ? AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND image.isDisabled = ? AND image.isDeleted = ? AND image.userID = ? AND image.imageID <> ?
                ORDER BY image.uploadTime DESC, image.imageID DESC LIMIT 10
        18289 Execute    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = '0' AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN ('29','53','58','59','63','66','69','71','76','30','54','55','60','64','67','70','72','31','56','61','65','68','75','32','57','62','73','44','74','45','2','33','50','35','37','51','36','46','52','40','41','47','42','43','49','48','39','38','34')) AND image.isDisabled = '0' AND image.isDeleted = '0' AND image.userID = '184' AND image.imageID <> '641'
                ORDER BY image.uploadTime DESC, image.imageID DESC LIMIT 10
        18289 Close stmt    
        18289 Prepare    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN (?,?,?)
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        18289 Execute    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN ('642','640','639')
                    ORDER BY image.uploadTime DESC, image.imageID DESC
        18289 Close stmt    
        18289 Prepare    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = ? AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) AND image.isDisabled = ? AND image.isDeleted = ? AND image.albumID = ?
                ORDER BY image.showOrder ASC
        18289 Execute    SELECT    image.imageID AS objectID
            FROM    gallery1_image image
                
                WHERE image.accessLevel = '0' AND image.imageID IN (SELECT imageID FROM gallery1_image_to_category WHERE categoryID IN ('29','53','58','59','63','66','69','71','76','30','54','55','60','64','67','70','72','31','56','61','65','68','75','32','57','62','73','44','74','45','2','33','50','35','37','51','36','46','52','40','41','47','42','43','49','48','39','38','34')) AND image.isDisabled = '0' AND image.isDeleted = '0' AND image.albumID = '186'
                ORDER BY image.showOrder ASC
        18289 Close stmt    
        18289 Prepare    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN (?,?,?,?)
                    ORDER BY image.showOrder ASC
        18289 Execute    SELECT    like_object.likes, like_object.dislikes, image.*, '' AS rawExifData
                    
                FROM    gallery1_image image
                     LEFT JOIN wcf1_like_object like_object ON (like_object.objectTypeID = 267 AND like_object.objectID = image.imageID)
                WHERE    image.imageID IN ('639','640','641','642')
                    ORDER BY image.showOrder ASC
        18289 Close stmt    
        18289 Prepare    SELECT    *
                    FROM    gallery1_image_to_category
                    WHERE imageID IN (?,?,?,?)
        18289 Close stmt    
        18289 Prepare    SELECT        tag.*, tag_to_object.objectID
            FROM        wcf1_tag_to_object tag_to_object
            LEFT JOIN    wcf1_tag tag
            ON        (tag.tagID = tag_to_object.tagID)
            WHERE tag_to_object.objectTypeID = ? AND tag_to_object.objectID IN (?)
        18289 Execute    SELECT        tag.*, tag_to_object.objectID
            FROM        wcf1_tag_to_object tag_to_object
            LEFT JOIN    wcf1_tag tag
            ON        (tag.tagID = tag_to_object.tagID)
            WHERE tag_to_object.objectTypeID = '266' AND tag_to_object.objectID IN ('641')
        18289 Close stmt    
        18289 Prepare    SELECT        like_object.*, 0 AS liked
                FROM        wcf1_like_object like_object
                WHERE like_object.objectTypeID = ? AND like_object.objectID IN (?)
        18289 Execute    SELECT        like_object.*, 0 AS liked
                FROM        wcf1_like_object like_object
                WHERE like_object.objectTypeID = '267' AND like_object.objectID IN ('641')
        18289 Close stmt    
        18289 Prepare    SELECT    user_option_value.*,user_avatar.*,user_rank.*, session.pageID, session.pageObjectID, session.lastActivityTime AS sessionLastActivityTime,
                    user_table.*
                FROM    wcf1_user user_table
                     LEFT JOIN wcf1_user_option_value user_option_value ON (user_option_value.userID = user_table.userID) LEFT JOIN wcf1_user_avatar user_avatar ON (user_avatar.avatarID = user_table.avatarID) LEFT JOIN wcf1_user_rank user_rank ON (user_rank.rankID = user_table.rankID) LEFT JOIN wcf1_session session ON (session.userID = user_table.userID)
                WHERE    user_table.userID IN (?,?)
                    ORDER BY user_table.username
        18289 Execute    SELECT    user_option_value.*,user_avatar.*,user_rank.*, session.pageID, session.pageObjectID, session.lastActivityTime AS sessionLastActivityTime,
                    user_table.*
                FROM    wcf1_user user_table
                     LEFT JOIN wcf1_user_option_value user_option_value ON (user_option_value.userID = user_table.userID) LEFT JOIN wcf1_user_avatar user_avatar ON (user_avatar.avatarID = user_table.avatarID) LEFT JOIN wcf1_user_rank user_rank ON (user_rank.rankID = user_table.rankID) LEFT JOIN wcf1_session session ON (session.userID = user_table.userID)
                WHERE    user_table.userID IN ('184','1')
                    ORDER BY user_table.username

Display More

Zur Info für Interessierte. Hier das Ende der Verbindung wenn sie normal und sauber abgebaut wird, oder geschlossen wird.

Code

18290 Close stmt    
18290 Query    START TRANSACTION
18290 Query    COMMIT
18290 Quit

Kann mir hier jemand helfen?

(Got an error reading communication packets)

Die zwei gefundene Fehler/Abbrüche/unsauber abgebaute oder noch offene Verbindung in /var/log/mysql/mysql.log

Zur Info für Interessierte. Hier das Ende der Verbindung wenn sie normal und sauber abgebaut wird, oder geschlossen wird.

Participate now!

Share