A Little Noise

24Mar/102

LOAD DATA and recovery

A little two-part quiz. If you get the first one without peeking, you're worth your pay as a DBA. If you get the second one without peeking, you may tell your boss that some random guy on the Internet says you deserve a raise.

Start with a text file, 'test.txt', with these three lines:

1
1
2

Set up the test in MySQL:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int primary key);
LOAD DATA INFILE 'test.txt' INTO TABLE t1;

This gives "ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'", which is expected.

What's in the table?

Show Answer ▼

Now, pretend you're setting up a slave, or there was a crash and you're recovering from binary logs:

mysqlbinlog bin.000001 | mysql

How many rows are in t1 now?

Show Answer ▼

Comments (2) Trackbacks (1)
  1. This probably falls under the category:
    “It isn’t a bug if we document it…”

    MySQL is really bad about documenting bad behavior instead of fixing it.

  2. Wouldn’t the second answer depend on the type of replication (row/statement)?


Leave a comment