The used command is not allowed with this MySQL version 에러 해결방법

The used command is not allowed with this MySQL version 에러 해결방법

아래와 같이 sql 문을 불러서 사용할 때 에러가 난다.

mysql> source loadstudent.sql

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

해결방법

1. mysql 접속시 --local-infile=1 옵션 추가

    mysql -u root -p --local-infile=1 database

  그러니까 실행할 때 위와 같이 명령어를 줍니다. root 는 사용자 이름, 맨 뒤에 데이터베이스는 db이름 입니다.

2.  local 키워드 제외

$ mysql>LOAD DATA INFILE 'test.csv' INTO TABLE tablename FIELDS TERMINATED BY ','; 

3. my.cnf 설정 변경

[mysql]

local-infile=1

3번 my.cnf 위치는 아래 명령어로 확인합니다. 맨 아래 파일 위치가 나오는데 그 파일을 찾아서 수정하시면 됩니다.

pi@raspberrypi:~/dbtest $ mysqld --verbose --help | grep -A 1 'Default options'
180320 11:06:02 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
180320 11:06:02 [Note] mysqld (mysqld 5.5.50-0+deb8u1) starting as process 23084 ...
180320 11:06:03 [Warning] Can't create test file /var/lib/mysql/raspberrypi.lower-test
180320 11:06:03 [Warning] Can't create test file /var/lib/mysql/raspberrypi.lower-test
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)
180320 11:06:03 [Warning] One can only use the --user switch if running as root

180320 11:06:03 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
180320 11:06:03 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
180320 11:06:03 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
pi@raspberrypi:~/dbtest $

mysql 실행할때 위 명령어로 들어가니 잘 됩니다.

pi@raspberrypi:~/dbtest $ mysql -u root -p --local-infile demo
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.50-0+deb8u1 (Raspbian)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> source loadstudent.sql
Query OK, 9 rows affected, 3 warnings (0.01 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 3
 
mysql> select * from student
    -> ;
+-------+--------------+-----------+-----------+
| scode | sname        | sdept     | sphone    |
+-------+--------------+-----------+-----------+
|       | NULL         | NULL      | NULL      |
| S001  |  fdfasasXy   |  computer |  123-4567 |
| S002  |  gsfgsdfdsfy |  computer |  123-4567 |
| S003  |  sssssssssy  |  computer |  123-4567 |
| S004  |  gggggggggXy |  computer |  123-4567 |
| S005  |  tttttXy     |  computer |  123-4567 |
| S006  |  aaaaaaaXy   |  computer |  123-4567 |
| S007  |  adfasdsy    |  computer |  123-4567 |
| S008  |  fdfasasXy   |  computer |  123-4567 |
+-------+--------------+-----------+-----------+
9 rows in set (0.00 sec)