WL#874 "Extended LOAD DATA".

Now one can use user variables as target for data loaded from file
(besides table's columns). Also LOAD DATA got new SET-clause in which
one can specify values for table columns as expressions.

For example the following is possible:
LOAD DATA INFILE 'words.dat' INTO TABLE t1 (a, @b) SET c = @b + 1;

This patch also implements new way of replicating LOAD DATA.
Now we do it similarly to other queries.
We store LOAD DATA query in new Execute_load_query event
(which is last in the sequence of events representing LOAD DATA).
When we are executing this event we simply rewrite part of query which
holds name of file (we use name of temporary file) and then execute it
as usual query. In the beggining of this sequence we use Begin_load_query
event which is almost identical to Append_file event


client/mysqlbinlog.cc:
  Added support of two new binary log events Begin_load_query_log_event and
  Execute_load_query_log_Event which are used to replicate LOAD DATA INFILE.
mysql-test/r/ctype_ucs.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/insert_select.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/loaddata.result:
  Added tests for new LOAD DATA features.
mysql-test/r/mix_innodb_myisam_binlog.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results (don't dare to get rid from binlog positions
  completely since it seems that this test uses them).
mysql-test/r/mysqlbinlog.result:
  New approach for binlogging of LOAD DATA statement. Now we store it as
  usual query and rewrite part in which file is specified when needed.
  So now mysqlbinlog output for LOAD DATA much more closer to its initial
  form. Updated test'd results accordingly.
mysql-test/r/mysqldump.result:
  Made test more robust to other tests failures.
mysql-test/r/rpl000015.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_change_master.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results.
mysql-test/r/rpl_charset.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly
mysql-test/r/rpl_deadlock.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly
mysql-test/r/rpl_error_ignored_table.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/rpl_flush_log_loop.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_flush_tables.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/rpl_loaddata.result:
  New way of replicating LOAD DATA. Now we do it similarly to other
  queries. We store LOAD DATA query in new Execute_load_query event
  (which is last in the sequence of events representing LOAD DATA).
  When we are executing this event we simply rewrite part of query which
  holds name of file (we use name of temporary file) and then execute it
  as usual query. In the beggining of this sequence we use Begin_load_query
  event which is almost identical to Append_file event...
  
  Updated test's results wwith new binlog positions.
mysql-test/r/rpl_loaddata_rule_m.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
  Since now LOAD DATA is replicated much in the same way as usual query
  --binlog_do/ignore_db work for it inthe same way as for usual queries.
mysql-test/r/rpl_loaddata_rule_s.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_loaddatalocal.result:
  Added nice test for case when it is important that LOAD DATA LOCAL
  ignores duplicates.
mysql-test/r/rpl_log.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly (don't dare to get rid from binlog 
  positions completely since it seems that this test uses them).
mysql-test/r/rpl_log_pos.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_max_relay_size.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_multi_query.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_relayrotate.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_replicate_do.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_reset_slave.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_rotate_logs.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_server_id1.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_server_id2.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly.
mysql-test/r/rpl_temporary.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/rpl_timezone.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/rpl_until.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results accordingly and tweaked test a bit to bring it
  back to good shape.
mysql-test/r/rpl_user_variables.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/r/user_var.result:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test's results and made it more robust for future similar 
  changes.
mysql-test/t/ctype_ucs.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and made it more robust for future similar
  changes.
mysql-test/t/insert_select.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and made it more robust for future similar
  changes.
mysql-test/t/loaddata.test:
  Added test cases for new LOAD DATA functionality.
mysql-test/t/mix_innodb_myisam_binlog.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/mysqlbinlog.test:
  New way of replicating LOAD DATA local. Now we do it similarly to other
  queries. We store LOAD DATA query in new Execute_load_query event
  (which is last in the sequence of events representing LOAD DATA).
  When we are executing this event we simply rewrite part of query which
  holds name of file (we use name of temporary file) and then execute it
  as usual query. In the beggining of this sequence we use Begin_load_query
  event which is almost identical to Append_file event...
  
  Thus we need new binlog positions for LOAD DATA events.
mysql-test/t/mysqlbinlog2.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/mysqldump.test:
  Made test more robust for failures of other tests.
mysql-test/t/rpl_charset.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/rpl_deadlock.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/rpl_error_ignored_table.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and made it more robust for future similar
  changes.
mysql-test/t/rpl_flush_tables.test:
  Addition of two new types of binary log events shifted binlog positions.
  Made test more robust for future similar changes.
mysql-test/t/rpl_loaddata.test:
  New way of replicating LOAD DATA. Now we do it similarly to other
  queries. We store LOAD DATA query in new Execute_load_query event
  (which is last in the sequence of events representing LOAD DATA).
  When we are executing this event we simply rewrite part of query which
  holds name of file (we use name of temporary file) and then execute it
  as usual query. In the beggining of this sequence we use Begin_load_query
  event which is almost identical to Append_file event...
  
  Apropritely updated comments in test.
mysql-test/t/rpl_loaddata_rule_m.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and made it more robust for future similar 
  changes.
  Since now LOAD DATA is replicated much in the same way as usual query
  --binlog_do/ignore_db work for it inthe same way as for usual queries.
mysql-test/t/rpl_loaddata_rule_s.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/rpl_loaddatalocal.test:
  Added nice test for case when it is important that LOAD DATA LOCAL
  ignores duplicates.
mysql-test/t/rpl_log.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly (don't dare to get rid from binlog positions
  completely since it seems that this test uses them).
mysql-test/t/rpl_log_pos.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/rpl_multi_query.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly.
mysql-test/t/rpl_temporary.test:
  Addition of two new types of binary log events shifted binlog positions.
  Made test more robust for future similar changes.
mysql-test/t/rpl_timezone.test:
  Addition of two new types of binary log events shifted binlog positions.
  Made test more robust for future similar changes.
mysql-test/t/rpl_until.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and tweaked it a bit to bring it back to good
  shape.
mysql-test/t/rpl_user_variables.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and made it more robust for future similar
  changes.
mysql-test/t/user_var.test:
  Addition of two new types of binary log events shifted binlog positions.
  Updated test accordingly and made it more robust for future similar
  changes.
sql/item_func.cc:
  Added Item_user_var_as_out_param class that represents user variable
  which used as out parameter in LOAD DATA.
  
  Moved code from Item_func_set_user_var::update_hash() function to
  separate static function to be able to reuse it in this new class.
sql/item_func.h:
  Added Item_user_var_as_out_param class that represents user variable
  which used as out parameter in LOAD DATA.
sql/log_event.cc:
  New way of replicating LOAD DATA. Now we do it similarly to other
  queries. We store LOAD DATA query in new Execute_load_query event
  (which is last in the sequence of events representing LOAD DATA).
  When we are executing this event we simply rewrite part of query which
  holds name of file (we use name of temporary file) and then execute it
  as usual query. In the beggining of this sequence we use Begin_load_query
  event which is almost identical to Append_file event.
sql/log_event.h:
  New way of replicating LOAD DATA. Now we do it similarly to other
  queries. We store LOAD DATA query in new Execute_load_query event
  (which is last in the sequence of events representing LOAD DATA).
  When we are executing this event we simply rewrite part of query which
  holds name of file (we use name of temporary file) and then execute it
  as usual query. In the beggining of this sequence we use Begin_load_query
  event which is almost identical to Append_file event.
sql/mysql_priv.h:
  Now mysql_load() has two more arguments. They are needed to pass list of
  columns and corresponding expressions from new LOAD DATA's SET clause.
sql/share/errmsg.txt:
  Added new error message which is used to forbid loading of data from
  fixed length rows to variables.
sql/sql_lex.h:
  Added LEX::fname_start/fname_end members. 
  They are pointers to part of LOAD DATA statement which should be
  rewritten during replication (file name + little extra).
sql/sql_load.cc:
  Added support for extended LOAD DATA. 
  Now one can use user variables as target for data loaded from file 
  (besides table's columns). Also LOAD DATA got new SET-clause in which
  one can specify values for table columns as expressions.
  
  Updated mysql_load()/read_fixed_length()/read_sep_field() to support
  this functionality (now they can read data from file to both columns and
  variables and assign do calculations and assignments specified in SET
  clause).
  
  We also use new approach for LOAD DATA binlogging/replication.
sql/sql_parse.cc:
  mysql_execute_command():
    Since now we have SET clause in LOAD DATA we should also check
    permissions for tables used in its expressions. Also mysql_load()
    has two more arguments to pass information about this clause.
sql/sql_repl.cc:
  New way of replicating LOAD DATA. Now we do it similarly to other
  queries. We store LOAD DATA query in new Execute_load_query event
  (which is last in the sequence of events representing LOAD DATA).
  When we are executing this event we simply rewrite part of query which
  holds name of file (we use name of temporary file) and then execute it
  as usual query. In the beggining of this sequence we use Begin_load_query
  event which is almost identical to Append_file event.
sql/sql_repl.h:
  struct st_load_file_info:
    Removed memebers which are no longer needed for LOAD DATA binnlogging.
sql/sql_yacc.yy:
  Added support for extended LOAD DATA syntax. Now one can use
  user variables as target for data loaded from file (besides table's 
  columns). Also LOAD DATA got new SET-clause in which one can specify
  values for table columns as expressions.
  
  For example the following is possible:
  LOAD DATA INFILE 'words.dat' INTO TABLE t1 (a, @b) SET c = @b + 1;
  
  Also now we save pointers to the beginning and to the end of part of 
  LOAD DATA statement which should be rewritten during replication.

23 files changed, 118 insertions, 46 deletions

diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 7d34c4504bd..151e2cf5464 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -338,7 +338,8 @@ create table t2 (c char(30)) charset=ucs2;
 set @v=convert('abc' using ucs2);
 reset master;
 insert into t2 values (@v);
-show binlog events from 96;
+--replace_column 2 # 5 #
+show binlog events from 98;
 # more important than SHOW BINLOG EVENTS, mysqlbinlog (where we
 # absolutely need variables names to be quoted and strings to be
 # escaped).
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index a5b163b3533..371df339b57 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -85,6 +85,7 @@ insert into t1 select * from t2;
 # verify the binlog :
 let $VERSION=`select version()`;
 --replace_result $VERSION VERSION
+--replace_column 2 # 5 #
 show binlog events;
 select * from t1;
 drop table t1, t2;
@@ -99,6 +100,7 @@ create table t2(unique(a)) select a from t1;
 # The above should produce an error, *and* not appear in the binlog
 let $VERSION=`select version()`;
 --replace_result $VERSION VERSION
+--replace_column 2 # 5 #
 show binlog events;
 drop table t1;
 
diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test
index aa0ea0a2f55..d384b955212 100644
--- a/mysql-test/t/loaddata.test
+++ b/mysql-test/t/loaddata.test
@@ -31,3 +31,37 @@ load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated
 select * from t1;
 drop table t1;
 
+#
+# Let us test extended LOAD DATA features
+#
+create table t1 (a int default 100, b int, c varchar(60));
+# we can do something like this
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b);
+select * from t1;
+truncate table t1;
+# we can use filled fields in expressions 
+# we also assigning NULL value to field with non-NULL default here
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a);
+select * from t1;
+truncate table t1;
+# we even can use variables in set clause, and missed columns will be set
+# with default values
+set @c:=123;
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b);
+select * from t1;
+# let us test side-effect of such load
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, @b);
+select * from t1;
+select @a, @b;
+truncate table t1;
+# now going to test fixed field-row file format
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
+select * from t1;
+truncate table t1;
+# this also should work
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c=concat(a,"+",b,"+",@c,"+",b,"+",if(c is null,"NIL",c));
+select * from t1;
+# and this should bark
+--error 1409 
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);
+drop table t1;
diff --git a/mysql-test/t/mix_innodb_myisam_binlog.test b/mysql-test/t/mix_innodb_myisam_binlog.test
index 08a81c850eb..9894359dfc0 100644
--- a/mysql-test/t/mix_innodb_myisam_binlog.test
+++ b/mysql-test/t/mix_innodb_myisam_binlog.test
@@ -27,7 +27,7 @@ commit;
 
 --replace_column 5 #
 --replace_result "xid=12" "xid=7"
-show binlog events from 96;
+show binlog events from 98;
 
 delete from t1;
 delete from t2;
@@ -40,7 +40,7 @@ insert into t2 select * from t1;
 rollback;
 
 --replace_column 5 #
-show binlog events from 96;
+show binlog events from 98;
 
 delete from t1;
 delete from t2;
@@ -56,7 +56,7 @@ commit;
 
 --replace_column 5 #
 --replace_result "xid=45" "xid=24"
-show binlog events from 96;
+show binlog events from 98;
 
 delete from t1;
 delete from t2;
@@ -74,7 +74,7 @@ select a from t1 order by a; # check that savepoints work :)
 
 --replace_column 5 #
 --replace_result "xid=67" "xid=36"
-show binlog events from 96;
+show binlog events from 98;
 
 # and when ROLLBACK is not explicit?
 delete from t1;
@@ -95,7 +95,7 @@ connection con2;
 # logging has been done, we use a user lock.
 select get_lock("a",10);
 --replace_column 5 #
-show binlog events from 96;
+show binlog events from 98;
 
 # and when not in a transact1on?
 delete from t1;
@@ -107,7 +107,7 @@ insert into t2 select * from t1;
 
 --replace_column 5 #
 --replace_result "xid=116" "xid=59"
-show binlog events from 96;
+show binlog events from 98;
 
 # Check that when the query updat1ng the MyISAM table is the first in the
 # transaction, we log it immediately.
