MySQL Bugs: #39750: INFORMATION_SCHEMA operations fail sporadically due to I/O errors

Bug #39750	INFORMATION_SCHEMA operations fail sporadically due to I/O errors
Submitted:	30 Sep 2008 6:44	Modified:	29 Jan 2009 5:15
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1, 6.0	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	pushbuild, sporadic, test failure, widespread

Description:
Operations with INFORMATION_SCHEMA involving temporary tables fail sporadically
due to I/O errors, such as:
  - Can't create/write to file 'c:\tmp\pbtmp-ps_stm-101\#sql_1300_0.MYD' (Errcode: 13)
  - Error on delete of '/dev/shm/pbtmp-ps_row-131/#sql_20d1_0.MAI' (Errcode: 2)

So far the problems have found on Windows (mainly) and on sapsrv2 (SuSE 10.0, x86_64).

See also Bug#37800.

How to repeat:
- http://tinyurl.com/3kk2vd
- Bug#37800

See also Bug#37813.

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/59327

2699 Vladislav Vaintroub	2008-11-20
      Bug #39750 : INFORMATION_SCHEMA operations fail sporadically due to I/O errors
      on Windows
      
      The problem: shortly after successfull my_delete, my_open with the same name
      would fail with ERROR_ACCESS_DENIED. This is pecularity of Windows: file does not 
      disappear entirely from the filesystem until the last open handle to it is closed
      and in the time after DeleteFile and before handles to it are closed it is in a 
      sort of "zombie" state (aka DELETE PENDING)-  any further access to this file will 
      result in access denied.
      
      It does not seem possible to prevent other applications like virus scanner or 
      indexer from opening the file while we're deleting it. Also, there is a possibility 
      that another MySQL thread has opened the file while we're deleting it (as in see 
      Bug #27594)
      
      The fix is to rename file to a unique name prior to deletion. This technique was 
      used prior, but just for binlog. This patch also fixes some glitches in prior 
      implementationof rename/remove.

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/60759

2728 Vladislav Vaintroub	2008-12-05
       Bug#39750 -cannot create temp file on Windows.
      The problem appears often in conjuction with temp files, when temp-pool is used, so that names of temp files are not unique.
      
      The reason is that rapid deletiion and creation of fiiles with the same name on Windows is not guaranteed to succeed. File disappears from the file system only when the last handle to it is closed. If for example  a virus scanner, a backup or indexing  application  opens the  temp file just before MySQL deletes it, the file will enter "delete pending" state. In this state,it is not possible to open the file , or create a file with the same name (CreateFile returns  ERROR_ACCESS_DENED, posix open returns EACESS)
      
      Fix (rather a cheap workarounf) is not to use temp-pool when working with temporary files- this will make filenames unique. 
      With this patch , temp- pool setting will be ignored on anything but Linux(the option only made sense for Linux since its invention anyway).

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/60940

2705 Vladislav Vaintroub	2008-12-08
       Bug#39750 -cannot create temp file on Windows.
      
      The problem appears often in conjuction with temp files, when temp-pool is used, so that names of temp files are not unique.
            
      The reason is that rapid deletiion and creation of files with the same name on Windows is not guaranteed to succeed. File disappears from the file system only when the last handle to it is closed. If for example  a virus scanner, a backup or indexing  application  opens the  temp file just before MySQL deletes it, the file will enter "delete pending" state and will remain in this state until the handle to file is closed. In this state,it is not possible to open the file , or create a file with the same name (CreateFile returns  ERROR_ACCESS_DENED, posix open returns EACESS)
            
      Fix (rather a cheap workaround) is not to use temp-pool when working with temporary files- this will make filenames unique. 
      With this patch , temp- pool setting will be ignored on anything but Linux(the option only made sense for Linux since its invention anyway).

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/60958

2748 Vladislav Vaintroub	2008-12-08 [merge]
      merge fix for Bug#39750 to 6.0-rpl

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/60965

2780 Vladislav Vaintroub	2008-12-08 [merge]
      merge bug#39750 to 6.0-bugteam

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/61141

2742 He Zhenxing	2008-12-10 [merge]
      Auto Merge

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

Noted in 5.1.31, 6.0.10 changelogs.

For a server started with the --temp-pool option on Windows,
temporary file creation could fail. This option now is ignored except
on Linux systems, which was its original intended scope.

Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)