Failed to create Trigger due to MySQL User permission _ MySQL

Source: Internet
Author: User
The failure to create a Trigger caused by MySQL User permissions is embarrassing. MySQL has been in the only-available and incomprehensible stage. I am not very familiar with some of the deeper management, so I have to strengthen it!

Recently, in a system test, when a MySQL database is used, a trigger needs to be created on a table, and the database is installed locally. However, no trigger can be created. the background error message is as follows:

Com. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: TRIGGER command denied to user 'root' @ 'Mortimer-PC' for table 't_user' at sun. reflect. nativeConstructorAccessorImpl. newInstance0 (Native Method) at sun. reflect. nativeConstructorAccessorImpl. newInstance (nativeconstructeffeccessorimpl. java: 39) at sun. reflect. delegatingConstructorAccessorImpl. newInstance (delegatingconstructextends cessorimpl. java: 27) at java. lang. reflect. constructor. newInstance (Constructor. java: 513) at com. mysql. jdbc. util. handleNewInstance (Util. java: 411) at com. mysql. jdbc. util. getInstance (Util. java: 386) at com. mysql. jdbc. SQLError. createSQLException (SQLError. java: 1052) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 4096) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 4028) at com. mysql. jdbc. mysqlIO. sendCommand (MysqlIO. java: 2490) at com. mysql. jdbc. mysqlIO. sqlQueryDirect (MysqlIO. java: 2651) at com.mysql.jdbc.ConnectionImpl.exe cSQL (ConnectionImpl. java: 2677) at com.mysql.jdbc.ConnectionImpl.exe cSQL (ConnectionImpl. java: 2627) at com.mysql.jdbc.StatementImpl.exe cute (StatementImpl. java: 841) at com.mysql.jdbc.StatementImpl.exe cute (StatementImpl. java: 681) at com.apusic.jdbc.adapter.StatementHandle.exe cute (Unknown Source) at com. apusic. esb. base. util. SQLUtil. createDBObjectIfNecessary (SQLUtil. java: 263) at com. apusic. esb. base. util. SQLUtil. createDBObjectIfNecessary (SQLUtil. java: 225) at com. apusic. esb. config. trigger. mysql. mySqlTriggerManager. createTriggersIfNecessary (MySqlTriggerManager. java: 83) at com. apusic. esb. config. trigger. triggerConfigManager. deploy (TriggerConfigManager. java: 331) at sun. reflect. nativeMethodAccessorImpl. invoke0 (Native Method) at sun. reflect. nativeMethodAccessorImpl. invoke (NativeMethodAccessorImpl. java: 39) at sun. reflect. delegatingMethodAccessorImpl. invoke (DelegatingMethodAccessorImpl. java: 25) at java. lang. reflect. method. invoke (Method. java: 597) at com. apusic. esb. base. communication. commInvokeUtil. invoke (CommInvokeUtil. java: 139) at com. apusic. esb. base. communication. commInvokeUtil. invoke (CommInvokeUtil. java: 43) at com. apusic. esb. base. communication. commInvokeUtil. invoke (CommInvokeUtil. java: 27) at com. apusic. esb. base. communication. socket. server. commSocketServer $ RequestProcessor. run (CommSocketServer. java: 99) at com. apusic. util. threadPoolImpl $ WorkerThread. run (Unknown Source) 10:38:42 error [apusic.com. apusic. esb. config. trigger. triggerConfigManager] com. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: TRIGGER command denied to user 'root' @ 'Mortimer-PC' for table 't_user' at sun. reflect. nativeConstructorAccessorImpl. newInstance0 (Native Method) at sun. reflect. nativeConstructorAccessorImpl. newInstance (nativeconstructeffeccessorimpl. java: 39) at sun. reflect. delegatingConstructorAccessorImpl. newInstance (delegatingconstructextends cessorimpl. java: 27) at java. lang. reflect. constructor. newInstance (Constructor. java: 513) at com. mysql. jdbc. util. handleNewInstance (Util. java: 411) at com. mysql. jdbc. util. getInstance (Util. java: 386) at com. mysql. jdbc. SQLError. createSQLException (SQLError. java: 1052) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 4096) at com. mysql. jdbc. mysqlIO. checkErrorPacket (MysqlIO. java: 4028) at com. mysql. jdbc. mysqlIO. sendCommand (MysqlIO. java: 2490) at com. mysql. jdbc. mysqlIO. sqlQueryDirect (MysqlIO. java: 2651) at com.mysql.jdbc.ConnectionImpl.exe cSQL (ConnectionImpl. java: 2677) at com.mysql.jdbc.ConnectionImpl.exe cSQL (ConnectionImpl. java: 2627) at com.mysql.jdbc.StatementImpl.exe cute (StatementImpl. java: 841) at com.mysql.jdbc.StatementImpl.exe cute (StatementImpl. java: 681) at com.apusic.jdbc.adapter.StatementHandle.exe cute (Unknown Source) at com. apusic. esb. base. util. SQLUtil. createDBObjectIfNecessary (SQLUtil. java: 263) at com. apusic. esb. base. util. SQLUtil. createDBObjectIfNecessary (SQLUtil. java: 225) at com. apusic. esb. config. trigger. mysql. mySqlTriggerManager. createTriggersIfNecessary (MySqlTriggerManager. java: 83) at com. apusic. esb. config. trigger. triggerConfigManager. deploy (TriggerConfigManager. java: 331) at sun. reflect. nativeMethodAccessorImpl. invoke0 (Native Method) at sun. reflect. nativeMethodAccessorImpl. invoke (NativeMethodAccessorImpl. java: 39) at sun. reflect. delegatingMethodAccessorImpl. invoke (DelegatingMethodAccessorImpl. java: 25) at java. lang. reflect. method. invoke (Method. java: 597) at com. apusic. esb. base. communication. commInvokeUtil. invoke (CommInvokeUtil. java: 139) at com. apusic. esb. base. communication. commInvokeUtil. invoke (CommInvokeUtil. java: 43) at com. apusic. esb. base. communication. commInvokeUtil. invoke (CommInvokeUtil. java: 27) at com. apusic. esb. base. communication. socket. server. commSocketServer $ RequestProcessor. run (CommSocketServer. java: 99) at com. apusic. util. threadPoolImpl $ WorkerThread. run (Unknown Source)
Analysis error message: TRIGGER command denied to user 'root' @ 'Mortimer-PC' for table 't_ user'. it seems that the root user is not allowed to execute the TRIGGER command on the t_user table, why not?
In the analysis, we first suspect whether the driver is correct because the driver does not match. The driver version is earlier (for example, 5.0), and the server version is 5.5, after the driver is replaced, the problem persists.

Second, the error message itself is analyzed and cannot be executed. it looks like a Permission Problem. Navicat connects to the database and views user permissions, the "Trigger" column of "root @ %" is not selected! The connection user seems to be "root @ mortimer-PC". Therefore, the "root @ localhost" rule does not seem to take effect. this is probably the reason why a trigger cannot be created.

Adjust the permissions of the "root @ %" user, select the "Trigger" column, restart the MySQL service, reconnect to MySQL, create a Trigger, and OK!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.