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 <[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 > >
