mysql Prepared_stmt_count 줄이기

# mariadb(또는mysql) 에서 stmt 가 정상종료되지 않고 있다.
# https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Prepared_stmt_count
# Com_stmt_prepare, Com_stmt_close, Com_stmt_fetch 는
# 각각 PREPARE, EXECUTE, DEALLOCATE PREPARE 를 실행한 카운트이다.
# Prepared_stmt_count 는 현재 prepared statement 사용중인 개수이다.
show global status like '%stmt%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Binlog_stmt_cache_disk_use | 0     |
| Binlog_stmt_cache_use      | 0     |
| Com_stmt_execute           | 18116 |
| Com_stmt_close             | 12216 |
| Com_stmt_fetch             | 0     |
| Com_stmt_prepare           | 33749 |
| Com_stmt_reset             | 0     |
| Com_stmt_send_long_data    | 0     |
| Com_stmt_reprepare         | 0     |
| Prepared_stmt_count        | 9991  |
+----------------------------+-------+

# 테스트를 위해서 db 를 재시작해서 variable 를 초기화 한다.
sudo systemctl restart mariadb

# 로컬에서 root 로 접속(root 패스워드 설정은 mariadb 설치 포스팅 참고)
mysql -u root -pysoftman

# 다음과 같이 prepare stmt1, stmt2, ... stmtN 로 이름을 달리 계속 생성할 수 있다.
# 같은 stmt 이름을 사용하게 이전 stmt 를 닫고(Com_stmt_close 증가) 새로 만든다.
# Com_stmt_prepare, Prepared_stmt_count 증가
prepare stmt1 from 'select * from mysql.help_category where name = ?';

# stmt1 에 name 으로 전달된 파라미터 설정
set @help_name = 'functions';

# prepare stmt1 를 실행한다.
# Com_stmt_execute 증가
execute stmt1 using @help_name;

# prepare stmt1 를 종료한다.
# Com_stmt_close 증가, Prepared_stmt_count 감소
deallocate prepare stmt1;

# Prepared_stmt_count 줄이기
# flush 를 사용해봤지만 영향이 없다.
# flush 열려 있는 테이블들을 닫는다.(캐시된 쿼리를 삭제하는데 사용할 수는 있다.)
# https://dev.mysql.com/doc/refman/5.5/en/flush.html#flush-tables
flush tables;

# 현재 db 커넥션을 종료하거나 사용중인 프로세스를 종료시켜도
# 해당 프로세스와 연결된 Prepared_stmt_count 줄어든다.
# 다음 쿼리로 KILL id; 들로 실행할 쿼리를 만든다.
select concat('KILL ',id,';') from information_schema.processlist where db='ysoftman';

# 해당 프로세스 종료
KILL 1;
KILL 2;
KILL 3;

# 참고
# max_prepared_stmt_count 설정 값은 파악해본다.
show variables like '%stmt%';

# max_prepared_stmt_count 를 2로 줄여준다.
set global max_prepared_stmt_count=2;

# Prepared_stmt_count 는 max_prepared_stmt_count 변수로 설정된 개수를
# 넘어가면 더이상 stmt 를 생성할수 없다는 에러가 발생한다.
prepare stmt3 from 'select * from mysql.help_category where name = ?';
ERROR 1461 (42000): Can't create more than max_prepared_stmt_count statements (current value: 2)

mysql 테이블 컬럼명이 예약어와 같을때

# mysql(또는 mariadb)의 테이블의 컬럼(필드)명이 mysql 에서 사용하는 예약어라면
# sql 쿼리를 실행할때 syntax 에러를 뱉어낸다.

# 예시
# 다음처럼 db 테이블을 만들었다면
create database mydb;
use mydb;
MariaDB [mydb]> desc fruits;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| type  | int(11)      | YES  |     | NULL    |       |
| order | varchar(255) | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

# 데이터를 추가할때 컬럼명을 명시해 insert 하는 경우
insert into fruits(type, order, name) values(1,'1','lemon');

# 다음과 같은 에러를 발생한다.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order, name) values(1,'1','lemon')' at line 1

# order mysql 명령어라 `` (grave, 따옴표 아님)로 다음과 같이 명시해야 한다.
insert into fruits(type, `order`, name) values(1,'1','lemon');

# 다음과 같이 모든 컬럼을 일괄 `` 적용해주는것이 좋다.
insert into fruits(`type`, `order`, `name`) values(1,'1','lemon');

# 사실 테이블을 생성할때도 필드명은 ``로 감싸준다.
create table fruits (
    `type` int,
    `order` varchar(255),
    `name` varchar(255)
);

mariadb 설치

# mysql 이 오라클로 넘어가고 나서 부터 무료로 사용하기 힘들어졌다.(비영리 목적은 가능)
# 그래서 mysql 과 호환되는 mariadb(mysql 만들 사람들이 나와서 만든 db) 를 많이 사용하는 추세다.

# centos 에서 설치
sudo yum install mariadb-server

# ubuntu 에서 설치
sudo apt-get install mariadb-server

# 버전 확인
mysql -V

# 설정
vi /etc/my.cnf  또는 /etc/mysql/my.cnf
...
[mysqld]
port = 13306
...
# db 실행
sudo systemctl start mariadb

# db 재시작
sudo systemctl restart mariadb

# db 상태확인
sudo systemctl status mariadb

# db 중지
sudo systemctl stop mariadb

# root 암호 설정
mysql_secure_installation
...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y

New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!

# db 접속
mysql -u root -p
Enter password:

# 다음과 같이 접속 허용이 되지 않는다면 sudo 로 실행하자.
sudo mysql -u root -p

# 접속후 db 확인
MariaDB [(none)]> show databases;

# mysql db 의 user 테이블에서 사용자 정보 확인
MariaDB [mysql]> select host, user from mysql.user;

# 원격에서 접속시 다음과 같은 에러가 발생하면
# 1.1 대역 IP 는 허용하도록 설정
# Host '1.1.1.1' is not allowed to connect to this MySQL server
insert into mysql.user (host,user,authentication_string,ssl_cipher, x509_issuer, x509_subject) values ('1.1.%','root',password('xxxxx'),'','','');
grant all privileges on *.* to 'root'@'172.26.%';
flush privileges;

MySQL 시간관련 함수

# mysql 에서 현재로 부터 1시간뒤, 하루뒤... 등을 계산하려면 date_add() 함수를 사용한다.

# 현재로 부터 1분 뒤
select date_add(now(), interval 1 minute);
# 또는 함수 없이 사용할 수도 있다.

select now() + interval 1 minute;

select now() + interval 1 hour;
select now() + interval 1 day;
select now() + interval 1 month;
select now() + interval 1 year;

# 현재 유닉스타임
select unix_timestamp();

# date -> 유닉스타임
select unix_timestamp('2000-12-31 12:00:00');

# 유닉스 타임 -> date 로 변환
select from_unixtime(unix_timestamp());

# 기타... date 스트링에서 해당 값만 파악하기

select year('2000-12-31 12:00:00');

select month('2000-12-31 12:00:00');

select day('2000-12-31 12:00:00');

select hour('2000-12-31 12:00:00');

select minute('2000-12-31 12:00:00');

select second('2000-12-31 12:00:00');

select monthname('2000-12-31 12:00:00');

select dayname('2000-12-31 12:00:00');

select dayofyear('2000-12-31 12:00:00');

select dayofmonth('2000-12-31 12:00:00');

select dayofweek('2000-12-31 12:00:00');

# 오늘 날짜만 파악하기
select curdate();

# 현재시간
select current_timestamp;