Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Archive for the month “August, 2012”

Deprecated in MySQL 5.6 – ignore_builtin_innodb

Back when MySQL 5.1 was first released, Oracle (which didn’t at the time own Sun or MySQL) wanted to add new InnoDB functionality faster than MySQL could effectively incorporate it into the server, and the InnoDB plugin was introduced.  It provided 5.1 users the option of deploying a more advanced version of InnoDB, or using the more conservative built-in version baked into MySQL.  By the time the plugin reached GA status, though, Oracle had announced the acquisition of Sun (and MySQL), and things started to change.  The coordination between the InnoDB development team at Oracle and the MySQL development team increased once we were all “under one roof”.  The strategic directions of InnoDB and MySQL were aligned, and the results can be seen in the tremendous work done for MySQL 5.5 and 5.6.  In fact, the InnoDB plugin was removed with MySQL 5.5:

The built-in InnoDB storage engine within MySQL is the original form of distribution for the storage engine. Contrast with the InnoDB Plugin. Starting with MySQL 5.5, the InnoDB Plugin is merged back into the MySQL code base as the built-in InnoDB storage engine (known as InnoDB 1.1).

This distinction is important mainly in MySQL 5.1, where a feature or bug fix might apply to the InnoDB Plugin but not the built-in InnoDB, or vice versa.

One artifact of the “two different InnoDB engines” introduced with the plugin remains:  the ignore_builtin_innodb configuration option.  As of 5.6.5 and later, that option is now deprecated, and has no effect:

D:\mysql-5.6.5-m8-win32>bin\mysqld –no-defaults –console –port=3307 –ignore-builtin-innodb
120531 10:54:30 [Warning] ignore-builtin-innodb is ignored and will be removed in future releases.

Since MySQL 5.5, we’ve focused on and invested in InnoDB as a core element and default storage engine for MySQL.  As such, in 5.5, we removed the InnoDB plugin.  And now with 5.6, we are eliminating the option to disable InnoDB.

 

Learn From MySQL Support Staff at MySQL Connect!

Members of the MySQL Support Team wear a number of different hats here at Oracle.  Obviously, our top priority is to provide amazing technical support that makes customers rave.  We also have a team dedicated to processing bug reports from the MySQL Community.  Some of us are active bloggers or assist on mailing lists or forums, while others find other ways to contribute to the MySQL Community.  We help out with QA and product planning, write books, and the consultative support aspects of MySQL subscriptions allow us the opportunity to help train and advise customers on best practices and successful MySQL-backed application architecture.  In short, we’re deeply invested in making sure that MySQL deployments are successful.

This year, we’re privileged to send some of these great MySQL experts to the inaugural MySQL Connect Conference this September 29-30, and it will be a great opportunity for you to experience first-hand the value that MySQL Support can provide:

Be sure to stop by the MySQL Support booth in the demo grounds at MySQL Connect and meet these MySQL experts – they’ll be happy to help you solve any of your tricky MySQL problems.

If you’re an Oracle DBA just getting started on MySQL, it’s worth the $100 fee to add MySQL Connect to your OOW registration, but Ben Krug will also be leading a session at OOW with the goal of Demystifying MySQL for Oracle DBAs and Developers.

And if California is just too far to travel, perhaps you can catch Uma Bhat‘s presentation at PyCon India, Introduction to MySQL Connector/Python.

We hope to see you at one of these upcoming conferences!

Load-balanced JDBC Tip for GlassFish Deployments

Having supported a number of successful load-balanced JDBC applications using MySQL Cluster and MySQL Connector/J over the years, I’ve found a few problems that are unique to specific Java app servers.  A recent customer inquiry reminded me of a GlassFish-specific issue, and the Connector/J connection property we introduced to help solve it.  I thought it might be useful to document this here for any GlassFish users looking to deploy a load-balanced JDBC application with MySQL (Cluster or multi-master replication).

If you’re entirely new to the load-balancing functionality in MySQL Connector/J, you may want to review some earlier posts.  In particular, it’s important to understand how a load-balanced Connector/J (logical) Connection object maintains multiple physical connections (small “c”) – up to one for each load-balanced server.  When the driver determines it’s appropriate to re-balance the connection, a new host is chosen, and if a physical connection to that server already exists within the Connection object, it will be reused, rather than creating a new physical connection.  Because we’re maintaining multiple physical connections, it’s important that the connection validation process used by the application server trigger validation of all underlying physical connections – not just the currently-active physical connection.  In Connector/J, load-balanced Connection objects will validate all underlying physical connections when a query starting with “/* ping */” (exactly) is encountered.

