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 <rturner@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