Xử lý lỗi “1118 – Row size too large” của MySQL/MariaDB

Trong quá trình alter trong sql đôi khi chúng ta gặp thông báo lỗi:

1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Lỗi 1118 - Row size too large xảy ra khi tổng kích thước của tất cả các cột trong bảng vượt quá giới hạn tối đa của MySQL/MariaDB, là 8126 bytes cho mỗi hàng, đối với bảng sử dụng định dạng lưu trữ InnoDB.

Lỗi 1118 - Row size too large thường xuất hiện trong các tình huống sau:

1. Tạo bảng với quá nhiều cột
   Ví dụ: CREATE TABLE example ( col1 VARCHAR(255), col2 VARCHAR(255), ... col300 VARCHAR(255) -- quá nhiều cột ) ENGINE=InnoDB; Khi tổng kích thước của tất cả các cột trong hàng vượt quá giới hạn 8126 bytes, lỗi sẽ xảy ra.
2. Sử dụng kiểu dữ liệu tốn dung lượng
   Nếu bảng có nhiều cột sử dụng kiểu dữ liệu VARCHAR, CHAR, hoặc TEXT với độ dài lớn: CREATE TABLE example ( col1 CHAR(500), col2 VARCHAR(1000), col3 VARCHAR(3000) ) ENGINE=InnoDB; Các kiểu này có thể nhanh chóng làm tăng kích thước hàng vượt mức giới hạn.
3. Chèn dữ liệu quá lớn vào một hàng
   Nếu các cột trong bảng cho phép lưu trữ lượng lớn dữ liệu (dù không vượt giới hạn kiểu dữ liệu), một hàng quá lớn cũng có thể gây lỗi: INSERT INTO example (col1, col2, col3) VALUES (REPEAT('a', 500), REPEAT('b', 1000), REPEAT('c', 3000));
4. Sử dụng kiểu CHAR thay vì VARCHAR
   Kiểu dữ liệu CHAR cố định dung lượng lưu trữ cho mỗi hàng, dẫn đến kích thước lớn hơn VARCHAR ngay cả khi dữ liệu thực tế nhỏ.
5. Cố gắng thêm cột vào bảng hiện có
   Khi bảng đã gần đạt giới hạn kích thước hàng, việc thêm cột mới có thể dẫn đến lỗi: ALTER TABLE example ADD COLUMN col_new VARCHAR(255);
6. Sử dụng ROW_FORMAT không phù hợp
   Nếu bảng sử dụng định dạng hàng COMPACT hoặc REDUNDANT, tất cả dữ liệu được lưu trực tiếp trong hàng, làm tăng kích thước hàng và dễ gây lỗi.

Tóm lại:

Lỗi này xảy ra khi thiết kế bảng hoặc dữ liệu vượt quá giới hạn kích thước hàng của MySQL, đặc biệt với các bảng lớn, kiểu dữ liệu không tối ưu, hoặc cấu hình không hợp lý.

Cách khắc phục

1. Chuyển đổi các cột sang kiểu TEXT hoặc BLOB

Các kiểu dữ liệu TEXT và BLOB được lưu trong không gian riêng biệt (không nằm trực tiếp trong hàng). Vì vậy, bạn có thể chuyển các cột không cần thiết sang TEXT hoặc BLOB:

ALTER TABLE your_table_name
MODIFY column_name TEXT;

Lưu ý: TEXT và BLOB không hỗ trợ chỉ mục đầy đủ nếu không chỉ định độ dài, nên cần cân nhắc khi thay đổi.

2. Kiểm tra và tối ưu hóa kiểu dữ liệu

• Sử dụng VARCHAR thay vì CHAR để giảm dung lượng lưu trữ.
• Xem xét sử dụng các kiểu dữ liệu nhỏ hơn (ví dụ, TINYINT thay vì INT, FLOAT thay vì DOUBLE nếu không cần độ chính xác cao).
• Loại bỏ các cột không cần thiết hoặc tách bảng thành nhiều bảng.

3. Bật ROW_FORMAT=COMPRESSED hoặc DYNAMIC

Các định dạng hàng này cho phép lưu trữ cột TEXT/BLOB bên ngoài bảng chính, giảm kích thước hàng. Bạn có thể kiểm tra và thay đổi định dạng hàng như sau:

• Kiểm tra định dạng hàng hiện tại: SHOW TABLE STATUS WHERE Name = 'your_table_name';
• Cập nhật bảng sang định dạng DYNAMIC hoặc COMPRESSED: ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;

4. Chia bảng thành nhiều bảng nhỏ hơn

Nếu bảng của bạn chứa quá nhiều cột, bạn có thể tách bảng thành các bảng nhỏ hơn và liên kết chúng qua khóa ngoại (FOREIGN KEY).

5. Tăng kích thước trang InnoDB

Nếu không thể giảm kích thước hàng, bạn có thể tăng kích thước trang InnoDB từ mặc định 16 KB lên 32 KB hoặc 64 KB. Tuy nhiên, điều này yêu cầu cấu hình lại MySQL:

• Thêm vào tệp cấu hình (my.cnf hoặc my.ini): [mysqld] innodb_page_size=32K
• Khởi động lại dịch vụ MySQL.

Lưu ý: Thay đổi này chỉ áp dụng cho các bảng được tạo sau khi cấu hình thay đổi.