Bug #8135 OPTIMIZE of InnoDB table does not return 'Table is full' if out of tablespace
Submitted: 25 Jan 2005 16:20 Modified: 22 Jun 2005 0:00
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Any (All)
Assigned to: Jim Winstead

[25 Jan 2005 16:20] Heikki Tuuri
Description:

mysql> optimize table aaa;
+-------------------+----------+----------+------------------+
| Table             | Op       | Msg_type | Msg_text         |
+-------------------+----------+----------+------------------+
| test.aaa | optimize | status   | Operation failed |
+-------------------+----------+----------+------------------+
1 row in set (16 min 52.93 sec)

mysql>

heikki@hundin:~/mysql-4.1/sql> gdb mysqld
GNU gdb 6.0
Copyright 2003 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i686-pc-linux-gnu"...
(gdb) run
Starting program: /home/heikki/mysql-4.1/sql/mysqld
[New Thread 16384 (LWP 6207)]
[New Thread 32769 (LWP 6287)]
[New Thread 16386 (LWP 6288)]
[New Thread 32771 (LWP 6289)]
[New Thread 49156 (LWP 6290)]
[New Thread 65541 (LWP 6291)]
[New Thread 81926 (LWP 6306)]
[New Thread 98311 (LWP 6307)]
[New Thread 114696 (LWP 6308)]
050125 16:20:43  InnoDB: Started; log sequence number 0 43674
[New Thread 131081 (LWP 6309)]
050125 16:20:43 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
/home/heikki/mysql-4.1/sql/mysqld: ready for connections.
Version: '4.1.10-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Sourc
e distribution
[New Thread 147466 (LWP 6433)]
[New Thread 163851 (LWP 9551)]
050125 17:28:29  InnoDB: Error: Write to file /home/heikki/data/ibdata1 failed a
t offset 0 2147483648.
InnoDB: 1048576 bytes should have been written, only -1 were written.
InnoDB: Operating system error number 27.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 27 means 'File too large'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html

How to repeat:
See above.

Suggested fix:
Let us make MySQL not to swallow error messages or numbers.
[5 Feb 2005 2:57] Jim Winstead
Patch for server to capture errors in OPTIMIZE TABLE <innodb_table>

Attachment: 8135.diff (application/octet-stream, text), 1.34 KiB.

[5 Feb 2005 3:06] Jim Winstead
I've written a patch for this and have tested it by hand, but have so far not been able to craft a test case for the bug that will run within the test suite. (I tested by setting up a 10MB InnoDB data file, creating a 5MB table, and running OPTIMIZE TABLE on it. A similar test within the test suite, which creates a 50MB InnoDB data file, causes the server to exit with error 13.)

Here is the test case that does not work:

create table t1 (b longblob) engine=innodb;
let $1 = 30 ;
while ($1)
{
  insert into t1 values (repeat('a',1024*1024));
  dec $1 ;
}
optimize table t1;
drop table t1;

The diff is attached to this bug, and is pasted below:

===== sql/sql_table.cc 1.274 vs edited =====
--- 1.274/sql/sql_table.cc      2005-02-02 10:27:58 -08:00
+++ edited/sql/sql_table.cc     2005-02-04 18:54:50 -08:00
@@ -2046,6 +2046,31 @@
             ((result_code= table->table->file->analyze(thd, check_opt)) > 0))
           result_code= 0; // analyze went ok
       }
+      if (result_code) // either mysql_recreate_table or analyze failed
+      {
+        const char *err_msg;
+        if (!(err_msg=thd->net.last_error))
+          goto no_special_error;
+        if (!thd->vio_ok())
+        {
+          sql_print_error(err_msg);
+        }
+        else
+        {
+          /* Hijack the row already in-progress. */
+          protocol->store("error", 5, system_charset_info);
+          protocol->store(err_msg, system_charset_info);
+          if (protocol->write())
+            sql_print_error("Failed on my_net_write, "
+                            "writing to stderr instead: %s\n",
+                            err_msg);
+          /* Start off another row for HA_ADMIN_FAILED */
+          protocol->prepare_for_resend();
+          protocol->store(table_name, system_charset_info);
+          protocol->store(operator_name, system_charset_info);
+        }
+      }
+no_special_error:
       result_code= result_code ? HA_ADMIN_FAILED : HA_ADMIN_OK;
       table->next= save_next;
       goto send_result_message;
[18 Feb 2005 14:21] Heikki Tuuri
Jim,

did you remember that ibdata1 must not be auto-extending?

No need to make an innodb.test case. Just test it by hand with a fixed-size 10 MB ibdata1 file.

Regards,

Heikki
[18 Feb 2005 20:21] Jim Winstead
mysql-test-run uses --innodb_data_file_path=ibdata1:50M, so as far as I can tell, it should have been able to handle my test case.
[19 Feb 2005 9:43] Heikki Tuuri
Jim,

you apparently have

innodb_file_per_table

set in my.cnf. No wonder why it does not run out of space in the ibdata file, since the table is in an .ibd file.

I was also able to repeat the error 13 with an ibdata1 of a fixed size 50 MB. I have to study why this occurs with the big BLOB column. I was able to resolve the situation by adding the :autoextend specification to ibdata1.

You should test OPTIMIZE with a table having smaller rows, so that you see your patch works ok. My job is to find out why BLOBs cause the rollback to run out of tablespace.

Thank you,

Heikki
heikki@hundin:~/mysql-4.1/sql> ./mysqld
050219 10:49:25  InnoDB: Started; log sequence number 0 98012073
050219 10:49:25 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
./mysqld: ready for connections.
Version: '4.1.10-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Sourc
e distribution
InnoDB: Fatal error 13 in rollback.
InnoDB: Error 13 means out of tablespace.
InnoDB: Consider increasing your tablespace.
heikki@hundin:~/mysql-4.1/sql> ./mysqld
050219 10:51:19  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050219 10:51:19  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 130669082.
InnoDB: Doing recovery: scanned up to log sequence number 0 135911936
InnoDB: Doing recovery: scanned up to log sequence number 0 141154816
InnoDB: Doing recovery: scanned up to log sequence number 0 146397696
InnoDB: Doing recovery: scanned up to log sequence number 0 147272947
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 16 row operations to undo
InnoDB: Trx id counter is 0 1792
050219 10:51:20  InnoDB: Starting an apply batch of log records to the database.
..
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7
3 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 1290, 16 rows to undoInnoDB: Fatal error 13 i
n rollback.
InnoDB: Error 13 means out of tablespace.
InnoDB: Consider increasing your tablespace.
heikki@hundin:~/mysql-4.1/sql>
[25 Apr 2005 23:13] Jim Winstead
Patch for this is at http://lists.mysql.com/internals/22168
[24 May 2005 18:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25229
[2 Jun 2005 1:11] Jim Winstead
Fixed in 4.1.13 and 5.0.7.
[22 Jun 2005 0:00] Mike Hillyer
Documented in 5.0.7 and 4.1.13 changelogs:

<listitem><para>OPTIMIZE of InnoDB table does not return 'Table is full' if out of tablespace. (Bug #8135)</para></listitem>