Month: March 2015

Scripts which I use to automatically build MySQL servers and run tests

Few months ago, when MySQL Engineering Team moved MySQL Server sources to GitHub I found it would be waste of time to manually copy all scripts which I use to regularly and automatically build and test all versions, needed for verifying bug reports. I run these scripts on 3 machines at least. So I started my own GitHub project, called mysql-scriptsNow this project contains four scripts. First one is which  I use for building. By default it checks out MySQL Server sources, builds them in directory $HOME/src/mysql-VERSION, then installs to $HOME/build/mysql-VERSION But all configuration is settable. For example, now I regularly build Percona Server with single command ` -g percona-server -p “percona_server-” -b 5.1 -b 5.5 -b 5.6` Before using this script you should clone either MySQL or Percona server and checkout each of branches at least once. You also need to create all src/server-name-VERSION directories.Second script is which I use to run MTR test cases automatically on any number of MySQL or Percona server versions.Third is which archives copies tests to remote machine.I wrote about, and already They changed a bit after that blog post, but not significantly and functionality remains same.

Command line prompt

The mysql> command-line prompt is iconic, shown in countless documentation pages, forum posts, tutorials and manuals. It’s immediately identifiable – one look, and you immediately know the context in which commands are being executed. So it’s only with good reason that we would consider changing the prompt value to something else, and Daniël van Eeden provided a compelling suggestion to modify this to provide user, host and database context. Because the mysql prompt is user-configurable, this is easy to do dynamically:
mysql> prompt something>
PROMPT set to ‘something> ‘
something> select 1;
| 1 |
| 1 |
1 row in set (0.00 sec)


Using the special character sequences defined at the bottom of one of the documentation pages, the prompt can supply with a number of different and useful information which provides context in the command line:
something> prompt \u@\h [\d]>
PROMPT set to ‘\u@\h [\d]> ‘
root@localhost [(none)]> prompt \h:\p [\d]>
PROMPT set to ‘\h:\p [\d]> ‘
localhost:3310 [(none)]> prompt \D>
PROMPT set to ‘\D> ‘
Mon Mar 23 17:09:58 2015>

The prompt can also be set as a command-line argument to the client, or via the configuration file read by the client at startup. Users who manage many servers and need information identifying to which instance a given command line client is connected might want to leverage the configuration file option, as this will affect all client instances started by default.
We actually changed the default prompt to “\u@\h [\d]> ” as Daniël proposed, but pulled it back out of 5.7 after using it internally for a few days and realizing a few issues. This post aims to document why we reverted to the original mysql> prompt, the options we’ve considered as alternatives, and a call for feedback.
Copying SQL
The mysql> prompt length aligns with the prompt length for continuation prompts, where queries span multiple lines. This makes it easy to copy SQL commands from terminals:
mysql> SELECT 1,
-> NOW();

When using prompts with differing length, copy operations pick up extraneous characters, requiring users to manipulate the contents to transform it back into executable SQL:
root@localhost [(none)] > SELECT 1,
-> NOW();

An option would be to make the buffer the continuation prompt to the same length as the original prompt. With larger and dynamic prompts, the results might be unsatisfactory – the example above takes up 25 characters of my 80-character-wide terminal.
MySQL allows users to connect without specifying a default database, and that’s not going to change anytime soon. This results in “[(none)]” being displayed in the prompt. When I first encountered the prompt change, I had to ask what the “none” meant – I had no idea what that meant. Informed users will only need to be told once (or never, if they are smarter than me), but it’s bound to confuse less-experienced users.
Misleading user name
The MySQL ACL system makes displaying user names an interesting exercise. With wildcards, anonymous users and proxy accounts, there’s any number of possible values for “user name” to display. Do you want to know the user name you supplied to the client, the user name portion of the account used to authenticate, or the user name portion of the account associated with the privileges you have? They are different:
shell> bin\mysql.exe -ublah -P3310
Welcome to the MySQL monitor. Commands end with ; or \g.

blah@localhost [(none)] > show grants;
| Grants for @localhost |
| GRANT USAGE ON *.* TO ”@’localhost’ |
1 row in set (0.00 sec)

Similarly for proxy users:
pa@localhost [(none)] > SELECT USER(), CURRENT_USER(), @@session.proxy_user;
| USER() | CURRENT_USER() | @@session.proxy_user |
| pa@localhost | pb@localhost | ‘pa’@’localhost’ |
1 row in set (0.00 sec)

pa@localhost [(none)] > show grants;
| Grants for pb@localhost |
| GRANT USAGE ON *.* TO ‘pb’@’localhost’ |
1 row in set (0.00 sec)

Like the [(none)] value, experienced MySQL users will understand the difference between USER(), CURRENT_USER() and @@session.proxy_user, but this can easily confuse users new to MySQL.
Server vs. client host
I’m used to seeing user@host define MySQL accounts, but that’s not what \u@\h actually provides. As noted above, the user is the user name supplied to the client, rather than the account. But the host is the server host, not the host of the account.
shell> ipconfig

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . :
IPv4 Address. . . . . . . . . . . :

shell> bin\mysql -utodd -h192.168.2.54
Welcome to the MySQL monitor. Commands end with ; or \g.

todd@ [(none)] > select user();
| user() |
| todd@GRAPHITE |
1 row in set (0.02 sec)

