You are here

Feed aggregator

MyIsam tables

Lastest Forum Posts - February 14, 2015 - 11:35am
I'm new to XtraBackup. Just tried my first backup. I'm trying to understand the results. The database I backed up was on a remote host and all the tables in the db are in MyIsam format. Yet all I see is the Innodb files in the backup results directory. Can someone explain how this works and confirm I got a valid result? I'd like to use this utility but the results aren't really transparent to me (at least not yet).

Percona XtraDB Cluster 5.6: a tale of 2 GTIDs

Latest MySQL Performance Blog posts - February 13, 2015 - 6:27am

Say you have a cluster with 3 nodes using Percona XtraDB Cluster (PXC) 5.6 and one asynchronous replica connected to node1. If asynchronous replication is using GTIDs, moving the replica so that it is connected to node2 is trivial, right? Actually replication can easily break for reasons that may not be obvious at first sight.


Let’s assume we have the following setup with 3 PXC nodes and one asynchronous replica:

Regarding MySQL GTIDs, a Galera cluster behaves like a distributed master: transactions coming from any node will use the same auto-generated uuid. This auto-generated uuid is related to the Galera uuid, it’s neither ABC, nor DEF, nor GHI.

Transactions executed for instance on node1 but not replicated to all nodes with Galera replication will generate a GTID using the uuid of the node (ABC). This can happen for writes on MyISAM tables if wsrep_replicate_myisam is not enabled.

Such local transactions bring the same potential issues as errant transactions do for a regular master-slave setup using GTID-based replication: if node3 has a local transaction, when you connect replica1 to it, replication may break instantly.

So do not assume that moving replica1 from node2 to node3 is a safe operation if you don’t check errant transactions on node3 first.

And if you find errant transactions that you don’t want to get replicated to replica1, there is only one good fix: insert a corresponding empty transaction on replica1.


Both kinds of GTIDs are using the same format: <source_id:trx_number>.

For Galera, <source_id> is generated when the cluster is bootstrapped. This <source_id> is shared by all nodes.

For MySQL, <source_id> is the server uuid. So it is easy to identify from which server a transaction originates.

Knowing the Galera GTID of a transaction will give you no clue about the corresponding MySQL GTID of the same transaction, and vice versa. You should simply consider them as separate identifiers.

MySQL GTID generation when writing to the cluster

What can be surprising is that writing to node1 will generate a MySQL GTID where <source_id> is not the server uuid:

node1> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 03c236a0-f860-11e3-9b80-9cebe8067a3f | +--------------------------------------+ node1> select @@global.gtid_executed; +------------------------------------------+ | @@global.gtid_executed | +------------------------------------------+ | b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:1 | +------------------------------------------+

Even more surprising is that if you write to node2, you will see a single GTID set as if both transactions had been executed on the same server:

node2> select @@global.gtid_executed; +------------------------------------------+ | @@global.gtid_executed | +------------------------------------------+ | b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:2 | +------------------------------------------+

Actually this is reasonable: the cluster acts as a distributed master regarding MySQL replication, so it makes sense that all nodes share the same <source_id>.

And by the way, if you are puzzled about how this ‘anonymous’ <source_id> is generated, look at this:

mysql> show global status like 'wsrep_local_state_uuid'; +------------------------+--------------------------------------+ | Variable_name | Value | +------------------------+--------------------------------------+ | wsrep_local_state_uuid | 4e0c0cc5-f876-11e3-bc0c-07c8c1ed0e15 | +------------------------+--------------------------------------+ node1> select @@global.gtid_executed; +------------------------------------------+ | @@global.gtid_executed | +------------------------------------------+ | b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:1 | +------------------------------------------+

If you ‘sum’ both <source_id>, you will get ffffffff-ffff-ffff-ffff-ffffffffffff.

How can local transactions show up?

Now the question is: given that any transaction executed on any node of the cluster is replicated to all nodes, how can a local transaction (a transaction only found on one node) appear?

The most common reason is probably a write on a MyISAM table if wsrep_replicate_myisam is not enabled, simply because writes on MyISAM tables are not replicated by Galera by default:

# Node1 mysql> insert into myisam_table (id) values (1); mysql> select @@global.gtid_executed; +----------------------------------------------------------------------------------+ | @@global.gtid_executed | +----------------------------------------------------------------------------------+ | 03c236a0-f860-11e3-9b80-9cebe8067a3f:1, b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:1-8 | +----------------------------------------------------------------------------------+ # Node2 mysql> select @@global.gtid_executed; +------------------------------------------+ | @@global.gtid_executed | +------------------------------------------+ | b1f3f33a-0789-ee1c-43f3-f8373e12f1ea:1-8 | +------------------------------------------+

As you can see the GTID of the local transaction on node1 uses the uuid of node1, which makes it easy to spot.

