MySQL Load Data Local

[Brian Yennie]

FWIW, you can also use fast INSERT syntax:

INSERT INTO myTable (a,b,c) VALUES (1,2,3),(4,5,6),(7,8,9);

instead of many statements:

INSERT INTO myTable (a,b,c) VALUES (1,2,3);
INSERT INTO myTable (a,b,c) VALUES (4,5,6);
INSERT INTO myTable (a,b,c) VALUES (7,8,9);

> 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
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your 
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>