자바블로그

MySql 서버8 이후 버전의 경우 기본 time_zone관련 설정이 되어 있지 않아 기존에 많이 사용하던 url형식으로 접속 하는 경우
time_zone 관련 에러가 발생 됩니다. 

mysql에 접속 해서 아래의 명령어를 실행 하면

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;

와 같은 결과가 나오는걸 확인 할 수 있습니다.

global설정은 system설정을 따르게 되어 있고 session(접속시)설정도 system설정을 따르게 되어 있는데

system은 아무 설정도 되어 있지 않습니다.(���ѹα� ǥ�ؽ�) 이 경우 에러가 발생되고

jdbc url에 serverTimeZone설정을 추가 해주면 정상적으로 접속이 가능 하게 됩니다.

접속 정보에 아래와 같이 serverTimeZone을 명시 해주면 됩니다.(serverTimezone=UTC, serverTimezone=Asia/Seoul)

jdbc:mysql://localhost:3306/test_db?characterEncoding=UTF-8&serverTimezone=UTC

또는 mysql서버에 system 및 session접속시 사용할 time_zone정보를 my.ini파일에 아래와 같이 명시 할 수 도 있습니다.

default-time-zone='+9:00'

my.ini 설정은 mysql서버를 재기동 해야 적용이 됩니다.(+9:00 은 서울 지역의 시간대 입니다. GMT+9시간)

프로시저 실행 중 아래와 같은 오류가 발생되는 경우 

Error Code : 1329 No data - zero rows fetched, selected, or processed

데이터를 찾지 못해서 발생되는 에러로 해당 하는 상황에 맞게 아래와 같이 작업 해줘야 합니다.

DELIMITER $$

DROP PROCEDURE IF EXISTS `PROC_TEST_WORK`$$

CREATE PROCEDURE `PROC_TEST_WORK`()
BEGIN
 	DECLARE done INT DEFAULT 0;	
	DECLARE v_column1 VARCHAR(12);
	DECLARE v_column2 VARCHAR(12);
	DECLARE v_column3 VARCHAR(10);
	
 	
	DECLARE c1 CURSOR FOR
		SELECT 	AA.COLUMN1,
				AA.COLUMN2,
				AA.COLUMN3				
			  FROM TEST_TABLE AA
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	OPEN c1;

	data_loop: LOOP

		FETCH c1 INTO v_column1, v_column2, v_column3;
			IF done = 1 THEN
				LEAVE data_loop;
			END IF;
			
			
			CALL PROC_LOG(CONCAT ( '데이터갱신 v_column1: ' ,v_column1, ', v_column2', v_column2));
			
	END LOOP data_loop;

	CLOSE c1;
END$$

DELIMITER ;

위의 코드 중 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

이 부분이 에러와 관련된 부분으로 done 변수의 값을 HANDLER FOR NOT FOUND 데이터를 찾지 못하는 상황이

발생되면 1로 변경하고 

loop문 안에서 

IF done = 1 THEN
   LEAVE data_loop;
END IF;

그런 상황이 발생되면 작업을 하지 않고 빠져 나오게 설정 해줍니다.

다른 분들이 올린 내용을 보면 DECLARE done INT DEFAULT FALSE 로 설정하고

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

IF done THEN 으로 설정 하는 경우도 있는데 동일한 동작을 하게 됩니다. (1=true, 0=false)

이런 작업을 진행 후에도 동일한 에러가 발생 된다면 해당 쿼리문이 정상 동작 하는지 여부를 체크 하는게 좋습니다.

전 database charset설정이 맞지 않아 한글이 깨지며 오류가 발생된 경우가 있었습니다.

mysql datetime 항목에 데이터 형식에 맞지 않는 데이터를 넣어도 등록이 되는경우 

데이터를 가지고 올때 sql 관련 에러가 발샐될 수 있습니다.

관련 문구의 내용중 '0000-00-00 00:00:00'에 해당하는 문구가 나오는 경우

datetime 항목에 '' 이나 0으로 빈값이 들어간 경우 입니다.

입력된 내용은 null로 초기화 해줘야 하고 더이상 이런식의 데이터가 입력 되지 않게 

mysql sql_mode의 설정에 NO_ZERO_DATE 관련 내용이 추가 되어야 합니다.

https://blog.jiniworld.me/64

mysql관련 이관작업시 sql덤프를 사용하는 경우

Error Code: 2006 - MySQL server has gone away 와 같은 에러 코드가 발생되고 작업을 진행 하지 못하는 경우가 생기게 되는데 이 경우