And this is where GlassFish users sometimes have problems – it can be difficult (or impossible, in some versions) to define a custom validation query that starts with “/* ping */” to trigger the necessary validation of all physical connections.  Here’s the scenario that proves most problematic:

  1. The application gets a Connection object from the connection pool, and the Server A is the currently-active server to which traffic is routed by the Connection object.
  2. The application issues commit(), which triggers re-balance, and Server B is made active.  The physical connection to Server A is retained.
  3. The application returns the Connection to the connection pool.
  4. The connection pool periodically validates the Connection using “SELECT 1″ – a query that is executed against the currently-active physical connection to Server B.  The physical connection to Server A remains idle, and eventually dies (unnoticed).
  5. The application is given this same Connection from the connection pool later, perhaps even validates it on check-out (again with “SELECT 1″), and uses it successfully.
  6. The application issues commit(), triggering re-balance again, and Server A is selected and made active.
  7. The application tries to use the Connection again, and it fails due to CommunicationException, as the physical connection to Server A has been left idle too long.

Explicitly in support of users of GlassFish or other JDBC connection pools where the connection validation query cannot be set to start with “/* ping */”, we added the loadBalanceValidateConnectionOnSwapServer connection property starting in MySQL Connector/J version 5.1.11.  In the example above, this affects step #6.  When the Connection re-balances and chooses a new physical connection, that physical connection is explicitly validated before returning control to the application.  Thus, when the application tries to use the Connection object again in step #7, it should find a valid Connection.

Having confidence that the Connection won’t choose an idle physical connection at re-balance sounds good, right?  So why not make this the default behavior?

Well, this adds some overhead to re-balance operations – in particular, commit().  If you have a JDBC connection pool that can maintain load-balanced connections adequately using the /*ping */ connection validation query, checking the physical connection at each commit() can be unwelcome overhead.  It also doesn’t guarantee the Connection won’t throw a CommunicationException – with applications connecting over a network, a disconnect is possible at any time.

Hopefully this tip will help users of GlassFish (and other JDBC connection pools where validation queries cannot be specified) be more successful with load-balanced MySQL deployments.

Identifying and killing blocking transactions in InnoDB

The MySQL Server has a few options to help deal with transactions holding InnoDB locks for excessive periods of time.  The –innodb-lock-wait-timeout option is one such option, but that just affects statements waiting on locks already held by another transaction.  If you want to ensure your application isn’t holding locks for long periods of time to start with, what options do you have?

Well, the manual has a useful example that leverages InnoDB tables in INFORMATION_SCHEMA to show which transactions are locking rows needed by other transactions.  That’s cool stuff, and you can take this a step further by looking for all connections with open transactions which exceed a given threshold or are blocking other transactions:

mysql> SET @threshold = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT p.user,
->      LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id,
->      TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
->      COUNT(DISTINCT ot.REQUESTING_TRX_ID) waiting
->    FROM INFORMATION_SCHEMA.INNODB_TRX t
->    JOIN INFORMATION_SCHEMA.PROCESSLIST p
->      ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
->    LEFT JOIN INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot
->      ON ( ot.BLOCKING_TRX_ID = t.TRX_id )
->    WHERE t.TRX_STARTED + INTERVAL @threshold SECOND <= NOW()
->    GROUP BY LEFT(p.HOST, LOCATE(':', p.HOST) - 1), p.id, duration
->    HAVING duration >= @threshold OR waiting > 0;
+------+-----------+----+----------+---------+
| user | host      | id | duration | waiting |
+------+-----------+----+----------+---------+
| root | localhost |  4 |     1062 |       0 |
| ODBC | localhost |  5 |      276 |       0 |
+------+-----------+----+----------+---------+
2 rows in set (0.08 sec)

The next step you can take is to create an EVENT which logs this, or even issues KILL CONNECTION (sadly, there is no KILL TRANSACTION, and KILL QUERY doesn’t rollback the transaction) commands on the blocking transaction.  You can also create control tables that allow you to fine-tune reporting or action thresholds for specific user/host combinations:

