http://cafe.rootcenter.com/130078704068

mysql replication 진행도중 slave서버의  error-log 파일내용을 확인한 후 대처법

 에러유형-4) - 2010년 5월 3일 추가

100503  8:41:05 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236)
100503  8:41:05 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when reading data from binary log
100503  8:41:05 [Note] Slave I/O thread exiting, read up to log 'binlog.021419', position 196980

 

해결1)  master서버의 max_allowed_packet 값이 작을 경우 문제가 발생할수 있다.

          - my.cnf파일을 수정해 준후 데몬을 재시작한다.

          - 데몬 재시작이 힘들경우 쉘 상에서 값을 변경한다.

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SET GLOBAL max_allowed_packet = 1024*1024*100;

mysql> SET SESSION max_allowed_packet = 1024*1024*100;

해결2) master서버의 max_allowed_packet값이 충분히 큰데 위와 같은 오류가 발생할 경우..

        - 먼저 master서버의 binlog파일을 확인해본다.

[root@db11 slave_log]#mysqlbinlog  binlog.021419 | more

--중략--
# at 196800
#100503  7:16:31 server id 1  end_log_pos 196827  Xid = 3282913394
COMMIT/*!*/;
# at 196827
#100503  7:16:31 server id 1  end_log_pos 138  Query thread_id=235254463 exec_time=0 error_code=0
SET TIMESTAMP=1272838591/*!*/;
Update tablename Set count0/*!*/;
# at 196965
#100503  7:16:31 server id 1  end_log_pos 196992  Xid = 3282913395
COMMIT/*!*/;
# at 196992
#100503  7:16:31 server id 1  end_log_pos 133  Query thread_id=232937265 exec_time=0 error_code=0
SET TIMESTAMP=1272838591/*!*/;
Update tablename Set size = 295976960/*!*/;
# at 197125

        - 확인 결과 196980 위치가 존재하지 않는다.

        - master_log_pos를 다음값으로 변경하면 끝.

 

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> change master to master_log_pos = 196992;

mysql> start slave;

 

에러유형-3) 2010.01.30 추가

100130 12:57:07 [ERROR] Slave: Error ''SQL 구문에 오류가 있습니다.' 에러 같읍니다. ('' 명령어 라인 1)' on query. Default database: 'dbname'. Query: 'UPDATE PARTNER_CNT SET .... WHERE ...', Error_code: 1064
100130 12:57:07 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.007704' position 70234030

해결) 키값이 중복되어 슬레이브에서 쿼리가 실행되지 못하고 있는것이다.

        정상적인 리플리케이션에서는 문제가 없지만 갑작스런 서버 다운, 네트워크 단절이 발생했을 경우 위와  같은 오류가 발생한다.

        아래 명령을 입력하여 해결 할 수 있다.      

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave; 

 

 

 에러유형-1)

100122  0:22:44 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
100122  0:22:44 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'binlog.017528' position 90025355
100122  0:22:48 [Note] Slave: connected to master 'xxxxxx@아이피',replication resumed in log 'binlog.017528' at position 90025355

 

 

해결) master서버와의 연결이 끊어지면 위와 같은 오류 메세지를 출력하고 리플리케이션이 중지된다.

        master서버가 정상적으로 돌아왔다면 slave 서버에 mysql을 접속한후 아래 명령을 입력한다.

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> start slave;

 

에러유형-2)

 100122  1:35:56 [Note] Slave I/O thread killed while reading event
100122  1:35:56 [Note] Slave I/O thread exiting, read up to log 'binlog.017530', position 33231841
100122  1:36:05 [Note] Slave SQL thread initialized, starting replication in log 'binlog.017528' at position 195, relay log '/db/slave_log/relay_log.002684' position: 90005865
100122  1:36:05 [ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 811157539, event_type: 0
100122  1:36:05 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
100122  1:36:05 [ERROR] Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0
100122  1:36:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.017528' position 195
100122  1:36:05 [Note] Slave I/O thread: connected to master 'xxxx@아이피',  replication started in log 'binlog.017530' at position 33231841

 

해결) slave서버가 갑작스럽게 다운 되었을때 로그 파일이 깨져서 위와 같은 오류가 발생한다.

        위 에러로그에서는 relay_log.002684 로그에서 중지 되었다.

        slave_log가 쌓이는 디렉토리( /db/slave_log/)에  relay_log.002685 파일이 있는지 확인후 아래 명령을 입력한다.

 

