I am currently ‘test-driving’ openSUSE 11.3 to see if it can replace my current OS of choice, Ubuntu, which I have been using since Warty and so far I’m mostly impressed with it.
However, I currently use MySQL Query Browser to administer a simple table on my company’s website that I update every week using a simple script that wipes the table and loads a csv file back into it. This has worked faithfully in Ubuntu for a long time. I tried the very same script last Friday, using my backed-up .mysqlgui config and get a ‘Malformed Packet Error 2027’ when it reaches the command to load the csv file. I end up with an empty database as the first command works without incident. I had to reboot to my Ubuntu partition to complete the task.
I have read that to use ‘LOAD DATA LOCAL INFILE’, both the client and server need to compiled with the ‘-enable-local-infile’ switch. Is the version that comes with openSUSE 11.3 compiled this way or is there something else going on that is causing this problem?
Any help or insight would be appreciated.
LOAD DATA LOCAL INFILE is disabled for security reasons in the openSUSE package; however, you can use LOAD DATA INFILE if the file is on an accessible partition. It sounds as if omitting the LOCAL may be sufficient in your case.
Thanks for the quick reply.
However, omitting ‘LOCAL’ results in
‘Access Denied for user: ‘user.domain@%’ (Using password: NO) errno 1045’
(user.domain changed to remain secure)
I’ll have to research that one tomorrow and report back any findings.
Thanks again for your suggestion.
I ended up giving up on using MySQL Query Browser completely and wrote a simple bash script to perform the same task using mysqlimport.
Example: mysqlimport -h hostname.com -u username -p --local --delete --fields-terminated-by=’~’ databasename tablename.csv
This much quicker in the end and works wonderfully.
On a side note, I first tried to connect to the database server via the command line and execute the ‘load data local infile’ command from there but got an error explaining that the command wasn’t supported in the version i was using. This is somewhat odd in that the’ mysqlimport’ command I am using essentially mirrors the ‘truncate’ and ‘load data local infile’ commands.
This option is documented in mysql.info; I’ve not ever needed it so had forgotten about it but it is the obvious workaround for the fact that LOCAL is disabled in openSUSE because it has to be called explicitly rather than leaving the client open permanently when LOCAL is not disabled.