| Bug #9719 | DELETE with WHERE on HEAP table just deletes first row of matched set | ||
|---|---|---|---|
| Submitted: | 7 Apr 2005 14:00 | Modified: | 4 Aug 2006 3:41 |
| Reporter: | Samuel Benzaquen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Memory storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1.11 (RPM) | OS: | Any (*) |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[11 Apr 2005 13:12]
Geert Vanderkelen
Hi Samuel, I could not reproduce on 4.1.11, using your table, and mysql> insert into sendmail_from (fecha) values (now()); Not sure what you mean with "fecha = null for now()"? Regards, Geert
[11 Apr 2005 15:43]
Samuel Benzaquen
With fecha = null for now() I meant that I used the NULL value on the SQL sentence, in consequence the inserted value was the defualt value for a timestamp column (now()). I could not reproduce the problem on any other table, just on that especific one. And as I said before, I dropped the table completely and recreated it and the behavior continued. Is there any way to make a dump or something about what the server is doing with the query?
[11 Apr 2005 16:08]
Geert Vanderkelen
Hi Samuel, 1. your fecha is 'not null', can not insert NULL values 2. default for a DATETIME field is 0000-00-00 00:00:00 Regards, Geert
[11 Apr 2005 18:26]
Samuel Benzaquen
Right... I got confused with another development I have at the moment. I do insert the timestamp for this table. Sorry for the mess on the report. The thing is that I am too focused on the DELETE statement that fails that I wasn't paying attention on the INSERT statement when I created the report. The insert is done by a perl script (DBI 1.4 - DBDmysql 2.9004) with a prepared statement. The delete is tried via the same script (without prepared statement) and via the mysql client. I think I can reproduce the problem now on any table. What I did: CREATE TEMPORARY TABLE b LIKE sendmail_from; INSERT INTO b (SELECT * FROM sendmail_from); DELETE FROM sendmail_from WHERE fecha < '2005-04-11 13:15:00'; (works fine) To break the table I do the following statement: CREATE TEMPORARY TABLE from_temp AS SELECT id_correo, relay_ip, relay_host FROM b WHERE fecha BETWEEN now() - INTERVAL 2 MINUTE AND now() - INTERVAL 1 MINUTE; (this statement was added to devel app around a week ago and matches with when the problem started) DELETE FROM sendmail_from WHERE fecha < '2005-04-11 13:16:00'; (deletes just one row) I tried reproducing it with artificial data and I can't do it. For now it just happens with data I insert from my perl script and I don't how much data is needed. I'll try to find some (small) data that triggers the problem. The table has more than 60.000 rows.
[11 Apr 2005 19:19]
Samuel Benzaquen
This script seems to trigger the problem:
CREATE TEMPORARY TABLE dummy (
`fecha` datetime NOT NULL default '0000-00-00 00:00:00',
KEY `fecha` TYPE BTREE (`fecha`)
) ENGINE=HEAP DEFAULT CHARSET=latin1;
INSERT INTO dummy (fecha) VALUES ('2005-04-11 14:23:1'),('2005-04-11 14:23:2'),('2005-04-11 14:23:3'),('2005-04-11 14:23:4'),('2005-04-11 14:23:5'),
('2005-04-11 14:23:6'),('2005-04-11 14:23:7'),('2005-04-11 14:23:8'),('2005-04-11 14:23:9'),('2005-04-11 14:23:10'),('2005-04-11 14:23:11'),('2005-04-11 14:23:12'),('2005-04-11 14:23:13'),('2005-04-11 14:23:14'),('2005-04-11 14:23:15'),('2005-04-11 14:23:16'),('2005-04-11 14:23:17'),('2005-04-11 14:23:18'),('2005-04-11 14:23:19'),('2005-04-11 14:23:20'),('2005-04-11 14:23:21'),('2005-04-11 14:23:22'),('2005-04-11 14:23:23'),('2005-04-11 14:23:24'),('2005-04-11 14:23:19'),('2005-04-11 14:23:26'),('2005-04-11 14:23:27'),('2005-04-11 14:23:28'),('2005-04-11 14:23:29'),('2005-04-11 14:23:30'),('2005-04-11 14:23:31'),('2005-04-11 14:23:32'),('2005-04-11 14:23:33'),('2005-04-11 14:23:34'),('2005-04-11 14:23:35'),('2005-04-11 14:23:36'),('2005-04-11 14:23:37'),('2005-04-11 14:23:38'),('2005-04-11 14:23:39'),('2005-04-11 14:23:40'),('2005-04-11 14:23:41'),('2005-04-11 14:23:42'),('2005-04-11 14:23:43'),('2005-04-11 14:23:44'),('2005-04-11 14:23:45'),('2005-04-11 14:23:46'),('2005-04-11 14:23:47'),('2005-04-11 14:23:48'),('2005-04-11 14:23:49'),('2005-04-11 14:23:50'),('2005-04-11 14:23:51'),('2005-04-11 14:23:52'),('2005-04-11 14:23:53'),('2005-04-11 14:23:54'),('2005-04-11 14:23:55'),('2005-04-11 14:23:53'),('2005-04-11 14:23:54'),('2005-04-11 14:23:57'),('2005-04-11 14:23:58'),('2005-04-11 14:23:59');
SELECT count(*) as PRIMERO FROM dummy;
SELECT * FROM dummy;
CREATE TEMPORARY TABLE from_temp AS SELECT 1 FROM dummy WHERE fecha BETWEEN '2005-04-11 14:23:10' AND '2005-04-11 14:23:20';
DELETE FROM dummy WHERE fecha < '2005-04-11 14:23:20';
SELECT count(*) as SEGUNDO FROM dummy;
SELECT * FROM dummy;
DELETE FROM dummy WHERE fecha < '2005-04-11 14:23:20';
SELECT count(*) as SEGUNDO FROM dummy;
SELECT * FROM dummy;
---------
If you ommit the 'CREATE TEMPORARY TABLE from_temp AS SELECT' line the DELETE queries work as expected.
[12 Apr 2005 7:37]
Geert Vanderkelen
Hi Samuel, Thanks for the report! I could reproduce it. Doesn't happen with HASH indexes, so maybe use HASH in Memory tables for now. On your site, tables using the Memory engine that work, are using HASH or BTREE? Regards, Geert
[12 Apr 2005 12:35]
Samuel Benzaquen
I just use BTREE indexes for date columns to erase any row that is older than some specific timestamp. BTREEs work fine on any other table. On that particular table, they work fine until I do that specific query, so I will reverse the changes in my code till it is fixed on MySQL. Thanks for your time.
[12 Apr 2005 13:00]
Samuel Benzaquen
As a side note, I have noticed that when I do: SELECT max(fecha), min(fecha) FROM sendmail_from it has different behavior on MyISAM and MEMORY, both with a BTREE index on that column. On MyISAM, the statement is constant (as expected); while on MEMORY, it uses a table scan. I don't know if that is a bug or is just that that feature hasn't been coded yet on MEMORY BTREEs.
[5 Jul 2006 9: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/8742
[18 Jul 2006 23:51]
Timothy Smith
Looks good, Ramil. Thank you.
[2 Aug 2006 17:05]
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/9964 ChangeSet@1.2520, 2006-08-02 13:06:59-04:00, cmiller@zippy.cornsilk.net +3 -0 Bug#9719: DELETE with WHERE on HEAP table just deletes first row of matched set. (Ramil's patch, recreated.)
[2 Aug 2006 18:56]
Chad MILLER
Pushed into 4.1.21 .
[4 Aug 2006 3:41]
Paul DuBois
Noted in 4.1.22 (not 4.1.21) changelog. DELETE with WHERE condition on a BTREE-indexed column for a MEMORY table deleted only the first matched row.
[10 Aug 2006 14:56]
Chad MILLER
Added to 5.0.25 .
Description: This used to work just yesterday. I have a problem deleting from a HEAP table. When I do: DELETE FROM table WHERE date < 'some_date'; it just deletes the first row that matches. But, if I add an order by clause it deletes all rows (as expected): DELETE FROM table WHERE date < 'some_date' ORDER BY date; date has a BTREE index on it. table has other indexes. I have others HEAP table with similar structure and the same delete on those tables work fine. I've dropped the table and created again. I've restarted the server. No change. I upgraded from 4.1.9 to 4.1.11 to see if it fixes the problem. No change. How to repeat: It now happens everytime I try to delete from that table. CREATE TABLE IF NOT EXISTS sendmail_from ( fecha datetime not null, servidor char(7) binary not null, id_correo char(14) binary not null, address_from varchar(255), size mediumint unsigned not null, nrcpts tinyint not null, relay_host varchar(255), relay_ip char(15) not null, INDEX USING HASH (relay_host), INDEX USING HASH (relay_ip), INDEX USING HASH (id_correo), INDEX USING BTREE (fecha) ) ENGINE = MEMORY; INSERT some data with fecha = null for now(). DELETE FROM sendmail_from WHERE fecha < 'some_date';