IT & Tech

Error Code: 1205. Lock wait timeout exceeded 에러발생시

구글링해보니 보통 다음 2가지 해결책 제시됨

1. lock timeout 시간을 늘려주라

workbench 기본 read time-out은 30초였는데 늘여도 소용없었음

1. LOCK 테이블에 lock이 존재하는지 확인해보라

use information_schema;
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_WAITS;
select * from information_schema.INNODB_TRX; -- 오랜 시간 동안 Commit 되지 않은 transaction
show full processlist; -- 로 어떤 IP에서 접속했는지 등 정보확인
kill 423911;-- 의심(?)되는 것을 킬

참고) https://www.popit.kr/mysql-lock-%EC%83%81%ED%99%A9-%EB%AC%B8%EC%A0%9C-%ED%95%B4%EA%B2%B0/

? shell로 접근하려면 10060port사용해야 하는지?

Can't connect to MySQL server on 'rds-an2-richcdp-qa2-mysql.cjbyfjmv7vdu.ap-northeast-2.rds.amazonaws.com' (10060) when trying to connect

EXPORT

$mysqldump -uroot -p wordpess > dump160724.sql

(workbench통해 실행할 때 명령어) mysqldump.exe --defaults-file="c:\users\user\appdata\local\temp\tmpr46kpq.cnf" --user=cdp_mgr --host=rds-an2-richcdp-qa2-mysql.cjbyfjmv7vdu.ap-northeast-2.rds.amazonaws.com --protocol=tcp --port=3310 --default-character-set=utf8 --skip-triggers --skip-column-statistics "richcdp_kic_qa2" "tb_rc_user"

IMPORT

$mysqladmin -uroot -p create wordpress

$mysql -uroot -p wordpress < dump160724.sql

• meta lock: online DDL지원하면서 도입된 개념(mySQL 5.X대 버젼부터 추가된듯), transaction 잡고 select 하면 DDL에 hang걸림, DML은 괜찮음 
  (즉, autocommit=false 설정하고 작업후 commit 안하고 그냥 끝내면 안됨, select만 해도 autocommit=true설정하거나 작업 후 반드시 commit해야함)
  svc package에는 view재생성 등 DDL이 많으므로 특히 주의해야함
• mySQL에는 배열, synonym이 없음 - sdp-ibs에서는 배열로 임시테이블 만드는 대신 string으로 임시테이블 만드는 방법 사용함 
• mySQL에는 sequence 없음 - 대신 auto increment라는 함수??사용가능 (default key 만들어주는 용도??) or sequence대신 테이블과 function을 사용할 수도 있음
  (sdp에서는 기존 sequence를 거의 테이블로 대체했음, hesdp_seq에 sequence로 사용하기 위한 테이블들 모여있음)
• mySQL은 oraclel과 hint 사용법이 다르며 oracle hint /*+ ...*/은 mySQL에서 주석으로 무시된다
• package 내 function들을 모두 procedure로 변경한 이유 - mySQL의 function은 out parameter를 사용할 수 없어서
• batch job - event job으로 등록가능 (hesdp_com.pbf_batch_history 테이블에 이력남김, 등록은 DBA통해서)
• HESDP_COM.FN_BLANKTONULL('$(GEN_USR_NO)') 사용하는 이유: 오라클은 ''을 null로 바꾸주는데 mySQL은 안바꿔줘서 function 만들어 사용함
• mySQL은 oracle과 달리 object 변경이 있을 시, 관련 procedure나 fucnciton 재컴파일 안해줘도 됨
• oracle은 select시 from절이 필수인 반면 mySQL은 필수아님 (from dual 안써도 됨)
• 시간관련 함수들은 상당히 다름 (위 표 참고)
• mySQL은 oracle과 달리 대소문자를 구분하는 경우가 많다. (e.g. 접속 user, 시간포맷 오라클은 YYYYMMDD=yyyymmdd이지만 mySQL은 %Y!=%y)
• mySQL은 oracle과 달리 inner view를 사용할 때 꼭 table alias를 명시해줘야 함, 안그러면 에러남
• SDP 운영 KIC, AIC, EIC, Q1 KIC mySQL DB만 master와 slave로 이중화 되어 있고 나머지는 하나임
  이중화된 DB의 경우, select는 slave에서 하고 update, delete, 혹은 update, delete와 select가 함께 일어나는 operation의 경우 master에서 작업한다.
  (성능때문에 이중화한건데, 그렇게 안하고 master만 계속 이용하면 이중화한 의미가 없음)
  master에서 변경된 data는 slave로 1초 이내에 복사된다.

동일한 테이블의 다른 컬럼 참조하여 업데이트하고싶을 때

• update tb_ic_mso_prdt_itm_bas set prdt_ver = (select prdt_ver from tb_ic_mso_prdt_itm_bas where..) 이렇게 하면 다음 에러발생
  • Error Code: 1093. You can't specify target table 'tb_ic_mso_prdt_itm_bas' for update in FROM clause
• 그럴경우 아래와 같이 T로 한번 더 감싸주면 됨
  • update tb_ic_mso_prdt_itm_bas set prdt_ver = (select T.* from (select prdt_ver from tb_ic_mso_prdt_itm_bas where..) T)
• 여기서 T를 가상/임시 테이블이라 하던가? 암튼 oracle에서는 아래와 같이 with T로 사용가능
  • with T as(select sysdate from dual) select * from T
• example
  • 에러

    update tb_ic_contents_cntry_code_map A
    set major_logo_url = ( select major_logo_url from tb_ic_contents_cntry_code_map where ic_contents_set_id='com.lge.crawler.xml.tms.TmsEpgCrawler' and cntry_code='US') 
    where ic_contents_set_id='com.lge.crawler.xml.gn.epg.GnEpgCrawlerMX' and cntry_code='MX'

  • 정상

    update tb_ic_contents_cntry_code_map A
    set major_logo_url = ( select * from
                    ( select major_logo_url from tb_ic_contents_cntry_code_map where ic_contents_set_id='com.lge.crawler.xml.tms.TmsEpgCrawler' and cntry_code='US') temp
                  )
    where ic_contents_set_id='com.lge.crawler.xml.gn.epg.GnEpgCrawlerMX' and cntry_code='MX'

이런식으로 order by 할 때 순서를 각각 정할 수도 있다

SELECT * FROM tb_ic_contents_set_job_gr
ORDER BY CASE ic_contents_set_id
WHEN 'com.lge.meta.crawler.newepg.NewEpgCrawler' THEN 1
WHEN 'com.lge.meta.TVMao.TVMaoCrawler' THEN 2 
ELSE 3 END

배열대신 string으로 임시테이블 생성하는 방법

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, '*', temp.n(index역할)), '*', -1) CHAN_CODE
FROM (select '12345*23451*12345' as col) t CROSS JOIN 
(SELECT a.N + b.N * 10 + 1 as n FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b ORDER BY n
) temp WHERE temp.n <= 1 + (LENGTH(t.col) - LENGTH(REPLACE(t.col, '*', '')));

배열대신 string사용하여 index로 접근하는 방법

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ',', 1(index, 1부터시작)), ',', -1) CHAN_CODE
FROM (select '12345,23451,12345' as col) t;

How can I simulate an array variable in MySQL?

