Thursday, July 30, 2015

MySQL replication monitoring 101


Replication is the process that transfers data from an active master to a slave server, which reproduces the data stream to achieve, as best as possible, a faithful copy of the data in the master.

To check replication health, you may start with sampling the service, i.e. committing some Sentinel value in the master and retrieving it from the slave.


Sentinel data: Tap tap… Is this thing on?


If you want to make sure that replication is working, the easiest test is using replication itself to see if data is being copied across from the master to the slaves. The method is easy:


  1. Make sure that the data you want to see is NOT in the master or in the slave. If you skip this step, you may think that replication is working, while in fact it may not.
  2. Either create a table in the master or use a table that you know exists both in the master and the slave.
  3. Insert several records in the master table.
  4. Check that they are replicated in the slave correctly.
  5. Update a record in the master.
  6. Watch it changing in the slave.
  7. Delete a record in the master.
  8. Watch it disappear in the slave.

This method is important because it works regardless of the database server and replication type. Whether you are replicating out of a MySQL master to a MySQL slave using native replication, or from an Oracle server to a Vertica slave using Tungsten Replicator, or sending data across MySQL Clusters through replication, or again using multiple sources in MySQL 5.7 or MariaDB 10, this method will always tell you if replication is happening or not.


The above method is just a simple proof that replication can work. It is not, however, proof that replication works always or that it does replicate all your data.

You can’t actually prove that replication always works. But the best approximation is monitoring it so that you know when it stops doing what you want.

As for making sure that replication copies ALL your data, you can’t prove that either, but by using checksum probes, you can achieve some peace of mind.


Monitoring: Are you still there?


Definitions


Monitoring replication is the process of ensuring that the data transfer tasks between master and slave services are working as expected. Unlike the sampling process above, where you check known data in the master and the slave, monitoring works on replication metadata, which is the data produced by the services involved in replication, telling you important pieces of information regarding the task:


  • Data origin. This is the identity of the master service, which can be marked by a host name, a server ID, a service or channel name, or a combination of the above. Sometimes called data domain.
  • Data stream. This is the name of a file, or a series of files containing the data being replicated. It could be an actual file in the operating system, or a URI in a wider environment. It may also be a port number and a protocol identifier when the replication is performed through an abstract API. If replication happens with global transaction identifiers, the reference to data streams can even be omitted.
  • Service stream. In replication systems that allow replication from multiple masters, and thus several streams converging to a single host, a service stream is the data coming from a given source into a slave. Regular MySQL replication does not have such concept. Tungsten Replicator, MariaDB 10, and MySQL 5.7 can define this notion to different degrees of usefulness.
  • Data extraction positions. These positions mark the point where the master services have set data available for the slaves. In practice, from a classic MySQL master/slave replication standpoint, it is the latest point written by the master in the binary logs. In other forms of replication, it could be the latest point where the master has organized its replication metadata in a format understandable by its slaves.
  • Data apply positions. These are several positions in the data stream where the replication slave has started or terminated a given task. Such tasks could be:

    • read the data from the distant master into a temporary stream;
    • start applying the data;
    • commit the data.
  • Global transaction identifiers. A concept that is novel to MySQL replication (introduced with some use limitations in MySQL 5.6 and with less limits in MariaDB 10) but well known in other replication systems such as Tungsten. It is a way of identifying transactions independently from positions in the replication stream.
  • Stage transfer information. This is metadata specific to the stages of replication, i.e. to the steps taken to transport data from one place to another. There is very little of this metadata publicly available in MySQL replication, but in more advanced replication systems you may get information on what happens when the data is copied to the replication stream, when it is sent across the network, when it is extracted from the stream, and when it is applied. The importance of this stage data is also enhanced if replication can be modified with filters at every stage.
  • Task related information. This metadata tells the state of a given task during replication. While the stage related data deals with the steps of the replication, the task is the practical work performed to advance the progress of the data through those steps. So you may see that the replication is currently busy in the stages of replication-stream to processing-queue, and from the processing queue to the dbms. Looking at the corresponding tasks, you can get the amount of resources (memory allocation, time) by each task in the process. You may know, for example, that a given task has used 5 seconds to extract a transaction from the replication stream, and the next task has spent 1 second to apply the same data to the DBMS. If the slave is lagging at that moment, you will know that the problem is not in the speed of the database but in the network. MySQL native replication does not have this rich information on tasks as of version 5.7.
  • Shard related information. This is information that shows what action is happening in a given share of the replication process, which may or may not be split by shards. The concept of shard is volatile, and can be expanded to several things. It could be a time-based identifier that splits the data into regular chunks, or it could be a hash algorithm that maintains the data load balanced across servers, or i could be a physical boundary, such as a schema name or a table name prefix, which defines the extent of the replication work. There is little or no sharding concept in MySQL prior to MySQL 5.6.
  • Channel or thread information. When replication can run in parallel streams, the replication system should be able to detect the status of every thread in detail. Depending on the replication implementation, this information can be equivalent to the shards or to the replication service streams. Recently, MySQL 5.7 uses channel to refer to a data stream.

Operations


In MySQL master/slave topologies, monitoring means comparing metadata from the master with metadata in the slaves, to ensure that replication is running to our satisfaction.


Up to version 5.5 (but it still holds true in later versions,) monitoring replication means essentially five things:

1. Making sure that the slave is replicating from the intended master.

2. Checking that the slave is replicating from the right binary logs.

3. Checking that the data from the master is transferred to the slave.

4. Checking that the slave is applying data without errors.

4. Checking that the slave is keeping up with the master.


To achieve the above goals, we need three pieces of information.

1. We need to know who the master is

2. What the master is doing,

3. And finally what the slave is doing.


Knowing only the slave status is not enough, as the slave may be replicating from the wrong source, or from the wrong set of binary logs.


To get the master identity, we determine in which host and port it is running:


master [localhost] {msandbox} ((none)) > show variables like 'port';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| port          | 22786 |  
+---------------+-------+  
1 row in set (0.01 sec)

