MySQL Load Data Local

[Sarah Reichelt]

On 8/5/06, Michael Caufield <mcaufield at hoosierstamping.com> wrote:
> > I too ran into this problem with MySQL 5 and was unable to solve it. I
> > don't think it's anything to do with Revolution, it's MySQL's fault. I
> > turned on the local-infile option but this made no difference.
> > Actually, I probably should have stopped & re-started the SQL server
> > after changing it, but in the end, I just made a loop to read the data
> > in and INSERT it line by line. In my case, this was a one-off data
> > import so I didn't care that it took a bit more time.
> >
> > Cheers,
> > Sarah
>
> Sarah,
> I believe the problem is on the client side not the server side. If
> the problem doesn't lie with Revolution, then why did Load Data Local
> work with all rev versions through 2.7.2-dp-1 using the 5.0.21
> version of MySQL server? It appears that with the the 2.7.2-gm-1
> release, the dbmysql.bundle driver has been compiled with --local-
> infile=0. I am using this procedure via a Revolution script to import
> data into MySQL from a text export created by a HyperCard/Oracle
> system that we are replacing with Revolution/MySQL. The export/import
> is run overnight so we have current data to test the new system. The
> import alone takes over 2 hours using Load Data Local. The only
> workaround I have found is to use:
>
> get shell("mysqlimport --local-infile=1 --lines-terminated-by='\r'
> dbname" && quote & loadfile & quote)
>
> instead of:
>
> revExecuteSQL gConnID,"load data local infile '" & loadfile & "' into
> table " & tablename & " lines terminated by '\r'"
>
> I haven't tested the line by line INSERT method, but would expect it
> to be substantially slower than Load Data.
>


Hi Michael,

I upgraded to Rev 2.7 long beofre upgrading MySQL and I didn't do any
SQL stuff for ages, so I had thought it was the server. I read online
a lot of stuff about this problem, however as I said, even with the
server set to allow local-infile, I couldn't get it to work, so I
expect you are right and it is Rev that is adding a second block.

Yes, the INSERT method is quite a lot slower, but for my purposes,
slowing a one-off operation from less than a second to about 5 seconds
didn't make much difference.

However if you can get this to work in Rev, I would be interested to
know for future reference. Since I am not running my app on the same
compuer as the MySQL server, your work-around would be difficult to
set up.

Cheers,
Sarah