Best practices for safely performing schema changes in Azure Database for MySQL
Azure Database for MySQL - Flexible Server is built on the open-source MySQL database engine, and the service supports MySQL 8.0 and newer versions. This means that users can take advantage of the flexibility and advanced capabilities of MySQL’s latest features while benefitting from a fully managed database service. While newer versions and features can provide a lot of value, the recent issues identified with MySQL versions 8.0+ makes it important to be aware of potential risks that can occur during certain operations, particularly if you are making online schema changes. Issues with data loss and duplicate keys with Online DDL Online Data Definition Language (DDL) operations are a powerful feature in MySQL, enabling schema changes like ALTER TABLE or OPTIMIZE TABLE with minimal impact on table availability. These operations are designed to reduce downtime by allowing concurrent reads and writes during schema modifications, making them an essential tool for managing active databases efficiently. However, a recent post on the Percona blog, Who Ate My MySQL Table Rows? highlights critical risks associated with MySQL 8.0.x versions after 8.0.27 and all versions beyond 8.4.y. Specifically, the open-source INPLACE algorithm, commonly used for online schema changes, can lead to data loss and duplicate key errors under certain conditions. These issues arise from constraints in the INPLACE algorithm, particularly during ALTER TABLE and OPTIMIZE TABLE operations, exposing vulnerabilities that compromise data integrity and system reliability. These risks are called out in the following bug reports: Bug #115511: Data loss during online ALTER operations with concurrent DML Bug #115608: Duplicate key errors caused by online ALTER operations Documented issues related to the INPLACE algorithm (used for online DDL) can cause: Data Loss: Rows may be accidentally deleted or become inaccessible. Duplicate Keys: Indexes can end up with duplicate entries, leading to data consistency issues and potential replication errors. Problems arise when INPLACE operations, such as ALTER TABLE or OPTIMIZE TABLE, run concurrently with: DML operations (INSERT, UPDATE, DELETE): Modifications to table data during the rebuild. A purge activity: Background cleanup operations for old row versions in InnoDB. These scenarios can lead to anomalies resulting from race conditions and incomplete synchronization between concurrent activities. Impact on Azure Database for MySQL - Flexible Server Customers For Azure Database for MySQL Flexible Server customers using MySQL 8.0+ and all versions after 8.4.y, this issue is particularly critical as it affects: Data Integrity: During schema changes such as ALTER TABLE or OPTIMIZE TABLE run using the INPLACE algorithm, data rows may be lost or duplicated if these operations run concurrently with a DML activity (e.g., INSERT, UPDATE, or DELETE) or background purge tasks. This can compromise the accuracy and reliability of the database, potentially leading to incorrect query results or the loss of critical business data. Replication Instability: Duplicate keys or missing rows can interrupt replication processes, which rely on a consistent data stream across the primary and replica servers. These issues can arise when there are concurrent insertions into the table during schema changes, leading to data inconsistencies between the primary and replicas. Such inconsistencies may result in replication lag, errors, or even a complete breakdown of high-availability setups, requiring manual intervention to restore synchronization. Operational Downtime: Resolving these issues often involves manually syncing data or restoring backups. These recovery efforts can be time-consuming and disruptive, leading to extended downtime for applications and potential business impact. Recommendations for safe schema changes on Azure Database for MySQL flexible servers To minimize the risks of data loss and duplicate keys while making schema changes, follow these best practices: Set old_alter_table=ON to Default to COPY Algorithm Enable the server parameter old_alter_table system variable so that ALTER TABLE operations without a specified ALGORITHM default to using the COPY algorithm instead of INPLACE. This reduces the risk for users who do not explicitly specify the ALGORITHM in their commands. Learn more on how configure server parameters in Azure Database for MySQL. Avoid using ALGORITHM=INPLACE Do not explicitly use ALGORITHM=INPLACE for ALTER TABLE commands, as it increases the risk of data loss or duplicate keys. Back up your data before schema changes Always perform a full on-demand backup of your server before executing schema changes. This precaution ensures data recoverability in case of unexpected issues. Learn more on how to take full on-demand backups for your server. Avoid Concurrent DML during schema changes Schedule schema changes like ALTER TABLE and OPTIMIZE TABLE during application maintenance windows when no concurrent writes activities occur. This minimizes race conditions and synchronization conflicts. Use External Tools for Safer Online Schema Changes Consider using external tools like pt-online-schema-change to modify table definitions without blocking concurrent changes. These tools enable you to make schema changes with minimal impact on availability and performance. Learn more about pt-online-schema-change. Disclaimer: The pt-online-schema-change tool is not managed or supported by Microsoft; use it at your discretion. Mitigation plans To address these risks, we’re actively working to integrate the necessary fixes to ensure a more robust and reliable experience for our customers. New Servers Fully Secured by End of February 2025 All new Azure Database for MySQL Flexible Server instances created after 1 st March 2025, will include the latest fixes, ensuring that schema changes are safeguarded against data loss and duplicate key risks. Rollout for Existing Servers For existing servers, we will roll out patches during upcoming maintenance windows by end of Q1 of Calendar Year 2025 We recommend monitoring your Azure portal for scheduled maintenance windows and Release notes for announcements about critical updates and patches. Priority updates available upon request If you require an urgent update outside of the scheduled maintenance windows, you can contact Azure Support. Provide the necessary server details and an appropriate maintenance window, and our team will work with you to prioritize the patching process. Note that priority patching will be available by February 2025. We recommend monitoring Release notes for announcements about critical updates and patches. Conclusion Safely managing schema changes on MySQL servers requires understanding the risks associated with online DDL operations, such as potential data loss and duplicate keys. To help safeguard data integrity and maintain server stability, implement best practices, for example enabling the COPY algorithm, using offline operations if feasible, or scheduling changes during low activity periods. Fixes are expected by the end of February 2025, and new Azure Database for MySQL flexible servers will be fully protected against these bugs. We will apply updates to existing servers during maintenance windows in Q1 2025. Following the recommendations above will help ensure that you can confidently make schema changes while preserving the reliability and performance of your server.Pinning the new (Outlook) Calendar in Teams
Not sure if anyone else has noticed, but the new calendar seems to refuse being pinned to the left navigation. Using version: 24295.612.3262.1872 The good news is I think I found a workaround: switch back to the old calendar Pin it Switch back Worked on my end. I might have been in this predicament because I originally had the new calendar installed as a separate app (from the Teams store). Since updating last night, that app went away but I was now able to view the toggle to switch to the new calendar. Now the only issue is that I can't move the pinned calendar to another position on the navigation. Anyone else having these issues? I've experienced this on two different accounts, on different devices.Issues with cursor jumping around while typing on documents in teams/randomly deleting text
Hi, wondering if anyone else has seen this issue. Sometimes, when working on a shared document in Teams and using the "Reviewing" function (aka tracking edits), our employees, including myself, will notice our cursor jumps around when making changes, often to a random spot in the document, and often randomly deletes or inserts text as well. This doesn't seem to happen if we open the document in Word and then edit it there, or if we only use the "Editing" function that does not track changes. It has nothing to do with the computer's physical trackpad or mouse either, as it doesn't happen in any other application/Word itself. Also, it happens for pretty much everyone in the organization, all of whom have different laptops. Anyone have a solution for this or know if there's a bug fix happening soon? It's very annoying and causes a lot of headaches when finalizing documents later.
Windows Server vNext Build 26052 NIC display issues
My server has four network interfaces, but the fourth port is not plugin any network connection. But, i see that the taskmgr showed that my port#4 was plugin a network And in the setting i found that it also show the network was plugin in Is that bug?
Bug - white line around business logo
I uploaded a custom business logo - a 500x500 pixel .png file that is one solid color except for the logo. When it appears on the new booking page, there is a 1 pixel wide border around the top and left edge that was not in the original file. This throws off the professional look of the page.
[Windows 11] Hover on taskbar apps don't get preview when open multiple windows of the same app
Is this a glitch or features? I have the problem since the first Windows 11 Insider Preview (Dev Channel, Build 22000.51). But it seems haven't be fixed yet. My current OS build is 22000.176 (Beta Channel). Hover on apps with one windows of the same app: Hover on apps with multiple windows of the same app: What it suppose to look like when hover on apps with multiple windows of the same app: Does anyone has this problem too? Thanks
Media Keys and Teams notifications
If I use keyboard media keys to pause music when I receive a call in teams, the notification sound doesn't seem to get dismissed when I answer the call. So, when I later press play, it also resumes playing the teams call sound over my music. It does seem to go away on it's own eventually, but it's kind of annoying when I want to listen to music!
