MySQL INTO OUTFILE Write Permissions

[Pizzadude]

I moved my development server to a new Win 7 machine. Everything works on the new machine, except I am having trouble with a sql statement that executes, but produces no result.

dim sqlpath as c = "[PathAlias.ADB_Path]" + "\\" + "QBexport" + "\\"
sqlpath = filename_decode(sqlpath)
sqlpath = stuff(sqlpath,1,2,"")
sqlpath = stritran(sqlpath,"\\","/")
sqlpath = " '" + sqlpath + "QBexport" + timestamp + ".iif'"

sql = <<%sql%
SET @qbinsert = CONCAT ('QBexport',:A,'.iif');
SET @t3 = :B;
SET @user = :C;
SET @compID = :D;
SET @sqlpath = :E;
SET @sql_text =
CONCAT ("SELECT * FROM `qb_table2` into outfile", @sqlpath,"
FIELDS ENCLOSED BY \'\"\'
TERMINATED BY \'\t\'
ESCAPED BY \'\n\';");
PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;
INSERT INTO qbdownload (fileUploadChar, fileNameShort, CreateTime, UploadedBy, Company_FK) values (@qbinsert, @qbinsert, @t3, @user, @compID)
%sql%

args.add("A",timestamp)
args.add("B",t3)
args.add("C",userName)
args.add("D",comp_ID)
args.add("E",sqlpath)

flag = cn.execute(sql,args)

This should do two things. First, create a file in a folder on the development server. Second, insert a record into the qbdownload table.

The flag returns true, but it accomplishes neither task.

I still have my old development machine, also Win 7, up and running, and it works fine there.

I have tried various versions of this, and have discovered that if I eliminate the code for the into outfile, it will successfully execute the insert.

I cannot get it to produce a file using into outfile.

I think it is a security/permissions issue. I believe I need to grant write access (to folders) to localhost. But I have looked and looked and have not found the solution.

[Davidk]

Re: MySQL INTO OUTFILE Write Permissions

What MySQL errcode are you getting back?

I think you're making this a bit too difficult. I would break this into 2 sql statements. The following works. You didn't supply some of the variables, so I just made them up.

Where, exactly, or relatively, do you want your .iif file created. Mine is just going into the PathAlias location, but it can go anywhere. I'm not running into any security issues.

function sqlTest as c (e as p)
	
debug(1)
	
dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments

flag = cn.open("::Name::Test")

if flag then

	dim st as Y
	st = now()

	dim sqlpath as c = "[PathAlias.ADB_Path]" + "\\" + "QBexport" + "\\"
	dim timestamp as c = cshorttime(st)
	sqlpath = filename_decode(sqlpath)
'	sqlpath = stuff(sqlpath,1,2,"")
'	sqlpath = stritran(sqlpath,"\\","/")
	sqlpath = sqlpath + "QBexport" + timestamp + ".iif"	
'	sqlpath = " '" + sqlpath + "QBexport" + timestamp + ".iif'"	
	
	sqlStmt = <<%sql%
	SELECT * INTO OUTFILE :sqlPath
	FIELDS ENCLOSED BY '"' 
	TERMINATED BY '\t' 
	ESCAPED BY '\n'
	FROM tblCustomers
	%sql%

	args.set("sqlPath",sqlpath)
	flag = cn.execute(sqlStmt,args)		
	
	sqlStmt = "INSERT INTO qbdownload (fileUploadChar, fileNameShort, CreateTime, UploadedBy, Company_FK) values (:qbinsert, :qbinsert, :t3, :user, :compID)"

	args.set("qbinsert","QBexport" + timestamp + ".iif")
	args.set("t3",date())
	args.set("user","Username")
	args.set("compId","1234")

	flag = cn.Execute(sqlStmt,args)
	if flag then
		'ALL OK
	else
		'there was an error - close the connection and exit
		cn.Close()
		dim msg as c 
		msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
		msg = js_escape(msg)
		dim jscmd as c 
		jscmd = "alert('" + msg + "');"
		sqlTest = jscmd
		exit function
	end if  
	
else
	'there was an error - close the connection and exit
	dim msg as c 
	msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
	msg = js_escape(msg)
	dim jscmd as c 
	jscmd = "alert('" + msg + "');"
	sqlTest = jscmd
	exit function
end if

cn.close()
	
end function

[Pizzadude]

Re: MySQL INTO OUTFILE Write Permissions

I am not getting any MySql error code.

I've tried separating it into two statements. The insert statement works just fine.

Running the into outfile part by itself fails, with no error code.
sqlIIF = <<%sql%
SET @sqlpath = :E;
SET @sql_text =
CONCAT ("SELECT * FROM `qb_table2` into outfile", @sqlpath,"
FIELDS ENCLOSED BY \'\"\'
TERMINATED BY \'\t\'
ESCAPED BY \'\n\';");
PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;
%sql%

I ran this in the Sql Command Window:

SET @sql_text =
CONCAT ("SELECT * FROM `qb_table2` into outfile", 'c:/Alpha5/FreightZone/QBexport/QBexport.iif',"
FIELDS ENCLOSED BY \'\"\'
TERMINATED BY \'\t\'
ESCAPED BY \'\n\';");
PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1

It returned "SQL command result: Success", but the file is not created.

David, I like the way you have simplified it. I will work on it using your example.

While researching INTO OUTFILE I read "The MySQL server process must have write access to the target file". I am writing to a folder within the PathAlias location. I can upload and download to other folders within the PathAlias location. Does INTO OUTFILE, because it is executing directly from MySql, need some special permission setting?

[Pizzadude]

Re: MySQL INTO OUTFILE Write Permissions

David, in working with your example, I changed the connection name and the name of the table in sqlStmt. However, the first flag = cn.execute(sqlStmt,args) returns false.

The second sqlStmt works. It inserts a record into qbdownload table.

dim todaysDate as d = date()
dim stamp as c = dtoc(todaysDate,"1-")
'12-14-15 This is DavidK's code

dim cn as sql::Connection
dim flag as l
dim sqlStmt as c
dim rs as sql::ResultSet
dim args as sql::arguments

flag = cn.open("::Name::FreightZoneMySql")

if flag then

dim st as Y
st = now()

dim sqlpath as c = "[PathAlias.ADB_Path]" + "\\" + "QBexport" + "\\"
dim timestamp as c = cshorttime(st)
sqlpath = filename_decode(sqlpath)
' sqlpath = stuff(sqlpath,1,2,"")
' sqlpath = stritran(sqlpath,"\\","/")
sqlpath = sqlpath + "QBexport" + timestamp + ".iif"
' sqlpath = " '" + sqlpath + "QBexport" + timestamp + ".iif'"

sqlStmt = <<%sql%
SELECT * INTO OUTFILE :sqlpath
FIELDS ENCLOSED BY '"'
TERMINATED BY '\t'
ESCAPED BY '\n'
FROM qb_table2
%sql%
debug(1)
args.set("sqlPath",sqlpath)
flag = cn.execute(sqlStmt,args) 'flag = .F.

sqlStmt = "INSERT INTO qbdownload (fileUploadChar, fileNameShort, CreateTime, UploadedBy, Company_FK) values (:qbinsert, :qbinsert, :t3, :user, :compID)"

args.set("qbinsert","QBexport" + timestamp + ".iif")
args.set("t3",date())
args.set("user","Username")
args.set("compId","1234")

flag = cn.Execute(sqlStmt,args)
if flag then
'ALL OK
else
'there was an error - close the connection and exit
cn.Close()
dim msg as c
msg = "Could not execute the query. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
sqlTest = jscmd
exit function
end if

else
'there was an error - close the connection and exit
dim msg as c
msg = "Could not open the connection. Error reported was: " + cn.CallResult.text
msg = js_escape(msg)
dim jscmd as c
jscmd = "alert('" + msg + "');"
sqlTest = jscmd
exit function
end if

cn.close()

[Davidk]

Re: MySQL INTO OUTFILE Write Permissions

You're better off running this in a function and debugging your way through it. The connection object returns properties containing an error code and message if there are any. I didn't have to set any write access... I believe that just means you can write to the drive... that it's not set read-only.

Does your full path exist? It must. Not the file name... but the path... the folder structure must be in place. You're only creating a file... not a structure.

As you step through the code, you can access any of the variables set. You can type these variable names into the debugger Expression section, or double-click on a variable name in the debugger code. Below is what you want to see as Expressions before you get to the statement that set the vars.

SQL_Output.PNG

[Pizzadude]

Re: MySQL INTO OUTFILE Write Permissions

So I simplified my code, to just creating a file using into outfile.

It works as expected on my former development machine. On the new machine, no error, and no file created. So something must be different.

In comparing the two machines:

AA logs onto MySql as root, and the user settings in MySql for root are identical.
qb_table2 is identical.
The folder security settings are identical.



dim todaysDate as d = date()
dim stamp as c = dtoc(todaysDate,"1-")
t1 = time("", now() )
t2 = time("-0h_0m_0s_2", t1)
dim timestamp as c = stamp + t2
dim t3 as c = time("0h:0m:0s",t1)

dim cn as SQL::Connection
dim flag as l
dim args as sql::arguments
flag = cn.open("::Name::FreightZoneMySql")

dim sqlpath as c = "[PathAlias.ADB_Path]" + "\\" + "QBexport" + "\\"
sqlpath = filename_decode(sqlpath)
sqlpath = stuff(sqlpath,1,2,"")
sqlpath = stritran(sqlpath,"\\","/")
sqlpath = " '" + sqlpath + "QBexport" + timestamp + ".iif'"

dim sql as c
sql = <<%sql%
SET @sqlpath = :E;
SET @sql_text =
CONCAT ("SELECT * FROM `qb_table2` into outfile", @sqlpath,"
FIELDS ENCLOSED BY \'\"\'
TERMINATED BY \'\t\'
ESCAPED BY \'\n\';");
PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;
%sql%

args.set("A",timestamp)
args.set("B",t3)
args.set("E",sqlpath)

flag = cn.Execute(sql,args)
cn.Close()

[Davidk]

Re: MySQL INTO OUTFILE Write Permissions

See post # 5 for additional info added.

[Pizzadude]

Re: MySQL INTO OUTFILE Write Permissions

Progress! I had just realized that I need to dig deeper into the debugging results....

cn.CallResult.NativeText = The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I suspected it was some kind of permission setting with MySql.

[Pizzadude]

Re: MySQL INTO OUTFILE Write Permissions

David, thanks for your help.

Here is what solved my problem:

Edit the my.ini file. Default location for windows is c:\ProgramData\MySQL\MySQL Server 5.6\my.ini

my.ini contains all the startup settings for MySql.


After making a backup copy of my.ini, Find and delete these lines:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.6/Uploads"

Stop the MySQL server and restart.

Apparently this only affects certain commands. INTO OUTFILE is one of them.

[Davidk]

Re: MySQL INTO OUTFILE Write Permissions

Good to hear. I couldn't work without the debugger... both XBasic and Javacsript. They are essential.