7.9 LTS Quality Gate search fails due to SQL query syntax error

Hi,

I recently upgraded our SonarQube instance from 7.8 EE to 7.9 LTS EE. We are using Microsoft SQL Server 2017 for our database. Since upgrading, I’ve been encountering issues with the Quality Gate search. When I click on one of the Quality Gates in the UI, I get a red error popup that says, An error has occurred. Please contact your administrator

I get the same error when I call the GET /api/qualitygates/search API directly. I looked in the web.log file and found the following exception:

2019.07.02 15:09:21 ERROR web[AWu0BYNlYW519IWpAAqr][o.s.s.w.WebServiceEngine] Fail to process request http://<sonar_instance>/api/qualitygates/search?gateId=7&page=1&pageSize=100&selected=all
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'key'.
### The error may exist in org.sonar.db.qualitygate.ProjectQgateAssociationMapper
### The error may involve org.sonar.db.qualitygate.ProjectQgateAssociationMapper.selectProjects-Inline
### The error occurred while setting parameters
### SQL: SELECT proj.id as id, proj.kee as key, proj.name as name, prop.text_value as gateId     FROM projects proj     LEFT JOIN properties prop ON prop.resource_id=proj.id AND prop.prop_key='sonar.qualitygate' AND prop.text_value = ?     where     proj.qualifier = 'TRK'     and proj.enabled = 1     and proj.main_branch_project_uuid is null     and proj.copy_component_uuid is null     and proj.organization_uuid=?                 order by proj.name
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'key'.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
	at com.sun.proxy.$Proxy58.selectProjects(Unknown Source)
	at org.sonar.db.qualitygate.ProjectQgateAssociationDao.selectProjects(ProjectQgateAssociationDao.java:30)
	at org.sonar.server.qualitygate.ws.SearchAction.handle(SearchAction.java:113)
	at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:110)
	at org.sonar.server.ws.WebServiceFilter.doFilter(WebServiceFilter.java:88)
	at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:126)
	at org.sonar.server.platform.web.MasterServletFilter.doFilter(MasterServletFilter.java:95)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.sonar.server.user.UserSessionFilter.doFilter(UserSessionFilter.java:87)
	at org.sonar.server.user.UserSessionFilter.doFilter(UserSessionFilter.java:71)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.sonar.server.platform.web.CacheControlFilter.doFilter(CacheControlFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.sonar.server.platform.web.SecurityServletFilter.doHttpFilter(SecurityServletFilter.java:76)
	at org.sonar.server.platform.web.SecurityServletFilter.doFilter(SecurityServletFilter.java:48)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.sonar.server.platform.web.RedirectFilter.doFilter(RedirectFilter.java:58)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.sonar.server.platform.web.requestid.RequestIdFilter.doFilter(RequestIdFilter.java:63)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.sonar.server.platform.web.RootFilter.doFilter(RootFilter.java:62)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at ch.qos.logback.access.tomcat.LogbackValve.invoke(LogbackValve.java:256)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'key'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2935)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:503)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:60)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 53 common frames omitted

It looks like a problem with the ProjectQgateAssociationMapper

I couldn’t get the query to work as is when I executed it manually against the database. I was able to get it working by putting ‘key’ in single quotes:

SELECT proj.id as id, proj.kee as 'key', proj.name as name, prop.text_value as gateId ...

or by changing ‘key’ to a different word:

SELECT proj.id as id, proj.kee as foo, proj.name as name, prop.text_value as gateId ...

Has this bug been reported and is it possible for a fix to be implemented in a bug release?

Thanks!
Schyler

I see now that there was a ticket created for this issue: SONAR-12261

2 Likes