@@ -120,13 +120,13 @@ begin;
 insert into t2 select * from t1;
 --replace_column 5 #
 --replace_result "xid=130" "xid=65"
-show binlog events from 96;
+show binlog events from 98;
 insert into t1 values(11);
 commit;
 
 --replace_column 5 #
 --replace_result "xid=130" "xid=65" "xid=133" "xid=67"
-show binlog events from 96;
+show binlog events from 98;
 
 
 # Check that things work like before this BEGIN/ROLLBACK code was added,
@@ -145,7 +145,7 @@ commit;
 
 --replace_column 5 #
 --replace_result "xid=152" "xid=77"
-show binlog events from 96;
+show binlog events from 98;
 
 delete from t1;
 delete from t2;
@@ -157,7 +157,7 @@ insert into t2 select * from t1;
 rollback;
 
 --replace_column 5 #
-show binlog events from 96;
+show binlog events from 98;
 
 delete from t1;
 delete from t2;
@@ -173,7 +173,7 @@ commit;
 
 --replace_column 5 #
 --replace_result "xid=184" "xid=93"
-show binlog events from 96;
+show binlog events from 98;
 
 delete from t1;
 delete from t2;
@@ -191,7 +191,7 @@ select a from t1 order by a; # check that savepoints work :)
 
 --replace_column 5 #
 --replace_result "xid=205" "xid=104"