CREATE TABLE test.innodb_trx_kill_threshold (
 user VARCHAR(100) NOT NULL,
 host VARCHAR(100) NOT NULL,
 trx_kill_threshold INT UNSIGNED,
 PRIMARY KEY (user, host)
) ENGINE = InnoDB;

CREATE TABLE test.innodb_trx_kill_audit (
 id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 user VARCHAR(100) NOT NULL,
 host VARCHAR(100) NOT NULL,
 connection_id INT UNSIGNED NOT NULL,
 trx_duration INT UNSIGNED NOT NULL,
 other_trx_waiting INT UNSIGNED NOT NULL DEFAULT 0,
 kill_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY (user, host, connection_id)
) ENGINE = InnoDB;

CREATE OR REPLACE VIEW test.innodb_trx_past_threshhold AS
SELECT p.user, 
     LEFT(p.HOST, LOCATE(':', p.HOST) - 1) host, p.id, 
     TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) duration,
     IFNULL(kt.trx_kill_threshold, 30) threshold, 
     (SELECT COUNT(ot.REQUESTING_TRX_ID) 
       FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS ot 
       WHERE ot.BLOCKING_TRX_ID = t.TRX_id) waiting
   FROM INFORMATION_SCHEMA.INNODB_TRX t 
   JOIN INFORMATION_SCHEMA.PROCESSLIST p 
     ON ( p.ID = t.TRX_MYSQL_THREAD_ID )
   LEFT JOIN test.innodb_trx_kill_threshold kt 
     ON ( p.USER = kt.user AND LEFT(p.HOST, LOCATE(':', p.HOST) - 1) = kt.host )
   WHERE t.TRX_STARTED + INTERVAL IFNULL(kt.trx_kill_threshold, 30) SECOND  0 ;

DELIMITER ||
CREATE PROCEDURE kill_long_innodb_trx (IN kill_connections INT)
 BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE l_user, l_host VARCHAR(100);
 DECLARE l_timeout, l_duration, l_conn_id, l_other_trx_waiting INT UNSIGNED;
 DECLARE cur CURSOR FOR 
   SELECT user, host, id, duration, threshold, waiting 
   FROM test.innodb_trx_past_threshhold;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 OPEN cur;

 read_loop: LOOP
  FETCH cur INTO l_user, l_host, l_conn_id, l_duration, l_timeout, l_other_trx_waiting;
  IF done THEN
    LEAVE read_loop;
  END IF;
  INSERT INTO test.innodb_trx_kill_audit
    ( user, host, connection_id, trx_duration, other_trx_waiting)
      VALUES
    ( l_user, l_host, l_conn_id, l_duration, l_other_trx_waiting )
    ON DUPLICATE KEY UPDATE trx_duration = l_duration, 
      other_trx_waiting = GREATEST(other_trx_waiting, l_other_trx_waiting);
  IF kill_connections THEN
    SET @stmt = CONCAT('KILL CONNECTION ', l_conn_id);
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
  END IF;

 END LOOP;

 CLOSE cur;

 END;
||
DELIMITER ;

CREATE EVENT innodb_trx_killer ON SCHEDULE EVERY 5 SECOND DO CALL kill_long_innodb_trx (1);

And now there’s an EVENT running every 5 seconds which will kill connections having InnoDB transactions older than the threshold (30s, unless configured differently for the user/host combination in the control table).  It will also log information to an audit table (test.innodb_trx_kill_audit) about the connections before they are killed.  One could easily tweak the stored procedure to simply record the audit details instead of killing connections.  Here’s an example of this in action:

mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO p VALUES (2, 3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT SLEEP(35);
+-----------+
| SLEEP(35) |
+-----------+
|         0 |
+-----------+
1 row in set (35.00 sec)

mysql> COMMIT;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

And the audit table:

mysql> SELECT * FROM test.innodb_trx_kill_audit\G
*************************** 1. row ***************************
               id: 1
             user: ODBC
             host: localhost
    connection_id: 157
     trx_duration: 31
other_trx_waiting: 0
        kill_time: 2012-08-28 13:08:07
1 row in set (0.00 sec)

Note:  It’s been pointed out to me that there are a few similar, earlier suggestions on how to do this, or something similar.

Understanding mysql_config_editor’s security aspects

The recent release of 5.6.6 includes a new utility, mysql_config_editor, which makes it easier to interact with MySQL clients securely.  At the same time, it’s easy to overstate the security benefits of using this new tool, and unfortunately, I think a couple of statements in the release notes and documentation go a step too far (something we’re in the process of correcting).  Ronald quoted one of them in his blog:

MySQL now provides a method for storing authentication credentials securely in an option file named .mylogin.cnf.

This enhancement really isn’t about securing passwords at a file-system level.  Don’t assume that the encryption used will prevent attackers from getting passwords stored in the .mylogin.cnf file if they have read access to it – it absolutely will not.  There are multiple ways to extract the password out of this file, some more complex than others.  Don’t make the mistake of believing that the password storage is safe just because the contents are encrypted.

So, if this enhancement isn’t about secure storing of passwords, what is it about?  It makes secure access via MySQL client applications easier to use.  For example, it helps eliminate the need to include a password as an argument to mysql or other command-line clients:

shell>  mysql -hproduction -uroot -pmypass

becomes

shell> mysql --login-path=production

(assuming we used mysql_config_editor to set up a “production” configuration)

That’s obviously more secure if somebody can view the running processes – you don’t want to expose your password like that.   Of course, you could always just include the -p argument and enter the password interactively (that doesn’t echo it to the screen or display it in any process list.  But that doesn’t help with scripts or batch jobs where interactive password entry isn’t an option.  So, great – this enhancement helps eliminate the need for including the password where it can’t be entered interactively.  Sounds great – but this mechanism has long existed in MySQL through the use of configuration files (and you can have as many different configuration files as you want dedicated to various connections).  You can easily put the password under the [client] header, and invoke clients like so:

shell> mysql --defaults-file=~/production.cnf

Again, assuming there’s ~/production.cnf file that has the connection attributes needed defined in the [client] section.

So, if it’s already possible to eliminate passwords for both interactive and batch use cases, why is this needed?  The most immediate answer is that this makes it easier to get password storage – when you do it – right.  In the above example with the production.cnf file, you have to create that file yourself and ensure that it has appropriate privileges assigned.  That’s not the case when you use mysql_config_utility.  Quoting from the manual:

The login file must be readable and writable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and the file is not used by client programs, either.

So one of the principal benefits of mysql_config_editor is ease-of-use – you can rely on it to store your password in a file that is not readable by other users.  That’s not to say that you could not have done exactly the same thing yourself by setting appropriate file permissions on a .cnf file of your own making, but using the tool eliminates the possibility that it gets overlooked.  Note that I believe that this aspect of the feature may not be working properly for Windows at this stage, and I’m in the process of validating and reporting a bug on this.

Now you may be wondering:  If the file encryption doesn’t protect against attackers having file system access, what does it protect against?  It eliminates a password storage location that might be misused by a user in a fit of urgency.  If a user can’t get access (their own) plain-text passwords from .mylogin.cnf, it may throw just one more hurdle preventing passwords from being copied and pasted into other files (which would have the same need for file system restrictions) or command-line parameters.  In short, it reduces the possibility that users may unintentionally use their own passwords in an insecure manner.

All that said, the feature does have some nice ease-of-use aspects that aren’t explicitly security-related.  For example, you don’t even have to store passwords using the utility, and use it as a collection of all your hard-to-remember server locations, and still enter the password interactively:

shell> bin\mysql_config_editor print --all
[slave1]
host = some.really.obscure.slave.host
[slave2]
host = another.really.obscure.slave.host

shell> bin\mysql --login-path=slave1 --user=something -p
Enter password: ****

The options you specify as client application arguments will overwrite whatever is stored in .mylogin.pass:

shell> bin\mysql --login-path=slave1 --host=localhost --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5

So, if you do choose to store passwords in your .mylogin.cnf file, and you use the same password for multiple accounts (look at me advocating security), you can have a single entry that remembers your password, and choose the host as an argument to the client.

This can be a useful enhancement for very specific security requirements and helps make it easier to be more secure in handling passwords as well as connections to multiple servers.  At the same time, it should not be confused with a mechanism that prevents attackers from acquiring passwords if they have file-system level access.

 

Bye-bye .MSI (Hello MySQL Installer)

Windows users of MySQL may find something missing when they first download MySQL 5.6.6.  The .MSI package of MySQL for Windows has been eliminated in favor of the MySQL Installer for Windows.  You can still download MySQL in .ZIP format (as well as a source code .ZIP for the GPL-licensed Community edition), but the quickest way to get running MySQL on Windows is the MySQL Installer.  You can download the version of MySQL Installer that includes MySQL 5.6.6 on dev.mysql.com.

The MySQL Installer has a number of improvements over the old .MSI installer and configuration wizard.  Running the 5.6.6 installer on my machine, it detected that ports 3306 and 3307 were already used by other MySQL installations, and suggested port 3308 for the new installation.  It also recognized conflicts with existing Windows service names, and suggests variants that don’t conflict (e.g., suggesting “MySQL56″ when “MySQL” is already taken, and “MySQL56_1″ when both are taken).  It smartly helps you set an appropriately-strong password for the root account, as well as the option to add new user accounts during initial configuration.  Additionally, you can install (or update) tools that Windows users will find helpful, including MySQL Workbench and the new MySQL Notifier and MySQL for Excel products, as well as various connectors and documentation – all from one interface.

The new installer is roughly the same size as MySQL Server .ZIP downloads (about 200MB).  The package includes MySQL Server, and downloads other products on-demand.  It will also help you check for and install recent updates to any of your installed products.  I ran into a few rough edges while testing (the Windows service created could not be started, and a datadir was created in two different locations), but on whole, it’s a vast improvement over the previous .MSI packages, and there are continuous improvements being made by the great engineering team here at Oracle.

Deprecated in MySQL 5.6 – YEAR(2)

Back in April, I issued a challenge to identify any compelling use case for the YEAR(2) data type.  Months later, I still don’t have such a use case, and the release of 5.6.6 brings deprecation of support for the YEAR(2) datatype.  Trying to create a table with a YEAR(2) column in 5.6.6 or greater will result in conversion to YEAR(4) and a warning:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.6-m9  |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE y (y YEAR(2));
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1818
Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
1 row in set (0.00 sec)

The change logs for 5.6.6 highlight this change, and reference the excellent documentation of YEAR(2) limitations and migration strategies.  If you’re not sure whether you have YEAR(2) columns or not, the following command will identify any such columns:

SELECT table_schema, table_name, column_name, column_type
FROM information_schema.columns
WHERE data_type = 'YEAR'
AND column_type != 'year(4)';

Note that some queries (including this one) against INFORMATION_SCHEMA tables can be slow, and may not be suitable for running in against a busy production database server.  In such instances, you may want to execute this against a slave, backup or development machine loaded with the current schema.

 

How will IPv6 changes in 5.6.6 affect you?

As stated in the 5.6.6 release notes, the default value of –bind-address has changed from IPv4-specific “0.0.0.0″ to “*” – the latter value which allows MySQL to bind to IPv6 interfaces by default.  There are a few implications to this change.

First, make sure you’ve hardened both your IPv4 and your IPv6 access points to your MySQL server.  If you are running IPv4 interfaces only, you can also change the –bind-address value back to the previous default value of “0.0.0.0″.  Because MySQL now listens by default on both IPv4 and IPv6 interfaces, an installation that has only hardened IPv4 interfaces may find unaddressed vulnerabilities exposed via the IPv6 interface that is now used by default.

Second, the change in defaults can have an impact on startup speed for deployments where there is no IPv6 support.  On my Windows XP laptop, startup stalls for 45 seconds while MySQL lets Windows determine that IPv6 is not supported:

120813 10:17:16 [Note] Server hostname (bind-address): ‘*’; port: 3307
120813 10:18:01 [Note] IPv6 is not available.
120813 10:18:01 [Note]   – ’0.0.0.0′ resolves to ’0.0.0.0′;
120813 10:18:01 [Note] Server socket created on IP: ’0.0.0.0′.

Why Windows takes 45 seconds to figure this out is anybody’s guess, but explicitly setting –bind-address to 0.0.0.0 eliminates this behavior, and server start-up speed is restored:

120813 10:27:39 [Note] Server hostname (bind-address): ’0.0.0.0′; port: 3307
120813 10:27:39 [Note]   – ’0.0.0.0′ resolves to ’0.0.0.0′;
120813 10:27:39 [Note] Server socket created on IP: ’0.0.0.0′.

Another option is to make sure your OS has IPv6 support enabled (Microsoft documents this procedure for XP in KB article 2478747).

Whether you have IPv6 support enabled or not, make sure you consider how the change in behavior introduced in 5.6.6 will affect you.

 

 

 

Post Navigation