잡학문구

MySQL DB에서 어떤 경우에 LOCK이 발생하고 그 LOCK을 어떻게 찾아가는지에 대해

여러 시나리오에 따라 테스트를 해본다.

- 테스트 테이블 생성 : 테스트를 위한 테이블과 데이터를 생성한다.

-- create table
CREATE TABLE select_lock
(col1 varchar(50),
 col2 varchar(50));
 
 INSERT INTO select_lock VALUES('1st', 'first');
 INSERT INTO select_lock VALUES('2nd', 'second');
 
 commit;

💎 autocommit = OFF 일 때, select lock  발생

auto_commit = 0 일 때, select 일 경우에도 shared_read lock이 발생하여 DDL 수행 시 대기상태로 빠진다.

-  session A) SELECT 수행

SELECT * FROM select_lock;

• lock 정보 조회 : 다른 session에서 session C) 의 SQL 수행으로 LOCK 정보를 확인해 보면, LOCK_TYPE이 SHARED_READ로 보여지며 이 테이블의 row에 대한 LOCK을 잡는다. 같은 session에서 조회할 경우 LOCK 정보가 보이지 않는다. 이와같이 autocommit 이 off 인 session에서 select 만 할 경우에도 LOCK이 발생하며 다른 session에서 DML은 가능하지만 DDL 수행 시 waiting LOCK이 발생한다.

+-------------+---------------+-------------+-------------+-------------+----------------+------------------+------------------+------------------+-----------+
| object_type | object_schema | object_name | lock_type   | lock_status | processlist_id | processlist_user | processlist_host | processlist_info | thread_id |
+-------------+---------------+-------------+-------------+-------------+----------------+------------------+------------------+------------------+-----------+
| TABLE       | deldb         | select_lock | SHARED_READ | GRANTED     |             16 | root             | localhost        | NULL             |        57 |
+-------------+---------------+-------------+-------------+-------------+----------------+------------------+------------------+------------------+-----------+

- session B) DDL 수행

아래의 명령어와 같이 DDL을 수행하면 수행되지 않고 대기 상태로 된다. 즉 다른 session의 LOCK이 해소되기를 기다린다.

ALTER TABLE select_lock CHANGE col1 colA varchar(16);
-- 대기상태

- session C) lock 조회

위의 두 SQL을 실행한 상태에서 다른 session에서 아래의 SQL을 수행한다.

SELECT l.object_type, l.object_schema, l.object_name, l.lock_type, l.lock_status,
       t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_time, t.processlist_info, 
       t.processlist_state,	t.thread_id
FROM   performance_schema.metadata_locks l
       inner join performance_schema.threads t on t.thread_id = l.owner_thread_id
WHERE  processlist_id <> connection_id();

아래의 결과와 같이 최초 autocommit이 OFF 인 session에서 LOCK을 발생시키고, 다른 session에서 수행한 DDL이 대기상태인 것을 알 수 있다. thread_id 또는 processlist_id 별로 LOCK_STATUS를 보면 동일한 id 중에 GRANTED 만 있는 processlist 또는 thread가 있는 것을 확인할 수 있다. 아래에서는 thread_id가 49 또는 processlist_id가 8 인 것을 볼 수 있다. 다른 process는 PENDING 이 있는 것을 볼 수 있다.

+-------------+---------------+-------------------+---------------------+-------------+-----------+----------------+------------------------------------------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME       | LOCK_TYPE           | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO                                     |
+-------------+---------------+-------------------+---------------------+-------------+-----------+----------------+------------------------------------------------------+
| TABLE       | deldb         | select_lock       | SHARED_READ         | GRANTED     |        49 |              8 | NULL                                                 |
| GLOBAL      | NULL          | NULL              | INTENTION_EXCLUSIVE | GRANTED     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| BACKUP LOCK | NULL          | NULL              | INTENTION_EXCLUSIVE | GRANTED     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| SCHEMA      | deldb         | NULL              | INTENTION_EXCLUSIVE | GRANTED     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLE       | deldb         | select_lock       | SHARED_UPGRADABLE   | GRANTED     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLESPACE  | NULL          | deldb/select_lock | INTENTION_EXCLUSIVE | GRANTED     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLE       | deldb         | #sql-1698_a       | EXCLUSIVE           | GRANTED     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLE       | deldb         | select_lock       | EXCLUSIVE           | PENDING     |        51 |             10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
+-------------+---------------+-------------------+---------------------+-------------+-----------+----------------+------------------------------------------------------+

processlist를 조회해 보면 STATE이 "Waiting for table metadata lock" 인 것을 볼 수 있으며 이는 다른 프로세스의 LOCK이 해소되기를 기다리는 상태인 것이다. 또는 threads 에서 processlist_state 에서도 확인할 수 있다.

mysql> select * from information_schema.processlist;
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
| ID | USER            | HOST            | DB    | COMMAND | TIME | STATE                           | INFO                                                 |
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
|  8 | root            | localhost:63207 | deldb | Sleep   |  272 |                                 | NULL                                                 |
| 10 | root            | localhost:63240 | deldb | Query   |  242 | Waiting for table metadata lock | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| 12 | root            | localhost:63450 | deldb | Query   |    0 | executing                       | select * from information_schema.processlist         |
|  5 | event_scheduler | localhost       | NULL  | Daemon  | 1943 | Waiting on empty queue          | NULL                                                 |
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+

