maria-discuss team mailing list archive

[Artem Kuchin <artem@xxxxxxxx>]

Hello!

Both master and slave run:
Server version: 10.1.14-MariaDB FreeBSD Ports

Replication is based on mixed format.
Slave is used for backups without locking and going off line.

I have a script  that runs once a day and checksums all tables, so all 
tables
are 100% in match (it wait for sync, locks and checksums).

One table is very often out of sync.

ON MASTER:

show create table Message193;
CREATE TABLE `Message193` (
  `Message_ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) NOT NULL,
  `Subdivision_ID` int(11) NOT NULL,
  `Sub_Class_ID` int(11) NOT NULL,
  `Priority` int(11) NOT NULL DEFAULT '0',
  `Keyword` char(255) NOT NULL,
  `ncTitle` varchar(255) DEFAULT NULL,
  `ncKeywords` varchar(255) DEFAULT NULL,
  `ncDescription` text,
  `ncSMO_Title` varchar(255) DEFAULT NULL,
  `ncSMO_Description` text,
  `ncSMO_Image` char(255) DEFAULT NULL,
  `Checked` tinyint(4) NOT NULL DEFAULT '1',
  `IP` char(15) DEFAULT NULL,
  `UserAgent` char(255) DEFAULT NULL,
  `Parent_Message_ID` int(11) NOT NULL DEFAULT '0',
  `Created` datetime NOT NULL,
  `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
  `LastUser_ID` int(11) NOT NULL,
  `LastIP` char(15) DEFAULT NULL,
  `LastUserAgent` char(255) DEFAULT NULL,
  `Type` char(255) DEFAULT NULL,
  `Title` char(255) DEFAULT NULL,
  `Background` char(255) DEFAULT NULL,
  `Link` char(255) DEFAULT NULL,
  PRIMARY KEY (`Message_ID`),
  UNIQUE KEY `Sub_Class_ID` (`Sub_Class_ID`,`Message_ID`,`Keyword`),
  KEY `User_ID` (`User_ID`),
  KEY `LastUser_ID` (`LastUser_ID`),
  KEY `Subdivision_ID` (`Subdivision_ID`),
  KEY `Parent_Message_ID` (`Parent_Message_ID`),
  KEY `Priority` (`Priority`,`LastUpdated`),
  KEY `Checked` (`Checked`),
  KEY `Created` (`Created`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |


ON SLAVE

CREATE TABLE `Message193` (
  `Message_ID` int(11) NOT NULL AUTO_INCREMENT,
  `User_ID` int(11) NOT NULL,
  `Subdivision_ID` int(11) NOT NULL,
  `Sub_Class_ID` int(11) NOT NULL,
  `Priority` int(11) NOT NULL DEFAULT '0',
  `Keyword` char(255) NOT NULL,
  `ncTitle` varchar(255) DEFAULT NULL,
  `ncKeywords` varchar(255) DEFAULT NULL,
  `ncDescription` text,
  `ncSMO_Title` varchar(255) DEFAULT NULL,
  `ncSMO_Description` text,
  `ncSMO_Image` char(255) DEFAULT NULL,
  `Checked` tinyint(4) NOT NULL DEFAULT '1',
  `IP` char(15) DEFAULT NULL,
  `UserAgent` char(255) DEFAULT NULL,
  `Parent_Message_ID` int(11) NOT NULL DEFAULT '0',
  `Created` datetime NOT NULL,
  `LastUpdated` timestamp NOT NULL,
  `LastUser_ID` int(11) NOT NULL,
  `LastIP` char(15) DEFAULT NULL,
  `LastUserAgent` char(255) DEFAULT NULL,
  `Type` char(255) DEFAULT NULL,
  `Title` char(255) DEFAULT NULL,
  `Background` char(255) DEFAULT NULL,
  `Link` char(255) DEFAULT NULL,
  PRIMARY KEY (`Message_ID`),
  UNIQUE KEY `Sub_Class_ID` (`Sub_Class_ID`,`Message_ID`,`Keyword`),
  KEY `User_ID` (`User_ID`),
  KEY `LastUser_ID` (`LastUser_ID`),
  KEY `Subdivision_ID` (`Subdivision_ID`),
  KEY `Parent_Message_ID` (`Parent_Message_ID`),
  KEY `Priority` (`Priority`,`LastUpdated`),
  KEY `Checked` (`Checked`),
  KEY `Created` (`Created`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |


Notice that   `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP,
on slave becomes
`LastUpdated` timestamp NOT NULL,

This is already bad enough because if master is lost then slave will not 
be the same as master, trigger are not restored.

Now check  records:

MASTER
MariaDB [anapolisdom_ru]> select * from Message193 limit 1\G
*************************** 1. row ***************************

          Created: 2016-11-25 13:25:09
      LastUpdated: 2016-11-25 13:25:09


MariaDB [anapolisdom_ru]> select * from Message193 limit 1\G


          Created: 2016-11-25 13:25:09
      LastUpdated: 0000-00-00 00:00:00


As you see  lastupdated field is not replicated at all.


Two questions, problems:

1) How to replicate default value and on update part for this field?

2) How to make it replicate the timestamp value?


Artem