Hi! The long-awaited MySQL/InnoDB-4.1.1 has been released. It is still labeled as alpha, because there are so many new features and bug fixes in it compared to 4.1.0.
IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1, you cannot downgrade any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. The biggest change for InnoDB in 4.1.1 is that you can now store each table and its indexes into its own file. This feature is called 'multiple tablespaces', because then each table is stored into its own tablespace. You can enable this feature by putting innodb_file_per_table in the [mysqld] section of my.cnf. Then InnoDB stores each table into its own file tablename.ibd in the database directory where the table belongs. This is like MyISAM does, but MyISAM divides the table to a data file tablename.MYD and the index file tablename.MYI. For InnoDB, both the data and the indexes are in the .ibd file. If you remove the line, then InnoDB creates tables in the ibdata files again. The old tables you had in the ibdata files before an upgrade to 4.1.1 remain there, they are not converted into .ibd files. InnoDB always needs the 'system tablespace', .ibd files are not enough. The system tablespace consists of the familiar ibdata files. InnoDB puts there its internal data dictionary and undo logs. You CANNOT FREELY MOVE .ibd files around, like you can MyISAM tables. This is because the table definition is stored in the InnoDB system tablespace, and also because InnoDB must preserve the consistency of transaction id's and log sequence numbers. You can move an .ibd file and the associated table from a database to another (within the same MySQL/InnoDB installation) with the familiar RENAME trick: RENAME TABLE olddatabasename.tablename TO newdatabasename.tablename; If you have a 'clean' backup of an .ibd file taken from the SAME MySQL/InnoDB installation, you can restore it to an InnoDB database with the commands: ALTER TABLE tablename DISCARD TABLESPACE; /* CAUTION: deletes the current .ibd file! */ <put the backup .ibd file to the proper place> ALTER TABLE tablename IMPORT TABLESPACE; 'Clean' in this context means: 1) There are no uncommitted modifications by transactions in the .ibd file. 2) There are no unmerged insert buffer entries to the .ibd file. 3) Purge has removed all delete-marked index records from the .ibd file. 4) mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file. You can make such a clean backup .ibd file with the following method. 1) Stop all activity from the mysqld server and commit all transactions. 2) Wait that SHOW INNODB STATUS\G shows that there are no active transactions in the database, and the 'main thread' of InnoDB is 'Waiting for server activity'. Then you can take a copy of the .ibd file. Another (non-free) method to make such a clean .ibd file is to 1) Use InnoDB Hot Backup to backup the InnoDB installation. 2) Start a second mysqld server on the backup and let it clean up the .ibd files. It is in the TODO to allow moving clean .ibd files also to another MySQL/InnoDB installation. That requires resetting of trx id's and log sequence numbers in the .ibd file. The changelog for InnoDB: * Multiple tablespaces now available for InnoDB. You can store each InnoDB type table and its indexes into a separate .ibd file into a MySQL database directory, into the same directory where the .frm file is stored. * The MySQL query cache now works for InnoDB tables also if AUTOCOMMIT=0, or the statements are enclosed inside BEGIN ... COMMIT. * Reduced InnoDB memory consumption by a few MB, if one sets the buffer pool size < 8 MB. * You can use raw disk partitions also in Windows. * This release contains all InnoDB bug fixes up to MySQL/InnoDB-4.0.16. * Some non-critical known bugs not yet fixed in this release. The fixes will probably come in 4.1.2. * A new my.cnf option innodb_locks_unsafe_for_binlog did not yet make it to 4.1.1. It will remove next-key locking in most cases, at the risk of breaking replication and binlog recovery in some cases. It is useful for eliminating transaction deadlocks. * A new InnoDB Hot Backup version 2.0 which supports multiple tablespaces in 4.1.1 is already ready, but the binaries not yet built. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