lock를 유발한 session이 아닌 다른 session에서 metalock이 발생한 상태에서 테이블에 대한 어떤 작업을 수행할 경우 작업이 수행되지 않고 PENDING 상태로 대기한다.

즉, 다른 session에서 select를 수행할 경우에도 DDL이 수행되지 않고 PENDING 상태로 대기한다.

단, lock을 유발한 session에서는 DML, DDL 작업이 가능하다.

- LOCK 해소

1) 첫번째 session에서 commit 또는 rollback을 수행한다.

2) 첫번째 session을 찾아 kill 시킨다.

3) DDL session을 kill 시킨다.

💎 explain 을 수행할 때

session A) autocommit = OFF일 때, EXPLAIN 수행 시

EXPLAIN FORMAT=tree
select * from select_lock;

session B)

SELECT l.object_type, l.object_schema, l.object_name, l.lock_type, l.lock_status,
       t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_time, t.processlist_info, 
       t.processlist_state,	t.thread_id
FROM   performance_schema.metadata_locks l
       inner join performance_schema.threads t on t.thread_id = l.owner_thread_id
WHERE  processlist_id <> connection_id();

수행결과 : 아래와 같이 SHARED_READ LOCK이 잡히는 것을 볼 수 있다. 즉, EXPLAIN을 수행하더라도 테이블에 SHARED_READ LOCK이 잡히는 것을 알 수 있다. 

+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+------------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE   | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_HOST | PROCESSLIST_USER | PROCESSLIST_INFO |
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+------------------+------------------+
| TABLE       | deldb         | select_lock | SHARED_READ | GRANTED     |       122 |             75 | localhost        | root             | NULL             |
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+------------------+------------------+

💎 autocommit = OFF 일 때, DML lock  발생

💎 유휴 세션 Blocking 쿼리 식별

내부 InnoDB locking 정보와 Session 정보를 연관시킬 수 있습니다. 예를 들어, 어떤 InnoDB 트랜잭션 ID에 대해 해당 세션이 잠금을 보유하여 다른 트랜잭션을 차단할 수 있는 세션인지 알고 싶을 수 있습니다.

아래 예제는 INFORMATION_SCHEMA.INNODB_TRX 테이블과 performance_schema의 data_locks 및 data_lock_waits 테이블 조회 데이터입니다.

💎 Lock 유발 SQL 찾기

1. DML

  - session#1 DML 수행

  - session#2 session#1과 동일한 데이터에 대한 DML 수행

  - session#1의 SQL 찾기

SELECT  l.OBJECT_TYPE, l.OBJECT_SCHEMA, l.OBJECT_NAME, l.LOCK_TYPE, l.LOCK_STATUS,
        t.THREAD_ID, PROCESSLIST_ID, PROCESSLIST_HOST, PROCESSLIST_USER, PROCESSLIST_INFO,
        s.sql_text, s.statement_id
FROM performance_schema.metadata_locks l
        INNER JOIN performance_schema.threads t ON t.THREAD_ID = l.OWNER_THREAD_ID
        INNER JOIN performance_schema.events_statements_current  s ON t.THREAD_ID = s.THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();

2. DDL

1. row lock

   - insert, update, delete : transaction 을 종료하지 않았을 경우

동일한 입력 매개변수에 대해 항상 동일한 결과를 생성하는 경우 "DETERMINISTIC"인 것으로 간주되고 그렇지 않은 경우 "NOT DETERMINISTIC" 것으로 간주됩니다. 기본값은 NOT DETERMINISTIC입니다. 함수가 "DETERMINISTIC"임을 선언하려면 DETERMINISTIC을 명시적으로 지정해야 합니다.

MySQL은 DETERMINISTIC으로 선언된 루틴에 비결정적(NOT DETERMINISTIC) 결과를 생성하는 문이 없는지 확인하지 않습니다. 루틴을 잘못 선언하면 결과나 성능에 영향을 미칠 수 있습니다. 비결정적 루틴을 DETERMINISTIC으로 선언하면 옵티마이저가 잘못된 실행 계획을 선택하여 예기치 않은 결과가 발생할 수 있습니다. DETERMINISTIC 루틴을 NON DETERMINISTIC으로 선언하면 성능이 저하될 수 있습니다.

동일한 입력 매개 변수에 대해 항상 동일한 결과를 생성하는 경우, DETERMINISTIC로 간주되고 그렇지 않으면 NOT DETERMINISTIC으로 간주됩니다.

MySQL의 Stored procedure나 Function이 NOT DETERMINISTIC으로 정의되면 Mysql은 이 Stored routine의 결과값이 시시각각 달라진다고 가정하고, 비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해서 비교를 실행하게 됩니다.

즉, 함수 호출의 결과값이 Cache되지 않고, 비교되는 레코드 건수만큼 함수 호출을 발생하는 것입니다.

테스트 수행

- 테이블생성

- 테이블 데이터 생성(1백만건), 코드테이블 생성(함수를 수행해서 코드값을 return 할 수 있도록, 1000건)

- 코드별 균등하게 데이터 생성(case1: 코드 별로 1,000건 정도, case2: 코드종류 1건만)

- 펑션 생성(determistic, non-determistic)

- 수행 결과 비교

- 수행시간, profiling, 자원사용량?

코드테이블 생성 및 데이터 생성

-- 코드테이블 생성
create table code_tab(code int, code_name varchar(20));
alter table code_tab add constraint pk_code_tab primary key (code);