UPDATE HESDP_IBS.TB_IC_CHAN_BAS A
SET LG_CHAN_GENRE = (
                                            SELECT REPLACE(LG_GENRE_CODE, ';', '')
                                            FROM (
                                                             SELECT chan_code
                                                                           , LG_GENRE_CODE
                                                                           , IF(@PART = AA.CHAN_CODE, @RNUM := @RNUM + 1, @RNUM := 1) AS score
                                                                                , @PART := AA.CHAN_CODE AS PART
                                                                             FROM (
                                                                                           SELECT SVC.CHAN_CODE
, SVC.CHAN_STD_NAME
, SVC.LG_GENRE_CODE
, COUNT(*) AS GENRE_COUNT
FROM HESDP_IBS.TB_IC_N_SVC_BAS SVC
WHERE 1=1
AND SVC.IC_CONTENTS_SET_ID = '$(contentsSetId)'
GROUP BY SVC.CHAN_STD_NAME, SVC.CHAN_CODE, SVC.LG_GENRE_CODE
ORDER BY CHAN_CODE, GENRE_COUNT
) AA,
(SELECT @PART := '', @RNUM := 0) CC
ORDER BY AA.CHAN_CODE, AA.GENRE_COUNT DESC
) T
WHERE T.score=1
AND A.chan_code=T.chan_code
AND A.IC_CONTENTS_SET_ID = '$(contentsSetId)'
)
WHERE A.IC_CONTENTS_SET_ID = '$(contentsSetId)'
AND (A.LG_CHAN_GENRE = '999' OR A.LG_CHAN_GENRE IS NULL)



DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_rows(미리생성한변수) =TRUE ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err =TRUE ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @sqlerrno = MYSQL_ERRNO, @sqlerrmsg = MESSAGE_TEXT;
SET O_error_message = (CONCAT('PACKAGE_ERROR',' ',
Ifnull(L_function, '') ,' ',
IFNULL(@sqlstate, '') ,' ',
IFNULL(@sqlerrno, ''), ' ',
IFNULL(@sqlerrmsg, '')));
ROLLBACK;

Spring-boot 실행후 http://localhost:8080/h2-console 로 접속가능

H2 DB는 웹브라우저의 local storage같은 걸 사용하는 듯 한번 저장된 데이터가 계속 캐쉬됨, DB URL, user가 바껴도 계속 캐쉬됨

https://developerhive.tistory.com/34

use mysql; or use information_schema; 실행후에는 매번 mysql./information_schema.안붙여도됨

• user account
  
  • select * from mysql.user;
    • 여기에 insert하는 것과 create user하는 것이 동일한 효과인듯 (create user하면 insert되어 있음)
• 권한
  • select * from mysql.db; schema/host/user별 권한확인가능
    • 여기에 insert하는 것과 grant () on () to () 하는 것과는 다른듯ㅠ
    • grant ()가 막혀있고 실행되지 않아 insert해보니 insert는 되지만 실제 권한은 못받음
    • show grants (for current user) 명령어로 현재 user에게 허용된  grant문 전체를 볼수는 있음
  • select * from information_schema.USER_PRIVILEGES;
    • 위의 mysql.db와 동일한데 각 권한을 row별로 나열로해 놓은듯?
• Table
  • select * from infomation_schema.tables -> information_schema의 테이블뿐 아니라 모든 테이블 확인가능
  • show tables; -> 현재 schema에 있는 테이블 목록만 보여줌
• Event
  • event scheduler 활성화
    • 활성화 확인: show variables like 'event%';  -> ON이면 활성화된것
    • 활성화시키는법: set global event_scheduler=ON; / set @@global.event_scheduler=ON;
  • read
    • 생성된 event확인: select * from information_schema.EVENTS; / show EVENTS;
      • e.g) select * from information_schema.events where event_name='EV_SP_IB_CONF_FILE'
    • 등록된 event내용확인: show create event '이벤트명'; -> ?안됨
  • update
    • alter event '이벤트명' on schedule every 1 month starts '2014-05-27 01:00:00';
  • delete
    • drop event '이벤트명';

•  mySQL

select str_to_date(A.start_time,'%H%i') utc, date_add(str_to_date(A.start_time,'%H%i'),INTERVAL 9 HOUR) kr, A.svc_act_flag, A.* from tb_ic_contents_set_job_gr A order by 5;
select serial_no, contents_set_id,job_type,gen_date,complt_date,schdr_prcs_stat flag,TIMEDIFF(COMPLT_DATE, GEN_DATE) time, 
	CONVERT_TZ(gen_date,'+00:00','+09:00') kr_start, CONVERT_TZ(complt_date,'+00:00','+09:00') kr_end from TB_IC_DMON_SCHD_HIST A 
	where contents_set_id like 'xumo%'
	ORDER BY 4 DESC;
