The used command is not allowed with this MySQL version

Jim_Madrid

I am trying to insert values into a mysql database from a csv or text file using the LOAD DATA LOCAL command and I get the following error message:

I read 4.2.4 Security issues with LOAD DATA LOCAL in mysql.com and understand that I have to configure mysql with --local-infile=1

That´s what I don´t understand...where do I do this exactly.

I typed in the following at the prompt:

C:\mysql\bin: mysqld --local-infile=1

and I get no response (normally another prompt would appear after which I type mysql.exe and I enter into mysql land)

I also have phpmyadmin and winmysqladmin...but cannot find with either of these to configure mysql.

Your help would be greatly appreciated.

Jim

Norman_Graham

The local command only works if you have the file in particular place (can't remember where). You don't need it. Use LOAD DATA INFILE "myfile.txt" into TABLE blah, blah ...

The file you want to load needs to be under c:\MYSQL\DATA\mytable and then it will load up just fine.

Best o luck

Norman

Jim_Madrid

Thanks a lot Norman that did the trick. However, now I have another problem...when I upload the file into the database all the values post into the first column. I exported the data (from filemaker pro) as comma separated text (csv) and .txt and both have the same result.

Any ideas what I am doing wrong??

Norman_Graham

... I've never actually used a CSV format. I can't help you. I learned the little I know about MySQL from the free manual and have always used tab-separated txt files as specified in the manual. Between each column value, you need a tab (only one). Each row ends with a tab after the last column value and then a single line return (hit enter once). Null values and/or empty values are rendered by \n. After the last row in the file there should be the final tab and then no line returns.

This may sound like a pain in the rear, particularly since, in a txt editor, you can't see tabs or line returns and there's no way of making them visible. However, there are tools to hand. I do all my MySQL load-up files in WORD (or any other word-processing package). I make extensive use of the search and replace tool to ensure clean data quickly and easily. Two or three simple search routines (e.g. search for ^p and ^t) ensure that there are no double tabs, no double line returns, no double spaces, no extra line returns at the end of the file and always a tab before each line return. You can do the same with a CSV file - replace all the commas with tabs etc.

My file is ready in 3 minutes and I just save it as a text-only file (or you can paste it into a text editor to make sure). The other advantage is that notepad and its relations can get pretty unstable when editing a large text file, particularly if you have the page-width function switched on to avoid endless scrolling from left to right. The lines start jumping around while you type and you can insert things in the wrong place without noticing. This won't happen with a word-processor. Also, a WYSIWYG editor is a lot easier on the eyes than checking through reams of text in notepad.

I hope this helps

Norman