You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Rick <rt...@imbuia-holdings.com> on 2018/08/13 16:26:17 UTC
Weird behavior with the latest rev of MySQL and NiFi 1.7.1
Hi Everybody!
I am seeing some really unusual and unexpected MySQL error messages
written into the NiFi log file by the latest MySQL version (5.7.23) when
I try to load simple records from a CSV file with a very simple
PutDatabaseRecord application.
So, before I expend a lot of effort having the drains up, I wondered if
anyone else has seen this sort of behavior with this NiFi / MySQL
version combo, or alternatively, if y'all have this combo working ok,
then I know that I need to look for something local on my system.
Stripping my NiFi flow down to the bare minimum necessary to demonstrate
the issue, it is: ListFile >> FetchFile >> PutDatabaseRecord, and it is
the latter that triggers these weird error messages when it attempts to
INSERT the records into the target SQL table. Shorn of the irrelevant
verbiage, the specific error that I am now getting in the NiFi log file
is "PutDatabaseRecord failed to process StandardFlowFIle Due to Unknown
Table </name of the table that I am trying to write to/> in
information_schema".
Behind the scenes are also a DBCPConnectionPool controller and a
CSVReader. These are enabled and running; the former is obviously
connecting to the DB as it can 'see' an information schema in the first
place (separately, I have checked and the DBConnectionURL is the right
one), and the latter is clearly working since if I inspect the flowfile
contents (payload) in the queue before it gets to PutDbRecord then it is
what I expect (half a dozen simple records that I am using for testing
with two char strings and an int value in each record). Nothing odd. No
weird hex / control characters or any such in there anywhere. Just
simple ascii text values.
This dataflow should work, but it isn't, and I dont understand why nor
why the odd SQL error message is given. If I use the SQL CLI then I can
read/write to/change (etc) the table as normal. If I run a bit of
equally simple Java, the insert works just fine, but with the NiFi, it
fails repeatably.
So, I admit to being baffled, and therefore any help / suggestions /
insight etc is welcome. Given how simple the application causing the
problem is, I am actually starting to wonder about there being a bug
somewhere either in NiFi or MySQL, but it's going to be one of those
that are a bugger to track down (no pun intended!) and so I thought that
I would ask the community (y'all!) for any comments and help first.
I've been working with SQL (and MySQL) for many years, and NiFi for a
fair while now, so I have done all the obvious already - changing db and
table names, creating new NiFi CSVReaders and so forth but they make no
difference. My dev environment here is also as simple as you can get - a
single machine running Ubuntu 18.04.1 (the latest LTS version), MySQL
5.7.23, and NiFi 1.7.1. There are no clustering, Hadoop, or Kafka type
things to cause problems anywhere.
All help gratefully received.... Regards and thanks to all....
Rick
Re: Weird behavior with the latest rev of MySQL and NiFi 1.7.1
Posted by Matt Burgess <ma...@apache.org>.
Rick,
Are you using the version of the MySQL driver in NiFi that corresponds
to your 5.7.23 server? That error seems like it'd be coming from the
database rather than the processor. I have seen issues (although not
this one) with older drivers against newer DBs.
Another thing to try might be to set up a simple flow with
ListDatabaseTables -> LogAttribute (leave the latter disabled) using
your same DBCPConnectionPool, and see if you get a flow file that
contains that table in the table.name attribute. You can even put the
table name in the Table Name Pattern property and see if you get a
single flow file output from ListDatabaseTables (see the doc [1] for
more details). I believe ListDatabaseTables does a SHOW TABLES rather
than explicitly querying information_schema, but it probably does the
same thing under the hood so I would think the same error you're
getting would show up here.
Regards,
Matt
[1] https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.7.1/org.apache.nifi.processors.standard.ListDatabaseTables/index.html
On Mon, Aug 13, 2018 at 12:26 PM Rick <rt...@imbuia-holdings.com> wrote:
>
> Hi Everybody!
>
> I am seeing some really unusual and unexpected MySQL error messages written into the NiFi log file by the latest MySQL version (5.7.23) when I try to load simple records from a CSV file with a very simple PutDatabaseRecord application.
>
> So, before I expend a lot of effort having the drains up, I wondered if anyone else has seen this sort of behavior with this NiFi / MySQL version combo, or alternatively, if y'all have this combo working ok, then I know that I need to look for something local on my system.
>
> Stripping my NiFi flow down to the bare minimum necessary to demonstrate the issue, it is: ListFile >> FetchFile >> PutDatabaseRecord, and it is the latter that triggers these weird error messages when it attempts to INSERT the records into the target SQL table. Shorn of the irrelevant verbiage, the specific error that I am now getting in the NiFi log file is "PutDatabaseRecord failed to process StandardFlowFIle Due to Unknown Table <name of the table that I am trying to write to> in information_schema".
>
> Behind the scenes are also a DBCPConnectionPool controller and a CSVReader. These are enabled and running; the former is obviously connecting to the DB as it can 'see' an information schema in the first place (separately, I have checked and the DBConnectionURL is the right one), and the latter is clearly working since if I inspect the flowfile contents (payload) in the queue before it gets to PutDbRecord then it is what I expect (half a dozen simple records that I am using for testing with two char strings and an int value in each record). Nothing odd. No weird hex / control characters or any such in there anywhere. Just simple ascii text values.
>
> This dataflow should work, but it isn't, and I dont understand why nor why the odd SQL error message is given. If I use the SQL CLI then I can read/write to/change (etc) the table as normal. If I run a bit of equally simple Java, the insert works just fine, but with the NiFi, it fails repeatably.
>
> So, I admit to being baffled, and therefore any help / suggestions / insight etc is welcome. Given how simple the application causing the problem is, I am actually starting to wonder about there being a bug somewhere either in NiFi or MySQL, but it's going to be one of those that are a bugger to track down (no pun intended!) and so I thought that I would ask the community (y'all!) for any comments and help first.
>
> I've been working with SQL (and MySQL) for many years, and NiFi for a fair while now, so I have done all the obvious already - changing db and table names, creating new NiFi CSVReaders and so forth but they make no difference. My dev environment here is also as simple as you can get - a single machine running Ubuntu 18.04.1 (the latest LTS version), MySQL 5.7.23, and NiFi 1.7.1. There are no clustering, Hadoop, or Kafka type things to cause problems anywhere.
>
> All help gratefully received.... Regards and thanks to all....
>
> Rick
>
Re: Weird behavior with the latest rev of MySQL and NiFi 1.7.1
Posted by Murray Williams <mu...@i-nexus.com>.
> I am now getting in the NiFi log file is "PutDatabaseRecord failed to
> process StandardFlowFIle Due to Unknown Table
> <
> name of the table that I am trying to write to
> >
> in information_schema".
I believe this is due to a mismatch between the MySQL driver version and the
MySQL version. See here
https://github.com/confluentinc/kafka-connect-jdbc/issues/573#issuecomment-476398546
In my case, I switched my target database from MySQL 5.7 to MySQL 8.0 and
this message went away. But based on other similar issues on forums, it
looks like you could also fix it by downgrading to a 5.17.x MySQL driver.
--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/
Re: Weird behavior with the latest rev of MySQL and NiFi 1.7.1
Posted by Juan Pablo Gardella <ga...@gmail.com>.
Did you check
https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html ?
I have similar issue with postgres. Mysql is case sensitive. Try to use
lower or upper case to test if that's the fix
On Mon, 13 Aug 2018 at 13:26 Rick <rt...@imbuia-holdings.com> wrote:
> Hi Everybody!
>
> I am seeing some really unusual and unexpected MySQL error messages
> written into the NiFi log file by the latest MySQL version (5.7.23) when I
> try to load simple records from a CSV file with a very simple
> PutDatabaseRecord application.
>
> So, before I expend a lot of effort having the drains up, I wondered if
> anyone else has seen this sort of behavior with this NiFi / MySQL version
> combo, or alternatively, if y'all have this combo working ok, then I know
> that I need to look for something local on my system.
>
> Stripping my NiFi flow down to the bare minimum necessary to demonstrate
> the issue, it is: ListFile >> FetchFile >> PutDatabaseRecord, and it is
> the latter that triggers these weird error messages when it attempts to
> INSERT the records into the target SQL table. Shorn of the irrelevant
> verbiage, the specific error that I am now getting in the NiFi log file is
> "PutDatabaseRecord failed to process StandardFlowFIle Due to Unknown Table <*name
> of the table that I am trying to write to*> in information_schema".
>
> Behind the scenes are also a DBCPConnectionPool controller and a
> CSVReader. These are enabled and running; the former is obviously
> connecting to the DB as it can 'see' an information schema in the first
> place (separately, I have checked and the DBConnectionURL is the right
> one), and the latter is clearly working since if I inspect the flowfile
> contents (payload) in the queue before it gets to PutDbRecord then it is
> what I expect (half a dozen simple records that I am using for testing with
> two char strings and an int value in each record). Nothing odd. No weird
> hex / control characters or any such in there anywhere. Just simple ascii
> text values.
>
> This dataflow should work, but it isn't, and I dont understand why nor why
> the odd SQL error message is given. If I use the SQL CLI then I can
> read/write to/change (etc) the table as normal. If I run a bit of equally
> simple Java, the insert works just fine, but with the NiFi, it fails
> repeatably.
>
> So, I admit to being baffled, and therefore any help / suggestions /
> insight etc is welcome. Given how simple the application causing the
> problem is, I am actually starting to wonder about there being a bug
> somewhere either in NiFi or MySQL, but it's going to be one of those that
> are a bugger to track down (no pun intended!) and so I thought that I would
> ask the community (y'all!) for any comments and help first.
>
> I've been working with SQL (and MySQL) for many years, and NiFi for a fair
> while now, so I have done all the obvious already - changing db and table
> names, creating new NiFi CSVReaders and so forth but they make no
> difference. My dev environment here is also as simple as you can get - a
> single machine running Ubuntu 18.04.1 (the latest LTS version), MySQL
> 5.7.23, and NiFi 1.7.1. There are no clustering, Hadoop, or Kafka type
> things to cause problems anywhere.
>
> All help gratefully received.... Regards and thanks to all....
>
>
>
> Rick
>
>