master [localhost] {msandbox} ((none)) > show variables like 'hostname';  
+---------------+-----------+  
| Variable_name | Value     |  
+---------------+-----------+  
| hostname      | localhost |  
+---------------+-----------+  
1 row in set (0.00 sec)

To know what the master is doing, we run SHOW MASTER STATUS:


master [localhost] {msandbox} ((none)) > show master status\G  
*************************** 1. row ***************************  
            File: mysql-bin.000003  
        Position: 5149170  
    Binlog_Do_DB:  
Binlog_Ignore_DB:  
1 row in set (0.00 sec)

Now we know that the master is running on localhost, with port 22786, and that it was last seen writing to binary log mysql-bin.000003 at position 5149170.


Armed with this information, we proceed to check the result of “SHOW SLAVE STATUS”


slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G  
*************************** 1. row ***************************  
               Slave_IO_State: Waiting for master to send event  
                  Master_Host: 127.0.0.1  
                  Master_User: rsandbox  
                  Master_Port: 22786  
                Connect_Retry: 60  
              Master_Log_File: mysql-bin.000003  
          Read_Master_Log_Pos: 5149170  
               Relay_Log_File: mysql_sandbox22787-relay-bin.000006  
                Relay_Log_Pos: 2060153  
        Relay_Master_Log_File: mysql-bin.000003  
             Slave_IO_Running: Yes  
            Slave_SQL_Running: Yes  
              Replicate_Do_DB:  
          Replicate_Ignore_DB:  
           Replicate_Do_Table:  
       Replicate_Ignore_Table:  
      Replicate_Wild_Do_Table:  
  Replicate_Wild_Ignore_Table:  
                   Last_Errno: 0  
                   Last_Error:  
                 Skip_Counter: 0  
          Exec_Master_Log_Pos: 2060007  
              Relay_Log_Space: 5149528  
              Until_Condition: None  
               Until_Log_File:  
                Until_Log_Pos: 0  
           Master_SSL_Allowed: No  
           Master_SSL_CA_File:  
           Master_SSL_CA_Path:  
              Master_SSL_Cert:  
            Master_SSL_Cipher:  
               Master_SSL_Key:  
        Seconds_Behind_Master: 2  
Master_SSL_Verify_Server_Cert: No  
                Last_IO_Errno: 0  
                Last_IO_Error:  
               Last_SQL_Errno: 0  
               Last_SQL_Error:  
  Replicate_Ignore_Server_Ids:  
             Master_Server_Id: 1

We can easily establish the master identity by checking Master_Host and Master_Port. In our case, we see that the master is 127.0.0.1, which, if you know your Latin in the Unix world, means localhost. The port is the same that the master uses. Check objective #1. The master is what it should be.


Then we check that we are replicating from the intended binary log. The master says mysql-bin.000003 and so does the slave at Master_Log_File. Here the binary log name could be different. For example, the master could say mysql-bin.000013 and the slave could still be processing mysql-bin.000006. There are several reasons for this to happen (planned maintenance, long lasting DDL operations, slow network, high traffic on the slave), but if the binary log names are inconsistent (for example, the slave is using mysql-bin.000013 while the master is using mysql-bin.000006) or if the name pattern is different (master says mysqrver.com–000012, while the slave says mysql-bin.000012) then we have a setup mismatch, and the slave is not replicating from the intended data stream. In our case, objective #2 is attained, since the names match.


To see if the data from the master has reached the slave, we first check that Slave_IO_Running is set to “Yes”, and then compare the value from Position in SHOW MASTER STATUS with the slave’s Read_Master_Log_Pos: in our case, the slave reports the same position as the master, thus certifying objective #3. This value can be lower, or the slave could still be processing a previous binary log. But replication is still considered healthy (as far as objective #3 is concerned) if these values increase continuously, i.e. if the slave keeps receiving data while the master produces it.

It’s worth mentioning that we get here some information about stage processing. MySQL replication has two stages: (1) pull from binary logs into relay logs, and (2) apply from relay logs into the database. We can get information about which binary log is currently being pulled, and to which relay log we’re writing. The exact positions of pulling and applying with reference to the master positions are not always available without inspecting the logs. Since the relay logs can rotate or be flushed at a different pace than the binary logs, monitoring the stage information with the simple metadata provided by SHOW SLAVE STATUS is not always easy or even possible.


The fourth objective is simple to check: if Slave_SQL_Running says “No” and Last_Error is non-empty, then we have an error to deal with. That’s beyond the scope of this article, and I recommend Ronald Bradford’s excellent book on MySQL replication for more information.


The fifth objective is determined by comparing again the value of Position in the master with Exec_Master_Log_Pos. In the current status, we’re behind by about 3 MB. (5149170–2060007). That could be a concern if it keeps that way. In our example, it was due to a massive data load, which was quickly absorbed. An additional check one minute later shows that all is well:


from SHOW MASTER STATUS:  
        File: mysql-bin.000003  
    Position: 168394841

from SHOW SLAVE STATUS:  
          Master_Log_File: mysql-bin.000003  
      Read_Master_Log_Pos: 168394841  
         Slave_IO_Running: Yes  
        Slave_SQL_Running: Yes  
      Exec_Master_Log_Pos: 168394841

Let me stress the importance of comparing data from master and slave. If you only have information in the slave that says it is executing position 1,000 from master binlog 134, you have no way of knowing if the slave is doing well until you see the master’s info. If the master shows that it is writing to binlog 134, you know that the slave is keeping up, but if the master mentions binlog 300, then you have a huge problem.


Latency: Are you catching up?


The data that gets in the master is the only data that matters. What’s in the slaves is just a copy that is there for your convenience, so that your applications can keep asking questions without burdening the master.

In a perfect world, data inserted in the master should be available in the slaves instantly. But reality is often different. Data can be delayed because of concurrent operations, network slowness, long lasting transactions in the slave, high query traffic, and so on.

Thus, you must assume that there is some latency between master and slave, and you need to know how bad that latency is. It could be as low as a few microseconds, or as long as minutes. Or even hours, if a slave was out for maintenance.