-- 코드데이터 생성
insert into code_tab(code, code_name)
WITH RECURSIVE cre_code(code, code_name) AS
(
 select 1 code, cast('code-1' as char(50))
 union all
 select code + 1, concat('code-', code) from cre_code where code < 1000
)
select * From cre_code;

transaction 테이블 생성 및 데이터 생성

-- transaction 테이블 생성
create table transaction_tab(tid int, tid_name varchar(20), code int);
alter table transaction_tab add constraint pk_transaction_tab primary key (tid);

-- transaction 데이터 생성
INSERT INTO TRANSACTION_TAB(TID, TID_NAME, CODE)
WITH RECURSIVE cre_tid(tid, tid_name, code) AS
(
 SELECT 1 tid, cast('transaction-1' AS CHAR(50)), 999
 UNION ALL
 SELECt /*+ SET_VAR(cte_max_recursion_depth = 2M) */ tid + 1, CONCAT('transaction-', tid), 999 FROM cre_tid where tid < 1000000
)
SELECT * FROM cre_tid;

stored function 생성 - DETERMISTIC, NON-DETERMISTIC

-- 코드값을 return 하는 stored function
DELIMITER $$
CREATE FUNCTION getcodename(v_code varchar(20)) RETURNS VARCHAR(20)
  DETERMINISTIC
BEGIN
declare v_codename varchar(20);
  select code_name into v_codename from code_tab where code = v_code ;
  return v_codename;
END $$
DELIMITER ;

수행시간 검증 

- Stored Function 호출, Scalar Subquery, Join 비교

-- function 호출
explain analyze
select *, getcodename(code) from transaction_tab 
where tid > 10000 and tid < 21000;
--------------------------------------------------------------------------------------
-> Filter: ((a.tid > 10000) and (a.tid < 21000))  (cost=101289.41 rows=110728) (actual time=4.361..475.477 rows=10999 loops=1)
     -> Table scan on A  (cost=101289.41 rows=996748) (actual time=0.022..422.806 rows=1000000 loops=1)
  ==> 수행시간 : 4.891 sec 
     
-- scalar subquery
explain analyze
select *, (select code_name from code_tab where code = A.code) from transaction_tab A 
where tid > 10000 and tid < 21000;
--------------------------------------------------------------------------------------
3	364	16:43:37	explain analyze
 select A.*, (select B.code_name from code_tab B where B.code = A.code) from transaction_tab A 
 where A.tid > 10000 and A.tid < 21000	1 row(s) returned	5.078 sec / 0.000 sec
 ==> 수행시간 : 5.078 sec 
 
-- join query
explain analyze
select *, B.code_name from transaction_tab A, code_tab B 
where A.tid > 10000 and A.tid < 21000
and   A.code = B.code;
--------------------------------------------------------------------------------------
-> Inner hash join (a.`code` = b.`code`)  (cost=1227191.04 rows=123006) (actual time=5.196..444.114 rows=10999 loops=1)
     -> Filter: ((a.tid > 10000) and (a.tid < 21000))  (cost=120.92 rows=11073) (actual time=4.628..442.106 rows=10999 loops=1)
        ...
==> 수행시간 : 0.469 sec

DETERMISTIC Stored Function 호출과 Scalar Subquery 그리고 JOIN 쿼리 비교 시

DETERMISTIC Stored Function 호출과 Scalar Subquery의 소요시간과 실행계획은 동일하였고 미세하게 Strored Function 수행 시간이 빨랐다. 그러나, JOIN 쿼리가 상대적으로 더 좋은 수행 속도를 보여주었다.(HASH JOIN을 수행하기 때문)

JOIN Query를 사용할 수 있으면 최대한 JOIN 쿼리를 사용하도록 한다.

code_table의 code 컬럼에 인덱스 생성 시(또는 Primary key 지정 시) 3개의 수행속도는 거의 비슷했다.

데이터와 테스트 방법에 따라 결과가 상이할 수 있으니 실제 적용 시에는 더 많은 Case로 테스트 해보는 것이 좋겠다.

NON-DETERMISTIC Function은 대부분의 경우 성능이 떨어지기 때문에 테스트하지 않는다. 

Stored Programs 정의

각 저장 프로그램에는 SQL 문으로 구성된 문장이 포함되어 있습니다. 이 명령문은 세미콜론(;) 문자로 구분된 여러 명령문으로 구성된 복합 명령문입니다. 

예를 들어 다음 저장 프로시저에는 BEGIN ... END 블록에 select 문장이 있습니다.

CREATE PROCEDURE user_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;

mysql 클라이언트 프로그램을 사용하여 위의 세미콜론 문자가 포함된 저장 프로그램을 작성하면 아래와 같이 에러가 발생합니다. 기본적으로 mysql은 세미콜론을 명령문 구분 기호로 인식하기 때문입니다. 

그러므로 mysql이 stored procedure 에서 세미콜론(;)을 인식하도록 임시로 구분 기호를 정의해야 합니다.

mysql> CREATE PROCEDURE user_count()
    -> BEGIN
    ->   SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near '' at line 3

