MySQL Bugs: #14782: SELECT INTO OUTFILE is not replicated on the SLAVE

Bug #14782	SELECT INTO OUTFILE is not replicated on the SLAVE
Submitted:	9 Nov 2005 9:42	Modified:	21 Jul 2006 18:02
Reporter:	Ghadi Rayess	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.17-BK, mysql-max-5.0.15-solaris8-sparc-64bit	OS:	Linux (Linux, Solaris)
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
Suppose we created the following procedure:

DELIMITER //
CREATE PROCEDURE PPAS.Billing_To_File()
BEGIN
	START TRANSACTION;    -------- Command 1
	SELECT *
	INTO OUTFILE '/export/home/ppas/ppas_billing/statistics/temp_file.txt'
	FIELDS TERMINATED BY '\t'
	LINES TERMINATED BY '\n'
	FROM BILLING_TABLE FOR UPDATE; ---------- Command 2
	DELETE FROM BILLING_TABLE; ---------- Command 3
	COMMIT; ---------- Command 4
END;
//

temp_file.txt will be created at the Master system and not at the Slave. Although Command 3 is executed at the Slave Database! 

How to repeat:
1 - Set up a Master - Slave databases.
2 - create the database PPAS and a table BILLING_TABLE
2 - create the same directories at the Master and Slave systems. (/export/home/ppas/ppas_billing/statistcs/)
3 - makes the owner of statistics to mysql
4 - create the procedure PPAS.Billing_To_File
5 - call the procedure PPAS.Bililng_To_File

Thank you for a problem report. Looks like a mysql version in you report is wrong. 4.1.12 does not support stored procedures at all. 

So, please, inform about the exact versions of master and slave used and send the my.cnf content from both master and slave. The following page from the manual, http://dev.mysql.com/doc/refman/5.0/en/replication-bugs.html, may also give you some hints...

MySQL version is 5.0.15... i've mistakenly put the wrong one before...

Looks like I was able to verify the problem on 5.0.17-BK (ChangeSet@1.1969, 2005-11-17 15:08:49+01:00,) on Linux. 

I started the server with binlog to check what will be written in it (if there is nothing from SELECT INTO OUTFILE... in the binlog, then it is surely not replicated):

[openxs@Fedora 5.0]$ bin/mysqld_safe --log-bin=binlog &
[1] 2593
[openxs@Fedora 5.0]$ Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_test      |
+---------------------+
| RawData             |
| ReplicationLog      |
...
| t1                  |
...
| z                   |
+---------------------+
69 rows in set (0,04 sec)

So, we have a table, t1:

mysql> select * from t1;
+------+
| a    |
+------+
|  500 |
+------+
1 row in set (0,02 sec)

with one row. I had created a procedure similar to yours then, but working with this t1 table:

mysql> delimiter //
mysql> create procedure to_file()
    -> begin
    ->   start transaction;
    ->   select *
    ->   into outfile '/tmp/temp_file.txt'
    ->   fields terminated by '\t'
    ->   lines terminated by '\n'
    ->   from t1 for update;
    ->   delete from t1;
    ->   commit;
    -> end//
Query OK, 0 rows affected (0,02 sec)

And executed it successfully:

mysql> call to_file()//
Query OK, 0 rows affected (0,03 sec)

mysql> delimiter ;
mysql> select * from t1;
Empty set (0,00 sec)

mysql> exit
Bye

[openxs@Fedora 5.0]$ bin/mysqladmin -uroot shutdown

The file with data was created successfully. Let's examine the binlog (the last one is binlog.000002):

[openxs@Fedora 5.0]$ bin/mysqlbinlog var/binlog.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#051120 15:29:05 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.
17-log created 051120 15:29:05 at startup
ROLLBACK;
# at 98
#051120 15:32:26 server id 1  end_log_pos 370   Query   thread_id=1     exec_time=0     error_code=0
use test;
SET TIMESTAMP=1132493546;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.
collation_server=8;
create procedure to_file()
begin
  start transaction;
  select *
  into outfile '/tmp/temp_file.txt'
  fields terminated by '\t'
  lines terminated by '\n'
  from t1 for update;
  delete from t1;
  commit;
end;
# at 370
#051120 15:32:53 server id 1  end_log_pos 455   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1132493573;
SET @@session.time_zone='SYSTEM';
delete from t1;
# at 455
#051120 15:49:00 server id 1  end_log_pos 474   Stop
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[openxs@Fedora 5.0]$

So, looks like the only result from call to_file() is:

delete from t1;

It can be replicated, but nothing else. 

May be, it is the intended behavior (not to replicate SELECT INTO OUTFILE... It is also not written into the binary log outside the SP, by the way). But it should be documented in the prominent place. So, this report is a documentation request, at least.

This is not a bug. As the documentation says:

  MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in its binary logs. ... Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data.

http://dev.mysql.com/doc/refman/5.0/en/replication-implementation.html

SELECT ... INTO OUTFILE does not update the database, so it is never replicated. DELETE does, so it is.