如何获取MariaDB创建触发器的SUPER权限？解决ERROR 1419报错

Got it, let's tackle this error you're hitting head-on. The ERROR 1419 (HY000) you're seeing is directly tied to MySQL's binary logging (binlog) security rules, and it's easy to resolve with one of two approaches.

Why This Happens #

When binary logging is enabled (common for replication or point-in-time recovery), MySQL wants to ensure any triggers, stored procedures, or custom functions you create can be safely replicated to slave servers without causing data inconsistencies. By default, it restricts creation of these objects to users with the SUPER privilege. The error message hints at an alternative: relaxing this restriction with the log_bin_trust_function_creators variable (note the "less safe" warning—we'll cover when to use which option).

Looking at your trigger code, the CALL change... line means you're invoking a stored procedure inside the trigger—this counts as a "side-effecting" operation that triggers the binlog check.

Solutions #

Option 1: Relax the Binlog Trust Restriction (Good for Testing/Local Environments) #

If you're working on a local dev setup or have permission to adjust MySQL variables, tweak the log_bin_trust_function_creators setting:

• Temporary fix (resets on MySQL restart):
  Run this query (you may need SUPER privilege to set the global variable):

  SET GLOBAL log_bin_trust_function_creators = 1;
  

  Or apply it only to your current session:

  SET SESSION log_bin_trust_function_creators = 1;
  

• Permanent fix (survives restarts):
  Edit your MySQL config file (my.cnf on Linux, my.ini on Windows) and add this line under the [mysqld] section:

  log_bin_trust_function_creators = 1
  

  Save the file and restart your MySQL service.

Option 2: Grant the SUPER Privilege (Recommended for Production) #

For production environments, stick with MySQL's default security model by getting the SUPER privilege assigned to your user. Ask your database admin to run these commands:

GRANT SUPER ON *.* TO 'your_username'@'your_host';
FLUSH PRIVILEGES;

Once you have this privilege, you'll be able to create your trigger without hitting the error.

Quick reminder: Make sure your trigger code is complete (you had CALL change... cut off) before re-running it—missing syntax could cause other errors even after fixing the permission issue.

内容的提问来源于stack exchange，提问作者Patrick Hammet