[
https://issues.apache.org/jira/browse/NIFI-4238?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16157603#comment-16157603
]
bruce lowther edited comment on NIFI-4238 at 9/7/17 8:54 PM:
-------------------------------------------------------------
Matt, thank you for responding. Here is the table creation I'm using for the
sqlite table:
{code:sql}
create table mamtx_watermark (
id integer primary key autoincrement,
dt datetime default (datetime('now')),
site text,
app text,
state text
);
{code}
I tried the SQL presented in the error logs against the sqlite3 database and it
works as expected:
{code:sql}
sqlite> SELECT id, datetime(dt) as 'dt', site, app, state FROM mamtx_watermark
WHERE id > 3;
4|2017-09-07 19:27:50|FOO|TST|INCREMENTAL
5|2017-09-07 19:25:43|BAR|TST|NEW
6|2017-09-07 19:25:45|BAZ|TST|NEW
{code}
I tried many variations of the select by changing the contents of the 'columns
to return' parameter.
for example, i event tried a select with constant data by setting Columns to
return to:
'id','dt','site','app','state'
and I get the same error.
{code:sql}
sqlite> select 'id','dt','site','app','state' from mamtx_watermark where id > 3;
id|dt|site|app|state
id|dt|site|app|state
id|dt|site|app|state
{code}
{code:none}
Unable to execute SQL select query SELECT 'id','dt','site','app','state' FROM
mamtx_watermark WHERE id > 6 due to
org.apache.nifi.processor.exception.ProcessEx
ception: Error during database query or conversion of records to Avro.: {}
org.apache.nifi.processor.exception.ProcessException: Error during database
query or conversion of records to Avro.
at
org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:305)
at
org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2529)
at
org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:299)
at
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120)
at
org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
at
org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
at
org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132)
at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 0
/ NULL (table: mamtx_watermark, column: _id_) cannot be converted to Avro type
at
org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:564)
at
org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:242)
at
org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:303)
... 13 common frames omitted
{code}
was (Author: osunderdog):
Matt, thank you for responding. Here is the table creation I'm using for the
sqlite table:
{code:sql}
create table mamtx_watermark (
id integer primary key autoincrement,
dt datetime default (datetime('now')),
site text,
app text,
state text
);
{code}
I tried the SQL presented in the error logs against the sqlite3 database and it
works as expected:
{code:sql}
sqlite> SELECT id, datetime(dt) as 'dt', site, app, state FROM mamtx_watermark
WHERE id > 3;
4|2017-09-07 19:27:50|FOO|TST|INCREMENTAL
5|2017-09-07 19:25:43|BAR|TST|NEW
6|2017-09-07 19:25:45|BAZ|TST|NEW
{code}
I tried many variations of the select by changing the contents of the 'columns
to return' parameter.
for example, i event tried a select with constant data by setting Columns to
return to:
'id','dt','site','app','state'
and I get the same error.
{code:sql}
sqlite> select 'id','dt','site','app','state' from mamtx_watermark where id > 3;
id|dt|site|app|state
id|dt|site|app|state
id|dt|site|app|state
{code}
{code:error}
Unable to execute SQL select query SELECT 'id','dt','site','app','state' FROM
mamtx_watermark WHERE id > 6 due to
org.apache.nifi.processor.exception.ProcessEx
ception: Error during database query or conversion of records to Avro.: {}
org.apache.nifi.processor.exception.ProcessException: Error during database
query or conversion of records to Avro.
at
org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:305)
at
org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2529)
at
org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:299)
at
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120)
at
org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
at
org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
at
org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132)
at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException: createSchema: Unknown SQL type 0
/ NULL (table: mamtx_watermark, column: _id_) cannot be converted to Avro type
at
org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:564)
at
org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:242)
at
org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:303)
... 13 common frames omitted
{code}
> Error in QueryDatabaseTable (NiFi CDC support): Unable to execute SQL select
> query due to org.apache.nifi.processor.exception.ProcessException: Error
> during database query or conversion of records to Avro
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: NIFI-4238
> URL: https://issues.apache.org/jira/browse/NIFI-4238
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 1.3.0
> Environment: Centos
> Reporter: Ella
> Attachments: config_file_1.png, config_file_2.png, config_file_3.png,
> diagram.png, Error.png, QueryDatabaseTableError.png
>
>
> Hi Guys,
> I should retrieve only the new added records from the DB2 database to a file
> by NiFi's CDC feature--QueryDatabaseTable processor; however, I have
> encountered the Error during executing my dataflow scenario. I have
> respectfully attached the snapshot of Error as well as the dataflow; I would
> really appreciate if someone helped me after all.
> Thanks a lot.
> Sincerely,
> Ella
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
