초보 DBA의 일상

2018-09-10 21:34:47 3235 [ERROR] /usr/local/mysql/bin/mysqld: Table './mysql/user' is marked as crashed and should be repaired

2018-09-10 21:34:47 3235 [ERROR] Fatal error: Can't open and lock privilege tables: Table './mysql/user' is marked as crashed and should be repaired

cd /home/date/mysql/    -- mysql/user 디렉토리로 이동 

myisamchk -r user         -- 해당 테이블 repair 진행 

mysql_upgrade -usjyang -psoon0714 --verbose

-- departments_dev 생성 (데이터 조회용 테이블)

CREATE TABLE `departments_dev` (

`dept_no` CHAR(4) NOT NULL,

`dept_name` VARCHAR(40) NOT NULL

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

insert into departments_dev values('d009','Customer Service');

insert into departments_dev values('d007','Sales');  -- 중복데이터 만큼 입력되는지 확인하기 위해 3번 insert 함

insert into departments_dev values('d007','Sales');

insert into departments_dev values('d007','Sales');

-- departments_dev2 생성 (데이터 입력용 테이블)

CREATE TABLE `departments_dev2` (

`dept_no` CHAR(4) NOT NULL,

`dept_name` VARCHAR(40) NOT NULL

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB;

DELIMITER //

-- drop procedure if exists SP_cursor_dev;

create procedure SP_cursor_dev(IN v1 char(4), IN v2 varchar(40))   -- INPUT 값을 받는 프로시저 생성 

BEGIN 

DECLARE c1 varchar(4);

DECLARE c2 varchar(40);

DECLARE c_Done int default 0;

/* 여기에 커서를 정의 합니다. */

DECLARE mCursor CURSOR FOR

select dept_no,dept_name

from departments_dev

where dept_no=v1

and dept_name=v2;

/* 데이터가 없으면 c_Done에 1 */

DECLARE CONTINUE HANDLER FOR NOT FOUND SET c_Done=-1;

OPEN mCursor;

/* 반복합니다. */ 

REPEAT

 /* 반환된 필드값을 변수에 담습니다. */

FETCH NEXT FROM mCursor INTO c1,c2;

IF NOT c_Done then

/* 수행할 쿼리리 여기에 작성합니다. */   

insert into departments_dev2 values(c1,c2);

END IF;

/* c_Done이 1이 될때까지 반복 합니다. */

UNTIL c_Done END REPEAT;

CLOSE mCursor;

select concat('error : ', c_Done);  -- 에러코드 출력

END //

DELIMITER ;

 CALL SP_cursor_dev('d007','Sales');

 CALL SP_cursor_dev('d077','Salaa');  

select * from departments_dev2;