Issues with MySQL and InnoDB ibdata1 file

So as of the past hour the X-Media Online websites have ground to a halt and can’t be accessed, after being alerted of the issue I quickly went to investigate. My first port of call is usually to /var/log because I’m ashamed to admit I’ve let some log files get out of hand before. But this time (thankfully) that wasn’t the problem (I’d fixed those issues, hopefully).

This time the issue seems to be in /var/lib/mysql where the ibdata1 file has grown to about 20GB in size which is just shy of the max size of the partition that it’s on.

I’m currently in the process of fixing it and will give updates as I go through the process of fixing it. I’m using both this Stackoverflow post and this MySQL documentation. I’m at step one, currently creating a dump of the entire database. I’ll keep you updated as I go.

Update: No update on the fixing yet, but I’ve determined the root of the problem to be the old X-Media Online wiki which must be open to editing. There are over 17 million records in the wiki text table making up 19.7GB of the 20GB InnoDB file. A lot of mistakes to learn from at this early hour of the morning (it’s 2:40am here right now).

Update 2: Dropped the old wiki database, cleared up the ibdata1 using the following documentation but not before reconfiguring InnoDB to use multiple table spaces as per this documentation.

Update 3: Bug mentioning the issue for future reference.

Advertisements

Published by:

Skyrail

Production Facilitator at Audacious Church Previously Software Developer at UKFast Ltd. and Web Applications Developer at SenseLogix Ltd. Graduate of Renewable Energy BSc from the University of Exeter. Experience with PHP, MySQL, Javascript, CSS and HTML.

Categories Linux, SQLTags , , Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s