maria-discuss team mailing list archive

[Artem Kuchin <artem@xxxxxxxx>]

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