Tuesday, August 04, 2015

Yet another MySQL 5.7 silent change

When a new version of MySQL appears, the first source of information for the brave experimenter is a page in the manual named What is new in MySQL X.X, also known as MySQL in a nutshell. For MySQL 5.7, the in-a-nutshell page lists quite a lot of changes. In that page, the list of removed features is enough to send a chill down the spine of most any DBA. Some of the items in the deprecation section are removals in disguise, as they require immediate, rather than delayed, action to use the new version with existing application (SET PASSWORD comes to mind immediately.)


In all that abundance of changes, there is at least one that may cause huge discomfort to many users:


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

mysql [localhost] {msandbox} (information_schema) > select * from GLOBAL_STATUS;  
Empty set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (information_schema) > show warnings\G  
*************************** 1. row ***************************  
  Level: Warning  
   Code: 1287  
Message: 'INFORMATION_SCHEMA.GLOBAL_STATUS' is deprecated and will be removed in  
a future release. Please use performance_schema.global_status instead  
1 row in set (0.00 sec)

Did I miss a deprecation information in MySQL 5.6? Apparently not. The manual says that information_schema.GLOBAL_STATUS is deprecated as of 5.7.6 (and so are GLOBAL_VARIABLES, SESSION_STATUS, and SESSION_VARIABLES).

Until recently, a deprecation notice used to mean that the feature would be removed in a future version, but it remained in place, so I could get organized to adapt my procedures to the recommended changes. Therefore I would expect that a deprecation in 5.7 would become a removal in 5.8, and a removal in 5.7 would mean that the feature had been deprecated in a previous release. But in this case, the deprecation is effectively killing the feature today. It’s no use to me if the information_schema.GLOBAL_VARIABLES is still there when it does not return results. This change can break lots of procedures that check the %_STATUS and %_VARIABLES tables to see if a given variable is available or not. For example, after enabling the semi-sync replication plugin, we want to check if the procedure was successful:


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

master [localhost] {msandbox} ((none)) > select * from information_schema.global_variables where variable_name='rpl_semi_sync_master_enabled';  
+------------------------------+----------------+  
| VARIABLE_NAME                | VARIABLE_VALUE |  
+------------------------------+----------------+  
| RPL_SEMI_SYNC_MASTER_ENABLED | ON             |  
+------------------------------+----------------+  
1 row in set (0.00 sec)

The same query does not give us what we expect in MySQL 5.7.8.


master [localhost] {msandbox} ((none)) > select * from information_schema.global_variables where variable_name='rpl_semi_sync_master_enabled';  
Empty set, 1 warning (0.00 sec)

master [localhost] {msandbox} ((none)) > show warnings\G  
*************************** 1. row ***************************  
  Level: Warning  
   Code: 1287  
Message: 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' is deprecated and will be removed in a future release. Please use performance_schema.global_variables instead  
1 row in set (0.00 sec)

When we query the new table, we get it.


master [localhost] {msandbox} ((none)) > select * from performance_schema.global_variables where variable_name='rpl_semi_sync_master_enabled';  
+------------------------------+----------------+  
| VARIABLE_NAME                | VARIABLE_VALUE |  
+------------------------------+----------------+  
| rpl_semi_sync_master_enabled | ON             |  
+------------------------------+----------------+  
1 row in set (0.00 sec)

However, if we had an automated procedure that was querying the information_schema table for this variable, the result of the operation would suggest that the plugin had not been installed yet or that the installation had failed.


If this is a true deprecation, the old table should continue working. If it is a change like removing the password column or changing the syntax of SET PASSWORD, users would be better off with a complete removal of the offending table. In that case, at least they would be notified of the problem and fix it. As it is now, they may waste time trying to find the cause of incomprehensible failures such as Bug#77732.

Monday, August 03, 2015

MySQL 5.7.8 : features, bugs and rumors

I’ve had a look at a preview release of MySQL 5.7.8, some time before it became available to the general public (perks and duties of an Oracle ACE) and I found a few interesting things among the release notes and the tarball itself:


  • There is a new tool named mysqlpump, which is intended as a replacement for mysqldump, with parallel processing, compression, progress watch, the long awaited ability of excluding databases or tables, and more.
  • The json functionality has been fished out from the labs and added to the main distribution.

I was initially pleased with this preview when it installed without errors in MySQL Sandbox, but my pleasure didn’t last long, as I soon stumbled upon a bug that affects replication. Until MySQL 5.7.7, a user with just REPLICATION SLAVE privileges can activate replication. In MySQL 5.7.8, though, replication fails with a baffling error:


slave1 [localhost] {msandbox} ((none)) > show slave status\G
[...]
            Last_IO_Errno: 1142
            Last_IO_Error: The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master. Error: SELECT command denied to user 'rsandbox'@'localhost' for table 'global_variables'

This error is baffling for three reasons:


  1. It has never been an issue for a user with this privilege to connect to the master;
  2. the user rsandbox can actually see information_schema.global_variables, which I originally thought was the table been denied;
  3. the above table is empty, because – see below – it was replaced by an homonymous table in performance_schema.

While I don’t understand how this bug could have been missed in the test suite, I believe the fix should not be too hard, and I hope the bug will be closed in the next release.
For the inquisitive readers, the reason for the bug is a changed default value for a compatibility variable. As of MySQL 5.7.6, system and status variables are available through performance_schema tables. There is a variable that controls the compatibility with previous behavior: show_compatibility_56. Up until MySQL 5.7.7, this switch was ON. In the latest version, the switch is OFF. This means that users that don’t have SELECT access to the global_variables table can’t run ‘SHOW VARIABLES …’ commands. This seems logical, but the restriction clashes with the basic requirements for replication, where the only privilege needed for the IO thread user is REPLICATION SLAVE.


So, something has to give. Either we change the requirements or we soften the restrictions. The current workaround, keeping show_compatibility_56=NO in the master would make the adoption of performance_schema features more difficult. Perhaps the best solution would be to change the IO thread user behavior, which should run ‘SELECT @@SERVER_ID’ instead of ‘SHOW VARIABLES LIKE “SERVER_ID”’. This is feasible, assuming that ‘SELECT @@VARIABLE_NAME’ will not be tied to the performance table as well.
And thinking about this workaround, the restriction on SHOW VARIABLES makes little sense, as it can be circumvented by running ‘SELECT @@VARIABLE_NAME’, thus making the restriction itself quite ineffective.


If you are planning to give MySQL 5.7 a try, be aware that there are lots of deprecated features, and at least one that should break lots of existing procedures but is not in the official list, as it is not, strictly speaking, a deprecation. The "password" column in mysql.user is gone. Instead, you will find an "authentication_string" column. Good luck!


And now come the rumors: I was reading the comments on the bug progress through the verification process, and I noticed that the bug has been confirmed for at least two new versions: MySQL 5.7.9 (no surprises here, as this is already mentioned in the release notes page) and MySQL 5.8.0-m17-enterprise-commercial-advanced-log of which, until this bug, we did not know anything about.
The latest milestone release, before the RC, was m16, so 5.8.0 seems to be the next incarnation right after 5.7 will be GA (it should be 5.7.9 or 5.7.10.) But this is only a rumor, so the next version could just as well be called MySQL 12g, to align it with its older stepbrother. (And this is really just a wild rumor!)


In some related news, MySQL Sandbox was updated with a workaround for the bug mentioned above. Moreover, it uses a different way of installing MySQL: instead of the now deprecated mysql_install_db, it uses mysqld --initialize.
If you want to use MySQL 5.7.8, please download the latest version (MySQL::Sandbox 3.0.62.)

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.

Vote on Planet MySQL