select * from TB_IC_ERROR_HIST where error_code like 'DM%' order by error_occur_date desc;
select DATE(strt_time),IC_CONTENTS_SET_ID,COUNT(*) from tb_ic_n_svc_bas GROUP BY DATE(strt_time),IC_CONTENTS_SET_ID;
select strt_date,IC_CONTENTS_SET_ID,COUNT(*) from vi_ic_n_brdcst_schd GROUP BY strt_date,IC_CONTENTS_SET_ID;

select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no, use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
	from TB_IC_N_CRL_BRDCST_PGM_HIST ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
	from TB_IC_N_CRL_BRDCST_SCHD_HIST A ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
	from TB_IC_N_CRL_PERSON_DETAIL_HIST A ORDER BY 4 DESC;
select * from tb_ic_n_crl_brdcst_pgm where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_brdcst_schd where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_person_detail where crawling_hist_map_seq='63731';
select * from tb_ic_n_svc_bas where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_pgm_mir where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_schd_org where IC_CONTENTS_SET_ID='';
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_PGM group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_schd group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_PERSON_DETAIL group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;

-- ipchannels

select A.ip_url, A.* from tb_ic_mso_bas A where mso_code like '30%'; 
select mso_code,mso_prdt_itm_code,prdt_ver,chan_ver,call_ver,chan_logo_ver,use_flag,interact_url, A.* from tb_ic_mso_prdt_itm_bas A where dvc_src_idx='7'; 
select A.mso_prdt_itm_code,A.use_flag, count(*) from tb_ic_mso_chan_map A, tb_ic_chan_bas B where A.mso_prdt_itm_code like '300%' and A.mso_prdt_itm_code!='300'
	and A.ic_contents_set_id=B.ic_contents_set_id and A.chan_code=B.chan_code and B.use_flag='Y' group by A.mso_prdt_itm_code,A.use_flag;
select chan_code from tb_ic_mso_chan_map where mso_prdt_itm_code='3000' order by chan_code;
select conf_code, stat_code, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code;
select conf_code, stat_code, chan_no, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code, chan_no;
select chan_code from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.CONF_NO=B.conf_no and stat_code='IB172' and conf_code='3000' order by chan_code;
select cntry_code,svc_set_cntry_id,use_flag,count(*) from tb_ic_chan_svc_cntry group by cntry_code,svc_set_cntry_id,use_flag; 
-- select IC_CONTENTS_SET_ID,src_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,src_id,use_flag;
-- select IC_CONTENTS_SET_ID,chan_gr_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,chan_gr_id,use_flag;
-- select IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag, count(*) from tb_ic_chan_bas where phys_brdcst_type='IP' group by IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag;
select IC_CONTENTS_SET_ID,cp_id,use_flag, count(*) from tb_ic_dmost_chan_bas group by IC_CONTENTS_SET_ID,cp_id,use_flag;

select str_to_date(A.start_time,'%H%i') utc, date_add(str_to_date(A.start_time,'%H%i'),INTERVAL 9 HOUR) kr, A.svc_act_flag, A.* from tb_ic_contents_set_job_gr A order by 5;
select serial_no, contents_set_id,job_type,gen_date,complt_date,schdr_prcs_stat flag,TIMEDIFF(COMPLT_DATE, GEN_DATE) time, 
    CONVERT_TZ(gen_date,'+00:00','+09:00') kr_start, CONVERT_TZ(complt_date,'+00:00','+09:00') kr_end from TB_IC_DMON_SCHD_HIST A 
    where contents_set_id like 'xumo%'
    ORDER BY 4 DESC;
select * from TB_IC_ERROR_HIST where error_code like 'DM%' order by error_occur_date desc;
select DATE(strt_time),IC_CONTENTS_SET_ID,COUNT(*) from tb_ic_n_svc_bas GROUP BY DATE(strt_time),IC_CONTENTS_SET_ID;
select strt_date,IC_CONTENTS_SET_ID,COUNT(*) from vi_ic_n_brdcst_schd GROUP BY strt_date,IC_CONTENTS_SET_ID;

select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no, use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
    from TB_IC_N_CRL_BRDCST_PGM_HIST ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
    from TB_IC_N_CRL_BRDCST_SCHD_HIST A ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
    from TB_IC_N_CRL_PERSON_DETAIL_HIST A ORDER BY 4 DESC;
select * from tb_ic_n_crl_brdcst_pgm where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_brdcst_schd where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_person_detail where crawling_hist_map_seq='63731';
select * from tb_ic_n_svc_bas where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_pgm_mir where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_schd_org where IC_CONTENTS_SET_ID='';
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_PGM group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_schd group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_PERSON_DETAIL group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;

-- ipchannels
select A.ip_url, A.* from tb_ic_mso_bas A where mso_code like '30%'; 
select mso_code,mso_prdt_itm_code,prdt_ver,chan_ver,call_ver,chan_logo_ver,use_flag,interact_url, A.* from tb_ic_mso_prdt_itm_bas A where dvc_src_idx='7'; 
select A.mso_prdt_itm_code,A.use_flag, count(*) from tb_ic_mso_chan_map A, tb_ic_chan_bas B where A.mso_prdt_itm_code like '300%' and A.mso_prdt_itm_code!='300'
    and A.ic_contents_set_id=B.ic_contents_set_id and A.chan_code=B.chan_code and B.use_flag='Y' group by A.mso_prdt_itm_code,A.use_flag;
select * from tb_ic_mso_chan_map where mso_prdt_itm_code like '3000%';
select conf_code, stat_code, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code;
select conf_code, stat_code, chan_no, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code, chan_no;
select A.stat_code, B.conf_code, A.chan_no, A.chan_code, A.chan_name 
    from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.CONF_NO=B.conf_no and stat_code='IB171' and A.use_flag='Y' and B.use_flag='Y' and conf_code like '3000%';
select cntry_code,svc_set_cntry_id,use_flag,count(*) from tb_ic_chan_svc_cntry group by cntry_code,svc_set_cntry_id,use_flag; 
-- select IC_CONTENTS_SET_ID,src_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,src_id,use_flag;
-- select IC_CONTENTS_SET_ID,chan_gr_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,chan_gr_id,use_flag;
-- select IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag, count(*) from tb_ic_chan_bas where phys_brdcst_type='IP' group by IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag;
select IC_CONTENTS_SET_ID,cp_id,use_flag, count(*) from tb_ic_dmost_chan_bas group by IC_CONTENTS_SET_ID,cp_id,use_flag;

• oracle

