Category Archives: MySQL

Thoughts about MySQL.

Bitten by MariaDB 10.2 Incompatibile Change

Since MariaDB first claimed to be a “drop-in replacement for MySQL”, I knew that claim wouldn’t age well.  It hasn’t – the list of incompatibilities between various MySQL and MariaDB verions grows larger and larger over time, now requiring a dedicated page to compare two specific recent releases.  Despite that, I largely operated under the assumption that basic, general use cases would see no difference.

I was wrong.

This past week, I was “helping” with a Support issue where installation of Cloudera Manager failed on MariaDB 10.2.  Like many products, the schema for Cloudera Manager has evolved over releases, and new installs apply both the original schema and version-specific updates to bring the schema to a current version.  When it failed, I reviewed the scripts and found nothing that should cause problems.  A fantastic Support colleague (thanks Michalis!) dug in deeper, and – contradicting my assumptions – found that the root problem was an incompatible change made in MariaDB 10.2.8.  This causes ALTER TABLE … DROP COLUMN commands to fail in MariaDB 10.2.8, which do not fail in any MySQL version, nor in any previous MariaDB version – including 10.2.7.

When dropping a column that’s part of a composite unique constraint, MariaDB throws error 1072.  Here’s an example:


MariaDB [test]> SHOW CREATE TABLE uc_test\G
*************************** 1. row ***************************
Table: uc_test
Create Table: CREATE TABLE `uc_test` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`,`c`),
UNIQUE KEY `d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [test]> ALTER TABLE uc_test DROP COLUMN c;
ERROR 1072 (42000): Key column ‘c’ doesn’t exist in table
MariaDB [test]> ALTER TABLE uc_test DROP COLUMN b;
ERROR 1072 (42000): Key column ‘b’ doesn’t exist in table

