LOAD DATA INFILE not allowed

dev support
#1

Converting an app from CF to Lucee. OS is Windows. Database is MySQL 5.5.62. Everything seems to run ok under Lucee but a crucial part of the app is that data are imported from txt files:

LOAD DATA LOCAL INFILE '#importfilename#' INTO TABLE bulkimport

That works (with the same database) with CF - but Lucee says “The used command is not allowed with this MySQL version”. Before I added the LOCAL parameter, CF said the same but now it works in CF. I did not expect Lucee to behave differently here. What is wrong? do I need another MySQL driver?

Adding “allowLoadLocalInfile=true” to the connection string doesn’t change anything.
Adding “local-infile = 1” to my.ini doesn’t change anything.

I can’t believe how intransparent this is.

-Michael

#2

I’ve checked it with the lucee5.3.2.77 & 5.3.3.52-snaphot & it throws an error as like you said. Latest version for MySQL connector doesn’t work under the lucee. But downgrade the MySQL Extension into 8.0.14, It works fine & data inserted into the corresponding table and this is the workaround for the issue.

#3

thank you !!

#4

Filed a new bug for this issue https://luceeserver.atlassian.net/browse/LDEV-2311

#5

See https://forums.mysql.com/read.php?3,672495,672495

Functionality Added or Changed

 * Default value of the connection property
   allowLoadLocalInfile has been changed to false.
   Applications that use the LOAD DATA LOCAL INFILE
   (http://dev.mysql.com/doc/refman/8.0/en/load-data.html)
   statement on MySQL Server needs to set this property to
   true explicitly. (Bug #29261254)
#6

I have found a workaround for this, but you will need to specify your datasource in your Application.cfc.
If you define your datasource as is shown here: Datasources :: Lucee Documentation then change the connectionString property and add:
&allowLoadLocalInfile=true
to the end, then the MySQL connector will send that string to the server and allow you to “get through”.

Once you’ve done that, you’ll need to remove the datasource from the Lucee administrator so that it USES the datasource you’ve just defined!

You also need to set some options in MySQL’s ini file:

[client]
loose-local-infile=1
local_infile=1

[mysqld]
loose-local-infile=1
local_infile=1

I’m not sure if both these options are needed in BOTH places, but the documentation is a bit fuzzy.

Hope this helps somebody!

2 Likes
#7

Thank you recantha for helping us with this workaround. I’m trying to do this myself and I have a few questions that I’d love you (or anyone) to help me with. Admittedly, I’m an old-school CFMLer, still using only Application.cfm for my apps, so some of my questions will seem a bit “how does he not know this?” haha

  • I tried simply pasting the code from the Lucee Admin datasource into a new Application.cfc, but it complained there needs to be a component.
  • So following the link you sent on Creating datasources and its link to Create an Application.cfc, I tried surrounding that code with the onApplicationStart component which also didn’t work.
  • I even tried wrapping that string in a cfscript and putting it in an Application.cfm (a guy can hope! LOL) but that failed too, so what is the proper way to “wrap” the DataSource creation string from the Lucee Admin in our Application.cfc? Is there a preferred function to use rather than OnApplicationStart?
  • Since we are deleting the original DataSource from Lucee Admin, I’m assuming this isn’t just a one-time run of this file that “creates” the DataSource, but rather it re-creates it on each start of this application?
  • Will my old-school cfm code be able to use this cfc-created DataSource? Or would I have to convert my app/s to nothing but CFCs?
  • Is it absolutely required that this DataSource creation lives only in an Application.cfc file? No other way of creating it?

Sorry if all that seemed really novice, but ya, I’m feeling a bit novice with my move to Lucee from Adobe ColdFusion.

Any help/advice anyone can provide is appreciated!

Dan

PS - is there any chance this option will be added to the Lucee Admin in the future?

#8

I’m missing a default Application.cfc template in the offical docs. At least I didn’t find any. Going to add it to the docs as soon as I get some time to contribute.

Welcome to modern cfml. I understand the problems you are facing, because I was also at that exact point some time ago. Don’t give up. We from the Lucee Community are here to help.

In the Application.cfc you need to start ditacting with the component tag:

component {

}

To start with an empty template, I’ve just found this wonderful git repository with a default Application.cfc

Also look into Lucee Application.cfc documentation which explains all functionality. Here is the tag referrence for Application.cfc

I think yes! Shouldn’t be a problem. No need to change all your legacy application code to .cfc’s. Just the Application.cfc should work. However, some new application stuff only works within a modern Application.cfc. But other legacy coded stuff should simply work, unless its some type of edge casy code where you might find some issues.

Not at all. You can create them in your Administrator, However, as soon as you do it, copy the generated code (you’ll find it in the Lucee Admin after creating it) to your Application.cfc. Do the same with everything else: Session settings, Charset settings, Mappings etc. Use the “Application Export” section to do it. Doing this you will ensure portability of your application because these settings are backed up when backing up your www root. Sometimes you need to wipe out the server context or the web context (e.g. may happen on up-/downgrades and these usually wipe out your settings of the Lucee Administrator. When having the settings stored in your Application.cfc the settings simply will persist there.

#9

You can also use the < cfapplication> tag on a single page.

I do it quite often:


<cfset datasource["myTempDsn"] = {
     type = "postgresql",
     host = "myHost",
     [all other keys...]
}>

<cfapplication
     name = "test-import"
     datasources = "#datasource#">

Now I can write:

<cfquery datasource="myTempDsn">
    SELECT somethings
</cfquery>

myTempDsn is only valid for this page.

I hope it was helpful to you.

1 Like