You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "GAbor Bilicz (Jira)" <ji...@apache.org> on 2020/06/16 08:37:00 UTC
[jira] [Commented] (NIFI-5819) JDBCCommon does not handle
converting SQLServer sql_variant type to avro
[ https://issues.apache.org/jira/browse/NIFI-5819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17136441#comment-17136441 ]
GAbor Bilicz commented on NIFI-5819:
------------------------------------
It's a Problem on NiFi avro level. There should be an option to convert/try to convert all unknow SQLTypes to string. I have the same Problem with QueryDatabaseTable too.
> 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
> Priority: Major
>
> 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
(v8.3.4#803005)