I am jumping in this thread late... The difference between table structure in master and slave might be because weird legacy default behavior of the first TIMSTAMP column in a table.
In [1], you have: "The following rules describe the possibilities for defining the first TIMESTAMP column in a table [...] With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP." [1]: https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html This changes in [2] with explicit_defaults_for_timestamp. [2]: https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html Depending on the way those boxes were upgraded from 5.5 to 10.1, and the content of the my.cnf, you could end-up with some strange table definition (but I do not know how this would have happened). I hope this helps, JFG On 1 December 2016 at 07:54, Artem Kuchin <ar...@artem.ru> wrote: > The only person with any access to slave virtual server is me. > So, nobody affected slave for sure 100%. > > > 30.11.2016 17:43, Pavel Ivanov пишет: > > What was the table schema in the beginning, before it became different >> on master and slave? If it was with the default value then maybe >> someone with SUPER privilege executed on the slave "ALTER TABLE >> Message193 CHANGE COLUMN LastUpdated timestamp NOT NULL". If it was >> without the default value then the default was added without >> binlogging, i.e. someone with SUPER privilege executed on the master >> "SET SESSION sql_log_bin = 0; ALTER TABLE Message193 CHANGE COLUMN >> LastUpdated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE >> CURRENT_TIMESTAMP". >> >> On Wed, Nov 30, 2016 at 6:04 AM, Artem Kuchin <ar...@artem.ru> wrote: >> >>> I understand that. I don't understand how it happened that table schema >>> on >>> slave is not the same >>> as on master. Any idea? >>> >>> 29.11.2016 19:42, Pavel Ivanov пишет: >>> >>> 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 <ar...@artem.ru> 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@lists.launchpad.net >>>>> Unsubscribe : https://launchpad.net/~maria-discuss >>>>> More help : https://help.launchpad.net/ListHelp >>>>> >>>> >>> >>> > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