Are there other statements that can create a local transaction? Actually we found that this is also true for FLUSH PRIVILEGES, which is tracked in the binlogs but not replicated by Galera. See the bug report.

As you probably know, these local transactions can hurt when you connect an async replica on a node with a local transaction: the replication protocol used by GTID-based replication will make sure that the local transaction will be fetched from the binlogs and executed on the async slave. But of course, if the transaction is no longer in the binlogs, that triggers a replication error!


I will repeat here what I always say about MySQL GTIDs: it is a great feature but replication works a bit differently from regular position-based replication. So make sure you understand the main differences otherwise it is quite easy to be confused.

The post Percona XtraDB Cluster 5.6: a tale of 2 GTIDs appeared first on MySQL Performance Blog.

mysql.innodb_index_stats &amp;quot;Duplication Entry&amp;quot; error on restore

Lastest Forum Posts - February 13, 2015 - 4:49am
Hej there,

since MySQL 5.6 there are at least two new tables located in the mysql database:

- innodb_index_stats
- innodb_table_stats

Now my problem. I tried to recover from a full database dump, including the mysql database. All databases were created and rows inserted successfully, except the mysql database.

At this point i always get:

ERROR 1062 (23000) at line 9476752: Duplicate entry 'XXXXXXXXXXXX-PRIMARY-n_diff_pfx01' for key

I think i know why this is happening. And I fixed this issue by editing the dump file and changed “insert into” to “replace into”. But I don’t know how to act right to avoid further problems like this.

I’d think the following way is a good solution. What do you think? Is there an alternative?

on dump ignore `mysql.innodb_index_stats' and `mysql.innodb_table_stats'
and an replication also ignore these tables

What is best practice for this matter?


How parallelize the SST network transfer

Lastest Forum Posts - February 13, 2015 - 2:25am
I know the xtrabackup option to parallelize the xtrabackup dump, but I would also like to parallelize the network transfer during the SST from the donor to the joiner.

The reason is that the joiner is an AWS instance that has a bandwidth capping of 8Mbit/s per process (the donor is not an AWS instance). A known workaround is to spread the replication between multiple processes, but I can't find a way to achieve this with XtraDB Cluster.

Any ideas how to solve this problem ?

How to trigger a partial SST ?

Lastest Forum Posts - February 13, 2015 - 2:17am
Hi there,

I have a use case for XtraDB Cluster that involves local tables that should not be copied from one node to another. The reason is that the law of the country where the donor resides does not allow user-related data to leave the country.

The solution I imagined is:
- set up the user-related tables with MyISAM engine
- include only non user-related tables in the SST using the "[xtrabackup] tables=" parameter

The transfer part works just fine, but it fails afterwards during the consistency check:
[ERROR] WSREP: Node consistency compromized, aborting...

I assume that it happens because some tables are missing. Knowing that these would not be replicated anyway (because they are MyISAM tables), what could I do to avoid the consistency check to fail ?

An idea would be to create them (empty) before startting the SST, but with my current config the whole mysql data directory gets cleaned up before each SST. Can I disable this cleanup feature ? Will it have some side effects on my SST or on the node consistency ?

Thanks for your help

innobackupex crashing Percona server 5.6.21-70.1

Lastest Forum Posts - February 12, 2015 - 3:15pm
Running a backup on a node of a cluster using innobackupex v2.2.8. The backup fails with the following:
: 150212 15:49:05 innobackupex: Finished backing up non-InnoDB tables and files 150212 15:49:05 innobackupex: Executing LOCK BINLOG FOR BACKUP... DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction at /usr/bin/innobackupex line 3036. innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3039 main::mysql_query('HASH(0x1adbda0)', 'LOCK BINLOG FOR BACKUP') called at /usr/bin/innobackupex line 3501 main::mysql_lock_binlog('HASH(0x1adbda0)') called at /usr/bin/innobackupex line 2000 main::backup() called at /usr/bin/innobackupex line 1592 innobackupex: Error: Error executing 'LOCK BINLOG FOR BACKUP': DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction at /usr/bin/innobackupex line 3036. 150212 15:49:05 innobackupex: Waiting for ibbackup (pid=29318) to finish DB Backup ending at Thu 12 Feb 2015 03:49:05 PM MST At the same time, the mysqld process logs and exits:
: 22:49:05 UTC - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Please help us make Percona XtraDB Cluster better by reporting any bugs at https://bugs.launchpad.net/percona-xtradb-cluster key_buffer_size=25165824 read_buffer_size=131072 max_used_connections=2 max_threads=202 thread_count=3 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 105204 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x16598640 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f28bc0e5d38 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8f97d5] /usr/sbin/mysqld(handle_fatal_signal+0x4b4)[0x6655c4] /lib64/libpthread.so.0[0x396ec0f710] [0x7f25200000a8] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 14411 Status: KILL_CONNECTION You may download the Percona XtraDB Cluster operations manual by visiting http://www.percona.com/software/percona-xtradb-cluster/. You may find information in the manual which will help you identify the cause of the crash. Doesn't happen consistently.

