To put it bluntly, MySQL has always been in a very useful 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?
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!