| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.19 | OS: | Linux (Linux) |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
[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.
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.