RE: innodb data file grew beyond the specified max size in config
When ever you use INNODB it must create a table space, something like a virtual file space or system for the data, that sits on top of the OS filesystem. By default it has allocated a certain size, change the value of innodb_data_file_path = ibdata1:500M:autoextend:max:2000M 500M back to the original size. The data will grow as large as the filesystem will allow it since the data is autoextended. Innodb datafiles contain the index and the data in one file for 4.0.x. This means that data will be the size of the columns + the indexes + some padding per row. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Hristo Chernev [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:14 AM To: [EMAIL PROTECTED] Subject: innodb data file grew beyond the specified max size in config Hi all I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE = INNODB. There was no errors in the err log and database is working ok. But after stopping mysql server It refuse to start again complaining: 041118 16:55:45 mysqld started 041118 16:55:45 [Warning] Asked for 1048576 thread stack, but got 126976 InnoDB: Error: auto-extending data file /data/mysql_4.1_ibdata/ibdata1 is of a different size InnoDB: 779008 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 128000 (relevant if non-zero) 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! 041118 16:55:45 [ERROR] Can't init databases 041118 16:55:45 [ERROR] Aborting 041118 16:55:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 041118 16:55:45 mysqld ended The server enlarged the initial tablespace file and made it 12GB regardless my settings of 2000M max for one ibdata file. It should split them into 2G pieces , correct? Is this a bug or I am missing something? I am running Mysql 4.1.7 on Linux Here is my config file: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data/mysql_4.1_data skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 server-id = 1 innodb_data_home_dir = /data/mysql_4.1_ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /data/mysql_4.1_iblog/ innodb_log_arch_dir = /data/mysql_4.1_iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- Hristo Chernev --- ÁÅÇÏËÀÒÅÍ ëè÷åí ÔÀÊÑ íîìåð çà âñåêè àáîíàò íà Mail.bg http://mail.bg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file grew beyond the specified max size in config
Hristo, thank you for the bug report. I broke the :autoextend:max:2000M feature when I added multiple tablespaces in 4.1.1. The bug is fixed in 4.1.8. Unfortunately, InnoDB does not automatically add 2000 MB files if you specify the max. You have to add additional ibdata files manually, as explained in tne manual. Best regards, Heikki 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 Order MySQL support from http://www.mysql.com/support/index.html .. Hi all I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE = INNODB. There was no errors in the err log and database is working ok. But after stopping mysql server It refuse to start again complaining: 041118 16:55:45 mysqld started 041118 16:55:45 [Warning] Asked for 1048576 thread stack, but got 126976 InnoDB: Error: auto-extending data file /data/mysql_4.1_ibdata/ibdata1 is of a different size InnoDB: 779008 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 128000 (relevant if non-zero) 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! 041118 16:55:45 [ERROR] Can't init databases 041118 16:55:45 [ERROR] Aborting 041118 16:55:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 041118 16:55:45 mysqld ended The server enlarged the initial tablespace file and made it 12GB regardless my settings of 2000M max for one ibdata file. It should split them into 2G pieces , correct? Is this a bug or I am missing something? I am running Mysql 4.1.7 on Linux Here is my config file: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data/mysql_4.1_data skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 server-id = 1 innodb_data_home_dir = /data/mysql_4.1_ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /data/mysql_4.1_iblog/ innodb_log_arch_dir = /data/mysql_4.1_iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- Hristo Chernev --- ÁÅÇÏËÀÒÅÍ ëè÷åí ÔÀÊÑ íîìåð çà âñåêè àáîíàò íà Mail.bg http://mail.bg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
