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:
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

[9 Nov 2005 9:42] Ghadi Rayess
Suppose we created the following procedure:

	START TRANSACTION;    -------- Command 1
	INTO OUTFILE '/export/home/ppas/ppas_billing/statistics/temp_file.txt'
	FROM BILLING_TABLE FOR UPDATE; ---------- Command 2
	DELETE FROM BILLING_TABLE; ---------- Command 3
	COMMIT; ---------- Command 4

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
[13 Nov 2005 12:01] Valeriy Kravchuk
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...
[14 Nov 2005 6:50] Ghadi Rayess
MySQL version is 5.0.15... i've mistakenly put the wrong one before...
[20 Nov 2005 13:04] Valeriy Kravchuk
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

[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*/;
# 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
# 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.
create procedure to_file()
  start transaction;
  select *
  into outfile '/tmp/temp_file.txt'
  fields terminated by '\t'
  lines terminated by '\n'
  from t1 for update;
  delete from t1;
# 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 */;
[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.
[21 Jul 2006 18:02] Jim Winstead
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.


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