I would think that the backup should just fail, but it shouldn't crash the mysql server in the process. Alternately, the mysql process crashed and that caused the backup to fail, but then I would think that innobackex shouldn't be causing the server to crash.

Any ideas on causality here?

Help with auth_pam plugin

Lastest Forum Posts - February 12, 2015 - 12:22am

i need some help to resolve one problem with Percona's auth_pam plugin.

I have a CentOS with mysql server 5.6 (i have also test 5.5).
I have downloaded, compiled and installed auth_pam. All of these steps ok. I have configure : /etc/pam.d/mysqld with:

: auth required pam_sss.so account required pam_sss.so After this, i hava created one user as specified at the documentation:

: CREATE USER 'miguel.tubia'@'localhost' IDENTIFIED WITH auth_pam; Of course, the password field is empty. I want to auth using sssd, which is configured to query our LDAP server.

On one Debian box, it has worked perfect.

Now, on this CentOS box, it doesn't work. When I'm going to login with the new user created and my ldap password, I get a login failed error.
Anyway, if i login with an empty password, I can login ok. I think that mysql is not using the pam plugin...

I don't know how to debug it. I don't get any error, i don't see any log with useful information.
Any help is welcome...

Best regards,

Apache monitoring problem $version = '1.1.4'

Lastest Forum Posts - February 11, 2015 - 10:00am

I am having same issue. I am using $version = '1.1.4'; and its not creating any graph.

After manually using the command:

wget -U Cacti/1.0 -q -O - -T 5 ""
Total Accesses: 19023
Total kBytes: 652953
CPULoad: .71728
Uptime: 86637
ReqPerSec: .219571
BytesPerSec: 7717.53
BytesPerReq: 35148.2
BusyWorkers: 2
IdleWorkers: 10
Scoreboard: ________WR__...................................... .................................................. .................................................. .................................................. .................................................. ......

when I run the PHP script:

ssh -q -o "ConnectTimeout 10" -o "StrictHostKeyChecking no" cacti@ -p 22 -i /etc/cacti/id_rsa wget -U Cacti/1.0 -q -O - -T 5 ""
Total Accesses: 19172
Total kBytes: 658535
CPULoad: .71785
Uptime: 86890
ReqPerSec: .220647
BytesPerSec: 7760.85
BytesPerReq: 35173.2
BusyWorkers: 1
IdleWorkers: 11
Scoreboard: __W_________...................................... .................................................. .................................................. .................................................. .................................................. ......

but still I do not see the graph...

Can some one help me here.. .where I am doing wrong!

Appreciated your help!


Percona xtrabackup knowledgebase

Lastest Forum Posts - February 11, 2015 - 4:01am
Hi All

As i want to acquire some knowledge about percona xtrabackup as i have already read about before and i want to know about xtrabackup performance when compared with mysqldump. Is this right where we can have some differences b/w these two. can you tell some tips and any references for implementing xtrabackup in my opensource project.

Percona xtrabackup knowledgebase

Lastest Forum Posts - February 11, 2015 - 4:01am
Hi All,

As i want to acquire some knowledge about percona xtrabackup as i have already read about before and i want to know about xtrabackup performance when compared with mysqldump. Is this right where we can have some differences b/w these two. can you tell some tips and any references for implementing xtrabackup in my opensource project.

Online GTID rollout now available in Percona Server 5.6

Latest MySQL Performance Blog posts - February 10, 2015 - 12:00am

Global Transaction IDs (GTIDs) are one of my favorite features of MySQL 5.6. The main limitation is that you must stop all the servers at the same time to allow GTID-replication. Not everyone can afford to take a downtime so this requirement has been a showstopper for many people. Starting with Percona Server 5.6.22-72.0 enabling GTID replication can be done without almost no downtime. Let’s see how to do it.

Implementation of the Facebook patch

Finding a solution to migrate to GTIDs with no downtime is not a new idea, and several companies have already developed their own patch. The 2 best known implementations are the one from Facebook and the one from Booking.com.

Both options have pros and cons, and we finally chose to port the Facebook patch and add a new setting (gtid_deployment_step).

Performing the migration

Let’s assume we have a master-slaves setup with 4 servers A, B, C and D. A is the master:

The 1st step is to take each slave out of rotation, one at a time, and set gtid_mode = ON and gtid_deployment_step = ON (and also log_bin, log_slave_updates and enforce_gtid_consistency).

