MySQL Server Forum - ok to delete ib_data1 after power outage?

Author

ok to delete ib_data1 after power outage?

timothy.williams@nvl.army.mil

2005-06-07, 8:23 pm

Hi.

We're running mysql 4.1.2-alpha on a Redhat linux box. Every time we
have a power outage, or the machine shuts down w/o shutting down the
database, I always have the error

InnoDB: Unable to lock DATADIR/ibdata1 with lock 1, error: 11: fcntl:
Resource temporarily unavailable

(edited location of DATADIR to protect the innocent)

If I rename (or delete) the ib_data1, ib_logfile0, ib_logfile1 files,
then mysql recreates them and I'm up and running again. I don't seem to
be losing any data, as our databases are pretty static; mostly queries
after the tables are loaded.

Is this safe to do? What should I be doing?

Thanks.

Bill Karwin

2005-06-07, 8:23 pm

timothy.williams@nvl.army.mil wrote:
> Hi.
>
> We're running mysql 4.1.2-alpha on a Redhat linux box. Every time we
> have a power outage, or the machine shuts down w/o shutting down the
> database, I always have the error
>
> InnoDB: Unable to lock DATADIR/ibdata1 with lock 1, error: 11: fcntl:
> Resource temporarily unavailable
>
> (edited location of DATADIR to protect the innocent)
>
> If I rename (or delete) the ib_data1, ib_logfile0, ib_logfile1 files,
> then mysql recreates them and I'm up and running again. I don't seem to
> be losing any data, as our databases are pretty static; mostly queries
> after the tables are loaded.
>
> Is this safe to do? What should I be doing?
>
> Thanks.
>

I found a thread discussing this symptom at:
http://forums.mysql.com/read.php?22...22344#msg-22344

It seems to happen regularly if the MySQL server host goes down, and the
MySQL data directory is on an NFS-mounted filesystem. It might be
specific to the Linux NFS implementation.

The ibdata1 file is the default file that contains all InnoDB tables and
indexes. If you don't use the InnoDB table storage type for any of your
databases, then clobbering this file is probably okay for now.

I recommend the better fix is to store your datadir on a local
filesystem, a drive physically local to the server running MySQL.

I always prefer to use a local datadir instead of NFS-mounted, for the
following reasons:
- Performance - a direct SCSI or ATA interface to the hard drive is
faster than a network;
- Reliability - the NFS host or the network could become unavailable or
congested;
- Data integrity - if another MySQL daemon tries to write to the same
datadir, it would be Very Bad.

Regards,
Bill K.

timothy.williams@nvl.army.mil

2005-06-08, 9:23 am

Bill Karwin wrote:
> timothy.williams@nvl.army.mil wrote:
(snip)
>
> I found a thread discussing this symptom at:
> http://forums.mysql.com/read.php?22...22344#msg-22344
>
> It seems to happen regularly if the MySQL server host goes down, and the
> MySQL data directory is on an NFS-mounted filesystem. It might be
> specific to the Linux NFS implementation.
>
> The ibdata1 file is the default file that contains all InnoDB tables and
> indexes. If you don't use the InnoDB table storage type for any of your
> databases, then clobbering this file is probably okay for now.
>
> I recommend the better fix is to store your datadir on a local
> filesystem, a drive physically local to the server running MySQL.
>
> I always prefer to use a local datadir instead of NFS-mounted, for the
> following reasons:
> - Performance - a direct SCSI or ATA interface to the hard drive is
> faster than a network;
> - Reliability - the NFS host or the network could become unavailable or
> congested;
> - Data integrity - if another MySQL daemon tries to write to the same
> datadir, it would be Very Bad.
>
> Regards,
> Bill K.

I'll have to look into putting the data on a local drive. Problem with
that is, those drives don't get backed up. They're considered
'scratch'. I don't have root access, and I'd have to get our sysadmins
to give us access.

I uncommented the innodb lines in the my.cnf file, but what besides
that determines if you are using InnoDB? Maybe I should just put the
comments back in and not bother with it?

Thanks.

Bill Karwin

2005-06-08, 11:23 am

timothy.williams@nvl.army.mil wrote:
> I'll have to look into putting the data on a local drive. Problem with
> that is, those drives don't get backed up. They're considered
> 'scratch'. I don't have root access, and I'd have to get our sysadmins
> to give us access.

I would create backups with mysqldump and write those to the NFS
filesystem. It's tricky using the live database files as a backup
method anyway. Because MySQL caches some changes in memory and may
write them to the physical file later, you aren't guaranteed that the
file on disk has the latest view of your data unless you have shut down
the MySQL service.

> I uncommented the innodb lines in the my.cnf file, but what besides
> that determines if you are using InnoDB? Maybe I should just put the
> comments back in and not bother with it?

Hmm. You should know this if you were the one who created the databases
and tables. You can also view the properties of a table this way:

mysql> show table status;

The "Engine" field of the output tells you whether each table is MyISAM
or InnoDB. You have to do this command while connected to each database
on your system to make sure you have no InnoDB tables anywhere.

Regards,
Bill K.

timothy.williams@nvl.army.mil

2005-06-09, 7:23 am

I checked, and the tables are MyISAM. There were created using mysqlcc.
That being the case, should I just comment back those lines in the
my.cnf file?
Thanks for the help. One of these days I should take a course in MySQL.

Sponsored Links