| Bug #46479 | mysql dump has wrong records | ||
|---|---|---|---|
| Submitted: | 30 Jul 2009 15:44 | Modified: | 31 Jul 2009 14:46 |
| Reporter: | Rajakumar Velu | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.45 | OS: | Linux (Red Hat Enterprise Linux Server release 5.2) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | dump issue, restore issue | ||
[30 Jul 2009 15:44]
Rajakumar Velu
[30 Jul 2009 16:20]
Valeriy Kravchuk
Are you sure your table is not corrupted? Please, check with CHECK TABLE first. If CHECK TABLE does not report the problem, but you still see that data are wrong, please, send SHOW CREATE TABLE and SHOW TABLE STATUS results for the problematic table.
[30 Jul 2009 17:26]
Rajakumar Velu
I didn't received any error while checking tables, actually it has wrong values on the table, if I execute a query on master then 0 output the same of slave as | D172641 | 7234604 | | D173952 | 7234963 | +---------+----------+ 104 rows in set (5.94 sec) mysql> select count(*) from order_item; +----------+ | count(*) | +----------+ | 8700858 | +----------+ 1 row in set (2.40 sec) this also same mysql> check tables order_item; +-----------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+-------+----------+----------+ | test.order_item | check | status | OK | +-----------------+-------+----------+----------+ 1 row in set (1 min 21.48 sec) ------------------------------ mysql> SHOW CREATE TABLE order_item; +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | order_item | CREATE TABLE `order_item` ( `order_item_id` int(11) NOT NULL auto_increment, `order_id` int(11) default NULL, `item` varchar(30) default NULL, `item_type` int(11) default NULL, `item_price` decimal(7,2) default NULL, `date_entered` datetime default NULL, `item_price_offset` decimal(7,2) default NULL, `tax_cost` decimal(7,2) default NULL, `item_num` int(11) default NULL, `status` int(11) NOT NULL default '1', `last_modified` datetime default NULL, `date_shipped` datetime default NULL, `company_id` int(11) default NULL, `ship_tax_cost` decimal(7,2) default NULL, `ship_cost` decimal(7,2) default NULL, `refund_amount` decimal(7,2) default NULL, `status_royalty` int(11) default '0', `status_reporting` int(11) default '1', `alias` varchar(128) default NULL, `offer_id` varchar(15) default NULL, `offer_rev_num` int(11) default NULL, `royalty_version` int(11) default '2', `misc_credit` decimal(7,2) NOT NULL default '0.00', `audit` int(2) default '0', PRIMARY KEY (`order_item_id`), KEY `order_item_idx1` (`order_id`), KEY `order_item_idx2` (`item`), KEY `order_item_idx3` (`company_id`), KEY `order_item_idx4` (`status_reporting`), KEY `order_item_idx5` (`status`), KEY `order_item_idx6` (`alias`), KEY `order_item_idx7` (`item`,`company_id`,`status`,`date_entered`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------------------------------------------------------------------
[30 Jul 2009 17:36]
Valeriy Kravchuk
So, some query gives you different results of master and on slave, immediately after you had restored dump that you've made on master, right? Are you sure that there were no changes on master while you were dumping and restoring? Can you send the exact query you execute and EXPLAIN results for it from master and slave?
[30 Jul 2009 17:39]
Rajakumar Velu
master is the live production DB.. slave :mysql> select item, order_id from order_item where date(date_shipped)='2000-01-01' and last_modified>'2009-07-01'; 104 rows in set (12.36 sec) Master : mysql> select item, order_id from order_item where date(date_shipped)='2000-01-01' and last_modified>'2009-07-01'; Empty set (3.66 sec) slave version ; mysql Ver 14.12 Distrib 5.0.22, Master Ver : mysql Ver 14.12 Distrib 5.0.45 is that version are OK.
[30 Jul 2009 17:47]
Valeriy Kravchuk
This: slave version ; mysql Ver 14.12 Distrib 5.0.22, Master Ver : mysql Ver 14.12 Distrib 5.0.45 is wrong configuration! Slave should NEVER be older than master. So, please, try to upgrade slave to 5.0.45 at least and check if the problem will be repeatable after that.
[31 Jul 2009 14:29]
Rajakumar Velu
I have tested it on 5.0.45 and looks good, actually I have checked with order_item table as we have issue on that only, Thank you, Regards, Rajakumar
[31 Jul 2009 14:46]
Valeriy Kravchuk
I am closing this as not repeatable in proper master-slave configuration, even with old 5.0.45.
[25 Aug 2009 6:18]
Roel Van de Paar
Rajakumar: you may also want to completely re-image your slave to ensure there's no data inconsistencies.
