Charlie Meyer created NIFI-5819:
-----------------------------------
Summary: JDBCCommon does not handle converting SQLServer
sql_variant type to avro
Key: NIFI-5819
URL: https://issues.apache.org/jira/browse/NIFI-5819
Project: Apache NiFi
Issue Type: Bug
Components: Core Framework
Affects Versions: 1.8.0
Reporter: Charlie Meyer
If i run a query using executesqlrecord against a mssql database such as
{{SELECT SERVERPROPERTY ('ProductVersion') AS MajorVersion}} nifi logs the
following stack trace:
{code:java}
{ "cause": { "cause": null, "stackTrace": [ { "methodName": "createSchema",
"fileName": "JdbcCommon.java", "lineNumber": 677, "className":
"org.apache.nifi.processors.standard.util.JdbcCommon", "nativeMethod": false },
{ "methodName": "writeResultSet", "fileName": "RecordSqlWriter.java",
"lineNumber": 68, "className":
"org.apache.nifi.processors.standard.sql.RecordSqlWriter", "nativeMethod":
false }, { "methodName": "lambda$onTrigger$1", "fileName":
"ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 362, "className":
"com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
"nativeMethod": false }, { "methodName": "write", "fileName":
"StandardProcessSession.java", "lineNumber": 2648, "className":
"org.apache.nifi.controller.repository.StandardProcessSession", "nativeMethod":
false }, { "methodName": "onTrigger", "fileName":
"ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 360, "className":
"com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
"nativeMethod": false }, { "methodName": "onTrigger", "fileName":
"AbstractProcessor.java", "lineNumber": 27, "className":
"org.apache.nifi.processor.AbstractProcessor", "nativeMethod": false }, {
"methodName": "onTrigger", "fileName": "StandardProcessorNode.java",
"lineNumber": 1165, "className":
"org.apache.nifi.controller.StandardProcessorNode", "nativeMethod": false }, {
"methodName": "invoke", "fileName": "ConnectableTask.java", "lineNumber": 203,
"className": "org.apache.nifi.controller.tasks.ConnectableTask",
"nativeMethod": false }, { "methodName": "run", "fileName":
"TimerDrivenSchedulingAgent.java", "lineNumber": 117, "className":
"org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1",
"nativeMethod": false }, { "methodName": "call", "fileName": "Executors.java",
"lineNumber": 511, "className":
"java.util.concurrent.Executors$RunnableAdapter", "nativeMethod": false }, {
"methodName": "runAndReset", "fileName": "FutureTask.java", "lineNumber": 308,
"className": "java.util.concurrent.FutureTask", "nativeMethod": false }, {
"methodName": "access$301", "fileName": "ScheduledThreadPoolExecutor.java",
"lineNumber": 180, "className":
"java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
"nativeMethod": false }, { "methodName": "run", "fileName":
"ScheduledThreadPoolExecutor.java", "lineNumber": 294, "className":
"java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
"nativeMethod": false }, { "methodName": "runWorker", "fileName":
"ThreadPoolExecutor.java", "lineNumber": 1149, "className":
"java.util.concurrent.ThreadPoolExecutor", "nativeMethod": false }, {
"methodName": "run", "fileName": "ThreadPoolExecutor.java", "lineNumber": 624,
"className": "java.util.concurrent.ThreadPoolExecutor$Worker", "nativeMethod":
false }, { "methodName": "run", "fileName": "Thread.java", "lineNumber": 748,
"className": "java.lang.Thread", "nativeMethod": false } ], "message":
"createSchema: Unknown SQL type -156 / sql_variant (table:
NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be converted to Avro
type", "localizedMessage": "createSchema: Unknown SQL type -156 / sql_variant
(table: NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be converted to
Avro type", "suppressed": [] }, "stackTrace": [ { "methodName":
"lambda$onTrigger$1", "fileName":
"ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 364, "className":
"com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
"nativeMethod": false }, { "methodName": "write", "fileName":
"StandardProcessSession.java", "lineNumber": 2648, "className":
"org.apache.nifi.controller.repository.StandardProcessSession", "nativeMethod":
false }, { "methodName": "onTrigger", "fileName":
"ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 360, "className":
"com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
"nativeMethod": false }, { "methodName": "onTrigger", "fileName":
"AbstractProcessor.java", "lineNumber": 27, "className":
"org.apache.nifi.processor.AbstractProcessor", "nativeMethod": false }, {
"methodName": "onTrigger", "fileName": "StandardProcessorNode.java",
"lineNumber": 1165, "className":
"org.apache.nifi.controller.StandardProcessorNode", "nativeMethod": false }, {
"methodName": "invoke", "fileName": "ConnectableTask.java", "lineNumber": 203,
"className": "org.apache.nifi.controller.tasks.ConnectableTask",
"nativeMethod": false }, { "methodName": "run", "fileName":
"TimerDrivenSchedulingAgent.java", "lineNumber": 117, "className":
"org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1",
"nativeMethod": false }, { "methodName": "call", "fileName": "Executors.java",
"lineNumber": 511, "className":
"java.util.concurrent.Executors$RunnableAdapter", "nativeMethod": false }, {
"methodName": "runAndReset", "fileName": "FutureTask.java", "lineNumber": 308,
"className": "java.util.concurrent.FutureTask", "nativeMethod": false }, {
"methodName": "access$301", "fileName": "ScheduledThreadPoolExecutor.java",
"lineNumber": 180, "className":
"java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
"nativeMethod": false }, { "methodName": "run", "fileName":
"ScheduledThreadPoolExecutor.java", "lineNumber": 294, "className":
"java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
"nativeMethod": false }, { "methodName": "runWorker", "fileName":
"ThreadPoolExecutor.java", "lineNumber": 1149, "className":
"java.util.concurrent.ThreadPoolExecutor", "nativeMethod": false }, {
"methodName": "run", "fileName": "ThreadPoolExecutor.java", "lineNumber": 624,
"className": "java.util.concurrent.ThreadPoolExecutor$Worker", "nativeMethod":
false }, { "methodName": "run", "fileName": "Thread.java", "lineNumber": 748,
"className": "java.lang.Thread", "nativeMethod": false } ], "message":
"java.lang.IllegalArgumentException: createSchema: Unknown SQL type -156 /
sql_variant (table: NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be
converted to Avro type", "localizedMessage":
"java.lang.IllegalArgumentException: createSchema: Unknown SQL type -156 /
sql_variant (table: NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be
converted to Avro type", "suppressed": [] }
{code}
The workaround is to modify the query to be like:
{{SELECT CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) AS
MajorVersion}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