-show binlog events from 96;
+show binlog events from 98;
 
 # Test for BUG#5714, where a MyISAM update in the transaction used to
 # release row-level locks in InnoDB
diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test
index 3f2ab2b31ec..df3b0acaa11 100644
--- a/mysql-test/t/mysqlbinlog.test
+++ b/mysql-test/t/mysqlbinlog.test
@@ -24,7 +24,6 @@ load data infile '../../std_data/words.dat' into table t1;
 load data infile '../../std_data/words.dat' into table t1;
 load data infile '../../std_data/words.dat' into table t1;
 load data infile '../../std_data/words.dat' into table t1;
-load data infile '../../std_data/words.dat' into table t1;
 # simple query to show more in second binlog
 insert into t1 values ("Alas");
 flush logs;
@@ -61,7 +60,7 @@ select "--- --database --" as "";
 select "--- --position --" as "";
 --enable_query_log
 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
---exec $MYSQL_BINLOG --short-form --local-load=$MYSQL_TEST_DIR/var/tmp/ --position=119 $MYSQL_TEST_DIR/var/log/master-bin.000002
+--exec $MYSQL_BINLOG --short-form --local-load=$MYSQL_TEST_DIR/var/tmp/ --position=232 $MYSQL_TEST_DIR/var/log/master-bin.000002
 
 # These are tests for remote binlog.
 # They should return the same as previous test.
@@ -93,7 +92,7 @@ select "--- --database --" as "";
 select "--- --position --" as "";
 --enable_query_log
 --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
---exec $MYSQL_BINLOG --short-form --local-load=$MYSQL_TEST_DIR/var/tmp/ --read-from-remote-server --position=119 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002
+--exec $MYSQL_BINLOG --short-form --local-load=$MYSQL_TEST_DIR/var/tmp/ --read-from-remote-server --position=232 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002
 
 # Bug#7853 (mysqlbinlog does not accept input from stdin)
 --disable_query_log
diff --git a/mysql-test/t/mysqlbinlog2.test b/mysql-test/t/mysqlbinlog2.test
index f2360d51c16..74ddf4e2be4 100644
--- a/mysql-test/t/mysqlbinlog2.test
+++ b/mysql-test/t/mysqlbinlog2.test
@@ -46,11 +46,11 @@ select "--- offset --" as "";
 --disable_query_log
 select "--- start-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --start-position=602 $MYSQL_TEST_DIR/var/log/master-bin.000001 
+--exec $MYSQL_BINLOG --short-form --start-position=604 $MYSQL_TEST_DIR/var/log/master-bin.000001 
 --disable_query_log
 select "--- stop-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --stop-position=602 $MYSQL_TEST_DIR/var/log/master-bin.000001 
+--exec $MYSQL_BINLOG --short-form --stop-position=604 $MYSQL_TEST_DIR/var/log/master-bin.000001 
 --disable_query_log
 select "--- start-datetime --" as "";
 --enable_query_log
@@ -75,11 +75,11 @@ select "--- offset --" as "";
 --disable_query_log
 select "--- start-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --start-position=602 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002
+--exec $MYSQL_BINLOG --short-form --start-position=604 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002
 --disable_query_log
 select "--- stop-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --stop-position=124 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002
+--exec $MYSQL_BINLOG --short-form --stop-position=126 $MYSQL_TEST_DIR/var/log/master-bin.000001 $MYSQL_TEST_DIR/var/log/master-bin.000002
 --disable_query_log
 select "--- start-datetime --" as "";
 --enable_query_log
@@ -102,11 +102,11 @@ select "--- offset --" as "";
 --disable_query_log
 select "--- start-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --start-position=602 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 
+--exec $MYSQL_BINLOG --short-form --start-position=604 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 
 --disable_query_log
 select "--- stop-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --stop-position=602 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 
+--exec $MYSQL_BINLOG --short-form --stop-position=604 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 
 --disable_query_log
 select "--- start-datetime --" as "";
 --enable_query_log
@@ -129,11 +129,11 @@ select "--- offset --" as "";
 --disable_query_log
 select "--- start-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --start-position=602 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001  master-bin.000002
+--exec $MYSQL_BINLOG --short-form --start-position=604 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001  master-bin.000002
 --disable_query_log
 select "--- stop-position --" as "";
 --enable_query_log
---exec $MYSQL_BINLOG --short-form --stop-position=124 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001  master-bin.000002
+--exec $MYSQL_BINLOG --short-form --stop-position=126 --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001  master-bin.000002
 --disable_query_log
 select "--- start-datetime --" as "";
 --enable_query_log
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 89f578dbb20..7264e6bbc3e 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1,5 +1,5 @@
 --disable_warnings
-DROP TABLE IF EXISTS t1, `"t"1`, t1aa,t2aa;
+DROP TABLE IF EXISTS t1, `"t"1`, t1aa, t2, t2aa;
 drop database if exists mysqldump_test_db;
 drop view if exists v1;
 --enable_warnings
diff --git a/mysql-test/t/rpl_charset.test b/mysql-test/t/rpl_charset.test
index d2c195dfdbf..ee54bc72a65 100644
--- a/mysql-test/t/rpl_charset.test
+++ b/mysql-test/t/rpl_charset.test
@@ -107,7 +107,7 @@ connection master;
 drop database mysqltest2;
 drop database mysqltest3;
 --replace_column 2 # 5 #
