MySQL 9.7: First Major LTS Since 8.4 Brings Enterprise Features to Community Edition

MySQL 9.7.0 has been released as the first major Long-Term Support (LTS) version since MySQL 8.4, bringing several enterprise-grade features to the Community Edition. This release comes amid community concerns about declining development activity and Oracle's long-term commitment to the MySQL project.

Context and Community Concerns

The release of MySQL 9.7 arrives at a critical time for the database ecosystem. Recent analysis of the MySQL repository revealed declining development activity and a shrinking contributor base, which has raised questions about the project's future direction. Additionally, Oracle's recent layoffs have further heightened community concerns about the company's commitment to MySQL.

In response, Oracle has taken steps to reassure users through meetings about MySQL's future, while acknowledging that some in the community have already begun creating tracking forks to extend MySQL's capabilities.

Mike Frank, product management director at Oracle, emphasizes that the long-term goal is to "create a tighter feedback loop with the community and help craft the roadmap together." This release represents a significant step in that direction by making several capabilities previously exclusive to the Enterprise Edition now available in the Community Edition.

Key Features in MySQL 9.7

Enterprise Features in Community Edition

One of the most significant aspects of MySQL 9.7 is the inclusion of several enterprise features in the Community Edition:

1. Dynamic Data Masking: This feature protects sensitive data at query time by applying masking policies to table columns without requiring application changes. Previously available only in the Enterprise Edition, it now allows community users to implement data protection strategies more easily.

2. OpenID Authentication: The release adds support for OpenID authentication, providing more flexible identity management options for organizations.

3. Hypergraph Optimizer: This advanced query optimization technology is now available in the Community Edition, promising improved performance for complex queries.

REST Service Improvements

The MySQL REST Service has been enhanced in this release, providing better capabilities for integrating MySQL with modern web applications and microservices architectures. The improvements include more robust error handling, enhanced performance, and additional authentication options.

JSON Duality and JavaScript Support

MySQL 9.7 introduces several enhancements for working with JSON data:

1. DML Support for JSON Duality Views: Developers can now perform Data Manipulation Language (DML) operations directly on JSON Duality Views, making it easier to work with semi-structured data.

2. Auto-increment Support for JSON Duality View Operations: The release adds support for auto-increment operations within JSON Duality Views, simplifying the management of JSON data with sequential identifiers.

3. In-Database JavaScript: The introduction of JavaScript support allows developers to write stored procedures and functions using JavaScript, opening up new possibilities for application logic within the database.

Security Enhancements

Beyond dynamic data masking and OpenID authentication, MySQL 9.7 includes several security improvements:

1. Enhanced Authentication Mechanisms: Additional authentication options provide more flexibility for different security requirements.

2. Security Patches: The release includes patches for various security vulnerabilities, ensuring that MySQL deployments remain secure.

Operational Improvements for DBAs and Operators

MySQL 9.7 introduces several features designed to improve database administration and operational visibility:

1. Flow-Control Monitoring: DBAs can now gain better visibility into cluster throttling with enhanced flow-control monitoring capabilities. This makes it easier to diagnose performance issues related to replication lag.

2. Extended Replication Applier Statistics: The release provides more detailed statistics for observing lag and throughput in multi-threaded replication environments. This is particularly valuable for monitoring complex replication topologies.

3. Automatic Eviction and Rejoin of Unhealthy Cluster Members: The cluster management capabilities have been enhanced to automatically handle unhealthy nodes, improving overall cluster resilience.

4. Primary Election During Failover: When a failover occurs, the system now favors electing the most up-to-date eligible node as the new primary, reducing the risk of data loss and minimizing recovery time.

Technical Deep Dive: The Hypergraph Optimizer

One of the most significant technical improvements in MySQL 9.7 is the introduction of the Hypergraph optimizer. This represents a fundamental change in how MySQL approaches query optimization.

Øystein Grøvlen, senior principal software engineer at Oracle, explains that the classic MySQL join optimizer was based on a "long-standing left-deep search framework" that, despite many improvements, had limitations that made certain optimization approaches difficult.

The Hypergraph optimizer addresses these limitations by:

1. Making Interesting Orders First-Class: The optimizer now considers interesting orders as integral parts of the optimization process, rather than afterthoughts.

2. Real Cost-Based Choices: The choice between nested-loop join and hash join is now a proper cost-based decision, allowing the optimizer to select the most efficient strategy based on the specific query and data characteristics.

3. Support for Bushy Join Plans: The new optimizer supports bushy join plans, which can be more efficient for certain query patterns than the traditional left-deep plans.

According to Grøvlen, these changes make "several important choices first-class parts of optimization instead of afterthoughts," leading to more efficient query execution plans.

However, Peter Zaitsev, founder of Percona and open source advocate, offers a word of caution: "Hypergraph optimized in MySQL 9.7 makes many queries to run faster, but as it usually goes with optimizers not all of them - make sure to test how it impacts your application do not just assume it is newer... so it must be better."

This advice is particularly important for production environments where query performance is critical. Organizations should thoroughly test the new optimizer with their specific workloads before deploying it in production.

Migration Path and Considerations

For organizations planning to migrate to MySQL 9.7, several considerations should be kept in mind:

1. LTS Timeline: MySQL 8.4 is currently set to reach end of life in three years, making 9.7 a viable long-term option for organizations that need extended support.

2. Repository Update Bug: At the time of general availability, a bug in the mysql-community.repo update (MySQL Bug #120315) silently disabled the 8.4 LTS repository and enabled 9.7 LTS instead. This caused routine package updates to switch the major version a server follows. Organizations should verify their repository configurations before upgrading.

3. Testing the Hypergraph Optimizer: Given the potential impact of the new optimizer on query performance, thorough testing is recommended before migration.

4. Feature Parity: With the inclusion of enterprise features in the Community Edition, organizations using the Enterprise Edition should evaluate whether they still need the commercial offering or if the Community Edition now meets their needs.

Conclusion

MySQL 9.7 represents a significant release that addresses several community concerns while introducing valuable new features. By bringing enterprise capabilities to the Community Edition, Oracle is demonstrating a commitment to the open-source nature of MySQL while also providing value to its commercial customers.

The introduction of the Hypergraph optimizer, enhanced JSON support, and improved operational capabilities make this release particularly noteworthy for organizations running MySQL in production environments. However, the community's skepticism about Oracle's commitment to MySQL is understandable, and the company will need to continue demonstrating its dedication to the open-source project to maintain trust.

For database administrators and developers, MySQL 9.7 offers compelling reasons to consider upgrading, particularly those who have been waiting for enterprise features to become available in the Community Edition. The long-term support timeline also makes it a viable option for organizations that need stability and extended support periods.

As the MySQL ecosystem continues to evolve, it will be interesting to see how Oracle balances its commercial interests with the needs of the open-source community. The 9.7 release is a positive step, but ongoing engagement and transparency will be essential for maintaining the project's momentum and addressing the concerns that have emerged in recent months.