| Bug #26207 | inserts don't work with shortened index | ||
|---|---|---|---|
| Submitted: | 9 Feb 2007 6:18 | Modified: | 13 Apr 2007 18:03 |
| Reporter: | mike sherwood | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.22, 5.1 BK, 4.1 BK | OS: | Windows (windows) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
| Tags: | INDEX, inserts | ||
[9 Feb 2007 10:52]
Sveta Smirnova
Thank you for the report. Please provide output of SELECT @@sql_mode;;
[10 Feb 2007 1:35]
mike sherwood
here is the result of SELECT @@sql_mode; STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[12 Feb 2007 7:57]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Please read carefully about STRICT_TRANS_TABLES at http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
[12 Feb 2007 9:39]
Sveta Smirnova
Sorry: was wrong in previous comment. Verified as described. All versions are affected. If omit STRICT_TRANS_TABLES, we get warning "Data truncated for column 'org' at row 1"
[1 Mar 2007 12: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/20896 ChangeSet@1.2425, 2007-03-01 14:56:24+02:00, gkodinov@magare.gmz +3 -0 Bug #26207: When making the key image to use in index search MySQL was not explicitly suppressing warnings. And if the context happens to enable warnings (e.g. INSERT .. SELECT) the warnings resulting from converting the constant the key is compared to are reported to the client. Fixed by suppressing warnings when converting the constants to the same type as the key parts.
[22 Mar 2007 15:22]
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/22650 ChangeSet@1.2425, 2007-03-22 17:22:30+02:00, gkodinov@magare.gmz +3 -0 Bug #26207: When making the key image to use in index search MySQL was not explicitly suppressing warnings. And if the context happens to enable warnings (e.g. INSERT .. SELECT) the warnings resulting from converting the data the key is compared to are reported to the client. Fixed by suppressing warnings when converting the data to the same type as the key parts.
[22 Mar 2007 16:44]
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/22664 ChangeSet@1.2425, 2007-03-22 18:44:16+02:00, gkodinov@magare.gmz +3 -0 Bug #26207: When making the key image to use in index search MySQL was not explicitly suppressing warnings. And if the context happens to enable warnings (e.g. INSERT .. SELECT) the warnings resulting from converting the data the key is compared to are reported to the client. Fixed by suppressing warnings when converting the data to the same type as the key parts.
[23 Mar 2007 13:54]
Alexey Botchkov
Pushed in 5.0.40 and 5.1.18
[13 Apr 2007 18:03]
Paul DuBois
Noted in 5.0.40, 5.1.18 changelogs. For INSERT INTO ... SELECT where index searches used column prefixes, insert errors could occur when key value type conversion was done.
Description: I have no idea what to call this, but it is easy to replicate with the steps below. If I put an index of length shorter than the field it is based on, I can't insert a row into a new table if that row has a value of that field longer than the index but shorter than the field definition. How to repeat: Here's the sequence: 1.Create two tables. table 1 is the source. table2 will be getting the inserts. table1 has an index (ix_org) which is 10 long, while org, the field it is based on is 30 long. create table table1 ( name varchar(30), place smallint, org varchar(30), index ix_org (org(10)) ) engine = MyISAM; create table table2 ( name varchar(30), place smallint, org varchar(30), place_total varchar(10) ) engine = MyISAM; 2. Insert some data into table1. Two rows: one row with a "org" field length shorter than 10 and another with a field length longer than 10. insert into table1 (name, place, org) values ('Joe','5','7_chars'), ('Fred','10','13_characters'); 3. now do the inserts test 1: insert into table2 select name as name, place as place, org as org, concat(place,'/', (select max(place) from table1 where org='7_chars')) as place_total from table1 t1; Result: all is good. in this case we are joining (in the subquery) on a value of "org" which is less than 10 characters > two rows inserted. test 2: (now join in the subquery with field length of org longer than 13) insert into table2 select name as name, place as place, org as org, concat(place,'/', (select max(place) from table1 where org='13_characters')) as place_total from table1 t1; Result: does not insert. error "data too long for column org at row1". Should have inserted the rows. Other tests: * if the length of the index is made the same length as the field, the error doesn't occur. * if the subquery is not in the insert command, the error doesn't occur.