mysql 에서 LOAD DATA INFILE 로 데이타 입력시 ERROR 1148 (42000) : The used command is not allowed 에러날경우

MYSQL ERROR 1148 (42000) : The used command is not allowed

In previous parts, of Series 2, data has been loaded into the database, from a text file, using the MYSQL statement:

LOAD DATA INFILE './ttd_entries.txt' INTO TABLE test.ttd FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;

 As it turns out using this statement is probably not the best way to load data into the database as it can lead to a series of errors, as we found out below.

The LOAD DATA INFILE statement appeared to have worked well and just when things were becoming interesting a few cracks began to emerge. Firstly, when attempting to add some new items to the things to do to table, ttd, we got the following error message.

1	ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/ttd_entries.txt' (Errcode: 2)

Now, after seeing the number of pages, on Google, a search for this error  produces we were encouraged enough to believe that a solution would be quite easily found. Hmm, wishful thinking! 

A solution proposed on some websites is to do with AppArmor, which prevents MYSQL from accessing the text file. The AppArmor solution proposed to solve the issue was to fire up a text editor like Emacs

sudo emacs /etc/apparmor.d/usr.sbin.mysql

and add the line /tmp/** rwk,  Then, restart the AppArmor application using the following command

sudo /etc/init.d/apparmor reload

Unsurprisingly, that didn't work and we still had the same error when trying to enter text into the database from a file. The next piece of invaluable advice was to check the MYSQL configuration file, to find the location of MYSQL databases. The file, my.cnf, should either be located in the /etc or /etc/mysql directories. On our system it is located in the latter location.

A check of the location of the MYSQL database storage directory showed it to be /var/lib/mysql, which implies that databases are stored as /var/lib/mysql/database_name. The temp directory was correctly set to /tmp. Hence,  everything appeared to be located, to us, where it should be. However, performing this exercise allowed us to confirm that we did not have access rights to this directory, which seemed to be the problem.

Hence, further search results suggested the ridiculous, as a solution to not having access rights, like to chmod 777 the directory, /var/lib/mysql, which is equivalent to placing a large banner outside your house saying "I leave my windows and doors unlocked, all the time. Please rob me!". For security reasons the /var/lib/mysql directory is owned by mysql (and possibily root) and should only be owned by MYSQL!

Another suggestion, that this time seemed reasonable, was to alter the MYSQL statement to the following

LOAD DATA LOCAL INFILE './ttd_entries.txt' INTO TABLE test.ttd FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;

which, presumably, has the effect of using a local directory for storage and not a directory that we do not have access to. Using this MYSQL statement produced the following error.

1	ERROR 1148 (42000): The used command is not allowed with this MySQL version

Crumbs! Just when we thought things couldn't get any worse we ended up with another error and more Google search page results to trundle through. Well, to cut a long story short (we know you good folk have better things to do and just want the solution) apparently the MYSQL variable LOAD DATA LOCAL INFILE is disabled on some systems, on either the server or client. When this is the case the client receives the ERROR 1148 above.

Now, although this sounded promising the suggested solutions were not. In the end we used a combination of the proposed solutions without sucess, until we tried starting MYSQL in the following way

$ mysql --local-infile=1

and like magic it worked, we can now load data into the database again. Now, the next thing to do is to find out how to permanently enable this variable in the database.

 

 

출처 http://www.recontech.co.uk/index.php/projects/series-2-project-and-product-management/76-series-2-error-1148-42000-the-used-command-is-not-allowed