Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    10

    Unanswered: disabling autoextend of datafile

    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

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2010
    Posts
    10
    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

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2010
    Posts
    10
    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

  6. #6
    Join Date
    Apr 2010
    Posts
    10
    Thanks a ton for the help.
    you guys are doing a great job.

  7. #7
    Join Date
    Apr 2010
    Posts
    10

    facing issue in limit db size

    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.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •