MySQL InnoDB table row access
Hi,
I want to access data from an InnoDB table. I know that I can do this using
the corresponding handler and ha_rnd_next() or ha_index_next().
My problem is that the original MySQL code is outperforming my
implementation even on simple projection queries, even though I use the same
functions.
The entry point for my own execution is in the sql_parce.cc file in the
function int mysql_execute_command(THD *thd).
~line 2600 res= execute_sqlcom_select(thd, all_tables);
database: http://dev.mysql.com/doc/employee/en/index.html
query: select title from titles;
my code:
while (!tables-table-file-ha_rnd_next(tables-table-record[0]))
{
result-send_data(thd-lex-select_lex.item_list);
}
handler-ha_index_or_rnd_end();
return;
result is of the type select_send
To evaluate the query, using this simple loop, takes around 20 percent
longer than the original MySQL code. I have used a debugger to step through
the code, but the code being executed
seems to be the same. That's why I think MySQL is doing some kind of
optimization on the table but I can't figure out where. I have also tried to
let my code run later in the evaluation process
of MySQL but the result stays the same.
Best regards,
Tobias Krueger
MySQL InnoDB memory performance tuning
Hi, I have a fairly small (data dir is 1.2GB) InnoDB database managed by MySQL 5.4.3-beta on an 8-core x86_64 Linux box with 16GB RAM. I'd like to use as much of the memory as possible, but despite specifying (e.g.) --innodb-buffer-pool-size=30, mysql only ever takes up 374M of resident memory (though the virtual memory totals about 3.5GB). Is there another setting that I should consider tweaking which will actually fully utilize the allotted resources? A (very) rough comparison: the hsqldb main-memory Java RDBMS can run jTPCC an order of magnitude faster than mysql. I know hsqldb lacks durability, but only a WAL would be needed, which (with group commits) is characterized by largely sequential writes. Ideally, I can bring mysql to this point -- the database is stored in memory, with only a WAL producing sequential writes to disk, as opposed to incurring random seeks due to buffer page flushes. OTOH, it may be possible that the DB is already entirely in memory, and the performance difference is due entirely to mysql runtime overheads. Either way, explanations/hints would be much appreciated. Thanks in advance. -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
HOW TO Backup a mysql innodb on windows?
Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Thanks, Nestor
Re: HOW TO Backup a mysql innodb on windows?
Sorry, I meant to say that the backup DB does not look the same as the original DB. Maybe it has to do with what Todd and Mychael mentioned. The percona tool does not work on Windows OS. I could not find a windows executable. Thanks, Nestor :-) On Mon, Sep 14, 2009 at 9:13 AM, Néstor rot...@gmail.com wrote: I tried this but when I upload the backup it just does not look the same. Thanks, Nestor :- On Mon, Sep 14, 2009 at 8:04 AM, prathiman...@vsnl.net wrote: Use sqlyog its a freeware Sent from my BlackBerry® on Reliance Mobile, India's No. 1 Network. Go for it! -Original Message- From: Néstor rot...@gmail.com Date: Mon, 14 Sep 2009 07:44:25 To: mysql@lists.mysql.com Subject: HOW TO Backup a mysql innodb on windows? Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Thanks, Nestor
Re: HOW TO Backup a mysql innodb on windows?
If I may, If you have foreign keys on your InnoDB, you can still import your data to MyISAM but foreign keys will be lost. Otherwise, the data will load just fine. - michael dykman On Mon, Sep 14, 2009 at 11:14 AM, Todd Lyons tly...@ivenue.com wrote: On Mon, Sep 14, 2009 at 7:44 AM, Néstor rot...@gmail.com wrote: Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? You can also use the free tool from Percona which can backup innodb tables without having to shutdown or read lock the database (but it does read lock the database while copying *.frm files for Innodb tables and any MyISAM tables). http://www.percona.com/docs/wiki/percona-xtrabackup:start If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Depends. If you use foreign keys in innodb, then you cannot import that into myisam because myisam does not support foreign keys. If you do not use foreign keys, it should work. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HOW TO Backup a mysql innodb on windows?
I did it again and using sqlyog did work. I also found out that the tool that comes with mysql admin 1.1 also has a backup tool that works. Thanks to all, Nestor :-) On Mon, Sep 14, 2009 at 9:16 AM, Néstor rot...@gmail.com wrote: Sorry, I meant to say that the backup DB does not look the same as the original DB. Maybe it has to do with what Todd and Mychael mentioned. The percona tool does not work on Windows OS. I could not find a windows executable. Thanks, Nestor :-) On Mon, Sep 14, 2009 at 9:13 AM, Néstor rot...@gmail.com wrote: I tried this but when I upload the backup it just does not look the same. Thanks, Nestor :- On Mon, Sep 14, 2009 at 8:04 AM, prathiman...@vsnl.net wrote: Use sqlyog its a freeware Sent from my BlackBerry® on Reliance Mobile, India's No. 1 Network. Go for it! -Original Message- From: Néstor rot...@gmail.com Date: Mon, 14 Sep 2009 07:44:25 To: mysql@lists.mysql.com Subject: HOW TO Backup a mysql innodb on windows? Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Thanks, Nestor
Re: HOW TO Backup a mysql innodb on windows?
I tried this but when I upload the backup it just does not look the same. Thanks, Nestor :- On Mon, Sep 14, 2009 at 8:04 AM, prathiman...@vsnl.net wrote: Use sqlyog its a freeware Sent from my BlackBerry® on Reliance Mobile, India's No. 1 Network. Go for it! -Original Message- From: Néstor rot...@gmail.com Date: Mon, 14 Sep 2009 07:44:25 To: mysql@lists.mysql.com Subject: HOW TO Backup a mysql innodb on windows? Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Thanks, Nestor
Re: HOW TO Backup a mysql innodb on windows?
On Mon, Sep 14, 2009 at 7:44 AM, Néstor rot...@gmail.com wrote: Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? You can also use the free tool from Percona which can backup innodb tables without having to shutdown or read lock the database (but it does read lock the database while copying *.frm files for Innodb tables and any MyISAM tables). http://www.percona.com/docs/wiki/percona-xtrabackup:start If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Depends. If you use foreign keys in innodb, then you cannot import that into myisam because myisam does not support foreign keys. If you do not use foreign keys, it should work. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HOW TO Backup a mysql innodb on windows?
On Mon, Sep 14, 2009 at 8:28 AM, Michael Dykman mdyk...@gmail.com wrote: If I may, If you have foreign keys on your InnoDB, you can still import your data to MyISAM but foreign keys will be lost. Otherwise, the data will load just fine. Very good point. My comment was based on the possibly erroneous assumption that if you were using foreign keys, when you switched to MyISAM you wanted to continue to use foreign keys. Without the op saying one way or the other, Michael's answer is more correct than mine. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HOW TO Backup a mysql innodb on windows?
A mysqldump will work just fine. By default, that dump is going to explicitly specify the table type .. you will have to edit it if you want to import to MyISAM. - michael dykman On Mon, Sep 14, 2009 at 10:44 AM, Néstor rot...@gmail.com wrote: Maybe one of you experts know the answer. I have a Innodb database that I want to back up. Is there a free tool to do this? mysqlhotbackup is a paid tool, is that the only one available? If I do a mysqldump of the innodb databse, will I be avail to uploaded into a myisam database and will it work? Thanks, Nestor -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql innodb commandline check and repair
I have Mysql 5.0.45 using innodb tables. Occasionally, I get corrupted tables. I can go into Mysql administrator gui and see the bad table and I can repair the index or whatever is wrong from the gui. I need a command line way to periodically detect for issues and if it finds one the I need a command line to repair a specific table. Thank you for your assistance, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql innodb commandline check and repair
Bryan Cantwell wrote: I have Mysql 5.0.45 using innodb tables. Occasionally, I get corrupted tables. I can go into Mysql administrator gui and see the bad table and I can repair the index or whatever is wrong from the gui. I need a command line way to periodically detect for issues and if it finds one the I need a command line to repair a specific table. Try mysqlcheck. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql InnoDB table creation problem
Depending on the version you use MySQL will see a definition of varchar(25) as 25 bytes or 25 characters. I believe this changed from 4.1 to 5.0 respectively but I am not sure. THis could be the root of the problem Boyd CONFIDENTIALITY NOTICE: This email attached documents may contain confidential information. All information is intended only for the use of the named recipient. If you are not the named recipient, you are not authorized to read, disclose, copy, distribute or take any action in reliance on the information and any action other than immediate delivery to the named recipient is strictly prohibited. If you have received this email in error, do not read the information and please immediately notify sender by telephone to arrange for a return of the original documents. If you are the named recipient you are not authorized to reveal any of this information to any other unauthorized person. If you did not receive all pages listed or if pages are not legible, please immediately notify sender by phone.
Re: mysql InnoDB table creation problem
On 3/26/07, Anil D [EMAIL PROTECTED] wrote: Varchar = 0 bytes I don't think this is right, see below. Charset used: UTF8 UTF8 means that some characters may be two bytes, see below. Note: When consider even the size Varchar(m) = m+1 bytes, the size of row has reached 35,000 bytes. Here is my guess, but I can't say for sure because I'm still a little new to this all. The row length doesn't exclude VARCHAR, only TEXT and BLOBs. If you take that into account as well as your encoding requiring two bytes per character, then a VARCHAR(50) is going to take up (50*2)+4 = 104 bytes. Either way, since mysql thinks it's too large, it must be to large. I would suggest converting several VARCHAR fields to TEXT and see what happens. -Josh
[ANNOUNCE] dumpster :: dumps out all related records in a mySQL InnoDB database
Hey all. Well I just finished my first version of a little tool I have affectionately dubbed dumpster. I do use my own SQL wrapper functions, but they should map fairly cleanly to a search and replace for the stock PHP mysql_*() ones, or your own ones. Mad props to Peter Brawley [EMAIL PROTECTED] for the initial SQL statement to get the FK constraints. If someone can point me at how to get the information I need to fix that bug, that'd be swell. ÐÆ5ÏÐ -- This script attempts to generate all the SQL statements needed to archive a snapshot of a single 'thing'. For example, it can harvest all records related to a given user. (This only works for InnoDB tables that utilize proper FK constraints) Usage: ./dumpster.php --database mydb --table users --id 1 [--delete] user_1.sql Then later simply mysql --force -u root mydb user_1.sql to put the 'user' back --help, -help, -h, or -? options, to get this help. --databasethe name of the database to use. --table the name of the table to use in the database --id the ID that joins all these tables together in the database --FKonly only show the Foreign Key list and exit. --debug to turn on output debugging. --version to return the version of this file. --delete deletes the record as it is output (in 'debug' mode this outputs only, no action). KNOWN ISSUE: if a column is defined as ON DELETE SET NULL, then there's a better than average chance that it might get NULL'd by a DELETE before it, therefore we won't be able to clean up some records properly as their FK ID is now NULL. catch22. :-| There is probably a way to find out which FKs have this particular constraint action and then we could save off their PK in an array and loop through them at the end I think? http://daevid.com/examples/dumpster.tgz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backups with MySQL/InnoDB
On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) -- David Hillman LiveText, Inc 1.866.LiveText x235
Re: Backups with MySQL/InnoDB
On 5/8/06, David Hillman [EMAIL PROTECTED] wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). -- David Hillman LiveText, Inc 1.866.LiveText x235 -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backups with MySQL/InnoDB
-Original Message- From: Daniel da Veiga [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 1:55 PM To: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On 5/8/06, David Hillman [EMAIL PROTECTED] wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). -- David Hillman LiveText, Inc 1.866.LiveText x235 In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. It would then be really sweet to be able to say 'restore a full backup of x database as of April 2, 2005 at 8:42 am' and have it create a new instance on a user defined port, then restore the closest previous full, then apply the binlogs up to the correct point in time. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backups with MySQL/InnoDB
Hi Greg, Maybe similar features to that of bacula (my current backup software of choice for my wifes business servers). This is a very comprehensive open source solution that has many of the features requested below. eg. multiple servers, pooling, aging etc. It is a good example of what my own requirements would be. Is the intention to have a MySQL type plugin? eg. will it have an api that will be open to other backup solutions being able to utilise what will be written? It would be nice to be able to utilise a standard XBSA solution giving access to the database from any one of the major enterprise backup solutions, eg. Legato Networker, Veritas Netbackup, HP Dataprotector etc. etc. This would allow an enormously simple and straightforward integration into many of the existing corporate solutions that exist around the world. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Duzenbury, Rich [mailto:[EMAIL PROTECTED] Sent: Tuesday, 9 May 2006 6:39 AM To: mysql@lists.mysql.com Subject: RE: Backups with MySQL/InnoDB -Original Message- From: Daniel da Veiga [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 1:55 PM To: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On 5/8/06, David Hillman [EMAIL PROTECTED] wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). -- David Hillman LiveText, Inc 1.866.LiveText x235 In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. It would then be really sweet to be able to say 'restore a full backup of x database as of April 2, 2005 at 8:42 am' and have it create a new instance on a user defined port, then restore the closest previous full, then apply the binlogs up to the correct point in time. Thanks. Regards, Rich -- 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: Backups with MySQL/InnoDB
On Sunday, May 07, 2006 6:14 PM, Greg 'groggy' Lehey wrote: On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. On Monday, 8 May 2006 at 8:15:17 -0700, paul rivers wrote: I would suggest looking at the functionality of Microsoft SQL Server or Sybase backups. It's extremely nice from an admin point of view, and certainly covers all of what Robert mentions. Yes, from an administrative perspective we're trying to make something that feels intuitive, and particularly the Microsoft approach seems a good starting point for this aspect. If you have a pet feature not discussed below, let me know. On Sunday, May 07, 2006 9:30 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. We're certainly planning incremental backups, but they probably won't be in the first release. We don't plan any size limitations (this is a streaming backup), and it will be transaction-safe (statement-safe for MyISAM) and online (i.e. concurrently with normal processing). Compression is a different issue. We haven't considered it so far, and though it's desirable, I don't see why we can't get an external program to do this (bzip2 or gzip, for example; the choice depends on your personal tradeoffs between time and space). On Monday, 8 May 2006 at 15:55:07 -0300, Daniel da Veiga wrote: On 5/8/06, David Hillman wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). The component we're working on at the moment is the streaming online backup API. Basically you issue an SQL command BACKUP DATABASE, and it outputs a data stream that you can point at your tape drive, to a disk, or across the network to something like VERITAS. We're very conscious of the multiple server issue, but it's going to have to wait until we can back up one server properly. Dealing with replication is a special case of multiple servers, so that will wait too. We will backup the binlog, though, and our current thinking is to use it for incremental backups, though this may change. On Monday, 8 May 2006 at 16:09:23 -0500, Rich Duzenbury wrote: In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. This is also another aspect of the backup solution we're working on. I'll put it down on the wish list. On Tuesday, 9 May 2006 at 7:18:28 +1000, David Logan wrote: Hi Greg, Maybe similar features to that of bacula (my current backup software of choice for my wifes business servers). This is a very comprehensive open source solution that has many of the features requested below. eg. multiple servers, pooling, aging etc. It is a good example of what my own requirements would be. I don't know Bacula, but I suppose I should investigate it. Do you know anybody in the project? Is the intention to have a MySQL type plugin? eg. will it have an api that will be open to other backup solutions being able to utilise what will be written? Yes, this is very much the intention. It's the API that we're defining now. We've been talking to Zmanda (http://www.zmanda.com/), who are interested in extending amanda with MySQL plugins, and we'd be more than happy for others to join in. It would be nice to be able to utilise a standard XBSA solution giving access to the database from any one of the major enterprise backup solutions, eg. Legato Networker, Veritas Netbackup, HP
Backups with MySQL/InnoDB
What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency.
Re: Backups with MySQL/InnoDB
On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ pgpTiC6AScuNm.pgp Description: PGP signature
RE: Backups with MySQL/InnoDB
Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. -Original Message- From: Greg 'groggy' Lehey [mailto:[EMAIL PROTECTED] Sent: Sunday, May 07, 2006 6:14 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On Sunday, 7 May 2006 at 9:27:31 -0700, Robert DiFalco wrote: What are people doing for backups on very large MySQL/InnoDB databases? Say for databases greater than 200 GB. Curious about the backup methods, procedures, and frequency. A second question, but not for the first time: how would you *like* to do backups if you had the choice? We're currently in the final stages of the design of an online backup solution, and in the near future I'll publish the specs. I won't mention them now to avoid influencing you, but now's the time to speak up if you want something specific. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB Row insert Calculation
Resend, Anybody please give me information about different insert performance between MySQL 5.0.18 and MySQL 4.1.18 as my posting at http://forums.mysql.com/read.php?22,74279,74279 Thank your Heikki Please see my testing result on MySQL Forum http://forums.mysql.com/read.php?22,74279,74279#msg-74279 I need explanation about this issue :) Heikki Tuuri wrote: Ady, - Original Message - From: Ady Wicaksono [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 06, 2006 5:32 PM Subject: MySQL InnoDB Row insert Calculation With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? I am assuming that you perform a COMMIT after each insert. If the computer does not have a battery-backed disk cache, then the commit speed is limited by the disk rotation speed, which is at most 250 rotations per second nowadays. If the computer does have a battery-backed disk cache (or you take the risk and use a non-battery-backed cache), then the speed is limited by the CPU usage, and for big tables by the disk seek time. If the insertion is CPU-bound, you normally can insert 3000 rows per second, or more. For a big table, several gigabytes or more, inserts to secondary indexes may require disk seeks, limiting the maximum insert speed to 100 rows per second, or less. Best regards, Heikki Oracle Corp./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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL InnoDB Row insert Calculation
With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB Row insert Calculation
Ady, - Original Message - From: Ady Wicaksono [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 06, 2006 5:32 PM Subject: MySQL InnoDB Row insert Calculation With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? I am assuming that you perform a COMMIT after each insert. If the computer does not have a battery-backed disk cache, then the commit speed is limited by the disk rotation speed, which is at most 250 rotations per second nowadays. If the computer does have a battery-backed disk cache (or you take the risk and use a non-battery-backed cache), then the speed is limited by the CPU usage, and for big tables by the disk seek time. If the insertion is CPU-bound, you normally can insert 3000 rows per second, or more. For a big table, several gigabytes or more, inserts to secondary indexes may require disk seeks, limiting the maximum insert speed to 100 rows per second, or less. Best regards, Heikki Oracle Corp./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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB Row insert Calculation
Heikki Please see my testing result on MySQL Forum http://forums.mysql.com/read.php?22,74279,74279#msg-74279 I need explanation about this issue :) Heikki Tuuri wrote: Ady, - Original Message - From: Ady Wicaksono [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 06, 2006 5:32 PM Subject: MySQL InnoDB Row insert Calculation With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? I am assuming that you perform a COMMIT after each insert. If the computer does not have a battery-backed disk cache, then the commit speed is limited by the disk rotation speed, which is at most 250 rotations per second nowadays. If the computer does have a battery-backed disk cache (or you take the risk and use a non-battery-backed cache), then the speed is limited by the CPU usage, and for big tables by the disk seek time. If the insertion is CPU-bound, you normally can insert 3000 rows per second, or more. For a big table, several gigabytes or more, inserts to secondary indexes may require disk seeks, limiting the maximum insert speed to 100 rows per second, or less. Best regards, Heikki Oracle Corp./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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql InnoDB
Hi! - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, February 14, 2006 5:50 PM Subject: MySql InnoDB Hi, I'v installed MySql on my machine and created a new tables. when i open some table to alter it,i see in the COMMENT textbox: InnoDB free: 3072 kB what doe's it mean? it's mean that i only have 3072kb free for a given table or what? If you are not using innodb_file_per_table, then the value 3072 kB means that in ibdata files you have that amount of space free for adding more data to your tables. To be precise, there are three 1 MB 'extents' available for extending your tables. In addition, there may be individual 16 kB 'fragment pages' available, but they are not listed in the printout, for simplicity. If you are using innodb_file_per_table, then the value 3072 kB means that you have that much free space in the .ibd file of that table, for extending the table with those 1 MB extents. Thank's a lot. Best regards, Heikki Oracle Corp./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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Innodb Crash on 2 concurrent select
On Wednesday, 16 November 2005 at 20:40:35 +0700, Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? ... InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This is obviously a bug. I've just checked the bug database, but I don't see a report on it yet. Could you please enter one? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Innodb Crash on 2 concurrent select
Ok greg, I'll report this bug Thx Greg 'groggy' Lehey wrote: On Wednesday, 16 November 2005 at 20:40:35 +0700, Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? ... InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This is obviously a bug. I've just checked the bug database, but I don't see a report on it yet. Could you please enter one? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Innodb Crash on 2 concurrent select
I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? Log file : 051116 20:27:22InnoDB: Assertion failure in thread 2366216768 in file srv0srv.c line 1873 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=12578816 max_used_connections=31 max_connections=910 threads_connected=11 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x8d0992ec, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8115587 0x4004a618 (nil) 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac e. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 051116 20:27:23 mysqld restarted InnoDB: ## InnoDB: WARNING! InnoDB: The log sequence number in ibdata files is higher InnoDB: than the log sequence number in the ib_logfiles! Are you sure InnoDB: you are using the right ib_logfiles to start up the database? InnoDB: Log sequence number in ib_logfiles is 33 1628260918, log InnoDB: sequence numbers stamped to ibdata file headers are between InnoDB: 0 0 and 192 460914688. InnoDB: ## 051116 20:27:24 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051116 20:27:24 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 33 1628260918. InnoDB: Doing recovery: scanned up to log sequence number 33 1628260918 051116 20:27:24 InnoDB: Flushing modified pages from the buffer pool... 051116 20:27:24 InnoDB: Started; log sequence number 33 1628260918 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.9-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Innodb Crash on 2 concurrent select
Dear All It happens after these condition InnoDB: ## Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 1103972416 has waited at ../include/btr0btr.ic line 28 for 369.00 seconds the semaphore: S-lock on RW-latch at 0x88cdd6b8 created in file buf0buf.c line 469 a writer (thread id 1105434432) has reserved it in mode exclusive number of readers 0, waiters flag 1 Last time read locked in file buf0flu.c line 562 Last time write locked in file buf0buf.c line 1674 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? Log file : 051116 20:27:22InnoDB: Assertion failure in thread 2366216768 in file srv0srv.c line 1873 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=12578816 max_used_connections=31 max_connections=910 threads_connected=11 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1782208 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x8d0992ec, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8115587 0x4004a618 (nil) 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trac e. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 051116 20:27:23 mysqld restarted InnoDB: ## InnoDB: WARNING! InnoDB: The log sequence number in ibdata files is higher InnoDB: than the log sequence number in the ib_logfiles! Are you sure InnoDB: you are using the right ib_logfiles to start up the database? InnoDB: Log sequence number in ib_logfiles is 33 1628260918, log InnoDB: sequence numbers stamped to ibdata file headers are between InnoDB: 0 0 and 192 460914688. InnoDB: ## 051116 20:27:24 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051116 20:27:24 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 33 1628260918. InnoDB: Doing recovery: scanned up to log sequence number 33 1628260918 051116 20:27:24 InnoDB: Flushing modified pages from the buffer pool... 051116 20:27:24 InnoDB: Started; log sequence number 33 1628260918 /usr/sbin/mysqld-max: ready for connections. Version: '4.1.9-Max' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-5.0.7 has been released
Hi!
InnoDB is the MySQL table type that supports foreign key constraints,
transactions, two-phase commit in XA, row-level locking, non-locking
consistent read (MVCC), all four SQL-1992 isolation levels of transactions,
multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a
non-free hot online backup tool.
MySQL-5.0.7 is a bugfix release of the 5.0 branch. The release is still
labeled as beta, because 5.0 contains many new features, and more real-world
testing by MySQL's millions of users is needed.
You can download MySQL-5.0.7 from
http://dev.mysql.com/downloads/mysql/5.0.html
Changes in features:
* In stored procedures and functions, InnoDB no longer takes full explicit
table locks for every involved table. Only `intention' locks are taken,
similar to those in the execution of an ordinary SQL statement. This greatly
reduces the number of deadlocks.
Bugs fixed:
* Do very fast shutdown only if innodb_fast_shutdown=2, but wait for threads
to exit and release allocated memory if innodb_fast_shutdown=1. Starting
with MySQL/InnoDB 5.0.5, InnoDB would do brutal shutdown also when
innodb_fast_shutdown=1. (Bug #9673)
* Fixed InnoDB: Error: stored_select_lock_type is 0 inside ::start_stmt()!
in a stored procedure call if innodb_locks_unsafe_for_binlog was set in
my.cnf. (Bug #10746)
* Fixed a duplicate key error that occurred with REPLACE in a table with an
AUTO-INC column. (Bug #11005)
Upgrading from 4.1:
* MyISAM and InnoDB tables created with DECIMAL or NUMERIC columns in MySQL
5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump
such tables with @command{mysqldump} before upgrading, and then reload them
after upgrading.
* Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers
the number of spaces that there were at the end of the string. Previously,
MySQL at storage trimmed end spaces from a VARCHAR. Tables created with
5.0.3 will remain to have the old VARCHAR semantics, while new tables will
have the new semantics.
* The sorting order for end-space in TEXT columns for InnoDB and MyISAM
tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as
space-padded at the end. If you have a non-unique index on a TEXT column,
you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check
reports errors. If you have a UNIQUE INDEX on a TEXT column, you should
rebuild the table with OPTIMIZE TABLE.
* The sorting order of BINARY and VARBINARY may still change in some 5.0.x
version.
* In 4.0 - 4.1.11 there is a bug in the InnoDB sorting order of ENUMs if the
collation of the ENUM is not latin1 or if there are more than about 100
different values for the ENUM.
* InnoDB Hot Backup 2.0.1 or earlier does not work with MySQL-5.0.3 or
later. A new version 2.1 will.
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/InnoDB-5.0.6 has been released
Hi!
InnoDB is the MySQL table type that supports foreign key constraints,
transactions, two-phase commit in XA, row-level locking, non-locking
consistent read (MVCC), all four SQL-1992 isolation levels of transactions,
multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a
non-free hot online backup tool.
MySQL-5.0.6 is a bugfix release of the 5.0 branch. The release is still
labeled as beta, because 5.0 contains many new features, and more real-world
testing is needed.
You can download MySQL-5.0.6 from
http://dev.mysql.com/downloads/mysql/5.0.html
Functionality added or changed:
* When the maximum length of SHOW INNODB STATUS output would be exceeded,
truncate the beginning of the list of active transactions, instead of
truncating the end of theoutput. (Bug #5436)
* If innodb_locks_unsafe_for_binlog option is set and the isolation level of
the transaction is not set to serializable then InnoDB uses a consistent
read for select in clauses like INSERT INTO ... SELECT and UPDATE ...
(SELECT) that do not specify FOR UPDATE or IN SHARE MODE. Thus no locks are
set to rows read from selected table.
Bugs fixed:
* MyISAM and InnoDB tables created with DECIMAL or NUMERIC columns in MySQL
5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump
suchtables with mysqldump before upgrading, and then reload them after
upgrading. (The same incompatibility will occur for these tables created in
MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (Bug #10465, Bug
#10625)
* Fixed a critical bug in InnoDB @code{AUTO_INCREMENT}: it could assign the
same value for several rows. (Bug #10359)
* All InnoDB bug fixes from 4.1.12 and earlier versions, and also the fixes
to bugs #10335 and #10607 listed in the 4.1.13 change notes.
Upgrading from 4.1:
* MyISAM and InnoDB tables created with DECIMAL or NUMERIC columns in MySQL
5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump
such tables with @command{mysqldump} before upgrading, and then reload them
after upgrading.
* Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers
the number of spaces that there were at the end of the string. Previously,
MySQL at storage trimmed end spaces from a VARCHAR. Tables created with
5.0.3 will remain to have the old VARCHAR semantics, while new tables will
have the new semantics.
* The sorting order for end-space in TEXT columns for InnoDB and MyISAM
tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as
space-padded at the end. If you have a non-unique index on a TEXT column,
you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check
reports errors. If you have a UNIQUE INDEX on a TEXT column, you should
rebuild the table with OPTIMIZE TABLE.
* The sorting order of BINARY and VARBINARY may still change in some 5.0.x
version.
* In 4.0 - 4.1.11 there is a bug in the InnoDB sorting order of ENUMs if the
collation of the ENUM is not latin1 or if there are more than about 100
different values for the ENUM.
* InnoDB Hot Backup 2.0.1 or earlier does not work with MySQL-5.0.3 or
later. A new version 2.1 will.
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]
Re: MySQL+InnoDB Licenses
Hi, the only right answers sould be here : http://www.mysql.com/company/legal/licensing/ Mathias Selon Daniel Kiss [EMAIL PROTECTED]: Hi All, I would have a question about licensing MySQL. I am writing an application that relies on MySQL+InnoDB (uses MySQL as a database backend). I will distribute my program under GPL (get fees only for official support). Do I or my client have to buy MySQL+InnoDB licenses in this case? Thank you, Daniel -- 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 Licenses
Hi, the only right answers sould be here : http://www.mysql.com/company/legal/licensing/ Mathias Selon Daniel Kiss [EMAIL PROTECTED]: Hi All, I would have a question about licensing MySQL. I am writing an application that relies on MySQL+InnoDB (uses MySQL as a database backend). I will distribute my program under GPL (get fees only for official support). Do I or my client have to buy MySQL+InnoDB licenses in this case? Thank you, Daniel -- 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+InnoDB Licenses
Hi All, I would have a question about licensing MySQL. I am writing an application that relies on MySQL+InnoDB (uses MySQL as a database backend). I will distribute my program under GPL (get fees only for official support). Do I or my client have to buy MySQL+InnoDB licenses in this case? Thank you, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+InnoDB Licenses
This has never arrived : Hi, the only right answers sould be here : http://www.mysql.com/company/legal/licensing/ Mathias Selon Daniel Kiss [EMAIL PROTECTED]: Hi All, I would have a question about licensing MySQL. I am writing an application that relies on MySQL+InnoDB (uses MySQL as a database backend). I will distribute my program under GPL (get fees only for official support). Do I or my client have to buy MySQL+InnoDB licenses in this case? Thank you, Daniel -- 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/InnoDB-5.0.4 is released
Hi! Greetings to all from the MySQL Users Conference 2005 in Santa Clara! The conference has just kicked off with tutorials, and will last till Thursday. Close to 1000 people are expected to attend the conference. MySQL-5.0 is probably the most important new MySQL release in several years. On the MySQL side, a vast number of new features, like stored procedures and views have been implemented in 5.0. On the InnoDB side, we changed the table format to a more space-saving one, and implemented the 2-phase commit XA protocol. MySQL-5.0.4 is a bugfix release. Since MySQL-5.0.3 introduced a large number of new features, we cannot yet recommend the MySQL 5.0 series for production use. This is a beta release for testing and developing your applications that take advantage of MySQL's new powerful features. InnoDB is the MySQL table type that supports foreign key constraints, transactions, two-phase commit in XA, row-level locking, non-locking consistent read (MVCC), all four SQL-1992 isolation levels of transactions, multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a non-free hot online backup tool. You can download MySQL-5.0.4 from http://dev.mysql.com/downloads/mysql/5.0.html Bugs fixed: * ENUM and SET columns were treated incorrectly as character strings. This bug did not manifest itself with latin1 collations if there were less than about 100 elements in an enum, but it caused malfunction with UTF-8. Old tables will continue to work. In new tables, ENUM and SET will be internally stored as unsigned integers. (Bug #9526) * Avoid test suite failures caused by a locking conflict between two server instances at server shutdown/startup. This conflict on advisory locks appears to be the result of a bug in the operating system; these locks should be released when the files are closed, but somehow that does not always happen immediately in Linux. (Bug #9381) * True VARCHAR in 5.0.3: InnoDB stored the 'position' of a row wrong in a column prefix primary key index; this could cause MySQL to complain 'ERROR 1032: Can't find record' in an update of the primary key, and also some ORDER BY or DISTINCT queries. (Bug #9314) Upgrading from 4.1: * Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers the number of spaces that there were at the end of the string. Previously, MySQL at storage trimmed end spaces from a VARCHAR. Tables created with 5.0.3 will remain to have the old VARCHAR semantics, while new tables will have the new semantics. * The sorting order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE. * The sorting order of BINARY and VARBINARY may still change in some 5.0.x version. * There is a bug in the InnoDB sorting order of ENUMs if the collation of the ENUM is not latin1 or if there are more than about 100 different values for the ENUM. This bug is also present in 4.0 and 4.1. * InnoDB Hot Backup 2.0.1 or earlier does not work with MySQL-5.0.3 or later. A new version 2.1 will. 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/InnoDB-4.1.11 is released
Hi!
MySQL/InnoDB-4.1.11 is a bugfix release of the stable 4.1 branch. This
branch is recommended for production use. There are no important bug fixes
in 4.1.11, for most users there is no need to upgrade from 4.1.10.
InnoDB is the MySQL table type that supports foreign key constraints,
transactions, row-level locking, non-locking consistent read (MVCC), all
four SQL-1992 isolation levels of transactions, multiple tablespaces,
asynchronous unbuffered disk I/O on Windows, and a non-free hot online
backup tool.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock (LOCK_REC_NOT_GAP) is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (See the Bugs database or the 4.1.9 change
notes about a workaround for that bug in 4.1.9). (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in fil_space_free(), in
DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
* If MySQL wrote to its binlog, but for some reason, trx-update_undo and
@code{trx-insert_undo} were NULL in InnoDB, then trx-commit_lsn was
garbage, and InnoDB could assert in the log flush of
trx_commit_complete_for_mysql(). (Bug #9277)
* If InnoDB cannot allocate memory, keep retrying for 60 seconds before we
intentionally crash mysqld; maybe the memory shortage is just temporary.
* If one used LOCK TABLES, created an InnoDB temp table, and did a
multi-table update where a MyISAM table was the update table and the temp
table was a read table, then InnoDB asserted in row0sel.c because
n_mysql_tables_in_use was 0. Also, we remove the assertion altogether and
just print an error to the .err log if this important consistency check
fails. (Bug #8677)
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/InnoDB-5.0.3 has been released
Hi! MySQL-5.0.3 is probably the most important new MySQL release in several years. On the MySQL side, a vast number of new features, like stored procedures and views, have been implemented in 5.0. On the InnoDB side, we changed the table format to a more space-saving one, and implemented the 2-phase commit XA protocol. Since 5.0.3 contains a lot of new features, we cannot yet recommend it for production use. This is a beta release for testing and developing your applications that take advantage of MySQL's new powerful features. InnoDB is the MySQL table type that supports foreign key constraints, transactions, row-level locking, non-locking consistent read (MVCC), all four SQL-1992 isolation levels of transactions, multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a non-free hot online backup tool. You can download mySQL-5.0.3 from http://dev.mysql.com/downloads/mysql/5.0.html Functionality added or changed: * Introduced a compact record format that does not store the number of columns or the lengths of fixed-size columns. The old format can be requested by specifying ROW_FORMAT=REDUNDANT. The new format (ROW_FORMAT=COMPACT) is the default. This typically saves 20 % of space compared to the old InnoDB table format. Note that the old tables that you have will still have the old table format. There is no automatic conversion when you upgrade to 5.0.3. New tables will by default have the new table format. * MySQL/InnoDB now supports two-phase commit of transactions, and the associated XA protocol. * Upgrading from 4.1: The sorting order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE. * When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the operating system version at run time and use the fcntl() file flush method on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X for internal disk drives, which caused corruption at power outages. * Implemented fast TRUNCATE TABLE. The old approach (deleting rows one by one) may be used if the table is being referenced by foreign keys. (Bug #7150) * Setting the initial AUTO_INCREMENT value for an InnoDB table using CREATE TABLE ... AUTO_INCREMENT = n now works, and ALTER TABLE ... AUTO_INCREMENT = n resets the current value. * Commit after every 10,000 copied rows when executing ALTER TABLE, CREATE INDEX, DROP INDEX or OPTIMIZE TABLE. This makes it much faster to recover from an aborted operation. * Added several InnoDB status variables. * A shared record lock (LOCK_REC_NOT_GAP) is now taken for a matching record in the foreign key check because inserts can be allowed into gaps. * Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog is used and isolation level of the transaction is not serializable. InnoDB uses consistent read in these cases for a selected table. * Added a new global system variable slave_transaction_retries: if the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or exceeded InnoDB's innodb_lock_wait_timeout, it automatically retries slave_transaction_retries times before stopping with an error. The default is 10. Bugs fixed: * All the bug fixes from the MySQL-4.0 and 4.1 branches. Outstanding bugs: * If an SQL statement fails because an error, MySQL may fail to roll back the statement automatically. It should be rolled back or the whole transaction rolled back according to the ANSI SQL standards. This bug that was introduced in 5.0.3 and will probably be fixed in upcoming 5.0.4. * Column prefix PRIMARY KEYs do not work properly for multi-byte character sets. Upgrading from 4.1: * Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers the number of spaces that there were at the end of the string. Previously, MySQL at storage trimmed the end spaces from a VARCHAR. Tables created with 5.0.3 will remain to have the old VARCHAR semantics, while new tables will have the new semantics. * The sorting order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE. * The sorting order of BINARY and VARBINARY may still change in some 5.0.x version. * There is a bug in the InnoDB sorting order of ENUMs if the collation of the ENUM is not latin1
MySQL/InnoDB-4.0.24 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, transactions, row-level locking that is never escalated, multiversioned concurrency control, asynchronous unbuffered disk I/O on Windows, and a non-free online hot backup tool. InnoDB is an 'ACID'-compliant table type. InnoDB is included in all MySQL downloads from http://www.mysql.com, and in the commercial MySQL Pro license. Release 4.0.24 is a bugfix release of the old stable MySQL-4.0 branch. Please observe this bug fix in upcoming 4.0.25: * Fixed a bug: MySQL-4.0.23 and 4.0.24 could complain that an InnoDB table created with MySQL-3.23.49 or earlier was in the new compact InnoDB table format of 5.0.3 or later, and InnoDB would refuse to use that table. (The same bug exists in 4.1.8 - 4.1.10.) There is nothing wrong with the table, it is mysqld that is in error. Workaround: wait that 4.0.25 or 4.1.11 is released before doing an upgrade, or dump the table and recreate it with any MySQL version = 3.23.50 before upgrading to 4.0.23 or 4.0.24. Full changelog for 4.0.24: Functionality changed or added: * Added configuration option and settable global variable innodb_autoextend_increment for setting the size in megabytes by which InnoDB tablespaces are extended when they become full. The default value is 8, corresponding to the fixed increment of 8 MB in previous versions of MySQL. * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused deadlocks very easily. Bugs fixed: * Work around a problem in AIX 5.1 patched with ML7 security patch: InnoDB would refuse to open its ibdata files, complaining about an operating system error 0. * Fixed a memory corruption bug if one created a table with a primary key that contained at least two column prefixes. An example: CREATE TABLE t(a char(100), b tinyblob, PRIMARY KEY(a(5), b(10))). * Use native tmpfile() function on Netware. All InnoDB temporary files are created under sys:\tmp. Previously, InnoDB temporary files were never deleted on Netware. * Honor the --tmpdir startup option when creating temporary files. Previously, InnoDB temporary files were always created in the temporary directory of the operating system. On Netware, InnoDB will continue to ignore --tmpdir. (Bug #5822) * Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or DELETE, and only the read tables are InnoDB type, the rest are MyISAM; this also fixes bug #7879 for InnoDB type tables. (Bug #7879) * Fixed a bug: 32-bit mysqld binaries built on HP-UX-11 did not work with InnoDB files greater than 2 GB in size. (Bug #6189) * Fixed a bug: InnoDB failed to drop a table in the background drop queue if the table was referenced by a foreign key constraint. * Fixed a bug: if we dropped a table where an INSERT was waiting for a lock to check a FOREIGN KEY constraint, then an assertion would fail in lock_reset_all_on_table(), since that operation assumes no waiting locks on the table or its records. 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]
Re: Problem with PHP + MySQL + InnoDB
Hello.
How to force to kill process which make lock of table ? since PHP make
many persistent connection to MySQL and i don't know
which one is locking the table :(
You may get information about processes on your MySQL server using SHOW
PROCESSLIST
and kill the weird process with the KILL. See:
http://dev.mysql.com/doc/mysql/en/kill.html
http://dev.mysql.com/doc/mysql/en/show-processlist.html
Ady Wicaksono [EMAIL PROTECTED] wrote:
I have an PHP that do application
$sql = SET AUTOCOMMIT=0;
$db-execQuery($sql);
$sql = DELETE FROM TABLE X WHERE...;
if($db-execQuery($sql)){
print ERROR ;
exit(0);
}
$sql = INSERT INTO TABLE ;
if($db-execQuery($sql)){
print ERROR ;
exit(0);
}
I have a persistent connection to MySQL
DELETE is succeed, but INSERT IS FAILED and i simply exit,
i know that i should do ROLLBACK OR COMMIT OR SET AUTOCOMMIT=1 before exit
However, if i don't do it, another session will wait for this PHP
session to finish his transaction and lock wait timeout will arise
How to force to kill process which make lock of table ? since PHP make
many persistent connection to MySQL and i don't know
which one is locking the table :(
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with PHP + MySQL + InnoDB
I have an PHP that do application
$sql = SET AUTOCOMMIT=0;
$db-execQuery($sql);
$sql = DELETE FROM TABLE X WHERE...;
if($db-execQuery($sql)){
print ERROR ;
exit(0);
}
$sql = INSERT INTO TABLE ;
if($db-execQuery($sql)){
print ERROR ;
exit(0);
}
I have a persistent connection to MySQL
DELETE is succeed, but INSERT IS FAILED and i simply exit,
i know that i should do ROLLBACK OR COMMIT OR SET AUTOCOMMIT=1 before exit
However, if i don't do it, another session will wait for this PHP
session to finish his transaction and lock wait timeout will arise
How to force to kill process which make lock of table ? since PHP make
many persistent connection to MySQL and i don't know
which one is locking the table :(
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innobase Oy declares MySQL/InnoDB-4.1 stable
Innobase Oy declares MySQL/InnoDB-4.1 stable Innobase Oy has decided to declare MySQL/InnoDB-4.1 stable and recommended for all production use, starting from version 4.1.10. MySQL/InnoDB-4.1 has been out for almost two years now, there have been millions of downloads of the software, and it is already in extensive production use at many MySQL sites. That gives us the confidence to recommend it for all production use from now on. The first alpha version of 4.1 was released on April 3, 2003, and the product entered the beta phase on June 28, 2004. The last known serious problem in MySQL/InnoDB-4.1 was the bug http://bugs.mysql.com/bug.php?id=7496 in the my.cnf option innodb_file_per_table. Best regards, Heikki Tuuri Innobase Oy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.10 is released
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
Upgrading to 4.1.9:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database names,
table names, constraint names, or column names in versions 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
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]
Re: MySQL/InnoDB-4.1.10 is released
Are foreign key and other constraints enforced by the db server in this
version or is this something that the programmer has to ensure via
application logic?
- Asad
On Tue, 15 Feb 2005, Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
Upgrading to 4.1.9:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database names,
table names, constraint names, or column names in versions 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
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.1.10 is released
Asad,
InnoDB type tables have enforced FOREIGN KEY constraints since 2001.
Unfortunately, none of the table types of MySQL yet supports CHECK
constraints.
Best regards,
Heikki
.
List: mysql
Subject:Re: MySQL/InnoDB-4.1.10 is released
From: Asad Habib ahabib () engin ! umich ! edu
Date: 2005-02-15 20:00:40
Message-ID: Pine.GSO.4.58.0502151459050.28688 () lapis ! engin ! umich !
edu
[Download message RAW]
Are foreign key and other constraints enforced by the db server in this
version or is this something that the programmer has to ensure via
application logic?
- Asad
On Tue, 15 Feb 2005, Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS
X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set.
(Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one
runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM.
(Bug
#7879)
Upgrading to 4.1.10:
* If you have created or used InnoDB tables with TIMESTAMP columns in
MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later,
then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY
indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of
those
characters and the space character changes for some character sets in
4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the
cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other
multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables
when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database
names,
table names, constraint names, or column names in versions 4.1, you
cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names.
Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
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/InnoDB-4.1.9 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 4.1.9 is mainly a bugfix release. This release fixes the CRITICAL BUG #7496 in the innodb_file_per_table option of my.cnf. Secondary indexes of a table could get corrupt at a mysqld shutdown. I recommend that all users of InnoDB with that my.cnf option immediately upgrade to MySQL-4.1.9! OS X 10.3 users should also consider upgrading to 4.1.9. Apple disabled fsync() for internal disk drives, and replaced it with a special fcntl() file flush method. A power outage can easily lead to database corruption in OS X 10.3 with older MySQL versions. Except of critical bug #7496, 4.1.8 has been a very good release, and if we do not discover any serious bugs in 4.1.9 in the next few weeks, I can recommend production use of 4.1.9. Functionality added or changed: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. * Print a more descriptive error and refuse to start InnoDB if the size of ibdata files is smaller than what is stored in the tablespace header; innodb_force_recovery overrides this. Bugs fixed: * Fixed the critical bug if you enabled innodb_file_per_table in my.cnf. If you shut down mysqld, records could disappear from the secondary indexes of a table. (Bug #7496) * Fixed a bug: 32-bit mysqld binaries built on HP-UX-11 did not work with InnoDB files greater than 2 GB in size. (Bug #6189) * Return a sensible error code from DISCARD TABLESPACE if it fails because the table is referenced by a FOREIGN KEY. * Fixed a bug: InnoDB failed to drop a table in the background drop queue if the table was referenced by a FOREIGN KEY constraint. * Fixed a bug: if we dropped a table where an INSERT was waiting for a lock to check a FOREIGN KEY constraint, then an assertion would fail in lock_reset_all_on_table(). * Fix a little bug: we looked at the physical size of a stored SQL NULL value from a wrong field in the index; this has probably caused no bugs visible to the user, only caused some extra space usage in some rare cases. * Use the fcntl() file flush method on OS X versions = 10.3. Apple had disabled fsync() in OS X for internal disk drives, which caused corruption at power outages. Upgrading to 4.1.9: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. 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]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. Could you explain a bit more about how this relates to MyISAM? Is it just that using LOCK TABLES with InnoDB was causing a lot of deadlocks? If so, that would explain what I've been seeing in MySQL 4.0.21 (lots of deadlocks on a very small table that I use for managing parallel processes). Until we upgrade (which will be soon, I think), is it best to turn AUTOCOMMIT off and COMMIT when appropriate? Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Nick, - Original Message - From: Nick Arnett [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, January 14, 2005 7:39 PM Subject: Re: MySQL/InnoDB-4.1.9 is released Heikki Tuuri wrote: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. Could you explain a bit more about how this relates to MyISAM? Is it just that using LOCK TABLES with InnoDB was causing a lot of deadlocks? http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. If so, that would explain what I've been seeing in MySQL 4.0.21 (lots of deadlocks on a very small table that I use for managing parallel processes). Until we upgrade (which will be soon, I think), is it best to turn AUTOCOMMIT off and COMMIT when appropriate? The proper way to use LOCK TABLES with InnoDB tables (or a mixture of MyISAM tables and InnoDB tables) is with SET AUTOCOMMIT=0. Then do like this: LOCK TABLES innodbtable WRITE; do what you like with the table COMMIT;#releases the InnoDB table lock UNLOCK TABLES;#releases the MySQL table lock Thanks! Nick Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. That's what I was just reading! So... is this the equivalent of using BEGIN and COMMIT, for which I have methods in the Python MySQLdb module? Or is there an advantage to the latter? Thanks again, Nick Arnett Director of Business Intelligence Services Liveworld Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Nick, - Original Message - From: Nick Arnett [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, January 14, 2005 9:28 PM Subject: Re: MySQL/InnoDB-4.1.9 is released Heikki Tuuri wrote: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. That's what I was just reading! So... is this the equivalent of using BEGIN and COMMIT, for which I have methods in the Python MySQLdb module? Or is there an advantage to the latter? the BEGIN; .. COMMIT; method does not work here because LOCK TABLES (and UNLOCK TABLES) does an implicit commit of the transaction. Also BEGIN does an implicit commit. As a sidenote, we are working on getting the industry-standard table locking syntax and semantics to MySQL/InnoDB. With the syntaxes LOCK TABLE innodbtable IN SHARE MODE; and LOCK TABLE innodbtable IN EXCLUSIVE MODE; you would get InnoDB table locking similar to DB2 and Oracle. No UNLOCK TABLES would be needed, the next commit would release the table lock. I hope Monty will approve this syntax to 5.0. Thanks again, Nick Arnett Director of Business Intelligence Services Liveworld Inc. Best regards, Heikki 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/InnoDB-4.0.23 is released
Hi! InnoDB is the MySQL table type that supports transactions, FOREIGN KEY constraints, row-level locking, non-locking consistent reads, and a non-free Hot Backup utility. Release 4.0.23 is a bugfix release of the stable MySQL-4.0 branch. This release fixes the critical hang bug of mysqld in MySQL-4.0.22 (or 4.1.7) that would occur after two runs of the innobackup-1.0 Perl script; innobackup-1.1.0 is immune to the bug in MySQL-4.0.22. I would like to wish all MySQL users Happy Holidays and a Prosperous New Year 2005! Functionality added or changed: * Do not periodically write SHOW INNODB STATUS information to a temporary file unless the configuration option innodb_status_file=1 is set. * Made the foreign key parser better aware of quotes. (Bug #6340) Bugs Fixed: * A sequence of BEGIN (or SET AUTOCOMMIT=0), FLUSH TABLES WITH READ LOCK, transactional update, COMMIT, FLUSH TABLES WITH READ LOCK could hang the connection forever and possibly the MySQL server itself. This happened for example when running the innobackup script several times. (Bug #6732) * Fixed a bug in LOAD DATA INFILE.REPLACE printing duplicate key error when executing the same load query several times. (Bug #5835) * Refuse to open new-style tables created with MySQL 5.0.3 or later. (Bug #7089) * Do not call rewind() when displaying SHOW INNODB STATUS information on stderr. * If one used INSERT IGNORE to insert several rows at a time, and the first inserts were ignored because of a duplicate key collision, then InnoDB in a replication slave assigned AUTO_INCREMENT values 1 bigger than in the master. This broke the MySQL replication. (Bug #6287) * Fix two hangs: FOREIGN KEY constraints treated table and database names as case-insensitive. RENAME TABLE t TO T would hang in an endless loop if t had a foreign key constraint defined on it. Fix also a hang over the dictionary mutex that would occur if one tried in ALTER TABLE or RENAME TABLE to create a foreign key constraint name that collided with another existing name. (Bug #3478) * Treat character 0xA0 as space in InnoDB's FOREIGN KEY parser if MySQL treats it as space in the default charset. EMS MySQL Manager inserts character 0xA0 after the table name in an ALTER, which confused InnoDB's parser. * If a connection had an open transaction but had done no updates to transactional tables (for example if had just done a SELECT FOR UPDATE), then executed a non-transactional update, that update automatically committed the transaction (thus releasing InnoDB's row-level locks etc). (Bug #5714) Best regards, Heikki Tuuri 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/InnoDB-4.1.8 is released
Hi!
InnoDB is the MySQL table type that supports FOREIGN KEY constraints,
row-level locking, Oracle-style consistent, non-locking SELECTs, multiple
tablespaces, and a non-free online hot backup tool.
Release 4.1.8 is mainly a bugfix release. There was a critical bug
associated with the innobackup Perl script in 4.1.7: the second run of
innobackup would make the mysqld server to hang. Otherwise, 4.1.7 has been a
very good release, and if we do not discover any serious bugs in 4.1.8 in
the next few weeks, I can recommend production use of 4.1.8.
Functionality added or changed:
* Do not periodically write SHOW INNODB STATUS information to a temporary
file unless the configuration option innodb_status_file=1 is set.
* Commit after every 10,000 copied rows when executing ALTER TABLE. This
makes it much faster to recover from an aborted ALTER TABLE or OPTIMIZE
TABLE.
* mysqldump --single-transaction --master-data now is able to take an online
(non-blocking) dump of InnoDB and report the corresponding binary log
coordinates. This makes a backup suitable for point-in-time recovery,
roll-forward or replication slave creation.
Bugs fixed:
* A sequence of BEGIN (or SET AUTOCOMMIT=0), FLUSH TABLES WITH READ LOCK,
transactional update, COMMIT, FLUSH TABLES WITH READ LOCK could hang the
connection forever and possibly the MySQL server itself. This happened for
example when running the innobackup script several times. (Bug #6732)
* Do not intentionally crash mysqld if the buffer pool is exhausted by the
lock table; return error 1206 instead. Do not intentionally crash mysqld if
we cannot allocate the memory for the InnoDB buffer pool. (Bug #6817) (Bug
#6827)
* Let InnoDB's FOREIGN KEY parser to remove the latin1 character @code{0xA0}
from the end of an unquoted identifier. The EMS MySQL Manager in ALTER TABLE
adds that character after a table name, which caused error 121 when we tried
to add a new constraint.
* Refuse to open new-style tables created with MySQL 5.0.3 or later. (Bug
#7089)
* Do not call rewind() when displaying SHOW INNODB STATUS information on
stderr.
* Made the foreign key parser better aware of quotes. (Bug #6340)
* If one used INSERT IGNORE to insert several rows at a time, and the first
inserts were ignored because of a duplicate key collision, then InnoDB in a
replication slave assigned AUTO_INCREMENT values 1 bigger than in the
master. This broke the MySQL replication. (Bug #6287)
* Fixed a bug: InnoDB ignored in innodb_data_file_path the max specification
in :autoextend:max:2000M. This bug was introduced in 4.1.1.
* Fixed a bug: innodb_locks_unsafe_for_binlog still uses next-key locking
(Bug #6747). InnoDB used next-key locking when record matched completely to
search tuple. This unnecessary next-key locking is now removed when
innodb_locks_unsafe_for_binlog option is used.
* Fix two hangs: FOREIGN KEY constraints treated table and database names as
case-insensitive. RENAME TABLE t TO T would hang in an endless loop if t had
a foreign key constraint defined on it. Fix also a hang over the dictionary
mutex that would occur if one tried in ALTER TABLE or RENAME TABLE to create
a foreign key constraint name that collided with another existing name. (Bug
#3478)
Upgrading to 4.1.8:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database names,
table names, constraint names, or column names in versions 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
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]
Re: MySQL/InnoDB-5.0.2 is released
Walt, - Original Message - From: kernel [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, December 02, 2004 11:01 PM Subject: Re: MySQL/InnoDB-5.0.2 is released MySQL to return wrong results if a SELECT uses two indexes at the same time Does mysql 5.0.x have the ability to use more than one index per table on a select ? in certain cases yes. It is the Row Ordered Retrieval code, that takes the insterection of row id's (or primary key values). We had to rewrite a simple select id from table_a where last_name like 'smith%' and first_name like 'john%' to select id from table_a left join ( select id from table_a where last_name like 'smith%' group by id ) as t2 on t2.id = table_a.id where table_a.first_name like 'john%' limit 201; We had tried an index on last_name, an index on first_name, and a combo index of (last_name, first_name). We cut the run time from 1min 57sec to 3seconds. I do not know if ROR works for that query. You have to test. walt Best regards, Heikki Heikki Tuuri wrote: Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 5.0.2 is a snapshot of the 5.0 development branch of MySQL. Unfortunately, this snapshot still contains some critical bugs, like http://bugs.mysql.com/bug.php?id=5401, which causes MySQL to return wrong results if a SELECT uses two indexes at the same time. InnoDB in MySQL-5.0.2 is almost the same as in the upcoming MySQL-4.1.8 release. Marko's new compact InnoDB table format did not make it to 5.0.2. The new compact table format will be pushed to the 5.0 BitKeeper tree today, and it will be included in 5.0.3. The biggest downside of InnoDB when compared to MyISAM has been that InnoDB tables take a lot more space than MyISAM tables. The new compact InnoDB table format will make InnoDB tables substantially smaller. You can look at the InnoDB roadmap at http://www.innodb.com/todo.php InnoDB functionality changed from 4.1: * If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB no longer in an UPDATE or a DELETE locks rows that do not get updated or deleted. This greatly reduces the probability of deadlocks. If you do not specify the option, InnoDB locks all rows that the UPDATE or DELETE scans, to ensure serializability. Upgrading to 5.0.2: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to = 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. 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]
MySQL/InnoDB-5.0.2 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 5.0.2 is a snapshot of the 5.0 development branch of MySQL. Unfortunately, this snapshot still contains some critical bugs, like http://bugs.mysql.com/bug.php?id=5401, which causes MySQL to return wrong results if a SELECT uses two indexes at the same time. InnoDB in MySQL-5.0.2 is almost the same as in the upcoming MySQL-4.1.8 release. Marko's new compact InnoDB table format did not make it to 5.0.2. The new compact table format will be pushed to the 5.0 BitKeeper tree today, and it will be included in 5.0.3. The biggest downside of InnoDB when compared to MyISAM has been that InnoDB tables take a lot more space than MyISAM tables. The new compact InnoDB table format will make InnoDB tables substantially smaller. You can look at the InnoDB roadmap at http://www.innodb.com/todo.php InnoDB functionality changed from 4.1: * If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB no longer in an UPDATE or a DELETE locks rows that do not get updated or deleted. This greatly reduces the probability of deadlocks. If you do not specify the option, InnoDB locks all rows that the UPDATE or DELETE scans, to ensure serializability. Upgrading to 5.0.2: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to = 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. 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]
Re: MySQL/InnoDB-5.0.2 is released
MySQL to return wrong results if a SELECT uses two indexes at the same time Does mysql 5.0.x have the ability to use more than one index per table on a select ? We had to rewrite a simple select id from table_a where last_name like 'smith%' and first_name like 'john%' to select id from table_a left join ( select id from table_a where last_name like 'smith%' group by id ) as t2 on t2.id = table_a.id where table_a.first_name like 'john%' limit 201; We had tried an index on last_name, an index on first_name, and a combo index of (last_name, first_name). We cut the run time from 1min 57sec to 3seconds. walt Heikki Tuuri wrote: Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 5.0.2 is a snapshot of the 5.0 development branch of MySQL. Unfortunately, this snapshot still contains some critical bugs, like http://bugs.mysql.com/bug.php?id=5401, which causes MySQL to return wrong results if a SELECT uses two indexes at the same time. InnoDB in MySQL-5.0.2 is almost the same as in the upcoming MySQL-4.1.8 release. Marko's new compact InnoDB table format did not make it to 5.0.2. The new compact table format will be pushed to the 5.0 BitKeeper tree today, and it will be included in 5.0.3. The biggest downside of InnoDB when compared to MyISAM has been that InnoDB tables take a lot more space than MyISAM tables. The new compact InnoDB table format will make InnoDB tables substantially smaller. You can look at the InnoDB roadmap at http://www.innodb.com/todo.php InnoDB functionality changed from 4.1: * If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB no longer in an UPDATE or a DELETE locks rows that do not get updated or deleted. This greatly reduces the probability of deadlocks. If you do not specify the option, InnoDB locks all rows that the UPDATE or DELETE scans, to ensure serializability. Upgrading to 5.0.2: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to = 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. 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/InnoDB-4.0.22 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces (in 4.1), and a non-free online hot backup tool. Release 4.0.22 is mainly a bugfix release of the stable 4.0 series. This release fixes the unfortunate mysqldump --opt assertion failure that slipped into 4.0.21. There are two new my.cnf options: innodb_table_locks : By setting this to 0, you can get the LOCK TABLES behavior that was used prior to 4.0.20. Old applications that use LOCK TABLES in the AUTOCOMMIT=1 mode can easily end up in deadlocks if this is set to 1, which is the default value. innodb_max_purge_lag: Under bursts of a high UPDATE or DELETE load, you can use this to force InnoDB to favor purge at the cost of users' SQL statements. If the purge lags behind, the physical size of the database may grow so that operation becomes disk-bound. To prevent that, it is better to use this parameter to favor purge. Functionality added or changed: * New mysqld option --innodb-table-locks and session variable innodb_table_locks (on by default). You can now disable InnoDB table locks if your application depends on the way MySQL did table locks before 4.0.20. (Bug #3299, Bug #5998) * Added the startup option and settable global variable innodb_max_purge_lag for delaying INSERT, UPDATE, and DELETE operations when the purge operations are lagging. The default value of this parameter is zero, meaning that there will not be any delays. * Change error code to HA_ERR_ROW_IS_REFERENCED if we cannot DROP a parent table because it is referenced by a FOREIGN KEY constraint. Bugs fixed: * Fixed a bug introduced in 4.0.21. An assertion failed if one used mysqldump with the option -l or --opt, or if one used LOCK TABLES ... LOCAL. (Workaround in 4.0.21: use --quick and --single transaction. (Bug #5538) * Make the check for excessive semaphore waits to tolerate glitches in the system clock (do not crash the server if the system time is adjusted while InnoDB is under load.). (Bug #5898) * Fixed a bug in the InnoDB FOREIGN KEY parser that prevented ALTER TABLE of tables containing # in their names. (Bug #5856) * Fixed problem introduced in 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, then COMMIT, would cause replication slaves to stop complaining about error 1223. The bug surfaced when using the InnoDB innobackup script. (Bug #5949) * If one updated a column so that its size changed, or updated it to an externally stored (TEXT or BLOB) value, then ANOTHER externally stored column would show up as 512 bytes of good data + 20 bytes of garbage in a consistent read that fetched the old version of the row. (Bug #5960) * Release the dictionary latch during a long cascaded FOREIGN KEY operation, so that we do not starve other users doing CREATE TABLE or other DDL operations. This caused a notorious 'Long semaphore wait' message to be printed to the .err log. (Bug #5961) * Let InnoDB remember row locking type (X or S) inside LOCK TABLES, also over plain consistent read SELECTs. * Having a column prefix index in the primary key, and the same column fully in a secondary key could cause an assertion failure in row_build_row_ref(). (Bug #5180) 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/InnoDB-4.1.7 is released
Hi! InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 4.1.7 is mainly a bugfix release. It is the first in the 4.1 series that has been labeled 'production', but since quite a few bugs were fixed in both 4.1.6 and 4.1.7, I would still wait for a few weeks to see if there are any critical bugs in 4.1.7. Functionality added or changed: * Made LOCK TABLES behave by default like it did before MySQL 4.0.20 or 4.1.2: no InnoDB lock will be taken. Added a startup option and settable system variable innodb_table_locks for making LOCK TABLE acquire also InnoDB locks. See section 16.17 Restrictions on InnoDB Tables. (Bug #3299, Bug #5998) * SHOW TABLE STATUS now shows the creation time of the table for InnoDB. Note that this timestamp might not be the correct time because, e.g., ALTER TABLE changes this timestamp. * If innodb_thread_concurrency would be exceeded, let a thread sleep 10 ms before entering the FIFO queue; previously, the value was 50 ms. Bugs fixed: * Fixed problem introduced in MySQL 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, then COMMIT, would cause replication slaves to stop complaining about error 1223. Bug surfaced when using the InnoDB innobackup script. (Bug #5949) * Release the dictionary latch during a long cascaded FOREIGN KEY operation, so that we do not starve other users doing CREATE TABLE or other DDL operation. This caused a notorious 'Long semaphore wait' message to be printed to the `.err' log. (Bug #5961) Upgrading to 4.1.7: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. 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/InnoDB-4.1.6 has been released
Hi! InnoDB is a MySQL table type which supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool. Release 4.1.6 is mainly a bugfix release. We do not yet declare MySQL/InnoDB-4.1 stable, because quite many bugs were fixed since 4.1.5, and there are still a few outstanding known bugs, especially in the UTF-8 character set support. Functionality added or changed: * Added the startup option and settable global variable innodb_max_purge_lag for delaying INSERT, UPDATE and DELETE operations when the purge operations are lagging. The default value of this parameter is zero, meaning that there will not be any delays. See section 16.13 Implementation of Multi-Versioning. * The innodb_autoextend_increment startup option that was introduced in release 4.1.5 was made a settable global variable. (Bug #5736) * If DROP TABLE is invoked on an InnoDB table for which the .ibd file is missing, print to error log that the table was removed from the InnoDB data dictionary, and allow MySQL to delete the .frm file. Bugs fixed: * Make the check for excessive semaphore waits tolerate glitches in the system clock (do not crash the server if the system time is adjusted while InnoDB is under load.). (Bug #5898) * Fixed a bug in the InnoDB FOREIGN KEY parser that prevented ALTER TABLE of tables containing `#' in their names. (Bug #5856) * Fixed a bug that prevented ALTER TABLE t DISCARD TABLESPACE from working. (Bug #5851) * SHOW CREATE TABLE now obeys the SET SQL_MODE=ANSI and SET SQL_QUOTE_SHOW_CREATE=0 settings. (Bug #5292) * Fixed a bug that caused CREATE TEMPORARY TABLE ... ENGINE=InnoDB to terminate mysqld when running in innodb_file_per_table mode. Per-table tablespaces for temporary tables will from now on be created in the temporary directory of mysqld. (Bug #5137) * Fixed some (not all) UTF-8 bugs in column prefix indexes. (Bug #5975) Some outstanding bugs: * LIKE 'abc%' does not work correctly in the UTF-8 charset on column prefix indexes (e.g., a VARCHAR(100), KEY a(10)). * Case-insensitive comparison of database, table, and column names containing accent characters (ASCII values = 128) do not work right. Upgrading to 4.1.6: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases. * If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later. * If you have used accent characters (ASCII codes = 128) in database names, table names, constraint names, or column names in versions 4.1, you cannot upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table. 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/InnoDB-4.1.5 is released
Hi! InnoDB is a MySQL table type which provides ACID transactions, row-level locking, consistent, non-locking SELECTs (MVCC concurrency control), FOREIGN KEY constraints with CASCADE options, and a commercial hot backup tool. Release 4.1.5 is mainly a bugfix release. InnoDB bug fixing is lagging behind after the summer vacation season, and there are a few important outstanding bugs in 4.1.5. See below about them. The full InnoDB changelog Functionality added: * Added configuration option innodb_autoextend_increment for setting the size in megabytes by which InnoDB tablespaces are extended when they become full. The default value is 8, corresponding to the fixed increment of 8MB in previous versions of MySQL. Bugs fixed: * Fixed a bug that InnoDB only allowed a maximum of 1000 connections inside InnoDB at the same time. A higher number could cause an assertion failure in sync0arr.c, line 384. Now we allow 1000, 1, or 5, depending on the buffer pool size. (Bug #5414) Outstanding bugs: * If you use the my.cnf option innodb_file_per_table, you cannot create TEMPORARY InnoDB type tables, unless you add a database 'tmp' (in Windows 'temp') to your MySQL installation. * Column prefix indexes, where only an initial prefix of a column is taken to an index, do not work yet properly in the UTF-8 character set. Upgrading from earlier MySQL versions: * If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed. * If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions = 4.1.2, then you have to rebuild those tables after you upgrade to = 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table (also MyISAM tables need a rebuild in these cases). 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/InnoDB-4.0.21 is released
Hi! InnoDB is a MySQL table type that provides FOREIGN KEY constraints, 'ACID' transactions, row-level locking, a non-locking consistent read (MVCC), and a commercial online backup tool. Release 4.0.21 is a bugfix release of the stable MySQL-4.0 branch. It is recommended for production use. You can download it from http://dev.mysql.com/downloads/mysql/4.0.html The most important InnoDB bug fixed is probably the '4 billion bug' that could cause InnoDB to complain: InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex and crash mysqld at every 4 billionth allocation of memory. The complete changelog for InnoDB: Functionality added or changed: * Renamed the `innodb.status.pid' files (created in the data directory) to `innodb_status.pid'. This avoids problems on filesystems that do not allow multiple periods in filenames. * Added innodb_status_file system variable to mysqld to control whether output from SHOW INNODB STATUS is written to a `innodb_status.pid' file in the data directory. By default, the file is not created. To create it, start mysqld with the --innodb_status_file=1 option. * Changes for NetWare to exit InnoDB gracefully on NetWare even in a case of an assertion failure, instead of intentionally crashing the `mysqld' server process. Bugs fixed: * Fixed a bug in ON DELETE CASCADE and ON UPDATE CASCADE foreign key constraints: long chains of cascaded operations would cause a stack overflow and crash the server. Cascaded operations are now limited to 15 levels. (Bug #4446) * Fixed a possible bug in LOCK TABLES introduced in MySQL/InnoDB-4.0.19: The count of tables explicitly locked by a transaction was incremented only after the locks were granted, but decremented when the lock structures were destroyed. * Fixed a bug in UNLOCK TABLES in AUTOCOMMIT=0 mode, introduced in MySQL/InnoDB-4.0.19: The memory allocated for some locks acquired by the transaction could be deallocated before those locks were released. The bug can lead to crashes and memory corruption of the buffer pool when the transaction acquires a large number of locks (table locks or row-level locks). * Increment the InnoDB watchdog timeout during CHECK TABLE. A long-running CHECK TABLE would cause InnoDB to complain about a 'long semaphore wait', and crash the server, if a query had to wait more than 600 seconds behind that CHECK TABLE operation. (Bug #2694) * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like below in the `.err' log. The workaround is to make innodb_additional_mem_pool_size big enough to hold all memory allocation. Use SHOW INNODB STATUS to determine that there is plenty of free space available in the additional mem pool, and the total allocated memory stays rather constant. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex * The special meaning of the table names innodb_monitor, innodb_lock_monitor, innodb_tablespace_monitor, innodb_table_monitor, and innodb_validate in CREATE TABLE and DROP TABLE statements was accidentally removed in MySQL/InnoDB-4.0.19. The diagnostic functions attached to these special table names (see section 16.12.1 SHOW INNODB STATUS and the InnoDB Monitors) are accessible again in MySQL/InnoDB-4.0.21. * When the private SQL parser of InnoDB was modified in MySQL/InnoDB-4.0.19 in order to allow the use of the apostrophe (`'') in table and column names, the fix relied on a previously unused function mem_realloc(), whose implementation was incorrect. As a result, InnoDB can incorrectly parse column and table names as the empty string. The InnoDB realloc() implementation has been corrected in MySQL/InnoDB-4.0.21. * Fixed a glitch introduced in 4.0.18 and 4.1.2: in SHOW TABLE STATUS InnoDB systematically overestimated the row count by 1 if the table fit on a single 16 kB data page. * InnoDB created temporary files with the C library function tmpfile(). On Windows, the files would be created in the root directory of the current file system. To correct this behavior, the invocations of tmpfile() were replaced with code that uses the function create_temp_file() in the MySQL portability layer. (Bug #3998) * If ALTER TABLE ... DROP FOREIGN KEY ... fails because of a wrong constraint name, return a table handler error number 150 instead of 152. * If there was little file I/O in InnoDB, but the insert buffer was used, it could happen that 'Pending normal aio reads' was bigger than 0, but the I/O handler thread did not get waken up in 600 seconds. This resulted in a hang, and crashing of InnoDB. * If we RENAMEd a table, InnoDB forgot to load the FOREIGN KEY constraints that reference the new table name, and forgot to check
MySQL/InnoDB-4.1.4 is released
Hi! InnoDB is a MySQL table type that provides foreign key constraints, ACID transactions, row-level locking, consistent (MVCC) non-locking reads, transaction savepoints, and a commercial InnoDB Hot Backup tool to MySQL. InnoDB is included in all MySQL releases, except the commercial 'MySQL Classic' binaries. For InnoDB, MySQL-4.1.4 is mainly a bugfix release. You can download the binaries from http://dev.mysql.com/downloads/mysql/4.1.html NOTE that an upgrade from 4.0 to 4.1.3 or later requires a rebuild of some tables if you have stored control characters (ASCII value 32) to non-latin1 non-BINARY indexed columns in an InnoDB table. If you have stored control characters in an indexed non-BINARY column in a MyISAM table, you have to REPAIR or rebuild that table. There is also a rebuild need if you have used a TIMESTAMP column in an InnoDB table in 4.1.0 - 4.1.3. Functionality added or changed: * Important: Made internal representation of TIMESTAMP values in InnoDB in 4.1 to be the same as in 4.0. This difference resulted in wrong datetime values in TIMESTAMP columns in InnoDB tables after upgrade from 4.0 to 4.1. (Bug #4492) Warning: extra steps during upgrade required! This means that if you are upgrading from 4.1.x, where x = 3, to 4.1.4 you should use mysqldump for saving and then restoring your InnoDB tables with TIMESTAMP columns. No conversion is needed if you upgrade from 3.23 or 4.0 to 4.1.4 or later. * Added a new startup option innodb_locks_unsafe_for_binlog. This option forces InnoDB not to use next-key locking in searches and index scans. * Added innodb_status_file system variable to mysqld to control whether output from SHOW INNODB STATUS is written to a `innodb_status.pid' file in the data directory. By default, the file is not created. To create it, start mysqld with the --innodb_status_file=1 option. * Changes for NetWare to exit InnoDB gracefully on NetWare even in a case of an assertion failure, instead of intentionally crashing the `mysqld' server process. Also keep in mind: * Important: Starting from MySQL 4.1.3, InnoDB uses the same character set comparison functions as MySQL for non-latin1_swedish_ci character strings that are not BINARY. This changes the sorting order of space and characters ASCII(32) in those character sets. For latin1_swedish_ci character strings and BINARY strings, InnoDB uses its own pad-spaces-at-end comparison method, which stays unchanged. If you have an InnoDB table created with MySQL 4.1.2 or earlier, with an index on a non-latin1 character set (in the case of 4.1.0 and 4.1.1 with any character set) CHAR/VARCHAR/or TEXT column that is not BINARY but may contain characters ASCII(32), then you should do ALTER TABLE or OPTIMIZE table on it to regenerate the index, after upgrading to MySQL 4.1.3 or later. Bugs fixed: * Fixed a bug in ON DELETE CASCADE and ON UPDATE CASCADE foreign key constraints: long chains of cascaded operations would cause a stack overflow and crash the server. Cascaded operations are now limited to 15 levels. (Bug #4446) * Increment the InnoDB watchdog timeout during CHECK TABLE. (Bug #2694) * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like below in the `.err' log. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex * Fixed a glitch introduced in 4.0.18 and 4.1.2: in SHOW TABLE STATUS InnoDB systematically overestimated the row count by 1 if the table fit on a single 16 kB data page. * InnoDB created temporary files with the C library function tmpfile(). On Windows, the files would be created in the root directory of the current file system. To correct this behavior, the invocations of tmpfile() were replaced with code that uses the function create_temp_file() in the MySQL portability layer. (Bug #3998) * If you RENAMEd a table, InnoDB forgot to load the foreign key constraints that reference the new table name, and forgot to check that they are compatible with the table. * If there was little file I/O in InnoDB, but the insert buffer was used, it could happen that 'Pending normal aio reads' was bigger than 0, but the I/O handler thread did not get waken up in 600 seconds. This resulted in a hang, and an intentional crashing of `mysqld'. An outstanding bug: * If you have specified innodb_file_per_table in my.cnf, and try to create a TEMPORARY InnoDB type table, InnoDB will complain that it cannot find a path ./tmp/tablename. Workaround: create a MySQL database whose name is tmp (on Windows, temp). 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]
SV: MySQL/InnoDB crashes system
Hi I still got this freeze problem, i have found out that this bug is related to InnoDB, i converted the table that gives problems back to MyISAM, and the dump operation runs fine just as it did before, but as soon as i convert it to InnoDB and dump this table my system freezes but not the first time, usually i can do a dump 2-10 times before it goes wrong. I am certain that it is not a heat problem, or any other hardware problem it could be a conflict between a driver and MySQL. I got 2 identical servers running in a replication setup, i do the testing on my backup server but i can create the freeze on both servers, we run on MySQL 4.0.18, but i have also tried 4.0.20a and 4.1.3-beta. the servers config: Intel Pentium 4 2.53 Ghz QDI Superb 4E-A 533 motherboard 1gb DDR333 ram 2x Seagate CHEETAH 73.5 gb U160 SCSI in Raid 1 Adaptec 2110S Raid controller Windows 2000 Server UK 3Com 996B Gigabit NIC The servers have SP4 and all updates if anyone have an idea on how to troubleshoot this? Thanks Nickolai Nielsen -Oprindelig meddelelse- Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sendt: 9. juli 2004 05:07 Til: [EMAIL PROTECTED] Emne: Re: MySQL/InnoDB crashes system Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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]
SV: MySQL/InnoDB crashes system
Hi Again i forgot to mention that this table has 527101 rows, and takes 90mb when it is dumped to the disk. I dont have this freeze problem when i dump the smaller smaller tables that also uses InnoDB. i Also discovered that it is not on the same posistion ind the dump file the lockup happens. Nickolai -Oprindelig meddelelse- Fra: Nickolai Nielsen [mailto:[EMAIL PROTECTED] Sendt: 10. juli 2004 16:28 Til: [EMAIL PROTECTED] Emne: SV: MySQL/InnoDB crashes system Hi I still got this freeze problem, i have found out that this bug is related to InnoDB, i converted the table that gives problems back to MyISAM, and the dump operation runs fine just as it did before, but as soon as i convert it to InnoDB and dump this table my system freezes but not the first time, usually i can do a dump 2-10 times before it goes wrong. I am certain that it is not a heat problem, or any other hardware problem it could be a conflict between a driver and MySQL. I got 2 identical servers running in a replication setup, i do the testing on my backup server but i can create the freeze on both servers, we run on MySQL 4.0.18, but i have also tried 4.0.20a and 4.1.3-beta. the servers config: Intel Pentium 4 2.53 Ghz QDI Superb 4E-A 533 motherboard 1gb DDR333 ram 2x Seagate CHEETAH 73.5 gb U160 SCSI in Raid 1 Adaptec 2110S Raid controller Windows 2000 Server UK 3Com 996B Gigabit NIC The servers have SP4 and all updates if anyone have an idea on how to troubleshoot this? Thanks Nickolai Nielsen -Oprindelig meddelelse- Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sendt: 9. juli 2004 05:07 Til: [EMAIL PROTECTED] Emne: Re: MySQL/InnoDB crashes system Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB crashes system
hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB crashes system
Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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 InnoDB tables dump [CASCADE]
Hi, I'd ask you : Can I use in MySQL 4.x on InnoDB tables something like : create dump from this InnoDB tables with cascade INSERT sql statements based foreign keys ? And if yes, how ? thank you very much regards Milan Svrlo -=x=- Skontrolované antivírovým programom NOD32 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB tables dump [CASCADE]
Milan Svrlo [EMAIL PROTECTED] wrote: Hi, I'd ask you : Can I use in MySQL 4.x on InnoDB tables something like : create dump from this InnoDB tables with cascade INSERT sql statements based foreign keys ? And if yes, how ? No. Probably you need mysqldump and SET FOREIGN_KEY_CHECKS = 0 command: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html http://dev.mysql.com/doc/mysql/en/mysqldump.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.2 is released
Hi! Long-awaited MySQL-4.1.2 was released today. Windows binaries of 4.1.2 will be released as soon as they are available. The binaries and source are available for download at: http://dev.mysql.com/downloads/mysql/4.1.html InnoDB is a MySQL table type that provides foreign key constraints, transactions, row level locking, a MVCC concurrency control method for transactions, and a non-free hot backup tool that can take binary backups of your database without disturbing normal processing. MySQL-4.1.2 is mainly a bugfix release, but there are also a few important new features in InnoDB. The most important new feature is that InnoDB now supports multiple character sets in the same installation. For example, one column in a table can be in the default latin1_swedish_ci character set / collation, while another column is in UTF-8 and in some other collation order. This capability came to MyISAM tables already in earlier 4.1 releases, but for InnoDB the feature was completed in 4.1.2. Another new feature is that MySQL now automatically creates an index on a FOREIGN KEY if the user does not specify a suitable index for it. A suitable index is one where the foreign key columns appear in the index specification as the first columns, and are in the same order as in the FOREIGN KEY specification. Automatical creation will eliminate most of the annoying cases of Error 1005 (errno 150) in table creation, when the user forgot to specify a suitable index. Note that MySQL still does not automatically create an index on the REFERENCED key in the parent table. But that is not as big a problem, because usually the referenced key is the PRIMARY KEY of the parent table, and an index always exists on it. For more information on InnoDB foreign key constraints, see: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html The complete changelog of 4.1.2: Functionality added or changed: * Support multiple character sets. Note that tables created in other collations than latin1_swedish_ci cannot be accessed in MySQL/InnoDB 4.0. * Automatically create a suitable index on a FOREIGN KEY, if the user does not create one. Removes most of the cases of Error 1005 (errno 150) in table creation. * Do not assert in `log0log.c', line 856 if ib_logfiles are too small for innodb_thread_concurrency. Instead, print instructions how to adjust `my.cnf' and call exit(1). * If MySQL tries to SELECT from an InnoDB table without setting any table locks, print a descriptive error message and assert; some subquery bugs were of this type. * Allow a key part length in InnoDB to be up to 3,500 bytes; this is needed so that one can create an index on a column with 255 UTF-8 characters. * All new features from InnoDB-4.0.17, InnoDB-4.0.18, InnoDB-4.0.19 and InnoDB-4.0.20. Bugs fixed: * All bug fixes from InnoDB-4.0.17, InnoDB-4.0.18, InnoDB-4.0.19 and InnoDB-4.0.20. * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom was a printout like below in the `.err' log. (Bug fix from 4.0.21.) InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex * Improved portability to 64-bit platforms, especially Win64. * Fixed an assertion failure when a purge of a table was not possible because of missing `.ibd' file. * Fixed a bug: do not retrieve all columns in a table if we only need the 'ref' of the row (usually, the PRIMARY KEY) to calculate an ORDER BY. (Bug #1942) * On Unix-like systems, obtain an exclusive advisory lock on InnoDB files, to prevent corruption when multiple instances of MySQL are running on the same set of data files. The Windows version of InnoDB already took a mandatory lock on the files. (Bug #3608) * Added a missing space to the output format of SHOW INNODB STATUS; reported by Jocelyn Fournier. 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/InnoDB-4.0.20 is released
Hi! InnoDB is a MySQL table type that provides FOREIGN KEY constraints, transactions, row level locking and multiversioned concurrency control to MySQL, as well as a commercial hot backup tool. Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Functionality added or changed: * Make MySQL table locks (LOCK TABLES ... ) aware of InnoDB row locks on the same table. The MySQL table lock request now has to wait until conflicting InnoDB row locks are released. (Bug #3299) * Better error message when the server has to crash because the buffer pool is exhausted by the lock table or the adaptive hash index. * Print always the count of pending pread() and pwrite() calls if there is a long semaphore wait. Often a mysqld hang is caused by bugs in the operating system, or a hardware fault, and this can reveal it. * Improve space utilization when rows of 1,500 to 8,000 bytes are inserted in the order of the primary key. * Remove potential buffer overflow errors by sending diagnostic output to stderr or files instead of stdout or fixed-size memory buffers. As a side effect, the output of SHOW INNODB STATUS will be written to a file `datadir/innodb.status.pid' every 15 seconds. An outstanding bug: * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like below in the .err log. The fix to this bug will be in 4.0.21. The workaround is to make innodb_additional_mem_pool_size big enough to hold all memory allocation. Use SHOW INNODB STATUS to determine that there is plenty of free space available in the additional mem pool, and the total allocated memory stays rather constant. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex Bugs fixed: * Fixed race conditions in SHOW INNODB STATUS: it could cause a seg fault in innobase_mysql_print_thd(). A similar bug may have caused seg faults in MySQL's SHOW PROCESSLIST (Bug #3596) * Fixed a bug: DROP DATABASE did not work if FOREIGN KEY references were defined within the database. (Bug #3058) * Remove unnecessary files, functions and variables. Many of these were needed in the standalone version of InnoDB. * Remove debug functions and variables from non-debug build. * Add diagnostic code to analyze an assertion failure in ha_innodb.cc on line 2020 reported by a user. (Bug #2903) * Fixed a bug: in a FOREIGN KEY, ON UPDATE CASCADE was not triggered if the update changed a string to another value identical in alphabetical ordering, e.g., `abc' - `aBc'. * Protect the reading of the latest foreign key error explanation buffer with a mutex; in theory, a race condition could cause SHOW INNODB STATUS print garbage characters after the error info. * Fixed a bug: The row count and key cardinality estimate was grossly too small if each clustered index page only contained one record. * Parse CONSTRAINT FOREIGN KEY correctly. (Bug #3332) * Fixed a memory corruption bug on Windows. The bug is present in all InnoDB versions in Windows, but it depends on how the linker places a static array in srv0srv.c, whether the bug shows itself. 4 bytes were overwritten with a pointer to a statically allocated string `get windows aio return value'. * Fix a glitch reported by Philippe Lewicki on the general mailing list: do not print a warning to the `.err' log if read_key fails with a lock wait timeout error 146. * Allow quotes to be embedded in strings in the private SQL parser of InnoDB, so that `'' can be used in InnoDB table and column names. Display quotes within identifiers properly. * Debugging: Allow UNIV_SYNC_DEBUG to be disabled while UNIV_DEBUG is enabled. * Debugging: Handle magic numbers in a more consistent way. 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]
Re: MySQL/InnoDB-4.0.20 is released
Heikki Tuuri wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) - Mark -- 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.20 is released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark wrote: | Heikki Tuuri wrote: | | |Release 4.0.20 is mainly a bugfix release, but there are also some |important functional changes. Release 4.0.19 was completely skipped |over because Bug #3596 might have caused segmentation faults on some |platforms. The changelog below lists all the changes since 4.0.18. | | | Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) | | - Mark | | There shouldn't be any problems with Perl BDB =) - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| - --- .- Este mensaje está digitalmente firmado para garantizar ~ su origen .- El intercambio de llaves públicas se realiza a petición ~ de las partes interesadas via e-mail - --- .- This message has been digitally signed .- Public Key (PGP or GPG) available upon request -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAql2Z8WJSBCrOXJ4RAgTXAKCrJDOV2vYXGrG61N3fYgYzjVe/MQCfcE41 GiZe0vHEYSHGyjHW9zPA6tk= =1zbO -END PGP SIGNATURE- -- 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.20 is released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark wrote: | Heikki Tuuri wrote: | | |Release 4.0.20 is mainly a bugfix release, but there are also some |important functional changes. Release 4.0.19 was completely skipped |over because Bug #3596 might have caused segmentation faults on some |platforms. The changelog below lists all the changes since 4.0.18. | | | Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) | | - Mark | | There shouldn't be any problems with Perl BDB =) -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAqmHW8WJSBCrOXJ4RArrBAJ0fAxYqrL97+AIMjxOckIfLmk/4lACgp9H1 6836Z0JQKjc8st3BzKaD4vQ= =3Cqb -END PGP SIGNATURE- -- 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.20 is released
Hmm... I didn't have to upgrade our DBD drivers when we moved from 3.23.57 to 4.0.18. Strange you had to. Do you remember your old DBD driver's version? Or was that only Win32 problem? - Original Message - From: Mark [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 2:21 PM Subject: Re: MySQL/InnoDB-4.0.20 is released Heikki Tuuri wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) - Mark -- 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.20 is released
Mihail Manolov wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) Hmm... I didn't have to upgrade our DBD drivers when we moved from 3.23.57 to 4.0.18. Strange you had to. I very distinctly remember reading the onsite documentation which stated that, since the C headers were changed, relative to 3.23.x, that I needed to reinstall the DBD drivers as well (not just DBI). Which I did. Do you remember your old DBD driver's version? Not sure any more. But, like I said, I believe it was the header changing stuff that made upgrading a necessity. Or was that only Win32 problem? Dunno. I am running FreeBSD 4.9R. ;) - Mark -- 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.20 is released
On Tue, May 18, 2004 at 10:56:14PM +0200, Mark wrote: Mihail Manolov wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) Hmm... I didn't have to upgrade our DBD drivers when we moved from 3.23.57 to 4.0.18. Strange you had to. I very distinctly remember reading the onsite documentation which stated that, since the C headers were changed, relative to 3.23.x, that I needed to reinstall the DBD drivers as well (not just DBI). Which I did. If you kept the old libmysqlclient.so.?? around that wouldn't be a problem. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql/innodb configuration
On Fri, 16 Apr 2004, mayuran wrote: I would like to optimize the configuration settings for this beast of a machine, here are the specs: Quad Xeon 3ghz (4x2 = 8 cpus), 512 cache 16 gigs ram running Redhat Enterprise 3.0 AS All tables are InnoDB. I read this warning in the MySQL documentation: *Warning:* On GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| will allow the process heap to grow over thread stacks, which will crash your server. But at the same time it says: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB Does this mean that MySQL wont make use of the 16gb it has total ? I had to set the value to 1G to make it even start up. You should be able to get higher than 1 gig ... a bit ... 1.5 gigs perhaps. But yes, unfortunately mysql and innodb can't directly use most of the memory. innodb has support on windows for using Intel's paged address extensions (PAE) to have paged access to more memory using the AWE interface, with a bit of a performance hit for doing so. However, that feature of innodb isn't available on Linux, plus it disables innodb's adaptive hashing support, which can be annoying especially considering mysql doesn't otherwise support anything like a hash join. The memory will still be used by your OS for caching files, which will help... but that isn't really as good as if innodb could use it, since multilevel caching can be a bit sketchy and some features of innodb (again, adaptive hashing...) can only be done if innodb has the data in it's cache. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql/innodb configuration
I would like to optimize the configuration settings for this beast of a machine, here are the specs: Quad Xeon 3ghz (4x2 = 8 cpus), 512 cache 16 gigs ram running Redhat Enterprise 3.0 AS All tables are InnoDB. I read this warning in the MySQL documentation: *Warning:* On GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| will allow the process heap to grow over thread stacks, which will crash your server. But at the same time it says: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is 2GB Does this mean that MySQL wont make use of the 16gb it has total ? I had to set the value to 1G to make it even start up. What other parameters can I tweak in the conf for maximum performance ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB large mem allocation on G5
Has anyone had any luck getting MySQL 4.0.18 (specifically innodb_buffer_pool_size) to use large amounts of memory on a G5 running OS X Server 10.3.3? I initially tried with the MySQL built binary, and was unable to get innodb_buffer_pool_size to go beyond 1.5 GB. Here's the my.cnf section that allowed MySQL to start: set-variable = key_buffer=128M set-variable = max_allowed_packet=1M set-variable = table_cache=4096 set-variable = sort_buffer=4M set-variable = max_connections=40 set-variable = record_buffer=2M set_variable = tmp_table_size=2M set-variable = thread_cache=8 set-variable= interactive_timeout=2000 set-variable= wait_timeout=2000 # Try number of CPU's*2 for thread_concurrency set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=32M default-table-type=innodb set-variable = long_query_time=120 log-slow-queries set-variable = query_cache_type=1 set-variable = query_cache_size=1M datadir= /raid/mysql-data # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /raid/mysql-data innodb_data_file_path = ibdata1:4000M;ibdata2:4000M:autoextend innodb_log_group_home_dir = /raid/mysql-data/ innodb_log_arch_dir = /raid/mysql-data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high set-variable = innodb_log_files_in_group=4 set-variable = innodb_buffer_pool_size=1536M set-variable = innodb_additional_mem_pool_size=10M # Set .._log_file_size to 25 % of buffer pool size set-variable = innodb_log_file_size=200M set-variable = innodb_log_buffer_size=16M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=8 If I put innodb_buffer_pool_size=2048M on the pre-built binary MySQL won't start, InnoDB dies trying to allocate memory: 040414 12:01:32 mysqld started *** malloc: vm_allocate(size=2147504128) failed (error code=3) *** malloc[5630]: error: Can't allocate region InnoDB: Fatal error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 15438152 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 10; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=2093056 max_used_connections=0 max_connections=40 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 376671 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Writing a core file 040414 12:01:33 mysqld ended I then looked at some previous posts on this list about needing to compile with G5 specific flags and the current OS X max memory per process being 4GB. List discussion: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm= bv3gfm%242bga%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq% 3Dmac%2520os%2520x%2520mysql%2520memory%2520g5%26hl%3Den%26lr%3D%26ie% 3DUTF-8%26oe%3DUTF-8%26sa%3DN%26tab%3Dwg G5 tuning: http://developer.apple.com/technotes/tn/tn2086.html#G5options So I compiled MySQL from source using this config line: CC=gcc CFLAGS=-O3 -fno-omit-frame-pointer -mcpu=970 -mtune=970 -mpowerpc64 -mpowerpc-gpopt CXX=gcc CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mcpu=970 -mtune=970 -mpowerpc64 -mpowerpc-gpopt ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared --with-innodb Which is basically a combination of what Apple recommends and what MySQL says is the config for the pre-built OS X binary. This is using gcc 3.3 on the latest version of OS X Server with the latest version of the developer tools (XTools) installed. This improved things somewhat...innodb_buffer_pool_size will now go to 2 GB, but if I try to go beyond I get the same error. Does anyone have suggestions for pushing innodb_buffer_pool_size beyond 2 GB? We can decrease the MyISAM settings pretty drastically if necessary as everything but the grant tables are InnoDB. Still, even with the settings above I would have expected to be able to get InnoDB to 3 GB if the overall process limit is 4 GB. Thanks for any help, Ware Adams -- MySQL General
can anyone help with mysql/innodb stuff?
I have a problem with mysql/innodb, hopefully someone here can offer a nice pointer. our innodb databases got wiped out (oops), and the problem is the .frm files dont seem to match the structure needed, we tried working around that in the docs online, but now we get this message: InnoDB: Error: table ./dan_logs_innodb/sent has a primary key in InnoDB InnoDB: data dictionary, but not in MySQL! can anyone tell me how to fix this? Thanks, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.0.18 is released
Hi! InnoDB is a MySQL table type which provides transactions, row-level locking, foreign key constraints, and a non-free hot backup tool for backing up InnoDB tables. InnoDB is included in all MySQL-4.0, 4.1, and 5.0 downloads, and also in the MySQL Pro commercial, non-GPL MySQL license. Release 4.0.18 is mainly a bugfix release, but there are some functional changes in FOREIGN KEY handling as well. The full changelog: * Do not allow dropping a table referenced by a FOREIGN KEY constraint, unless the user does SET FOREIGN_KEY_CHECKS=0. The error message here is somewhat misleading 'Cannot delete or update a parent row...', and must be changed in a future version 4.1.x. * Make InnoDB to remember the CONSTRAINT name given by a user to a FOREIGN KEY. * Change print format of FOREIGN KEY constraints spanning multiple databases to backquotedatabasenamebackquote.backquotetablenamebackquote. But when parsing them we must also accept backquotedatabasename.tablenamebackquote, because that was the output format in 4.0.18. * An optimization in locking: if AUTOCOMMIT=1, then we do not need to make a plain SELECT set shared locks even on the SERIALIZABLE isolation level, because we know the transaction is read-only: a read-only transaction can always be performed on the REPEATABLE READ level, and that does not endanger the serializability. * Implement an automatic downgrade from = 4.1.1 - 4.0.18 if the user has not created tables in .ibd files or used other 4.1.x features. CONSULT the manual section http://www.innodb.com/ibman.php#Multiple.tablespaces carefully if you want to downgrade! * Fixed a bug: MySQL should not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY. The MySQL manual says that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE. In versions 4.0.18 and 4.1.2 MySQL could resolve a duplicate key conflict in REPLACE by doing an UPDATE on the existing row, and FOREIGN KEY checks could behave in a semantically wrong way. (Bug #2418) * Fixed a bug: generate foreign key identifiers locally for each table, in the form 'databasename/tablename_ibfk_number'. If the user gives the constraint name explicitly, then remember it. These changes should ensure that foreign key id's in a slave are the same as in the master, and DROP FOREIGN KEY does not break replication. (Bug #2167) * Fixed a bug: allow quoting of identifiers in InnoDB's FOREIGN KEY definitions with backquote (`) and double quote (). You can now use also spaces in table and column names, if you quote the identifiers. (Bug #1725) (Bug #2424) * Fixed a bug: FOREIGN KEY ... ON UPDATE/DELETE NO ACTION must check the foreign key constraint, not ignore it. Since we do not have defered constraints in InnoDB, this bug fix makes InnoDB to check NO ACTION constraints immediately, like it checks RESTRICT constraints. * Fixed a bug: InnoDB crashed in RENAME TABLE if 'databasename.tablename' is shorter than 5 characters. (Bug #2689) * Fixed a bug: InnoDB row count and index cardinality estimates wrapped around at 512 million in 32-bit computers. Note that unless MySQL is compiled with the BIG_TABLES option, they will still wrpa around at 4 billion. * Fixed a bug: with InnoDB, UNIQUE secondary index, and NULL values in that unique index; with the IS NULL predicate, InnoDB returned only the first matching row, though there can be many. This bug was introduced in 4.0.16. (Bug #2483) 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]
Re: MySQL/InnoDB-5.0.0 is released
Ramesh, - Alkuperäinen viesti - Lähettäjä: Ramesh Vadlapatla [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lähetetty: Monday, December 29, 2003 10:48 PM Aihe: Re: MySQL/InnoDB-5.0.0 is released Hello Heikki, This is excellent news. Stored Procedures support is really cool. Just a couple of confirmations/questions: 1. Does this release support Sub Queries? yes. 2. Does this release support I18N? MySQL-4.1.1 (and presumably 5.0.0) support UTF8 and UCS-2 with some restrictions: http://www.mysql.com/doc/en/Charset-Unicode.html Note also that InnoDB does not yet support multiple character sets in one installation. I have promised to add that support to InnoDB in 4.1.2. 3. Where can I download this from? I couldn't see it in the Download(s) page. http://www.mysql.com/downloads/mysql-5.0.html thanks, Ramesh Best regards, Heikki Innobase Oy http://www.innodb.com 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 Order MySQL support from http://www.mysql.com/support/index.html On Mon, 29 Dec 2003, Heikki Tuuri wrote: Hi! Due to Christmas, this release note comes late. For MySQL users release 5.0.0 is a milestone: you can now write stored procedures in MySQL. InnoDB in this MySQL release is essentially the same as in 4.1.1, with the bug fixes of 4.0.17 included. Later 5.0.x versions will probably include new space saving table formats for InnoDB. IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1 or higher (like to 5.0.0), you cannot downgrade to a version lower than 4.1.1 any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. 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 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]
MySQL/InnoDB-5.0.0 is released
Hi! Due to Christmas, this release note comes late. For MySQL users release 5.0.0 is a milestone: you can now write stored procedures in MySQL. InnoDB in this MySQL release is essentially the same as in 4.1.1, with the bug fixes of 4.0.17 included. Later 5.0.x versions will probably include new space saving table formats for InnoDB. IMPORTANT NOTE: if you upgrade to InnoDB-4.1.1 or higher (like to 5.0.0), you cannot downgrade to a version lower than 4.1.1 any more! That is because earlier versions of InnoDB are not aware of multiple tablespaces. 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 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]
MySQL/InnoDB-4.0.17 is released
Hi! InnoDB is a MySQL table type which is included in all GPL versions of MySQL-4.0 that you can download from http://www.mysql.com, and is included in the commercial MySQL license MySQL Pro, which you can buy from https://order.mysql.com/?sub=pgpg_no=1. You can buy technical support for InnoDB from http://www.mysql.com/support/index.html. Remember that you have to buy an ADVANCED support contract WITH the InnoDB OPTION. You have to check the box on the second order page to include the InnoDB support option. InnoDB is the only MySQL table type which supports transactions, row level locking, multiversioned concurrency control, and foreign key constraints. An separate online binary hot backup tool for InnoDB can be bought from http://www.innodb.com/order.php. ... The MySQL/InnoDB version 4.0 is the stable version which is recommended for production use. Release 4.0.17 of InnoDB contains only a few bug fixes associated with column prefix secondary indexes, for example, CREATE TABLE t (a VARCHAR(255), INDEX a(10)) TYPE=InnoDB; and UPDATEs of secondary index columns when the new updated value is alphabetically equivalent, e.g., 'abcde' - 'aBCdé'. There are several not-yet-fixed non-critical bugs in InnoDB-4.0.17. Their fixing was delayed because of the effort to release 4.1.1 earlier this month. ... The full changelog for InnoDB-4.0.17: * Fixed a bug: if you created a column prefix secondary index and updated it so that the last characters in the column prefix were spaces, InnoDB would assert in row0upd.c, line 713. The same assertion failed if you updated a column in an ordinary secondary index so that the new value was alphabetically equivalent, but had a different length. This could happen, for example, in the utf-8 character set if you updated a letter to its accented or umlaut form. * Fixed a bug: InnoDB could think that a secondary index record was not locked though it had been updated to an alphabetically equivalent value, e.g., 'abc' - 'aBc'. * Fixed a bug: if you updated a secondary index column to an alphabetically equivalent value, and rolled back your update, InnoDB failed to restore the field in the secondary index to its original value. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
Dmitry, we have to consider implementing NOWAIT and SKIP LOCKED clauses to SQL statements. The latter would be useful in implementing transactional queues. But there are lots of items in the TODO list. Do not expect these soon. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Dmitry Anikin [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, December 15, 2003 11:26 AM Subject: Can I really have no_wait row-locks in MySQL+InnoDB? Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- 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: Can I really have no_wait row-locks in MySQL+InnoDB?
mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
At 01:14 AM 12/16/2003, you wrote: mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris Chris, Hot Dang you're good! :-) I didn't see this in any of this in Paul Dubois's books. Maybe it could be added in a future version??? Call the section The Blame Game and put it into the Appendix called MySQL Survivor Game..Be the last one to be voted off the island.. bg Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
mos wrote: At 01:14 AM 12/16/2003, you wrote: mos wrote: At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) No no no!!! You've got it all wrong! You go and find the user who had the least involvement in causing the deadlock and scapegoat them! Either that, or you use it as an excuse for doing one of the following things: 1. Getting the purchase of more hardware approved. 2. Getting the purchase of MySQL Pro and InnoDB Hot Backup approved 3. Getting a MySQL support contract purchase approved 4. Getting a raise for resolving the situation with poise, grace and subtlety (Eg: Shutting it down, blaming users, blaming SCO...) Regards, Chris Chris, Hot Dang you're good! :-) I didn't see this in any of this in Paul Dubois's books. Maybe it could be added in a future version??? Call the section The Blame Game and put it into the Appendix called MySQL Survivor Game..Be the last one to be voted off the island.. bg Mike I think it would be better to base it on the prophetic vision of how reality television is set to evolve: Series 7. Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I really have no_wait row-locks in MySQL+InnoDB?
Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
Hi, Dmitry Anikin schrieb: Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing What you describe is basically not a deadlock situation! A deadlock means that two sessions wait for each other in such a way that neither can proceed before the other one has finished its transaction. Regards, Frank. algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I really have no_wait row-locks in MySQL+InnoDB?
To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Dr. Frank Ullrich wrote: Hi, Dmitry Anikin schrieb: Suppose some user issued 'select ... for update', then went for coffee-break (to think hard on what he really wants to update in that row). Another client tries to update the same row and I don't want him to wait, just immediately return an error, so he could do some other useful task meanwhile. I haven't found any no_wait option for locks in the manual :(. There's a variable innodb_lock_wait_timeout, though, but unfortunately I can't assign 0 to it (min. value is 1). Still, 1 second time-out can be bearable (although I'd appreciate a way to reduce it to zero) but what disturbs me is that I've read in the manual that deadlock-removing What you describe is basically not a deadlock situation! A deadlock means that two sessions wait for each other in such a way that neither can proceed before the other one has finished its transaction. Regards, Frank. algorithm aborts transaction which it thinks is most suitable for aborting (not last-in-first-aborted). Since time-out feature has something to do with deadlocks can I be absolutely sure that WAITING transaction will be aborted and not that which issued the lock? And also it would be fine to have non-destructive means to determine whether some row has been locked so I may just skip (postpone) some updates without rollback of whole transaction. Is it possible? -- 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: Can I really have no_wait row-locks in MySQL+InnoDB?
At 04:22 AM 12/15/2003, you wrote: To elaborate on Dr Frank's thing if you're interested, here's a classic deadlock example: 1. Transaction A obtains an exclusive lock on a set of rows which we will call R1. 2. Transaction B obtains an exclusive lock on another set of rows which we will call R2. 3. Transaction A requests (but obviously doesn't acquire) an exclusive lock on R2. 4. Transaction B requests (but obviously doesn't acquire) an exclusive lock on R1. 5. Classic deadlock! There are various ways of dealing with this (with timeouts and graph traversal seemingly popular). As you can see, neither transaction can go forward. Thus, the almighty InnoDB will think for a moment, decide which transaction it has something personal against, laugh at it and force it to ROLLBACK. That transaction can then try again if it doesn't feel totally small and humiliated. Hope this helps! Regards, Chris Chris, When my MySQL database gets into a deadlock situation like that, I just shutdown the server, power off the machine and go home. Works every time.vbg Mike (Sorry, it's been a late night) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.1 is released
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]
MySQL/InnoDB-4.0.16 +Optimizer behaviour
Hello all, Default optimizer behavior has changed in 4.0.16 (since 4.0.14) for this simple question: select state_id, orderdata_id from order_delivery where orderdata_id in (3193340,3193343,3193346,3193349,3193352,3193355) and is_deleted=0 order by xtime desc CREATE TABLE `order_delivery` ( `orderdata_id` int(11) NOT NULL default '0', `state_id` int(11) NOT NULL default '0', `xtime` datetime NOT NULL default '-00-00 00:00:00', `admin_user_id` int(11) NOT NULL default '0', `note` text NOT NULL, `is_deleted` int(1) NOT NULL default '0', KEY `orderdata_id` (`orderdata_id`), KEY `is_deleted` (`is_deleted`), KEY `xtime` (`xtime`) ) TYPE=InnoDB | 4.0.14 === mysql EXPLAIN select state_id, orderdata_id from order_delivery where - orderdata_id in - (3193340,3193343,3193346,3193349,3193352,3193355) - and is_deleted=0 order by xtime desc; ++---+-+--+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+-+--+-+--+--+-+ | order_delivery | range | orderdata_id,is_deleted | orderdata_id | 4 | NULL | 5 | Using where; Using filesort | ++---+-+--+-+--+--+-+ 1 row in set (0.25 sec 4.0.16 === ++--+-++-+---++-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+-++-+---++-+ | order_delivery | ref | orderdata_id,is_deleted | is_deleted | 4 | const | 228021 | Using where; Using filesort | ++--+-++-+---++-+ 1 row in set (0.15 sec) Due to this simple queries with WHERE IN() became aprox. 30 time slower in my case. MyISAM in not affected. Have anyone ever seen such problems with InnoDB tables in 4.0.16? -- Best regards, Sergey S. Kostyliov [EMAIL PROTECTED] Public PGP key: http://sysadminday.org.ru/rathamahata.asc -- 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, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM 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
Jon, FULLTEXT is far away, unless we find a corporate sponsor. Could your company sponsor the porting? Journaled file systems are just extra overhead for InnoDB, though the overhead seems to be small. Regards, Heikki - Alkuperäinen viesti - Lähettäjä: Jon Hancock [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Lähetetty: Monday, October 27, 2003 10:42 AM Aihe: Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1 Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM 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
Hi, As I have said before, I'm not Heikki, but I'm such a massive geek I'm likely to have one or two useful bits of info for you. :-) 1. You'd have a rough time getting indexes and tables to be seperated out, unless you were willing to set up your various symlinks/hardlinks by hand. Even then, you may be inviting problems. Additionally, no date has been announced for FULLTEXT indexing on InnoDB tables, and Heikki considers it a low priority by the looks of things (not having a go at the god of multiversioned DBs, just making a possibly incorrect observation). 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Hope this helps! Regards, Chris Jon Hancock wrote: Heikki, I have two questions in regards to the tablespace changes: 1 - You mention being able to store indexes in a seperate tablespace. How far off is this for MySQL to implement? I would like to see FULLTEXT indexes stored in seperate tablspace (seperate RAID channel) so the two features (InnoDB FULLTEXT) would both need to be available. 2 - Is there any value to using Journaled file systems with the InnoDB tablespaces? A new system I'm putting together will have seperate drives for only InnoDB data. Is a Journaled file system extra overhead? If so, is Raw significantly more efficient? How does this choice effect backup soultion? thanks, Jon - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 24, 2003 9:55 PM 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]
InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)
On Monday, October 27, 2003, at 07:45 AM, Chris Nolan wrote: 2. I personally use ReiserFS for all of my stuff, most of which is based upon InnoDB. One thing you have to remember is that InnoDB treats the space inside the tablespace as a Berkeley Fast Filesystem-style space, using the underlaying filesystem minimally. To quote the manuals, raw partition usage can speed up IO on a number of UNIXes (and Windows too seemingly). Regarding backup, you'd need to use mysqldump or InnoDB Hot Backup to backup a raw-partition setup. This isn't a bad thing though - I use mysqldump and can get a consistant snapshot of a 12 GB DB without problems while the thing is running. Just out of curiosity, has anyone been able to get InnoDB to use a raw partition in OSX? When I tried it, it complained about the file already existing. - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
