maria-discuss team mailing list archive

Thread
Date
Re: [External] Re: ON update timestamp replication

To: Artem Kuchin <artem@xxxxxxxx>
From: "Jean-Francois B. Gagne" <jeanfrancois.gagne@xxxxxxxxxxx>
Date: Sat, 14 Jan 2017 08:09:06 +0100
Cc: MariaDB discuss <maria-discuss@xxxxxxxxxxxxxxxxxxx>
In-reply-to: <cc79ee79-8f46-968b-cf6e-29034470faf9@artem.ru>
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 <artem@xxxxxxxx> 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 <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
>>>>>
>>>>
>>>
>>>
>
> _______________________________________________
> 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
>
References

ON update timestamp replication
From: Artem Kuchin, 2016-11-28
Re: ON update timestamp replication
From: Pavel Ivanov, 2016-11-29
Re: ON update timestamp replication
From: Artem Kuchin, 2016-11-30
Re: ON update timestamp replication
From: Pavel Ivanov, 2016-11-30
Re: ON update timestamp replication
From: Artem Kuchin, 2016-12-01