gtid_deployment_step = ON means that a server will not generate GTIDs when it executes writes, but it will record a GTID in its binary log if it gets an event from the replication stream tagged with a GTID.

The 2nd step is to promote one of the slaves to become the new master (for instance C) and to disable gtid_deployment_step. It is a regular slave promotion so you should do it the same way you deal with planned slave promotions (for instance using MHA or your own scripts). Our patch doesn’t help you do this promotion.

At this point replication will break on the old master as it has gtid_mode = OFF and gtid_deployment_step = OFF.

Don’t forget that you need to use CHANGE MASTER TO MASTER_AUTO_POSITION = 1 to enable GTID-based replication.

The 3rd step is to restart the old master to set gtid_mode = ON. Replication will resume automatically, but don’t forget to set MASTER_AUTO_POSITION = 1.

The final step is to disable gtid_deployment_step on all slaves. This can be done dynamically:

mysql> SET GLOBAL gtid_deployment_step = OFF;

and you should remove the setting from the my.cnf file so that it is not set again when the server is restarted.

Optionally, you can promote the old master back to its original role.

That’s it, GTID replication is now available without having restarted all servers at the same time!


At some point during the migration, a slave promotion is needed. And at this point, you are still using position-based replication. The patch will not help you with this promotion so use your regular failover scripts. If you have no scripts to deal with that kind of situation, make sure you know how to proceed.

Also be aware that this patch provides a way to migrate to GTIDs with no downtime, but not a way to migrate away from GTIDs with no downtime. So test carefully and make sure you understand all the new stuff that comes with GTIDs, like the new replication protocol, or how to skip transactions.

Other topologies

If you are using master-master replication or multiple tier replication, you can follow the same steps. With multiple tier replication, simply start by setting gtid_mode = ON and gtid_deployment_step = ON for the leaves first.


If you’re interested by the benefits of GTID replication but if taking a downtime has always scared you, you should definitely download the latest Percona Server 5.6 and give it a try!

The post Online GTID rollout now available in Percona Server 5.6 appeared first on MySQL Performance Blog.

Cluster does not start

Lastest Forum Posts - February 8, 2015 - 5:44pm
I have Percona-XtraDB-Cluster-server 5.6.21 installed on 3 nodes. The cluster used to work on 2 nodes, but I cannot bring it back up.

I have bootstrapped the cluster on node1. But node2 fails:

WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20150209 02:15:30.956)
grep: /var/lib/mysql//xtrabackup_checkpoints: No such file or directory
WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql/ (20150209 02:15:41.121)
WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20150209 02:15:41.122)
2015-02-09 02:15:41 2672050 [Warning] WSREP: 1.0 (node1.com): State transfer to 0.0 (node2.com) failed: -22 (Invalid argument)
2015-02-09 02:15:41 2672050 [ERROR] WSREP: gcs/src/gcs_group.cpp:int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():722: Will never receive state. Need to abort.
2015-02-09 02:15:41 2672050 [Note] WSREP: gcomm: terminating thread
2015-02-09 02:15:41 2672050 [Note] WSREP: gcomm: joining thread
2015-02-09 02:15:41 2672050 [Note] WSREP: gcomm: closing backend
WSREP_SST: [ERROR] Cleanup after exit with status:1 (20150209 02:15:41.489)
2015-02-09 02:15:41 2672050 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'node2.com' --auth userass' --datadir '/var/lib/mysql/' --defaults-file '/etc/my.cnf' --parent '2672050' '' : 1 (Operation not permitted)
2015-02-09 02:15:41 2672050 [ERROR] WSREP: Failed to read uuid:seqno from joiner script.
2015-02-09 02:15:41 2672050 [ERROR] WSREP: SST failed: 1 (Operation not permitted)
2015-02-09 02:15:41 2672050 [ERROR] Aborting

I don't know how to debug this issue. Any idea?

Reading state information hangs when installing percona-xtradb-cluster-56

Lastest Forum Posts - February 7, 2015 - 8:44am
Dear Colleagues

I am experiencing a very strange issue when trying to install the Percona XtraDB Cluster.

When I run: apt-get update && apt-get install percona-xtradb-cluster-56 percona-xtradb-cluster-server-5.6

The updating proceeds as expected and then when the next section starts, the system just hangs. I see no progress what-so-ever.


It never goes further than that. It's been at this position for the past 30 minutes.

At first I thought it may be synaptic, so I installed apache2 and php5 and everything worked as expected. It only hangs when I try installing percona-xtradb-cluster-56 percona-xtradb-cluster-server-5.6

I'll appreciate any assistance as I'm utterly stumped.

Thank you

Nagios not listing all databases in the respective regions - pmp-check-aws-rds.py

Lastest Forum Posts - February 6, 2015 - 1:37pm
Hello everyone!

