Re: [codership-team] Incomplete replication (table contents missing) in 5-node MySQL-wsrep Galera cluster.

[Alex Yurchenko]

On 2012-10-08 18:41, Oleksandr Drach wrote:
> Dear Community!
>
> I have setup 5-node MySQL-Server-Wsrep+Galera cluster using rsync_wan
> SST
> method. And replications comes *only for databases and tables
> changes*, but
> not the *tables contents*.

Please post SHOW CREATE TABLE for one of the tables in question. Most
likely it is MyISAM.

> *
> *
> *My questions:*
> 1. How to fix it and get full replication including tables data?
> 2. How to properly set wsrep_notify_cmd parameter to log changes only
> to
> /var/log/syslog file without using of any external commands?
>
> *Software versions:*
> - OS: Ubuntu 12.04.01 LTS Server 64 bit
> - mysql-server-wsrep-5.5.23-23.6-amd64
> - galera-23.2.1
> - libaio1 0.3.109-2ubuntu1
> - psmisc 22.15-2ubuntu1.1
> - libssl0.9.8 0.9.8o-7ubuntu3.1
>
>
> *Apparmor* exclusion has been already applied for mysqld as
> recommended in
> Ubuntu.
> No *firewall* restrictions set at the moment.
>
> Nodes configurations:
> */etc/mysql/my.cnf*
> ...
> bind-address = 8.8.8.8 #external IP of node here, of
> course
> there's my addressess in real configs :)
> ...
>
> */etc/mysql/conf.d/wsrep.cnf*
>
> [mysqld]
> # (This must be substituted by wsrep_format)
> binlog_format=ROW
> # Currently only InnoDB storage engine is supported
> default-storage-engine=innodb
> # to avoid issues with 'bulk mode inserts' using autoinc
> innodb_autoinc_lock_mode=2
> # This is a must for paralell applying
> innodb_locks_unsafe_for_binlog=1
> # Query Cache is not supported with wsrep
> query_cache_size=0
> query_cache_type=0
> # Override bind-address
> # In some systems bind-address defaults to 127.0.0.1, and with
> mysqldump SST
> # it will have (most likely) disastrous consequences on donor node
> bind-address=8.8.8.8 # Node's IP there
> ##
> ## WSREP options
> ##
> # Full path to wsrep provider library or 'none'
> wsrep_provider=/usr/lib/galera/libgalera_smm.so
> # Provider specific configuration options
> #wsrep_provider_options=
> wsrep_provider_options="gmcast.listen_addr = tcp://8.8.8.8:4567;" #
> Node's
> IP there
> # Logical cluster name. Should be the same for all nodes.
> wsrep_cluster_name="cmi_wsrep_cluster"
>
> # Group communication system handle
> wsrep_cluster_address="gcomm://8.8.4.4"# 1st node's IP there
>
> # Human-readable node name (non-unique). Hostname by default.
> #wsrep_node_name=
>
> # Base replication <address|hostname>[:port] of the node.
> # The values supplied will be used as defaults for state transfer
> receiving,
> # listening ports and so on. Default: address of the first network
> interface.
> wsrep_node_address=8.8.8.8 # Node's IP there
>
> # Address for incoming client connections. Autodetect by default.
> wsrep_node_incoming_address=8.8.8.8 # Node's IP there
>
> # How many threads will process writesets from other nodes
> wsrep_slave_threads=3
>
> # DBUG options for wsrep provider
> #wsrep_dbug_option
>
> # Generate fake primary keys for non-PK tables (required for
> multi-master
> # and parallel applying operation)
> wsrep_certify_nonPK=1
>
> # Maximum number of rows in write set
> wsrep_max_ws_rows=131072
>
> # Maximum size of write set
> wsrep_max_ws_size=1073741824
>
> # to enable debug level logging, set this to 1
> wsrep_debug=0
>
> # convert locking sessions into transactions
> wsrep_convert_LOCK_to_trx=0
>
> # how many times to retry deadlocked autocommits
> wsrep_retry_autocommit=1
>
> # change auto_increment_increment and auto_increment_offset
> automatically
> wsrep_auto_increment_control=1
>
> # retry autoinc insert, which failed for duplicate key error
> wsrep_drupal_282555_workaround=0
>
> # enable "strictly synchronous" semantics for read operations
> wsrep_causal_reads=0
>
> # Command to call when node status or cluster membership changes.
> # Will be passed all or some of the following options:
> # --status - new status of this node
> # --uuid - UUID of the cluster
> # --primary - whether the component is primary or not ("yes"/"no")
> # --members - comma-separated list of members
> # --index - index of this node in the list
> wsrep_notify_cmd=yes
>
> ##
> ## WSREP State Transfer options
> ##
>
> # State Snapshot Transfer method
> wsrep_sst_method=rsync_wan
>
> # Address on THIS node to receive SST at. DON'T SET IT TO DONOR
> ADDRESS!!!
> # (SST method dependent. Defaults to the first IP of the first
> interface)
> wsrep_sst_receive_address=8.8.8.8 # Node's IP there
>
> # SST authentication string. This will be used to send SST to joining
> nodes.
> # Depends on SST method. For mysqldump method it is root:<root
> password>
> wsrep_sst_auth=wsrep_user:wsrep_password
>
> # Desired SST donor name.
> #wsrep_sst_donor=
>
> # Protocol version to use
> # wsrep_protocol_version=
>
> Thanks in advance for your help!
> If additional information is required please let me know!

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Oleksandr Drach]

