maria-discuss team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

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 <artem@xxxxxxxx> 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 <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
>
>
>