Bug #28367 InnoDB free is larger than the total size
Submitted: 11 May 2007 7:54 Modified: 16 May 2007 6:45
Reporter: Denis Dordoigne Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1.8 OS:Linux (xfs)
Assigned to: Heikki Tuuri CPU Architecture:Any
Tags: innodb free

[11 May 2007 7:54] Denis Dordoigne
Description:
The total size of my InnoDB space is 73728000 kB :
######
[user@computer~]$ grep "ibdata" /etc/my.cnf
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:2000M;ibdata9:2000M;ibdata10:2000M;ibdata11:2000M;ibdata12:2000M;ibdata13:2000M;ibdata14:2000M;ibdata15:2000M;ibdata16:2000M;/MYDISK3/WEBF11021/ibdata17:2000M;/MYDISK3/WEBF11021/ibdata18:2000M;/MYDISK3/WEBF11021/ibdata19:2000M;/MYDISK3/WEBF11021/ibdata20:2000M;/MYDISK3/WEBF11021/ibdata21:2000M;/MYDISK3/WEBF11021/ibdata22:2000M;/MYDISK3/WEBF11021/ibdata23:2000M;/MYDISK3/WEBF11021/ibdata24:2000M;/MYDISK3/WEBF11021/ibdata25:2000M;/MYDISK3/WEBF11021/ibdata26:2000M;/MYDISK3/WEBF11021/ibdata27:2000M;/MYDISK3/WEBF11021/ibdata28:2000M;/MYDISK3/WEBF11021/ibdata29:2000M;/MYDISK3/WEBF11021/ibdata30:2000M;/MYDISK3/WEBF11021/ibdata31:2000M;/MYDISK3/WEBF11021/ibdata32:2000M;/MYDISK3/WEBF11021/ibdata33:2000M;/MYDISK3/WEBF11021/ibdata34:2000M;/MYDISK3/WEBF11021/ibdata35:2000M;/MYDISK3/WEBF11021/ibdata36:2000M
#######

My InnoDB free space is 76561408 kB :
#######
mysql> SHOW TABLE STATUS like 'ADD_USR'\G
[...]
Comment: InnoDB free: 76561408 kB
####### 

How to repeat:
Create an instance with 36*2000M ibdatas.

Insert structure and data.

Launch a "SHOW TABLE STATUS" command.

Suggested fix:
Do not take account of autoextent if it is not required ?

I do not understand this comment in the fsp_get_available_space_in_free_extents function (innobase/fsp/fsp0fsp.c) :

/* Below we play safe when counting free extents above the free limit:
	some of them will contain extent descriptor pages, and therefore
	will not be free extents */
[11 May 2007 9:26] Sveta Smirnova
Thank you for the report.

But version 4.1.8 is quite old. Please upgrade to current 4.1.22 version, try with it and say us result.
[14 May 2007 12:34] Heikki Tuuri
Hi!

Are you sure mysqld reads your my.cnf when it starts?

InnoDB reports 74,767 MB of free space, though the my.cnf contains only 72,000 MB!

--Heikki
[14 May 2007 13:21] Denis Dordoigne
> Please upgrade to current 4.1.22 version, try with it and say us result
It is not possible, I will try it with another server.

> Are you sure mysqld reads your my.cnf when it starts?
Yes.

> InnoDB reports 74,767 MB of free space, though the my.cnf contains only 72,000
This is the bug.
[14 May 2007 15:02] Heikki Tuuri
Denis,

please show what SHOW VARIABLES prints.

Please show:

ls -l

in the relevent data file directories.

--Heikki
[14 May 2007 17:01] Denis Dordoigne
SHOW VARIABLES prints

Attachment: show_variables.txt (text/plain), 21.91 KiB.

[14 May 2007 17:02] Denis Dordoigne
ls -l in the relevent data file directories.

Attachment: ls_ibdatas.txt (text/plain), 4.20 KiB.

[14 May 2007 17:19] Denis Dordoigne
Comment : we recently removed 7 ibdatas from the first directory and added 20 new ibdatas in the second directory but the method related in the reference manual ("15.8. adding-and-removing") was not seriously followed : only the 7 seven extra ibdatas were removed before restarting mysqld, and not all ibdatas.
[14 May 2007 17:25] Heikki Tuuri
Denis,

that is probably the explanation. The free space counter in the header page of ibdata1 is wrong now. Now, if you try to fill your tablespace, InnoDB will crash, because the file it tries to access does not exist!

You can probably fix the problem by adding a couple of all-zeroed ibdata files to your database and edit the .my.cnf. Then restart mysqld.

Regards,

Heikki
[14 May 2007 17:41] Denis Dordoigne
Thanks for your analyse Heikki Tuuri, so it's not a bug.

> adding a couple of all-zeroed ibdata files to your database
How can I do that ? It is an SQL request to execute ?

> and edit the .my.cnf
What are the elements whichh need a modification ?

Thanks for your help,

-- 
Denis
[16 May 2007 6:45] Denis Dordoigne
We stopped the instance, removed all the existing tablespace files and restarted the instance : all is OK now. Thanks,

-- 
Denis