The way latency is measured depends on the metadata available in the replication stream. If there is no sensible metadata available, you can use a hybrid system, similar to the sentinel data method outlined above, where you insert a high resolution timestamp in the master, and compare it to a similar value in the operating system when retrieving the data from the slave. See Measuring Replication speed for a sample implementation.


In general, when metadata is available, the latency is the amount of time elapsed between the moment when the transaction was committed and when the same data is applied in the slave. By this definition, latency can vary between slaves of the same system, because of networking or performance issues.


Status persistence: That was an awful fall. Are you OK, dear?


As long as the database server is alive, we should be able to get sensible information about its functioning and the state of replication. But computers can fail, and you should expect them to fail, especially if you are running them in large numbers. With server failure seen as an everyday occurrence, it is reasonable to expect your database to come online and heal itself after a server crash. With storage engines like innodb, this expectation is legitimate, as the system is able to survive nasty crashes. What about replication data?

In MySQL, up to version 5.5, replication metadata was stored in files (master.info and relay-log.info). Since these files depend on the operating system to maintain their data, it often happens that they are out of sync with the database server, meaning that the metadata doesn’t show the latest replication position. When this happens, we have a replication inconsistency, which can result in a breakage (duplicate key insert) or data duplicate (statement-based updates applied twice).

Recent releases (MySQL 5.6, MySQL 5.7 and MariaDB 10.x) have adopted the crash-safe tables (which Tungsten Replicator has had for at least four years already) with different degrees of readiness.

Having a persistent state allows the monitoring tools to rely on the stored data and detect the health of replication after a failure.


Completeness: Did you miss anything?


In many replication system, you can add filters to reduce or modify the amount of data being replicated in one or more nodes. Filters can be grouped in four categories:


  • Extractor removal: Data is removed at the source, and it never gets in the replication stream. This happens, for example, when a master has a filter or a client-side setting that prevents an object from being replicated. This omission can be a single occurrence, where the master suspends logging for one or more events, or a permanent occurrence, where a filter removes all events affecting a given schema or table.
  • Applier removal: Data is sent to the slaves, but one or more slaves can filter off events. Again, this can happen temporarily (a slave replicates up to a given event and then skips the unwanted ones) or permanently (a filter tells the slave that some objects should not be replicated).
  • Extractor modifiers: Data is modified on the master side, before it is sent to the slaves. This kind of modification could be benign, as in the case where data is adjusted so that it could be understood by slaves running different database servers (e.g. MySQL to Oracle), or it could be destructive, meaning that its original value or shape cannot be reproduced, as in the case of an anonymizer, where sensitive data are encoded using a one-way function.
  • Applier modifiers: Same as the previous one, but the operation is performed on the slave side. Common occurrences of this filters are schema or table renames (to avoid conflicts for data coming from different sources) or format changes (e.g. date format transformation when sending data between heterogeneous servers).

We must be very cautious when using these filters. If a slave does not have all the data from the master, it won’t be a suitable replacement when the master fails. If you run replication for high availability, at least one slave should be kept online without filters.


Checksum probes: Have you got it all?


Monitoring Is not enough for the DBA’s peace of mind. We also need to make sure that all the data from the master has reached the slaves, and that the data in the slaves is a reliable copy of what we have in the master.

Sadly, there is no acknowledged standard method to achieve this goal. If your data is reasonably small, you can brute-force compare the databases with a quick data extraction in both servers followed by a few OUTER JOIN queries. But this is seldom the case. You usually want to compare data too big to fit in memory, and too big to be transferred across servers efficiently, and you also want to run the comparison without stopping the servers. The problem was outlined in an article that I wrote more than 10 years ago Taming the Distributed Database problem and which has inspired some of the tools currently available to the DBAs (pt-table-checksum is probably the most popular among them). But the tools are not easy to use, or friendly, or if they are friendly they lack one or two requirements. Since this topic is still in flux, I will resume it when the available tools reach a higher level of usefulness.


What’s next


This article covers the necessary background that will allow me to explore in more detail how replication works in the latest development in MySQL and surrounding environment, namely MySQL 5.6, MySQL 5.7, and MariaDB 10, with some sparse comparisons with Tungsten Replicator. Armed with the above definitions and examples, we can compare replication monitoring and managing capabilities for different systems.

We will specifically explore how to use GTID (global transaction IDs) in regular master/slave replication, with multiple sources and parallel replication.

Saturday, July 25, 2015

MySQL 5.7 : no more password column!

Maintaining a project like MySQL::Sandbox is sometimes tiring, but it has its advantages. One of them is that everything related to the server setup comes to my attention rather earlier than if I were an average DBA or developer.

I try to keep MySQL Sandbox up to date with every release of MySQL and (to a lesser extent) MariaDB [1]. For this reason, I am used to trying a new release with MySQL Sandbox, and … seeing it fail.

Of the latest changes in MySQL, probably the most disruptive was what happened in MySQL 5.7.6, where the mysql.user table lost the password column.

Yep. No ‘password’ column anymore. And just to make the setup procedure harder, the syntax of SET PASSWORD was changed, and deprecated.


Previously, I could run:


mysql [localhost] {msandbox} (mysql) > select version();  
+-----------+  
| version() |  
+-----------+  
| 5.6.25    |  
+-----------+  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (mysql) > select host,user,password from user;  
+-----------+-------------+-------------------------------------------+  
| host      | user        | password                                  |  
+-----------+-------------+-------------------------------------------+  
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |  
+-----------+-------------+-------------------------------------------+  
8 rows in set (0.00 sec)

In the latest releases, though, this fails.


mysql [localhost] {msandbox} (mysql) > select version();  
+-----------+  
| version() |  
+-----------+  
| 5.7.8-rc  |  
+-----------+  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (mysql) > select host,user,password from user;  
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

Instead of a password column (which was CHAR(41)), we have now an authentication_string column of type TEXT.


+-----------+-------------+-------------------------------------------+  
| host      | user        | authentication_string                     |  
+-----------+-------------+-------------------------------------------+  
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |  
| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |  
+-----------+-------------+-------------------------------------------+

