Not the answer you need?
Register and ask your own question!

Too many GTID when executing show slave status On Mysq 5.6

binhminh07binhminh07 EntrantCurrent User Role Beginner
Hi ,

I have a problem with mysql 5.6 when GITD mode is ON.




Would you please tell me how to solve problem ?

Thanks so much.

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    It seems, there are gaps in the executed transactions. Have you ignored transactions or skipped them? In that case, mysql instead of showing a range, shows hundred of ranges. I think, the only way to clean that mess is, to recreate the slave OR reset slave.
  • binhminh07binhminh07 Entrant Current User Role Beginner
    hi niljoshi

    thanks for your reply .

    How to skip it ? if skip it , replication can be executed with new master??
    I mean with new master we don't know which GTID will be excuted next
  • just1fixjust1fix Entrant Current User Role Beginner
    Like niljoshi said it looks like you're using --replicate-do-db and/or --replicate-ignore-db settings on the slave. In this case not all of the master transactions are executed on the slave, that's why there're 'holes' in executed_set and its length is constantly growing.

    If you do indeed ignore some databases in replication then consider the possibility to not log them in the first place on the master (if you don't need them). I.e. use --binlog-do-db and/or --binlog-ignore-db on the master instead. Then all of the transactions logged on master will be executed on slave.
    There'll be no holes in the range (at least that's what I think will happen, I haven't tested it, but it seems logical to increase the GTID counter for those transactions only that are logged).
  • binhminh07binhminh07 Entrant Current User Role Beginner
    thanks just1fix

    I didn't use --replicate-do-db and/or --replicate-ignore-db at my.cnf.


    Maybe that problem happen as the below context :

    When we have a SELECT command executed.
    The SESSION TEMPORARY TABLE in PROCEDURE was established.
    The Temporary often created with engine MEMORY, if many connections had been establishing , Memory will over and engine auto be switch to MYISAM.

    But MyISAM with GTID has a bug like here http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html


    LIKE this,and in many connetctions establishing.

    (MYISAM engine + GTID problem is here?)
    CREATE PROCEDURE xxxx()
    BEGIN
    -- it waste??
    CREATE TEMPORARY TABLE zzzz(
    tmpid BIGINT NOT NULL AUTO_INCREMNET,
    sum_counter BIGINT DEFAULT 0,
    PRIMARY KEY(tmpid)
    )ENGINE = MEMORY;
    -- auto extend MYISAM at over memory table limit
    INSERT INTO zzzz(sum_counter) SELECT SUM(id) FROM users WHERE status=1;

    -- result to record set
    SELECT * FROM zzzz;
    END
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.