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!