Hi
I have used the following command for disabling autoextend of data file


set global innodb_data_file_path = 'ibdata1:400M'

But it is giving error saying
Variable 'innodb_data_file_path' is a read only variable

Is there any way I can disable the autoextend of a particular datafile.

I want to limit the size of the datafile.

Thanks

This is not a dynamic property, which means it must be set when mysql server gets started. You need to add this into the my.cnf (or my.ini on windows) under the [mysqld] section:

[mysqld]
innodb_data_file_path=ibdata1:400M

Do I have to change any other parameter along with innodb_data_file_path

because after doing
innodb_data_file_path = ibdata1:40M

and mysql doesn't restart.

I have changed innodb_data_file_path and stopped and started but start does work.
If I change innodb_data_file_path back to its previous value

innodb_data_file_path = ibdata1:400M:autoextend

It get started successfully

What error did you get in your mysql log file? Not sure whether this is what the problem is but if your file is 400M and you attempt to limit the size to 40M then this could be a problem.

An associated parameter is innodb_data_home_dir:

The paths to individual data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here

I saw the logs and figure out that the new size of ibdata1 should be the one which you should specify

innodb_data_file_path=ibdata1:776M (776 is the actual size of ibdata1 in $HOME/var)

not

innodb_data_file_path=ibdata1:40M

Thanks a ton for the help.
you guys are doing a great job.

Hi

I have limit the db size using

ibdata1:928M

but the db is getting full. Although the size of ibdata1 is not getting increased but time stamp is increasing.

This is normal as the header of the file will include some internal structures which are updated regularly. You can always increase the configuration of ibdata1 to be larger or start removing unwanted tables which are placed in this file which should free up some space. Or put back the autoextend parameter to allow it to continue growing without any checks.