[root@db11 slave_log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11443092
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> stop slave;

mysql> CHANGE MASTER TO relay_log_file='/db/slave_log/relay_log.002685', relay_log_pos=0;

mysql> start slave;

저작자표시 비영리 변경금지

'Database > Mysql' 카테고리의 다른 글

프로시저의 예  (0) 2011.01.26
MySQL DUMP 백업 및 복원  (0) 2010.09.20
load data  (0) 2010.07.19
LOAD XML Syntax [v5.5]  (0) 2010.04.07
MySQL 사용 중 발생할 수 있는 대기 현상 장애의 원인과 대처  (0) 2009.12.30

Basic Update Statements
The Oracle UPDATE statement processes one or more rows in a table and sets one or more columns to the values you specify.

Update all records		 UPDATE <table_name>
SET <column_name> = <value>
CREATE TABLE test AS
SELECT object_name, object_type
FROM all_objs;

SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'OOPS';

SELECT DISTINCT object_name
FROM test;

ROLLBACK;

Update a specific record		 UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>
SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'LOAD'
WHERE object_name = 'DUAL';

COMMIT;

SELECT DISTINCT object_name
FROM test

Update based on a single queried value		 UPDATE <table_name>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name
  WHERE <column_name> <condition> <value>)
WHERE <column_name> <condition> <value>;
CREATE TABLE test AS
SELECT table_name, CAST('' AS VARCHAR2(30)) AS lower_name
FROM user_tables;

desc test

SELECT *
FROM test
WHERE table_name LIKE '%A%';

SELECT *
FROM test
WHERE table_name NOT LIKE '%A%';

-- this is not a good thing ...
UPDATE test t
SET lower_name = (
  SELECT DISTINCT LOWER(table_name)
  FROM user_tables u
  WHERE u.table_name = t.table_name
  AND u.table_name LIKE '%A%');
-- look at the number of rows updated

SELECT * FROM test;

-- neither is this
UPDATE test t
SET lower_name = (
  SELECT DISTINCT LOWER(table_name)
  FROM user_tables u
  WHERE u.table_name = t.table_name
  AND u.table_name NOT LIKE '%A%');

SELECT * FROM test;

UPDATE test t
SET lower_name = (
  SELECT DISTINCT LOWER(table_name)
  FROM user_tables u
  WHERE u.table_name = t.table_name
  AND u.table_name LIKE '%A%')
WHERE t.table_name LIKE '%A%';

SELECT * FROM test;

Update based on a query returning multiple values 		UPDATE <table_name> <alias>
SET (<column_name>,<column_name> ) = (
   SELECT (<column_name>, <column_name>)
   FROM <table_name>
   WHERE <alias.column_name> = <alias.column_name>)
WHERE <column_name> <condition> <value>;
CREATE TABLE test AS
SELECT t. table_name, t. tablespace_name,  s.extent_management
FROM user_tables t, user_tablespaces s
WHERE t.tablespace_name = s. tablespace_name
AND 1=2;

desc test

SELECT * FROM test;

-- does not work
UPDATE test
SET (table_name, tablespace_name) = (
  SELECT table_name, tablespace_name
  FROM user_tables);

-- works
INSERT INTO test
(table_name, tablespace_name)
SELECT table_name, tablespace_name
FROM user_tables;

COMMIT;

SELECT *
FROM test
WHERE table_name LIKE '%A%';

-- does not work
UPDATE test t
SET tablespace_name, extent_management = (
  SELECT tablespace_name, extent_management
  FROM user_tables a, user_tablespaces u
  WHERE t.table_name = a.table_name
  AND a.tablespace_name = u.tablespace_name
  AND t.table_name LIKE '%A%');

-- works but look at the number of rows updated
UPDATE test t
SET (tablespace_name, extent_management) = (
  SELECT DISTINCT u.tablespace_name, u.extent_management
  FROM user_tables a, user_tablespaces u
  WHERE t.table_name = a.table_name
  AND a.tablespace_name = u.tablespace_name
  AND t.table_name LIKE '%A%');

ROLLBACK;

-- works properly
UPDATE test t
SET (tablespace_name, extent_management) = (
  SELECT DISTINCT (u.tablespace_name, u.extent_management)
  FROM user_tables a, user_tablespaces u
  WHERE t.table_name = a.table_name
  AND a.tablespace_name = u.tablespace_name)
WHERE t.table_name LIKE '%A%';

SELECT * FROM test;

