On 30 Mar 2014 01:15, "Marco Tusa" <email address hidden> wrote:
>
> Ragu,
> I know very well that STATEMENT is not the official replication way for
>

>
>
> But this has NOTHING to do with that trick, but with the fact that
> galera is not enforcing the ROW format at session level.

This has been fixed in 5.6 and a warning is emitted in 5.5 (no error here
for backward compatibility).

>
> As such the pt-table-checksum is able to change it (Line 9097)
>           if ( $original_binlog_format !~ /STATEMENT/i ) {
>              $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
>              eval {
>                 PTDEBUG && _d($dbh, $sql);
>                 $dbh->do($sql);
>              };
>
>
> At the same time if I have other activity running on the other node ...
> or on this Master node, this activity is reported in ROW format:
>
> root@localhost:pm) [sakila]>insert into payment3 select * from payment2;
> #140329 15:18:19 server id 3  end_log_pos 4991358 Write_rows: table id
> 57 flags: STMT_END_F
> ### INSERT INTO sakila.payment3
> ### SET
> ###   @1=1 /* SHORTINT meta=0 nullable=1 is_null=0 */
> ###   @2=1 /* SHORTINT meta=0 nullable=0 is_null=0 */
> ###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ###   @4=76 /* INT meta=0 nullable=1 is_null=0 */
> ###   @5=000000002.990000000 /* DECIMAL(5,2) meta=1282 nullable=0
> is_null=0 */
> ###   @6=2005-05-25 11:30:37 /* DATETIME meta=0 nullable=0 is_null=0 */
> ###   @7=1140059550 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
> ### INSERT INTO sakila.payment3
>
>
> Given all the above, it seems that WE CAN use pt-table-checksum, but I
> am unsure about the GALERA stability, and WHY then we have that trick.
>
> The parameter wsrep_forced_binlog_format was introduced in Galera on the
> 2011-11-08 (Seppo comment
> https://bugs.launchpad.net/codership-mysql/+bug/887564).
> The trick seems from 4/24/12 Alex comment
> (
https://groups.google.com/forum/#!msg/codership-team/kKqigq31dVQ/TV-DTUI0YYYJ
)
>
> SO it comes AFTER, the wsrep_forced_binlog_format, and as such I am tent
> to assume it was introduced because there was a problem on the use of
> wsrep_forced_binlog_format...
>
> So after all this I think we can summarize my question as:
>
> "If we have to use the pt-table-checksum, will be the usage of
> wsrep_forced_binlog_format=NONE to be consider SAFE and CONSISTENT for
> galera replication, OR we have to assume that this parameter should
> NEVER be set/modify?
>
> In the case it cannot be used, what is the way then to use
> pt-table-checksum.
> In any case what is the reason of having the /*!99997*/ trick?
> "
> Should be consider all this as a documentation BUG or an dangerous
> exposed feature or a real bug?
>
>
> ------------------------------------------------
> Marco Tusa,
> Mobile: +1-613-282-7337
> do something for your planet!
> http://www.tusacentral.net/
> ------------------------------------------------
> Save a tree... please don't print this e-mail unless you really need to
>
> On 29/03/2014 03:27, Raghavendra D Prabhu wrote:
> >>    The issue is that when running pt-table-checksum on node1 all the
statement that pt-table-checksum execute to test the data
> >   > on the nodes, are replicated with ROW replication.
> >
> > This is because STATEMENT binlog format is not supported in
> > wsrep/galera.  pt-checksum possibly needs fixes for PXC in this regard.
> >
>
> --
> You received this bug notification because you are subscribed to MySQL
> patches by Codership.
> Matching subscriptions: wsrep
> https://bugs.launchpad.net/bugs/1298689
>
> Title:
>   Possible bug in the in MySQL + Galera and PXC when running PT-table-
>   checksum on a slave.
>
> Status in MySQL patches by Codership:
>   New
>
> Bug description:
>   Current nodes setup
>
>    node1 <-> node2 <-> node3
>     |
>     \/
>     Slave
>
>     The issue is that when running pt-table-checksum on node1 all the
statement that pt-table-checksum execute to test the data on the nodes, are
replicated with ROW replication.
>     To note that I am testing on percona toolkit 2.2.7 and the version of
pt-table-checksum is implementing the /*!99997*/ trick as describe at
https://groups.google.com/forum/#!msg/codership-team/kKqigq31dVQ/TV-DTUI0YYYJ
.
>
>     From MySQL point of view I have tested with PXC 5.5.34 and Codership
5.5.43 (wsrep 25.9)
>
>     When pt-table-checksum is executed both the servers print the DEBUG
information,
>      140327 15:38:48 [Note] WSREP: consistency check: REPLACE INTO
`percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary,
upper_boundary, this_cnt, this_crc)
>      SELECT /*!99997*/ 'sakila', 'staff', '1', NULL, NULL, NULL, COUNT(*)
AS cnt,
>      COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `staff_id`,
`first_name`, `last_name`, `address_id`, `picture`, `email`,
>      `store_id`, `active`, `username`, `password`, `last_update` + 0,
CONCAT(ISNULL(`picture`), ISNULL(`email`), ISNULL(`password`))))
>      AS UNSIGNED)), 10, 16)), 0) AS crc FROM `sakila`.`staff` /*checksum
table*/
>
>      But then the information is passed as ROW format as UPDATE:
>                 BINLOG '
>
SH40UxMjAAAATAAAAA2SAAAAACQAAAAAAAEAB3BlcmNvbmEACWNoZWNrc3VtcwAM/v4DBA/8/P4D
>                 /gMHDf7A/sAEWAICAv54/nh4Bg==
>
SH40UxgjAAAAeQAAAIaSAAAAACQAAAAAAAEADP////949gZzYWtpbGEFc3RhZmYBAAAACDIzMzY2
>
OGFlAgAAAEh+NFNw8AZzYWtpbGEFc3RhZmYBAAAANzMqPggyMzM2NjhhZQIAAAAIMjMzNjY4YWUC
>                 AAAASH40Uw==
>                 '/*!*/;
>                 ### UPDATE percona.checksums
>                 ### WHERE
>                 ###   @1='sakila' /* STRING(192) meta=65216 nullable=0
is_null=0 */
>                 ###   @2='staff' /* STRING(192) meta=65216 nullable=0
is_null=0 */
>                 ###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
>                 ###   @4=NULL /* INT meta=4 nullable=1 is_null=1 */
>                 ###   @5=NULL /* INT meta=600 nullable=1 is_null=1 */
>                 ###   @6=NULL /* INT meta=2 nullable=1 is_null=1 */
>                 ###   @7=NULL /* INT meta=2 nullable=1 is_null=1 */
>                 ###   @8='233668ae' /* STRING(120) meta=65144 nullable=0
is_null=0 */
>                 ###   @9=2 /* INT meta=0 nullable=0 is_null=0 */
>                 ###   @10=NULL /* INT meta=65144 nullable=1 is_null=1 */
>                 ###   @11=NULL /* INT meta=0 nullable=1 is_null=1 */
>                 ###   @12=1395949128 /* TIMESTAMP meta=0 nullable=0
is_null=0 */
>                 ### SET
>                 ###   @1='sakila' /* STRING(192) meta=65216 nullable=0
is_null=0 */
>                 ###   @2='staff' /* STRING(192) meta=65216 nullable=0
is_null=0 */
>                 ###   @3=1 /* INT meta=0 nullable=0 is_null=0 */
>                 ###   @4=0.166211             /* FLOAT meta=4 nullable=1
is_null=0 */
>                 ###   @5=NULL /* FLOAT meta=600 nullable=1 is_null=1 */
>                 ###   @6=NULL /* FLOAT meta=2 nullable=1 is_null=1 */
>                 ###   @7=NULL /* FLOAT meta=2 nullable=1 is_null=1 */
>                 ###   @8='233668ae' /* STRING(120) meta=65144 nullable=0
is_null=0 */
>                 ###   @9=2 /* INT meta=0 nullable=0 is_null=0 */
>                 ###   @10='233668ae' /* STRING(120) meta=65144 nullable=1
is_null=0 */
>                 ###   @11=2 /* INT meta=0 nullable=1 is_null=0 */
>                 ###   @12=1395949128 /* TIMESTAMP meta=0 nullable=0
is_null=0 */
>                 # at 37510
>
>           Given that there is no checksum execution on the SLAVE.
>
>
>     pt-table-checksum code related to the patch
>     #
########################################################################
>      # Checksum args and the DMS part of the checksum query for each
table.
>      #
########################################################################
>      my %crc_args     = $rc->get_crc_args(dbh => $master_dbh);
>      my $checksum_dml = "REPLACE INTO $repl_table "
>                       . "(db, tbl, chunk, chunk_index,"
>                       . " lower_boundary, upper_boundary, this_cnt,
this_crc) "
>                       . "SELECT"
>                       . ($cluster->is_cluster_node($master_cxn) ? '
/*!99997*/' : '')
>                       . " ?, ?, ?, ?, ?, ?,";
>      my $past_cols    = " COUNT(*), '0'";
>
>
>
>
>
>      HOW to replicate:
>      ================================
>      1) install a cluster using either PXC or MySQL/Galera  both(5.5)
>      2) connect another MySQL (simple) as slave to node1
>      3) load a test schema
>      4) remove some records on the SLAVE
>      5) run pt-table-checksum as /pt-table-checksum -u<user> -p<pw> -h
192.168.0.35 -P3306  --databases sakila,test --recursion-method
dsn=h=192.168.0.35,P=3306,u=<user>,p=<pw>,D=percona,t=dsns
--no-check-binlog-format
>         dsns table:
>           (root@localhost:pm) [percona]>select * from dsns;
>                 +----+-----------+---------------------------------------+
>                 | id | parent_id | dsn                                   |
>                 +----+-----------+---------------------------------------+
>                 |  6 |      NULL | h=192.168.0.35,P=5510,u=stress,p=tool |
>                 +----+-----------+---------------------------------------+
>                 1 row in set (0.00 sec)
>         6) review data from the pt-table-checksum and from the query on
master/slave
>                 SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS
chunks
>                 FROM percona.checksums
>                 WHERE (
>                  master_cnt <> this_cnt
>                  OR master_crc <> this_crc
>                  OR ISNULL(master_crc) <> ISNULL(this_crc))
>                 GROUP BY db, tbl;
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/codership-mysql/+bug/1298689/+subscriptions