mysql 구분 기호를 재정의하려면 delimiter 명령을 사용합니다. 다음 예에서는 user_count() 프로시저를 정의하기 위해  delimiter를 (//) 로 사용했습니다. 이를 통해 프로시저 본문에 사용된 구분 기호*;)를 자체에서 해석하지 않고 서버로 전달하여 stored procedure를 생성할 수 있습니다. 

마지막 줄의 delimiter ; 는 다시 임시로 변경한 delimiter를 세미콜론(;)으로 원복하는 명령어입니다.

mysql> delimiter //

mysql> CREATE PROCEDURE user_count()
    -> BEGIN
    ->   SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

PROCEDURE, FUNCTION, TRIGGER에서 세미콜론을 구분하기 위해 delimeter를 사용합니다.

MySQL 에서 Oracle connect by 구현하기

Oracle에서 Hierarchy 구조를 구현하는 connect by 구문은 없지만, with recursive 문으로 동일하게 구현할 수 있습니다.

재귀 공통 테이블 표현식은 자체 이름을 참조하는 하위 쿼리가 있는 식입니다. 예를 들어:

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

ERROR 1146 (42S02): Table 'cte_name' doesn't exist

SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets

이전에 표시된 재귀적 CTE 하위 쿼리에는 단일 행을 검색하여 초기 행 집합을 생성하는 비재귀적 부분이 있습니다.

SELECT 1

CTE 하위 쿼리에는 다음 재귀 부분도 있습니다.

SELECT n + 1 FROM cte WHERE n < 5

각 반복에서 해당 SELECT는 이전 행 집합의 n 값보다 하나 더 큰 새 값을 가진 행을 생성합니다. 첫 번째 반복은 초기 행 세트(1)에서 작동하고 1+1=2를 생성합니다. 두 번째 반복은 첫 번째 반복의 행 집합(2)에서 작동하고 2+1=3을 생성합니다. 이것은 n이 더 이상 5보다 작지 않을 때 발생하는 재귀가 끝날 때까지 계속됩니다.

CTE의 재귀 부분이 비재귀 부분보다 열에 대해 더 넓은 값을 생성하는 경우 데이터 잘림을 방지하기 위해 비재귀 부분의 열을 확장해야 할 수 있습니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

비엄격 SQL 모드에서 명령문은 다음 출력을 생성합니다.

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

비재귀적 SELECT가 열 너비를 결정하기 때문에 str 열 값은 모두 'abc'입니다. 결과적으로 재귀 SELECT에 의해 생성된 더 넓은 str 값이 잘립니다.
엄격한 SQL 모드에서 명령문은 오류를 생성합니다.

ERROR 1406 (22001): Data too long for column 'str' at row 1

이 문제를 해결하려면 명령문이 잘림이나 오류를 생성하지 않도록 비재귀 SELECT에서 CAST()를 사용하여 str 열을 더 넓게 만듭니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

Now the statement produces this result, without truncation:

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

열은 위치가 아닌 이름으로 액세스됩니다. 즉, 이 CTE에서와 같이 재귀 부분의 열은 위치가 다른 비재귀 부분의 열에 액세스할 수 있습니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;

한 행의 p는 이전 행의 q에서 파생되고 그 반대의 경우도 마찬가지이므로 양수 값과 음수 값은 출력의 각 연속 행에서 위치를 바꿉니다.

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

일부 구문 제약 조건은 재귀 CTE 하위 쿼리 내에서 적용됩니다.

이러한 제약 조건은 ORDER BY, LIMIT(MySQL 8.0.18 및 이전 버전) 및 DISTINCT의 MySQL 관련 제외를 제외한 SQL 표준에서 비롯됩니다.
재귀 CTE의 경우 재귀 SELECT 부분에 대한 EXPLAIN 출력 행은 Extra 열에 Recursive를 표시합니다.
EXPLAIN이 표시하는 예상 비용은 반복당 비용을 나타내며 총 비용과 상당히 다를 수 있습니다. 옵티마이저는 WHERE 절이 false가 되는 시점을 예측할 수 없기 때문에 반복 횟수를 예측할 수 없습니다.

CTE 실제 비용은 결과 집합 크기의 영향을 받을 수도 있습니다. 많은 행을 생성하는 CTE는 메모리 내 형식에서 디스크 형식으로 변환하기에 충분히 큰 내부 임시 테이블이 필요할 수 있으며 성능이 저하될 수 있습니다. 그럴경우 허용되는 메모리 내 임시 테이블 크기를 늘리면 성능이 향상될 수 있습니다.

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

SET max_execution_time = 1000; -- impose one second timeout

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+

mysql> WITH RECURSIVE fibonacci ...
       ...
       SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
|    13 |
+-------+

mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date       | price  |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 |  50.00 |
| 2017-01-08 |  10.00 |
| 2017-01-08 |  20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 |   5.00 |
+------------+--------+

mysql> SELECT date, SUM(price) AS sum_price
       FROM sales
       GROUP BY date
       ORDER BY date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-06 |     50.00 |
| 2017-01-08 |    180.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;

+------------+
| date       |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+

WITH RECURSIVE dates (date) AS
(
  SELECT MIN(date) FROM sales
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates
  WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;

+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |    300.00 |
| 2017-01-04 |      0.00 |
| 2017-01-05 |      0.00 |
| 2017-01-06 |     50.00 |
| 2017-01-07 |      0.00 |
| 2017-01-08 |    180.00 |
| 2017-01-09 |      0.00 |
| 2017-01-10 |      5.00 |
+------------+-----------+

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+

WITH RECURSIVE employee_paths (id, name, path) AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM employee_paths AS ep JOIN employees AS e
      ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

mysql> WITH RECURSIVE ...
       ...
       SELECT * FROM employees_extended
       WHERE id IN (692, 4610)
       ORDER BY path;
+------+-------+-----------------+
| id   | name  | path            |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
|  692 | Tarek | 333,692         |
+------+-------+-----------------+

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

GCP에 프로젝트 수행을 위한 자원 구성

1. GCP 가입

google 계정 생성 후 GCP 에 등록합니다.

2. VPC 생성

  - subnet 생성

  - 

3. VM 인스턴스 생성

  - java 설치

  - nginx 설치

  - tomcat 설치

  - mysql JDBC driver 설치

4. Cloud SQL 생성

5. load balance 생성(external, internal)

6. filestore 생성

7. 

1. Project 생성

GCP 콘솔의 좌측 3단메뉴를 클릭한 다음 [IAM 및 관리자]를 클릭한 후 [프로젝트 만들기] 메뉴를 클릭한다. 원하는 프로젝트이름을 입력한 후 [만들기] 버튼을 클릭한다. 본 문서에서는 프로젝트 이름을 "pjt-aicallbot-prd"로 입력한다.

2. VPC 생성

이름 : vpc의 이름

서브넷

  - 서브넷 생성 모드 : 커스텀 선택

  - 새 subnet 생성

    이름 : 서브넷 이름

    리전 : 서브넷을 생성할 리전 선택(예시 : asia-northeast3

    IP 주소범위 : 서브넷 주소범위 입력(예시 :10.0.0.0/22)

    비공개 Google 액세스 : 사용 선택(Cloud SQL 접속 시 필요)

    흐름로그 : 사용 선택

방화벽 규칙

  - icmp와 ssh port를 선택한다.

[만들기] 버튼을 클릭하여 VPC와 subnet을 생성한다.

* 참고

자동 모드 IPv4 범위

3. VM 인스턴스 생성

GCP 클라우드 콘솔의 탐색메뉴(좌측 상단의 삼단메뉴)에서 [Compute Engine] - [VM 인스턴스]를 클릭한 후 [인스턴스 만들기]를 클릭한다.

- 이름 : VM 인스턴스의 이름

- 리전, 영역 : VM 인스턴스를 생성할 리전과 영역 선택

- 머신 구성 : 업무에 필요한 적정한 vCPU와 메모리 선택

- 부팅디스크 : 원하는 OS 이미지를 선택(CentOS, Debian, Red Hat Enterprise Linux, Ubuntu, Window Server 등)

- 액세스 범위 : 사용할 API 액세스 설정(Cloud SQL을 사용할 경우 "모든 Cloud API에 대한 전체 액세스 허용"을 선택하던지, "각 API에 액세스 설정"을 선택하여 Cloud SQL 항목을 "하용 설정됨"으로 선택

네트워킹, 디스크, 보안, 관리, 단독 테넌시 항목

- 네트워크 태그 : 네트워크 태그 입력(서버에 접속할 수 있는 설정, AWS 의 Security Group 과 유사)

- 호스트이름 : 인스턴스의 호스트 이름

- 네트워크 인터페이스 : 네트워크를 클릭하여 생성한 VPC 선택, VPC에 생성한 서브네트워크 선택, 기본내부IP는 임시(자동)으로 선택하던지 지정된 IP를 쓰고자 하면 임시(커스텀)을 클릭하여 IP 입력, 또는 "고정 내부 IP주소 예약" 을 선택하여 고정IP 사용

- 외부IP : 외부에서 접속하지 않을 경우 "없음" 선택

보안

- VM 액세스 : "IAM 권한을 통해 VM 액세스 제어" 선택. SSH키로 접근할 경우에는 "프로젝트 차원 SSH키 차단" 해제

[만들기] 버튼을 클릭하여 VM 인스턴스를 생성한다.

GCP 콘솔에서 SSH(브라우저에서 열기) 로 방금 생성한 VM 인스턴에서 접속한다.

java, mysql-client, was server 등 GCP 기본 아키텍처를 구성하기 위해 필요한 패키지를 설치한다.

- java 설치

$ sudo apt install openjdk-11-jdk 
## 또는
$ sudo apt install default-jdk

## 설치후 버전확인
$ java -version
openjdk version "11.0.14" 2022-01-18
OpenJDK Runtime Environment (build 11.0.14+9-post-Debian-1deb10u1)
OpenJDK 64-Bit Server VM (build 11.0.14+9-post-Debian-1deb10u1, mixed mode, sharing)

- tomcat 설치

tomcat 설치를 위한 계정을 생성한다

$ sudo mkdir /opt/tomcat
$ sudo groupadd tomcat
$ sudo useradd -s /bin/false -g tomcat -d /opt/tomcat tomcat

wget 이 없을 경우 sudo apt install wget 명령어로 설치합니다.

/opt/tomcat 디렉토리로 변경하고 wget을 사용하여 zip 파일을 다운로드합니다.

$ sudo apt install wget
$ cd /opt/tomcat
$ sudo wget https://dlcdn.apache.org/tomcat/tomcat-10/v10.0.17/bin/apache-tomcat-10.0.17.tar.gz

다운로드가 완료되면 tar 파일의 압축을 풉니다.

sudo tar xzvf /opt/tomcat/apache-tomcat-10.0.17.tar.gz -C /opt/tomcat --strip-components=1

압축을 해제한 파일에 대해 tomcat user로 소유를 변경해 줍니다.

sudo chown -R tomcat:tomcat /opt/tomcat/

- Tomcat 서비스 등록

시스템에 서비스로 등록하기 위한 파일을 생성한다.

sudo vi /etc/systemd/system/tomcat.service

아래 내용을 입력한다. 아래 내용 중 JAVA_HOME 은 자신의 java 경로를 지정한다.

[Unit]
Description="Tomcat Service"
After=network.target syslog.target
[Service]
Type=forking
User=tomcat
Group=tomcat
Environment="JAVA_HOME=/usr/lib/jvm/java-1.11.0-openjdk-amd64"
Environment="CATALINA_BASE=/opt/tomcat"
Environment="CATALINA_HOME=/opt/tomcat"
Environment="CATALINA_PID=/opt/tomcat/temp/tomcat.pid"
Environment="CATALINA_OPTS=-Xms512M -Xmx1024M -server -XX:+UseParallelGC"
ExecStart=/opt/tomcat/bin/startup.sh
ExecStop=/opt/tomcat/bin/shutdown.sh
[Install]
WantedBy=multi-user.target

서비스를 등록하고 시작한다.

## 서비스 등록
$ sudo systemctl enable tomcat

## 서비스 시작
$ sudo systemctl start tomcat

## 서비스 상태확인
$ sudo systemctl status tomcat

브라우저로 "http://YOUR_IP_ADDRESS:8080"에 접속하여  tomcat 서비스가 정상적인지 확인해 본다.

서비스를 중지한다.

$ sudo systemctl stop tomcat

tomcat 버전을 확인한다.

$ sudo /opt/tomcat/bin/version.sh | grep version
Server version: Apache Tomcat/10.0.17

- 웹서버 설치(nginx 설치)

패키지 인덱스를 업데이트합니다.

sudo apt update

 Nginx 패키지를 설치합니다.

sudo apt install nginx

Nginx 버전을 확인한다.

$ sudo nginx -v
nginx version: nginx/1.14.2

Nginx 서비스는 설치 프로세스가 완료된 후 자동으로 시작됩니다. 다음 curl 명령을 실행하여 확인할 수 있습니다.

curl -I 127.0.0.1

# HTTP/1.1 200 OK
# Server: nginx/1.10.3
# Date: Mon, 27 Aug 2018 22:29:02 GMT
# Content-Type: text/html
# Content-Length: 612
# Last-Modified: Mon, 27 Aug 2018 22:27:54 GMT
# Connection: keep-alive
# ETag: "5b847aea-264"
# Accept-Ranges: bytes

방화벽 조정

서버 방확벽이 설정되어 있을 경우에 해당된다.

iptable을 사용하여 시스템에 대한 연결을 필터링하는 경우 HTTP(80) 및 HTTPS(443) 포트를 열어야 합니다. 

다음 명령을 실행하여 필요한 포트를 엽니다.

sudo iptables -A INPUT -p tcp --dport 80 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 443 -j ACCEPT

systemctl로 Nginx 서비스 관리

systemctl 명령을 사용하여 다른 systemd 장치와 마찬가지로 Nginx 서비스를 관리할 수 있습니다.

Nginx 서비스를 중지하려면 다음을 실행합니다.

sudo systemctl stop nginx

 시작하려면 다음을 입력합니다.

sudo systemctl start nginx

 Nginx 서비스를 재시작하려면 다음을 수행합니다.

sudo systemctl restart nginx

 구성을 변경한 후 Nginx 서비스를 다시 로드합니다.

sudo systemctl reload nginx

 부팅 시 자동으로 서비스를 기동하도록 다음을 수행합니다.

sudo systemctl enable nginx

부팅 시 자동으로 서비스를 시작하지 않도록 하려면 다음을 수행합니다.

sudo systemctl disable nginx

- Nginx 구성 파일은 /etc/nginx 디렉터리에 저장됩니다.

- 기본 Nginx 구성 파일은 /etc/nginx/nginx.conf입니다.

- mysql client 설치

Cloud SQL 에 접속하기 위한 mysql client를 설치한다. DB 접속 테스트를 위한 방법은 

1) mysql client 로 확인

$ sudo apt install default-mysql-client

2) mysql jdbc driver 로 확인

- jdbc driver download

- 연결테스트용 java 프로그램

3) Cloud SQL 연결테스트로 확인

- Cloud SQL 클릭 후 좌측 메뉴의 [연결] 메뉴를 클릭한다.

- [연결 테스트] 탭을 클릭 후 [만들기]를 클릭한다.

  . 테스트이름

  . 프로토콜

  . 소스, 대상 지정(대상은 Cloud SQL 및 포트, 소스는 Cloud SQL에 접속할 서버)

- 접속 계정 생성

VM 인스턴스에 접속할 계정을 생성하고 패스워드를 설정한다. GCP debian linux 에 sudo 그룹은 sudo 명령어를 수행할 수 있는 권한이 있다.

$ sudo useradd -m -d /home/admin -s /bin/bash -g adm -G users,sudo admin
$ sudo passwd admin

인스턴스 템플릿

인스턴스 그룹에서 사용할 인스턴스 템플릿을 생성한다.

[Compute Engine] - [인스턴스 템플릿]을 클릭한 후 나타난 화면에서 [인스턴스 템플릿만들기]를 클릭한다.

인스턴스 템플릿명을 itm-aicallboot-admin-prd-01 로 한다.

네트워크 인터페이스 항목에서 네트워크는 위에서 생성한 VPC와 subnet을 선택한다.

[만들기]를 클릭한다.

인스턴스 그룹

load balancing에서 사용할 인스턴스 그룹을 생성한다.

[Compute Engine] - [인스턴스 그룹]을 클릭한 후 나타난 화면에서 [인스턴스 그룹만들기]를 클릭한다.

인스턴스 그룹명을 igr-aicallboot-admin-prd-01 로 한다.

"Intance template" 항목에서 위에서 생성한 인스턴스 템플릿을 선택한다.

"위치" 항목에서 "여러 영역"을 선택하고 asia-northeast3 리전과 asia-northeast3-a, asia-northeast3-b, asia-northeast3-c 영역을 선택한다. 

"자동확장" 항목의 "자동확장모드"를 사용:그룹에 인스턴스 추가 및 삭제를 선택한다. 자동확장할 인스턴스의 최소/최대 개수를 입력한다.

[만들기]를 클릭한다.

load balancing

GCP 콘솔의 좌측 3단메뉴를 클릭한 다음 네트워크 서비스를 클릭한 후 [부하 분산] 메뉴를 클릭한다.

아래 화면이 나타나면 [부하 분산기 만들기]를 클릭한다.

아래 화면에서 구성하려는 부하분산기의 용도에 따라 HTTP(S), TCP, UDP 부하 분산기의 [구성 시작]을 클릭한다. 본 문서에서는 HTTP(S) 부하 분산기와 TCP부하 분산기를 생성한다.

HTTP(S) 부하 분산 :

HTTP 부하분산을 위해 인터넷 연결 또는 내부 전용 선택 화면에서 "인터넷에서 VM 또는 서버리스 서비스로" 항목을 선택한다.

TCP 부하 분산 :

"부하 분산기 만들기" 화면에서 "백엔드 유형" 항목에서 "대상 풀 및 대상 인스턴스"를 선택한 후 [계속] 버튼을 클릭한다. 백엔드 유형을 인스턴스 그룹으로 지정하려면 "백엔드 서비스"를 선택한다.

부하 분산기 이름을 lbl-aicallbot-int-prd-01 로 입력하고 리전을 선택한다. 우측 화면에서 "기존 인스턴스 선택" 탭을 선택하고 기존에 생성한 VM 인스턴스를 선택한다. 상태 확인에서는 상태확인 생성을 클릭하여 이름과 port를 입력하여 생성한다. 체크할 port는 80으로 설정했다. 본 문서에서는 check-stat-prd-01 이름과 80포트로 생성했다.

화면에서 "프런트엔트 구성"을 클릭한 후, 우측에 나타난 화면에서 포트 부분에 로드 밸런서에 접속할 port 번호를 입력한다. 로드 밸런서는 port forwarding 이 없으므로 백엔드 서버에서 서비스하는 port와 동일하게 입력한다. 백엔드 서버에서 80 포트를 체크하도록 설정하였으므로 80 포트를 입력한다.

최종 "검토 및 완료(선택사항)"을 클릭하여 생성하고자 하는 데로 잘 구성이 되었는지 확인한 다음 [만들기] 버튼을 클릭하여 로드 밸런서를 생성한다.

로드 밸런서의 IP를 확인하여 브라우저에 http://load_balancer_IP/ 를 입력한다.

웹서버의 첫번째 화면을 각 서버별로 상이하게 작성하고 브라우저를 계속 reflesh 하면 웹 화면이 변경되는 것을 확인할 수 있다.

Filestore 생성

첨부 파일 등 파일 공유를 위해 Filestore를 생성한다. 기존 on-premise 에서의 NFS 파일시스템과 동일하다.

좌측 메뉴에서 [Filestore] - [인스턴스]를 클릭한 후 Cloud Filestore API 화면이 나타나면 [사용]을 클릭한다.

그 다음 화면에서 [인스턴스 만들기]를 클릭한다.

"인스턴스 만들기" 화면이 나타나면 인스턴스ID, 스토리지 유형, 용량할당, 저장위치, VPC 네트워크, 파일공유 이름 등을 설정한다.

본 문서에서는 인스턴스ID를 fsr-aicallbot-admin-prd-01 로 입력했고, 저장위치와 영역은 각각 asia-northeast3과 asia-northeast3-a를 선택했으며 VPC 네트워크는 위에서 생성한 VPC를 선택했다.

파일 공유이름으로는 nasdata_prd_01 로 입력했다. 모든 항목을 입력했으면 [만들기] 버튼을 클릭한다.

생성이 완료되면 VM이 이 파일 공유 항목을 마운할 수 있도록 NFS 마운트 point가 생성된다.

본 문서에서는 10.7.208.186:/nasdata_prd_01 로 생성되었다.

Filestore를 사용하는 VM 인스턴스에 접속하여 NFS 파일시스템을 마운트한다. filestore를 공유할 VM 인스턴스 모두에서 작업해준다.

- NFS 설치

sudo apt-get -y update && sudo apt-get install nfs-common

- Filestore 에 매핑할 로컬 디렉토리 생성

sudo mkdir -p mount-point-directory 에서 mount-point-directory는 로컬에 생성할 디렉토리이다. 본 문서에서는 아래와 같이 /filedir로 생성한다. 

sudo mkdir -p /filedir

mount명령어로 Filestore 인스턴스에서 파일 공유를 마운트한다.

위에서 생성된 NFS 마운트포인트명으로 파일시스템을 마운트한다.

sudo mount ip-address:/file-share mount-point-directory 에서 ip-address:/file-share 는 위에서 생성한 filestore의 NFS 마운트포인트이고 mount-point-directory는 VM에서 생성한 마운트포인트 디렉토리이다.

$ sudo mount 10.7.208.186:/nasdata_prd_01 /filedir

## mount한 NFS 확인
$ df -H | grep filedir
10.7.208.186:/nasdata_prd_01  1.1T     0  1.1T   0% /filedir

root 사용자가 아닌 다른 사용자가 쓸 수 있도록 chmod로 마운트 디렉토리에 대한 권한을 설정한다.

sudo chmod go+rw /filedir

VM 인스턴스가 재부팅되어도 자동으로 마운트하도록 /etc/fstab 파일에 등록한다.

10.7.208.186:/nasdata_prd_01 /filedir nfs defaults,_netdev 0 0

Comments(주석)

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;

/*! MySQL-specific code */

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

SELECT /*+ BKA(t1) */ FROM ... ;

MySQL Workbench 프로그램에서

Administration 항목의 일부 메뉴 클릭 시 아래와 같이 chcp 관련 RuntimeError 가 발생합니다.

에러 조치 방안으로 PATH에 C:\Windows\System32 경로를 추가하라는 메시지가 뜨는데 PATH에 경로를 추가해도 동일한 오류가 발생합니다.

유니코드를 지원하지 않는 프로그램용 언어

이 설정은 유니코드를 지원하지 않는 프로그램에서 텍스트를 표시할 때 사용되는 언어를 제어한다고 설명되어 있습니다. 이 언어를 기존 한국어(대한민국)에서 영어(미국)으로 변경한 후, MySQL Workbench가 설치된 컴퓨터를 재기동하면 위에서 발생했던 에러가 발생하지 않습니다.

변경 방법은 [윈도우키] - [설정(톱니바퀴 모양)] 을 클릭하면 아래와 같이 윈도우 설정 화면이 나타납니다. [시간 및 언어] 메뉴를 클릭합니다.

아래 화면이 나타나면 좌측의 [언어] 메뉴를 클릭한 후 우측 상단의 [관리 언어 설정] 메뉴를 클릭합니다.

[관리자 옵션] 탭에서 [시스템 로캘 변경] 버튼을 클릭한 후 한국어(대한민국)를 영어(미국)으로 변경합니다. 변경 후 컴퓨터를 재부팅하면 오류없이 MySQL Workbench 의 기능을 사용할 수 있습니다.

MySQL 설치 후 character set과 collation을 변경하는 방법에 대해 설명합니다.

설치환경

- Windows 10

- Mysql 5.7.37

MySQL 설치 시 캐릭터셋을 명시적으로 설정하지 않으면 MySQL 5.7 이하는 latin1, MySQL 8은 utf8mb4가 기본 캐릭터셋이 됩니다. 현재 character set 확인은 mysql 로 접속한 후 status 명령어나 show varaiables, 시스템 테이블 조회 등으로 알 수 있습니다.

현재 설정값 확인

1)  status 명령어

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.37, for Win64 (x86_64)

