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 <[email protected]> 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 >
