1Z0-888 · Question #21
A simple master-to-slave replication is currently being used. This information is extracted from the SHOW SLAVE STATUS output: Last_SQL_Error: Error 'Duplicat entry '8' for key 'PRIMARY'' on query…
The correct answer is C. SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1. The listed answer of C is actually incorrect for this scenario - the real correct answer is B. The SHOW SLAVE STATUS output shows Auto-Position: 1 with Retrieved_Gtid_Set and Executed_Gtid_Set populated, confirming this is GTID-based replication. MySQL explicitly forbids SET…
Question
mytable (
ID int(11) NOT NULL DEFAULT '0',
name char(10) DEFAULT NULL,
PRIMARY KEY (ID)
)
The table mytable on the slave contains:
+-----+--------+
| ID | name |
+-----+--------+
| 7 | Nancy |
| 8 | George |
+-----+--------+
You have issued a STOP SLAVE command. You have determined that it is safe to skip the transaction in this case. One or more statements are required before you can issue a START SLAVE command to resolve the duplicate key error. Which statement should be used?Options
- ASET GTID_NEXT="CONSISTENCY"; BEGIN; COMMIT;
- BSET GTID_NEXT="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC";
- CSET GLOBAL SQL_SKIP_SLAVE_COUNTER=1
- DSET GLOBAL enforce_gtid_consistency=ON
- ESET GTID_EXECUTED="5da6b4f5-6f60-11e8-b2d6-0010e05f3e06:8";
How the community answered
(28 responses)- A18% (5)
- B14% (4)
- C61% (17)
- D4% (1)
- E4% (1)
Explanation
The listed answer of C is actually incorrect for this scenario - the real correct answer is B.
The SHOW SLAVE STATUS output shows Auto-Position: 1 with Retrieved_Gtid_Set and Executed_Gtid_Set populated, confirming this is GTID-based replication. MySQL explicitly forbids SET GLOBAL SQL_SLAVE_SKIP_COUNTER when gtid_mode=ON - issuing it will throw an error. Option B is the correct approach: inject an empty transaction tagged with the exact GTID that needs to be skipped (5da6b4f5...:8), which marks it as "executed" in gtid_executed without actually running the duplicate INSERT, then restore GTID_NEXT to AUTOMATIC so replication resumes normally.
Why each distractor fails:
- A -
"CONSISTENCY"is not a valid value forGTID_NEXT; valid values areAUTOMATIC,ANONYMOUS, or a specific GTID. - C -
SQL_SLAVE_SKIP_COUNTERis incompatible withgtid_mode=ONand will error out; it only works in non-GTID (binary-log position) replication. - D -
enforce_gtid_consistency=ONenforces GTID-safe SQL statements; it has nothing to do with skipping a failed transaction. - E -
GTID_EXECUTEDis a read-only system variable; you cannotSETit directly.
Memory tip: With GTIDs, think "inject, don't skip" - you can never subtract a transaction, only tell MySQL you already ran it by committing an empty one with the exact GTID.
Topics
Community Discussion
No community discussion yet for this question.