Note that using the \U option shows the full user name/host returned from USER():
todd@ [(none)] > prompt \U >
PROMPT set to ‘\U > ‘

It makes sense to display the host a client is connected to, rather than the host component of the account used to connect. But it does allow for confusion about what is being displayed.
Other common use cases
Daniël’s suggestion works great for DBAs who manage multiple MySQL installations and need to ensure they are executing commands against the intended instance.  It’s less useful for single-host deployments, or for use cases like MySQL Sandbox, where multiple instances may all exist locally.  I frequently have 5+ MySQL instances running locally for various testing purposes, and the proposed default prompt wouldn’t help me distinguish between them at all.  Such use cases would be better served by a prompt value like “\h:\p > “:
root@localhost [(none)] > prompt \h:\p >
PROMPT set to ‘\h:\p > ‘
localhost:3310 >

The need Daniël identified is something we want to address, but the issues listed above gave us second thoughts about changing the default prompt. We’ve identified some possible alternatives, which I’m listing below:
Add prompt help
The definition of special sequences isn’t easy to find in the documentation, and even some experienced MySQL users are unfamiliar with the options that exist. Those that do know the possibilities for customizing prompts sometimes find it difficult to quickly recall the special sequences they might need. We’d like to add this information to the client, so that it is readily available:
mysql> prompt ?

\d – The default database
\u – Your user name
\h – The server host

Define prompt macros
We know that \u@\h [\d]> reflects the needs of a common use case, as does \h:\p >. Another common use case likely involves outputting the date/time in the prompt for tracking purposes. Another common use case might be to simply include the default database.
We can provide macros which are easily remembered and cycled through, so that users don’t have to build their own prompts for these common use cases. For example:
mysql> prompt \1
PROMPT set to ‘\u@\h using \d> ‘
root@localhost using (none)> prompt \2
PROMPT set to ‘\h:\p> ‘
localhost:3310> prompt \3
PROMPT set to ‘\D> ‘
Fri Mar 20 14:08:59 2015> prompt
Returning to default PROMPT of mysql>

Multi-line prompts
One possible solution is to leverage multi-line prompts, so that contextual information is provided on one line, while the familiar mysql> prompt is retained:
root@localhost [(none)] > prompt \u@\h [\d]\nmysql>
PROMPT set to ‘\u@\h [\d]\nmysql> ‘
root@localhost [(none)]
mysql> SELECT 1;
| 1 |
| 1 |
1 row in set (0.00 sec)

root@localhost [(none)]

This makes copying SQL easy, but still displays context information for a specific use case, and which may confuse less-experienced users.
Per-instance custom prompt definitions
As noted earlier, it’s easy enough to define a custom prompt dynamically, as a command-line option, or even globally within a configuration file. It could be useful if the custom prompts could be defined on a per-connection basis, and stored with other connection options in mysql_config_editor. Doing so would allow users to connect to a specific instance, and have a specific (non-default) prompt automatically associated.
Please let us know if you think the reasoning behind our decision to leave the prompt as-is is unsound in any way, or if you have particular affinity for any of the proposed alternatives. Thanks!

Analyst for MySQL v1.1: Database Performance, Security, & Best Practices Auditing Tool Released – Download for FREE!

Itchy Ninja Software is pleased to announce the release of Analyst for MySQL v1.1. Revolutionize the way you work and administrate MySQL, MariaDB, Galera, and Percona XtraDB installations.
Make More Efficient Use of Your Time
Gathering all of the metrics to diagnose a database installation is a very time consuming process, and many simply do not have the experience to know where to begin. With Analyst for MySQL, you will be able to get your hands on hundreds of metrics within moments. It really takes all of the guesswork, as well as tedious long sessions of writing queries out of managing a MySQL database server.
Not only can you run the program on Windows, Mac, or Linux, you can also generate server reports from each of those platforms as well! No need to install anything on the server at any time. All diagnostics are run from your laptop or desktop machine. The source computer (your laptop or desktop machine) can be any operating system and so can the server you are auditing.
Performance Tuning
With Analyst, you will get dozens of charts, tables, graphs, and rules concentrated on helping you resolve performance issues. You can even see CPU utilization, disk I/O, swapping / paging, network throughput, and more. For Linux systems with systat (SAR) installed, historical system metrics are also available.
Minimize Downtime
Analyst includes over 200 industry-proven rules which help identify the source of the issues as well as advice on how to resolve them. You’ll also get multiple charts, tables, and graphs that give you immediate access to the specifics of how your server is running.
Team Collaboration
Ever wish you could share all of the key server metrics with the rest of your team when there is an outage? With Analyst for MySQL we make this easy. You will be able to export all of the key data and send it to anyone you want. Have a manager who wants a report of what you have done? Analyst makes it easy to show reports from before and after your work to show improvement in scoring. Have team members who are not in the office and/or don’t have access to the server? No problem. Just send an Analyst report file to them and they will have instant access to the same data you have.
Security Hardening
With Analyst for MySQL, you will find anonymous users, users without passwords, accounts with excessive privileges, connections that are not encrypted, and much more.
Best Practices
One of the first tasks a MySQL administrator should perform when receiving a new server or setting one up is to ensure standards. We have spent countless hours drafting documentation on proper server configuration to ensure environments meet industry best practices. With Analyst for MySQL, you can benefit from all of our work.
Server Metrics
See metrics in the form of tabular data, pie graphs, line charts, and more. You will have metrics such as memory usage, InnoDB Buffer Pool usage, query cache utilization, and more at the tip of your fingers. Confused by the new Performance Schema? No worries. Analyst provides easy to understand metrics and does all of the heavy lifting for you.
For those with systat package installed on Linux, you will be able to turn back the clock and see how the server was performing throughout the day! See metrics such as CPU utilization, disk I/O, RAM consumption, network throughput, and more in gorgeous charts and graphs.
High Availability
For those with an Analyst Enterprise license, an audit of a MySQL Cluster, Percona XTRADB Cluster, MariaDB CLuster, or Galera Cluster you will give you rules, tables, graphs, and alerts giving detailed information on cluster performance. You will know immediately how the cluster is performing. Moreover, you will know whether the node you audited is the source of the problem or whether it is another node in the cluster.
Ease of Use
We created Analyst for MySQL to be easy to use. We know you don’t have time, or even want to read a complicated manual, to figure out how to use software. Want to archive your findings? Or send a report to someone? Analyst offers the ability to save your data to disk, export it into PDF, CSV, PNG, RTF, or HTML format, and of course printing.
Server Scoring
Analyst will examine key metrics in several areas including standardization, best practices, administration, high availability, performance, security, and more and assign a score to that category. You will get an overall score to see the current condition of your server. Once you start making the suggested improvements, run another analysis of the server. What could be easier?
Free To Use!
Click here to download your copy today!

