| Bug #20604 | FORCE INDEX uses keys disabled by ALTER TABLE | ||
|---|---|---|---|
| Submitted: | 21 Jun 2006 12:24 | Modified: | 9 Feb 2007 20:34 |
| Reporter: | Sergey Vojtovich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.0BK/4.1BK/5.0BK/5.1BK | OS: | Linux (Suse Linux 10) |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | Q1 | ||
[21 Jun 2006 13:05]
MySQL Verification Team
Thank you for the bug report. Verified as described.
[11 Jan 2007 17:53]
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/17963 ChangeSet@1.2359, 2007-01-11 10:46:47+01:00, mhansson@linux-st28.site +3 -0 BUG # 20604 Fixed The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), fails to check that keys are not disabled by ALTER TABLE. The fact that keys are disabled does not reveal itself in keys_in_use_for_query, but rather in a field in its "table share", called keys_in_use. The reasoning behind this is most likely that a DISABLE KEYS is set on table level, as opposed to a particular projection of a table. Table level objects are known as "table shares" inside the code. Projections of tables are known in the code as a "table". Hence the set of indexes that can be used are the intersection of those that this particular "table" object permits using and those that the "table share" permits using.
[11 Jan 2007 17:53]
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/17964 ChangeSet@1.2359, 2007-01-11 12:18:06+01:00, mhansson@linux-st28.site +3 -0 BUG # 20604 Fixed The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), fails to check that keys are not disabled by ALTER TABLE. The fact that keys are disabled does not reveal itself in keys_in_use_for_query, but rather in a field in its "table share", called keys_in_use. The reasoning behind this is most likely that a DISABLE KEYS is set on table level, as opposed to a particular projection of a table. Table level objects are known as "table shares" inside the code. Projections of tables are known in the code as a "table". Hence the set of indexes that can be used are the intersection of those that this particular "table" object permits using and those that the "table share" permits using.
[11 Jan 2007 17:53]
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/17965 ChangeSet@1.2359, 2007-01-11 09:59:49+01:00, mhansson@linux-st28.site +3 -0 Fixed Bug #20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), fails to check that keys are not disabled by ALTER TABLE. The fact that keys are disabled does not reveal itself in keys_in_use_for_query, but rather in a field in its "table share", called keys_in_use. The reasoning behind this is most likely that a DISABLE KEYS is set on table level, as opposed to a particular projection of a table. Table level objects are known as "table shares" inside the code. Projections of tables are known in the code as a "table". Hence the set of indexes that can be used are the intersection of those that this particular "table" object permits using and those that the "table share" permits using.
[22 Jan 2007 14:38]
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/18540 ChangeSet@1.2359, 2007-01-22 15:40:44+01:00, mhansson@linux-st28.site +5 -0 BUG # 20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE are not to be found in table->keys_in_use_for_query. The bug is that disabled keys still appear in keys_in_use_for_query, in the normal case. The assumption for keys_is_use_for_query is as of now that disabled keys are _not_ included. This has also been put as a comment above the field.
[23 Jan 2007 7:55]
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/18602 ChangeSet@1.2359, 2007-01-23 08:57:53+01:00, mhansson@linux-st28.site +6 -0 BUG # 20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE are not to be found in table->keys_in_use_for_query. The bug is that disabled keys still appear in keys_in_use_for_query, in the normal case. The assumption for keys_is_use_for_query is as of now that disabled keys are _not_ included. This has also been put as a comment above the field.
[25 Jan 2007 8:20]
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/18762 ChangeSet@1.2392, 2007-01-25 09:25:59+01:00, mhansson@dl145s.mysql.com +5 -0 BUG#20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE ... DISABLE KEYS are not in the set table->keys_in_use_for_query. I.E., if a key is in this set, the optimizer assumes it is free to use it. The bug is that keys disabled with ALTER TABLE ... DISABLE KEYS still appear in table->keys_in_use_for_query When the TABLE object has been initialized with setup_tables(). In the cases when the TABLE has been initialized any other way, for instance open_table(), the keys disabled in the aforementioned way are not in the set. The provided patch does the following: - changes the code that uses keys_is_use_for_query so that it assumes that keys_is_use_for_query already takes into account all disabled keys, and generally all keys that should be used by the query. - subtracts all disabled keys from keys_is_use_for_query early, during setup_tables, so that later phases can rely on consistent contents of keys_is_use_for_query.
[25 Jan 2007 13:43]
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/18775 ChangeSet@1.2409, 2007-01-25 14:45:48+01:00, mhansson@linux-st28.site +5 -0 BUG#20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE ... DISABLE KEYS are not in the set table->keys_in_use_for_query. I.E., if a key is in this set, the optimizer assumes it is free to use it. The bug is that keys disabled with ALTER TABLE ... DISABLE KEYS still appear in table->keys_in_use_for_query When the TABLE object has been initialized with setup_tables(). In the cases when the TABLE has been initialized any other way, for instance open_table(), the keys disabled in the aforementioned way are not in the set. The provided patch does the following: - changes the code that uses keys_is_use_for_query so that it assumes that keys_is_use_for_query already takes into account all disabled keys, and generally all keys that should be used by the query. - subtracts all disabled keys from keys_is_use_for_query early, during setup_tables, so that later phases can rely on consistent contents of keys_is_use_for_query.
[25 Jan 2007 15:56]
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/18785 ChangeSet@1.2409, 2007-01-25 16:58:30+01:00, mhansson@linux-st28.site +5 -0 BUG#20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE ... DISABLE KEYS are not in the set table->keys_in_use_for_query. I.E., if a key is in this set, the optimizer assumes it is free to use it. The bug is that keys disabled with ALTER TABLE ... DISABLE KEYS still appear in table->keys_in_use_for_query When the TABLE object has been initialized with setup_tables(). Before setup_tables is called, however, keys that are disabled in the aforementioned way are not included in TABLE::keys_in_use_for_query. The provided patch does the following: - changes the code that uses keys_is_use_for_query so that it assumes that keys_is_use_for_query already takes into account all disabled keys, and generally all keys that should be used by the query. - subtracts all disabled keys from keys_is_use_for_query early, during setup_tables, so that later phases can rely on consistent contents of keys_is_use_for_query.
[26 Jan 2007 11:34]
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/18841 ChangeSet@1.2409, 2007-01-26 12:36:29+01:00, mhansson@linux-st28.site +5 -0 BUG#20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE ... DISABLE KEYS are not in the set table->keys_in_use_for_query. I.E., if a key is in this set, the optimizer assumes it is free to use it. The bug is that keys disabled with ALTER TABLE ... DISABLE KEYS still appear in table->keys_in_use_for_query When the TABLE object has been initialized with setup_tables(). Before setup_tables is called, however, keys that are disabled in the aforementioned way are not included in TABLE::keys_in_use_for_query. The provided patch changes the code that updates keys_is_use_for_query so that it assumes that keys_is_use_for_query already takes into account all disabled keys, and generally all keys that should be used by the query.
[29 Jan 2007 14:04]
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/18946 ChangeSet@1.2409, 2007-01-29 15:07:11+01:00, mhansson@linux-st28.site +5 -0 BUG#20604: FORCE INDEX uses keys disabled by ALTER TABLE The function that checks whether we can use keys for aggregates, find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE ... DISABLE KEYS are not in the set table->keys_in_use_for_query. I.E., if a key is in this set, the optimizer assumes it is free to use it. The bug is that keys disabled with ALTER TABLE ... DISABLE KEYS still appear in table->keys_in_use_for_query When the TABLE object has been initialized with setup_tables(). Before setup_tables is called, however, keys that are disabled in the aforementioned way are not included in TABLE::keys_in_use_for_query. The provided patch changes the code that updates keys_is_use_for_query so that it assumes that keys_is_use_for_query already takes into account all disabled keys, and generally all keys that should be used by the query.
[3 Feb 2007 6:29]
Igor Babaev
The fix has been pushed into 5.1.16-beta main tree.
[9 Feb 2007 20:34]
Paul DuBois
Noted in 5.1.16 changelog. Indexes disabled with ALTER TABLE ... DISABLE KEYS could in some cases be used by specifying FORCE INDEX.
[11 Mar 2007 12:31]
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/21691 ChangeSet@1.2480, 2007-03-11 14:31:43+02:00, gkodinov@magare.gmz +3 -0 WL3527: post-merge updates sql_yacc.yy: WL3527: updated the diff to use correct parser words table.cc: WL3527: exteneded the fix for bug #20604 to fit the new variables sql_select.cc: WL3527: renamed used_keys to covering_keys
Description: Having indexes disabled by ALTER TABLE and FORCE INDEX in the SELECT query results in the error from storage engine for some queries. How to repeat: There are at least two cases that I was able to discover: CREATE TABLE t1(a TINYINT, b VARCHAR(255), KEY(a), FULLTEXT(b)) ENGINE=MyISAM; INSERT INTO t1 VALUES(1, 'abcde'); ALTER TABLE t1 DISABLE KEYS; SELECT * FROM t1 FORCE INDEX(b) WHERE MATCH(b) AGAINST('abcde' IN BOOLEAN MODE); SELECT MAX(a) FROM t1 FORCE INDEX(a); Suggested fix: Fall to table scan when indexes are disabled and FORCE INDEX is used in the query. The problem seems to be around keys_in_use_for_query bitmap which includes disabled indexes. This bug should be fixed probably after fix for BUG#20357 is pushed. There is another problem with min/max and disabled indexes.