Fixing MySQL Sandbox to handle this issue and to be at the same time compatible with previous releases was quite challenging, but in the end I did it. Recent versions of the sandbox can handle all the releases from Oracle, Percona, and MariaDB without showing hiccups.

So, for testing, the issue is solved. Now comes the hard part: when thousands of database administration procedures will break down for lack of the password column. To all the DBAs and database developers out there: good luck!




  1. It is my pleasure to disclose that MariaDB 10.1 runs in MySQL Sandbox 3.0.55+, with only minimal changes.  ↩


Sunday, July 19, 2015

MYSQL Sandbox 3.0.55 and new Github replication scripts


Both MySQL and MariaDB have been busy, each introducing new features, sometimes creating the same feature, often with different syntax.

This is sometimes good for users, who have a wide choice. And sometimes it is bad, as once you are used to the deployment and syntax of one flavor, it is hard to switch to a different one. This problem is enhanced if you are dealing with an application, MySQL Sandbox, that needs to work well with all flavors.

The latest releases of MySQL Sandbox (3.0.51 to 3.0.55) have been necessary to solve minor and major troublesome points with MySQL 5.7.8 and MariaDB 10.1.

The current version (3.0.55) can install all the newest releases, including replication with MySQL 5.7.8 which suffers from a compatibility bug (better explored in a separate article).

To make like easier for testers of newest versions, all replication deployments through MySQL Sandbox now include a test_replication script, which ensures that replication is working correctly. The new release also includes more tarball pattern tests, to check that known name patterns are recognized. In all, MySQL Sandbox has now about 100 tests more than before. Every time I release a new version, I run the suite with 10 or 12 versions of MySQL, Percona Server, MariaDB, for a grand total of about 5,000 tests.

And speaking of tests, there are features that require more attention than just installing a group of sandboxes, and are not easy to incorporate into MySQL Sandbox tools. For this reason, I have published on GitHub the sample scripts that I use to demonstrate multi-source replication for MySQL 5.7 and MariaDB 10. Since I was at it, I have also published the examples used for Pivot tables demos.

Tuesday, March 10, 2015

MySQL 5.7.6 is out. Be prepared for big changes



Today Oracle released MySQL 5.7.6 milestone 16. With this, MySQL 5.7 has been in development for over 2 years.
Compared to MySQL 5.6, the changes are quite extensive. The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases.
A full list of what is new would take too much space here, but I would like to mention some key points:


  • Oracle has spent a considerable amount of energy in the improvement of MySQL security and safety. You will see many new features, but even more old features that were deprecated and more that were removed after deprecation in 5.6.
  • The installation process has been changing in every milestone release of MySQL 5.7, always towards the goal of making MySQL more robust. The effort, though, will break compatibility with installation practices used for previous versions.

In this article, I will cover the biggest changes during installation.
In MySQL 5.6, mysql_install_db has an option for the generation of a random password during database creation. The process is unfriendly to scripting but it is a step in the direction of ending the long time practice of creating a root user without a password.
In MySQL 5.7.4, there was a further change, with the random password generation becoming the default, with the possibility of skipping the creation with the option –skip-random-password.
In MySQL 5.7.5, the default was confirmed, but the option was changed to –insecure.


And now, in MySQL 5.7.6, the crackdown on old practices continues: mysql_install_db is deprecated, and replaced with mysqld –initialize (formerly known as “mysqld –bootstrap,” now deprecated.)
Here’s a test run:


