A Little Noise

March 24, 2010

LOAD DATA and recovery

Filed under: MySQL Gotchas — snoyes @ 12:39 pm

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 ▼

3 Comments »

  1. […] LOAD DATA and recovery « A Little Noise […]

    Pingback by Computer Security Devices « Network And Security | Network Security — March 25, 2010 @ 2:22 am

  2. 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.

    Comment by Justin Swanhart — March 25, 2010 @ 2:46 pm

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

    Comment by Patrice Levesque — March 20, 2014 @ 4:41 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress