How to fix CR_NET_PACKET_TOO_LARGE (2020): Got packet bigger than max_allowed_packet in MySQL

-- Check server max_allowed_packet
SHOW VARIABLES LIKE 'max_allowed_packet';

-- Check global and session values
SELECT @@global.max_allowed_packet, @@session.max_allowed_packet;

-- Check in bytes (divide by 1024*1024 for MB)
SELECT @@global.max_allowed_packet / 1024 / 1024 as max_mb;

-- Common default values:
-- MySQL 5.7: 4MB (4194304 bytes)
-- MySQL 8.0: 64MB (67108864 bytes)
-- MariaDB: 16MB (16777216 bytes)

# Edit MySQL configuration file
sudo nano /etc/mysql/my.cnf
# or
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or
sudo nano /etc/my.cnf

# Add or modify under [mysqld] section
[mysqld]
max_allowed_packet = 256M

# Common values:
# 64M  - Moderate applications
# 128M - Applications with medium-sized BLOBs
# 256M - Large media files or datasets
# 512M - Very large operations
# 1G   - Maximum recommended for most systems

# Also set for client connections (optional)
[client]
max_allowed_packet = 256M

[mysql]
max_allowed_packet = 256M

# Restart MySQL service
sudo systemctl restart mysql
# or
sudo service mysql restart

# Verify new setting
mysql -e "SHOW VARIABLES LIKE 'max_allowed_packet';"

-- Set for current session only
SET SESSION max_allowed_packet = 268435456; -- 256MB

-- Or set globally without restart (affects new connections)
SET GLOBAL max_allowed_packet = 268435456;

-- Verify the change took effect
SELECT @@session.max_allowed_packet;

// PHP PDO example
$pdo = new PDO($dsn, $user, $pass, [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION max_allowed_packet=268435456"
]);

// PHP mysqli example
$mysqli = new mysqli($host, $user, $pass, $db);
$mysqli->query("SET SESSION max_allowed_packet=268435456");

# Python MySQL connector
import mysql.connector

config = {
    'user': 'username',
    'password': 'password',
    'database': 'mydb',
    'connection_timeout': 30
}

connection = mysql.connector.connect(**config)
cursor = connection.cursor()
cursor.execute("SET SESSION max_allowed_packet=268435456")

// Node.js mysql2
const mysql = require('mysql2/promise');

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

await connection.execute('SET SESSION max_allowed_packet=268435456');

-- Instead of one large INSERT:
INSERT INTO large_table VALUES (1, 'huge data...'), (2, 'more data...'), ...;

-- Use multiple smaller INSERTs:
INSERT INTO large_table VALUES (1, 'chunk1...');
INSERT INTO large_table VALUES (2, 'chunk2...');
-- etc.

-- Or use transactions with chunked inserts:
START TRANSACTION;
INSERT INTO large_table VALUES (1, 'data1');
INSERT INTO large_table VALUES (2, 'data2');
-- ... up to reasonable chunk size
COMMIT;

-- Store large files in chunks
CREATE TABLE file_chunks (
    file_id INT,
    chunk_number INT,
    chunk_data LONGBLOB,
    PRIMARY KEY (file_id, chunk_number)
);

-- Or use file system storage with path references
ALTER TABLE documents ADD COLUMN file_path VARCHAR(500);
-- Store files on disk, keep only metadata in database

// PHP chunked insert example
$largeData = [...]; // Array of data
$chunkSize = 1000;

foreach (array_chunk($largeData, $chunkSize) as $chunk) {
    $placeholders = implode(',', array_fill(0, count($chunk), '(?, ?)'));
    $stmt = $pdo->prepare("INSERT INTO table (col1, col2) VALUES $placeholders");
    
    $flattened = [];
    foreach ($chunk as $row) {
        $flattened[] = $row[0];
        $flattened[] = $row[1];
    }
    
    $stmt->execute($flattened);
}

# On master server my.cnf
[mysqld]
max_allowed_packet = 256M

# On slave server my.cnf
[mysqld]
max_allowed_packet = 256M
slave_max_allowed_packet = 256M  # MariaDB specific

# For MySQL replication thread
[mysqld]
slave_max_allowed_packet = 256M

-- Check binlog format
SHOW VARIABLES LIKE 'binlog_format';

-- ROW-based replication may create larger events
-- Consider using MIXED or STATEMENT format if packet size is issue

-- Check max_binlog_size
SHOW VARIABLES LIKE 'max_binlog_size';

-- Ensure max_binlog_size is reasonable (default 1GB)
-- In my.cnf:
max_binlog_size = 100M

-- Split large transactions
START TRANSACTION;
-- Some operations
COMMIT;

START TRANSACTION;
-- More operations
COMMIT;

-- Instead of one huge transaction

-- Use appropriate data types
-- Instead of LONGTEXT for all text, use:
VARCHAR(255)  -- For short text
TEXT          -- Up to 64KB
MEDIUMTEXT    -- Up to 16MB
LONGTEXT      -- Up to 4GB (may cause packet issues)

-- Similarly for BLOB:
TINYBLOB      -- Up to 255 bytes
BLOB          -- Up to 64KB
MEDIUMBLOB    -- Up to 16MB
LONGBLOB      -- Up to 4GB

-- Consider compression for large text/BLOB columns
ALTER TABLE documents 
MODIFY COLUMN content LONGBLOB 
COMPRESSION='zlib';

-- Or use COMPRESS()/UNCOMPRESS() functions
INSERT INTO table (compressed_data) 
VALUES (COMPRESS('large text data...'));

SELECT UNCOMPRESS(compressed_data) FROM table;

-- Partition large tables
CREATE TABLE large_table (
    id INT,
    data TEXT,
    created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

-- Use LIMIT for large result sets
SELECT * FROM large_table LIMIT 1000;

-- Fetch in batches
SELECT * FROM large_table WHERE id > ? LIMIT 1000;

-- Avoid SELECT * on tables with large columns
SELECT id, name, created_at FROM large_table;
-- Instead of SELECT *

-- Use indexed columns in WHERE clauses
SELECT * FROM large_table WHERE indexed_column = ?;