$ ~/opt/mysql/5.7.6/bin/mysqld --no-defaults --basedir=~/opt/mysql/5.7.6/ --datadir=$PWD/data --initialize
2015-03-09T05:06:37.159659Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-09T05:06:37.355155Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-03-09T05:06:37.410118Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-03-09T05:06:37.467002Z 0 [Warning] Failed to setup SSL
2015-03-09T05:06:37.467029Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-03-09T05:06:37.468142Z 1 [Warning] A temporary password is generated for root@localhost: f<jqhdJ(A5p#

Compared to the previous versions, the most notable difference is that there is no .mysql_secret file, but a simple line on the screen mentioning the temporary password.
But there is one, more important behavioral difference: this command works only once. When using mysql_install_db, you could run the same command even if the data directory existed, where the script would re-apply the data creation commands. Using mysqld –initialize, you can only run on a non-existing data directory.


$ ~/opt/mysql/5.7.6/bin/mysqld --no-defaults --basedir=~/opt/mysql/5.7.6/ --datadir=$PWD/data --initialize
2015-03-09T05:49:12.504413Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-09T05:49:12.505398Z 0 [ERROR] --initialize specified but the data directory exists. Aborting.
2015-03-09T05:49:12.505422Z 0 [ERROR] Aborting

Using the newly created database is a bit more trickier than before:


$ ~/opt/mysql/5.7.6/bin/mysql --no-defaults   -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.6-m16

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> set password=password('test');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password('test')' at line 1

Uh? What’s this? This command used to work until recently. The reason is a change in the syntax of SET PASSWORD, which now accepts a plain text argument:


mysql> set password='test';
Query OK, 0 rows affected (0.00 sec)

The old syntax was meant to be only deprecated, but it was accidentally completely removed. This will be hopefully fixed in MySQL 5.7.7.


More changes involve the use of GRANT, REVOKE, CREATE USER, and ALTER USER, which now are more strict. You get warnings if you try to create users with the GRANT command, or when mixing granting of privileges with authentication options.


mysql> grant all on test.* to testuser identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.

mysql> grant all on *.* to testuser identified by 'test';

Query OK, 0 rows affected, 1 warning (0.00 sec)


Warning (Code 1287): Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.

In short, if you have automated scripts that deal with installing and administering MySQL, you should test them with warnings enabled, and be prepared for broken compatibility with your old practice.


One such ‘old practice’ scripts that is broken by the new syntax changes is MySQL-Sandbox. I have just released an updated version (MySQL Sandbox 3.0.48) with a workaround for MySQL 5.7.6 changed SET PASSWORD syntax.


BTW, did I mention that the password field in the mysql.user table was removed? That’s another surprise that may break quite a lot of existing tests:


mysql> use mysql
Database changed
mysql> select host,user,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

mysql> select host, user, authentication_string from user;
+-----------+-------------+-------------------------------------------+
| host      | user        | authentication_string                     |
+-----------+-------------+-------------------------------------------+
| localhost | root        | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | msandbox    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | msandbox_rw | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| localhost | msandbox_ro | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| 127.%     | rsandbox    | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |
| %         | testuser    | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-----------+-------------+-------------------------------------------+
9 rows in set (0.00 sec)

Wednesday, June 04, 2014

MariaDB 10 is a Sandbox killjoy?

Using MySQL Sandbox I can install multiple instances of MySQL. It is not uncommon for me to run 5 or 6 instances at once, and in some occasions, I get to have even 10 of them. It is usually not a problem. But today I had an issue while testing MariaDB, for which I needed 5 instances, and I the installation failed after the 4th one. To make sure that the host could run that many servers, I tried installing 10 instances of MySQL 5.6 and 5.7. All at once, for a grand total of 20 instances:

$ make_multiple_sandbox --how_many_nodes=10 5.6.14
installing node 1
installing node 2
installing node 3
installing node 4
installing node 5
installing node 6
installing node 7
installing node 8
installing node 9
installing node 10
group directory installed in $HOME/sandboxes/multi_msb_5_6_14
$ make_multiple_sandbox --how_many_nodes=10 5.7.4
installing node 1
installing node 2
installing node 3
installing node 4
installing node 5
installing node 6
installing node 7
installing node 8
installing node 9
installing node 10
group directory installed in $HOME/sandboxes/multi_msb_5_7_4

$ ~/sandboxes/use_all 'select @@port, @@version'
# server: 1:
@@port  @@version
14015   5.6.14-log
# server: 2:
@@port  @@version
14016   5.6.14-log
# server: 3:
@@port  @@version
14017   5.6.14-log
# server: 4:
@@port  @@version
14018   5.6.14-log
# server: 5:
@@port  @@version
14019   5.6.14-log
# server: 6:
@@port  @@version
14020   5.6.14-log
# server: 7:
@@port  @@version
14021   5.6.14-log
# server: 8:
@@port  @@version
14022   5.6.14-log
# server: 9:
@@port  @@version
14023   5.6.14-log
# server: 10:
@@port  @@version
14024   5.6.14-log
# server: 1:
@@port  @@version
7975    5.7.4-m14-log
# server: 2:
@@port  @@version
7976    5.7.4-m14-log
# server: 3:
@@port  @@version
7977    5.7.4-m14-log
# server: 4:
@@port  @@version
7978    5.7.4-m14-log
# server: 5:
@@port  @@version
7979    5.7.4-m14-log
# server: 6:
@@port  @@version
7980    5.7.4-m14-log
# server: 7:
@@port  @@version
7981    5.7.4-m14-log
# server: 8:
@@port  @@version
7982    5.7.4-m14-log
# server: 9:
@@port  @@version
7983    5.7.4-m14-log
# server: 10:
@@port  @@version
7984    5.7.4-m14-log

This worked fine. Then I removed all the instances, and tried again with MariaDB

 $ sbtool -o delete -s ~/sandboxes/multi_msb_5_6_14/ 
 ...
 $ sbtool -o delete -s ~/sandboxes/multi_msb_5_7_4/
 ...

With MariaDB 10, the installation failed after the 4th node.

$ make_multiple_sandbox --how_many_nodes=10 10.0.11
installing node 1
installing node 2
installing node 3
installing node 4
error while creating grant tables
Installing MariaDB/MySQL system tables in '/home/tungsten/sandboxes/multi_msb_10_0_11/node4/data' ...
140604  8:27:14 [Note] InnoDB: Using mutexes to ref count buffer pool pages
140604  8:27:14 [Note] InnoDB: The InnoDB memory heap is disabled
140604  8:27:14 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140604  8:27:14 [Note] InnoDB: Compressed tables use zlib 1.2.3
140604  8:27:14 [Note] InnoDB: Using Linux native AIO
140604  8:27:14 [Note] InnoDB: Using CPU crc32 instructions
2014-06-04 08:27:14 7f207d353780 InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
InnoDB: Warning: io_setup() attempt 5 failed.
2014-06-04 08:27:16 7f207d353780 InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
InnoDB: Warning: Linux Native AIO disabled because os_aio_linux_create_io_ctx() failed. To get rid of this warning you can try increasing system fs.aio-max-nr to 1048576 or larger or setting innodb_use_native_aio = 0 in my.cnf
140604  8:27:16 [Note] InnoDB: Initializing buffer pool, size = 128.0M
140604  8:27:16 [Note] InnoDB: Completed initialization of buffer pool
140604  8:27:16 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
140604  8:27:16 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
140604  8:27:16 [Note] InnoDB: Database physically writes the file full: wait...
140604  8:27:16 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
140604  8:27:17 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
140604  8:27:18 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
140604  8:27:18 [Warning] InnoDB: New log files created, LSN=45781
140604  8:27:18 [Note] InnoDB: Doublewrite buffer not found: creating new
140604  8:27:18 [Note] InnoDB: Doublewrite buffer created
140604  8:27:18 [Note] InnoDB: 128 rollback segment(s) are active.
140604  8:27:19 [Warning] InnoDB: Creating foreign key constraint system tables.
140604  8:27:19 [Note] InnoDB: Foreign key constraint system tables created
140604  8:27:19 [Note] InnoDB: Creating tablespace and datafile system tables.
140604  8:27:19 [Note] InnoDB: Tablespace and datafile system tables created.
140604  8:27:19 [Note] InnoDB: Waiting for purge to start
140604  8:27:19 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.17-65.0 started; log sequence number 0
140604  8:27:24 [Note] InnoDB: FTS optimize thread exiting.
140604  8:27:24 [Note] InnoDB: Starting shutdown...
140604  8:27:25 [Note] InnoDB: Shutdown completed; log sequence number 1616697
OK
Filling help tables...
140604  8:27:25 [Note] InnoDB: Using mutexes to ref count buffer pool pages
140604  8:27:25 [Note] InnoDB: The InnoDB memory heap is disabled
140604  8:27:25 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
140604  8:27:25 [Note] InnoDB: Compressed tables use zlib 1.2.3
140604  8:27:25 [Note] InnoDB: Using Linux native AIO
140604  8:27:25 [Note] InnoDB: Using CPU crc32 instructions
2014-06-04 08:27:25 7f12bb0e9780 InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
InnoDB: Warning: io_setup() attempt 5 failed.
2014-06-04 08:27:28 7f12bb0e9780 InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
InnoDB: Warning: Linux Native AIO disabled because os_aio_linux_create_io_ctx() failed. To get rid of this warning you can try increasing system fs.aio-max-nr to 1048576 or larger or setting innodb_use_native_aio = 0 in my.cnf
140604  8:27:28 [Note] InnoDB: Initializing buffer pool, size = 128.0M
140604  8:27:28 [Note] InnoDB: Completed initialization of buffer pool
140604  8:27:28 [Note] InnoDB: Highest supported file format is Barracuda.
140604  8:27:28 [Note] InnoDB: 128 rollback segment(s) are active.
140604  8:27:28 [Note] InnoDB: Waiting for purge to start
140604  8:27:28 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.17-65.0 started; log sequence number 1616697
140604  8:27:28 [Note] InnoDB: FTS optimize thread exiting.
140604  8:27:28 [Note] InnoDB: Starting shutdown...
140604  8:27:30 [Note] InnoDB: Shutdown completed; log sequence number 1616707

This smells like a bug. BTW, the installation fails with both MariaDB 10.0.10 and 10.0.11, and only on Ubuntu Linux. I can install 10 instances just fine on Mac OSX. I haven’t tried with CentOS.

Friday, May 02, 2014

MySQL defaults evolution

MySQL, the original brand, the one developed by the MySQL team at Oracle, is steadily evolving. You can feel it if you try every new release that comes out of the milestone release cycle. Or even if you don’t try all of them, just testing a release once in a while gives you something to think about.

The engineers at Oracle are trying hard to improve the defaults. If you are the out-of-the-box type, and just install the new version on top of the previous one, leaving the same setup in place, you may be up for a for a few surprises. It’s the marketing, see? They tell you that just by replacing your old MySQL (5.1 or 5.5) with MySQL 5.6 you get 30% to 70% performance improvement. Which happens to be true, not only because the server is better, but also because they have changed the defaults. However, this change in defaults may come with some serious consequences for the ones who skip the release notes.

An annoying consequence of the MySQL team focusing on security is that in MySQL 5.6 you get a mandatory warning if you use a password in the command line. On one hand, it’s a good thing, because they force you to use better security practices. On the other hand, it’s a royal PITA, because many applications are broken because of this warning, just by replacing MySQL 5.1 or 5.5 with 5.6. There are solutions. For example, you can adopt the new mysql_config_editor to handle your password, but that would break compatibility with previous MySQL versions. Rewriting complex procedures to use configuration files instead of username and passwords is tricky, especially if you are testing exactly the behavior of using a password on the command line to override the contents of an options file.

INTERMISSION: this is a point of contention with the MySQL team. They have started a trend of introducing features that will prevent working smoothly with previous versions of MySQL. Up to MySQL 5.5, installing a server and staring using it was a set of steps that would work in the same way regardless of the version. With MySQL 5.6, all bets are over. When you install a new server, you get a long list of unwanted messages to the error output (which is fortunately fixed in MySQL 5.7), and then you get the warning if you use a password on the command line. For me, and for many developers who build software related to MySQL, the ability of writing a piece of software that works well with any version is paramount. The MySQL team seems to think that users will be happy to throw everything to the wind and start writing new code for MySQL 5.6 only instead of reusing what was working until the month before. And let’s be clear: I fully understand the need of moving forward, but I don’t understand the need of trapping users in the new behavior without remedy.

Back to the defaults. What else is new? One good improvement in MySQL 5.6 is a change in the default value for SQL_MODE. Up to MySQL 5.5, it was an empty string. In MySQL 5.6.6 and later it is ‘NO_ENGINE_SUBSTITUTION.’ Can this change have side effects? Yes. Code that worked before may break. IMO, it’s a good thing, because getting an error when trying to create a table of a non-existing engine is better than having the table created silently with the default engine. I can, however, think of at least one case where a silent substitution is desirable, as I have seen in action at a customer’s deployment. That aside, one wonders why they did not go the extra mile and add STRICT_TRANS_TABLES (or even STRICT_ALL_TABLES) to the default. It turned out that they did it… and didn’t. When you install a new data directory using mysql_install_db, the procedure creates a my.cnf file in the $BASEDIR (the place where your mysql tarball was expanded), containing the line

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

However, if you have skipped the release notes, you will be left wondering where does this additional directive come from, since the manual mentions only one of them, and SHOW VARIABLES tells you that SQL_MODE contains two values.

MySQL 5.7 has also changed something. There is a lot of cleanup going on. Options and variables that were deprecated long ago suddenly disappear. Did you ever use ‘key-buffer’ as a short for ‘key-buffer-size’? If you did, that directive in the my.cnf won’t work anymore. (I could not find it in the release notes, but the test suite for MySQL Sandbox suddenly stopped working when I tried MySQL 5.7 and then I realized what was happening.) More to the point, though, is the installation procedure. In MySQL 5.6 there is a –random-passwords option that generates a random password for the server, and you can’t do anything with root until you use such random password to access the server and change the password to something else. This is an improvement over the old and despicable root without password, which has been the default since the very beginning of MySQL, and it’s been the source of many security nightmares and interesting bugs. In MySQL 5.7.4, this behavior, i.e. the generation of a random password during the installation, is now the default. It is good news, because the old behavior was a disaster, but if you have an automated way of dealing with installation, there will be more hard work in front of you to handle the changes. The implementation is not script friendly, and definitely nightmarish if you want to install several instances of the server in the same host. What happens when you install MySQL 5.7.4? The installation software generates a random password, and writes it to a file named .mysql_secret in your $HOME directory. A sample file that was created by the installer would be:

# The random password set for the root user at Mon Mar 31 10:16:54 2014 (local time):
2X7,S4PGkIg=H(lJ


EOF

If you wanted a script to read the password generated by this procedure, it would be a mistake to look for the second line. In fact, if you repeat the installation on the same host, you get something like this:

# The random password set for the root user at Mon Mar 31 10:16:54 2014 (local time):
2X7,S4PGkIg=H(lJ


# The random password set for the root user at Tue Apr 29 09:35:07 2014 (local time):
_S07zDt7dQ=,sxw9


# The random password set for the root user at Tue Apr 29 09:42:19 2014 (local time):
r[yn4ND$-5p,4q}5


EOF

Then the correct approach would be looking for the last non empty line in the file. However, if you were installing several instances in the same host (such as MySQL Sandbox does) you wouldn’t be able to find which password belongs to which server. Admittedly, multiple instances of the same server is not what most users do, but since it breaks MySQL Sandbox, which is used by many, I mention it here. BTW, MySQL Sandbox 3.0.44 has a temporary fix for this behavior. If you install MySQL 5.7.4 or later, it will include –skip-random-passwords, and defaults to the old password-less installation. There is a hackish workaround for the above uncompromising design and I will add it to MySQL Sandbox unless the next MySQL version introduces an optional path for the .mysql_secret file.

Summing up, there are mostly good improvements from the MySQL team, although the attention to usability is still low. They are whipping users into better security. A gentler approach would be appreciated. Sometimes I see a post from the community team asking for feedback on some feature being deprecated or modified. I would welcome such requests on matters that affect the default behavior of everyday tools.

Thursday, February 13, 2014

On the road again - FOSSAsia

On the road again - FOSSAsia

It has been a few busy months until now. I have moved from Italy to Thailand, and the move has been my first priority, keeping me from attending FOSDEM and interacting with social media. Now I start catching my breath, and looking around for new events to attend. But before I get into this, let’s make a few things clear:

  • I am still working for Continuent. Actually, it’s because of my company flexibility that I could move to a different country (a different continent, 6 time zones away) without much trouble. Thanks, Continuent! (BTW: Continuent is hiring! )
  • I am still involved with MySQL activities, events, and community matters. I just happen to be in a different time zone, where direct talk with people in Europe and US need to happen on a different schedule.

I am already committed to attend Percona Live MySQL Conference & Expo in Santa Clara, where I will present a tutorial on MySQL replication features and a regular session on multi-master topologies with Tungsten.

But in the meantime, Colin encouraged me to submit talk proposals at FOSSAsia, and both my submissions were accepted. So, at the end of February I will be talking about some of my favorite topics:

  • Easy MySQL multi master replication with Tungsten
  • Data in the cloud: mastering the ephemeral

The exact schedule will be announced shortly. I am eager to attend an open source event in Asia. It’s been a long time since I went to a similar event in Malaysia, which was much pleasant.

Thursday, January 16, 2014

PerconaLive 2014 program is published

PerconaLive 2014 program is published

Percona Live MySQL Conference and Expo, April 1-4, 2014

After a few months of submissions and reviews, the program for PerconaLive MySQL Conference and Expo 2014 is published. The conference will be held in Santa Clara, from April 1 to 4, 2014.

Registration with early bird discount is available until February 2nd. If you plan to attend, this is probably the best time to act.

I will be presenting twice at the conference:

Notice that the Call for Participation is still open for lightning talks and BoF. You can submit a talk until the end of January.

Tuesday, January 07, 2014

Multiple masters : attraction to the stars

In the last 10 years I have worked a lot with replication systems, and I have developed a keen interest in the topic of multiple masters in a single cluster. My interest has a two distinct origins:

  • On one hand, I have interacted countless times with users who want to use a replication system as a drop-in replacement for a single server. In many cases, especially when users are dealing with applications that are not much flexible or modular, this means that the replication system must have several points of data entry, and such points must work independently and in symbiosis with the rest of the nodes.
  • On the other hand, I am a technology lover (look it up in the dictionary: it is spelled geek), and as such I get my curiosity stirred whenever I discover a new possibility of implementing multi-master systems.

The double nature of this professional curiosity makes me sometimes forget that the ultimate goal of technology is to improve the users life. I may fall in love with a cute design or a clever implementation of an idea, but that cleverness must eventually meet with usability, or else it loses its appeal. There are areas where the distinction between usefulness and cleverness is clear cut. And there are others where we really don’t know where we stand because there are so many variables involved.

One of such cases is a star topology, where you have many master nodes, which are connected to each other through a hub. You can consider it a bi-directional master/slave. If you take a master/slave topology, and make every node able to replicate back to the master, then you have almost a star. To make it complete, you also need to add the ability of the master of broadcasting the changes received from the outside nodes, so that every node gets the changes from every other node. Compared to other popular topologies, say point-to-point all-masters, and circular replication, the star topology has the distinct advantage of requiring less connections, and of making it very easy to add a new node.

Star

Figure #1: Star topology

However, anyone can see immediately one disadvantage of the star topology: the hub is the cornerstone of the cluster. It’s a single point of failure (SPOF). If the hub fails, there is no replication anywhere. Period. Therefore, when you are considering a multi-master topology, you have to weigh in the advantages and disadvantages of the star, and usually you consider the SPOF as the most important element to consider.

Depending on which technology you choose, though, there is also another important element to consider, i.e. that data must be replicated twice when you use a star topology. It’s mostly the same thing that happens in a circular replication. If you have nodes A, B, C, and D, and you write data in A, the data is replicated three times before it reaches D (A->B, B->C, and C->D). A star topology is similar. In a system where A, B, and D are terminal nodes, and C is the hub, data needs to travel twice before it reaches D (A->C, C->D). Circular replication

Figure #2: Circular replication

This double transfer is bad for two reasons: it affects performance, and it opens to the risk of unexpected transformations of data. Let’s explore this concept a bit. When we replicate data from a master to a slave, there is little risk of mischief. The data goes from the source to a reproducer. If we use row-based-replication, there is little risk of getting the wrong data in the slave. If we make the slave replicate to a further slave, we need to apply the data, generate a further binary log in the slave host, and replicate data from that second binary log. We can deal with that, but at the price of taking into account more details, like where the data came from, when to stop replicating in a loop, whether the data was created with a given configuration set, and so on. In short, if your slave server has been configured differently from the master, chances are that the data down the line may be different. In a star topology, this translates into the possibility of data in each spoke to be replicated correctly in the hub, but to be possibly different in the other spokes.

Compare this with a point-to-point all-masters. In this topology, there are no SPOFs. You pay for this privilege by having to set a higher number of connections between nodes (every node must connect to every other node), but there is no second hand replication. Before being applied to the slave service, the data is applied only once in the originating master.

Point to point all masters

Figure #2: Point-to-point all-masters topology

Where do I want to go from all the above points? I have reached the conclusion that, much as user like star topologies, because of their simplicity, I find myself often recommending the more complex but more solid point-t-point all-masters setup. Admittedly, the risk of data corruption is minimal. The real spoiler in most scenarios is performance. When users realize that the same load will flow effortlessly in a point-to-point scenario, but cause slave lags in a star topology, then the choice is easy to make. If you use row-based replication, and in a complex topology it is often a necessary requirement, the lag grows to a point where it becomes unbearable.

As I said in the beginning, all depends on the use case: if the data load is not too big, a star topology will run just as fine as point-to-point, and if the data flow is well designed, the risk of bad data transformation becomes negligible. Yet, the full extent of star topologies weaknesses must be taken into account when designing a new system. Sometimes, investing some effort into deploying a point-to-point all-masters topology pays off in the medium to long term. Of course, you can prove that only if you deploy a star and try it out with the same load. If you deploy it on a staging environment, no harm is done. If you deploy in production, then you may regret. In the end, it all boils down to my mantra: don’t trust the theory, but test, test, test.

Thursday, December 12, 2013

Quick and dirty concurrent operations from the shell

Let’s say that you want to measure something in your database, and for that you need several operations to happen in parallel. If you have a capable programming language at your disposal (Perl, Python, Ruby, PHP, or Java would fit the bill) you can code a test that sends several transactions in parallel.

But if all you have is the shell and the mysql client, things can be trickier. Today I needed such a parallel result, and I only had mysql and bash to accomplish the task.

In the shell, it’s easy to run a loop:

for N in $(seq 1 10)
do
    mysql -h host1 -e "insert into sometable values($N)" 
done

But this does run queries sequentially, and each session will open and close before the next one starts. Therefore there is no concurrency at all.
Then I thought that the method for parallel execution in the shell is to run things in the background, and then collect the results. I just needed to find a way of keeping the first session open while the others are being started.

Here’s what I did: I ran a loop with a countdown, using the seq command, and I included a sleep statement in each query, waiting for a decreasing amount of seconds. If I start with 10 seconds, the first query will sleep for 10 seconds, the second one for 9 seconds, and so on. I will run each command in the background, so they will eat up the time independently.

#!/bin/bash
mysql -h host1 test -e 'drop table if exists t1'
mysql -h host1 test -e 'create table t1 (i int not null primary key, ts timestamp)'

for N in $(seq 10 -1 1)
do
    query1="set autocommit=0"
    query2="insert into test.t1 (i) values($N)"
    query3="select sleep($N) into @a; commit"
    mysql -h host1 -e "$query1;$query2;$query3" &
done

wait

mysql -h host1 test -e 'select * from t1'

The effect of this small script is that the commit for these 10 commands come at the same time, as you can see from the resulting table:

+----+---------------------+
| i  | ts                  |
+----+---------------------+
|  1 | 2013-12-12 18:08:00 |
|  2 | 2013-12-12 18:08:00 |
|  3 | 2013-12-12 18:08:00 |
|  4 | 2013-12-12 18:08:00 |
|  5 | 2013-12-12 18:08:00 |
|  6 | 2013-12-12 18:08:00 |
|  7 | 2013-12-12 18:08:00 |
|  8 | 2013-12-12 18:08:00 |
|  9 | 2013-12-12 18:08:00 |
| 10 | 2013-12-12 18:08:00 |
+----+---------------------+

This is a very good result, but what happens if I need to run 500 queries simultaneously, instead of 10? I don’t want to wait 500 seconds (8+ minutes). So I made an improvement:

for N in $(seq 5000 -10 1)
do
    echo $N
    query1="set autocommit=0"
    query2="insert into test.t1 (i) values($N)"
    query3="select sleep(concat('0.', lpad($N,4,'0'))) into @a; commit"
    mysql -h host1 -e "$query1;$query2;$query3" &
done

Now each SLEEP command is called with a fractional argument, starting at “0.5000”, and continuing with “0.4999,” and so on. You can try it. All 500 rows are committed at the same time.

However, the same time is a bit fuzzy. When we use timestamps with second granularity, it’s quite easy to show the same time. But with microseconds it’s a different story. Here’s what happens if I use MySQL 5.6 with timestamp columns using microseconds (TIMESTAMP(3)):

+----+-------------------------+
| i  | ts                      |
+----+-------------------------+
|  1 | 2013-12-12 18:27:24.070 |
|  2 | 2013-12-12 18:27:24.070 |
|  3 | 2013-12-12 18:27:24.069 |
|  4 | 2013-12-12 18:27:24.068 |
|  5 | 2013-12-12 18:27:24.065 |
|  6 | 2013-12-12 18:27:24.066 |
|  7 | 2013-12-12 18:27:24.062 |
|  8 | 2013-12-12 18:27:24.064 |
|  9 | 2013-12-12 18:27:24.064 |
| 10 | 2013-12-12 18:27:24.064 |
+----+-------------------------+

For the purpose of my tests (the actual queries were different) this is not an issue. Your mileage may vary.

Vote on Planet MySQL