Bug #38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
Submitted: 16 Jul 2008 14:50 Modified: 18 Mar 2009 15:17
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1, 6.0 bzr OS:Any
Assigned to: Alfranio Tavares Correia Junior CPU Architecture:Any

[16 Jul 2008 14:50] Philip Stoev 
Description:
If secure-file-priv is set on slave, it becomes unable to execute LOAD DATA INFILE statements sent from master using statement-based replication.

The slave error log says:

080716 17:45:14 [ERROR] Slave SQL: Error 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement' on query. Default database: 'test'. Query: 'LOAD DATA INFILE '../tmp/SQL_LOAD-2-1-1.data' REPLACE INTO TABLE C', Error_code: 1290
080716 17:45:14 [Warning] Slave: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement Error_code: 1290
080716 17:45:14 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 105677

5.1 does not appear to be affected.

How to repeat:
A test case will be uploaded shortly.

Suggested fix:
It appears that the slave thread places the data obtained from the master in a temporary file in a directory named "../tmp/" (?), which is not the one specified with --secure-file-priv. The solution would be to place the temp files in the allowed directory and/or permanently allow that other directory as well.
[16 Jul 2008 15:24] Philip Stoev 
Test file for bug 38174

Attachment: rpl_bug38174.test (application/octet-stream, text), 4.15 KiB.
[16 Jul 2008 15:25] Philip Stoev 
OPT file for bug 38174

Attachment: rpl_bug38174-slave.opt (text/plain), 24 bytes.
[17 Jul 2008 7:25] Sveta Smirnova 
Thank you for the report.

Verified as described with little change: SET BINLOG_FORMAT = 'statement'; -> SET BINLOG_FORMAT = 'mixed'; Version 5.1 is affected as well.

Workaround: SET BINLOG_FORMAT = 'row';
[17 Jul 2008 7:27] Sveta Smirnova 
I think another solution could be using row binlog format in mixed mode.
[5 Feb 2009 21:02] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65409

2768 Alfranio Correia	2009-02-05
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
      
      If secure-file-priv was set on slave, it became unable to execute
      LOAD DATA INFILE statements sent from master using mixed or
      statement-based replication.
      
      This patch fixes the issue by ignoring this security restriction
      while executing the SQL Thread.
[9 Feb 2009 21:39] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65664

2768 Alfranio Correia	2009-02-09
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
            
      If secure-file-priv was set on slave, it became unable to execute
      LOAD DATA INFILE statements sent from master using mixed or
      statement-based replication.
            
      This patch fixes the issue by forcing that the temporary files are always
      created under the --secure-file-priv unless --secure-file-priv is defined.
[11 Feb 2009 15:39] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65924

2768 Alfranio Correia	2009-02-11
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
      
      If secure-file-priv was set on slave, it became unable to execute
      LOAD DATA INFILE statements sent from master using mixed or
      statement-based replication.
      
      This patch fixes the issue by ignoring this security restriction
      and checking if the files are created and read by the slave in the
      --slave-load-tmpdir while executing the SQL Thread.
[12 Feb 2009 6:51] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65986

2769 Alfranio Correia	2009-02-12
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
[12 Feb 2009 16:57] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66063

2769 Alfranio Correia	2009-02-12
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
      
      Post-fix.
[13 Feb 2009 14:00] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66216

2768 Alfranio Correia	2009-02-13
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
            
      If secure-file-priv was set on slave, it became unable to execute
      LOAD DATA INFILE statements sent from master using mixed or
      statement-based replication.
            
      This patch fixes the issue by ignoring this security restriction
      and checking if the files are created and read by the slave in the
      --slave-load-tmpdir while executing the SQL Thread.
[14 Feb 2009 19:08] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66357

2768 Alfranio Correia	2009-02-14
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
                  
      If secure-file-priv was set on slave, it became unable to execute
      LOAD DATA INFILE statements sent from master using mixed or
      statement-based replication.
                  
      This patch fixes the issue by ignoring this security restriction
      and checking if the files are created and read by the slave in the
      --slave-load-tmpdir while executing the SQL Thread.
[14 Feb 2009 19:32] Alfranio Tavares Correia Junior 
Please, check also http://bugs.mysql.com/42861
[21 Feb 2009 9:36] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67083

2811 Alfranio Correia	2009-02-21
      BUG#38174 secure-file-priv breaks LOAD DATA INFILE replication in statement mode
                        
      If secure-file-priv was set on slave, it became unable to execute
      LOAD DATA INFILE statements sent from master using mixed or
      statement-based replication.
                        
      This patch fixes the issue by ignoring this security restriction
      and checking if the files are created and read by the slave in the
      --slave-load-tmpdir while executing the SQL Thread.
[21 Feb 2009 10:29] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67084

3072 Alfranio Correia	2009-02-21 [merge]
      BUG#38174 merge 5.1-bugteam --> 6.0-bugteam
[22 Feb 2009 13:41] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67109

2812 Alfranio Correia	2009-02-22
      Post-fix for BUG#38174.
[13 Mar 2009 19:04] Bugs System 
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:azundris@mysql.com-20090224070618-mr7stu6rfcvoj18g) (merge vers: 5.1.33) (pib:6)
[15 Mar 2009 10:18] Jon Stephens 
Documented bugfix in the 5.1.33 changelog as follows:

        If --secure-file-priv was set on the slave, it was unable to
        execute LOAD DATA INFILE statements sent from the master when
        using mixed-format or statement-based replication.

        As a result of this fix, this security restriction is now
        ignored on the slave in such cases; instead the slave checks
        whether the files were created and should be read by the slave
        in its slave_load_tmpdir.

Set bug status to NDI pending merge of fix to 6.0 tree.
[18 Mar 2009 13:18] Bugs System 
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:azundris@mysql.com-20090223123708-n9rf2to3g15br7za) (merge vers: 6.0.10-alpha) (pib:6)
[18 Mar 2009 15:17] Jon Stephens 
Fix also documented in the 6.0.11 changelog; closed.
[9 May 2009 16:42] Bugs System 
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:39] Bugs System 
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:36] Bugs System 
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)