Bug #29047 Existing rows deleted when new rows inserted

• Submitted: 12 Jun 2007 11:16
• Modified: 12 Jul 2007 13:11
• Reporter: Tom Bamford
• Status: No Feedback
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 5.0.22-Debian_0ubuntu6.06-log
• OS: Linux (Ubuntu Dapper 6.06 LTS)
• CPU Architecture: Any
• Tags: rows deleted disappear

[12 Jun 2007 11:16] Tom Bamford

Description:
I am trying to insert rows into a MyIsam table with 4 fields, all of them are int(10) unsigned. After inserting about 8-10 new rows, they all disappear. There are two existing rows in the same table that were inserted some time ago, they are not being deleted, only the new rows. This happens even after I disconnect all mysql clients from the server and restart mysql, so it is not a rogue client.

How to repeat:
CREATE TABLE `ebay_items_stock` (
  `organisation_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `stock_id` int(10) unsigned NOT NULL,
  `purchase_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`organisation_id`,`item_id`,`stock_id`),
  KEY `purchase_id` (`purchase_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (20, 1, 6, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (20, 2, 27, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (20, 3, 67, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (20, 4, 70, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (20, 6, 42, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (20, 10, 73, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (42, 1, 1, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (56, 1, 1, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (55, 1, 3, 0);
INSERT INTO `ebay_items_stock` (`organisation_id`, `item_id`, `stock_id`, `purchase_id`) VALUES (55, 2, 7, 0);

[12 Jun 2007 11:18] Tom Bamford

Got the OS wrong, changed.

[12 Jun 2007 11:20] Tom Bamford

Just to be clear, the rows all vanish at the same time as a single new row is inserted.

[12 Jun 2007 12:58] Tom Bamford

Just remember something that may be relevant. About 20 mins before this started happening I issued this query to effect the table:

delete from ebay_items_stock where item_id not in (select item_id from ebay_items)

[12 Jun 2007 13:11] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.41, and inform about the results. I ncase of the same problem, run CHECK TABLE ... EXTENDED on the table and try again.

[12 Jul 2007 23:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".