MySQL Bugs: #56281: [ERROR] Slave SQL: Table definition on master and slave does not match

Bug #56281	[ERROR] Slave SQL: Table definition on master and slave does not match
Submitted:	26 Aug 2010 6:51	Modified:	10 Sep 2010 18:04
Reporter:	Anil Alpati	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Below is the datatype mismatch error which I'm getting while replication -

100825 23:26:26 [ERROR] Slave SQL: Table definition on master and slave does not match: Column 16 type mismatch - received type 252, mifos.loan_account has type 15, Error_code: 1535

I have cross checked the MASTER and SLAVE databases - EVERY THING LOOKS PERFECTLY ALRIGHT

How to repeat:
Herewith I'm giving you binary log file in which my replications breaks - 

#100823 13:13:10 server id 1  end_log_pos 2440732 	Write_rows: table id 77 flags: STMT_END_F

BINLOG '
jiZyTBMBAAAAWgAAALg9JQAAAE0AAAAAAAAABW1pZm9zAAxsb2FuX2FjY291bnQAGwMDAwIC9gL2
AgL2AgMCAgr8AgICAg8CAwICAgkVBBUEDQoC/QL2v78H
jiZyTBcBAAAAZAAAABw+JQAQAE0AAAAAAAEAG/////8UKKD/LNQ6AHUGAAABAIAAAAAAAAV4AAAC
AIAAAAAAAAV4AAACAAIAgBgAAAAAAMwbMQAUABu1DwAAAAAAAAAADwAAAA==
'/*!*/;
### INSERT INTO schemaname.tablename
### SET
###   @1=3855404
###   @2=1653
###   @3=NULL
###   @4=1
###   @5=NULL
###   @6=000001400.000000000
###   @7=2
###   @8=000001400.000000000
###   @9=2
###   @10=2
###   @11=000000024.000000000.000000000
###   @12=NULL
###   @13=3218380
###   @14=NULL
###   @15=20
###   @16='2010:08:27'
###   @17=''
###   @18=0
###   @19=0
###   @20=0
###   @21=15
###   @22=NULL
###   @23=0
###   @24=NULL
###   @25=NULL
###   @26=NULL
###   @27=NULL
# at 2440732
# at 2440797

TABLE DESCRIPTion

Attachment: resultset.html (text/html), 3.14 KiB.

In the bin log file - if you see @11 column its displays -@11=000000024.000000000.000000000

But datatype is decimal(13,10) as attached is same for both master and slave

Right now I have datatype to text...but there is no output.

Please, send the results of:

show create table t\G

for the problematic table from both master and slave.

mysql> show create table loan_account \G
*************************** 1. row ***************************
       Table: loan_account
