Load Data Local Infile SQL Query

[DataLackey]

Hi,

I'm trying to use Filemaker to manage a mysql database on Amazon RDS. I'm trying to set up an import layout that will allow users to load data into into the RDS database.

Mostly, Filemaker and mysql are playing well together. But I'm getting a problem when I try import data using LOAD DATA LOCAL INFILE . I've run the query successfully with a different client on the same database (Sequel Pro) but when I run it using the Execute SQL script step in Filemaker I get an error, with the message "Load data local infile forbidden".

I've looked around online, and it seems like I need the server and client to both enable 'local_infile'. I know it's enabled on the server (because I can check manually, and the query works on a different client). Does anyone know how to check or change this within Filemaker?

Otherwise, does anyone have any ideas as to how I might work around this? Unfortunately I can't call a stored procedure on the server because the query relies on variables. My understanding is that you can't use variables in a LOAD DATA INFILE statement on the server, and you can't use LOAD DATA INFILE in a prepared statement.

Does anyone know how to change the local_infile variable within the Filemaker mysql client, or failing that, does anyone have a good workaround for the restriction on variables with the LOAD DATA INFILE statement if I run it on the server?

In case it's not already blatantly obvious, I don't really know what I'm doing, so any help (especially help that is dumbed down) would be hugely appreciated.

Thanks,

John

 

[webko]

Might be helpful if we knew which way this is running? You're trying to load data into the Amazon database? From a text file?

[DataLackey]

Yep. The text file is in a container field, stored externally. Trying to load that into the Amazon database. I've got a query that works in Sequel Pro, but doesn't work in Filemaker Execute SQL script step.

[webko]

What external error is returned - see Get(LastExternalErrorDetail) function

[DataLackey]

It's 1408 error: Load data local infile forbidden.

[Wim Decorte]

9 hours ago, DataLackey said:

Yep. The text file is in a container field, stored externally. Trying to load that into the Amazon database. I've got a query that works in Sequel Pro, but doesn't work in Filemaker Execute SQL script step.

How can it work in Sequel Pro if the text file is inside FM in a container file?

The 'execute sql' script step is internal to FM and has native access to the FM data, Sequel Pro would need to use the FM xDBC interface to get into FM to read the data so they are totally different beasts.

Are you trying to read the text file from its OS-level location to push to mysql?  If so that should not be possible because it means you have OS-level file sharing active on the FM remote container location, and that's a big no-no.

[DataLackey]

The container field is set to "Store container data externally',  so the text file isn't in the FM database. Sequel Pro can access the file without an xDBC driver.

The folder that holds the externally stored container field contents does not have file sharing enabled. As far as I know, file sharing isn't necessary for the LOAD DATA LOCAL INFILE SQL statement. I've had no problems with running these statements in Sequel Pro with the input file in a range of different locations without file sharing enabled.

I don't think the file being in a container field is the issue here. If I run a LOAD DATA LOCAL INFILE statement in Filemaker's execute SQL script step it gives the "forbidden" error regardless of the file's location. If I run the same scripts in Sequel Pro, they work.

[Wim Decorte]

8 hours ago, DataLackey said:

The container field is set to "Store container data externally',  so the text file isn't in the FM database. Sequel Pro can access the file without an xDBC driver.

The folder that holds the externally stored container field contents does not have file sharing enabled.

BIG RED FLAG!!!

You should never ever touch a file that fms stored externally.  The feature is not a document management feature; it is there to make FM's storage and backups more efficient.  You should treat the externally stored container data files like you would treat live FM files (because that's what those files are).  Nothing but FMS should have access to those files.

As to using the "execute sql" script step: if you are inside FM you need to work with that container data by referencing the container field, never by referencing the path to the externally stored file.  In fact, from an FM client you should never be able to resolve the path to those externally stored container data files.  If you can then OS sharing is active and sooner or later it will create havoc.

[DataLackey]

Hey Wim,

Thanks for looking into this. I didn't realise this was a problem. I'll make sure that the script doesn't reference a file from a container field.

That said, it still doesn't solve the issue I first raised. I've tried running the SQL query to load data from a text file that's not stored in Filemaker at all, it still doesn't work. I've run the query with text file sitting in the desktop. Sequel Pro works, Filemaker doesn't. They're both using the same ODBC driver and the same user account on the same external database.

Is this a problem with Filemaker's local_infile configuration, or is something else going on?

Edited October 27, 2016 by DataLackey

[Mike Duncan]

10 hours ago, DataLackey said:

The container field is set to "Store container data externally',  so the text file isn't in the FM database. Sequel Pro can access the file without an xDBC driver.

The folder that holds the externally stored container field contents does not have file sharing enabled. As far as I know, file sharing isn't necessary for the LOAD DATA LOCAL INFILE SQL statement. I've had no problems with running these statements in Sequel Pro with the input file in a range of different locations without file sharing enabled.

I don't think the file being in a container field is the issue here. If I run a LOAD DATA LOCAL INFILE statement in Filemaker's execute SQL script step it gives the "forbidden" error regardless of the file's location. If I run the same scripts in Sequel Pro, they work.

I am not sure that SQL command is supported in FM. Are you saying the Sequel Pro uses it to insert into it's own database, or that it inserts it into your AWS RDS hosted db?

Also, does the ODBC driver support that syntax? It might be a limitation there.

[DataLackey]

I'm trying to load data into the Amazon RDS db. Sequel Pro and FM are both connected to the same host db using the same ODBC driver. I figure the ODBC driver supports the syntax, since the query works on Sequel Pro.

The fact that I'm getting a "forbidden" error rather than a syntax error when it doesn't work suggests that Filemaker supports the command, but I don't know for sure.