Percona XtraBackup 2.2.10 for MySQL hot backups is now available (free!)

Percona is glad to announce the release of Percona XtraBackup 2.2.10 on March 31, 2015. Downloads are available from our download site or Percona Software Repositories. Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered […]

The post Percona XtraBackup 2.2.10 for MySQL hot backups is now available (free!) appeared first on MySQL Performance Blog.

OurSQL Episode 206: Pieces of Fabric

PodcastsPerformanceServer Tuning

In this episode we discuss sharding and high availability with MySQL Fabric, including installation and configuration. Ear Candy is rolling out GTIDs with no restarting, and At the Movies is putting MySQL Fabric to use, including a live demo.

Using gdb to understand what locks (and when) are really set by InnoDB. Part I.

There is a story behind this post. To make it short, the detailed study presented here started when I noted old Bug #72748 in “No feedback” status few days ago. I considered this status totally wrong and started a discussion on Facebook about it (yes, this is what I usually do, I discuss handling of MySQL bugs in public).The technical topic of this discussion (blaming aside) was the way AUTO-INC locks are set by InnoDB with default innodb_autoinc_lock_mode=1. Manual defines “bulk inserts” concept: “Bulk inserts” Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT … SELECT, REPLACE … SELECT, and LOAD DATA statements, but not plain INSERT. InnoDB will assign new values for the AUTO_INCREMENT column one at a time as each row is processed.  and then says: innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)This is the default lock mode. In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT … SELECT, REPLACE … SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time. I suspected (based on test case presented by my colleague Peiran last year) that this may not be case in reality, or at least the process is a bit more complicated. In the process of technical discussion, to make my points clear and highlight the underlying problem, I’ve reported another bug, Bug #76533, with simple test case and some speculations (that were not entirely correct as I soon found out).After re-reading my report and re-checking results I was really surprised to see the result that can not be explained by the manual page mentioned above (yes, after all my bug reports for the manual I still consider it correct until the opposite is clearly proved, but that can be reproduced on MySQL 5.5, 5.6 and 5.7.Once when I was in doubts I’ve got a nice advice from my dear friend Sinisa, “Read the Source, Luke!”. But one has probably to spend few days at least to understand enough details to say for sure what happens when one executes INSERT INTO T … SELECT FROM T (same table) comparing to INSERT INTO T … SELECT FROM S (other table). So, I gave good advice myself (that I actually used to give others at FOSDEM 2015): “Use gdb, Luke!”. It was even easier to follow that way after reading this great post by Annamalai.So, I set up simple test, like this:[openxs@centos ~]$ mysql -uroot testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.23-72.1-log Percona Server (GPL), Release 72.1, Revision 0503478Copyright (c) 2009-2015 Percona LLC and/or its affiliatesCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.mysql> select @@innodb_autoinc_lock_mode, @@log_bin, @@binlog_format;+—————————-+———–+—————–+| @@innodb_autoinc_lock_mode | @@log_bin | @@binlog_format |+—————————-+———–+—————–+|                          1 |         1 | STATEMENT       |+—————————-+———–+—————–+1 row in set (0.01 sec)mysql> select @@innodb_locks_unsafe_for_binlog;+———————————-+| @@innodb_locks_unsafe_for_binlog |+———————————-+|                                0 |+———————————-+1 row in set (0.00 sec) mysql> create table t(id int auto_increment primary key, val int);Query OK, 0 rows affected (0.21 sec)mysql> create table tt(id int auto_increment primary key, val int);Query OK, 0 rows affected (0.14 sec)mysql> insert into t(val) values (1), (2), (3), (4);Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> insert into tt(val) values (1), (2), (3), (4);Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from t;+—-+——+| id | val  |+—-+——+|  1 |    1 ||  2 |    2 ||  3 |    3 ||  4 |    4 |+—-+——+4 rows in set (0.00 sec)mysql> select * from tt;+—-+——+| id | val  |+—-+——+|  1 |    1 ||  2 |    2 ||  3 |    3 ||  4 |    4 |+—-+——+4 rows in set (0.01 sec)That is, on Percona Server 5.6.23 I’ve created two InnoDB tables with auto_increment PRIMARY key and one other integer column. I’ve inserted the same 4 rows into both tables. Now, I wonder what locks are set while executing each the following statements, in the same single session, and in what order they are set:insert into t(val) select 100 from tt;insert into t(val) select 100 from t;as I suspect there is a difference that is not clearly documented. I decided to use gdb to set breakpoints on the following functions: lock_table() lock_rec_lock()row_lock_table_autoinc_for_mysql()Don’t ask me why these, it’s topic for a separate discussion (but you can guess based on the names and/or code review and/or this great post I’ve already mentioned).I’ve made sure there is debug info for the official RPM binaries I’ve used:[root@centos ~]# rpm -qa | grep -i percona… Percona-Server-server-56-5.6.23-rel72.1.el6.x86_64Percona-Server-56-debuginfo-5.6.23-rel72.1.el6.x86_64…So, I’ve attached gdb to mysql process running and set breakpoints:[root@centos ~]# gdb -p `pidof mysqld`GNU gdb (GDB) Red Hat Enterprise Linux (7.2-75.el6)…  Loaded symbols for /usr/lib64/mysql/plugin/ha_tokudb.so0x00007fd74b0970d3 in poll () from /lib64/ separate debuginfos, use: debuginfo-install glibc-2.12-1.149.el6_6.5.x86_64 jemalloc-3.6.0-1.el6.x86_64 keyutils-libs-1.4-5.el6.x86_64 krb5-libs-1.10.3-33.el6.x86_64 libaio-0.3.107-10.el6.x86_64 libcom_err-1.41.12-21.el6.x86_64 libgcc-4.4.7-11.el6.x86_64 libselinux-2.0.94-5.8.el6.x86_64 libstdc++-4.4.7-11.el6.x86_64 nss-softokn-freebl-3.14.3-22.el6_6.x86_64 openssl-1.0.1e-30.el6_6.7.x86_64 zlib-1.2.3-29.el6.x86_64(gdb) set pagination 0(gdb) b lock_tableBreakpoint 1 at 0x91c940: file /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/, line 4426.(gdb) b lock_rec_lockBreakpoint 2 at 0x91de04: file /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/, line 2329.(gdb) b row_lock_table_autoinc_for_mysqlBreakpoint 3 at 0x984504: file /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/, line 1146.(gdb) cContinuing.Then in the session where I created my tables I’ve started transaction to check the first statement:mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> insert into t(val) select 100 from tt;and immediately hit one of breakpoints in gdb:[Switching to Thread 0x7fd74cf79700 (LWP 1866)]Breakpoint 1, lock_table (flags=0, table=0x7fd7233f7de8, mode=LOCK_IS, thr=0x7fd7233e29e8) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            if (flags & BTR_NO_LOCKING_FLAG) {(gdb) p table->name$1 = 0x7fd7383feae8 “test/tt”So, we had a request for IS lock for the tt table.  I had source code at hand to check for some details that may be useful while interpreting data:– this is from  storage/innobase/include/lock0types.h /* Basic lock modes */enum lock_mode {        LOCK_IS = 0,    /* intention shared */        LOCK_IX,        /* intention exclusive */        LOCK_S,         /* shared */        LOCK_X,         /* exclusive */        LOCK_AUTO_INC,  /* locks the auto-inc counter of a table                        in an exclusive mode */        LOCK_NONE,      /* this is used elsewhere to note consistent read */        LOCK_NUM = LOCK_NONE, /* number of lock modes */        LOCK_NONE_UNSET = 255};– this is from storage/innobase/include/lock0lock.h#define LOCK_WAIT       256     /*!< Waiting lock flag; when set, it                                means that the lock has not yet been                                granted, it is just waiting for its                                turn in the wait queue *//* Precise modes */#define LOCK_ORDINARY   0       /*!< this flag denotes an ordinary                                next-key lock in contrast to LOCK_GAP                                or LOCK_REC_NOT_GAP */#define LOCK_GAP        512     /*!< when this bit is set, it means that the                                lock holds only on the gap before the record;                                for instance, an x-lock on the gap does not                                give permission to modify the record on which                                the bit is set; locks of this type are created                                when records are removed from the index chain                                of records */#define LOCK_REC_NOT_GAP 1024   /*!< this bit means that the lock is only on                                the index record and does NOT block inserts                                to the gap before the index record; this is                                used in the case when we retrieve a record                                with a unique key, and is also used in                                locking plain SELECTs (not part of UPDATE                                or DELETE) when the user has set the READ                                COMMITTED isolation level */#define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waiting                                gap type record lock request in order to let                                an insert of an index record to wait until                                there are no conflicting locks by other                                transactions on the gap; note that this flag                                remains set when the waiting lock is granted,                                or if the lock is inherited to a neighboring                                record */if gdb would not be kind enough to do this for me (it ‘s, for enum, as you can see).Now, let’s check how we ended up here:(gdb) bt#0  lock_table (flags=0, table=0x7fd7233f7de8, mode=LOCK_IS, thr=0x7fd7233e29e8) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/  0x0000000000999bdd in row_search_for_mysql (buf=0x7fd71bfed410 “\37504”, mode=1, prebuilt=0x7fd7233e2068, match_mode=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/  0x00000000008e8b41 in ha_innobase::index_read (this=0x7fd71bf9d610, buf=0x7fd71bfed410 “\37504”, key_ptr=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/handler/  0x00000000008dc090 in ha_innobase::index_first (this=0x7fd71bf9d610, buf=0x7fd71bfed410 “\37504”) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/handler/  0x000000000059a3ee in handler::ha_index_first (this=0x7fd71bf9d610, buf=0x7fd71bfed410 “\37504”) at /usr/src/debug/percona-server-5.6.23-72.1/sql/  0x00000000006b491d in join_read_first (tab=0x7fd72332dc60) at /usr/src/debug/percona-server-5.6.23-72.1/sql/  0x00000000006b3c0d in sub_select (join=0x7fd72332cf40, join_tab=0x7fd72332dc60, end_of_records=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/  0x00000000006b2ea8 in do_select (this=0x7fd72332cf40) at /usr/src/debug/percona-server-5.6.23-72.1/sql/  JOIN::exec (this=0x7fd72332cf40) at /usr/src/debug/percona-server-5.6.23-72.1/sql/  0x00000000006fc3c5 in mysql_execute_select (thd=0x7fd71c354000, tables=0x7fd72332c888, wild_num=0, fields=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ mysql_select (thd=0x7fd71c354000, tables=0x7fd72332c888, wild_num=0, fields=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x00000000006fcc25 in handle_select (thd=0x7fd71c354000, result=0x7fd72332ce00, setup_tables_done_option=1073741824) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x00000000006d977b in mysql_execute_command (thd=0x7fd71c354000) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x00000000006db7e8 in mysql_parse (thd=0x7fd71c354000, rawbuf=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x00000000006dcf59 in dispatch_command (command=<value optimized out>, thd=0x7fd71c354000, packet=0x7fd72330f001 “insert into t(val) select 100 from tt”, packet_length=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x00000000006aa772 in do_handle_one_connection (thd_arg=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x00000000006aa860 in handle_one_connection (arg=0x7fd71c354000) at /usr/src/debug/percona-server-5.6.23-72.1/sql/ 0x0000000000adc113 in pfs_spawn_thread (arg=0x7fd71c3443e0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/perfschema/ 0x00007fd74c9869d1 in start_thread () from /lib64/ 0x00007fd74b0a08fd in clone () from /lib64/, we started with SELECT part and set IS lock on the table we read from tt. This is expected, as manual says: “INSERT INTO T SELECT … FROM S WHERE … sets an exclusive index record lock (without a gap lock) on each row inserted into T. If the transaction isolation level is READ COMMITTED, or innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case:”Our case is REPEATABLE READ and innodb_locks_unsafe_for_binlog is not enabled, so some kind of S locks will be set on rows, and it means we need IS lock on the table (again, by the manual). Let’s continue:(gdb) cContinuing.Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249ae700, heap_no=2, index=0x7fd7233f82e8, thr=0x7fd7233e29e8) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {(gdb) p index->table_name$2 = 0x7fd7383feae8 “test/tt”(gdb) p index->name$3 = 0x7fd7233f8480 “PRIMARY”So, we clearly see an attempt to set a record lock on some row (heap_no=2) in the PRIMARY key of the table tt (this is a row of data, surely). mode=2 means LOCK_S in the enum. So this is an attempt to set S lock on the row (“shared next-key lock” as manual explained above, one day I’ll explain how to distinguish it from other kinds of shared locks if needed). Let’s continue:(gdb) cContinuing.Breakpoint 3, row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/            if (trx == table->autoinc_trx) {(gdb) p trx$4 = <value optimized out>(gdb) p table->namevalue has been optimized out(gdb) p table$5 = <value optimized out>(gdb) bt#0  row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/  0x00000000008e8dcc in ha_innobase::innobase_lock_autoinc (this=0x7fd71bf9cc10) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/handler/  0x00000000008e9128 in innobase_get_autoinc (this=0x7fd71bf9cc10, offset=1, increment=1, nb_desired_values=1, first_value=0x7fd74cf767c0, nb_reserved_values=0x7fd74cf767d0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/handler/  ha_innobase::get_auto_increment (this=0x7fd71bf9cc10, offset=1, increment=1, nb_desired_values=1, first_value=0x7fd74cf767c0, nb_reserved_values=0x7fd74cf767d0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/handler/  0x000000000059ad18 in handler::update_auto_increment (this=0x7fd71bf9cc10) at /usr/src/debug/percona-server-5.6.23-72.1/sql/  0x00000000008f06e9 in ha_innobase::write_row (this=0x7fd71bf9cc10, record=0x7fd71bfec810  <incomplete sequence \375>) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/handler/…#19 0x00000000006dcf59 in dispatch_command (command=<value optimized out>, thd=0x7fd71c354000, packet=0x7fd72330f001 “insert into t(val) select 100 from tt”, packet_length=Unhandled dwarf expression opcode 0xf3) at /usr/src/debug/percona-server-5.6.23-72.1/sql/… I had not checked the code in time, so attempts to print something I see had not helped, but top lines of backtrace clearly show: we want to get auto_increment value for the row we are planning to write somewhere (to the table t). So, we set IS lock on table read from, we set S lock on the first row we tried to read from it via SELECT and then we try to insert the row into the target table t, and we need auto_increment value for it. Let’s continue:(gdb) cContinuing.Breakpoint 1, lock_table (flags=0, table=0x7fd7233f69e8, mode=LOCK_AUTO_INC, thr=0x7fd7233e3780) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            if (flags & BTR_NO_LOCKING_FLAG) {(gdb) p table->name$6 = 0x7fd7383feac0 “test/t”(gdb) p thr$7 = (que_thr_t *) 0x7fd7233e3780(gdb) p *thr$8 = {common = {type = 9, parent = 0x7fd7233e36b8, brother = 0x0, val = {data = 0x0, ext = 0, len = 0, type = {prtype = 0, mtype = 0, len = 0, mbminmaxlen = 0}}, val_buf_size = 0}, magic_n = 8476583, child = 0x7fd7233e3548, graph = 0x7fd7233e36b8, state = 1, is_active = 1, run_node = 0x7fd7233e3548, prev_node = 0x7fd7233e3548, resource = 0, lock_state = 0, slot = 0x0, thrs = {prev = 0x0, next = 0x0}, trx_thrs = {prev = 0x0, next = 0x0}, queue = {prev = 0x0, next = 0x0}, fk_cascade_depth = 0}So,  we clearly see the attempt to set LOCK_AUTO_INC on the table t. This happens only after we read some row from the table tt and now inserting it into the target table, t.  (gdb) cContinuing.Breakpoint 1, lock_table (flags=0, table=0x7fd7233f69e8, mode=LOCK_IX, thr=0x7fd7233e3780) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            if (flags & BTR_NO_LOCKING_FLAG) {(gdb) p table->name$9 = 0x7fd7383feac0 “test/t”(gdb) p *table$10 = {id = 65, heap = 0x7fd71c3514a0, name = 0x7fd7383feac0 “test/t”, dir_path_of_temp_table = 0x0, data_dir_path = 0x0, space = 51, flags = 1, flags2 = 80, ibd_file_missing = 0, cached = 1, to_be_dropped = 0, n_def = 5, n_cols = 5, can_be_evicted = 1, corrupted = 0, drop_aborted = 0, cols = 0x7fd71c371868, col_names = 0x7fd71c3718f0 “id”, name_hash = 0x0, id_hash = 0x0, indexes = {count = 1, start = 0x7fd7233f7b68, end = 0x7fd7233f7b68}, foreign_set = std::set with 0 elements, referenced_set = std::set with 0 elements, table_LRU = {prev = 0x7fd74902cb68, next = 0x7fd7233f7de8}, fk_max_recusive_level = 0, n_foreign_key_checks_running = 0, def_trx_id = 84487, query_cache_inv_trx_id = 84497, big_rows = 0, stats_latch_created = 2, stats_latch = 0x7fd71c3efb80, stat_initialized = 1, memcached_sync_count = 0, stats_last_recalc = 1427781732, stat_persistent = 0, stats_auto_recalc = 0, stats_sample_pages = 0, stat_n_rows = 4, stat_clustered_index_size = 1, stat_sum_of_other_index_sizes = 0, stat_modified_counter = 0, stats_bg_flag = 0 ’00’, autoinc_lock = 0x7fd71c3718a8, autoinc_mutex = {event = 0x7fd71c3ef600, lock_word = 0 ’00’, waiters = 0, list = {prev = 0x7fd7233f7f50, next = 0x7fd71c370f28}, count_os_wait = 0, cmutex_name = 0xc3cb37 “&table->autoinc_mutex”, pfs_psi = 0x0}, autoinc = 6, n_waiting_or_granted_auto_inc_locks = 1, autoinc_trx = 0x7fd71c370c68, fts = 0x0, quiesce = QUIESCE_NONE, n_rec_locks = 0, n_ref_count = 1, locks = {count = 1, start = 0x7fd71c3718a8, end = 0x7fd71c3718a8}, is_corrupt = 0}(gdb) p table->autoinc_lock$11 = (ib_lock_t *) 0x7fd71c3718a8(gdb) p *(table->autoinc_lock)$12 = {trx = 0x7fd71c370c68, trx_locks = {prev = 0x7fd71c373530, next = 0x0}, type_mode = 20, hash = 0x0, index = 0x0, un_member = {tab_lock = {table = 0x7fd7233f69e8, locks = {prev = 0x0, next = 0x0}}, rec_lock = {space = 140561986054632, page_no = 0, n_bits = 0}}} Now, we set IX lock on table T (no wonder, we need to inserts rows there). According to the comments in the code:– this is from storage/innobase/include/lock0priv.h static const byte lock_compatibility_matrix[5][5] = { /**         IS     IX       S     X       AI */ /* IS */ {  TRUE,  TRUE,  TRUE,  FALSE,  TRUE}, /* IX */ {  TRUE,  TRUE,  FALSE, FALSE,  TRUE}, /* S  */ {  TRUE,  FALSE, TRUE,  FALSE,  FALSE}, /* X  */ {  FALSE, FALSE, FALSE, FALSE,  FALSE}, /* AI */ {  TRUE,  TRUE,  FALSE, FALSE,  FALSE}};…/* STRONGER-OR-EQUAL RELATION (mode1=row, mode2=column) *    IS IX S  X  AI * IS +  –  –  –  – * IX +  +  –  –  – * S  +  –  +  –  – * X  +  +  +  +  + * AI –  –  –  –  + * See lock_mode_stronger_or_eq(). */static const byte lock_strength_matrix[5][5] = { /**         IS     IX       S     X       AI */ /* IS */ {  TRUE,  FALSE, FALSE,  FALSE, FALSE}, /* IX */ {  TRUE,  TRUE,  FALSE, FALSE,  FALSE}, /* S  */ {  TRUE,  FALSE, TRUE,  FALSE,  FALSE}, /* X  */ {  TRUE,  TRUE,  TRUE,  TRUE,   TRUE}, /* AI */ {  FALSE, FALSE, FALSE, FALSE,  TRUE}};IX lock is compatible with AI (and AI is hold by this same transaction anyway), so let’s assume we got it and continue:(gdb) cContinuing.Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249ae700, heap_no=3, index=0x7fd7233f82e8, thr=0x7fd7233e29e8) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {(gdb) p index->table_name$13 = 0x7fd7383feae8 “test/tt”(gdb) p index->name$14 = 0x7fd7233f8480 “PRIMARY”Now we set S lock (mode=2) on the next row (heap_no=3) we read in SELECT. We continue reading…(gdb) cContinuing.Breakpoint 3, row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/            if (trx == table->autoinc_trx) {(gdb) cContinuing.So, request for auto_increment value again (we ask for the lock that we already have), to get it for the row we plan to insert. All clear, so we continued and got this:[Switching to Thread 0x7fd716bf7700 (LWP 1718)]Breakpoint 1, lock_table (flags=0, table=0x7fd74902cb68, mode=LOCK_IX, thr=0x7fd71c1db638) at /usr/src/debug/percona-serverinnodb_table-5.6.23-72.1/storage/innobase/lock/            if (flags & BTR_NO_LOCKING_FLAG) {(gdb) p table->name$15 = 0x7fd730396c60 “mysql/innodb_table_stats”So, other thread stepped in to set IX lock on innodb_table_stats table. This is a background update of table statistics and there will be many hits for our breakpoints because of this. I’ll skip them all from now on, to keep concentrated on one main topic. For our tables in question we later had already well know sequences:…Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249ae700, heap_no=5, index=0x7fd7233f82e8, thr=0x7fd7233e29e8) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {(gdb) p index->table_name$17 = 0x7fd7383feae8 “test/tt”(gdb) cContinuing.Breakpoint 3, row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/            if (trx == table->autoinc_trx) {(gdb) cContinuing….that is, requests for S lock on yet another row in the source table (tt) and then request for the auto_increment. It ends up like this (changes to persistent statistics tables skipped):Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249ae700, heap_no=1, index=0x7fd7233f82e8, thr=0x7fd7233e29e8) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {(gdb) p index->table_name$20 = 0x7fd7383feae8 “test/tt”(gdb) p block$21 = (const buf_block_t *) 0x7fd7249ae700(gdb) p *block$22 = {page = {space = 52, offset = 3, buf_fix_count = 1, io_fix = 0 ’00’, state = 5 ’05’, flush_type = 1, buf_pool_index = 0, zip = {data = 0x0, m_end = 0, m_nonempty = 0, n_blobs = 0, ssize = 0}, hash = 0x0, list = {prev = 0x7fd7249cf280, next = 0x0}, newest_modification = 129192199, oldest_modification = 0, LRU = {prev = 0x7fd7249ae580, next = 0x7fd7249ae880}, old = 0, freed_page_clock = 0, access_time = 1852500389, is_corrupt = 0}, frame = 0x7fd72c438000 “\333s\257\247”, unzip_LRU = {prev = 0x0, next = 0x0}, mutex = {event = 0x7fd72400e900, lock_word = 0 ’00’, waiters = 0, list = {prev = 0x7fd7249ae900, next = 0x7fd7249ae600}, count_os_wait = 0, cmutex_name = 0xc375cd “&block->mutex”, pfs_psi = 0x0}, lock = {lock_word = 1048575, waiters = 0, recursive = 0, writer_thread = 140561776342784, event = 0x7fd72400e980, wait_ex_event = 0x7fd72400ea00, list = {prev = 0x7fd7249ae940, next = 0x7fd7249ae640}, pfs_psi = 0x0, count_os_wait = 0, lock_name = 0xc375db “&block->lock”, last_s_file_name = 0xc281a0 “/mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos6-64/rpmbuild/BUILD/percona-server-5.6.23-72.1/storage/innobase/row/”, last_x_file_name = 0xc34650 “/mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos6-64/rpmbuild/BUILD/percona-server-5.6.23-72.1/storage/innobase/btr/”, writer_is_wait_ex = 0, last_s_line = 3226, last_x_line = 741}, lock_hash_val = 39242, check_index_page_at_flush = 1, modify_clock = 1, n_hash_helps = 0, n_fields = 1, n_bytes = 0, left_side = 1, curr_n_fields = 0, curr_n_bytes = 0, curr_left_side = 0, index = 0x0}(gdb) cContinuing.Note heap_no=1 above. This is a supremum record. I’ve also checked some details you can get for the block (page in the buffer pool) in the process. At this moment we’ve finally got rows inserted:mysql> insert into t(val) select 100 from tt;Query OK, 4 rows affected, 1 warning (31 min 9.51 sec)Records: 4  Duplicates: 0  Warnings: 1mysql> show warnings\G*************************** 1. row ***************************  Level: Note   Code: 1592Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.1 row in set (34.72 sec)As you can assume for the above (time to execute show warnings)  we hit some breakpoints as InnoDB continues to update persistent statistics for the table t. I’ve later executed the following:mysql> show engine innodb status\G…————TRANSACTIONS————Trx id counter 84515Purge done for trx’s n:o < 84514 undo n:o < 0 state: runningHistory list length 371LIST OF TRANSACTIONS FOR EACH SESSION:—TRANSACTION 84514, ACTIVE 223 sec2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2—TRANSACTION 84511, ACTIVE 2394 sec3 lock struct(s), heap size 360, 5 row lock(s), undo log entries 4MySQL thread id 1, OS thread handle 0x7fd74cf79700, query id 18 localhost root initshow engine innodb status——–…————–ROW OPERATIONS————–0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDB2 RW transactions active inside InnoDB0 RO transactions active inside InnoDB2 out of 1000 descriptors usedMain thread process no. 1681, id 140561818302208, state: sleepingNumber of rows inserted 12, updated 0, deleted 0, read 120.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/sSo, you see transaction that made 2 changes in the background thead (this is reated to persitent statistics table), and our transaction that made 4 changes (inserted 4 rows). You see 5 row locks (and surely will NOT see AUTO-INC table one no matter how you try, as statement is completed, but it was there). These are 5 S locks set on the tt table we read (4 rows read and supremum record). But where our X locks on rows inserted into the t table (transaction is still active)? Here they are:mysql> select * from t;+—-+——+| id | val  |+—-+——+|  1 |    1 ||  2 |    2 ||  3 |    3 ||  4 |    4 ||  5 |  100 ||  6 |  100 ||  7 |  100 ||  8 |  100 |+—-+——+8 rows in set (0.00 sec)They exist, but they are implicit. You’ve probably noted that there was no call for X record lock mentioned, and they had NOT happened. Implicit locks do not have accociated lock object with them, they are present just because the trx_id for the transaction that changed (inserted, in this case) the row. Annamalai explained them well here.Probably it’s time to stop, as this post is already big enough. To summarize, the following locks are set (and in the following order) while executing INSERT INTO t … SELECT FROM tt where there is an auto_increment column for which the value is generated in the destination table with default innodb_aunoinc_lock_mode=1 according to our tracing in gdb:IS lock on the source table (tt)S lock on the first row in tt we read with SELECTAUTO-INC lock on the destination table (t)IX lock on the destination table (t)Probably implicit X lock on the row inserted (we had not seen this in gdb clearly)S lock on the next row in tt we read with SELECTProbably implicit X lock on the row inserted (we had not seen this in gdb clearly)…S lock on the supremum in tt we read with SELECTI see three potential problems here.There is a small chance to run fast concurrent INSERT (that uses AUTO-INC lock) into the destination table after we read the first row from the source table, but before we request AUTO-INC lock on the destination.Also, try to find explanation for this case in the manual. It says nothing on when AUTO-INC lock is set.The last but not the least, I do not see any explanation of implicit X locks in the manual. I had not try hard yet.For these (and more) I’ll report bugs. The story will be continued in the next blog post(s) in this series. As for that “day” when I plan ot tell you more about InnoDB locks, it’s predefined. Please, visit this talk at Percona Live 2015. There Nilnanadan and me (let’s hope) will tell you notably more based on our findings and experience.

Pillars of Powershell #2: Commanding

Introduction This is the second blog post as a continuance in the series on the Pillars of PowerShell. In the initial blog post we went over the various interfaces that can be used to work with PowerShell. In this blog post we are going to start out by going through a few terms you might find when you…

MySQL Parallel Replication and Slave Group Commit the link above to read my latest article on the developer blog.  It is about MySQL Parallel Replication and a very nice side effect of the MariaDB implementation: Slave Group Commit.This is also a good opportunity to remind you that I will speak at Percona Live Santa Clara 2015 about Binlog Servers at  More to come about the content of the talk soon.

PowerShell Script to Manipulate SQL Server Backup Files

Scenario I use Ola Hallengren’s famous backup solution to back up my SQL Server databases. The destination for full backups is a directory on local disk; let’s say D:\SQLBackup\ If you are familiar with Ola’s backup scripts, you know the full path for backup file looks something like: D:\SQLBackup\InstanceName\DatabaseName\FULL\InstanceName_DatabaseName_FULL_yyyymmdd_hhmiss.bak Where InstanceName is a placeholder for…

TEL/電話+86 13764045638
QQ 47079569