Update the results of a SELECT statement		 UPDATE (<SELECT Statement>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;
SELECT *
FROM test
WHERE table_name LIKE '%A%';

SELECT *
FROM test
WHERE table_name NOT LIKE '%A%';

UPDATE (
  SELECT *
  FROM test
  WHERE table_name NOT LIKE '%A%')
SET extent_management = 'Unknown'
WHERE table_name NOT LIKE '%A%';

SELECT * FROM test;
 
Correlated Update

Single column		 UPDATE TABLE(<SELECT STATEMENT>) <alias>
SET <column_name> = (
  SELECT <column_name>
  FROM <table_name> <alias>
  WHERE <alias.table_name> = <alias.table_name>);
conn hr/hr

CREATE TABLE empnew AS
SELECT * FROM employees;

UPDATE empnew
SET salary = salary * 1.1;

UPDATE employees t1
SET salary = (
  SELECT salary
  FROM empnew t2
  WHERE t1.employee_id = t2.employee_id);

drop table empnew;

Multi-column		 UPDATE <table_name> <alias>
SET (<column_name_list>) = (
  SELECT <column_name_list>
  FROM <table_name> <alias>
  WHERE <alias.table_name> <condition> <alias.table_name>);
CREATE TABLE t1 AS
SELECT table_name, tablespace_name
FROM user_tables
WHERE rownum < 11;

CREATE TABLE t2 AS
SELECT table_name,
TRANSLATE(tablespace_name,'AEIOU','VWXYZ') AS TABLESPACE_NAME
FROM user_tables
WHERE rownum < 11;

SELECT * FROM t1;

SELECT * FROM t2;

UPDATE t1 t1_alias
SET (table_name, tablespace_name) = (
  SELECT table_name, tablespace_name
  FROM t2 t2_alias
  WHERE t1_alias.table_name = t2_alias.table_name);

SELECT * FROM t1;
 
Nested Table Update
  See Nested Tables page
 
Update With Returning Clause

Returning Clause demo		 UPDATE (<SELECT Statement>)
SET ....
WHERE ....
RETURNING <values_list>
INTO <variables_list>;
conn hr/hr

var bnd1 NUMBER
var bnd2 VARCHAR2(30)
var bnd3 NUMBER

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000,
department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;

print bnd1
print bnd2
print bnd3

rollback;
conn hr/hr

variable bnd1 NUMBER

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 100
RETURNING SUM(salary) INTO :bnd1;

print bnd1

rollback;
 
Update Object Table

Update a table object		 UPDATE <table_name> <alias>
SET VALUE (<alias>) = (
  <SELECT statement>)
WHERE <column_name> <condition> <value>;
CREATE TYPE people_typ AS OBJECT (
last_name     VARCHAR2(25),
department_id NUMBER(4),
salary        NUMBER(8,2));
/

CREATE TABLE people_demo1 OF people_typ;

desc people_demo1

CREATE TABLE people_demo2 OF people_typ;

desc people_demo2

INSERT INTO people_demo1
VALUES (people_typ('Morgan', 10, 100000));

INSERT INTO people_demo2
VALUES (people_typ('Morgan', 10, 150000));

UPDATE people_demo1 p
SET VALUE(p) = (
  SELECT VALUE(q) FROM people_demo2 q
  WHERE p.department_id = q.department_id)
WHERE p.department_id = 10;

SELECT * FROM people_demo1;
 
Record Update

Update based on a record

Note: This construct updates every column so use with care. May cause increased redo, undo, and foreign key locking issues.

 UPDATE <table_name>
SET ROW = <record_name>
WHERE <column_name> <condition> <value>;
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

SELECT DISTINCT tablespace_name
FROM t;

DECLARE
 trec  t%ROWTYPE;
BEGIN
  trec.table_name := 'DUAL';
  trec.tablespace_name := 'NEW_TBSP';

  UPDATE t
  SET ROW = trec
  WHERE table_name = 'DUAL';

  COMMIT;
END;
/

SELECT DISTINCT tablespace_name
FROM t;
 
Update Partitioned Table

Update only records in a single partition		 UPDATE <table_name> PARTITION (<partition_name>)
SET <column_name> = <value>
WHERE <column_name> <condition> <value>;
conn sh/sh

UPDATE sales PARTITION (sales_q1_2005) s
SET s.promo_id = 494
WHERE amount_sold > 9000;

http://psoug.org/reference/update.html
저작자표시 비영리 변경금지

'Database > Oracle' 카테고리의 다른 글

계층형 쿼리 (CONNECT BY)  (0) 2010.07.14
오라클 클라이언트 가 토드에서 안될때. ㅋ  (0) 2010.06.29
Oracle optimizer goal, FIRST_ROWS 그리고 ALL_ROWS  (0) 2010.05.14
◈ DB Link (오라클 원격DB 연결)  (0) 2010.05.13
Architecture  (0) 2010.05.04

http://genes1s.egloos.com/2369915
저작자표시 비영리 변경금지

'Database > Server' 카테고리의 다른 글

쉘스크립트  (0) 2014.05.09
리눅스 date 명령 : 어제(과거) , 내일(미래) 날짜, 시간 구하기  (0) 2013.10.11
RSH 설정  (0) 2010.06.10
lsvg 명령어  (0) 2010.05.26
RAID 종류 및 설명  (0) 2010.05.20
http://yaaong.tistory.com/28
저작자표시 비영리 변경금지

'Database > Server' 카테고리의 다른 글

리눅스 date 명령 : 어제(과거) , 내일(미래) 날짜, 시간 구하기  (0) 2013.10.11
# ACL (Access Control List)  (0) 2010.06.10
lsvg 명령어  (0) 2010.05.26
RAID 종류 및 설명  (0) 2010.05.20
유닉스 명령어  (0) 2010.04.07

+ Recent posts

티스토리툴바