| Bug #119990 | Proactive handling of InnoDB tablespace full condition | ||
|---|---|---|---|
| Submitted: | 6 Mar 0:04 | Modified: | 13 Mar 23:08 |
| Reporter: | Fariha Shaikh (OCA) | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[6 Mar 0:07]
Fariha Shaikh
I am working on this and will submit a PR soon, thank you!
[9 Mar 19:10]
Fariha Shaikh
Upstream PR is available here: https://github.com/mysql/mysql-server/pull/649
[10 Mar 0:52]
OCA Admin
Contribution submitted via Github - Bug #119990 Proactive handling of InnoDB tablespace full condition (*) Contribution by Fariha Shaikh (Github FarihaIS, mysql-server/pull/649#issuecomment-4027616294): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: git_patch_3374992802.txt (text/plain), 17.15 KiB.
[13 Mar 23:08]
Fariha Shaikh
Hi, I revised the logic in my branch again and updated the PR description to reflect the latest changes (the PR commit page is not showing the latest commit however). Please see my branch for the latest changes: https://github.com/FarihaIS/mysql-server/tree/bug119990
Description: InnoDB write failures can be encountered due to the underlying file system’s file size limits. InnoDB logs warnings and error codes (MY-012640 Error number 27 means ‘File too large’), sets an internal tablespace full flag, and may roll back some transactions. However, further modification attempts (inserts/updates) to affected tables will keep failing until administrative action is taken. For instance, while attempting to write data to the .ibd tablespace file that exceeds file system constraints, the operation fails. The current InnoDB engine responds by logging the error, but new transactions continue to be accepted and retried, resulting in user disruption, potential data integrity concerns, and soft block corruption in file. Add proactive monitoring for InnoDB tablespaces by emitting warnings when they approach configured size limits. This feature is specific to InnoDB storage engine and does not affect other storage engines like MyISAM. How to repeat: -- 1. Create a table with InnoDB engine CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, data LONGBLOB ) ENGINE=InnoDB; -- 2. Insert data until the tablespace file (.ibd) approaches or exceeds -- the underlying file system's file size limit (e.g., on a filesystem -- with a small file size cap, or by setting innodb_data_file_path to -- a constrained size for the system tablespace). -- For example, repeatedly insert ~1MB rows: INSERT INTO t1 (data) VALUES (REPEAT('a', 1024*1024)); -- ... repeat until the .ibd file hits the filesystem limit ... -- 3. Observe: InnoDB logs "Error number 27 means 'File too large'" (MY-012640), -- sets an internal tablespace-full flag, and may roll back the transaction. -- However, subsequent INSERT/UPDATE attempts against the same table continue -- to be accepted, retried, and fail — with no proactive warning before -- the limit is reached. -- 4. There is currently no mechanism to: -- a) Warn when a tablespace is *approaching* a configured size threshold -- (e.g., at 70% or 80% of a limit), before writes start failing. -- b) Disable/enable such warnings at runtime (an "andon cord" toggle). -- c) Reset the warning state after administrative action (e.g., TRUNCATE TABLE). Suggested fix: Add proactive monitoring for InnoDB tablespaces by emitting warnings when they approach configured size limits. This feature is specific to InnoDB storage engine and does not affect other storage engines like MyISAM.