| Bug #20256 | LOCK WRITE - MyISAM | ||
|---|---|---|---|
| Submitted: | 4 Jun 2006 6:07 | Modified: | 25 Sep 2006 19:57 |
| Reporter: | Eric Grossi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.0.25-BK, 5.0.22 | OS: | Linux (Linux, W2K) |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
[4 Jun 2006 6:11]
Eric Grossi
All work fine with MySQL 4.1
[5 Jun 2006 12:17]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with latest 5.0.23-BK on Linux:
mysql> CREATE TABLE `object` (
-> `id` int(11) NOT NULL auto_increment,
-> `dcreate` datetime NOT NULL default '0000-00-00 00:00:00',
-> `dmodify` datetime NOT NULL default '0000-00-00 00:00:00',
-> `pid` int(11) NOT NULL default '0',
-> `tid` int(11) NOT NULL default '0',
-> `oid` int(11) NOT NULL default '0',
-> `cid` int(11) NOT NULL default '0',
-> `creator` int(11) NOT NULL default '0',
-> `ownid` int(11) NOT NULL default '0',
-> `resid` int(11) NOT NULL default '0',
-> `trash` int(11) NOT NULL default '0',
-> PRIMARY KEY (`id`),
-> KEY `dcreate` (`dcreate`),
-> KEY `dmodify` (`dmodify`),
-> KEY `pid` (`pid`),
-> KEY `tid` (`tid`),
-> KEY `oid` (`oid`),
-> KEY `cid` (`cid`),
-> KEY `creator` (`creator`),
-> KEY `ownid` (`ownid`),
-> KEY `resid` (`resid`),
-> KEY `trash` (`trash`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `folder` (
-> `id` int(11) NOT NULL auto_increment,
-> `name` varchar(255) NOT NULL default '',
-> `file_files` text NOT NULL,
-> `description` text NOT NULL,
-> PRIMARY KEY (`id`),
-> FULLTEXT KEY `name` (`name`),
-> FULLTEXT KEY `description` (`description`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> LOCK TABLES `object` WRITE, `folder` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `folder` ( `name` ) VALUES ('test-02');
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1',
-> LAST_INSERT_ID() );
Query OK, 1 row affected (0.01 sec)
mysql> SELECT `object`.* ,`folder`.`name`
-> FROM `object`
-> LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id`
-> WHERE `object`.`pid`=4 AND `folder`.`id` IS NOT NULL;
+----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+
| id | dcreate | dmodify | pid | tid | oid | cid | creator | ownid | resid | trash | name |
+----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+
| 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 4 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | test-02 |
+----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+
1 row in set (0.00 sec)
mysql> select * from object;
+----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+
| id | dcreate | dmodify | pid | tid | oid | cid | creator | ownid | resid | trash |
+----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+
| 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 4 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
+----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from folder;
+----+---------+------------+-------------+
| id | name | file_files | description |
+----+---------+------------+-------------+
| 1 | test-02 | | |
+----+---------+------------+-------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23 |
+-----------+
1 row in set (0.00 sec)
This result looks OK for me. Or I missed something?
[5 Jun 2006 14:59]
Eric Grossi
Ok yes the first time, the sequence work but not second time. Try this:
LOCK TABLES `object` WRITE, `folder` WRITE;
INSERT INTO `folder` ( `name` ) VALUES ('test-01');
INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', LAST_INSERT_ID() );
SELECT `object`.* ,`folder`.`name`
FROM `object`
LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id`
WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL);
UNLOCK TABLES;
LOCK TABLES `object` WRITE, `folder` WRITE;
INSERT INTO `folder` ( `name` ) VALUES ('test-02');
INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', LAST_INSERT_ID() );
SELECT `object`.* ,`folder`.`name`
FROM `object`
LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id`
WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL);
Here is my console dump:
mysql> LOCK TABLES `object` WRITE, `folder` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `folder` ( `name` ) VALUES ('test-01');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', LAST_IN
SERT_ID() );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT `object`.* ,`folder`.`name`
-> FROM `object`
-> LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id`
-> WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL);
+-----+---------------------+---------------------+-----+-----+-----+-----+-----
----+-------+-------+-------+---------+
| id | dcreate | dmodify | pid | tid | oid | cid | crea
tor | ownid | resid | trash | name |
+-----+---------------------+---------------------+-----+-----+-----+-----+-----
----+-------+-------+-------+---------+
| 696 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 24 | 1 |
0 | 0 | 0 | 0 | Info |
| 697 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 25 | 1 |
0 | 0 | 0 | 0 | Common |
| 698 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 4 | 1 | 26 | 0 |
0 | 0 | 0 | 0 | test-01 |
+-----+---------------------+---------------------+-----+-----+-----+-----+-----
----+-------+-------+-------+---------+
3 rows in set (0.00 sec)
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> LOCK TABLES `object` WRITE, `folder` WRITE;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `folder` ( `name` ) VALUES ('test-02');
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', LAST_IN
SERT_ID() );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT `object`.* ,`folder`.`name`
-> FROM `object`
-> LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id`
-> WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL);
+-----+---------------------+---------------------+-----+-----+-----+-----+-----
----+-------+-------+-------+---------+
| id | dcreate | dmodify | pid | tid | oid | cid | crea
tor | ownid | resid | trash | name |
+-----+---------------------+---------------------+-----+-----+-----+-----+-----
----+-------+-------+-------+---------+
| 696 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 24 | 1 |
0 | 0 | 0 | 0 | Info |
| 697 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 25 | 1 |
0 | 0 | 0 | 0 | Common |
| 698 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 4 | 1 | 26 | 0 |
0 | 0 | 0 | 0 | test-01 |
+-----+---------------------+---------------------+-----+-----+-----+-----+-----
----+-------+-------+-------+---------+
3 rows in set (0.00 sec)
[20 Jul 2006 18:15]
Valeriy Kravchuk
Verified just as described in the last comment, using your (private) try.sql uploaded, with 5.0.25-BK on Linux: openxs@suse:~/dbs/5.0> bin/mysql -uroot bug20256 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 5.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show tables; +--------------------+ | Tables_in_bug20256 | +--------------------+ | category | | contact | | file | | folder | | group | | grpuser | | language | | log | | object | | parent | | security | | session | | subscription | | user | +--------------------+ 14 rows in set (0.01 sec) mysql> LOCK TABLES `object` WRITE, `folder` WRITE; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `folder` ( `name` ) VALUES ('test-01'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', -> LAST_INSERT_ID() ); Query OK, 1 row affected (0.00 sec) mysql> SELECT `object`.* ,`folder`.`name` -> FROM `object` -> LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id` -> WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL); +-----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+ | id | dcreate | dmodify | pid | tid | oid | cid | creator | ownid | resid | trash | name | +-----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+ | 696 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 24 | 1 | 0 | 0 | 0 | 0 | Info | | 697 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 25 | 1 | 0 | 0 | 0 | 0 | Common | | 698 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 4 | 1 | 26 | 0 | 0 | 0 | 0 | 0 | test-01 | +-----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+ 3 rows in set (0.03 sec) Lmysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> LOCK TABLES `object` WRITE, `folder` WRITE; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `folder` ( `name` ) VALUES ('test-02'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', -> LAST_INSERT_ID() ); Query OK, 1 row affected (0.00 sec) mysql> SELECT `object`.* ,`folder`.`name` -> FROM `object` -> LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id` -> WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL); +-----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+ | id | dcreate | dmodify | pid | tid | oid | cid | creator | ownid | resid | trash | name | +-----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+ | 696 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 24 | 1 | 0 | 0 | 0 | 0 | Info | | 697 | 2006-06-05 10:18:09 | 2006-06-05 10:18:09 | 4 | 1 | 25 | 1 | 0 | 0 | 0 | 0 | Common | | 698 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 4 | 1 | 26 | 0 | 0 | 0 | 0 | 0 | test-01 | +-----+---------------------+---------------------+-----+-----+-----+-----+---------+-------+-------+-------+---------+ 3 rows in set (0.01 sec)
[7 Sep 2006 15:03]
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/11552 ChangeSet@1.2241, 2006-09-07 20:03:10+05:00, svoj@may.pils.ru +7 -0 BUG#20256 - LOCK WRITE - MyISAM Only MyISAM tables locked with LOCK TABLES ... WRITE were affected. A query that is optimized with index_merge doesn't reflect rows inserted within LOCK TABLES. MyISAM doesn't flush a state within LOCK TABLES. index_merge optimization creates a copy of the handler, which thus gets outdated MyISAM state. New handler->clone() method is introduced to fix this problem. For non-MyISAM storage engines it allocates a handler and opens it with ha_open(). For MyISAM it additionally copies MyISAM state pointer to cloned handler.
[12 Sep 2006 13:24]
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/11767 ChangeSet@1.2245, 2006-09-12 18:25:35+05:00, svoj@april.(none) +7 -0 BUG#20256 - LOCK WRITE - MyISAM Only MyISAM tables locked with LOCK TABLES ... WRITE were affected. A query that is optimized with index_merge doesn't reflect rows inserted within LOCK TABLES. MyISAM doesn't flush a state within LOCK TABLES. index_merge optimization creates a copy of the handler, which thus gets outdated MyISAM state. New handler->clone() method is introduced to fix this problem. For non-MyISAM storage engines it allocates a handler and opens it with ha_open(). For MyISAM it additionally copies MyISAM state pointer to cloned handler.
[21 Sep 2006 7:57]
Ingo Strüwing
Pushed to 5.1.12.
[21 Sep 2006 17:10]
Ingo Strüwing
Pushed to 5.0.26.
[25 Sep 2006 19:57]
Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.
Description: When an INSERT is called inside LOCK WRITE and just after we call SELECT with a LEFT JOIN without call UNLOCK RELEASE, then result doesn't reflect inserted row. If one record on left table was previously deleted before entering LOCK, SELECT give the correct result. If one record on left table was previously deleted before entering LOCK and we optimize left table, SELECT give a wrong result. How to repeat: **Table struct** CREATE TABLE `object` ( `id` int(11) NOT NULL auto_increment, `dcreate` datetime NOT NULL default '0000-00-00 00:00:00', `dmodify` datetime NOT NULL default '0000-00-00 00:00:00', `pid` int(11) NOT NULL default '0', `tid` int(11) NOT NULL default '0', `oid` int(11) NOT NULL default '0', `cid` int(11) NOT NULL default '0', `creator` int(11) NOT NULL default '0', `ownid` int(11) NOT NULL default '0', `resid` int(11) NOT NULL default '0', `trash` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `dcreate` (`dcreate`), KEY `dmodify` (`dmodify`), KEY `pid` (`pid`), KEY `tid` (`tid`), KEY `oid` (`oid`), KEY `cid` (`cid`), KEY `creator` (`creator`), KEY `ownid` (`ownid`), KEY `resid` (`resid`), KEY `trash` (`trash`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `folder` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `file_files` text NOT NULL, `description` text NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `name` (`name`), FULLTEXT KEY `description` (`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; First, feed object table with some rows. **Sequence to test** LOCK TABLES `object` WRITE, `folder` WRITE; INSERT INTO `folder` ( `name` ) VALUES ('test-02'); INSERT INTO `object` ( `pid` , `tid` , `oid` ) VALUES ( '4', '1', LAST_INSERT_ID() ); SELECT `object`.* ,`folder`.`name` FROM `object` LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id` WHERE `object`.`pid`=4 AND `folder`.`id` IS NOT NULL; NB: If we add another LEFT JOIN then the result is correct, ex: SELECT `object`.* ,`folder`.`name` FROM `object` LEFT JOIN `folder` ON `object`.`tid`=1 AND `object`.`oid`=`folder`.`id` LEFT JOIN `file` ON `object`.`tid`=2 AND `object`.`oid`=`file`.`id` WHERE `object`.`pid`=4 AND (`folder`.`id` IS NOT NULL OR `file`.`id` IS NOT NULL); Suggested fix: The way to ensure correct result is to call UNLOCK RELEASE before calling SELECT. In some case it’s not a good option.