Re: MySQL on NetWare?
Hi Chris We're running MySQL on NetWare6.5 with good performance stability. About 20GB with InnoDB and a simple replication enabled (M - S). This database will grow up to 200GB in a year (with 400 active connections). We did tests and we liked. We'll set up another 2 servers to run a 100GB database (with 300 active connections) cluster-enabled. About the file size limit, your friend is wrong. You have 2 FS with NW5.0+: - TFS (old file system), but _does_ support 4GB. - NSS 64bit file system (yes, it support files 4GB+). And it's the default filesystem since NW6.0. Our databases runs only with InnoDB, but with 21 files x 1 GB. Personally I don't like to have big files in any OS. But if I want, I can create just one file with 21GB, no problem. An engineer at Novell told me about tests with a 4TB database. So, don't worry. We're fine. Regards, Eduardo - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 10:12 PM Subject: MySQL on NetWare? Hi all! I was wondering if anyone on the list currently runs MySQL on Novell NetWare. Any comments about performance relative to MySQL on similar hardware running a different OS? Additionally, I've been told (by someone of dubious authority) that NetWare has (and has had for a long time) a 2 GB file size limit. Is this correct? I was under the impression that the native NetWare FS was actually very impressive in all respects... Regards, Chris -- 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 in production
Is innodb stable enough to use un mass production environement ? Yes, it's. We use it on a 24x7 system (replicated), with 20GB w/no issues. We're using 4.0.16 on NetWare6.5. Eduardo - Original Message - From: Nicolas Ross [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:13 PM Subject: Innodb in production Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- 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 in production
Hi, How are you doing backups??? Since it's replicated, I stop the slave and copy the entire database directory. Then, I restart the server and the slave will get all the queries from the master... Works very well! Eduardo - Original Message - From: Arnoldus Th.J. Koeleman [EMAIL PROTECTED] To: 'Eduardo D Piovesam' [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:38 PM Subject: RE: Innodb in production How are you doing backups??? -Original Message- From: Eduardo D Piovesam [mailto:[EMAIL PROTECTED] Sent: Monday, December 15, 2003 10:31 AM To: Nicolas Ross; [EMAIL PROTECTED] Subject: Re: Innodb in production Is innodb stable enough to use un mass production environement ? Yes, it's. We use it on a 24x7 system (replicated), with 20GB w/no issues. We're using 4.0.16 on NetWare6.5. Eduardo - Original Message - From: Nicolas Ross [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:13 PM Subject: Innodb in production Hi ! Our db server has about 140+ db's for a total of about 1.5 gigs of data. Some while ago, for a specific DB, I did testing using transaction tables with bdb. This was a bad experience. I ran into some problems and I had to convert back to myisam. One thing I don't like about innobd and bdb is that all the data of all db's are all stored in one (or many) file in the base directory compared to myisam tables where data resides in the db directory. Is innodb stable enough to use un mass production environement ? Nicolas -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication error (1236 - impossible position on slave)
Hi, We have 2 MySQL 4.0.16 with replication enabled (M - S), only InnoDB tables. We're getting Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) in the slave. The master server had a problem... when it came back (with success) the log have: 031210 15:19:24 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 4169000545 InnoDB: Doing recovery: scanned up to log sequence number 3 4171205097 031210 15:19:25 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 45866, file name ./SV07-bin.008 031210 15:19:43 InnoDB: Flushing modified pages from the buffer pool... 031210 15:19:51 InnoDB: Started But in the slave log: 031210 17:02:36 Slave: connected to master '[EMAIL PROTECTED]:xxx',replication resumed in log 'SV07-bin.008' at position 45866 031210 17:02:36 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 031210 17:02:36 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 031210 17:02:36 Slave I/O thread exiting, read up to log 'SV07-bin.008', position 45866 With show master status (on master) I get: File = SV07-bin.009 Position = 456371766 And with show slave status (on slave) I get: Master_log_file = SV07-bin.008 Read_master_log_pos = 45866 Relay_log_file = SV08-relay-bin.011 Relay_log_pos = 458600084 Relay_master_log = SV07-bin.008 Slave_io_running = no Slave_sql_running = yes Exec_master_log_pos = 45866 Relay_log_space = 458600084 Is SV07-bin.008 corrupted? How can I debug? Thanks, Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication : blocking updates to slave
Hi, We're using replication (1 master + 1 slave), and we want to block updates / deletes, issued by the users, to the slave. Because sometimes they get confused and use the slave as the master... got it? Any clue? Thanks, Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication : blocking updates to slave
Hi Fred, thanks for reply. But we want to replicate the mysql database... we want a exact copy of the master. Is there another way? Thanks, Eduardo - Original Message - From: Fred van Engen [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 10:32 AM Subject: Re: Replication : blocking updates to slave Eduardo, On Wed, Dec 03, 2003 at 10:25:28AM -0200, Eduardo D Piovesam wrote: We're using replication (1 master + 1 slave), and we want to block updates / deletes, issued by the users, to the slave. Because sometimes they get confused and use the slave as the master... got it? Give their account different GRANTs on the master than on the slave(s) and make sure that GRANTs (the `mysql` database) are not replicated from the master to the slaves. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication : blocking updates to slave
Hi Duncan, Great tip! It's not exactly what I was looking for (block only updates, and permit selects), but it's the best option... Removing the mysql db from replicating, and then granting only selects to the users, will cause more work to us when the master crash... we'll need to rebuild all the priveleges on the slave. Thanks, Eduardo - Original Message - From: Duncan Hill [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 11:00 AM Subject: Re: Replication : blocking updates to slave On Wednesday 03 December 2003 12:56, Eduardo D Piovesam wrote: Hi Fred, thanks for reply. But we want to replicate the mysql database... we want a exact copy of the master. Is there another way? Firewall the users from the slave server? If they're not meant to use it, don't let them see it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication : blocking updates to slave
Hi Fred, You would just be missing the mysql.* tables and those are only used for permission related stuff. Permissions are exactly what you want to be different between slaves and master. We want to have a full copy of our database, to put it online again if something goes wrong with the master... Thanks, Eduardo - Original Message - From: Fred van Engen [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 11:21 AM Subject: Re: Replication : blocking updates to slave Eduardo, On Wed, Dec 03, 2003 at 10:56:56AM -0200, Eduardo D Piovesam wrote: But we want to replicate the mysql database... we want a exact copy of the master. You would just be missing the mysql.* tables and those are only used for permission related stuff. Permissions are exactly what you want to be different between slaves and master. Is there another way? Not that I know of. I just looked at the startup options but couldn't find anything for this purpose. Regards, Fred. - Original Message - From: Fred van Engen [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 10:32 AM Subject: Re: Replication : blocking updates to slave Eduardo, On Wed, Dec 03, 2003 at 10:25:28AM -0200, Eduardo D Piovesam wrote: We're using replication (1 master + 1 slave), and we want to block updates / deletes, issued by the users, to the slave. Because sometimes they get confused and use the slave as the master... got it? Give their account different GRANTs on the master than on the slave(s) and make sure that GRANTs (the `mysql` database) are not replicated from the master to the slaves. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- 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: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hello Heikki, thanks for your reply. It was just my curiosity, with the MyISAM concept it's good for the OS, because it'll cache only the tables most useds, and not all the tablespace file. And how to administer the multiple tablespace support? I'm worried in the space unused by some tables, example: - Multiple tablespace support active - Minium tablespace size is 500MB (in the my.cnf) - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and table2 have 600MB Then you'll have: - table1.ibd with 10kb or 500MB? - 2 x table2.ibd files with 500MB each or - Just one table2.ibd with 600MB or 1GB? Thanks again, Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:55 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- 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: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Heikki, Thanks for clarifying, and as you see, I'm new to MySQL (came from Oracle). I'll read all the docs first (I'm doing it right now). ;) We're evaluating MySQL (InnoDB) on NetWare65, we'll move all our Oracle servers (also, on NetWare) to it, and then start testing. Currently we have only 2 problems : view + stored procedures, but we can do some workaround because we have only a few of them. Regards, Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Eduardo D Piovesam [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:23 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, - Alkuperäinen viesti - Lähettäjä: Eduardo D Piovesam [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] Lähetetty: Friday, October 24, 2003 6:17 PM Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hello Heikki, thanks for your reply. It was just my curiosity, with the MyISAM concept it's good for the OS, because it'll cache only the tables most useds, and not all the tablespace file. but InnoDB only caches the most used 16 kB pages in the tablespaces to the buffer pool. MyISAM does not cache .MYD file contents at all, but relies on the OS file cache for them. The MyISAM key_buffer caches .MYI contents. And how to administer the multiple tablespace support? I'm worried in the space unused by some tables, example: - Multiple tablespace support active - Minium tablespace size is 500MB (in the my.cnf) The minimum size for an .ibd file is 64 kB. It grows page by page to 1 MB, up to 32 MB in 1 MB chunks, and after that in 8 MB chunks. .ibd files are always 'auto-extending' tablespaces. You do not specify their size in my.cnf. - 2 tables in the InnoDB database, table1 have only 10kb (few rows), and table2 have 600MB Then you'll have: - table1.ibd with 10kb or 500MB? - 2 x table2.ibd files with 500MB each or - Just one table2.ibd with 600MB or 1GB? Thanks again, Eduardo Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 10:55 AM Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Eduardo, to make the user interface simple, I decided to take the table per file approach. Each .ibd file is internally a 'tablespace'. The simple approach I chose is similar to how MyISAM now works. I thought it would be nice for current MySQL users. In Oracle, one can store several tables into a single named tablespace, and can also split indexes and data of a single table to separate tablespaces. Nothing prevents adding those features to InnoDB, too. It just requires new syntax in CREATE TABLE to specify these options. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables .. From: Eduardo D Piovesam ([EMAIL PROTECTED]) Subject: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 View this article only Newsgroups: mailing.database.myodbc Date: 2003-10-23 14:43:28 PST (Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo -- 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: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
Hello Heikki, I didn't understand the concept of tablespaces utilized, - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 12:00 PM Subject: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hi! InnoDB is a MySQL table type which provides transactions, row-level locking, non-locking consistent SELECT (multiversioned concurrency control), foreign key constraints, and a non-free hot backup tool for backing up InnoDB tables. InnoDB is included in all MySQL-4.0 and 4.1 downloads, and also in the MySQL Pro commercial, non-GPL MySQL license. Release 4.0.16 is a bugfix release of the stable 4.0 branch. There are a few known outstanding bugs in InnoDB-4.0.16, but their fixing has been delayed, because we are allocating all free resources to preparing the upcoming MySQL-4.1.1 release. --- A sneak peek of MySQL-4.1.1: 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 public BitKeeper source tree of 4.1 now supports multiple tablespaces for InnoDB. You can enable them with the line 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. 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. --- InnoDB changelog for 4.0.16: * Fixed a bug: contrary to what was said in the manual, in a locking read InnoDB set two record locks if a unique exact match search condition was used on a multi-column unique key. For a single column unique key it worked right. * Fixed a bug: if one used the rename trick #sql... - rsql... described in section 15.1 of http://www.innodb.com/ibman.html to recover a temporary table, InnoDB asserted in row_mysql_lock_data_dictionary(). * There are several outstanding non-critical bugs reported in the MySQL bugs database. Their fixing has been delayed, because resources are allocated to the upcoming 4.1.1 release. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- 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: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1
(Sorry for the last email, it's not complete). Hello Heikki, Sorry, but I didn't understand the concept of tablespace applied. It's different from Oracle, right? AFAIK, tablespace is utilized to logically group tables into one (or more) files. And to group indexes into another files... But you said that the each table (with its indexes) will be in one file... is there an reason? Is it better than split tables and indexes? Thank you. Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 12:00 PM Subject: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Hi! InnoDB is a MySQL table type which provides transactions, row-level locking, non-locking consistent SELECT (multiversioned concurrency control), foreign key constraints, and a non-free hot backup tool for backing up InnoDB tables. InnoDB is included in all MySQL-4.0 and 4.1 downloads, and also in the MySQL Pro commercial, non-GPL MySQL license. Release 4.0.16 is a bugfix release of the stable 4.0 branch. There are a few known outstanding bugs in InnoDB-4.0.16, but their fixing has been delayed, because we are allocating all free resources to preparing the upcoming MySQL-4.1.1 release. --- A sneak peek of MySQL-4.1.1: 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 public BitKeeper source tree of 4.1 now supports multiple tablespaces for InnoDB. You can enable them with the line 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. 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. --- InnoDB changelog for 4.0.16: * Fixed a bug: contrary to what was said in the manual, in a locking read InnoDB set two record locks if a unique exact match search condition was used on a multi-column unique key. For a single column unique key it worked right. * Fixed a bug: if one used the rename trick #sql... - rsql... described in section 15.1 of http://www.innodb.com/ibman.html to recover a temporary table, InnoDB asserted in row_mysql_lock_data_dictionary(). * There are several outstanding non-critical bugs reported in the MySQL bugs database. Their fixing has been delayed, because resources are allocated to the upcoming 4.1.1 release. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: Multiple tablespace + subqueries
Heikki, Great! Thank you! Regards, Eduardo - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 26, 2003 4:31 PM Subject: Re: Multiple tablespace + subqueries Eduardo, - Original Message - From: Eduardo D Piovesam [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, September 25, 2003 10:45 PM Subject: Multiple tablespace + subqueries Hello all, We want to use MySQL (InnoDB) for a corporate/non-stop environment, but we really want/need multiple tablespace support + subqueries. When will both be available for a production environment? Only in 4.1.x? Any date? multiple tablespaces already work in my private 4.1 tree and pass heavy stress tests. They will be included in MySQL-4.1.1. I guess 4.1.1 will be released around November 15th, 2003. People often start using MySQL in production when it is labeled beta. Either 4.1.1 or 4.1.2 will get that label. Thank you! Eduardo Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- 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]
Multiple tablespace + subqueries
Hello all, We want to use MySQL (InnoDB) for a corporate/non-stop environment, but we really want/need multiple tablespace support + subqueries. When will both be available for a production environment? Only in 4.1.x? Any date? Thank you! Eduardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
