MySQL Bugs: #28367: InnoDB free is larger than the total size

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

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 */

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.

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

> 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.

Denis,

please show what SHOW VARIABLES prints.

Please show:

ls -l

in the relevent data file directories.

--Heikki

SHOW VARIABLES prints

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

ls -l in the relevent data file directories.

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

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.

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

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

We stopped the instance, removed all the existing tablespace files and restarted the instance : all is OK now. Thanks,

-- 
Denis