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.<\/p>\n
Start with a text file, ‘test.txt’, with these three lines:<\/p>\n
Set up the test in MySQL:<\/p>\n <\/p>\n This gives “ What’s in the table? <\/p>\n Now, pretend you’re setting up a slave, or there was a crash and you’re recovering from binary logs:<\/p>\n How many rows are in t1 now?<\/p>\n Why? The manual says, “mysqlbinlog converts LOAD DATA INFILE statements to LOAD DATA LOCAL INFILE statements,” and, “with LOCAL, the default duplicate-key handling behavior is the same as if IGNORE is specified.” Note that a replicating slave will handle it correctly – if the master used LOCAL (and therefore IGNORE), the slave will do IGNORE. If the master did not use LOCAL or IGNORE and so got the error above, the slave will do the same, and so the data will match. So be advised: replication and 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[6],"tags":[],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-gotchas"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-1G","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=104"}],"version-history":[{"count":8,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions"}],"predecessor-version":[{"id":112,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/104\/revisions\/112"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}1
\n1
\n2<\/code><\/p>\nDROP TABLE IF EXISTS t1;
\nCREATE TABLE t1 (id int primary key);
\nLOAD DATA INFILE 'test.txt' INTO TABLE t1;<\/code><\/p>\nERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'<\/code>“, which is expected. <\/p>\nmysqlbinlog bin.000001 | mysql<\/code><\/p>\n
\nmysql> SELECT * FROM t1;
\n+----+
\n| id |
\n+----+
\n| 1 |
\n| 2 |
\n+----+
\n2 rows in set (0.00 sec)<\/code><\/p>\n
\n
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/mysqlbinlog.html<\/a>
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/load-data.html<\/a><\/p>\nmysqlbinlog | mysql<\/code> may not give the same results.<\/spoiler><\/p>\n","protected":false},"excerpt":{"rendered":"