Perfect, thanks a lot!

Silly me just forget to check tables type. Everything is working fine now (with InnoDB, as it should be) :)

Please advice also reply to my second question: 

How to properly set wsrep_notify_cmd parameter to log changes only to /var/log/syslog file without using of any external commands? 

вторник, 9 октября 2012 г., 12:36:39 UTC+3 пользователь Alexey Yurchenko написал:

[Alex Yurchenko]

On 2012-10-09 15:06, Oleksandr Drach wrote:
> Perfect, thanks a lot!
> Silly me just forget to check tables type. Everything is working fine
> now
> (with InnoDB, as it should be) :)
>
> Please advice also reply to my second question:
> How to properly set wsrep_notify_cmd parameter to log changes
> only to /var/log/syslog file without using of any external commands?

Unfortunately not much option here, this was intended for
script/command that would actually take some action - like alert load
balancer or something.

Regards,
Alex

[Henrik Ingo]

Basically: if you want to log something to syslog, you can, but you
need to write that script yourself. Galera will then call your script,
the rest is up to you. See:
http://www.codership.com/wiki/doku.php?id=notification_command

henrik


--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[Oleksandr Drach]

Actually for logging to syslog some random stuff will fit in  (as in my configuration: wsrep_notify_cmd=yes).

But I asked is there a "proper" and "clean" way to it (unless my method is quick and dirty).

вторник, 9 октября 2012 г., 15:57:58 UTC+3 пользователь Henrik Ingo написал:

[Henrik Ingo]

Well, it is also possible to log all of MySQL error log to syslog.
But Galera is quite verbose, so that's maybe not what you want. That
would then exactly be the point with providing your own notify script,
you could decide yourself what the significant events are.

henrik

> --

[Alex Yurchenko]

On 2012-10-09 16:07, Henrik Ingo wrote:
> Well, it is also possible to log all of MySQL error log to syslog.
> But Galera is quite verbose, so that's maybe not what you want. That
> would then exactly be the point with providing your own notify
> script,
> you could decide yourself what the significant events are.
>
> henrik
>

The thing is that notification command is passed information which is
not logged anywhere (maybe it should). So it is not like you're
selectively redirecting Galera log to syslog. It is a totally different
information.

Yet, this event is not supposed to happen often (if it does, you have
far more serious problems on your plate), so the overhead of calling a
custom script should not be an issue.

Alex

[seppo....@codership.com]

Somewhat strange. Do you happen to use options like binlog-do-db,
binlog-ignore-db, replicate-do-db or replicate-ignore-db? Galera
honors MySQL replication filtering, and with these you can block out
some schemas.

Also, if your session happens to set wsrep_on=0, for some reason, it
would disable replication for this session.

just my 2 cents, seppo


Quoting Amol Kedar <ajk...@gmail.com>:

> hello,
> I have exactly same issue and my scenarios is same if i create a new schema
> or table it gets replicated but when i run any inserts it won't replicate
>
> i thought this thread is relevant and hence i am adding my information here
>
> i have a 3 node cluster on ubuntu 10.04 LTS, the cluster replicates about
> 15 schemas and now if i create any new schema it does not replicate the
> contents of new structres in that schema
>
> a point to mention is that old schema's however are replicating fine across
> all 3 nodes
>
>
>
> mysql> SHOW CREATE TABLE abc;
> +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table | Create Table
>
> |
> +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | abc | CREATE TABLE `abc` (
> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
> `name` varchar(255) DEFAULT NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
> +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
> *node 1*
> mysql> select * from abc;
> +----+------+
> | id | name |
> +----+------+
> | 3 | anan |
> +----+------+
>
> *node 2*
> mysql> select * from abc;
> Empty set (0.03 sec)
>
> *node 3*
> mysql> select * from abc;
> Empty set (0.03 sec)
>
>
> $ sudo dpkg --list | grep percona
> ii percona-toolkit 2.1.3
> Advanced MySQL and system command-line tools
> ii percona-xtrabackup 2.0.2-461.lucid Open
> source backup tool for InnoDB and XtraD
> ii percona-xtradb-cluster-client-5.5 5.5.24-23.6-341.lucid
> Percona Server database client binaries
> ii percona-xtradb-cluster-common-5.5 5.5.24-23.6-341.lucid
> Percona Server database common files (e.g. /
> ii percona-xtradb-cluster-galera-2.x 112.lucid
> Galera components of Percona XtraDB Cluster
> ii percona-xtradb-cluster-server-5.5 5.5.24-23.6-341.lucid
> Percona Server database server binaries
>
>
> i haven't done any *Apparmor *edits as mentioned above, but my cluster has
> been working fine so far and just today it is creating these issues, is
> there anything else that i can check?

[Amol Kedar]

You were right on...yes i had the binlog-do-db and i had physically setup some db's to replicate and always overlooked those parameters in my.cnf while debugging

Thanks for your help...

Another related question, basically because of this problem we actually figured that the replication was not working for this particular db

so we have a table to which the application writes and it has ID as autoincrement,but somehow on node 1 the id got reset to 2 from 20 

would their be any scenarios on which the auto increment will be reset?

[Amol Kedar]

ok on debugging i found that if the table has an auto increment of 10 and if we delete all the 10 rows from the table by using the delete command (not truncate)

and once that is done if we restart the mysql database the auto increment id gets reset to 1?

is that bug? or am i not setting something correctly?

[Henrik Ingo]

This should be normal InnoDB behavior if I remember correctly. In any
case, it is totally safe behavior, not a bug.

henrik

[Amol Kedar]

oh ok i verified this on mysql 5.1.47 and yes you are correct it is a normal behavior of innodb

ok since my application does have a parent table and a child table and we just delete rows from the parent table and not from the children, having a auto increment column reset is a big issue

is their a setting i can try so the db does not reset the auto increment counter for a particular table?

[Henrik Ingo]

On Fri, Oct 19, 2012 at 11:15 PM, Amol Kedar <ajk...@gmail.com> wrote:
> oh ok i verified this on mysql 5.1.47 and yes you are correct it is a normal
> behavior of innodb
>
> ok since my application does have a parent table and a child table and we
> just delete rows from the parent table and not from the children, having a
> auto increment column reset is a big issue
>
> is their a setting i can try so the db does not reset the auto increment
> counter for a particular table?

Indeed there is:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_insert_id

Still, you might want to consider if your design is solid. Usually you
would also delete child records of a foreign key relationship, if the
parent is deleted. (Or move them to an archive table.)

henrik

[Amol Kedar]

thanks for the link, but sorry i am still confused what i need to set in the my.cnf file for the insert_id?

this link seems to be mentioning about how to use the insert_id in insert and alter statements?

if you elaborate that would be great help

and yes we are changing our system design to not leave orphan records in the system

[Henrik Ingo]

I've never used it, but seems to be just:

SET INSERT_ID=5535357;
INSERT INTO ... ;

I'm pretty sure you only need to use it once, after that the next
insert will again increment from the previous one.

henrik

> --