| Bug #33222 | myisam-table drops rows when column is added and a char-field > 128 exists | ||
|---|---|---|---|
| Submitted: | 13 Dec 2007 16:57 | Modified: | 15 Apr 2008 2:09 |
| Reporter: | Lutz Maibach | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.22 and 6.0 | OS: | Linux (RedHat ES5) (FreeBSD 7.0 AMD64) |
| Assigned to: | Ingo Strüwing | CPU Architecture: | Any |
| Tags: | char, loss of data, myisam | ||
[14 Dec 2007 11:28]
Hartmut Holzgraefe
A quick question: In the last step you write that you get this back from the ALTER TABLE statement: Query OK, 100 rows affected, 2 warnings Could you do a SHOW WARNINGS right after that and post the two reported warnings to this bug report, too?
[14 Dec 2007 11:46]
Lutz Maibach
Hi Hartmut, here is the requested information: mysql> alter table foo add column trara varchar(10); Query OK, 100 rows affected, 3 warnings (0.02 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql> show warnings; +-------+------+--------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------+ | Error | 1034 | Found wrong packed record at 2144 | | Error | 1034 | Found wrong packed record at 2144 | | Error | 1034 | Number of rows changed from 49 to 99 | +-------+------+--------------------------------------+ 3 rows in set (0.00 sec)
[14 Dec 2007 18:43]
Susanne Ebrecht
mysql> CREATE TABLE `foo` (
-> `field1` int(11) NOT NULL AUTO_INCREMENT,
-> `field2` int(11) NOT NULL DEFAULT '0',
-> `field3` int(11) NOT NULL DEFAULT '0',
-> `field4` date DEFAULT NULL,
-> `field5` varchar(60) DEFAULT NULL,
-> `longchar` char(250) DEFAULT NULL,
-> `shortvchar` varchar(10) DEFAULT NULL,
-> PRIMARY KEY (`field1`),
-> KEY `field2` (`field2`),
-> KEY `field3` (`field3`),
-> KEY `field4` (`field4`),
-> KEY `field5` (`field5`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=79170 DEFAULT CHARSET=latin1
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into foo (field2, field3, field4, field5, longchar,shortvchar) values(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij'),
(1,1,current_date,'abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghijklmnopqrstuvwxyzZYXWVUTSRQPOMNLKJIHGFEDCBA','abcdefghij');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
I repeated this 11 time ...
mysql> select count(*) from foo\G
*************************** 1. row ***************************
count(*): 110
$ ls -lh /home/myhome/mysql51/var/mydatabase/
... 14K 14 Dez 19:32 foo.MYI
mysql> update foo set longchar=repeat("X",128) where field2=1;Query OK, 110 rows affected (0.02 sec)Rows matched: 110 Changed: 110 Warnings: 0
mysql> alter table foo add column whatever varchar(10);
071214 19:35:06 [Warning] Warning: Enabling keys got errno 137 on mydatabase.#sql-9df3_3, retryingQuery
OK, 110 rows affected, 111 warnings (0.02 sec)Records: 110 Duplicates: 0 Warnings: 0
mysql> show warnings;
+-------+------+------------------------------------+
| Level | Code | Message
|+-------+------+------------------------------------+
| Error | 1034 | Found wrong packed record at 0 |
| Error | 1034 | Found wrong packed record at 0 |
| Error | 1034 | Found wrong packed record at 216 |
| Error | 1034 | Found wrong packed record at 432 |
| Error | 1034 | Found wrong packed record at 648 |
| Error | 1034 | Found wrong packed record at 864 |
| Error | 1034 | Found wrong packed record at 1080 |
| Error | 1034 | Found wrong packed record at 1296 |
| Error | 1034 | Found wrong packed record at 1512 |
...
| Error | 1034 | Found wrong packed record at 12960 |
| Error | 1034 | Found wrong packed record at 13176 |
| Error | 1034 | Found wrong packed record at 13392 |
+-------+------+------------------------------------+
64 rows in set (0.00 sec)
mysql>select count(*) from foo\G
*************************** 1. row ***************************
count(*): 0
With only 100 rows, my foo.MYI just was 6K and all worked fine.
[17 Dec 2007 8:03]
Lutz Maibach
Hello Susanne, sorry for the "incomplete" example. I didn't want to post our table where it happened cause it has 35 fields so description would have been very long. Therefore I created the foo-table but used the wrong no. of inserted columns. 100 were enough using the original table, I inserted more in foo but only looked at the size of the MYI file and forgot to put the right no. of inserted col. into the description. If I get you right you're now able to reproduce the problem. Any other information neccesary from my side to fix it? Greetings from Germany Lutz Maibach
[17 Dec 2007 11:08]
Susanne Ebrecht
Hello Lutz, all is fine. I understood what you did and could reproduce it with MySQL 5.1.22 and 6.0. I couldn't reproduce it with 5.0.51. I don't have further questions. Greetings from Germany back to Germany. Susanne
[17 Dec 2007 21:46]
Calvin Sun
Investigation result from svoj:
I was able to track down the problem and have a fix for it. As this is my
last working day, I will not be able to handle review/push cycle quickly, so
please unassign this bug from me. Below comes detailed description of the
problem.
For dynamic record format tables with CHAR field, that contain value longer
than 127 bytes CHECK TABLE may report that table has wrongly packed record,
whereas records are fine. REPAIR TABLE (and sibling operations like ADD/DROP
COLUMN) may throw away such records.
The problem is signed vs unsigned comparision. Appeared after big types
removal patch by Monty. That is affects 5.1 and up.
Following is much simplier test case for this bug:
CREATE TABLE t1(a CHAR(130) NOT NULL, b VARCHAR(1) NOT NULL);
INSERT INTO t1 VALUES(REPEAT("X",128),'');
CHECK TABLE t1;
DROP TABLE t1;
A minimal patch should look like (the function name is _mi_rec_check()):
+++ edited/../storage/myisam/mi_dynrec.c 2007-12-18 00:20:27 +04:00
@@ -1133,7 +1133,7 @@
goto err;
to+=2;
}
- else if (*to++ != (char) new_length)
+ else if (*to++ != (uchar) new_length)
goto err;
to+=new_length;
}
It would be great to check similiar case a few lines above:
if (to[0] != (char) ((new_length & 127)+128) ||
to[1] != (char) (new_length >> 7))
But as I do not remember how FIELD_SKIP_ENDSPACE/FIELD_SKIP_PRESPACE could
be longer than 255 bytes, I cannot write a test case for it.
Ingo: do you remember how could that happen or it is just a dead code?
[18 Dec 2007 14: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/40138 ChangeSet@1.2641, 2007-12-18 15:19:04+01:00, istruewing@stella.local +3 -0 Bug#33222 - myisam-table drops rows when column is added and a char-field > 128 exists ALTERing a table with long char columns warned about corruptions and left the altered table empty. The problem was a signed/unsigned compare in MyISAM code. A char to uchar change became necessary after the big byte to uchar change.
[10 Jan 2008 13:55]
Sergey Vojtovich
Approved with wishlist.
[14 Jan 2008 16:59]
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/40990 ChangeSet@1.2641, 2008-01-14 17:59:45+01:00, istruewing@stella.local +3 -0 Bug#33222 - myisam-table drops rows when column is added and a char-field > 128 exists CHECK TABLE (non-QUICK) and any form of repair table did wrongly rate records as corrupted under the following conditions: 1. The table has dynamic row format and 2. it has a CHAR like column > 127 bytes (but not VARCHAR) (for multi-byte character sets this could be less than 127 characters) and 3. it has records with > 127 bytes significant length in that column (a byte beyond byte position 127 must be non-space). Affected were the statements CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE, ALTER TABLE. CHECK TABLE reported and marked the table as crashed if any record was present that fulfilled condition 3. The other statements deleted these records. The problem was a signed/unsigned compare in MyISAM code. A char to uchar change became necessary after the big byte to uchar change.
[17 Jan 2008 9:01]
Ingo Strüwing
Queued to 6.0-engines, 5.1-engines
[27 Mar 2008 11:17]
Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:49]
Bugs System
Pushed into 6.0.5-alpha
[31 Mar 2008 20:20]
Jon Stephens
Pushed into 5.1.23-ndb-6.3.11.
[15 Apr 2008 2:09]
Paul DuBois
Noted in 5.1.24, 6.0.5 changelogs.
Description: Adding a column to a table containing a field defined as char(128) (NOT varchar) or longer, all rows in which the char-field contains at least 128 characters are deleted . Second condition is that thy MYI-File is at least 8kb long. How to repeat: 1. CREATE TABLE `foo` ( `field1` int(11) NOT NULL AUTO_INCREMENT, `field2` int(11) NOT NULL DEFAULT '0', `field3` int(11) NOT NULL DEFAULT '0', `field4` date DEFAULT NULL, `field5` varchar(60) DEFAULT NULL, `longchar` char(250) DEFAULT NULL, `shortvchar` varchar(10) DEFAULT NULL, PRIMARY KEY (`field1`), KEY `field2` (`field2`), KEY `field3` (`field3`), KEY `field4` (`field4`), KEY `field5` (`field5`) ) ENGINE=MyISAM AUTO_INCREMENT=79170 DEFAULT CHARSET=latin1 2. Insert several rows into foo so foo.MYI grows to at least 8kb 3. Enter a string with at least 128 chars into 'longchar', for example: update foo set longchar=repeat("X",128) where field1=1 4. Now add a new column to foo: alter table foo add column whatever varchar(10) (you can use any datatype, behaviour is always the same) If you inserted 100 rows, one of them with 128 "X" before adding column whatever, the result would look like this: Query OK, 100 rows affected, 2 warnings Records: 100 Duplicates: 0 Warnings: 0 select count(*) from foo; +----------+ | count(*) | +----------+ | 99 | +----------+