Connection id:          19
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         5.7.37-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    euckr
Conn.  characterset:    euckr
TCP port:               3306
Uptime:                 23 hours 35 min 40 sec

2) show variables

mysql> show global variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | euckr                                                   |
| character_set_connection | euckr                                                   |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | euckr                                                   |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

3) select system table

mysql> select * from performance_schema.global_variables where variable_name like 'character%';
+--------------------------+---------------------------------------------------------+
| VARIABLE_NAME            | VARIABLE_VALUE                                          |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | latin1                                                  |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | latin1                                                  |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

설정값 변경

1. 환경파일(my.ini) 수정

별도로 설정파일의 설치위치를 지정하거나 변경하지 않은 경우,

윈도우에서 my.ini 파일은  C:\ProramData\MySQL\MySQL Server 5.7 하위에 생성됩니다.

참고로, linux인 경우에는 RHEL/CentOS는  /etc/my.cnf 이고 Ubuntu 는 /etc/mysql/mysql.conf.d/mysqld.cnf 입니다.

이 파일을 열어서 아래 내용을 추가합니다.

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

이 설정은 서버 전체에 적용되며 신규로 생성한 데이터베이스와 해당 데이터베이스에 만드는 테이블의 기본값으로 적용됩니다.

utf8_general_ci

