Laravel - 라라벨 GROUP BY 에러 SQLSTATE[42000]: Syntax error or access violation: 1055

에러 발생

아래 쿼리는 MySQL 에서 정상 동작한다.

 

SELECT * FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7);
SELECT *, LEFT(REQ_DATE, 7) FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7);

 

하지만 라라벨에서 실행해 보면 아래 두가지 경우 모두 에러 메시지가 발생한다.

 

// query builder 를 사용하는 경우
$memos = DB::table('sj_reorder')->where('REQ_DATE', '>', '2019-12-01')->groupby(DB::raw('LEFT(REQ_DATE, 7)'))->get();

// select() Method 를 사용하는 경우
$query = "SELECT * FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7)";
$memos = DB::select($query);

 

Error Message

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1055 'samjin.sj_reorder.REQ_NO' isn't in GROUP BY (SQL: select * from `sj_reorder` where `REQ_DATE` > 2019-12-01 group by LEFT(REQ_DATE, 7))

 

해결 방법

원인은 메시지에 기재되어 있듯이, GROUP BY 절에 쓰인 칼럼은 SELECT 에도 명시되어야 한다는 것이다.

 

$query = "SELECT * YM FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7)";
// 아래처럼 수정하면 여전히 에러 발생 (GROUP BY 절에 모든 칼럼을 명시해야 한다)
$query = "SELECT *, LEFT(REQ_DATE, 7) AS YM FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7)";
// 결론은 아래와 같이 필요한 칼럼만 명시해야 한다.
$query = "SELECT LEFT(REQ_DATE, 7) AS YM, REQ_NO FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7), REQ_NO";
$reorders = DB::select($query);

$reorders = DB::table('sj_reorder')->where('REQ_DATE', '>', '2019-12-01')->groupby(DB::raw('LEFT(REQ_DATE, 7)'))->get();
// 수정하면
$reorders = DB::table('sj_reorder')->select(DB::raw('LEFT(REQ_DATE, 7) AS YM'), 'REQ_NO')->where('REQ_DATE', '>', '2019-12-01')->groupby(DB::raw('LEFT(REQ_DATE, 7)'), 'REQ_NO')->get();

 

* : asterisk 로 여러가지(모든) 칼럼을 출력하는 것이 불가능하기 때문에 불편하다.

이런 경우 아래와 같은 방법도 있다.

 

다른 해결 방법

DB::statement("SET SQL_MODE=''"); // 쿼리문 상단에 실행
$query = "SELECT * FROM sj_reorder WHERE REQ_DATE > '2019-12-01' GROUP BY LEFT(REQ_DATE, 7)";
$reorders = DB::select($query);

 

DB::statement("SET SQL_MODE=true") 을 하게 되면, 현재 세션에서 임시적으로 MySQL 설정 strict 를 off(false) 하기 때문에 쿼리문을 손 보지 않더라도 에러가 발생하지 않는다.

 

config/database.php 파일을 편집하면 파일(Model, Controller)마다 처리할 필요 없이 현재 프로젝트에 반영된다.

 

'mysql' => [
...(생략)...
    'strict' => false,
    'engine' => null,
...(생략)...
],

 

해당 설정은 보안 이슈를 야기하고, 몇 가지 변경점을 가져오는데 대표적으로는 DB 에 문자열이 잘려서 들어가는 것이다.

본래는 칼럼의 LENGTH 를 초과하는 경우 INSERT / UPDATE 쿼리가 아예 실행이 안되어야 하는데, 실행은 되면서 문자열이 잘린 형태로 적용된다 (이미 MySQL 에 설정되어 있다면 그대로 운영해 왔겠지만).

유효성 검사를 DB 에서 하였는지 PHP 코드에서 하였는지에 따라 추가적으로 손을 봐야 할 수 있다.

 

 

참고문헌 및 관련링크

1. php - Laravel : Syntax error or access violation: 1055 Error - Stack Overflow
2. MySQL Strict mode 끄기/켜기