-show binlog events from 96;
+show binlog events from 98;
 sync_slave_with_master;
 
 # Check that we can change global.collation_server (since 5.0.3)
diff --git a/mysql-test/t/rpl_deadlock.test b/mysql-test/t/rpl_deadlock.test
index aa474f79963..a196495e834 100644
--- a/mysql-test/t/rpl_deadlock.test
+++ b/mysql-test/t/rpl_deadlock.test
@@ -68,7 +68,7 @@ show slave status;
 # 2) Test lock wait timeout
 
 stop slave;
-change master to master_log_pos=534; # the BEGIN log event
+change master to master_log_pos=536; # the BEGIN log event
 begin;
 select * from t2 for update; # hold lock
 start slave;
@@ -89,7 +89,7 @@ set global max_relay_log_size=0;
 
 # This is really copy-paste of 2) of above
 stop slave;
-change master to master_log_pos=534;
+change master to master_log_pos=536;
 begin;
 select * from t2 for update;
 start slave;
diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test
index a520024b7da..5388b6af8d4 100644
--- a/mysql-test/t/rpl_error_ignored_table.test
+++ b/mysql-test/t/rpl_error_ignored_table.test
@@ -48,7 +48,8 @@ connection master;
 --error 0,1053;
 reap;
 connection master1;
-show binlog events from 96;
+--replace_column 2 # 5 #
+show binlog events from 98;
 save_master_pos;
 connection slave;
 # SQL slave thread should not have stopped (because table of the killed
diff --git a/mysql-test/t/rpl_flush_tables.test b/mysql-test/t/rpl_flush_tables.test
index b98235fbed8..eb16ddcd8de 100644
--- a/mysql-test/t/rpl_flush_tables.test
+++ b/mysql-test/t/rpl_flush_tables.test
@@ -18,6 +18,7 @@ rename table t1 to t5, t2 to t1;
 flush no_write_to_binlog tables;
 # Check that it's not in the binlog.
 --replace_result $SERVER_VERSION SERVER_VERSION
+--replace_column 2 # 5 #
 show binlog events;
 # Check that the master is not confused.
 select * from t3;
@@ -25,6 +26,7 @@ select * from t3;
 flush tables;
 # Check that it's in the binlog.
 --replace_result $SERVER_VERSION SERVER_VERSION
+--replace_column 2 # 5 #
 show binlog events;
 save_master_pos;
 connection slave;
diff --git a/mysql-test/t/rpl_loaddata.test b/mysql-test/t/rpl_loaddata.test
index 487ad4a7aa6..d1404dea211 100644
--- a/mysql-test/t/rpl_loaddata.test
+++ b/mysql-test/t/rpl_loaddata.test
@@ -36,8 +36,7 @@ select * from t3;
 # But we can't simply read this binlog, because as the slave has not been
 # restarted for this test, the file_id is uncertain (would cause test
 # failures). So instead, we test if the binlog looks long enough to
-# contain LOAD DATA. That is, I (Guilhem) have done SHOW BINLOG EVENTS on my
-# machine, saw that the binlog is of size 1068 (in 5.0.0) when things go fine.
+# contain LOAD DATA. Since 5.0.3 we assume that binlog of 1292 is ok.
 # If LOAD DATA was not logged, the binlog would be shorter.
 show master status;
 
@@ -84,7 +83,9 @@ set sql_log_bin=1;
 load data infile '../../std_data/rpl_loaddata.dat' into table t1;
 save_master_pos;
 connection slave;
-# The SQL slave thread should be stopped now.
+# The SQL slave thread should be stopped now. 
+# Exec_Master_Log_Pos should point to the start of Execute event
+# for last load data.
 wait_for_slave_to_stop;
 
 # CHANGE MASTER and see if error is cleared in SHOW SLAVE STATUS.
diff --git a/mysql-test/t/rpl_loaddata_rule_m.test b/mysql-test/t/rpl_loaddata_rule_m.test
index ff00e8f4925..97c984c4369 100644
--- a/mysql-test/t/rpl_loaddata_rule_m.test
+++ b/mysql-test/t/rpl_loaddata_rule_m.test
@@ -19,5 +19,9 @@ create database mysqltest;
 create table t1(a int, b int, unique(b));
 use mysqltest;
 load data infile '../../std_data/rpl_loaddata.dat' into table test.t1;
