ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides 에러 해결

ERROR 3948 에러의 경우 DELETE FROM score Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 0.000 sec 에러와 연관이 있다.

 

MySQL에 테이블 생성 스크립트를 적용하던 중 3948 에러가 발생해서 확인해 보니 쿼리문에 문제가 생겨 발생하는 에러였다.

테이블을 생성하는 스크립트 파일(이하 A.sql)

# create score table for grade-keeping project

# The PRIMARY KEY is composed of two columns to prevent any combination
# of event_id/student_id from appearing more than once.

DROP TABLE IF EXISTS score;
#@ _CREATE_TABLE_
CREATE TABLE score
(
	student_id	INT UNSIGNED NOT NULL,
	event_id	INT UNSIGNED NOT NULL,
	PRIMARY KEY (event_id, student_id),
	score		INT NOT NULL
);
#@ _CREATE_TABLE_

생성된 테이블에 데이터를 추가하는 스크립트 파일 (이하 B.sql)

DELETE FROM score;
INSERT INTO score VALUES (1,1,20);
INSERT INTO score VALUES (3,1,20);
INSERT INTO score VALUES (4,1,18);
INSERT INTO score VALUES (5,1,13);
INSERT INTO score VALUES (6,1,18);
INSERT INTO score VALUES (7,1,14);
INSERT INTO score VALUES (8,1,14);
INSERT INTO score VALUES (9,1,11);
INSERT INTO score VALUES (10,1,19);
INSERT INTO score VALUES (11,1,18);
INSERT INTO score VALUES (12,1,19);
INSERT INTO score VALUES (14,1,11);
INSERT INTO score VALUES (15,1,20);
INSERT INTO score VALUES (16,1,18);
INSERT INTO score VALUES (17,1,9);
INSERT INTO score VALUES (18,1,20);
INSERT INTO score VALUES (19,1,9);
INSERT INTO score VALUES (20,1,9);
INSERT INTO score VALUES (21,1,13);
INSERT INTO score VALUES (22,1,13);
INSERT INTO score VALUES (23,1,16);
INSERT INTO score VALUES (24,1,11);
INSERT INTO score VALUES (25,1,19);
INSERT INTO score VALUES (26,1,10);
INSERT INTO score VALUES (27,1,15);
INSERT INTO score VALUES (28,1,15);
INSERT INTO score VALUES (29,1,19);
INSERT INTO score VALUES (30,1,17);
INSERT INTO score VALUES (31,1,11);
INSERT INTO score VALUES (1,2,17);
INSERT INTO score VALUES (2,2,8);
INSERT INTO score VALUES (3,2,13);
INSERT INTO score VALUES (4,2,13);
INSERT INTO score VALUES (5,2,17);
INSERT INTO score VALUES (6,2,13);
INSERT INTO score VALUES (7,2,17);
INSERT INTO score VALUES (8,2,8);
INSERT INTO score VALUES (9,2,19);
INSERT INTO score VALUES (10,2,18);
INSERT INTO score VALUES (11,2,15);
INSERT INTO score VALUES (12,2,19);
INSERT INTO score VALUES (13,2,18);
INSERT INTO score VALUES (14,2,18);
INSERT INTO score VALUES (15,2,16);
INSERT INTO score VALUES (16,2,9);
INSERT INTO score VALUES (17,2,13);
INSERT INTO score VALUES (18,2,9);
INSERT INTO score VALUES (19,2,11);
INSERT INTO score VALUES (21,2,12);
INSERT INTO score VALUES (22,2,10);
INSERT INTO score VALUES (23,2,17);
INSERT INTO score VALUES (24,2,19);
INSERT INTO score VALUES (25,2,10);
INSERT INTO score VALUES (26,2,18);
INSERT INTO score VALUES (27,2,8);
INSERT INTO score VALUES (28,2,13);
INSERT INTO score VALUES (29,2,16);
INSERT INTO score VALUES (30,2,12);
INSERT INTO score VALUES (31,2,19);
INSERT INTO score VALUES (1,3,88);
INSERT INTO score VALUES (2,3,84);
INSERT INTO score VALUES (3,3,69);
INSERT INTO score VALUES (4,3,71);
INSERT INTO score VALUES (5,3,97);
INSERT INTO score VALUES (6,3,83);
INSERT INTO score VALUES (7,3,88);
INSERT INTO score VALUES (8,3,75);
INSERT INTO score VALUES (9,3,83);
INSERT INTO score VALUES (10,3,72);
INSERT INTO score VALUES (11,3,74);
INSERT INTO score VALUES (12,3,77);
INSERT INTO score VALUES (13,3,67);
INSERT INTO score VALUES (14,3,68);
INSERT INTO score VALUES (15,3,75);
INSERT INTO score VALUES (16,3,60);
INSERT INTO score VALUES (17,3,79);
INSERT INTO score VALUES (18,3,96);
INSERT INTO score VALUES (19,3,79);
INSERT INTO score VALUES (20,3,76);
INSERT INTO score VALUES (21,3,91);
INSERT INTO score VALUES (22,3,81);
INSERT INTO score VALUES (23,3,81);
INSERT INTO score VALUES (24,3,62);
INSERT INTO score VALUES (25,3,79);
INSERT INTO score VALUES (26,3,86);
INSERT INTO score VALUES (27,3,90);
INSERT INTO score VALUES (28,3,68);
INSERT INTO score VALUES (29,3,66);
INSERT INTO score VALUES (30,3,79);
INSERT INTO score VALUES (31,3,81);
INSERT INTO score VALUES (2,4,7);
INSERT INTO score VALUES (3,4,17);
INSERT INTO score VALUES (4,4,16);
INSERT INTO score VALUES (5,4,20);
INSERT INTO score VALUES (6,4,9);
INSERT INTO score VALUES (7,4,19);
INSERT INTO score VALUES (8,4,12);
INSERT INTO score VALUES (9,4,17);
INSERT INTO score VALUES (10,4,12);
INSERT INTO score VALUES (11,4,16);
INSERT INTO score VALUES (12,4,13);
INSERT INTO score VALUES (13,4,8);
INSERT INTO score VALUES (14,4,11);
INSERT INTO score VALUES (15,4,9);
INSERT INTO score VALUES (16,4,20);
INSERT INTO score VALUES (18,4,11);
INSERT INTO score VALUES (19,4,15);
INSERT INTO score VALUES (20,4,17);
INSERT INTO score VALUES (21,4,13);
INSERT INTO score VALUES (22,4,20);
INSERT INTO score VALUES (23,4,13);
INSERT INTO score VALUES (24,4,12);
INSERT INTO score VALUES (25,4,10);
INSERT INTO score VALUES (26,4,15);
INSERT INTO score VALUES (28,4,17);
INSERT INTO score VALUES (30,4,11);
INSERT INTO score VALUES (31,4,19);
INSERT INTO score VALUES (1,5,15);
INSERT INTO score VALUES (2,5,12);
INSERT INTO score VALUES (3,5,11);
INSERT INTO score VALUES (5,5,13);
INSERT INTO score VALUES (6,5,18);
INSERT INTO score VALUES (7,5,14);
INSERT INTO score VALUES (8,5,18);
INSERT INTO score VALUES (9,5,13);
INSERT INTO score VALUES (10,5,14);
INSERT INTO score VALUES (11,5,18);
INSERT INTO score VALUES (12,5,8);
INSERT INTO score VALUES (13,5,8);
INSERT INTO score VALUES (14,5,16);
INSERT INTO score VALUES (15,5,13);
INSERT INTO score VALUES (16,5,15);
INSERT INTO score VALUES (17,5,11);
INSERT INTO score VALUES (18,5,18);
INSERT INTO score VALUES (19,5,18);
INSERT INTO score VALUES (20,5,14);
INSERT INTO score VALUES (21,5,17);
INSERT INTO score VALUES (22,5,17);
INSERT INTO score VALUES (23,5,15);
INSERT INTO score VALUES (25,5,14);
INSERT INTO score VALUES (26,5,8);
INSERT INTO score VALUES (28,5,20);
INSERT INTO score VALUES (29,5,16);
INSERT INTO score VALUES (31,5,9);
INSERT INTO score VALUES (1,6,100);
INSERT INTO score VALUES (2,6,91);
INSERT INTO score VALUES (3,6,94);
INSERT INTO score VALUES (4,6,74);
INSERT INTO score VALUES (5,6,97);
INSERT INTO score VALUES (6,6,89);
INSERT INTO score VALUES (7,6,76);
INSERT INTO score VALUES (8,6,65);
INSERT INTO score VALUES (9,6,73);
INSERT INTO score VALUES (10,6,63);
INSERT INTO score VALUES (11,6,98);
INSERT INTO score VALUES (12,6,75);
INSERT INTO score VALUES (14,6,77);
INSERT INTO score VALUES (15,6,62);
INSERT INTO score VALUES (16,6,98);
INSERT INTO score VALUES (17,6,94);
INSERT INTO score VALUES (18,6,94);
INSERT INTO score VALUES (19,6,74);
INSERT INTO score VALUES (20,6,62);
INSERT INTO score VALUES (21,6,73);
INSERT INTO score VALUES (22,6,95);
INSERT INTO score VALUES (24,6,68);
INSERT INTO score VALUES (25,6,85);
INSERT INTO score VALUES (26,6,91);
INSERT INTO score VALUES (27,6,70);
INSERT INTO score VALUES (28,6,77);
INSERT INTO score VALUES (29,6,66);
INSERT INTO score VALUES (30,6,68);
INSERT INTO score VALUES (31,6,76);

위와 같은 A, B파일을 차례대로 실행시킬 때 B 파일에서 에러가 났었다.

DELETE FROM score; 위 부분이 에러를 발생시키는 쿼리문이며 에러를 해석해 보자면 Delete문을 Where 조건 없이 사용할 때 safe update mode가 활성화되어있기 때문에 나는 에러다.

해결 방법으로는 여러가지가 있는데 나는 일시적으로 보안등급을 낮춘 다음 쿼리 실행 후 보안등급을 원상태로 돌렸다.

-- 보안등급 상태 확인
select @@sql_safe_updates;

-- 보안등급 상태 변경
set sql_safe_updates=0;

-- 쿼리 실행
-- ...

-- 보안등급 상태 원복
set sql_safe_updates= -- 상태값;