[RESOLVED] LOAD DATA INFILE (not on same server)

wlw_bb

Is this even possible?

Requirements: (due to Policies, Regulations, and of course internal politics)
1) .php page on intranet server “Aweb” provides a button for user to click to truncate a table in a mysql DB and re-populate with updated .txt file
2) .txt file resides on server “Bfile”, and the DB is located on server “Cmysql”.

BTW
Servers Aweb & Bfile are windows
Server Cmysql is unix

$import = ‘LOAD DATA INFILE “file://NetworkServer/divsion/webuse/fordb/T20_Export.txt” INTO TABLE

Result is:

Can’t get stat of’/var/lib/mysql/file:/NetworkServer/divsion/webuse/fordb/T20_Export.txt’ (Errcode:2)

Is it possible to get the db to look outside of its own server? :o

bradgrafelman

It is possible, though 'file://' is a Windows protocol, not Unix.

See this article about accessing Windows shares from Linux (or use Google to find similar articles).

wlw_bb

Just to make sure I'm understanding this correctly...
to allow the php code on the windows web server to tell the db on the unix server
to get information from a file on the windows file server,
i need to ask our unix admin to mount the windows file server to the unix db server.

OMG politics make my head hurt.

bradgrafelman

Well, I suppose you could rig up a solution using FTP if you had access to that, but in the end, yes, you need to engineer a solution that will allow the two servers running different operating systems to communicate with each other.

Weedpacket

Just to make a minor correction the file: URL scheme is not OS-specific (RFC1738). You're thinking of UNC.) I don't think LOAD DATA INFILE is expecting a URL. (For that matter, I don't think the file: scheme knows what to do with a naming authority in the URL - even its authors reckon it's of "limited use".....)

The file is named "Export" - could the web server send the file to some place from where the db server can read it? Could it not perhaps connect to the db itself and send the results that way?

wlw_bb

Update:
After I went to our unix team with this solution, their mgr. went to "the powers that be" and convinced them instead to give permission to set the user up (with a samba conection and proper access controls) to allow the user to place and update the text file directly on the db server. This eliminates the third server in the equation and my problem.

Thanks for keeping me from banging my head on a problem that should not have been my problem. 🙂