-show binlog events from 96; # should be nothing
+# Starting from 5.0.3 LOAD DATA is replicated much in the same way as ordinary
+# query so "show binlog ..." should show two events (before 5.0.3 no events
+# were returned).
+--replace_column 2 # 5 #
+show binlog events from 98;
 drop database mysqltest;
diff --git a/mysql-test/t/rpl_loaddata_rule_s.test b/mysql-test/t/rpl_loaddata_rule_s.test
index 71a3929da32..9802c00d35f 100644
--- a/mysql-test/t/rpl_loaddata_rule_s.test
+++ b/mysql-test/t/rpl_loaddata_rule_s.test
@@ -17,4 +17,4 @@ save_master_pos;
 connection slave;
 sync_with_master;
 select count(*) from t1; # check that LOAD was replicated
-show binlog events from 96; # should be nothing
+show binlog events from 98; # should be nothing
diff --git a/mysql-test/t/rpl_loaddatalocal.test b/mysql-test/t/rpl_loaddatalocal.test
index 70f4ab96b6a..f9325b39af6 100644
--- a/mysql-test/t/rpl_loaddatalocal.test
+++ b/mysql-test/t/rpl_loaddatalocal.test
@@ -34,3 +34,27 @@ drop table t1;
 save_master_pos;
 connection slave;
 sync_with_master;
+
+#
+# Now let us test how well we replicate LOAD DATA LOCAL in situation when
+# we met duplicates in tables to which we are adding rows.
+# (It supposed that LOAD DATA LOCAL ignores such errors)
+#
+connection master;
+create table t1(a int);
+insert into t1 values (1), (2), (2), (3);
+select * into outfile '../../var/master-data/rpl_loaddatalocal.select_outfile' from t1;
+drop table t1;
+create table t1(a int primary key);
+load data local infile './var/master-data/rpl_loaddatalocal.select_outfile' into table t1;
+system rm ./var/master-data/rpl_loaddatalocal.select_outfile ;
+select * from t1;
+save_master_pos;
+connection slave;
+sync_with_master;
+select * from t1;
+connection master;
+drop table t1;
+save_master_pos;
+connection slave;
+sync_with_master;
diff --git a/mysql-test/t/rpl_log.test b/mysql-test/t/rpl_log.test
index 055c1a7c93a..8cb99d5432e 100644
--- a/mysql-test/t/rpl_log.test
+++ b/mysql-test/t/rpl_log.test
@@ -38,9 +38,9 @@ select count(*) from t1;
 drop table t1;
 --replace_result $VERSION VERSION
 show binlog events;
-show binlog events from 96 limit 1;
-show binlog events from 96 limit 2;
-show binlog events from 96 limit 2,1;
+show binlog events from 98 limit 1;
+show binlog events from 98 limit 2;
+show binlog events from 98 limit 2,1;
 flush logs;
 
 # We need an extra update before doing save_master_pos.
diff --git a/mysql-test/t/rpl_log_pos.test b/mysql-test/t/rpl_log_pos.test
index c9d7056924f..2a03497846b 100644
--- a/mysql-test/t/rpl_log_pos.test
+++ b/mysql-test/t/rpl_log_pos.test
@@ -38,7 +38,7 @@ insert into t1 values (1),(2),(3);
 save_master_pos;
 connection slave;
 stop slave;
-change master to master_log_pos=96;
+change master to master_log_pos=98;
 start slave;
 sync_with_master;
 select * from t1;
diff --git a/mysql-test/t/rpl_multi_query.test b/mysql-test/t/rpl_multi_query.test
index fbe6d8057f5..2ddd61ce7ba 100644
--- a/mysql-test/t/rpl_multi_query.test
+++ b/mysql-test/t/rpl_multi_query.test
@@ -24,6 +24,6 @@ sync_slave_with_master;
 select * from mysqltest.t1;
 connection master;
 --replace_column 2 # 5 #
