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