← Back to team overview

maria-discuss team mailing list archive

Re: ON update timestamp replication

 

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





Follow ups

References