set GLOBAL max_allowed_packet = 1024 * 1024 * 16; 

set SESSION max_allowed_packet = 1024 * 1024 * 16;

show variables like '%max_allowed_packet%' 명령으로 수정된 정보는 확인 가능 합니다.

(다른글의 설정 파일 변경 까지는 필요 없는경우 저렇게 쿼리문으로 처리 가능 합니다.)

서버의 설정이나 버전에 따라서 mysql의 한글 정보 insert등이 깨져 버리는 경우가 생기게 되면

jdbc url부분에 인자값으로 useUnicode=true&characterEncoding=utf8 내용을 추가 하면 정상적으로 처리

가능 합니다.

mysql 사용시 resource의 url설정시 

?autoReconnect=true↦zeroDateTimeBehavior=convertToNull

옵션을 추가 해야 하는 경우가 생긴다

autoReconnect는 일정 시간이 지나면 연결을 끊는 mysql을 위해서 추가되는 옵션이며

zeroDateTimeBehavior 부분의 경우 datetime컬럼에 null을 입력 하기 위해서 추가되는 옵션이다

autoReconnecct의 경우 mysql성능에 영향을 미치기 때문에 가능 하면

validationQuery="select 1" 을 사용하길 권장 합니다.

SET @CA := 0; 

UPDATE table_name SET column_name = (@CA := @CA + 1); 

같은 방식으로 oracle의 update rownum 같은 처리를 할 수 있습니다.

CA라는 변수를 생성해서 해당 변수를 update에 사용하는 방식 입니다.

역시 select문에서의 rownum대체 역시 동일한 방식으로 처리 하게 됩니다.

SELECT @rwn:=@rwn+1 AS rownum

FROM 

(SELECT * FROM table_name

ORDER BY column_name ASC) a, (SELECT @rwn:=0) AS r

같은 방식으로 처리가 가능 합니다.

SET @rwn := 0;

SELECT @rwn:=@rwn+1 AS rownum

FROM 

(SELECT * FROM table_name

ORDER BY column_name ASC) a

이렇게 처리 하고 있는 겁니다.

SELECT NOW();

결과 : 2016-11-23 10:24:27

SELECT CURDATE();

결과 : 2016-11-23

SELECT DATE_FORMAT(NOW(), '%y%m%d');

결과 : 161123

SELECT DATE_FORMAT(CURDATE(), '%y%m%d');

결과 : 161123

SELECT DATE_FORMAT(NOW(), '%y.%m.%d');

결과 : 16.11.23

SELECT DATE_FORMAT(CURDATE(), '%y/%m/%d');

결과 : 16/11/23

SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d');

결과 : 2016-11-23

날짜 출력 형식

%Y - Year 네자리

%y - Year 두자리

%m - Month (00-12)

%d - Day (00-31)

%e - Day (0-31)

%p - AM or PM

%H - Hour (00-23)

%h - Hour (01-12)

%k - Hour (0-23)

%l - Hour (1-12)

%i - Minutes (00-59)

%s - Second (00-59)

mysql root계정 비밀번호 변경

service mysql stop or 윈도우 service 정지

/etc/mysql/my.conf or 윈도우 Program Data 폴더의 ini 파일 하단에

skip-grant-tables 문구 추가

service mysql start or 윈도우 service 시작

mysql -uroot mysql 명령어 실행 (윈도우의 경우 설치된 mysql폴더의 bin폴더 하위까지 이동 후 실행)

UPDATE user SET password=PASSWORD('비밀번호') WHERE user='root';

flush privileges; 

명령어 2개 실행 후 

skip-grant-tables 문구 삭제 후 서비스 다시 시작

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1505 > 1024). You can change this value on the server by setting the max_allowed_packet' variable

이런 에러가 발생 되는 경우 my-sql 설정 my.ini의 max_allowed_packet 설정을 변경 하게 됩니다.

이렇게 변경을 하고 진행을 해도 중간에 다시 설정이 초기화 되는 경우 설정 문제가 아닌 누군가 설정을 변경 하고 있는 걸 수 있습니다.

my.ini파일의 general-log=0 설정을 1로 변경 해주면my-sql로 넘어오는 모든 sql정보를 로그로 남기게 됩니다.

설정이 변경 된걸 확인 하고 해당 로그를 확인 하는 작업을 진행 해서 혹 누군가 설정을 변경 하고 있다면 해당 작업을 하지 못하게 처리 해야 합니다.