Mssql error when trying to create a Quality Gates condition

  • Versions used * Developer Edition Version 7.6 (build 21501)
    When trying to add a condition in Quality Gates , I selected any option like new issues
    It gaves me as option Operator is greater than then is the Error field.
    No matter what I put in the field (1, 0, null, a date) … when click “add condition” it shows in the screen"an error has ocurred. Contact the administrator"

Looks like is a data type issue in the quality_gate_conditions table

any clue how to fix it? or what it is about?

Log:
2019.12.17 11:00:45 TRACE web[AW6O…O][sql] time=1ms | sql=select id, qgate_id as qualityGateId, metric_id as metricId, operator, value_error as errorThreshold, created_at as createdAt, updated_at as updatedAt from quality_gate_conditions where qgate_id=? order by id asc | params=31

2019.12.17 11:00:45 TRACE web[AW6O…hO][sql] time=2ms | sql=insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, created_at, updated_at) values (?, ?, ?, ?, ?, ?) | params=31, 97, GT, 0, 2019-12-17 11:00:45.909, 0

2019.12.17 11:00:45 ERROR web[AW6O…][o.s.s.w.WebServiceEngine] Fail to process request http://localhost:9000/api/qualitygates/create_condition

org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

The error may involve org.sonar.db.qualitygate.QualityGateConditionMapper.insert-Inline

The error occurred while setting parameters

SQL: insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, created_at, updated_at) values (?, ?, ?, ?, ?, ?)

Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

Hello @ivette07mar,

You should update to the stable LTS 7.9.2 and see how it goes from here.

Antoine

Hi Antoine,
are you aware of other possible options to fix this mssql issue rather than updating the LTS and upgrading to 7.9?
at this moment we can’t upgrade.

Our DBA will be able to perform the fix if there is a solution from the vendor side in the case it is an issue with a ms sql table/scheme issue.
We are paying a full license of Developper Edition, we would love someone with experience who can help on this matter and tell us how to proceed to fix this issue.
Thanks,
Ivette

We were able to update to LTS 7.9.2 and we still had the error, as well we upgraded to Enterprise Edition 8.2 and error is still there, any idea how we can resolve this issue to be able to create again conditions in new and existing quality gates ?
We can create quality gates but we cannot add any condition in there.

Log:

Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

The error may exist in org.sonar.db.qualitygate.QualityGateConditionMapper

The error may involve org.sonar.db.qualitygate.QualityGateConditionMapper.insert-Inline

The error occurred while setting parameters

SQL: insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, created_at, updated_at) values (?, ?, ?, ?, ?, ?)

Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

Hello,

We need to know a little more about the context of this issue.
Can you run below query and share output as a text file:

select * from quality_gate_conditions;

Additionally, you could also set the SonarQube instance logging to TRACE and look at precise values passed to the INSERT statement that is triggering the error on the db side.
What are those values?

Greetings,
Kris

Hello Kris,
here is the query result

id qgate_id metric_id period operator value_error value_warning created_at updated_at
1 1 113 1 GT 0 NULL 2016-09-23 11:03:30 NULL
2 1 111 1 GT 0 NULL 2016-09-23 11:03:30 NULL
3 1 119 1 GT 5 NULL 2016-09-23 11:03:30 NULL
4 1 36 1 LT 80 NULL 2016-09-23 11:03:30 NULL
5 2 111 1 GT 0 2016-10-11 15:49:19 NULL
6 2 113 1 GT 0 2016-10-11 15:49:33 NULL
7 2 119 1 GT 5 2016-10-11 15:49:48 NULL
8 4 111 1 GT 0 2017-04-06 10:16:28 NULL
9 4 113 1 GT 0 2017-04-06 10:16:47 NULL
11 4 15 NULL LT 30 2017-04-06 10:18:20 2017-04-06 10:18:33
12 6 113 1 GT 0 NULL 2018-05-11 23:27:29 NULL
13 6 111 1 GT 0 NULL 2018-05-11 23:27:29 NULL
14 6 119 1 GT 5 NULL 2018-05-11 23:27:29 NULL
16 7 175 1 GT 1 NULL 2018-07-06 14:34:29 NULL
17 7 174 1 GT 1 NULL 2018-07-06 14:34:29 NULL
18 7 173 1 GT 1 NULL 2018-07-06 14:34:29 NULL
19 7 36 1 LT 80 NULL 2018-07-06 14:34:29 NULL
20 7 172 1 GT 3 NULL 2018-07-06 14:34:29 NULL
21 8 89 NULL GT 10 NULL 2018-07-12 10:24:13 2019-05-01 02:46:35
22 8 111 1 GT 10 NULL 2018-07-12 10:25:16 2019-05-01 02:46:35
23 8 173 1 GT 1 NULL 2018-07-12 10:26:08 NULL
24 8 175 1 GT 1 NULL 2018-07-12 10:26:42 NULL
25 8 119 1 GT 4 NULL 2018-07-12 10:28:17 2019-05-01 02:46:35
31 11 36 1 LT 10 NULL 2018-10-23 14:33:09 2019-05-01 02:46:35
34 11 172 1 GT 10 NULL 2018-10-23 14:37:44 2019-05-01 02:46:35

