DROP TRIGGER 原先触发器名字;-- 先删除原先的触发器(事先拷贝出来原先的触发器SQL)
CREATE DEFINER=`这里是权限比较大的账号,通常指定为 root@localhost` TRIGGER `原先触发器名字` BEFORE INSERT ON 创建到那张表
FOR EACH ROW ----这里写触发器要执行的SQL----;
以下是报错信息
Error Msg : ### Error updating database. Cause: java.sql.SQLException: The user specified as a definer ('ljz'@'61.148.227.170') does not exist ### The error may involve com.yitianyike.dao.IPublishPlan.insertUseGeneratedKeys-Inline ### The error occurred while setting parameters ### SQL: INSERT INTO PublishPlan ( hash_id,hash,down_start,down_end,concentration_value,play_start,play_end,play_mode,smart_factor,plan_style,status,update_time,create_time,channel_code,vender,name,resource,auto_release,plan_type,template_id,audit,commit,creater,desc_info,create_user ) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ### Cause: java.sql.SQLException: The user specified as a definer ('ljz'@'61.148.227.170') does not exist ; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1449]; The user specified as a definer ('ljz'@'61.148.227.170') does not exist; nested exception is java.sql.SQLException: The user specified as a definer ('ljz'@'61.148.227.170') does not exist
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421) at com.sun.proxy.$Proxy78.insert(null:-1) at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:254) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:52) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) at com.sun.proxy.$Proxy97.insertUseGeneratedKeys(null:-1) at com.yitianyike.service.impl.PublishPlanServiceImpl.add(PublishPlanServiceImpl.java:320) at com.yitianyike.controller.PublishPlanController.PublishPlanAdd(PublishPlanController.java:74) at com.yitianyike.controller.PublishPlanController$$FastClassBySpringCGLIB$$532aac87.invoke(:-1) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85) at com.yitianyike.aop.LogAop.inOut(LogAop.java:138) at sun.reflect.GeneratedMethodAccessor258.invoke(null:-1) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:620) at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:609) at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) at com.yitianyike.controller.PublishPlanController$$EnhancerBySpringCGLIB$$79f73e1f.PublishPlanAdd(:-1) at sun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethodAccessorImpl.java:-2) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:817) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:731) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:968) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:870) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:844) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.springframework.boot.context.web.ErrorPageFilter.doFilter(ErrorPageFilter.java:120) at org.springframework.boot.context.web.ErrorPageFilter.access$000(ErrorPageFilter.java:61) at org.springframework.boot.context.web.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:95) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.boot.context.web.ErrorPageFilter.doFilter(ErrorPageFilter.java:113) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1533) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1489) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745)
mysql 触发器采坑记录(mysql 触发器采坑记录 由于不了解mysql创建触发器要记录创建者IP,导致更改创建者IP后,触发器不可执行。 当创建触发器(视图、存储过程、函数)时,如果没有)
mysql 触发器采坑记录(语序有点凌乱,回头再整理)由于不了解mysql创建触发器要记录创建者IP,导致更改创建者IP后,触发器不可执行。当创建触发器(视图、存储过程、函数)时,如果没有指定创建者,则mysql会自动添加用户+IP到information_scheme中的TRIGGERS(等等)DEFINER字段中,当程序执行的时候触发器的时候,mysql 会检查