This differs from previous version behavior, as well as MySQL, which does one of two things:

  1. Narrows the UNIQUE constraint to the remaining columns (surely not ideal, Bug#17098).
  2. Rejects the change if the resulting narrowed UNIQUE constraint is violated.

While narrowing the existing UNIQUE constraint seems inappropriate, at least MySQL provides a useful and appropriate error message as a result:

mysql> INSERT INto uc_test values (1,1,1,1),(2,1,2,2);
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> alter table uc_test DROP COLUMN c;
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘b’

I agree with the suggestion in the MySQL bug report that a more appropriate action would be to drop the entire UNIQUE constraint in which the dropped column appeared.

MariaDB elected to go a different route. This behavior change was triggered by the work done in MDEV-11114, which explicitly identified a defect in MariaDB’s CHECK CONSTRAINT feature.  As the CHECK CONSTRAINT feature is not found upstream, neither was this defect, which failed to drop constraints declared independent of the column definition when the related column was dropped.  Rather than restrict the fix to the problematic CHECK CONSTRAINT code, MariaDB developers elected to impose new restrictions on all CONSTRAINTS – despite a very clearly-articulated comment from Mike Bayer documenting the incompatibilities this would trigger.  MariaDB also elected to make this change  in a maintenance release instead of a minor version upgrade.  As a result, SQL scripts – like that used by Cloudera Manager – fail using MariaDB 10.2.8, while they did not in 10.2.7.

Note that I have not tested this behavior in a replication stream – I presume it breaks replication from any MySQL or MariaDB version to 10.2.8+.  That’s an even bigger issue for many deployments.

MariaDB has an opportunity – and I’d argue an obligation – to avoid introducing such incompatibilities in maintenance releases.  As somebody supporting enterprise products built to use relational databases such as MariaDB, we need to have confidence that maintenance release upgrades won’t break products relying on those services.  Ignoring the promise of being a “drop in replacement”, the consequences of this change were known in advance, yet MariaDB chose to pursue it in a maintenance release.  There are two real, underlying bugs – the CHECK CONSTRAINT defect introduced by MariaDB, which should have been fixed without impact to other functionality, and the long-standing MySQL defect that triggers constraint narrowing.  SQL standards compliance is a viable reason for behavior changes, but such should be done at minor releases only.

I also object to MariaDB’s decision to repurpose an existing MySQL error code for this purpose.  I agree with Elena’s comment that more meaningful error messages are required.  This is new error-generating behavior, and should have a unique, new error code and message to accompany it for troubleshooting purposes.  This is particularly important when the behavior deviates from established historical behavior.

I’m all for MariaDB developing their own value-add features for MySQL Server.  Defects found in such features should be fixed without impacting common behavior, though.

 

Requests for MariaDB

There has been a recent spate of blog posts and tweets regarding MaxScale binary downloads requiring acceptance of an Evaluation License that some in the community perceived to be onerous.  MariaDB took quick action to make these binaries available without accepting the license, and were quickly – and rightly – praised for listening to the community. [UPDATE 2016-04-14: It turns out the measures MariaDB took were incomplete, and that accessing downloads of MaxScale still require accepting the Evaluation License terms – see comments for details]  The MaxScale binaries are part of a larger concern for me, and I commented as much on the blog highlighting MariaDB’s action.  That comment hasn’t been approved by the author (though later comments have been), so I thought it might not be the right venue to raise such questions – hence this blog post.  I hope MariaDB proves as responsive to these concerns/questions as they were to the MaxScale binary issues! Continue reading Requests for MariaDB

Rosetta Stone: MySQL, Pig and Spark (Basics)

In a world where new data processing languages appear every day, it can be helpful to have tutorials explaining language characteristics in detail from the ground up.  This blog post is not such a tutorial.   It also isn’t a tutorial on getting started with MySQL or Hadoop, nor is it a list of best practices for the various languages I’ll reference here – there are bound to be better ways to accomplish certain tasks, and where a choice was required, I’ve emphasized clarity and readability over performance.  Finally, this isn’t meant to be a quickstart for SQL experts to access Hadoop – there are a number of SQL interfaces to Hadoop such as Impala or Hive that make Hadoop incredibly accessible to those with existing SQL skills.

Instead, this post is a pale equivalent of the Rosetta Stone – examples of identical concepts expressed in three different languages:  SQL (for MySQL), Pig and Spark.  These are the exercises I’ve worked through in order to help think in Pig and Spark as fluently as I think in SQL, and I’m recording this experience in a blog post for my own benefit.  I expect to reference it periodically in my own future work in Pig and Spark, and if it benefits anybody else, great. Continue reading Rosetta Stone: MySQL, Pig and Spark (Basics)

New MySQL Online Training

Oracle University recently unveiled a new online training offering – the MySQL Learning Subscription.  The combination of freely-accessible and compelling paid content makes this an exciting development to me, and should prove valuable to the community and customer base alike.  This post will briefly explore this new MySQL educational resource.

Continue reading New MySQL Online Training

SYS Schema: Simplified Access To SSL/TLS Details

A while back, I wrote a blog post explaining how PERFORMANCE_SCHEMA improvements in MySQL Server 5.7 provides new visibility into the SSL/TLS status of each running client configuration.  An excellent recent post from Frederic Descamps at Percona covers similar territory.  Both of us use PERFORMANCE_SCHEMA tables directly – a powerful interface, but one that requires a query joining multiple tables.  Thanks to the excellent work of Mark Leith, and a contribution from Daniël van Eeden, access to this same information is made far easier via the SYS schema. Continue reading SYS Schema: Simplified Access To SSL/TLS Details

Questions about MariaDB’s bug system

MariaDB recently announced the migration of the JIRA bug tracking system from the current Atlassian-hosted instance to a self-hosted installation to be found at jira.mariadb.org.    This likely isn’t a big deal to the community, and MariaDB is being very proactive in coordinating this change in the community – but it’s an opportunity for me to ask a few questions regarding MariaDB’s JIRA usage to which I can’t find answers.  I certainly welcome answers, feedback or clarification from MariaDB staff.

Before getting started, I should say that I appreciate MariaDB – the product, the company, the staff and the foundation.  MariaDB/SkySQL/Monty Programs serve a unique and useful purpose for community, users and staff who prefer not to deal with Oracle.  I don’t view myself as a critic of MariaDB, and I consider a good number of MariaDB staff my friends.  Getting a better understanding of how MariaDB operates the bug system is of interest to me, and perhaps the larger community as well. Continue reading Questions about MariaDB’s bug system

Leaving MySQL

After nearly ten years working for MySQL, I’m pursuing a new opportunity to expand into new areas of open source data infrastructure as part of the excellent Cloudera support organization.  I’ve been fortunate to work with incredibly talented, dedicated and wonderful people on relational databases, and I’m looking forward to doing the same in the Hadoop space in my new role.  Despite this transition, I intend to remain active in the MySQL community – most immediately, finishing off a handful of half-finished blog posts in the coming weeks.

My various bit roles at MySQL have given me a front-row seat as the company grew from a smaller independent company to a prominent product at Sun to part of a much larger, enterprise-focused portfolio within Oracle.  I’m incredibly proud of the progress MySQL has made over the years, in each stage – but the past 6 years under the stewardship of Oracle are particularly satisfying.  The Oracle way of doing things is well-understood, and has historically produced very successful results – for the products, the customers and the business – but it’s not for everybody.  While I certainly appreciate the motivation of those who wanted to continue an independent MySQL tradition outside of Oracle, my heroes are the committed MySQL staff who stayed to ensure MySQL flourished inside Oracle.  Thanks for all you have done – and continue to do – to ensure MySQL is strong and gets better.

Oracle isn’t perfect, and there have been mistakes made along the way, and things I still wish could change today.  It’s a big company, and MySQL is a small part of it.  But there is an incredible dedication within the MySQL team at Oracle to improve products and experiences for both community users and customers alike.  There’s also a number of legacy Oracle staff who have worked hard to position MySQL for success inside Oracle, and to help apply and adapt Oracle ways of doing things to add value for MySQL users.  Keep up the good work – I know I’m excited to see what the future holds for MySQL.

Simplified SSL/TLS Setup for MySQL Community

Transport Layer Security (TLS, also often referred to as SSL) is an important component of a secure MySQL deployment, but the complexities of properly generating the necessary key material and configuring the server dissuaded many users from completing this task.  MySQL Server 5.7 simplifies this task for both Enterprise and Community users.  Previous blog posts have detailed the changes supporting Enterprise builds; this blog post will focus on parallel improvements made to MySQL Community builds.

Continue reading Simplified SSL/TLS Setup for MySQL Community

Which accounts can access this data?

Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host.  But what about when you need visibility into privileges from the other direction – which accounts can access specific data?  If you’re a DBA – or perform DBA duties, regardless of your title – you may have been asked this question.  It’s an important question to ask in an audit or compliance review – but it can be a difficult question to answer.    This post will walk through how to assess this, but if you’re impatient and need answers to this question immediately, jump to the end – there’s a simple shortcut. Continue reading Which accounts can access this data?

Secure Java Connections by Default

MySQL Connector/Java 5.1.38 was released earlier this week, and it includes a notable improvement related to secure connections.  Here’s how the change log describes it:

When connecting to a MySQL server 5.7 instance that supports TLS, Connector/J now prefers a TLS over a plain TCP connection.

This mirrors changes made in 5.7 to the behavior of MySQL command-line clients and libmysql client library.  Coupled with the streamlined/automatic generation of TLS key material to ensure TLS availability in MySQL Server 5.7 deployments, this is an important step towards providing secure communication in default deployments. Continue reading Secure Java Connections by Default