Here is the log using trace for the logs
**FYI, I removed the force user authentication option under ADMINISTRATION\CONFIGURATION\SECURITY, restarted instance and Im getting this log

2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][o.s.s.p.w.UserSessionFilter] Thread[http-nio-0.0.0.0-9000-exec-2,5,main] serves /api/qualitygates/create_condition
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=0ms | sql=SELECT u.id as id, u.uuid as uuid, u.login as login, u.name as name, u.email as email, u.active as “active”, u.scm_accounts as “scmAccounts”, u.salt as “salt”, u.crypted_password as “cryptedPassword”, u.hash_method as “hashMethod”, u.external_id as “externalId”, u.external_login as “externalLogin”, u.external_identity_provider as “externalIdentityProvider”, u.user_local as “local”, u.is_root as “root”, u.onboarded as “onboarded”, u.homepage_type as “homepageType”, u.homepage_parameter as “homepageParameter”, u.last_connection_date as “lastConnectionDate”, u.created_at as “createdAt”, u.updated_at as “updatedAt” FROM users u WHERE u.uuid=? | params=AXMlx-6abtl7d9fzYlgU
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=select kee as “key”, is_empty as empty, text_value as value, created_at as createdAt from internal_properties where kee in ( ? ) | params=organization.default
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=select org.uuid as “uuid”, org.kee as “key”, org.name as “name”, org.description as “description”, org.default_quality_gate_uuid as “defaultQualityGateUuid”, org.url as “url”, org.avatar_url as “avatarUrl”, org.subscription as “subscription”, org.created_at as “createdAt”, org.updated_at as “updatedAt” from organizations org where org.uuid = ? | params=AV_Lkz_L4Pxexu9WYH3W
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=select org.uuid as “uuid”, org.kee as “key”, org.name as “name”, org.description as “description”, org.default_quality_gate_uuid as “defaultQualityGateUuid”, org.url as “url”, org.avatar_url as “avatarUrl”, org.subscription as “subscription”, org.created_at as “createdAt”, org.updated_at as “updatedAt” from organizations org where org.kee = ? | params=default-organization
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=SELECT qg.id as id, qg.uuid as uuid, qg.name as name, qg.is_built_in as isBuiltIn, oqg.organization_uuid as organizationUuid, qg.created_at as createdAt, qg.updated_at as updatedAd FROM quality_gates qg INNER JOIN org_quality_gates oqg ON oqg.quality_gate_uuid = qg.uuid AND oqg.organization_uuid = ? WHERE qg.id = ? | params=AV_Lkz_L4Pxexu9WYH3W, 19
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=select gr.role from group_roles gr inner join groups_users gu on gr.group_id=gu.group_id where gr.organization_uuid=? and gr.resource_id is null and gu.user_id=? union select gr.role from group_roles gr where gr.organization_uuid=? and gr.group_id is null and gr.resource_id is null union select ur.role from user_roles ur where ur.organization_uuid=? and ur.user_id=? and ur.resource_id is null | params=AV_Lkz_L4Pxexu9WYH3W, 4492, AV_Lkz_L4Pxexu9WYH3W, AV_Lkz_L4Pxexu9WYH3W, 4492
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=SELECT m.id, m.name as kee, m.description, m.direction, m.domain, m.short_name as shortName, m.qualitative, m.val_type as valueType, m.user_managed as userManaged, m.enabled, m.worst_value as worstValue, m.best_value as bestValue, m.optimized_best_value as optimizedBestValue, m.hidden, m.delete_historical_data as deleteHistoricalData, m.decimal_scale as decimalScale FROM metrics m WHERE m.name=? | params=new_bugs
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=1ms | sql=select id, qgate_id as qualityGateId, metric_id as metricId, operator, value_error as errorThreshold, created_at as createdAt, updated_at as updatedAt from quality_gate_conditions where qgate_id=? order by created_at asc | params=19
2020.07.06 17:52:24 TRACE web[AXMmF1XMjruwbwjQAABs][sql] time=3ms | sql=insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, created_at, updated_at) values (?, ?, ?, ?, ?, ?) | params=19, 111, GT, 1, 2020-07-06 17:52:24.656, 0
2020.07.06 17:52:24 ERROR web[AXMmF1XMjruwbwjQAABs][o.s.s.w.WebServiceEngine] Fail to process request http://localhost:9000/api/qualitygates/create_condition
org.apache.ibatis.exceptions.PersistenceException:

Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

The error may exist in org.sonar.db.qualitygate.QualityGateConditionMapper

The error may involve org.sonar.db.qualitygate.QualityGateConditionMapper.insert-Inline

The error occurred while setting parameters

