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
