Bug #20060 mysqld option "--flush " doesn't work for update statement
Submitted: 25 May 2006 4:33 Modified: 18 Aug 2006 14:24
Reporter: Yoshiaki Tajika (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.19 OS:Linux (Linux)
Assigned to: Sergey Vojtovich

[25 May 2006 4:33] Yoshiaki Tajika
Description:
mysqld option "--flush " doesn't work for update statement.

According to the manual, this option makes mysqld flush all changes to disk 
after each SQL statement. Certainly, fdatasync() is called after INSERT,
DELETE, or UPDATE for key-column. But fdatasync() is NOT called after 
UPDATE for non-key-column.

How to repeat:
create table t1(c1 int primary key, c2 int) engine=myisam;
insert into t1 values(1,1); /* this is flushed */
update t1 set c2=c2+1 where c1=1; /* this is NOT flushed */

To see what happens,
1. Use strace command, and check if fdatasync() or fsync() is called 
    just after update statement.
Or
2. Use gdb debugger, and set a breakpoint at my_sync() and 
    mi_lock_database(). my_lock_database() calls my_sync(), which 
    calls fdatasync() or fsync().

Suggested fix:
I'm not sure, but please check the code around here.
-------------------
mi_locking.c: mi_lock_database()
{
   :
  if(share->changed && !share->w_locks)
  {
      :
    my_sync();
  }
-------------------

share->changed is 1, when INSERT, DELETE, or UPDATE for key-column.
share->changed is 0, when UPDATE for non-key-column.
That's all I investigated.
[25 May 2006 9:41] Tonci Grgin
Hi Yoshiaki Tajika.
Thanks for your report.
Verified as described by reporter on latest bk build.
mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.21-debug-log |
+------------------+
1 row in set (0.06 sec)

After update statement if (share->changed && !share->w_locks) is false so flush doesn't occur:

	DBUG_PRINT("info",("changed: %u  w_locks: %u",
			   (uint) share->changed, share->w_locks));
	if (share->changed && !share->w_locks)
	{
	  share->state.process= share->last_process=share->this_process;
	  share->state.unique=   info->last_unique=  info->this_unique;
	  share->state.update_count= info->last_loop= ++info->this_loop;
          if (mi_state_info_write(share->kfile, &share->state, 1))
	    error=my_errno;
	  share->changed=0;
	  if (myisam_flush)
	  {
	    if (my_sync(share->kfile, MYF(0)))
	      error= my_errno;
	    if (my_sync(info->dfile, MYF(0)))
	      error= my_errno;
	  }
	  else
	    share->not_flushed=1;
	  if (error)
	    mi_mark_crashed(info);
	}
[1 Aug 2006 16:10] 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/9907

ChangeSet@1.2532, 2006-08-01 21:09:29+05:00, svoj@may.pils.ru +1 -0
  BUG#20060 - mysqld option "--flush " doesn't work for update statement
  
  Problem described in this bug report affects MyISAM tables only.
  
  Running mysqld --flush instructs mysqld to sync all changes to disk
  after each SQL statement. It worked well for INSERT and DELETE
  statements, but it did sync for UPDATE only in case if there was
  index change (change of colum that has an index). If no updated column
  has an index, data wasn't synced to disk.
  
  This fix makes UPDATE statement to sync data to disk even if there is
  no index change (that is only data change) and mysqld is run with
  --flush option.
[9 Aug 2006 9: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/10191

ChangeSet@1.2532, 2006-08-09 14:28:39+05:00, svoj@may.pils.ru +1 -0
  BUG#20060 - mysqld option "--flush " doesn't work for update statement
  
  Problem described in this bug report affects MyISAM tables only.
  
  Running mysqld --flush instructs mysqld to sync all changes to disk
  after each SQL statement. It worked well for INSERT and DELETE
  statements, but it did sync for UPDATE only in case if there was
  index change (change of colum that has an index). If no updated column
  has an index, data wasn't synced to disk.
  
  This fix makes UPDATE statement to sync data to disk even if there is
  no index change (that is only data change) and mysqld is run with
  --flush option.
[9 Aug 2006 17:48] Sergey Vojtovich
Pushed into 4.1.22, 5.0.25.
[15 Aug 2006 7:41] Sergey Vojtovich
Pushed into 5.1.12.
[18 Aug 2006 14:24] Jon Stephens
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

Bugfix documented in 4.1.22, 5.0.25, and 5.1.12 changelogs.