[Maria-discuss] ON update timestamp replication
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
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
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
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
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
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
participants (3)
-
Artem Kuchin -
Jean-Francois B. Gagne -
Pavel Ivanov