Description:
In check_temp_dir(), mysqld creates a temporary file and removes it immediately, without protecting any mutex/lockfile/etc. 
--------------
  /*
    Check if the directory exists.
   */
  if (!(dirp=my_dir(tmp_dir,MYF(MY_WME))))
    DBUG_RETURN(1);
  my_dirend(dirp);

  /*
    Check permissions to create a file.
   */
  if ((fd= mysql_file_create(key_file_misc,
                             tmp_file, CREATE_MODE,
                             O_WRONLY | O_BINARY | O_EXCL | O_NOFOLLOW,
                             MYF(MY_WME))) < 0)
  DBUG_RETURN(1);

  /*
    Clean up.
   */
  mysql_file_close(fd, MYF(0));
--------------

check_temp_dir() is called at initializing SQL thread. 
  if (check_temp_dir(rli->slave_patternload_file))
The filename is fixed to SQL_LOAD-.

A problem might happen when multiple mysqld instances on the same host start SQL thread at the same time. SQL thread might abort with the following error.

110802 00:00:00 [ERROR] Slave SQL: Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 17), Error_code: 1

Setting tmpdir separately from each mysqld instance is certainly a workaround for this issue, but this should be a problem anyway.

How to repeat:
See above

Thank you for the problem report. Verified by code review.

Documented fix in the 5.6.5 changelog as follows:

      A race condition could occur when running multiple instances of mysqld on
      a single machine, when more than slave thread was started at the same
      time, and each such thread tried to use the same temporary file.

Closed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

2016-03-09 10:22:28 15680 [Note] Event Scheduler: Loaded 0 events
2016-03-09 10:22:28 15680 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2016-03-09 10:22:28 15680 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 151, relay log './t1-relay-bin.000001' position: 4
2016-03-09 10:22:28 15680 [ERROR] Slave SQL: Unable to use slave's temporary directory /tmp                                                          | - Can't read dir of '/tmp                                                          |/' (Errcode: 2 - No such file or directory), Error_code: 12

Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 17)
   
   

   
   

    
    


     
     
这个错误时在Mysql主从配置产生的，最后找到这个Mysql的一个bug

     
     
http://bugs.mysql.com/bug.php?id=62055

     
     
bug的主要原因是：打开文件的函数中指定打开模式时，如果O_CREAT和O_EXCL同时指定，那么当文件存在时会导致打开文件出错，这个使用方法本来也没有什么错误，但是当使用Mysql主从备份机制，在一台服务器上安装多个mysqld实例时，就会出问题，代码在Mysql源码中/sql/slave.cc文件中，Mysql5.1.68是在2904行

     
     


      
      

       
       
      
      

      
      
/*
  Check the temporary directory used by commands like
  LOAD DATA INFILE.
 */
static 
int check_temp_dir(char* tmp_file)
{
  int fd;
  MY_DIR *dirp;
  char tmp_dir[FN_REFLEN];
  size_t tmp_dir_size;

  DBUG_ENTER("check_temp_dir");

  /*
    Get the directory from the temporary file.
  */
  dirname_part(tmp_dir, tmp_file, &tmp_dir_size);

  /*
    Check if the directory exists.
   */
  if (!(dirp=my_dir(tmp_dir,MYF(MY_WME))))
    DBUG_RETURN(1);
  my_dirend(dirp);

  /*
    Check permissions to create a file.
   */
  if ((fd= my_create(tmp_file, CREATE_MODE,
                     O_WRONLY | O_BINARY | O_EXCL | O_NOFOLLOW,
                     MYF(MY_WME))) < 0)
  DBUG_RETURN(1);

  /*
    Clean up.
   */
  my_close(fd, MYF(0));
  my_delete(tmp_file, MYF(0));

  DBUG_RETURN(0);
}

      
      

       
       
      
      

     
     

     
     
上面红色的是调用了一个函数打开文件，my_create，在这个函数中第三个参数传递了O_EXCL，但是并没有O_CREAT，下面继续看my_create函数，它在/mysys/my_create.c文件中定义

     
     


      
      

       
       
      
      

      
      
File my_create(const char *FileName, int CreateFlags, int access_flags,
           myf MyFlags)
{
  int fd, rc;
  DBUG_ENTER("my_create");
  DBUG_PRINT("my",("Name: '%s' CreateFlags: %d  AccessFlags: %d  MyFlags: %d",
           FileName, CreateFlags, access_flags, MyFlags));

#if !defined(NO_OPEN_3)
  fd = open((char *) FileName, access_flags | O_CREAT,
        CreateFlags ? CreateFlags : my_umask);
#elif defined(VMS)
  fd = open((char *) FileName, access_flags | O_CREAT, 0,
        "ctx=stm","ctx=bin");
#elif defined(__WIN__)
  fd= my_sopen((char *) FileName, access_flags | O_CREAT | O_BINARY,
           SH_DENYNO, MY_S_IREAD | MY_S_IWRITE);
#else
  fd = open(FileName, access_flags);
#endif

  if ((MyFlags & MY_SYNC_DIR) && (fd >=0) &&
      my_sync_dir_by_file(FileName, MyFlags))
  {
    my_close(fd, MyFlags);
    fd= -1;
  }

  rc= my_register_filename(fd, FileName, FILE_BY_CREATE,
                           EE_CANTCREATEFILE, MyFlags);
  /*
    my_register_filename() may fail on some platforms even if the call to
    *open() above succeeds. In this case, don't leave the stale file because
    callers assume the file to not exist if my_create() fails, so they don't
    do any cleanups.
  */
  if (unlikely(fd >= 0 && rc < 0))
  {
    int tmp= my_errno;
    my_delete(FileName, MyFlags);
    my_errno= tmp;
  }
  
  DBUG_RETURN(rc);
} /* my_create */

      
      

       
       
      
      

     
     

     
     
红色的字体部分代码是为了实现跨平台，其中默认是蓝色字体代码，可以明显的看到，这时将O_CREAT添加进来了，此时就造成了O_CREAT和O_EXCL同时使用了。

     
     
在POSIX关于open函数的文档中可以看到，当O_CREAT和O_EXCL同时使用时，如果文件存在就会失败。

     
     
http://linux.die.net/man/3/open

     
     
 

    
    
   
   
参考：

http://www.cnblogs.com/lit10050528/p/4155325.html

总结不要使用 5.6.10 5.1.59, 5.5.15 版本 

使用主从切换的时候，会遇到这个问题。