collation에서 ci는 case insensitive를 나타냅니다. 즉 영문의 대/소문자를 구분하지 않습니다. 대/소문자를 구분하여 정렬이 필요한 경우에는 utf8_bin 을 사용합니다. 예를 들어 order by 구문을 사용할 경우 collation을 ci 로 설정한 경우에는 대.소문자를 구분하지 않고 정렬합니다. a,A,B,b 라는 데이터가 있다면 ordering을 하게되면 대.소문자를 구분하지 않기 때문에 a,A,B,b 등으로 출력됩니다. 반면 utf8_bin을 적용하게 되면 a,b,A,B 식으로 정렬됩니다.

MySQL이 실행되고 있으면 재기동을 해줍니다.

MySQL에 접속하여 위의 3가지 방법 중 하나로 variable을 조회하여 정상적으로 수정이 되었는지 확인합니다.

추가로, 기존에 생성된 데이터베이스는 변경되지 않으므로 characterset과 collcation을 변경해줘야 합니다.

아래와 같은 명령어를 수행하면 기존에 생성했던 데이터베이스의 characterset과 collcation 이 변경됩니다. 아래 dbname 대신에에 변경하고 하는 DB를 입력하면 됩니다.

mysql> ALTER SCHEMA `dbname` DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_bin ;