CMDEV认证培训包-3期（PX020） 习题1 PX 020 030202 167习题分析.docxVIP

第一题： A simple master-to-slave replication is currently being used. The following information is extracted from the SHOW SLAVE STATUS output:?Last_SQL_Error: Error Duplicate entry 8 for key PRIMARY on query. Default database: mydb.Query: insert into mytable VALUES (8, George)Skip_Counter: 0?Retrieved_Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8Auto_Position: 1You execute a SHOW CREATE TABLE mytable on the slave:?CREATE TABLE mytable (ID int(11) NOT NULL DEFAULT 0,name char(10) DEFAULT NULL,PRIMARY KEY (ID))The table mytable on the slave contains the following:?You have issued a STOP SLAVE command. One or more statements are required before you canissue a START SLAVE command to resolve the duplicate key error.Which statement should be used?A)SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1B)?SET GTID_NEXT=CONSISTENCY;?BEGIN; COMMIT;?SET GTID_NEXT= AUTOMATIC’;C)SET GLOBAL enforce_gtid_consistency=OND)SET GTID_EXECUTED=38f32e23480a7-32a1-c323f78067fd37821 : 9;E)SET GTID_NEXT=38f32e23480a7-32a1-c323f78067fd37821 : 9;?BEGIN; COMMIT;?SET GTID_NEXT=AUTOMATIC;答案：E分析：此题中使用的Replication是通过GTID实现的，因此A错，因此GLOBAL SQL_SKIP_SLAVE_COUNTER=1对使用GTID进行的Replication无效C错，因为GLOBAL enforce_gtid_consistency=ON是实现的前提。由于GTID_NEXT的有效值为：AUTOMATIC / ANONYMOUS / UUID:NUMBER因此 B错由于Retrieved_Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8因此已经收到主库事务1-8，因此报错是从第9个事务重复记录导致的，很有可能slave上的第8行被人为录入了，导致同步问题。D错，因为GTID_EXECUTED表示已经执行完成的事务。为了临时绕过这个问题，使用注入空事务(BEGIN; COMMIT; ) 代替完成第9个事务.完成后GTID_EXECUTED才会变为38f32e23480a7-32a1-c323f78067fd37821 : 9这时候重新SET GTID_NEXT=AUTOMATIC; 重启slave后，开始从第10个事务开始同步 第二题： Consider the following statement on a RANGE partitioned table:?ALTER TABLE orders DROP PARTITION p1, p3;What is the outcome of executing the above statement?A.Only the first partition (p1) will be dropped as only one can be dropped at any time.B.All data in p1 and p3 partitions are removed, but the table definition remains unchanged.C.A syntax error will result