Cơn đau đầu vận hành lúc 2 giờ sáng
Lỗi này thường xảy ra ngay sau khi di chuyển cơ sở dữ liệu định kỳ hoặc nâng cấp máy chủ. Một phút trước ứng dụng của bạn vẫn chạy mượt mà; phút tiếp theo, các bảng điều khiển báo cáo của bạn bắt đầu xuất hiện các lỗi SQL khó hiểu. Có khả năng bạn đang nhìn vào một loạt văn bản thông báo rằng truy vấn GROUP BY của bạn không hợp lệ. Điều này xảy ra do các phiên bản MySQL hiện đại bật chế độ nghiêm ngặt (strict mode) theo mặc định—cụ thể là cài đặt ONLY_FULL_GROUP_BY.
Thông báo lỗi chính xác
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Tại sao lỗi này xảy ra
Trong MySQL 5.6 trở về trước, công cụ này hoạt động thoải mái hơn nhiều. Bạn có thể chọn bất kỳ cột nào, ngay cả khi nó không nằm trong mệnh đề GROUP BY của bạn. MySQL sẽ chỉ đơn giản lấy một giá trị từ hàng đầu tiên mà nó gặp trong nhóm đó. Mặc dù tiện lợi, nhưng điều này mang tính không xác định (non-deterministic). Về mặt kỹ thuật, nó vi phạm các quy tắc SQL tiêu chuẩn vì cơ sở dữ liệu về cơ bản là đang đoán xem bạn muốn giá trị nào.
Bắt đầu từ MySQL 5.7 và tiếp tục đến 8.0, ONLY_FULL_GROUP_BY được kích hoạt theo mặc định. Giờ đây, các quy tắc rất nghiêm ngặt: mọi cột trong danh sách SELECT của bạn phải nằm trong mệnh đề GROUP BY hoặc được bao bọc trong một hàm tổng hợp (aggregate function) như COUNT(), MAX(), SUM(), hoặc AVG().
Giải pháp 1: Viết lại truy vấn (Cách làm "Đúng")
Sửa mã nguồn là chiến lược dài hạn ổn định nhất. Nó đảm bảo các truy vấn của bạn luôn có thể dự đoán được và tuân thủ các tiêu chuẩn SQL. Nếu bạn cần một cột không phải là một phần của logic gom nhóm, bạn có hai lựa chọn chính.
Lựa chọn A: Thêm các cột vào GROUP BY
Nếu bạn chọn id và name nhưng chỉ gom nhóm theo name, hãy đưa cả id vào nhóm. Điều này cho MySQL biết rõ ràng cách xử lý dữ liệu.
-- Lệnh này thất bại:
SELECT id, name, COUNT(*) FROM products GROUP BY name;
-- Lệnh này hoạt động:
SELECT id, name, COUNT(*) FROM products GROUP BY name, id;
Lựa chọn B: Sử dụng ANY_VALUE()
Đôi khi bạn không thực sự quan tâm đến việc id nào được trả về. Có lẽ bạn biết chúng giống hệt nhau cho nhóm đó, hoặc bất kỳ giá trị đại diện nào cũng được. Hãy sử dụng hàm ANY_VALUE() để báo hiệu ý định của bạn cho MySQL.
SELECT ANY_VALUE(id), name, COUNT(*)
FROM products
GROUP BY name;
Giải pháp 2: Tắt ONLY_FULL_GROUP_BY cho Session
Có thể bạn cần một bản sửa lỗi ngay lập tức để khôi phục dịch vụ và không thể cấu trúc lại hàng chục truy vấn ngay tức khắc. Bạn có thể tắt chế độ này chỉ cho kết nối hiện tại của mình. Điều này hoàn hảo để thử nghiệm hoặc chạy các script cũ mà không thay đổi cài đặt toàn cục (global).
-- Kiểm tra các chế độ hiện tại của bạn trước
SELECT @@sql_mode;
-- Loại bỏ ONLY_FULL_GROUP_BY cho session hiện tại
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Giải pháp 3: Tắt ONLY_FULL_GROUP_BY trên toàn cục (Sửa lỗi vĩnh viễn)
Bạn đang quản lý một cơ sở mã nguồn cũ khổng lồ? Bạn có thể cần tắt bước kiểm tra này ở cấp độ máy chủ. Cảnh báo: Thay đổi này ảnh hưởng đến mọi cơ sở dữ liệu trên instance, điều này có thể che giấu các lỗi truy vấn thực sự ở những nơi khác.
Bước 1: Xác định vị trí tệp cấu hình MySQL của bạn
Trên hầu hết các bản phân phối Linux, bạn sẽ tìm thấy cấu hình tại đây:
/etc/mysql/my.cnf(Chung)/etc/mysql/mysql.conf.d/mysqld.cnf(Ubuntu/Debian)/etc/my.cnf(CentOS/RHEL)
Bước 2: Chỉnh sửa cấu hình
Mở tệp với quyền root: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf. Tìm phần [mysqld] và thêm hoặc sửa đổi dòng sql_mode. Bạn nên bao gồm các chế độ mặc định ngoại trừ chế độ đang gây rắc rối.
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Bước 3: Khởi động lại MySQL
Tải lại dịch vụ để áp dụng các thay đổi của bạn:
sudo systemctl restart mysql
Xác minh: Cách xác nhận bản sửa lỗi
Đừng bao giờ mặc định rằng thay đổi cấu hình đã hoạt động. Hãy chạy lệnh này trong terminal MySQL của bạn để xác minh trạng thái toàn cục:
SELECT @@GLOBAL.sql_mode;
Xác nhận rằng ONLY_FULL_GROUP_BY không còn trong danh sách. Nếu nó đã biến mất, các truy vấn cũ của bạn sẽ thực thi mà không gặp lỗi 1055.
Lời khuyên thực tế cho môi trường Production
- Cấu trúc lại truy vấn là cách bền vững nhất: Tắt chế độ này chỉ là một biện pháp tạm thời. Sử dụng
ANY_VALUE()hoặc tổng hợp đúng cách giúp ứng dụng của bạn tương thích với Postgres, SQL Server và các phiên bản MySQL trong tương lai. - Người dùng Laravel: Eloquent ORM của Laravel thường đặt các chế độ session riêng. Kiểm tra
config/database.phpcho'strict' => true. Đặt giá trị này thànhfalsethường giải quyết được vấn đề mà không cần động vào cấu hình máy chủ. - An toàn là trên hết: Luôn sao lưu
my.cnftrước khi chỉnh sửa. Một ký tự thừa cũng có thể ngăn cơ sở dữ liệu của bạn khởi động, biến một lỗi nhỏ thành một sự cố lớn.