Cách sửa lỗi MySQL ERROR 1418: Thiếu DETERMINISTIC hoặc READS SQL DATA

Kịch bản lỗiBạn vừa hoàn thành việc viết một hàm (function) MySQL tùy chỉnh để tự động hóa quy trình làm việc. Bạn nhấn thực thi và mong đợi một thông báo thành công, nhưng cơ sở dữ liệu lại trả về đoạn văn bản gây khó chịu này:

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled...

Lỗi này thường gặp trên các máy chủ production nơi binary logging (binlog) đang hoạt động để phục vụ replication (sao chép) hoặc point-in-time recovery (khôi phục tại một thời điểm). Nếu bạn đang sử dụng dịch vụ quản lý như AWS RDS hoặc Google Cloud SQL, bạn sẽ thường xuyên thấy lỗi này vì họ mặc định bật binary logging để xử lý sao lưu và tính sẵn sàng cao.

Tại sao MySQL chặn bạnHãy coi binary logging như một hộp đen ghi lại chuyến bay. Để giữ cho máy chủ replica đồng bộ hoàn hảo với máy chủ master, MySQL cần đảm bảo rằng mọi lời gọi hàm đều tạo ra kết quả giống nhau trên cả hai máy. Nếu một hàm "không thể dự đoán", hai máy chủ cuối cùng sẽ bị lệch dữ liệu, dẫn đến hỏng dữ liệu.

Ví dụ, một hàm sử dụng UUID() hoặc RAND() là không xác định (non-deterministic); nó tạo ra một giá trị khác nhau mỗi khi chạy. Nếu MySQL cho phép các hàm này mà không có khai báo cụ thể, master có thể lưu 'ID-A' trong khi replica lại lưu 'ID-B'. Để ngăn chặn cơn ác mộng này, MySQL mặc định đặt log_bin_trust_function_creators thành 0 (OFF), buộc bạn phải chứng minh hàm của mình là an toàn.

Cách khắc phụcBạn có hai cách: viết SQL tốt hơn bằng cách khai báo ý định của hàm, hoặc yêu cầu MySQL tin tưởng bạn một cách mù quáng. Cách đầu tiên là tiêu chuẩn chuyên nghiệp cho môi trường production.

Cách 1: Khai báo các đặc điểm của hàm (Khuyên dùng)Thêm một từ khóa duy nhất sẽ cho MySQL biết chính xác cách hàm của bạn tương tác với dữ liệu. Hãy đặt một trong các từ khóa này ngay sau mệnh đề RETURNS:

• DETERMINISTIC: Hàm luôn trả về cùng một kết quả cho cùng một đầu vào (ví dụ: tính thuế).- NO SQL: Hàm không chạm vào cơ sở dữ liệu (ví dụ: xử lý chuỗi).- READS SQL DATA: Hàm sử dụng lệnh SELECT nhưng không bao giờ sửa đổi dữ liệu.- MODIFIES SQL DATA: Hàm thực hiện các thao tác INSERT, UPDATE, hoặc DELETE.Ví dụ về một hàm đã được sửa:

DELIMITER //
CREATE FUNCTION get_discount_price(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 0.90;
END //
DELIMITER ;

Cách 2: Sửa bằng biến toàn cục "Trust"Nếu bạn đang di chuyển hàng trăm hàm cũ hoặc làm việc trong môi trường phát triển cục bộ, việc cập nhật thủ công từng đoạn mã sẽ rất tẻ nhạt. Bạn có thể tắt tính năng kiểm tra nghiêm ngặt bằng cách bật một biến toàn cục. Việc này yêu cầu quyền SUPER, nghĩa là nó có thể không hoạt động trên các DB đám mây được quản lý nếu không thay đổi "Parameter Group" trước.

Chạy lệnh này trong terminal của bạn:

SET GLOBAL log_bin_trust_function_creators = 1;

Lưu ý: Thay đổi này có tính tạm thời. Nếu dịch vụ MySQL khởi động lại, thiết lập sẽ quay về 0.

Cách 3: Cấu hình vĩnh viễnĐể thiết lập tin cậy này có hiệu lực sau khi khởi động lại, hãy thêm nó vào tệp cấu hình máy chủ của bạn (my.cnf trên Linux hoặc my.ini trên Windows).

• Mở tệp cấu hình của bạn (thường ở /etc/mysql/).- Tìm phần tiêu đề [mysqld].- Thêm dòng này: log_bin_trust_function_creators = 1- Khởi động lại dịch vụ: sudo systemctl restart mysql## Xác minh giải phápKiểm tra xem thay đổi đã có hiệu lực chưa bằng cách truy vấn các biến hệ thống:

SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

Nếu giá trị là ON, bạn đã sẵn sàng. Hãy kiểm tra bằng cách tạo một hàm giả mà không có bất kỳ đặc điểm nào:

CREATE FUNCTION quick_test() RETURNS INT RETURN 1;

Lời kết? Mặc dù Cách 2 nhanh hơn, nhưng Cách 1 là lựa chọn kỹ thuật tối ưu hơn. Nó giúp tài liệu hóa hành vi mã nguồn của bạn và đảm bảo các bản ghi replication luôn sạch sẽ và có thể dự đoán được.