To troubleshoot the #1227 error in phpMyAdmin, indicating access denied for changing the sql_mode variable without SUPER or SYSTEM_VARIABLES_ADMIN privileges, you can follow these steps:
Check Current Privileges: Ensure that your MySQL user account has the necessary privileges to modify the
sql_modevariable. You need either SUPER or SYSTEM_VARIABLES_ADMIN privileges for this operation.Review User Privileges: In phpMyAdmin, navigate to the “User Accounts” or “Privileges” section and check the privileges assigned to your MySQL user. If necessary, contact your database administrator to grant the required privileges.
Grant SUPER or SYSTEM_VARIABLES_ADMIN Privilege: If your user lacks the necessary privileges, you’ll need to grant either SUPER or SYSTEM_VARIABLES_ADMIN privilege. Use MySQL’s
GRANTstatement to provide the required access.
grant all privileges on databasename.* to sqluser@'%' with grant option;or
GRANT SUPER ON on databasename.* to sqluser@'%' with grant option;
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO sqlsuser;Replace ‘your_user’ and ‘your_host’ with your actual MySQL username and host.
Restart MySQL Server: After making changes to user privileges, restart the MySQL server to apply the modifications.
Verify
sql_modeConfiguration: Check the currentsql_modeconfiguration. If it includes specific modes that you are trying to enable or disable, ensure that you have the privilege to modify them.Use Configuration Files: Instead of modifying
sql_modedirectly through phpMyAdmin, consider updating the MySQL configuration files (e.g., my.cnf or my.ini). This can be done by adding or modifying thesql_modeentry. After making changes, restart the MySQL server.Contact Database Administrator: If the issue persists, contact your database administrator or hosting provider for further assistance. They can help review and adjust the necessary privileges or troubleshoot other potential issues.
Remember to exercise caution when modifying user privileges, and always have a backup of your database before making significant changes.
Cloudpanel Tutorial
Login using SSH
/etc/mysql/debian.cnfOpen debian.cnf File and copy the username and password from debian.cnf file
host = localhost
user = debian-sys-maint
password = anyTYpeOFPasswordNow Open SSH, add the below command and enter your password on prompt
mysql -u debian-sys-maint -p After applying the correct password below prompt will display
Now apply the below script
grant all privileges on databasename.* to sqluser@'%' with grant option;
GRANT SUPER ON on databasename.* to sqluser@'%' with grant option;
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO sqlsuser;Now to confirm your changes paste the below code.
SHOW GRANTS FOR 'sqluser';Now you can easily update phpmyadmin Server variables and settings
Note: If your SQL server reseting your changes use below code
set PERSIST sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';