maria-discuss team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

You really need to make schema of this table to be the same on the
master and on the slave. The value of LastUpdated is getting out of
sync because when master executes an INSERT statement without a value
for LastUpdated, the master will automatically insert the current time
into LastUpdated column because the table definition has DEFAULT
CURRENT_TIMESTAMP. But then the master will write the executed
statement as is into binlog and send it as is to the slave. And when
slave executes the same statement it won't insert current time into
LastUpdated field because the table definition on the slave doesn't
have the DEFAULT clause. When you make the table definitions the same,
the timestamp value will be replicated properly.

On Mon, Nov 28, 2016 at 3:13 AM, Artem Kuchin <artem@xxxxxxxx> wrote:
> 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
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp