| Bug #29335 | INSERT DELAYED checks table space incorrectly on MyISAM | ||
|---|---|---|---|
| Submitted: | 25 Jun 2007 12:29 | Modified: | 27 Jun 2007 10:14 |
| Reporter: | Pavel Pushkarev | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0.38 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | insert delayed, myisam | ||
[27 Jun 2007 9:10]
Sveta Smirnova
Thank you for the report. I can not repeat described behaviour: all INSERT DELAYED statements return no error: SHOW TABLE STATUS LIKE 't1'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t1 MyISAM 10 Fixed 0 0 0 327679 1024 0 NULL 2007-06-27 12:03:53 2007-06-27 12:03:53 NULL latin1_swedish_ci NULL max_rows=1 insert into t1 values(1); ERROR HY000: The table 't1' is full insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); insert delayed into t1 values(1); select count(*) from t1; count(*) 65535 insert into t1 values(1); ERROR HY000: The table 't1' is full insert delayed into t1 values(1); insert delayed into t1 values(1); select count(*) from t1; count(*) 65535 Please upgrade to current 5.0.41 or 5.0.42 version, try with it and if one of INSERT DELAYED statement fails provide accurate version of your operating system, MySQL package (file name if you downloaded it from MySQL web site) and exact actions we need to issue to repeat the problem. Also please note error is INSERT DELAYED failure, not success. See also comment by Sergei Golubchik to bug #29238
[27 Jun 2007 9:32]
Pavel Pushkarev
The problem is that INSERT DELAYED does NOT show any error - just the way you have seen in your test. Suppose I have a select-and-insert MyISAM table (I will never delete rows from it). When it becomes full, I want to extend the pointer size. I use DDL for it: root@localhost test > INSERT INTO f VALUES (0); ERROR 1114 (HY000): The table 'f' is full root@localhost test > INSERT DELAYED INTO f VALUES (0); Query OK, 1 row affected (0.00 sec) root@localhost test > INSERT DELAYED INTO f VALUES (0); Query OK, 1 row affected (0.00 sec) root@localhost test > SELECT COUNT(*) FROM f; +----------+ | COUNT(*) | +----------+ | 65535 | +----------+ 1 row in set (0.05 sec) root@localhost test > ALTER TABLE f MAX_ROWS=100000; Query OK, 65535 rows affected (0.04 sec) Records: 65535 Duplicates: 0 Warnings: 0 root@localhost test > SELECT COUNT(*) FROM f; +----------+ | COUNT(*) | +----------+ | 65535 | +----------+ 1 row in set (0.00 sec) Here we can see, that DDL actually cancelled all pending INSERT DELAYED's (as it should have, no problem here). But I have inserted a row and expect it to get into the table somewhen. It will never get into the table - yet I get no error.
[27 Jun 2007 10:14]
Sveta Smirnova
Thank you for the feedback. But INSERT DELAYED returns no error by design. This is documented and you have to change logic of the application if you want to get error. So I'll close this report as "Not a Bug"
Description: INSERT DELAYED checks free table space incorrectly on MyISAM storage engine. In the example below the first INSERT DELAYED must fail the same way as the second one. There is no error message and the actual insert is not made to the table. Possibly, the engine is waiting for the table to get "freed by other threads", but none are locking it anyway. How to repeat: root@localhost test > SHOW TABLE STATUS LIKE 'f'\G *************************** 1. row *************************** Name: f Engine: MyISAM Version: 10 Row_format: Fixed Rows: 65535 Avg_row_length: 5 Data_length: 327675 Max_data_length: 327679 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2007-06-25 16:15:34 Update_time: 2007-06-25 16:16:00 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: max_rows=1 Comment: 1 row in set (0.00 sec) root@localhost test > DESC f; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | NO | | | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) root@localhost test > INSERT INTO f VALUES (0); ERROR 1114 (HY000): The table 'f' is full root@localhost test > INSERT DELAYED INTO f VALUES (0); Query OK, 1 row affected (0.00 sec) root@localhost test > INSERT DELAYED INTO f VALUES (0), (0); ERROR 1114 (HY000): The table 'f' is full Suggested fix: Make the same kind of check as in non-DELAYED version (possibly, "<" instead of "<=").