MySQL ODBC Connector - ~how to add a new innodb data file~

Author

~how to add a new innodb data file~

Mohammed Abdul Azeem

2006-03-30, 3:25 am

Hi,

Iam running a mysql server 5.0.15 over Redhat linux es4. My disk space
has exhausted. so i need to add a new ibdata file to my /etc/my.cnf
configuration. I followed the following procedure to do so.

1. I checked the ibdata1 file size.

when i do a du -sh ibdata1 , i get the size to be 443M

when i do a ls -ltr ibdata1 , i get the size to be 463470592 bytes

2. I edited my /etc/my.cnf to add the following:

innodb_data_file_pat
h = /mysql-system/mysql/data/ibdata1:443M;/mysql-
system2/ ibdata2:50M:autoexte
nd

i got the following error:

060330 01:48:42 mysqld started
InnoDB: Error: data file /mysql-system/mysql/data/ibdata1 is of a
different size
InnoDB: 28288 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 28416 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_pat
h in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do
not
InnoDB: remove old data files which contain your precious data!
060330 1:48:42 [Note] /mysql-system/mysql/bin/mysqld: ready for
connections.
Version: '5.0.15-standard-log' socket: '/tmp/mysql.sock' port: 3306
MySQL Community Edition - Standard (GPL)

3. Then i tried adding the value from 'ls -ltr ibdata1' which is
463470592 bytes. I rounded the same to 464M . but still got the same
error.

Can anyone help me out on how to go about the same.

Thanks in advance,
Abdul.

____________________
____________________

This email has been Scanned for Viruses!
www.newbreak.com

====================
====================

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Heikki Tuuri

2006-04-01, 3:24 am

Mohammed,

http://dev.mysql.com/doc/refman/5.0...d-removing.html
"
If your last data file was defined with the keyword autoextend, the
procedure for reconfiguring the tablespace must take into account the size
to which the last data file has grown. Obtain the size of the data file,
round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and
specify the rounded size explicitly in innodb_data_file_pat
h. Then you can
add another data file. Remember that only the last data file in the
innodb_data_file_pat
h can be specified as auto-extending.
"

The correct way to get the size of a file is the 'ls -l' command. I think
the 'du' command measures the physical size required for the file on the
disk, and it can differ from the size of the file as seen by mysqld.

463470592 / (1024 * 1024) = 442.

InnoDB measures the file size in MB, where MB is defined as 1024 * 1024
bytes.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

----- Original Message -----
From: "Mohammed Abdul Azeem" <abdul@riskspan.com>
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 30, 2006 10:03 AM
Subject: ~how to add a new innodb data file~

> Hi,
>
> Iam running a mysql server 5.0.15 over Redhat linux es4. My disk space
> has exhausted. so i need to add a new ibdata file to my /etc/my.cnf
> configuration. I followed the following procedure to do so.
>
> 1. I checked the ibdata1 file size.
>
> when i do a du -sh ibdata1 , i get the size to be 443M
>
> when i do a ls -ltr ibdata1 , i get the size to be 463470592 bytes
>
> 2. I edited my /etc/my.cnf to add the following:
>
> innodb_data_file_pat
h = /mysql-system/mysql/data/ibdata1:443M;/mysql-
> system2/ ibdata2:50M:autoexte
nd
>
> i got the following error:
>
> 060330 01:48:42 mysqld started
> InnoDB: Error: data file /mysql-system/mysql/data/ibdata1 is of a
> different size
> InnoDB: 28288 pages (rounded down to MB)
> InnoDB: than specified in the .cnf file 28416 pages!
> InnoDB: Could not open or create data files.
> InnoDB: If you tried to add new data files, and it failed here,
> InnoDB: you should now edit innodb_data_file_pat
h in my.cnf back
> InnoDB: to what it was, and remove the new ibdata files InnoDB created
> InnoDB: in this failed attempt. InnoDB only wrote those files full of
> InnoDB: zeros, but did not yet use them in any way. But be careful: do
> not
> InnoDB: remove old data files which contain your precious data!
> 060330 1:48:42 [Note] /mysql-system/mysql/bin/mysqld: ready for
> connections.
> Version: '5.0.15-standard-log' socket: '/tmp/mysql.sock' port: 3306
> MySQL Community Edition - Standard (GPL)
>
> 3. Then i tried adding the value from 'ls -ltr ibdata1' which is
> 463470592 bytes. I rounded the same to 464M . but still got the same
> error.
>
> Can anyone help me out on how to go about the same.
>
> Thanks in advance,
> Abdul.
>
>
> ____________________
____________________

> This email has been Scanned for Viruses!
> www.newbreak.com
>
> ====================
====================

>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links