[
https://issues.apache.org/jira/browse/NIFI-3898?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16011572#comment-16011572
]
Koji Kawamura commented on NIFI-3898:
-------------------------------------
[~egortsaryk9]
I think you're using PostgreSQL because 'ON CONFLICT' is in the insert sql.
I've tested once again with PostgreSQL (I did it with MySQL previously). JDBC
driver is postgresql-42.1.1.jar.
{code}
postgres=# SELECT version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
4.9.2, 64-bit
(1 row)
# Created a table consists of UUID and JSON column, should be similar to your
example:
create table my_table (
id uuid not null,
data json,
primary key (id)
);
# Successful insert statement
INSERT INTO my_table(id, data) VALUES('220f27c5-ce2f-4ab4-8bdd-fc9187d36783',
'{"my_jsonb_data_field":"some data"}') ON CONFLICT DO NOTHING;
# Incorrect insert statement (having unnecessary quote)
INSERT INTO postgres.my_table(id, data)
VALUES('220f27c5-ce2f-4ab4-8bdd-fc9187d36783', ' '{"my_jsonb_data_field":"some
data"}') ON CONFLICT DO NOTHING;
{code}
I confirmed successful insert statement is routed to 'success'. And the
incorrect insert statement is routed to 'retry'. Ideally it should be 'failure'
but since the PostgreSQL driver throws PSQLException which is not a subclass of
java.sql.SQLTransientException, we can't determine whether this error is
retry-able or not. So, I think routing it to 'retry' is correct in this case.
Following is the stack-trace I got. It was PSQLException, not
ArrayIndexOutOfBoundsException. So it's clear that I wasn't able to reproduce
your problem. Please share your stack-trace. Probably Postgres Driver version
difference?
{code}
2017-05-16 00:52:55,454 ERROR [Timer-Driven Process Thread-10]
o.apache.nifi.processors.standard.PutSQL PutSQL[id=0eb373
93-015c-1000-c963-e324b47a2662] Failed to update database for [] due to
org.postgresql.util.PSQLException: Unterminated
string literal started at position 115 in SQL INSERT INTO my_table(id, data)
VALUES('220f27c5-ce2f-4ab4-8bdd-fc9187d3678
3',' '{"my_jsonb_data_field":"some data"}') ON CONFLICT DO NOTHING;. Expected
char; it is possible that retrying the op
eration will succeed, so routing to retry: org.postgresql.util.PSQLException:
Unterminated string literal started at pos
ition 115 in SQL INSERT INTO my_table(id, data)
VALUES('220f27c5-ce2f-4ab4-8bdd-fc9187d36783',' '{"my_jsonb_data_field":
"some data"}') ON CONFLICT DO NOTHING;. Expected char
org.postgresql.util.PSQLException: Unterminated string literal started at
position 115 in SQL INSERT INTO my_table(id, d
ata) VALUES('220f27c5-ce2f-4ab4-8bdd-fc9187d36783','
'{"my_jsonb_data_field":"some data"}') ON CONFLICT DO NOTHING;. Exp
ected char
at org.postgresql.core.Parser.checkParsePosition(Parser.java:1134)
at org.postgresql.core.Parser.parseSql(Parser.java:1031)
at org.postgresql.core.Parser.replaceProcessing(Parser.java:975)
{code}
Since this is not always reproducible, I lowered the priority from Critical to
Major.
> PutSql - flow files get stuck in incoming queue if there are incorrect INSERT
> statements in flow files
> ------------------------------------------------------------------------------------------------------
>
> Key: NIFI-3898
> URL: https://issues.apache.org/jira/browse/NIFI-3898
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 1.2.0
> Reporter: yahor tsaryk
> Labels: putsql
> Attachments: Screen Shot 2017-05-15 at 21.25.46.png, Screen Shot
> 2017-05-15 at 21.25.54.png
>
>
> Hi everybody, I just updated to 1.2.0 version and I found that if incoming
> flow file for PutSql processor contains incorrect sql INSERT statement (such
> as
> INSERT INTO public.my_table(id, data)
> VALUES('220f27c5-ce2f-4ab4-8bdd-fc9187d36783', '
> '{"my_jsonb_data_field":"some data"}') ON CONFLICT DO NOTHING; - the
> statement contains redundant apostrophe character) the flow files just get
> stuck in the incoming queue, they haven't being directed to "Failure" queue.
> I don't use "Rollback On Failure" feature - it is set to false. "Support
> Fragmented Transactions" option is also set to false.
> Also I tried to set "Batch Size" value to 1 but result is the same as with
> default batch size value (100). (It fails with
> "java.lang.ArayIndexOutOfBoundsException")
> Shouldn't incoming flow files with incorrect/broken sql statements be
> directed to the "Failure" relationship automatically ? Current behavior
> reminds like "Rollback On Failure" feature is set to true, but I just want to
> filter out incorrect sql insert statements.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
