• MySQL知识库 :: troubleshooting
  • I am Using Autoextend with InnoDB, I Added a New File to my Tablespace, Why am I Getting a "wrong table space size" Error?

  • Discussion

    When an InnoDB tablespace approaches its maximum size, it can be expanded by adding additional tablespace files. The last file in the list of tablespace files can be set to autoextend, growing in size as the tablespace grows, up to a maximum configurable size.

    When the autoextend file reaches its maximum size, it can be converted to a regular tablespace file and a new tablespace file can be added with the autoextend attribute.

    When adding tablespace files, if incorrect file size information is entered in the MySQL configuration file, the "wrong table space size" error can be encountered, producing an error like the one below:

    InnoDB: Error: data file .\\ibdata1 is of a different size
InnoDB: 640 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 1280 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_path 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!

    Solution

    First, ensure that all static sized tablespace files are accurately represented in your configuration file. When adding tablespace files to a tablespace that has an autoextend tablespace file, follow these directions:

    • If your last data file already was defined with the keyword autoextend, the procedure to edit my.cnf must take into account the size to which the last data file has grown. You have to look at the size of the data file, round the size downward to the closest multiple of 1024 * 1024 bytes (i.e., 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. Remember that only the last data file in the innodb_data_file_path can be specified as auto-extending.

    As an example, assume that the tablespace has just one auto-extending data file ibdata1:

    innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

    Suppose that this data file, over time, has grown to 988MB. Below is the configuration line after adding another auto-extending data file:

    innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

    When you add a new file to the tablespace, make sure that it does not exist. InnoDB will create and initialize it when you restart the server.