-show binlog events from 96;
+show binlog events from 98;
 drop database mysqltest;
 sync_slave_with_master;
diff --git a/mysql-test/t/rpl_temporary.test b/mysql-test/t/rpl_temporary.test
index f84c9b09aef..e65469de7e0 100644
--- a/mysql-test/t/rpl_temporary.test
+++ b/mysql-test/t/rpl_temporary.test
@@ -82,6 +82,7 @@ drop temporary table t3;
 select * from t2;
 
 --replace_result $VERSION VERSION
+--replace_column 2 # 5 #
 show binlog events;
 
 drop table t1, t2;
diff --git a/mysql-test/t/rpl_timezone.test b/mysql-test/t/rpl_timezone.test
index ebb58a9c880..3c180ca8849 100644
--- a/mysql-test/t/rpl_timezone.test
+++ b/mysql-test/t/rpl_timezone.test
@@ -32,6 +32,7 @@ select * from t1;
 connection master;
 # We should not see SET ONE_SHOT time_zone before second insert
 --replace_result $VERSION VERSION
+--replace_column 2 # 5 #
 show binlog events;
 
 #
diff --git a/mysql-test/t/rpl_until.test b/mysql-test/t/rpl_until.test
index 318cca600a3..9ee1dca9c11 100644
--- a/mysql-test/t/rpl_until.test
+++ b/mysql-test/t/rpl_until.test
@@ -24,7 +24,7 @@ show binlog events;
 
 # try to replicate all queries until drop of t1
 connection slave;
-start slave until master_log_file='master-bin.000001', master_log_pos=304;
+start slave until master_log_file='master-bin.000001', master_log_pos=321;
 sleep 2;
 # here table should be still not deleted
 select * from t1;
@@ -42,7 +42,7 @@ sleep 2;
 show slave status;
 
 # try replicate all until second insert to t2;
-start slave until relay_log_file='slave-relay-bin.000004', relay_log_pos=710;
+start slave until relay_log_file='slave-relay-bin.000004', relay_log_pos=751;
 sleep 4;
 select * from t2;
 --replace_result $MASTER_MYPORT MASTER_MYPORT
@@ -58,7 +58,7 @@ sync_with_master;
 stop slave;
 
 # this should stop immediately as we are already there
-start slave until master_log_file='master-bin.000001', master_log_pos=710;
+start slave until master_log_file='master-bin.000001', master_log_pos=783;
 # 2 is not enough when running with valgrind
 real_sleep 4
 # here the sql slave thread should be stopped
@@ -77,6 +77,6 @@ start slave until master_log_file='master-bin.000001';
 start slave until relay_log_file='slave-relay-bin.000002';
 --error 1277
 start slave until relay_log_file='slave-relay-bin.000002', master_log_pos=561;
-
+# Warning should be given for second command
 start slave sql_thread;
-start slave until master_log_file='master-bin.000001', master_log_pos=710;
+start slave until master_log_file='master-bin.000001', master_log_pos=783;
diff --git a/mysql-test/t/rpl_user_variables.test b/mysql-test/t/rpl_user_variables.test
index 47bc9de37ba..711d83219a6 100644
--- a/mysql-test/t/rpl_user_variables.test
+++ b/mysql-test/t/rpl_user_variables.test
@@ -46,7 +46,8 @@ save_master_pos;
 connection slave;
 sync_with_master;
 select * from t1;
-show binlog events from 96;
+--replace_column 2 # 5 #
+show binlog events from 98;
 connection master;
 drop table t1;
 save_master_pos;
diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test
index 07076f1f6f4..d8dfc10bfe4 100644
--- a/mysql-test/t/user_var.test
+++ b/mysql-test/t/user_var.test
@@ -108,7 +108,8 @@ INSERT INTO t1 VALUES(@`a b`);
 set @var1= "';aaa";
 SET @var2=char(ascii('a'));
 insert into t1 values (@var1),(@var2);
-show binlog events from 96;
+--replace_column 2 # 5 #
+show binlog events from 98;
 # more important than SHOW BINLOG EVENTS, mysqlbinlog (where we
 # absolutely need variables names to be quoted and strings to be
 # escaped).