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)