Create Table: CREATE TABLE `loan_account` (
  `ACCOUNT_ID` int(11) NOT NULL DEFAULT '0',
  `BUSINESS_ACTIVITIES_ID` int(11) DEFAULT NULL,
  `COLLATERAL_TYPE_ID` int(11) DEFAULT NULL,
  `GRACE_PERIOD_TYPE_ID` smallint(6) NOT NULL DEFAULT '0',
  `GROUP_FLAG` smallint(6) DEFAULT NULL,
  `LOAN_AMOUNT` decimal(21,4) DEFAULT NULL,
  `LOAN_AMOUNT_CURRENCY_ID` smallint(6) DEFAULT NULL,
  `LOAN_BALANCE` decimal(21,4) DEFAULT NULL,
  `LOAN_BALANCE_CURRENCY_ID` smallint(6) DEFAULT NULL,
  `INTEREST_TYPE_ID` smallint(6) DEFAULT NULL,
  `INTEREST_RATE` decimal(13,10) DEFAULT NULL,,
  `FUND_ID` smallint(6) DEFAULT NULL,
  `MEETING_ID` int(11) DEFAULT NULL,
  `CURRENCY_ID` smallint(6) DEFAULT NULL,
  `NO_OF_INSTALLMENTS` smallint(6) NOT NULL DEFAULT '0',
  `DISBURSEMENT_DATE` date DEFAULT NULL,
  `collateral_note` varchar(500) DEFAULT NULL,
  `GRACE_PERIOD_DURATION` smallint(6) DEFAULT NULL,
  `INTEREST_AT_DISB` smallint(6) DEFAULT NULL,
  `GRACE_PERIOD_PENALTY` smallint(6) DEFAULT NULL,
  `PRD_OFFERING_ID` smallint(6) DEFAULT NULL,
  `t_collateral_note` varchar(255) DEFAULT NULL,
  `REDONE` smallint(6) NOT NULL,
  `PARENT_ACCOUNT_ID` int(11) DEFAULT NULL,
  `MONTH_RANK` smallint(6) DEFAULT NULL,
  `MONTH_WEEK` smallint(6) DEFAULT NULL,
  `RECUR_MONTH` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`ACCOUNT_ID`),
  KEY `CURRENCY_ID` (`CURRENCY_ID`),
  KEY `LOAN_AMOUNT_CURRENCY_ID` (`LOAN_AMOUNT_CURRENCY_ID`),
  KEY `LOAN_BALANCE_CURRENCY_ID` (`LOAN_BALANCE_CURRENCY_ID`),
  KEY `FUND_ID` (`FUND_ID`),
  KEY `GRACE_PERIOD_TYPE_ID` (`GRACE_PERIOD_TYPE_ID`),
  KEY `INTEREST_TYPE_ID` (`INTEREST_TYPE_ID`),
  KEY `MEETING_ID` (`MEETING_ID`),
  KEY `COLLATERAL_TYPE_ID` (`COLLATERAL_TYPE_ID`),
  KEY `BUSINESS_ACTIVITIES_ID` (`BUSINESS_ACTIVITIES_ID`),
  KEY `PRD_OFFERING_ID` (`PRD_OFFERING_ID`),
  KEY `fk_loan_account` (`PARENT_ACCOUNT_ID`),
  CONSTRAINT `fk_loan_account` FOREIGN KEY (`PARENT_ACCOUNT_ID`) REFERENCES `loan_account` (`ACCOUNT_ID`),
  CONSTRAINT `fk_loan_acc_id` FOREIGN KEY (`PARENT_ACCOUNT_ID`) REFERENCES `account` (`ACCOUNT_ID`),
  CONSTRAINT `fk_loan_bus_act_id` FOREIGN KEY (`BUSINESS_ACTIVITIES_ID`) REFERENCES `lookup_value` (`LOOKUP_ID`),
  CONSTRAINT `fk_loan_col_type_id` FOREIGN KEY (`COLLATERAL_TYPE_ID`) REFERENCES `lookup_value` (`LOOKUP_ID`),
  CONSTRAINT `fk_loan_prd_off_id` FOREIGN KEY (`PRD_OFFERING_ID`) REFERENCES `prd_offering` (`PRD_OFFERING_ID`),
  CONSTRAINT `loan_account_ibfk_1` FOREIGN KEY (`ACCOUNT_ID`) REFERENCES `account` (`ACCOUNT_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_13` FOREIGN KEY (`PARENT_ACCOUNT_ID`) REFERENCES `account` (`ACCOUNT_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_2` FOREIGN KEY (`CURRENCY_ID`) REFERENCES `currency` (`CURRENCY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_3` FOREIGN KEY (`LOAN_AMOUNT_CURRENCY_ID`) REFERENCES `currency` (`CURRENCY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_4` FOREIGN KEY (`LOAN_BALANCE_CURRENCY_ID`) REFERENCES `currency` (`CURRENCY_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_6` FOREIGN KEY (`FUND_ID`) REFERENCES `fund` (`FUND_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_7` FOREIGN KEY (`GRACE_PERIOD_TYPE_ID`) REFERENCES `grace_period_type` (`GRACE_PERIOD_TYPE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `loan_account_ibfk_8` FOREIGN KEY (`INTEREST_TYPE_ID`) REFERENCES `interest_types` (`INTEREST_TYPE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

table structure is same in both master and slave

Any updates ??

I still would like to see SHOW CREATE TABLE results from both master and slave, and check if they are the same myself.

I still would like to see SHOW CREATE TABLE results from both master and slave, and check if they are the same myself.

BOth are same

Thank you for the feedback.

I can repeat error mentioned trying to repeat the problem:

1. Setup master-slave environment.
On master:
2. create database mifos;
3. use mifos
4. create table loan_account ... (see definition from comment above, but skip all constraints);
5. mysql> #100823 13:13:10 server id 1  end_log_pos 2440732 Write_rows: table id 77 flags: STMT_END_F
mysql> 
mysql> BINLOG '                                                                                   
jiZyTBMBAAAAWgAAALg9JQAAAE0AAAAAAAAABW1pZm9zAAxsb2FuX2FjY291bnQAGwMDAwIC9gL2
AgL2AgMCAgr8AgICAg8CAwICAgkVBBUEDQoC/QL2v78H
jiZyTBcBAAAAZAAAABw+JQAQAE0AAAAAAAEAG/////8UKKD/LNQ6AHUGAAABAIAAAAAAAAV4AAAC
AIAAAAAAAAV4AAACAAIAgBgAAAAAAMwbMQAUABu1DwAAAAAAAAAADwAAAA==
'/*!*/;
ERROR 1105 (HY000): Unknown error
6. $cat hostname.err 
100828  0:50:54 [ERROR] Slave SQL: Table definition on master and slave does not match: Column 16 type mismatch - received type 252, mifos.loan_account has type 15, Error_code: 1535
100828  0:51:59 [ERROR] Slave SQL: Table definition on master and slave does not match: Column 16 type mismatch - received type 252, mifos.loan_account has type 15, Error_code: 1535

Strange why I get "Slave SQL" on master. Do you get same error on master or on slave?

It worked well in master with out any issues.

For your information - I have totally two slaves -

First Slave is on Windows  - Working 
Second Slave is on Linux   - Not working

MASTER is on LINUX .

Awaiting from feedback at your earliest

Thank you for the feedback.

Please send output of SHOW CREATE TABLE loan_account;, SHOW TABLE STATUS LIKE 'loan_account' and CHECK TABLE loan_account taken on problem slave.

Please also send output of SHOW CREATE TABLE loan_account;, SHOW TABLE STATUS LIKE 'loan_account' taken on master.

It is important to compare what these queries outputs, because even if you were sure tables are same when you created it there can be corruption or some other bad thing.

Attached the requested information. please check and let me know at your earliest

Attached the requested info.

Attachment: Table_info.zip (application/zip, text), 2.64 KiB.

Thank you for the feedback.

Diff showed table definitions from master and slave don't match:

$diff -u Tableinfo_4m_master.sql Table_info_4m_slave.sql 
--- Tableinfo_4m_master.sql     2010-08-30 12:11:41.000000000 +0400
+++ Table_info_4m_slave.sql     2010-08-30 12:05:09.000000000 +0400
@@ -1,5 +1,4 @@
-./mysql -ureaduser -p54209lpsfg -h203.200.1.84 -P3306 mifos -e "show create table loan_account \G" >> /home/anil/Desktop/Tableinfo_4m_master.sql
-
+mysql> SHOW CREATE TABLE mifos.loan_account \G
 *************************** 1. row ***************************
        Table: loan_account
 Create Table: CREATE TABLE `loan_account` (
@@ -19,7 +18,7 @@
   `CURRENCY_ID` smallint(6) DEFAULT NULL,
   `NO_OF_INSTALLMENTS` smallint(6) NOT NULL DEFAULT '0',
   `DISBURSEMENT_DATE` date DEFAULT NULL,
-  `COLLATERAL_NOTE` text,
+  `collateral_note` varchar(500) DEFAULT NULL,
   `GRACE_PERIOD_DURATION` smallint(6) DEFAULT NULL,
   `INTEREST_AT_DISB` smallint(6) DEFAULT NULL,
   `GRACE_PERIOD_PENALTY` smallint(6) DEFAULT NULL,

So this is not a bug.