SQL: insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, created_at, updated_at) values (?, ?, ?, ?, ?, ?)

Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93)
at com.sun.proxy.$Proxy34.insert(Unknown Source)
at org.sonar.db.qualitygate.QualityGateConditionDao.insert(QualityGateConditionDao.java:30)
at org.sonar.server.qualitygate.QualityGateConditionsUpdater.createCondition(QualityGateConditionsUpdater.java:98)
at org.sonar.server.qualitygate.ws.CreateConditionAction.handle(CreateConditionAction.java:89)
at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:110)
at org.sonar.server.platform.web.WebServiceFilter.doFilter(WebServiceFilter.java:88)
at org.sonar.server.platform.web.MasterServletFilter$GodFilterChain.doFilter(MasterServletFilter.java:139)
at org.sonar.server.platform.web.MasterServletFilter.doFilter(MasterServletFilter.java:108)
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.UserSessionFilter.doFilter(UserSessionFilter.java:88)
at org.sonar.server.platform.web.UserSessionFilter.doFilter(UserSessionFilter.java:72)
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.RequestIdFilter.doFilter(RequestIdFilter.java:66)
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:137)
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:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:798)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:808)
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: Implicit conversion from data type varbinary to datetime2 is not allowed. Use the CONVERT function to run this query.
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 jdk.internal.reflect.GeneratedMethodAccessor197.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.sonar.db.profiling.InvocationUtils.invokeQuietly(InvocationUtils.java:34)
at org.sonar.db.profiling.ProfilingPreparedStatementHandler.invoke(ProfilingPreparedStatementHandler.java:45)
at com.sun.proxy.$Proxy76.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:52)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
… 53 common frames omitted
2020.07.06 17:52:30 TRACE web[AXMmF1XMjruwbwjQAABt][o.s.s.p.w.UserSessionFilter] Thread[http-nio-0.0.0.0-9000-exec-3,5,main] serves /
2020.07.06 17:52:30 TRACE web[AXMmF1XMjruwbwjQAABt][sql] time=0ms | sql=select p.prop_key as “key”, p.is_empty as empty, p.text_value as textValue, p.clob_value as clobValue, p.resource_id as resourceId, p.user_id as userId from properties p where p.prop_key=? and p.resource_id is null and p.user_id is null | params=sonar.forceAuthentication
2020.07.06 17:52:31 TRACE web[sql] time=0ms | sql=select top (?) id, data from notifications order by id asc | params=1

Thanks for sharing.

Below part is interesing:

Basing on above I was able to execute, on MS SQL 2017, below SQL insert query, taking the params passed and transforming to below form:
insert into quality_gate_conditions (qgate_id, metric_id, operator, value_error, created_at, updated_at) values (19, 111, 'GT', 1, '2020-07-06 17:52:24.656', 0);

Can you use above query and try to execute it in Mgmt Studio - does it succeed?
What is the version of MS SQL that you are using?

Best regards,
Kris

Hi Kris, thanks for the following up. It didn’t work in our end.
The results for the insert is :

Msg 206, Level 16, State 2, Line 2
Operand type clash: int is incompatible with datetime2

The SQL Version is : Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64) Jun 15 2019 00:45:05 Copyright © 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

DBA told me this:I am pretty sure you can not insert a ZERO value into the last field seeing as it is a DATE field. How about inserting a NULL or an actual date?

we wait for your suggestion, since the issue seems a data type conversion.Thanks

Hello,

Thanks for letting me know.

I test also on 2017 on my side and the record created (via the previously shared insert SQL query) is created with default value of date (1900-01-01 00:00:00.000) (as mentioned at https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15).

While testing the same on SonarQube 7.9.3 and MS SQL 2017 the error does not seem to exist anymore and record gets created with NULL value:

1st record above is created manually with the insert SQL previously shared and the 2nd is created with SonarQube UI.

Since 7.6 is not supported anymore I would advise to upgrade to current LTS or latest version available (8.4).

Hope that helps.
Best regards,
Kris

hI Kris , in my 3rd post I did mention the versions we tried and the last one being used.
“We were able to update to LTS 7.9.2 and we still had the error, as well we upgraded to Enterprise Edition 8.2…”
I don’t think this bug is related of the version used, we just want more help if you have seen this case before.
Let us know if you have more options for us, in the mean time I will ask the dba to try to create the record using NULL or as a date.
Thanks

Hello,

in my 3rd post I did mention the versions we tried and the last one being used.

Right, sorry for overlooking it.

I don’t think this bug is related of the version used

That is correct - I rather tend to think this may be an implementation detail on the db side of yours.
I do not think we have seen similar case before and I have just tested this on MS SQL 2017 and 7.9.x LTS. That would have come to the surface much earlier should that be a general bug.

I think the best way forward with this would be to look for MS SQL help on this topic - you have an exact query that does not work on your side and works on mine on the same db engine version.
Does that make sense after all?

Best regards,
Kris