执行这样的sql
alter table pj_usr_auth add constraint FK_Reference_8 foreign key (usr) references usr (usr) on delete restrict on update restrict;
错误
2016-03-29 21:04:42,423 [ERROR][com.alibaba.druid.filter.stat.StatFilter] - merge sql error, dbType mysql, sql : alter table pj_usr_auth add constraint FK_Reference_8 foreign key (usr) references usr (usr) on delete restrict on update restrict; com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'rict on update restrict;',expect ON, actual ON on at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:231) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:407) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:145) at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:140) at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:53) at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145) at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:630) at com.alibaba.druid.filter.stat.StatFilter.internalBeforeStatementExecute(StatFilter.java:397) at com.alibaba.druid.filter.stat.StatFilter.statementExecuteBefore(StatFilter.java:345) at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:185) at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487) at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:137) at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:421) at org.nutz.dao.impl.sql.run.NutDaoExecutor._runStatement(NutDaoExecutor.java:321) at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:52) at org.nutz.plugins.cache.dao.CachedNutDaoExecutor.exec(CachedNutDaoExecutor.java:105) at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:316) at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:59) at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:239) at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:271) at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:235) at com.rekoe.MvcSetup.init(MvcSetup.java:79) at org.nutz.mvc.impl.NutLoading.evalSetup(NutLoading.java:276) at org.nutz.mvc.impl.NutLoading.load(NutLoading.java:120) at org.nutz.mvc.ActionHandler.(ActionHandler.java:19) at org.nutz.mvc.NutFilter._init(NutFilter.java:87) at org.nutz.mvc.NutFilter.init(NutFilter.java:65) at com.rekoe.mvc.RkCmsNutFilter.init(RkCmsNutFilter.java:22) at org.apache.catalina.core.ApplicationFilterConfig.initFilter(ApplicationFilterConfig.java:277) at org.apache.catalina.core.ApplicationFilterConfig.getFilter(ApplicationFilterConfig.java:258) at org.apache.catalina.core.ApplicationFilterConfig.setFilterDef(ApplicationFilterConfig.java:382) at org.apache.catalina.core.ApplicationFilterConfig.(ApplicationFilterConfig.java:103) at org.apache.catalina.core.StandardContext.filterStart(StandardContext.java:4650) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5306) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744) 2016-03-29 21:04:42,669 [ERROR][com.alibaba.druid.pool.DruidDataSource] - discard connection
原提问者GitHub用户Rekoe
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。
根据错误日志显示,Druid SQL解析器在解析这个SQL语句时遇到了语法错误。具体来说,错误发生在on delete restrict on update restrict这段语句中,Druid SQL解析器期望的是ON关键字,但实际上出现了两次ON关键字,导致解析器无法正确解析该语句。
该SQL语句中的ON DELETE RESTRICT和ON UPDATE RESTRICT是定义外键约束时常用的语句,用于指定在删除或更新主表记录时如何处理与之相关联的从表记录。正确的语法应该是:
pgsql
Copy
ALTER TABLE pj_usr_auth ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (usr) REFERENCES usr (usr) ON DELETE RESTRICT ON UPDATE RESTRICT;
需要注意的是,在MySQL中,外键约束的默认行为是RESTRICT,因此可以省略ON DELETE RESTRICT ON UPDATE RESTRICT这段语句,得到如下简化的语句:
Copy
ALTER TABLE pj_usr_auth ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (usr) REFERENCES usr (usr);
如果仍然遇到类似的语法错误,可以尝试使用MySQL命令行工具或其他SQL客户端工具执行这个SQL语句,以验证该SQL语句是否正确。同时,也可以检查Druid SQL解析器的版本和配置,以确定是否存在配置或版本问题。
问题已修复,请用新版本 https://github.com/alibaba/druid/releases/tag/1.1.14
原回答者GitHub用户wenshao
根据错误日志,您的SQL语句在Druid连接池中执行时出现了语法错误。具体错误信息是:com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'rict on update restrict;',expect ON, actual ON
。根据错误信息来看,Druid解析器在解析SQL语句时发现了语法错误,并且提示了错误的位置。
根据您提供的SQL语句来看,您使用的是MySQL数据库,而MySQL的外键约束语法是不包含ON DELETE RESTRICT
和ON UPDATE RESTRICT
这样的语句的。正确的MySQL外键约束语法应该是类似下面的形式:
ALTER TABLE pj_usr_auth
ADD CONSTRAINT FK_Reference_8 FOREIGN KEY (usr) REFERENCES usr(usr);
您可以尝试使用上述语句来执行ALTER TABLE操作,应该就不会再出现语法错误了。