SELECT A.IC_CONTENTS_SET_ID,A.CRAWLING_SEQ seq,A.DB_DAEMON_JOB_TYPE type,gen_usr_no NO,TO_CHAR(CRT_DATE, 'YYYY/MM/DD hh24:mi') crt, TO_CHAR(LAST_CHG_DATE, 'YYYY/MM/DD hh24:mi') last_Chg, A.use_flag, 
TO_CHAR(CRT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_crt, TO_CHAR(LAST_CHG_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_last
FROM hesdp_app.TB_IC_N_CRL_BRDCST_PGM_HIST A ORDER BY crt_date DESC;
SELECT A.IC_CONTENTS_SET_ID,A.CRAWLING_SEQ seq,A.DB_DAEMON_JOB_TYPE type,gen_usr_no NO,TO_CHAR(CRT_DATE, 'YYYY/MM/DD hh24:mi') crt, TO_CHAR(LAST_CHG_DATE, 'YYYY/MM/DD hh24:mi') last_Chg, A.use_flag, 
TO_CHAR(CRT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_crt, TO_CHAR(LAST_CHG_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_last
FROM hesdp_app.TB_IC_N_CRL_BRDCST_SCHD_HIST A ORDER BY crt_date DESC;
SELECT A.IC_CONTENTS_SET_ID,A.CRAWLING_SEQ seq,A.DB_DAEMON_JOB_TYPE type,gen_usr_no NO,TO_CHAR(CRT_DATE, 'YYYY/MM/DD hh24:mi') crt, TO_CHAR(LAST_CHG_DATE, 'YYYY/MM/DD hh24:mi') last_Chg, A.use_flag, 
TO_CHAR(CRT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_crt, TO_CHAR(LAST_CHG_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_last
FROM hesdp_app.TB_IC_N_CRL_PERSON_DETAIL_HIST A ORDER BY crt_date DESC; 

SELECT SERIAL_NO seq,CONTENTS_SET_ID,TO_CHAR(GEN_DATE, 'YYYY/MM/DD hh24:mi') GEN, TO_CHAR(COMPLT_DATE, 'YYYY/MM/DD hh24:mi') COMPLT, SCHDR_PRCS_STAT F,JOB_TYPE,
TO_CHAR(GEN_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_gen, TO_CHAR(COMPLT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_complt, ROUND((COMPLT_DATE - GEN_DATE) * 24 * 60, 0) time
FROM hesdp_app.TB_IC_DMON_SCHD_HIST A ORDER BY 1 DESC; 

SELECT TO_CHAR(TO_DATE(A.start_time,'hh24mi')+9/24,'hh24:mi') KST, 'UTC'||TO_CHAR(TO_DATE(A.start_time,'hh24mi'),'hh24:mi') GMT,A.INTVL_TIME intvl , A.SVC_ACT_FLAG F, A.* 
FROM hesdp_app.tb_ic_contents_set_job_gr A ORDER BY 6,1; 

SELECT IC_CONTENTS_SET_ID, TO_CHAR(STRT_TIME,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.TB_IC_N_SVC_BAS GROUP BY IC_CONTENTS_SET_ID, TO_CHAR(STRT_TIME,'YYYY-MM-DD') ORDER BY 1,2; 
SELECT IC_CONTENTS_SET_ID, TO_CHAR(STRT_DATE,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.VI_IC_N_BRDCST_SCHD WHERE use_flag='Y' GROUP BY IC_CONTENTS_SET_ID, TO_CHAR(STRT_DATE,'YYYY-MM-DD') ORDER BY 1,2; 
SELECT chan_code, COUNT(*) FROM TB_IC_N_SVC_BAS WHERE hesdp_app.chan_code LIKE '9999%' GROUP BY chan_code; 
SELECT chan_code, COUNT(*) FROM VI_IC_N_BRDCST_SCHD WHERE hesdp_app.chan_code LIKE '9999%' GROUP BY chan_code; 
SELECT TO_CHAR(STRT_TIME,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.TB_IC_N_SVC_BAS WHERE chan_code LIKE '9999%' GROUP BY TO_CHAR(STRT_TIME,'YYYY-MM-DD');
SELECT TO_CHAR(STRT_DATE,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.VI_IC_N_BRDCST_SCHD WHERE chan_code LIKE '9999%' GROUP BY TO_CHAR(STRT_DATE,'YYYY-MM-DD');

SELECT * FROM hesdp_app.tb_ic_error_hist WHERE ERROR_CODE LIKE 'DM%' ORDER BY error_occur_date DESC;
SELECT * FROM hesdp_app.tb_ic_last_upd_time_bas;

• mySQL site에서 download메뉴로 가면 상용SW 설치하는 곳나옴, 돈안내고 개발용으로 쓰려면 developer zone으로 간 후 다운받아야함 (2020.01.23)
 
• mysql: client 개념인듯
• mysql -u [계정] -p [데이터베이스] - 여기에서 기본으로 들어갈꺼면[데이터베이스]는 생략가능한듯
• mysql --defaults-file="C:\MYSQL\my.ini" -u root -p
• -u 사용할 계정, 예) root
• -p 패스워드 물어봄
• --defaults-file
• "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" "-uroot" "-p" "--default-character-set=utf8mb4"
• mysqld: mysql daemon을 의미
• 그냥 mysqld.exe 실행하니까 다음과 같은 에러발생

• Can't change dir to 'D:\Program Files\MySQL\MySQL Server 5.7\data\'     (Errcode: 2 - No such file or directory)
  2015-11-17T08:30:18.822962Z 0 [Warning] TIMESTAMP with implicit DEFAULT     value is deprecated. Please use --explicit_defaults_for_timestamp server option     (see documentation for more details).
  2015-11-17T08:30:18.822962Z 0 [Warning] Insecure configuration for --secure-    file    -priv: Current value does not restrict location of generated files.     Consider setting it to a valid, non-empty path.
  2015-11-17T08:30:18.822962Z 0 [Note] mysqld (mysqld 5.7.9) starting as     process 1108 ...
  2015-11-17T08:30:18.838586Z 0 [Warning] Can't create test file D:\Program     Files\MySQL\MySQL Server 5.7\data\DESKTOP-RNBR3E8.lower-test
  2015-11-17T08:30:18.838586Z 0 [Warning] Can't create test file D:\Program     Files\MySQL\MySQL Server 5.7\data\DESKTOP-RNBR3E8.lower-test
  2015-11-17T08:30:18.838586Z 0 [ERROR] failed to set datadir to D:\Program     Files\MySQL\MySQL Server 5.7\data\
  2015-11-17T08:30:18.838586Z 0 [ERROR] Aborting

  (참고 https://stackoverflow.com/questions/33752407/mysqld-cant-change-dir-to-data-server-doesnt-start)

• {mysql_home_dir}/data가 없어서 파일을 못써서 에러가 났다는 의미인듯 해서 data폴더 생성후 재실행하니 data폴더안에 에러로그가 쌓이고 역시 정상실행은 안됨, 에러로그안에는 data dictionary를 초기화못했다는 의미의 에러로그가 남아있었음
• 뭔가 환경설정 세팅에 오류가 있는 것 같음
• 결국 mysqld.exe --defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini 로 하니 해결됨
• my.ini가 환경설정 관련 내용을 담고 있음
• root PW 잊어버려서 mysqld.exe --defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini --skip-grant로 실행했으나 이렇게는 daemon이 떴다가 금방 꺼짐 뭔가 설정이 안맞는듯

• mySQL 에러코드
• ERROR 1045 (28000): Access denied for user
• password 틀렸거나 password사용하지 않은 접근했을 때
• 해결법: https://zetawiki.com/wiki/MySQL_ERROR_1045_%EC%A0%91%EA%B7%BC_%EA%B1%B0%EB%B6%80#cite_note-1

• mysql command option 아래에서 참고할 것
• https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_datadir
• root pw 변경방법
• https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html
• 환경설정인 cnf(리눅스), ini(윈도우인듯) 파일 사용방법 관련
• https://dev.mysql.com/doc/refman/8.0/en/option-files.html
• 윈도우에서는 MySQL Installation Wizard를 사용해서 설치했다 면 --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini" 옵션을 추가해줘야함

-------------------------------------

2019/03/06 mysqld 실행하니 다음 에러 발생하면서 또 안됨 ㅠㅠ

 innodb_system data file 'ibdata1' must be writable 

다음과 같이 이것저것 조치하니까 됨

• C:\ProgramData\MySQL\MySQL Server 8.0\Data\irdata1 파일의 쓰기권한이 없던 user에게 권한부여
• C:\ProgramData\MySQL\MySQL Server 8.0를 D:\로 이동해보고 다시 원복함

-------------------------------------

workbench는 아무거나 실행되지 않고 새로 설치한 것으로만 실행됨

기존 workbench는 다음 에러메세지뜨며 실행안됨

Internal Error Exception caught while processing action from home screen: bad conversion