When working with MySQL, encountering an Error 1095 – SQLSTATE: HY000 (ER_KILL_DENIED_ERROR) can be a bit perplexing. This error message indicates that you’re attempting to kill a thread or process for which you are not the owner. Understanding and resolving this error requires a few diagnostic steps and understanding the permissions within MySQL.
Understanding the Error
The full error message is usually “You are not owner of thread %lu,” where %lu is a placeholder for the thread ID. In MySQL, each connection to the database is represented by a thread. When you execute a KILL command, you’re instructing the server to terminate that connection. However, MySQL enforces certain security measures to prevent users from killing threads that they do not own, unless they have the necessary privileges.
Diagnosing the Issue
First, check which thread you are trying to kill and whether you have the correct privileges to do so:
SHOW PROCESSLIST;This command will display a list of all current threads. Locate the thread you are trying to kill and note its ID and the user who owns it.
Checking Privileges
If you are not the owner of the thread, you will need the SUPER privilege to kill it. To check if you have the SUPER privilege, run:
SHOW GRANTS FOR 'your_username'@'your_host';Replace 'your_username'@'your_host' with your actual username and host. Look for the SUPER privilege in the output.
Granting Privileges
If you do not have the SUPER privilege, you will need to ask your database administrator to grant it to you, or to kill the thread on your behalf. If you are the administrator, you can grant the privilege with:
GRANT SUPER ON *.* TO 'username'@'host';Replace 'username'@'host' with the appropriate username and host.
Using SYSTEM_USER Privilege (MySQL 8.0.14 and later)
In newer versions of MySQL (8.0.14 and later), the SYSTEM_USER privilege can be used to kill threads. If you encounter this error, you might need to grant the SYSTEM_USER privilege to the user:
GRANT SYSTEM_USER ON *.* TO 'username'@'host';Killing the Thread as the Owner
If you are the owner of the thread, ensure that you are connected to the MySQL server with the correct user account. If connected with the correct user, you should be able to kill your own threads:
KILL thread_id;Replace thread_id with the ID of the thread you want to kill.
Alternative Method for RDS MySQL
If you are using RDS MySQL, you might not be able to use the KILL command directly. Instead, you might need to call a specific command or use the RDS console to terminate sessions. The exact method can vary, so consult the RDS documentation or support resources for guidance.
Conclusion
Resolving the MySQL Error 1095 involves understanding your privileges and the ownership of the thread in question. By following the steps outlined above, you can diagnose and resolve this error effectively, ensuring that your MySQL server continues to operate smoothly and securely.