I have installed percona plugins, put the credentials on /etc/boto.cfg and /etc/nagios3/.boto, but the script only show the database at US Virginia, how can I display my databases at "São Paulo" zone?

I am new to administrate percona scripts, I appreciate any help.



Percona Server 5.6.22-72.0 is now available

Latest MySQL Performance Blog posts - February 6, 2015 - 9:04am

Percona is glad to announce the release of Percona Server 5.6.22-72.0 on February 6, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories.

Based on MySQL 5.6.22, including all the bug fixes in it, Percona Server 5.6.22-72.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source and free – and this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release can be found in the 5.6.22-72.0 milestone on Launchpad.

New Features:

  • Percona Server is now able to log the query’s response times into separate READ and WRITE INFORMATION_SCHEMA tables. Two new INFORMATION_SCHEMA tables QUERY_RESPONSE_TIME_READ and QUERY_RESPONSE_TIME_WRITE have been implemented for READ and WRITE queries correspondingly.
  • Percona Server now supports Online GTID deployment. This enables GTID to be deployed on existing replication setups without making the master read-only and stopping all the slaves. This feature was ported from the Facebook branch.
  • New ps_tokudb_admin script has been implemented to make the TokuDB storage engine installation easier.

Bugs Fixed:

  • SET STATEMENT ... FOR would crash the server if it could not execute the due to: 1) if the was Read-Write in a Read-Only transaction (bug #1387951), 2) if the needed to re-open an already open temporary table and would fail to do so (bug #1412423), 3) if the needed to commit implicitly the ongoing transaction and the implicit commit would fail (bug #1418049).
  • TokuDB storage engine would fail to load after the upgrade on CentOS 5 and 6. Bug fixed #1413956.
  • Fixed a potential low-probability crash in XtraDB linear read-ahead code. Bug fixed #1417953.
  • Setting the max_statement_time per query had no effect. Bug fixed #1376934.

Other bugs fixed: #1407941, and #1415843 (upstream #75642)

Release notes for Percona Server 5.6.22-72.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker

The post Percona Server 5.6.22-72.0 is now available appeared first on MySQL Performance Blog.

2 questions about innodb_buffer_pool_size parameter on RDS

Lastest Forum Posts - February 6, 2015 - 8:36am
Hi All !
I am using mysql 5.5.40 , running on amazon RDS. (instance - m1.large)
I ran the following query:
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A that suppose to give me the recommended value for this parameter , and I got 85 GB.
The current size is 5.3GB .({DBInstanceClassMemory*3/4}).
I have 2 questions ,
1. This query is reliable ? should I upgrade the instance ?
2. If I understand , in this situation , most of the queries using IO to the disk . There is any way to check if query wrote to the disk?

Thank you!!

pt-table-checksums with interrupted connection

Lastest Forum Posts - February 6, 2015 - 12:46am
My scenario:
Master-Server with a database named "management" and a table "teamdriveinvitation"
And there is a slave-server with row-based replication enabled. All logins and passwords are the same.

I find that the update of the checksum-table is NOT done with the logged-in session of the percona-tool, but the newly generated checksums are transported via the replication-statements.
Is this intended?

I logged this on the slave with the mysql.log:
root@EUHQTMSQL001-1A:/usr/local/scripts# tail -n1 -f mysql.log |grep -i teamdriveinvitation
17137 Query SHOW TABLES FROM `management` LIKE 'teamdriveinvitation'
17137 Query SHOW CREATE TABLE `management`.`teamdriveinvitation`
17137 Query EXPLAIN SELECT * FROM `management`.`teamdriveinvitation` WHERE 1=1
3 Query REPLACE INTO `percona`.`euirtmsql001_1a` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'management', 'teamdriveinvitation', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS(' #', `id`, `teamdriveid`, `invitedby`, `invited`, `message`, `dateinvited`, `role`, CONCAT(ISNULL(`invitedby`), ISNULL(`message`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `management`.`teamdriveinvitation` /*checksum table*/
3 Query UPDATE `percona`.`euirtmsql001_1a` SET chunk_time = '0.001044', master_crc = '91204457', master_cnt = '32' WHERE db = 'management' AND tbl = 'teamdriveinvitation' AND chunk = '1'
17137 Query SELECT MAX(chunk) FROM `percona`.`euirtmsql001_1a` WHERE db='management' AND tbl='teamdriveinvitation' AND master_crc IS NOT NULL
17137 Query SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `percona`.`euirtmsql001_1a` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='management' AND tbl='teamdriveinvitation')

As you can see, there is a connection "17137" which was recently opened by the percona-tool. Then suddenly the update of the checksum-table is done from the connection "3", which is the normal replication-stream. Then it switches back to connection "17137" to check for any differences.

mariadb, galera cluster, haproxy, keepalived

Lastest Forum Posts - February 4, 2015 - 7:37am
hello guys

nice being part of you.

recently I've setup a galera cluster with haproxy/keepalived as follow

*3 nodes setup
Node1 act as writing node
node2,3 are for reading. ( in case of failure of node1 , node2 will be the writing node )

* i have haproxy-keepalived on all 3 machines.
* i have a VIP pointing at node1 but the reading sequence configured on haproxy is node2,node3,node1.

my problem is when i was testing the failover scenario.
I shutdown node1 and everything moved to node2/3 ( node2 took the place of writing node )

after getting back node1 the mysql was not able to start as was not able to sync from the other 2 nodes.

my configuration on 3 nodes is as following

binlog_format = ROW
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name = "cluster"
wsrep_cluster_address = gcomm://
wsrep_node_address =
wsrep_node_name = 'MariaDBNode1'
wsrep_node_incoming_address =
wsrep_sst_method = rsync
wsrep_sst_donor =,

binlog_format = ROW
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name = "cluster"
wsrep_cluster_address = gcomm://,
wsrep_node_address =
wsrep_node_name = 'MariaDBNode2'
wsrep_node_incoming_address =
wsrep_sst_method = rsync


binlog_format = ROW
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name = "cluster"
wsrep_cluster_address = gcomm://,
wsrep_node_address =
wsrep_node_name = 'MariaDBNode3'
wsrep_node_incoming_address =
wsrep_sst_method = rsync

please note that before my final testing i had
wsrep_cluster_address = gcomm://,,
on all nodes.

i changed it now as shown on the above configuration

i would appreciate if you could help me.

Does percona xtradb supporting partitioning with foreign keys?

Lastest Forum Posts - February 4, 2015 - 2:04am
Does percona xtradb support partitioning with foreign keys?

The future of MySQL quality assurance: Introducing pquery

Latest MySQL Performance Blog posts - February 4, 2015 - 12:00am

Being a QA Engineer, how would you feel if you had access to a framework which can generate 80+ crashes – a mix of hitting developer introduced assertions (situations that should not happen), and serious unforeseen binary crashes – for the world’s most popular open source database software – each and ever hour? What if you could do this running on a medium spec machine – even a laptop?

The seniors amongst you may object “But… generating a crash or assertion is one thing – creating a repeatable testcase for the same is quite another.”

Introducing pquery, mtr_to_sql, reducer.sh (the pquery-enabled version), and more:

80+ coredumps per hour. Fully automatic testcase creation. Near-100% testcase reproducibility. C++ core. 15 Seconds run time per trial. Up to 20-25k lines of SQL executed per trial. CLI testcases. Compatible with sporadic issues. High-end automation of many aspects.

It all started when we realized how slow RQG really is. The Random Query Generator by Philip Stoev is a fantastic tool, and it has been greatly expanded over the years, but though it is a Swiss army knife in what you can do with it, when it comes to speed it is not the fastest. The Perl backend – combined with much random-ness – has made the system slow. At least when compared with the ultra-fast execution of C++ code.

I discussed things with Alexey Bychko, and before long pquery was born. The C++ core code of pquery is Alexey’s creation. It easily executes 20k random lines of SQL in 15-20 seconds, with full logging (including errors) turned on. Though the tool looks fairly simple when reviewing the code, it is incredibly powerful.

Now, one thing which people being introduced to QA for MySQL (or any other large software with many features/functions/options etc.) have to grasp is “random spread testing”. If your random spread (and the amount of samples) is large enough (read: ‘sufficient’), it is relatively easy to get a good “overall quality” estimate of your software by doing a few runs (taking usually around 1-3 days – or longer if various options are being tested).

So,we now had speed (pquery) and near-perfect testcase simplification/reduction & reproducibility (the new pquery adaption of reducer.sh) – but we needed one thing more: SQL which would cover… well… every area of mysqld. A fast framework without matching grammars is not worth much…

Converting the grammars from RQG seemed like a mammoth task – and we would really just be re-writing RQG in C. And creating a new SQL generator was an almost hopeless venture (I gave it a short try) – given the huge variety and complexity when constructing SQL statements.

I took the cheeky road. And it paid off. mtr_to_sql.sh was born.

The MTR testcases included (and delivered) with the MySQL server download contain individual tests for nearly every possible SQL syntax possible, including ones that – ought to – error out (but not crash). Not only that, there are specific MTR testcases for each feature, not to mention the many MTR testcases that were added to cover bug regression testing. Where there is smoke…

[roel@localhost 5.6]$ pwd /bzr/5.6 [roel@localhost 5.6]$ find . | grep ".test$" | wc -l 3867 [roel@localhost 5.6]$ cat ~/percona-qa/mtr_to_sql.sh | grep BZR_PATH | head -n1 BZR_PATH="/bzr/5.6/mysql-test" [roel@localhost 5.6]$ time ~/percona-qa/mtr_to_sql.sh Done! Generated /tmp/mtr_to_sql.yy for RQG, and /tmp/mtr_to_sql.sql for pquery (SQL is indentical in both, but first is formatted for use with RQG) real 0m20.150s user 1m2.951s sys 0m1.214s [roel@localhost 5.6]$ cat /tmp/mtr_to_sql.sql | wc -l 107541

107.5K of high-quality SQL. Covering every possible functionality and buggy area out there. Free.

Let the testing begin!

I was quite dumbfounded when (after further tuning and scripting) we started seeing 50+, then 80+ cores per hour. Sig11’s (crashes), Sig6’s (asserts), server hangs, character set issues, error log errors and so. Many crashes and issues in optimized code. Fun.

Our best weeks yet?

Pquery update: 220+ bugs logged, of which 140+ in MySQL, 60+ in Percona, 10+ in TokuTek (with limited TokuTek testing) #mysql #percona #qa

— Roel Van de Paar (@RoelVandePaar) November 21, 2014

Last week @rameshvs02 & @RoelVandePaar logged 70+ bugs. Today reducer.sh was updated w/ pquery functionality. Testcases on their way! #mysql

— Roel Van de Paar (@RoelVandePaar) October 27, 2014

All of the above can be done on commodity hardware, running a single server, running single-threaded SQL (single client) and with no special mysqld options activated.

Compare this to RQG. Even with combinations.pl running hundreds if not thousands of mysqld — option combinations, and with nearly-everything-is-random-sql, it still comes nowhere near even one tenth of that number/speed. And this is even when using high-end hardware, 8 simultaneous mysqld servers, up to 20-25 client threads and at times special grammar-mix tools like MaxiGen etc.

In preparation for the Twitter week mentioned above we started running 4-5 simultaneous pquery run’s (5x mysqld, still all single threaded; a single client per mysqld) in different shell screen sessions, controlled by cron jobs.

A whole set of automation scripts were quickly added to handle the huge influx in bugs (you can get all for free (GPLv2) at $bzr branch lp:percona-qa – see pquery*.sh files), and now you can quickly review a list of issues pquery discovered. For writing this article, I started a run and in it’s first hour it found exactly 85 crashes. Here is a report from around ~2h;

[roel@localhost 830147]$ ~/percona-qa/pquery-results.sh ================ Sorted unique issue strings (Approx 439 trials executed, 167 remaining reducer scripts) btr0btr.ic line 143 (Seen 31 times: reducers 123,124,135,150,159,164,16,173,175,178,179,18,196,199,224,22,238,245,286,310,319,324,366,388,38,401,67,73,78,88,9) btr0btr.ic line 169 (Seen 1 times: reducers 158) btr0cur.cc line 769 (Seen 1 times: reducers 304) buf0buf.cc line 2738 (Seen 2 times: reducers 113,257) fsp0fsp.cc line 1899 (Seen 5 times: reducers 145,174,409,69,85) . is_set (Seen 32 times: reducers 112,165,170,192,203,218,231,249,24,253,259,273,278,280,289,329,331,333,336,338,363,371,373,379,384,398,404,44,47,6,72,82) .length % 4 (Seen 4 times: reducers 169,220,307,80) m_lock .= __null .... thd->mdl_context.is_lock_owner.m_namespace, ...., ...., MDL_SHARED(Seen 3 times: reducers 297,403,86) row0quiesce.cc line 683 (Seen 1 times: reducers 97) row0umod.cc line 338 (Seen 1 times: reducers 357) .slen % 2 (Seen 21 times: reducers 106,122,131,144,221,250,251,252,275,282,296,316,318,32,359,375,39,405,407,43,46) .slen % 4 (Seen 5 times: reducers 103,382,76,7,81) sort_field->length >= length (Seen 1 times: reducers 138) timer == __null (Seen 36 times: reducers 133,139,149,160,161,181,183,184,185,20,212,227,229,234,244,260,266,274,292,294,295,298,301,308,326,327,330,343,346,364,367,400,48,50,59,99) .tlen % 2 (Seen 8 times: reducers 117,119,200,205,213,217,285,35) .tlen % 4 (Seen 3 times: reducers 25,355,365) trx0roll.cc line 264 (Seen 1 times: reducers 40) Z21mysql_execute_commandP3THD (Seen 4 times: reducers 182,237,291,393) ZN13Bounded_queueIhhE4pushEPh (Seen 3 times: reducers 101,118,214) ZN8Protocol13end_statementEv (Seen 4 times: reducers 211,410,42,61) ================

For these (standard by now) pquery runs, we use pquery-run.sh. It starts a server, executes and monitors the pquery binary, and then checks on the outcome:

[roel@localhost percona-qa]$ ./pquery-run.sh [07:23:21] [0] Workdir: /sda/459689 | Rundir: /dev/shm/459689 | Basedir: /sda/Percona-Server-5.6.21-rel69.0-687.Linux.x86_64-debug [07:23:21] [0] mysqld Start Timeout: 60 | Client Threads: 1 | Queries/Thread: 25000 | Trials: 100000 | Save coredump trials only: TRUE [07:23:21] [0] Pquery timeout: 15 | SQL file used: /home/roel/percona-qa/pquery/main-new.sql [07:23:21] [0] MYSAFE: --maximum-bulk_insert_buffer_size=1M --maximum-join_buffer_size=1M --maximum-max_heap_table_size=1M --maximum-max_join_size=1M --maximum-myisam_max_sort_file_size=1M --maximum-myisam_mmap_size=1M --maximum-myisam_sort_buffer_size=1M --maximum-optimizer_trace_max_mem_size=1M --maximum-preload_buffer_size=1M --maximum-query_alloc_block_size=1M --maximum-query_prealloc_size=1M --maximum-range_alloc_block_size=1M --maximum-read_buffer_size=1M --maximum-read_rnd_buffer_size=1M --maximum-sort_buffer_size=1M --maximum-tmp_table_size=1M --maximum-transaction_alloc_block_size=1M --maximum-transaction_prealloc_size=1M --log-output=none --sql_mode=ONLY_FULL_GROUP_BY [07:23:21] [0] Archiving a copy of this script (/home/roel/percona-qa/pquery-run.sh) in the workdir (/sda/459689) for later reference... [07:23:21] [0] Archiving a copy of the infile used for this run (/home/roel/percona-qa/pquery/main-new.sql) in the workdir (/sda/459689) for later reference... [07:23:21] [0] Generating datadir template (using mysql_install_db)... [07:23:29] [0] Making a copy of mysqld used to /sda/459689/mysqld (handy for core file analysis and manual bundle creation)... [07:23:29] [0] Storing a copy of ldd files for mysqld in same directory also... [07:23:29] [0] Starting pquery testing iterations... [07:23:29] [0] ====== TRIAL #1 ====== [07:23:29] [0] Ensuring there are no relevant servers running... [07:23:29] [0] Clearing rundir... [07:23:29] [0] Generating new workdir /dev/shm/459689/1... [07:23:29] [0] Copying datadir from template... [07:23:29] [0] Starting mysqld. Error log is stored at /dev/shm/459689/1/log/master.err [07:23:29] [0] Waiting for mysqld (pid: 10879) to fully start... [07:23:31] [0] Server started ok. Client: /sda/Percona-Server-5.6.21-rel69.0-687.Linux.x86_64-debug/bin/mysql -uroot -S/dev/shm/459689/1/socket.sock [07:23:31] [0] Starting pquery (log stored in /dev/shm/459689/1/pquery.log)... [07:23:31] [0] pquery running... ./pquery-run.sh: line 150: 10879 Aborted (core dumped) $CMD > ${RUNDIR}/${TRIAL}/log/master.err 2>&1 <---- success! [07:23:32] [0] Cleaning up... [07:23:36] [0] pquery summary: 578/769 queries failed (24.84% were successful) [07:23:36] [0] mysqld core detected at /dev/shm/459689/1/data/core.10879.1000.1000.6.1414700611.mysqld [07:23:36] [1] Copying rundir from /dev/shm/459689/1 to /sda/459689/1 [07:23:36] [1] ====== TRIAL #2 ====== [...]

(The MYSAFE settings are some generic settings specifically suited for QA tested – kindly provided by Shane Bester)

Within the scripts many QA-goodies are already built-in: automated gdb query extraction from the core and the error log (each added 3x to the end of the sql trace to ensure maximum reproducibility), unique issue classification using bug-relevant strings, /dev/shm execution for optimal speed, etc. – it is all based/build on many years of mysqld QA experience.

If you can’t wait to spin off some I-crashed-mysqld (it’s easy you know…) bugs of your own, download the tools at lp:percona-qa ($bzr branch lp:percona-qa) and checkout the many pquery* scripts. Some shown in use above.

And, when you’re ready for slightly more serious feature testing – add whatever statements (+matching mysqld options/parameters) your feature uses to the sql file (or even replace it), plus the relevant mysqld options to pquery-run.sh (MYEXTRA string) (You can use sql-interleave.sh/sql-interleave-switch.sh to interleave new sql into the main sql file(s), available in the pquery/ directory of the percona-qa branch mentioned above). Soon you will see fireworks.

Enjoy the show!

The post The future of MySQL quality assurance: Introducing pquery appeared first on MySQL Performance Blog.


Subscribe to Percona aggregator