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]
Re: Foreign Keys in CREATE TABLEs produced by mysqldump
Michael, - Alkuperäinen viesti - Lähettäjä: <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Monday, December 29, 2003 11:53 PM Aihe: Re: Foreign Keys in CREATE TABLEs produced by mysqldump > "Heikki Tuuri" <[EMAIL PROTECTED]> wrote on 12/24/2003 01:53:07 PM: > > > Michael, > > > > this is the question where the valid answer is: upgrade! > > > > In which version was this corrected? I failed to mark it to the release notes :(. It was fixed in some 4.0.x version <= 4.0.12. > I'm hesitant to upgrade to the latest > and greatest without it being banged on by the masses first.. InnoDB-4.0.17 should be stabler than any of 3.23.xx. It contains more diagnostic code and more checks. > I have half a dozen or so business applications running w/ this mysql data > store, regression testing them would involve an amount of time I can not > afford to spend. The 3.23 branch works well for us right now and I have no > real need to use 4.0 (feature-wise.) So until 3.23.x is no longer > supported or there is a dire need to use 4.x functionality... I can't make > a business case justifying the regression test work. I believe 3.23.58 was the very last release from the 3.23 series. > > " > > MySQL/InnoDB-3.23.57, June 20, 2003 > > > > Changed the default value of innodb_flush_log_at_trx_commit from 0 > > to 1. If you have not specified it explicitly in your my.cnf, and your > > application runs much slower with this new release, it is because > > the value 1 causes a log flush to disk at each transaction commit. > > " > > Thats it! Thanks. > > -mike 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb defragmentation question
Franky, since MySQL performs ALTER TABLE ... TYPE = InnoDB; by totally rebuilding the table, it is very normal that the space usage temporarily doubles in ibdata files. But if it doubles also after an immediate SECOND rebuild, then that must be a bug. If you can reproduce that phenomenon, please send me the following before and after each step: 1) SHOW TABLE STATUS; 2) SHOW INNODB STATUS; 3) ls -l in the datadir, 4) and what the following prints to the .err log: CREATE TABLE innodb_table_monitor(a INT) TYPE = InnoDB; DROP TABLE innodb_table_monitor; Note that if you use multiple tablespaces in 4.1.1, then InnoDB will delete the old .ibd file after the rebuild, and the disk space is freed to the OS. Thus, multiple tablespaces help in your problem. Note also that the big transaction which builds the new table will also use some 20 bytes per row in the undo logs in the system tablespace, that is, the ibdata files. And the ibdata files will not shrink in 4.1.1, either. The space in undo logs is freed within the ibdata files, and can be used for undo logs of other transactions, but the space is not freed to the OS. 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 ... List:MySQL General Discussion« Previous MessageNext Message » From:Franky Van LiedekerkeDate:December 30 2003 3:00pm Subject:innodb defragmentation question since it has been the holidays, I can understand this one failed to draw attention of the people able to answer, so I'm sending it again: Hi all, if I try to defrag an InnoDB table (using "alter table ... type=innodb;") I see the size of ibdata1 growing to almost double its size, and again it doubles if I try it again ... there's only 1 innodb table in my setup, so no other table can be causing this. Is this intentional/normal or is there something I should configure? I'm running mysql-4.0.16 on solaris 2.8. Franky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Wait Timeout
Randy, that is expected: http://www.innodb.com/ibman.php#Locks_set_by_statements " If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete which requires checking of the constraint condition sets shared record level locks on the records it looks at to check the constraint. Also in the case where the constraint fails, InnoDB sets these locks. " 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 .. From: "Randy Chrismon" ([EMAIL PROTECTED]) Subject: Re: Lock Wait Timeout View: Complete Thread (2 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-12-30 11:12:12 PST Well now, this is interesting. The agent mentione previously ran flawlessly for a couple of weeks. The day I left for Christmas vacation, I started getting the lock wait timeout error. The difference? I altered the table to include a foreign key constraint. Not sure how this could possibly be the issue but when I dropped the foreign key constraint, the agent worked perfectly. I'm still at a loss as to why this problem happened. I'm using INNODB tables in MySQL 4.0.16. It looks like the one record which timed out was being updated and the referenced record and field values existed. In addition, I had already updated 26,000 records so I can't figure out what's wrong with this one particular record. Any thoughts appreciated. Randy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb defragmentation question
Franky, you apparently have just the PRIMARY KEY defined on the table and no secondary indexes. I guess the fragmentation comes because you delete rows from in the middle with respect to the primary key ordering? Or do you also insert in random order? Anyway, you cannot do anything to prevent fragmentation. Below the average length of a row in 50 bytes. It cannot yet be terribly fragmented, because the minimum length for an InnoDB row is about 20 bytes. 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 From: Franky Van Liedekerke ([EMAIL PROTECTED]) Subject: Re: innodb defragmentation question View: Complete Thread (4 articles) Original Format Newsgroups: mailing.database.myodbc Date: 2003-12-31 00:33:47 PST the problem is that, if it happens again, I get a file of 900 MB, which gets kinda big ... In order to rectify the situation after that, I'll need to dump all innodb tables, drop them and reinsert them. This would takes hours, and in the meantime the application running on top of it would be down ... Anyway, here's the output of "show table status" and "show innodb status" for the corresponding tables. Maybe you can tell me how to check if defrag is even needed? | history | InnoDB | Fixed | 6132057 | 50 | 310378496 |NULL |0 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | users_groups| InnoDB | Fixed | 0 | 0 | 16384 |NULL |0 | 0 |NULL | NULL| NULL| NULL || InnoDB free: 8192 kB | | usrgrp | InnoDB | Dynamic| 7 | 2340 | 16384 |NULL |16384 | 0 | 8 | NULL| NULL| NULL || InnoDB free: 8192 kB And the "show innodb status": = 031231 9:31:03 INNODB MONITOR OUTPUT = Per second averages calculated from the last 23 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 269898, signal count 269639 Mutex spin waits 164682, rounds 1645099, OS waits 81402 RW-shared spins 345770, OS waits 172804; RW-excl spins 15688, OS waits 15679 TRANSACTIONS Trx id counter 0 7556158 Purge done for trx's n:o < 0 2341232 undo n:o < 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 150765 MySQL thread id 150754, query id 42886888 localhost root SHOW INNODB STATUS ---TRANSACTION 0 7556157, not started, OS thread id 55 MySQL thread id 44, query id 42886887 localhost root FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 107685 OS file reads, 11603482 OS file writes, 6685465 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.09 writes/s, 4.26 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 144, node heap has 1 buffer(s) 0.96 hash searches/s, 3.35 non-hash searches/s --- LOG --- Log sequence number 0 395581663 Log flushed up to 0 395581663 Last checkpoint at 0 395580831 0 pending log writes, 0 pending chkp writes 6292464 log i/o's done, 4.00 log i/o's/second -- -- BUFFER POOL AND MEMORY -- Total memory allocated 17823008; in additional pool allocated 719232 Buffer pool size 512 Free buffers 0 Database pages 511 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 110330, created 17200, written 6223975 0.00 reads/s, 0.00 creates/s, 4.30 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 10, state: sleeping Number of rows inserted 6116574, updated 0, deleted 9399, read 15517877 3.91 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.74 reads/s END OF INNODB MONITOR OUTPUT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQLDump loses connection on large dump.
Pete, what does SHOW VARIABLES LIKE '%timeout%'; say? Does the MNRD mysqld server crash? Anything in the .err log? What do you have as max_packet_size in my.cnf? Could row: 13154861 be bigger than that? 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Pete McNeil"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, January 06, 2004 1:27 AM Subject: MYSQLDump loses connection on large dump. > Thanks in advance for any help. > > I'm using mysqldump to create snapshots of slave database servers. > I am upgrading from 4.0.14 on Redhat 8 to 4.0.17 Fedora Core 2. > I have a pair of 4.0.14 boxes MNR6 master --> MNR7 slave. > I have a pair of 4.0.17 boxes MNRC master --> MNRD slave. > > In moving to the new boxes I have set up this replication sequence: > > MNR6->MNR7->MNRC->MNRD > > I am using this command to make backups at the slaves: > > mysqldump -A -Q --opt --user=xx --password=xx | gzip > > /mnt/drive-u/MySQL-Backups/`date -I`.mnrd-backup.sql.gz > > This is done in a script once per day on the slave of a pair. > /mnt/drive-u is a samba file link to a central server for backups. > > When I run this script on MNR7 it runs flawlessly. > > When I run this script on MNRD I get the following error: > > mysqldump: Error 2013: Lost connection to MySQL server during query when > dumping table `RuleHistogram` at row: 13154861 > > RuleHistogram is in the largest InnoDB table. > > The configuration of MNR6 and MNR7 is underpowered for the application, > but it works. One 2.4GHz P4, 1 GB Ram, 2IDE HD in raid 1. > > MNRC and MNRD were built for the application and have much better > hardware: Two 2.4GHz XEON, 4GB Ram, 4 SATA Drives Raid 10 via 3Ware > Escalade. > > MNRC and MNRD respond nicely to queries and show no signs of trouble > except for the above. > > My Question: Why would the more powerful servers with the more advanced > software stall during a mysqldump operation when the less powerful > survers successfully perform an identical operation on identical data > without a hitch? > > What can I do to solve this problem with mysqldump? > > (BTW: I just sat through another failure - watching top, mysqld goes to > idle along with mysqldump... they wait a few seconds in this condition, > then the error occurs and the script stops. The mysql server remains > responsive to queries before and after the operation. No errors are > reported in the .err file.) > > Thanks again in advance! > > _M > > Pete McNeil (Madscientist) > President, MicroNeil Research Corporation > Chief SortMonster, www.SortMonster.com > VOX: 703-406-2016 > FAX: 703-406-2017 > > > -- > 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: mediumtext crash on strings > 1MB?
Chris, you should set max_packet_size in my.cnf. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Chris Seidel" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, January 05, 2004 11:07 PM Subject: mediumtext crash on strings > 1MB? > Hello, > > I have a table in which one of the column types has been > declared as mediumtext to hold blocks of text averaging 250k > in size. A mediumtext datatype should be able to hold > 16 million > characters. However, I found my inserts failing when some of > my strings exceeded 1 MB (e.g. a string of 1125921 bytes fails, > while a string of 1009684 bytes succeeds). > > I've been using this table for months, but recently some of my records > grew to greater than 1 MB, and suddenly my inserts stopped working. > > Is there a bug with mediumtext such that it holds less than the advertised > max? > > I'm using MySQL 3.23.56 on Linux RedHat 8, and have tried inserts via > Perl DBI as well as via SQL command line. > > -Chris Seidel > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb_buffer_pool_size / *_buffer_size relation
Roman, set-variable = sort_buffer_size=1M set-variable = read_buffer_size=1M set-variable = read_rnd_buffer_size=1M are relevant also for InnoDB. Only a small key_buffer for MyISAM is normally needed. MySQL's system tables are always MyISAM type, and in some rare cases MySQL may create MyISAM type temporary tables when processing DISTINCT or ORDER BY. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Roman Neuhauser" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, January 05, 2004 4:20 PM Subject: innodb_buffer_pool_size / *_buffer_size relation > I've been trying to tune a MySQL installation a bit, and have a few > questions regarding relations/interactions between InnoDB and MySQL > when it comes to the various buffers... I wanted this mail to be more > specific, but given the lack of info in the manual, I basically don't > have much cannon fodder. > > My first question is: what is the interaction between MySQL's > *_buffer_size and InnoDB's innodb_buffer_pool_size? > IOW, are *_buffer_size tunables applicable to InnoDB tables? Would I > need to tune e. g. key_buffer_size in a server with only InnoDB tables? > > -- > If you cc me or remove the list(s) completely I'll most likely ignore > your message.see http://www.eyrie.org./~eagle/faqs/questions.html > > -- > 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 error
Don, the InnoDB text below is only a help message, not an error, and is not dangerous. It is not associated with the timeout problems you have on Fedora. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Don Matlock"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, January 06, 2004 2:07 AM Subject: Mysql error > Hi all, > Larry Brown tried to help me out with this one (much appreciated Larry), > anyways, when I start up fedora I get this error from mysql that says it > has timed out...it still works, but this error shows up in the mysql > logs: > > 040104 18:38:37 mysqld started > Cannot initialize InnoDB as 'innodb_data_file_path' is not set. > If you do not want to use transactional InnoDB tables, add a line > skip-innodb > to the [mysqld] section of init parameters in your my.cnf > or my.ini. If you want to use InnoDB tables, add to the [mysqld] > section, for example, > innodb_data_file_path = ibdata1:10M:autoextend > But to get good performance you should adjust for your hardware > the InnoDB startup options listed in section 2 at > http://www.innodb.com/ibman.html > /usr/libexec/mysqld: ready for connections > > Everytime I restart fedora this is the log that is made. > Mysql is still working as my phpBB2 dateabase is still functioning...but > it is annoying. > When I set fedora to run at run lvl 3 instead of 5...Mysql and apache > stop working all together (the reason I know this is I wanted to stop X > from starting every time I booted up...wanted it to boot into a text > start up). > > Anyways...anythoughts on how I might be able to remedy this > error...Larry suggested I check the rc.d files and see if mysql is > pre-maturely starting...but I really don't know what to look for in > those areas:( > Anyways...any thoughts are greatly appreciated. > Don > > > > > -- > 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: Binaries for 4.x
Kool, there are no longer GPL binaries of MySQL for SCO operating systems. Only commercial binaries that you have to buy from MySQL AB. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "kooldude" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, January 06, 2004 4:34 PM Subject: Binaries for 4.x > Does anyone have an idea where I could obtain binaries for MySQL 4.x for > either SCO openserver release 5 or AIX 4.2.1 > > The binaries avalable from MySQL AB stop at version 3.22.32 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 Production Release
Hi! I have a hobby of predicting release dates. It took 17 months for 4.0 to be declared 'production'. That gives us an estimate that 4.1 will be declared 'production' in September 2004. But since 4.1.1 already seems to be a very stable release, I expect it to happen sooner. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Tobias Asplund" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, January 06, 2004 10:41 PM Subject: RE: MySQL 4.1 Production Release > On Tue, 6 Jan 2004, Knepley, Jim wrote: > > > Any chance that there's a quarterly strategic roadmap published > > somewhere? > > > > I have projects that sometimes depend on a feature in the next rev' or > > some such, and I need to plan out for my organization... Difficult to > > answer my boss when the dependencies are released "when they're ready." > > > > This is a snip from the 5.0.0 release message: > > "THe MySQL 4.1 branch seams to be relatively stable and we will, if we > don't find any new unexpected hard bugs that will require new design > decisions, make a beta release of 4.1 in January followed by a gamma > release ASAP." > > > cheers, > Tobias > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb_buffer_pool_size / *_buffer_size relation
Roman, - Alkuperäinen viesti - Lähettäjä: "Roman Neuhauser" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Wednesday, January 07, 2004 8:05 PM Aihe: Re: innodb_buffer_pool_size / *_buffer_size relation > # [EMAIL PROTECTED] / 2004-01-06 10:57:52 +0200: > > From: "Roman Neuhauser" <[EMAIL PROTECTED]> > > > My first question is: what is the interaction between MySQL's > > > *_buffer_size and InnoDB's innodb_buffer_pool_size? > > > IOW, are *_buffer_size tunables applicable to InnoDB tables? Would I > > > need to tune e. g. key_buffer_size in a server with only InnoDB tables? > > > > set-variable = sort_buffer_size=1M > > set-variable = read_buffer_size=1M > > set-variable = read_rnd_buffer_size=1M > > > > are relevant also for InnoDB. > > > > Only a small > > > > key_buffer > > > > for MyISAM is normally needed. MySQL's system tables are always MyISAM type, > > and in some rare cases MySQL may create MyISAM type temporary tables when > > processing DISTINCT or ORDER BY. > > I take it key_buffer_size is only applicable to MyISAM tables, yes, but there are always at least some small MyISAM tables in a MySQL installation. > sort_buffer_size, read_buffer_size and read_rnd_buffer_size are > relevant to InnoDB, Yes. > but the above could also be read as "you should > set them to 1M if you use InnoDB". That is a good value for all table types. > Also, the question about the relation remains without answer. What relation? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: consistency checking InnoDB tables
Hi! - Original Message - From: "robert_rowe" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, January 08, 2004 7:16 PM Subject: Re: consistency checking InnoDB tables > > I had the same question a while back and I was told that InnoDB does this automatically. There are no utilities that I could find. > CHECK TABLE tablename; is the way to check an InnoDB table for consistency. After a mysqld crash, you do not need to repair InnoDB type tables. The automatic crash recovery of InnoDB returns them automatically to a transaction-consistent state. If an InnoDB table becomes corrupt, the way to 'repair' it is to dump, drop, and reimport it. In cases of bad corruption you may need to resort to a backup of your database, or dump all tables and recreate the whole InnoDB tablespace (that is, the ibdata files and ib_logfiles). If InnoDB's crash recovery crashes or its internal cleaning processes crash because of database corruption, then you need to set set-variable = innodb_force_recovery=... in my.cnf to a value 1 ... 6. See http://www.innodb.com/ibman.php#Forcing_recovery for details. 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key contraints, on delete cascade not working?
Andrew,
please check with
SHOW VARIABLES LIKE '%innodb%';
that you really have InnoDB enabled.
I tested the script below on Linux with 4.0.8 and 4.0.18, and it worked ok.
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
Order MySQL technical support from https://order.mysql.com/
[EMAIL PROTECTED]:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database if exists MYDB;
Query OK, 0 rows affected (0.06 sec)
mysql> create database MYDB;
Query OK, 1 row affected (0.00 sec)
mysql> use MYDB;
Database changed
mysql> create table user (
-> useridvarchar (8)not null,
-> nametinytextnot null,
-> primary key (userid)
-> ) type=innodb; -- user
Query OK, 0 rows affected (0.03 sec)
mysql> create table useropts (
-> useridvarchar (8)not null,
-> nametinytext,
-> valuevarchar (128),
-> key user_index (userid),
-> foreign key (userid) references user (userid) on delete cascade
-> ) type=innodb; -- useropts
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user values ("userA", "User A");
Query OK, 1 row affected (0.00 sec)
mysql> insert into useropts values ("userA", "option", "value");
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+++
| userid | name |
+++
| userA | User A |
+++
1 row in set (0.00 sec)
mysql> select * from useropts;
+++---+
| userid | name | value |
+++---+
| userA | option | value |
+++---+
1 row in set (0.00 sec)
mysql> delete from user;
Query OK, 1 row affected (0.00 sec)
mysql> select * from useropts;
Empty set (0.00 sec)
mysql> select * from user;
Empty set (0.01 sec)
mysql>
- Original Message -
From: "Andrew DeFaria" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Saturday, January 10, 2004 7:03 AM
Subject: foreign key contraints, on delete cascade not working?
> I created the following .sql file to demonstrate a problem I'm having.
> According to the manual:
>
> If |ON DELETE CASCADE| is specified, and a row in the parent table
> is deleted, then InnoDB automatically deletes also all those rows in
> the child table whose foreign key values are equal to the referenced
> key value in the parent row.
>
> However:
>
> drop database if exists MYDB;
> create database MYDB;
> use MYDB;
> create table user (
> useridvarchar (8)not null,
> nametinytextnot null,
> primary key (userid)
> ) type=innodb; -- user
> create table useropts (
> useridvarchar (8)not null,
> nametinytext,
> valuevarchar (128),
> key user_index (userid),
> foreign key (userid) references user (userid) on delete cascade
> ) type=innodb; -- useropts
> insert into user values ("userA", "User A");
> insert into useropts values ("userA", "option", "value");
> select * from user;
> select * from useropts;
> delete from user;
> select * from useropts;
> select * from user;
>
> $ mysql
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 35215 to server version: 4.0.10-gamma
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql> source MYDB.sql
> Query OK, 0 rows affected (0.01 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Database changed
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 0 rows affected (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> Query OK, 1 row affected (0.01 sec)
>
> +++
> | userid | name |
> +++
> | userA | User A |
> +++
> 1 row in set (0.00 sec)
>
> +++---+
> | userid | name | value |
> +++---+
> | userA | option | value |
> +++---+
> 1 row in set (0.00 sec)
>
> Query OK, 1 row affected (0.00 sec)
>
> +++---+
> | userid | name | value |
> +++---+
> | userA | option | value |
> +++---+
> 1 row in set (0.00 sec)
>
> Empty set (0.00 sec)
>
> As you can see I when I delete from user (the parent table) the useropts
> (child table) entry remains. Shouldn't it be deleted?
>
>
> --
> E-mail returned to sender -- insufficient voltage.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON UPDATE CASCADE
Morten, http://www.innodb.com/ibman.php#InnoDB_foreign_keys " A deviation from SQL standards: if ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update a table for which there already is an update operation in the stack of cascaded operations, it acts like RESTRICT. In plain English this means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, works starting from 4.0.13. A self-referential ON DELETE CASCADE has always worked. " I realize InnoDB should give an error if you try to create a cyclic ON UPDATE CASCADE constraint. Or even better, we should implement it so that also that would work! Though the behavior in the case where the constraint recurses to update the same row twice is problematic. 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 Order MySQL technical support from https://order.mysql.com/ .. List:MySQL General Discussion« Previous MessageNext Message » From:Morten GulbrandsenDate:January 9 2004 1:47pm Subject:ON UPDATE CASCADE Hello, If I change the value of a reference a , for instance by means of updating or inserting values, I'd expect both updated values and inserted values to cascade, hence to change b, where FOREIGN KEY (b) REFERENCES A(a) ON UPDATE CASCADE In this example foreign key and reference are taken from one and the same table, that should be possible, please help me , what is wrong ? My code woun't cascade anything. Yours Sincerely Morten Gulbrandsen === -- DROP TABLE IF EXISTS A -- Query OK, 0 rows affected (0.04 sec) -- CREATE TABLE A ( a CHAR(9) NOT NULL, b CHAR(9), c INT NOT NULL DEFAULT 1, PRIMARY KEY (a), INDEX (b, c) )ENGINE = INNODB -- Query OK, 0 rows affected (0.05 sec) -- SHOW WARNINGS -- Empty set (0.00 sec) -- DESCRIBE A -- +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | a | char(9) | | PRI | | | | b | char(9) | YES | MUL | NULL| | | c | int(11) | | | 1 | | +---+-+--+-+-+---+ 3 rows in set (0.01 sec) -- ALTER TABLE A ADD FOREIGN KEY (b) REFERENCES A(a) ON DELETE SET NULL ON UPDATE CASCADE -- Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recreating InnoDB tables -WITHOUT- .frm
Matthew, http://www.innodb.com/ibman.php#InnoDB_Monitor " Starting from 3.23.44, there is innodb_table_monitor with which you can print the contents of the internal data dictionary of InnoDB. " The output format is not beautiful, and you have to manually reconstruct the MySQL CREATE TABLE statements from it. Adam, you can try creating a dummy InnoDB table with enough PRIMARY KEY columns. Then replace its .frm file with an old one you have, and try to print SHOW CREATE TABLE. I do not know if mysqld will crash or assert. This question was discussed on this mailing list some 2 years ago. 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 Order MySQL technical support from https://order.mysql.com/ ... List:MySQL General Discussion« Previous MessageNext Message » From:Matthew ScottDate:January 13 2004 5:33pm Subject:Recreating InnoDB tables -WITHOUT- .frm To all the InnoDB gurus out there: I have a similar problem to this person's predicament, except my situation is that I have all the innodb data and log files, but have absolutely no .FRM files. Are there any general tools for data recovery from InnoDB databases? Any companies that can do this for a fee? Anything??? :) Thanks. [EMAIL PROTECTED] wrote: > I'm cleaning up a user-error where the innodb data files were deleted > without a useful backup. I need to reconstruct the tables and still have > the frm files. Is this possible? A significant amount of time was put into > these tables' structures and I hate to lose that effort... > > Yes the users are kicking themselves about the backup... > > MTIA > Adam > -- Matthew Scott <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.58 seg faults occasionally
Michael, the crashes below happen in independent areas of code. The 2 first are inside InnoDB, and the third inside MySQL. This looks like random thread crashes, or random memory corruption. I assume that you have my.cnf set so that the memory usage cannot approach 2 GB. You are running a relatively new Linux kernel, 2.4.23. Did the crashes start when you upgraded Linux? 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 . List:MySQL General Discussion« Previous MessageNext Message » From:Michael BacarellaDate:January 16 2004 12:32am Subject:MySQL 3.23.58 seg faults occasionally First we cut to the chase with a resolved stack trace from the most recent crash: 0x80c23d5 handle_segfault__Fi + 425 0x40022f54 _end + 935506260 0x822cdef btr_search_build_page_hash_index + 4771 0x82281c3 btr_search_info_update_slow + 919 0x8213f9e btr_cur_search_to_nth_level + 3154 0x81e9dce row_sel_get_clust_rec_for_mysql + 102 0x81ece61 row_search_for_mysql + 6769 0x852 general_fetch__11ha_innobasePcUiUi + 322 0x8111220 index_next_same__11ha_innobasePcPCcUi + 40 0x80e7d7d join_read_next__FP14st_read_record + 53 0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341 0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412 0x80dff58 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1 3select_result + 5576 0x80c8aba mysql_execute_command__Fv + 806 0x80cbd88 mysql_parse__FP3THDPcUi + 72 0x80c7c74 do_command__FP3THD + 1324 0x80c7127 handle_one_connection__FPv + 659 and the one before it: 0x80c23d5 handle_segfault__Fi + 425 0x40022f54 _end + 935506260 0x823d4a8 trx_rseg_get_on_id + 24 0x823952d trx_undo_get_undo_rec_low + 45 0x823977d trx_undo_get_undo_rec + 49 0x82399c0 trx_undo_prev_version_build + 548 0x81f3f35 row_vers_build_for_consistent_read + 641 0x81e9d5e row_sel_build_prev_vers_for_mysql + 226 0x81ecda4 row_search_for_mysql + 6580 0x852 general_fetch__11ha_innobasePcUiUi + 322 0x8111373 rnd_next__11ha_innobasePc + 83 0x8103da6 rr_sequential__FP14st_read_record + 150 0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341 0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412 0x80dff58 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1 3select_result + 5576 0x80c8aba mysql_execute_command__Fv + 806 0x80cbd88 mysql_parse__FP3THDPcUi + 72 0x80c7c74 do_command__FP3THD + 1324 0x80c7127 handle_one_connection__FPv + 659 and the one before that: 0x80c23d5 handle_segfault__Fi + 425 0x40022f54 _end + 935506260 0x401141b7 _end + 936494007 0x80f42f7 write_header__9Log_eventP11st_io_cache + 91 0x80f426c write__9Log_eventP11st_io_cache + 24 0x80f424d write__15Query_log_eventP11st_io_cache + 37 0x80f write__9MYSQL_LOGP15Query_log_event + 1507 0x80eceec mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15en um_duplicates13thr_lock_type + 1752 0x80c9dd0 mysql_execute_command__Fv + 5692 0x80cbd88 mysql_parse__FP3THDPcUi + 72 0x80c7c74 do_command__FP3THD + 1324 0x80c7127 handle_one_connection__FPv + 659 Typically this happens to me on a heavily loaded server where I'm querying against a not very memory resident table so it takes a few seconds to load. Afterwards, when the table is better cached I issue a few more queries and one of them eventually causes the seg fault. It doesn't really crash on itself during normal load, only if I go in and introduce non-typical queries. All tables are InnoDB. Data and log files are stored on independent Linux MD based RAID-1 arrays. Data is stored on a raw array, logs are stored on an ext3fs. Host OS is Debian "stable" branch. The machine survived 18 days of CTCS burn-in before we turned MySQL loose. *** mysqlbug output: Server version 3.23.58-max-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 11 min 12 sec Threads: 10 Questions: 652813 Slow queries: 301 Opens: 39775 Flush tables: 1 Open tables: 256 Queries per second avg: 971.448 System: Linux dbms3 2.4.23 #1 SMP Tue Dec 23 03:08:01 EST 2003 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='-O2 -mpentiumpro' CXX='gcc' CXXFLAGS='-O2 -mpentiumpro -felide-constructors' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Dec 23 10:41 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x1 root root 1153784 Apr 8 2003 /lib/libc-2.2.5.so -rw-r--r--1 root root 2391002 Apr 8 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 8 2003 /usr/lib/libc
Re: Too slow recovering mysqldump files
Mikel, have you set the size of the InnoDB log files as recommended in the manual? 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Mikel -"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, January 19, 2004 7:25 PM Subject: Too slow recovering mysqldump files > Hi list, does anyone know a faster way to recover a mysqldump file cause > When I recovered one dump file it took 26 hours ! to finish, I think it's > too slow. > Thnx in advanced, greetings > > MySQL server 3.23.58 > RedHat 7.3 > 4GB RAM > 2 scsi disk via fiber channel (333GB each) > 2 processor Xeon 1.6GHZ > > dump file size: 2.5 GB > ibdata: 11GB > innodb tables > key_buffer=850M > innodb_buffer_pool_size=850M > table_cache=1500 > > _ > MSN. Más Útil Cada Día http://www.msn.es/intmap/ > > > -- > 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: Too slow recovering mysqldump files
Mikel, it is apparently disk-bound. I recommend setting innodb_buffer_pool_size as high as 1.4 GB during such big import. Adjust innodb_log_file_size accordingly, and make key_buffer smaller during the big import. Help is coming: Marko Mäkelä is writing a compressed InnoDB table format, which can squeeze a typical table to 1 / 4 the size of a normal InnoDB table. I believe the compressed format will be available in October 2004. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Mikel -" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 21, 2004 12:32 AM Subject: Re: Too slow recovering mysqldump files > Here we are my options: > > set-variable = innodb_log_file_size=150M > set-variable = innodb_log_buffer_size=8M > > I follow the example that came here: > "http://www.mysql.com/doc/en/InnoDB_start.html"; > > Greetings and best regards > > Mikel > > > >From: "Heikki Tuuri" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Subject: Re: Too slow recovering mysqldump files > >Date: Mon, 19 Jan 2004 22:44:50 +0200 > > > >Mikel, > > > >have you set the size of the InnoDB log files as recommended in the manual? > > > >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 > > > >Order MySQL technical support from https://order.mysql.com/ > > > >- Original Message - > >From: ""Mikel -"" <[EMAIL PROTECTED]> > >Newsgroups: mailing.database.myodbc > >Sent: Monday, January 19, 2004 7:25 PM > >Subject: Too slow recovering mysqldump files > > > > > > > Hi list, does anyone know a faster way to recover a mysqldump file > >cause > > > When I recovered one dump file it took 26 hours ! to finish, I think > >it's > > > too slow. > > > Thnx in advanced, greetings > > > > > > MySQL server 3.23.58 > > > RedHat 7.3 > > > 4GB RAM > > > 2 scsi disk via fiber channel (333GB each) > > > 2 processor Xeon 1.6GHZ > > > > > > dump file size: 2.5 GB > > > ibdata: 11GB > > > innodb tables > > > key_buffer=850M > > > innodb_buffer_pool_size=850M > > > table_cache=1500 > > > > > > _ > > > MSN. Más Útil Cada Día http://www.msn.es/intmap/ > > > > > > > > > -- > > > 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] > > > > _ > MSN. Más Útil Cada Día http://www.msn.es/intmap/ > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB key adding slowness
Keith, please see my reply to Mikel in thread 'Too slow recovering mysqldump files'. Since you are running a 64-bit version, you can set innodb_buffer_pool_size as high as 3 GB in your hardware. You can monitor the progress of the big ALTER TABLE with SHOW INNODB STATUS\G 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Keith Thompson"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 3:50 AM Subject: Re: InnoDB key adding slowness > I forgot to mention too that this is the 64bit MySQL 4.0.17 > running on Solaris9. > > >Hi all, > > >I decided I needed another index in an InnoDB table that has about > >25 million rows (each 80 bytes long). As I've done dozens of times > >with MyISAM tables, I did a simple "alter table": > > alter table WMH_CHK_a add key JoinTrans (TransID) > > >This has been running for 14 hours and I have no idea how much > >longer it's going to take, but it's getting darned frustrating > >as it's preventing lots of other work. > > >This is not a complicated table and it is running on a 4-CPU Sun > >server with a high-speed disk setup. The server has 4GB memory > >and I've got all of the InnoDB parameters set up at a moderate > >level (such as a 1GB buffer_pool_size), etc. It is otherwise not > >very busy on other activity, so it should have most of the system's > >resources available to it. > > >Any ideas what is taking this so long? This would not have > >taken more than a couple hours with a similar MyISAM table on > >the same server (based on prior experience). > > >Thanks -keith > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB locking 'non-existence' of a row
Alex, diagram: record1 'gap' record2 (User A holds a next-key lock on record2) InnoDB can lock the non-existence of a row in the 'gap'. But it cannot presently make another user B to wait before B acquires a lock on the gap. The reason is that B's cursor has already passed the gap when B ends up waiting for a next-key lock on record2. If we would allow user A to insert to the gap, then the cursor of B should be moved backwards, so that B's cursor would see the inserted record when A commits. Currently, InnoDB does not move a cursor backwards when a lock wait ends. Locks on gaps are purely inhibitive. That is, you can prevent other users from inserting to the gap, but you cannot guarantee that you yourself will be able to insert. In the general case, we cannot prevent 2 users acquiring conflicting locks on the same gap: gap1 delete_marked_record gap2 If A holds an X-lock on gap1 and B holds an X-lock on gap2, and purge removes the delete_marked_record, then the gaps merge, and both A and B hold an X-lock on the gap. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Zeltser, Alex"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, January 20, 2004 9:20 PM Subject: RE: InnoDB locking 'non-existence' of a row > Hi Joe, > > Thanks for your reply. Actually, in my experience (and according to the = > docs), if you select 'for > update' or 'lock in share mode', you _can_ lock non-existence of a row = > for inserts. In that case I > think the 'gap' where the row would be is locked, and attempts to insert = > the row from another > transaction will block or fail (until the first one does a commit or a = > rollback). Perhaps I'm > misunderstanding what's happening? > > Unfortunately, what I'm trying to do is try to have one transaction = > 'lock' the non-existence of a > row with a select, and another wait until the lock is released--also = > with a select. I've considered > doing what you propose with a separate lock table, and may still do just = > that, but first wanted to > see if I can accomplish the same thing with some clever DB manipulation. > > Thanks! > > Alex > > -Original Message- > From: Joe Shear [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 20, 2004 11:00 AM > To: Zeltser, Alex > Cc: [EMAIL PROTECTED] > Subject: RE: InnoDB locking 'non-existence' of a row > > > hi,=20 > Selecting a non-existent row won't acquire any locks that prevents = > inserts from happening. One way > to accomplish what you want is to create a separate insert lock table = > consisting of a table name and > a lock counter. Add a row for each table that you want to have these = > insert locks on, and before > performing any inserts, either update the corresponding row in the = > insert lock table or select it > for an update.=20 > > joe > > On Tue, 2004-01-20 at 10:01, Zeltser, Alex wrote: > > Hi Chris, > >=20 > > Thanks for the response and the suggestions. Doesn't SERIALIZABLE=20 > > level just add 'LOCK IN SHARE MODE' to your SELECTs, but other than=20 > > that works just like the default REPEATABLE READ level? I've tried by = > > > example below using 'LOCK IN SHARE MODE' instead of 'FOR UPDATE', but=20 > > the results were the same. > >=20 > > Is there any way to make the second session block when both it and the = > > > first one are 'locking' non-existence of a row? > >=20 > > Thanks in advance, > >=20 > > Alex > >=20 > > -Original Message- > > From: Chris Nolan [mailto:[EMAIL PROTECTED] > > Sent: Friday, January 16, 2004 4:55 PM > > To: Zeltser, Alex > > Cc: [EMAIL PROTECTED] > > Subject: Re: InnoDB locking 'non-existence' of a row > >=20 > >=20 > > Hi Alex! > >=20 > > On Sat, 2004-01-17 at 05:50, Zeltser, Alex wrote: > > > Hi, > > >=20 > > > I wanted to take advantage of the InnoDB 'gap' locking to lock > > > 'non-existence' of a row, the way the manual recommends. I tried to = > > > > do this by using 'select ... for update', using the 'mysql' client=20 > > > from two separate sessions as shown below: > > >=20 > > > Session 1: > > > > set AUTOCOMMIT=3D0; > > > > begin; > &
Re: how to access iblog file?
Hi! - Original Message - From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 2:35 AM Subject: how to access iblog file? > Hi all, > > i have run mysql ver 8.21 distrib 3.23.43 for win. i use innodb > architecture in my database. i have trouble access iblog file. Anyone know > how to access iblog file ? What error message does mysqld print? > Thanks in advance. > > -M. Amrin- 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too slow recovering mysqldump files
Mikel, - Original Message - From: "Mikel -" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 21, 2004 7:19 PM Subject: Re: Too slow recovering mysqldump files > I'll set up those parameters in my.cnf and try again the recovery from the > dump file. I'll hope these changes make faster the recovery. Dr. Ullrich suggested that you should set innodb_flush_log_at_trx_commit=0 during the big import, or wrap the big import inside a transaction. Otherwise, the speed may be restricted to 1 row / disk rotation = 100 rows/second. > Thanks again Heikki, > > Mikel > > P.S. Do you recommend the innodb hot backup tool, does it do faster than the > other options, or is a combination of both? InnoDB Hot Backup takes a BINARY backups of your database. If you need to restore from a backup taken with InnoDB Hot Backup, you do not need to import table dumps. Thus restoration is much faster than if you use table dumps as a backup (table dump = LOGICAL backup). 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 Order MySQL technical support from https://order.mysql.com/ > >From: "Heikki Tuuri" <[EMAIL PROTECTED]> > >To: "Mikel -" <[EMAIL PROTECTED]> > >CC: <[EMAIL PROTECTED]> > >Subject: Re: Too slow recovering mysqldump files > >Date: Wed, 21 Jan 2004 05:10:27 +0200 > > > >Mikel, > > > >it is apparently disk-bound. > > > >I recommend setting innodb_buffer_pool_size as high as 1.4 GB during such > >big import. Adjust innodb_log_file_size accordingly, and make key_buffer > >smaller during the big import. > > > >Help is coming: Marko Mäkelä is writing a compressed InnoDB table format, > >which can squeeze a typical table to 1 / 4 the size of a normal InnoDB > >table. I believe the compressed format will be available in October 2004. > > > >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 > > > >Order MySQL technical support from https://order.mysql.com/ > > > > > >- Original Message - > >From: "Mikel -" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Cc: <[EMAIL PROTECTED]> > >Sent: Wednesday, January 21, 2004 12:32 AM > >Subject: Re: Too slow recovering mysqldump files > > > > > > > Here we are my options: > > > > > > set-variable = innodb_log_file_size=150M > > > set-variable = innodb_log_buffer_size=8M > > > > > > I follow the example that came here: > > > "http://www.mysql.com/doc/en/InnoDB_start.html"; > > > > > > Greetings and best regards > > > > > > Mikel > > > > > > > > > >From: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > >To: <[EMAIL PROTECTED]> > > > >Subject: Re: Too slow recovering mysqldump files > > > >Date: Mon, 19 Jan 2004 22:44:50 +0200 > > > > > > > >Mikel, > > > > > > > >have you set the size of the InnoDB log files as recommended in the > >manual? > > > > > > > >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 > > > > > > > >Order MySQL technical support from https://order.mysql.com/ > > > > > > > >- Original Message - > > > >From: ""Mikel -"" <[EMAIL PROTECTED]> > > > >Newsgroups: mailing.database.myodbc > > > >Sent: Monday, January 19, 2004 7:25 PM > > > >Subject: Too slow recovering mysqldump files > > > > > > > > > > > > > Hi list, does anyone know a faster way to recover a mysqldump file > > > >cause > > > > > When I recovered one dump file it took 26 hours ! to finish, I think > > > >it's > > > > > too slow. > > > > > Thnx in advanced, greetings > > > > > > > > > > MySQL server 3.23.58 &g
Re: InnoDB locking 'non-existence' of a row
Alex, - Original Message - From: ""Zeltser, Alex"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, January 21, 2004 10:28 PM Subject: RE: InnoDB locking 'non-existence' of a row > Hello Heikki, > > Thank you for your reply and your explanation. It clarifies things and = > explains some of the > unexpected behavior I've observed (such as my own inability to insert to = > the gap after locking it). > > In general, is there a place where I can find a good discussion on the = > various locks used by InnoDB? > I've seen references to various locks in InnoDB status output, but = > wasn't sure what each kind was > (S-locks, X-locks, etc.). This would be a great aid for helping = > troubleshoot these kinds of > problems. /mysql/innobase/lock/lock0lock.c contains a lengthy comment at the start of the file. And the full source code of the algorithms, too :). Papers by IBM researcher C. Mohan may also be of interest. > Thank you for your time, > > Alex Zeltser 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB loading: add keys before or after
Hi! You should always create the indexes BEFORE adding the data to an InnoDB table. In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will be much slower to add the indexes afterwards. Many databases have an optimized index build procedure where adding an index afterwards is faster, but that is not the case for InnoDB. DISABLE KEYS has no effect on InnoDB. It is in the TODO to speed up index creation. Maybe in 2005 it will be faster to add the indexes afterwards :). 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Mirza" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, January 22, 2004 11:04 AM Subject: Re: InnoDB loading: add keys before or after > In theory it is fastest to add indexes first, then disable it (ALTER > TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys > (ALTER TABLE x DISABLE KEYS). > mirza > > Keith Thompson wrote: > > Hello all, > > > > I need to load a new InnoDB table with about 80 million rows. > > With MyISAM I have often found that it is faster to create > > the table without indexes, add all the data, then add the > > indexes. Is this true with InnoDB as well, or should I put > > my indexes in before loading? > > > > Similarly, I have another large table that is currently MyISAM > > that I'd like to move to InnoDB. What would be the fastest > > steps towards accomplishing this (as far as indexes, etc.)? > > > > Thanks -keith > > > > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB loading: add keys before or after
Keith, I would divide the work: 1) CREATE TABLE innodbtypetable ... 2) INSERT INTO innodbtypetable SELECT * from myisamtypetable WHERE primarykey >= xyz AND primarykey < zyx; etc. If you run out of disk space or get some other problem, the rollback will not be as huge as for a single transaction. Regards, Heikki - Original Message - From: "Keith Thompson" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 22, 2004 4:46 PM Subject: Re: InnoDB loading: add keys before or after > Heikki, > > Thanks for your help. > > I have another very large table to convert to InnoDB from MyISAM. > I also have the same table saved in a file suitable for 'load data'. > Which would be faster: > > ALTER TABLE xxx TYPE=InnoDB; > > or > > CREATE TABLE newxxx ...; INSERT INTO newxxx select * from xxx; > > or > > CREATE TABLE newxxx ...; LOAD DATA INFILE ... > > Thanks -keith > > > >Hi! > > >You should always create the indexes BEFORE adding the data to an InnoDB > >table. > > >In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will > be > >much slower to add the indexes afterwards. > > >Many databases have an optimized index build procedure where adding an > index > >afterwards is faster, but that is not the case for InnoDB. > > >DISABLE KEYS has no effect on InnoDB. > > >It is in the TODO to speed up index creation. Maybe in 2005 it will be > >faster to add the indexes afterwards :). > > >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 > > >Order MySQL technical support from https://order.mysql.com/ > > > > >- Original Message - > >From: "Mirza" <[EMAIL PROTECTED]> > >Newsgroups: mailing.database.myodbc > >Sent: Thursday, January 22, 2004 11:04 AM > >Subject: Re: InnoDB loading: add keys before or after > > > >> In theory it is fastest to add indexes first, then disable it (ALTER > >> TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys > >> (ALTER TABLE x DISABLE KEYS). > >> mirza > >> > >> Keith Thompson wrote: > >> > Hello all, > >> > > >> > I need to load a new InnoDB table with about 80 million rows. > >> > With MyISAM I have often found that it is faster to create > >> > the table without indexes, add all the data, then add the > >> > indexes. Is this true with InnoDB as well, or should I put > >> > my indexes in before loading? > >> > > >> > Similarly, I have another large table that is currently MyISAM > >> > that I'd like to move to InnoDB. What would be the fastest > >> > steps towards accomplishing this (as far as indexes, etc.)? > >> > > >> > Thanks -keith > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postponing Integrity Checking...
Matthew, sorry, InnoDB does not support deferred constraint checking. It is in the long-term TODO. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Matthew Bogosian" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, January 24, 2004 4:11 AM Subject: Re: Postponing Integrity Checking... > On Fri, 23 Jan 2004, Paul DuBois wrote: > > >At 17:32 -0800 1/23/04, Matthew Bogosian wrote: > >>Although this does the trick (kind of), this just turns off integrity > >>checking for that session, right? When I turn it back on, any > >>statement that would have failed but didn't is still in a failed > >>state. In other > > > >Right. You're not supposed to use it in order to do something that can > >screw up your tables. > > But I *can*, and that's the point. I want to use the database to protect > me from myself (or my company's application from its DBAs, etc.). > > >I guess I don't see the point of what you're trying to do. If you > >think you may be doing something that violates a foreign key > >constraint, *and* you want to roll it back if so, then just let the > >error occur within the transaction and roll it back if it does. > > Yes, I want all the constraints to be maintained. But the change I want > to make violates one of them, but only temporarily. > > I'm sorry for not conveying what I mean more clearly. Here's a simpler > question. Given: > > CREATE TABLE test_parent > ( > id INTEGER NOT NULL PRIMARY KEY > ) TYPE = INNODB; > > CREATE TABLE test_child > ( > id INTEGER NOT NULL PRIMARY KEY, > > parent_id INTEGER NOT NULL, > INDEX (parent_id), > FOREIGN KEY (parent_id) REFERENCES test_parent (id) ON DELETE > CASCADE > ) TYPE = INNODB; > > INSERT INTO test_parent VALUES (1); > INSERT INTO test_child VALUES (50, 1); > > How do I execute the following UPDATE statements such that I can ensure > that all integrity constraints are maintained upon the completion of the > last one? > > UPDATE test_parent SET id = 6 WHERE id = 1; > UPDATE test_child SET parent_id = 6 WHERE parent_id = 1; > > >>words, I could screw up and so something like: > >> > >> SET FOREIGN_KEY_CHECKS = 0; > >> BEGIN; > >> UPDATE test_parent SET id = 6 WHERE id = 1; > >> UPDATE test_child SET parent_id = 782 WHERE parent_id = 1; > >> COMMIT; > >> SET FOREIGN_KEY_CHECKS = 1; > >> > >>MySQL wouldn't complain in this case, I'd just have a child row who's > >>parent ID pointed to a non-existent parent (ID 782). Ideally, upon > >>reaching the COMMIT there'd be some way for me to have an error (and > >>have the transaction be rolled back). Here's what happens now: > > > >That doesn't make sense. Once you commit, you've committed. You can't > >commit and then roll back. > > Sorry, I was using the word "commit" to mean transaction barrier. What I > meant was to indicate that I am done with the transaction with a desire > to commit the changes, but only if the integrity constraints were > maintained. > > -- > 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: CREATE TABLE
Jeremiah, if you are using a fairly recent version of MySQL, you can print with SHOW INNODB STATUS\G a detailed explanation of the latest foreign key error. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Aleksandar Bradaric" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, January 23, 2004 9:23 PM Subject: Re: CREATE TABLE > Hi, > > > Error: > > ERROR 1005: Can't create table > > './tamiyausa/user_shipping_info.frm' (errno: > > 150) > > C:\mysql\bin>perror 150 > Error code 150: Unknown error > 150 = Foreign key constraint is incorrectly formed > > Look like your foreign keys are not properly defined. Do > both tables exist? And the fields you are referencing? > > > Take care, > Aleksandar > > > -- > 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 3.23.58 seg faults occasionally
Michael,
I guess the table is corrupt and its row contains a wrong trx id. InnoDB
tries to retrieve an earlier version of the row, but the roll ptr in the row
is garbage too, and it contains a nonexistent rollback segment id.
"
InnoDB: Error: trying to return an SQL NULL field in a non-null innoDB:
column! Table name
secret/tablename
"
The above is also a clear indication of table corruption. The NULL bit in an
InnoDB column is set, though the field should never be NULL.
You should upgrade to MySQL-4.0.17, which has better data page checksum
checking. Do you get corruption with the same application on earlier Linux
kernels, or could there be something wrong with Linux-2.4.23?
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
/**
Copies an undo record to heap. This function can be called if we know that
the undo log record exists. */
trx_undo_rec_t*
trx_undo_get_undo_rec_low(
/*==*/
/* out, own: copy of the record */
dulint roll_ptr, /* in: roll pointer to record */
mem_heap_t* heap) /* in: memory heap where copied */
{
trx_undo_rec_t* undo_rec;
ulint rseg_id;
ulint page_no;
ulint offset;
page_t* undo_page;
trx_rseg_t* rseg;
ibool is_insert;
mtr_t mtr;
trx_undo_decode_roll_ptr(roll_ptr, &is_insert, &rseg_id, &page_no,
&offset);
rseg = trx_rseg_get_on_id(rseg_id);
...
/**
Looks for a rollback segment, based on the rollback segment id. */
trx_rseg_t*
trx_rseg_get_on_id(
/*===*/
/* out: rollback segment */
ulint id) /* in: rollback segment id */
{
trx_rseg_t* rseg;
rseg = UT_LIST_GET_FIRST(trx_sys->rseg_list);
ut_ad(rseg);
while (rseg->id != id) {
rseg = UT_LIST_GET_NEXT(rseg_list, rseg);
ut_ad(rseg);
}
return(rseg);
}
(gdb) disassemble trx_rseg_get_on_id
Dump of assembler code for function trx_rseg_get_on_id:
0x81fa930 : push %ebp
0x81fa931 : mov0x83f3190,%eax
0x81fa936 : mov0x24(%eax),%eax
0x81fa939 : mov%esp,%ebp
0x81fa93b : mov0x8(%ebp),%edx
0x81fa93e : cmp%edx,(%eax)
0x81fa940 :
je 0x81fa94c
0x81fa942 : mov0xa0(%eax),%eax
0x81fa948 : cmp%edx,(%eax)
0x81fa94a :
jne0x81fa942
0x81fa94c : mov%ebp,%esp
0x81fa94e : pop%ebp
0x81fa94f : ret
End of assembler dump.
(gdb)
List:MySQL General Discussion« Previous MessageNext Message »
From:Michael BacarellaDate:January 26 2004 6:21pm
Subject:Re: MySQL 3.23.58 seg faults occasionally
> > the crashes below happen in independent areas of code. The 2 first are
> > inside InnoDB, and the third inside MySQL. This looks like random thread
> > crashes, or random memory corruption.
>
> My colleague tells me that the third stack trace (the one inside MySQL)
> is from a RESET MASTER. This server used to be a slave that was promoted
> to master. My apologies for not making this distinction.
>
> The ones inside InnoDB have been triggered by querying disk-resident
> low use tables.
We can get SIGSEGV almost every hour (from an hourly maintenance script).
This is the stack trace:
0x80c23d5 handle_segfault__Fi + 425
0x40022f54 _end + 935506260
0x823d4a8 trx_rseg_get_on_id + 24
0x823952d trx_undo_get_undo_rec_low + 45
0x823977d trx_undo_get_undo_rec + 49
0x82399c0 trx_undo_prev_version_build + 548
0x81f3f35 row_vers_build_for_consistent_read + 641
0x81e9d5e row_sel_build_prev_vers_for_mysql + 226
0x81ecda4 row_search_for_mysql + 6580
0x852 general_fetch__11ha_innobasePcUiUi + 322
0x8111373 rnd_next__11ha_innobasePc + 83
0x8103da6 rr_sequential__FP14st_read_record + 150
0x80e74d9 sub_select__FP4JOINP13st_join_tableb + 341
0x80e7190 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 412
0x80df9d7
mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemP8st_orderT4T3T4UiP1
3select_result
+ 4167
0x80c8aba mysql_execute_command__Fv + 806
0x80cbd88 mysql_parse__FP3THDPcUi + 72
0x80c7c74 do_command__FP3THD + 1324
Attempting to convert the table that caused the crash to MyISAM
resulted in:
InnoDB: Error: trying to return an SQL NULL field in a non-null innoDB:
column! Table name
secret/tablename
Also, duplicate keys. We're deleting the table and recreating it.
Any thoughts?
> > You are running a relatively new Linux kernel, 2.4.23. Did the
InnoDB file format changes 2004 - 2005; Re: InnoDB development
Chris, InnoDB file format changes: 4.1.0 -> 4.1.1 introduced multiple tablespaces; 4.1.1 -> 4.1.2 or .3allow multiple charsets in the same database installation (currently only the default charset is used in InnoDB); 5.0.0 -> 5.0.x create all new InnoDB tables in a space-saving format; this will also slightly change the InnoDB log format, and users of InnoDB Hot Backup should upgrade ibbackup at the same time. I hope there will not be more InnoDB file format changes in 2004 - 2005. In the MySQL tradition, an upgrade is always painless with no data conversion. But a downgrade is tricky if the file format has changed. 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 . List:MySQL General Discussion« Previous MessageNext Message » From:Chris NolanDate:January 27 2004 1:29am Subject:InnoDB development Hi all, I've been away from the list for a while and am wondering if the following question has been answered: Given that the extremely funky InnoDB is going to gain a new file format in the future, would it be a fair guess to say that any additions to InnoDB requiring file format changes would be made after the release of the new compressed format? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shrinking innodb datafiles?
Lawrence, if you use MySQL-4.1.1, and specify innodb_file_per_table in my.cnf, then InnoDB places each table into its own .ibd file. That is a way to free the disk space to the OS if you drop a table. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Lawrence Smith" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, January 30, 2004 12:59 AM Subject: Re: Shrinking innodb datafiles? > --- Jeff Mathis <[EMAIL PROTECTED]> wrote: > > someone will no doubt echo what I'm about to say. > > InnoDB files are created at startup. the files use > > all the disk you > > allocate to them in your my.cnf startup file. > > > > If you want smaller InnoDB files, specify a smaller > > file size in your > > my.cnf file, but I have a feeling thats not what you > > want to do... > > I have the default my.cnf , which creates a 10MB file > and IIRC default extension of 8MB (this is on a > devel machine BTW). I have a couple of DBs with > parsely populated tables, and loaded a test database > with a lot of data which would account for the vast > size of the file. I thought dropping the DB would > free up space but this doesn't seem to be the case. > > I suppose the workaround would be to dump and recreate > all databases. > > Thx > > > = > http://www.explanation-guide.info > > > BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Backups
Mauro, - Original Message - From: "Mauro Marcellino" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, January 29, 2004 10:44 PM Subject: InnoDB Backups > --=_NextPart_000_00CE_01C3E67E.9D867B90 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > I have made a couple of other postings related to this but I guess my > question is: > > Can I backup InnoDB tables (binary files) using an open file agent? what do you mean by an open file agent? You cannot just copy the ibdata files and ib_logfiles as is, because they would be inconsistent. A commercial tool to make an online (= hot) binary backup is InnoDB Hot Backup. A free way to make an online backup is to use mysqldump. > If yes...and I am using Automatic COMMITs my backup will be current? > > If I am not using Automatic COMMITs then my backup will contain data = > up > to the last COMMIT (In other words, data since the last COMMIT will not > be included in the backup. Is this true? > > I appreciate any guidance...Thanks Much! > > Mauro 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Key locks on InnoDB
Hi! It depends on the isolation level whether locking reads set next-key locks (= lock the record AND the gap before it), or only lock the record itself. http://www.innodb.com/ibman.php#Set.transaction: " READ UNCOMMITTED This is also called dirty read: non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not consistent reads under this isolation level; otherwise this level works like READ COMMITTED. READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, not the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since phantom rows have to be blocked for MySQL replication and recovery to work. Consistent reads behave like in Oracle: each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE, which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users. In consistent reads there is an important difference from the previous isolation level: in this level all consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECTs within the same transaction, these SELECTs are consistent also with respect to each other. SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE. " http://www.innodb.com/ibman.php#Locks.set.by.statements: " SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. " 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 List:MySQL General Discussion« Previous MessageNext Message » From:Ingolf HenriciDate:January 30 2004 12:52pm Subject:Key locks on InnoDB Hi, got a problem with the key locks, using InnoDB: The mysql.com Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE: sets shared next-key locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE: sets exclusive next-key locks on all index records the read encounters. The Innodb-Manual reads: SELECT ... FROM ... LOCK IN SHARE MODE sets shared locks on all index records the read encounters. SELECT ... FROM ... FOR UPDATE sets exclusive locks on all index records the read encounters. What locks are used for these statements?? shared locks or shared next-key-locks thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shrinking innodb datafiles?
Lawrence, - Original Message - From: "Lawrence Smith" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, January 31, 2004 6:47 PM Subject: Re: Shrinking innodb datafiles? > Heikki, > > thanks for the reply, will note. > > Just to confirm if I understand the situation > correctly: innodb datafiles cannot be reduced in size > to reclaim spaced freed by deleted data? you are right. > (That'd > probably explain why I had a hard time finding ISPs > offering innodb-enabled MySQL). That might be a factor, though I guess the demand for transactions is rather small in ISP-hosted databases. > Lawrence Smith 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 Order MySQL technical support from https://order.mysql.com/ > --- Heikki Tuuri <[EMAIL PROTECTED]> wrote: > > Lawrence, > > > > if you use MySQL-4.1.1, and specify > > > > innodb_file_per_table > > > > in my.cnf, then InnoDB places each table into its > > own .ibd file. That is a > > way to free the disk space to the OS if you drop a > > table. > > > > > BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Backups
Mauro, - Original Message - From: "Mauro Marcellino" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, January 30, 2004 6:51 PM Subject: Re: InnoDB Backups > By open file tool I mean software that works concurrently with a backup > suite such as veritas that would backup any open files (such as MySQL > binaries) that would normally be skipped. with Google I found something called an 'open file tool'. I do not know if it works properly with InnoDB or other relational databases. Best to test it very carefully. > What do you mean by inconsistent? What does InnoDB Hot backup do > differently than an open file agent? It checks the consistency of each InnoDB data page and archives the log as well. And it has been extensively tested. > So the only two ways to do an online backup of InnoDB tables is InnoDB Hot > backup or mysqldump? Also snapshot file systems, like the Veritas file system should work, though I have not tested them. > Thanks, > > Mauro 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 Order MySQL technical support from https://order.mysql.com/ > - Original Message - > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, January 30, 2004 4:22 AM > Subject: Re: InnoDB Backups > > > > Mauro, > > > > - Original Message - > > From: "Mauro Marcellino" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.myodbc > > Sent: Thursday, January 29, 2004 10:44 PM > > Subject: InnoDB Backups > > > > > > > --=_NextPart_000_00CE_01C3E67E.9D867B90 > > > Content-Type: text/plain; > > > charset="iso-8859-1" > > > Content-Transfer-Encoding: quoted-printable > > > > > > I have made a couple of other postings related to this but I guess my > > > question is: > > > > > > Can I backup InnoDB tables (binary files) using an open file agent? > > > > what do you mean by an open file agent? > > > > You cannot just copy the ibdata files and ib_logfiles as is, because they > > would be inconsistent. A commercial tool to make an online (= hot) binary > > backup is InnoDB Hot Backup. A free way to make an online backup is to use > > mysqldump. > > > > > If yes...and I am using Automatic COMMITs my backup will be current? > > > > > > If I am not using Automatic COMMITs then my backup will contain data = > > > up > > > to the last COMMIT (In other words, data since the last COMMIT will not > > > be included in the backup. Is this true? > > > > > > I appreciate any guidance...Thanks Much! > > > > > > Mauro > > > > 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 > > > > Order MySQL technical support from https://order.mysql.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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key constraint error on replace
Daniel, - Original Message - From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, January 26, 2004 5:53 AM Subject: key constraint error on replace > Hi there, i am trying to use a replace, when mysql throws an error > of "Cannot delete or update a parent row: a foreign key constraint fails" , > is there any way to get more information like which key is having the > issue, is there more detailed information i can extract ? this is a known problem, which should be documented: sometimes MySQL internally performs REPLACE as an INSERT, sometimes as a DELETE + INSERT, and sometimes as an UPDATE. The behavior with foreign key constraints is not very well predictable. You can get a detailed description of the latest FOREIGN KEY error with SHOW INNODB STATUS\G 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]
Re: Shrinking innodb datafiles?
Lawrence, - Alkuperäinen viesti - Lähettäjä: "Lawrence Smith" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Sunday, February 01, 2004 1:03 PM Aihe: Re: Shrinking innodb datafiles? > > --- Heikki Tuuri <[EMAIL PROTECTED]> wrote: > > > > > > > Just to confirm if I understand the situation > > > correctly: innodb datafiles cannot be reduced in > > size > > > to reclaim spaced freed by deleted data? > > > > you are right. > > Once again thanks. One absolutely last question (only > just occurred to me): will the space in the file used > by the deleted data be reused? yes, of course. Though, remember that if you randomly delete about 50 % of rows, it may happen that no 16 kB page or 1 MB extent becomes completely empty. Then the freed space can only be used by the same index tree from which the space was freed. The way to reorganize an InnoDB table is to run a 'null' ALTER TABLE: ALTER TABLE myinnodbtable TYPE = InnoDB; Be prepared for a very long run time, since the above command completely rebuilds the table. If you are using >= MySQL-4.1.1 and storing InnoDB tables to their own .ibd files, then the above command really frees the space to the OS. That is because the ALTER TABLE table is performed by creating a new .ibd file and deleting the old .ibd file. > Thx > > Lawrence Smith 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AUTO_INCREMENT in InnoDB
Hassan, - Original Message - From: "Hassan Shaikh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, February 01, 2004 5:01 PM Subject: AUTO_INCREMENT in InnoDB > Hi, > > How do I reset the AUTO_INCREMENT column to some arbitrary number? My > table type is InnoDB. drop the table, recreate it, insert a dummy row with the auto-inc column set to the desired value minus 1, and delete that dummy row. > Thanks. > > Hassan 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does MySQL generate internal foreign key id when constrain name
Philip, - Original Message - From: "Philip Walden" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, February 02, 2004 3:39 AM Subject: Why does MySQL generate internal foreign key id when constrain name > I am using MySQL 4.1.1-1. > > When I add a "named" foreign key constraint > > alter table sb_query_nm_sub_tp > add constraint f1sbquerynmsubtp foreign key (query_nm) references > sb_query_class (query_nm); > > And then do a show create table: > > | sb_query_nm_sub_tp | CREATE TABLE `sb_query_nm_sub_tp` ( > `query_nm` varchar(32) NOT NULL default '', > `sub_tp_tree_nm` varchar(32) NOT NULL default '', > `sub_tp_nm` varchar(32) NOT NULL default '', > `updt_user_id` smallint(6) NOT NULL default '0', > `updt_dt` date NOT NULL default '-00-00', > `create_dt` date NOT NULL default '-00-00', > PRIMARY KEY (`query_nm`), > CONSTRAINT `0_1584` FOREIGN KEY (`query_nm`) REFERENCES > `sb_query_class` (`query_nm`) > ) TYPE=InnoDB DEFAULT CHARSET=latin1 | > > > I get an internally generated name where f1sbquerynmsubtp <> 0_1584. > > To drop the foreign key, I have to use the internal id which is only > retrievable from the "show create table". I have some legacy code I am > trying to port from another RDBMS. This code adds/drops constraints and > expects the constraint to be named as specified in the "add". > > How would I code to find the internal constraint id right after the > "add"? Or is there a way to get MySQL to use the constraint name that is > given. This seems like a significant deficiency in MySQL. I am working on fixing this problem, along with another one in the replication of ALTER TABLE ... DROP FOREIGN KEY ...; The solution would be to remember the foreign key constraint name, if given by the user. If the user does not supply a name, then we would generate an id of the form: `databasename.tablename_fk_` Replication does not like globally generated id's like `0_1584`, because the id's will differ if the slave is not set up as an exact binary replica of the master. I hope these bug fixes make it to 4.0.18 and 4.1.2. > Thanks > > Phil 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: starting a crashed mysqld
Rusma, below it crashes because several data pages are corrupt. You can try starting it on level 6 and dump your tables. Then recreate your InnoDB data files and ib_logfiles and import the table dumps. Since the corruption is extensive, you are probably not able to dump all your data, though. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Rusma Mulyadi"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, February 03, 2004 1:09 AM Subject: FW: starting a crashed mysqld > --=_NextPart_000_0006_01C3E9A6.E7140E00 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: quoted-printable > > The RAID that we used to hold our mysql data was crashed a few days ago. > > Although it is fine now (after switching to the secondary host = > connector), > we can't seem to start the mysqld. > > We tried to start the server using the various innodb_force_recovery = > options > (1-6) with no luck. > > Below is the error log with the innodb_force_recovery =3D 1. =20 > > =20 > > Any inputs are much appreciated. > > =20 > > Thanks! > > =20 > > 040202 13:27:32 mysqld started > > 040202 13:27:32 InnoDB: Database was not shut down normally. > > InnoDB: Starting recovery from log files... > > InnoDB: Starting log scan based on checkpoint at > > InnoDB: log sequence number 28 1814914366 > > 040202 13:27:33 InnoDB: Starting an apply batch of log records to the > database... > > InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 InnoDB: Database page > corruption on disk or=20 > > a failed > > InnoDB: file read of page 393110. > > InnoDB: You may have to recover from a backup. > > 040202 13:27:33 InnoDB: Page dump in ascii and hex (16384 bytes): > > len 16384; hex ... > > =20 > > =20 > > 8 040202 13:27:33 InnoDB: Page checksum 394994200 stored checksum > 1142435917 > > InnoDB: Page lsn 28 1820582657, low 4 bytes of lsn at page end = > 1820222636 > > InnoDB: Page may be an index page where index id is 0 1660 > > InnoDB: Database page corruption on disk or a failed > > InnoDB: file read of page 393110. > > InnoDB: You may have to recover from a backup. > > InnoDB: It is also possible that your operating > > InnoDB: system has corrupted its own file cache > > InnoDB: and rebooting your computer removes the > > InnoDB: error. > > InnoDB: If the corrupt page is an index page > > InnoDB: you can also try to fix the corruption > > InnoDB: by dumping, dropping, and reimporting > > InnoDB: the corrupt table. You can use CHECK > > InnoDB: TABLE to scan your table for corruption. > > InnoDB: Look also at section 6.1 of > > InnoDB: http://www.innodb.com/ibman.html about > > InnoDB: forcing recovery. > > 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 InnoDB: = > Database > page corruptio > > n on disk or a failed > > InnoDB: file read of page 698866. > > InnoDB: You may have to recover from a backup. > > 040202 13:27:36 InnoDB: Page dump in ascii and hex (16384 bytes): > > len 16384; hex 00..=20 > > =20 > > 040202 13:28:18 InnoDB: Page checksum 1308210445 stored checksum = > 3312266381 > > InnoDB: Page lsn 28 1674093630, low 4 bytes of lsn at page end = > 1820530832 > > InnoDB: Page may be an index page where index id is 0 1657 > > InnoDB: Database page corruption on disk or a failed > > InnoDB: file read of page 497825. > > InnoDB: You may have to recover from a backup. > > InnoDB: It is also possible that your operating > > InnoDB: system has corrupted its own file cache > > InnoDB: and rebooting your computer removes the > > InnoDB: error. > > InnoDB: If the corrupt page is an index page > > InnoDB: you can also try to fix the corruption > > InnoDB: by dumping, dropping, and reimporting > > InnoDB: the corrupt table. You can use CHECK > > InnoDB: TABLE to scan your table for corruption. > > InnoDB: Look also at section 6.1 of > > InnoDB: http://www.innodb.com/ibman.html about > > InnoDB: forcing recovery. > > InnoDB: Probable data corruption on page 497825 > > InnoDB: Original record RECORD: info bits 32 0: len 4; hex 42a68d35; asc > B..5;; 1: len 8;=20 > > hex 8094ca94; asc ;; > > InnoDB: on that page. Steps 1. > > InnoDB: Cannot find the dir slot for record RECORD: info bits 32 0: l
Re: Memory Leak using InnoDB ?
Geoffrey, Dan, - Original Message - From: "Dan Nelson" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 07, 2004 5:27 AM Subject: Re: Memory Leak using InnoDB ? > In the last episode (Feb 07), Geoffrey said: > > I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM. > > > > One application has to access the database (1 connection to the DB is > > open on startup and left open). However this application performs a > > lot of queries on the DB. > > > > Thanks to "top", I can see that the "used memory" is constantly > > increasing and never freed (up to 1 GB) when performing a lot of > > queries. In fact, I can see the total memory usage increasing but the > > mysqld process memory usage remains the same. > > > > When the DB is not accessed, the memory usage is stable. Stopping MySQL > > server doesn't free the abnormaly allocated memory. > > Ideally, you should have very little "free" memory according to top > (most systems will see under 20MB free). Free memory is wasted memory. > Unix uses memory not allocated to processes for a disk cache. To > determine whether you are truly low on memory, run iostat and watch the > swap columns. Constant swap activity means you're low on memory. Dan is right. A memory leak in the mysqld process should show up in the 'top' line for that process. By the way, monitoring if InnoDB has memory leaks is very easy. Just look with SHOW INNODB STATUS at the 'total allocated memory'. If that keeps climbing without bounds, that is an indication of a memory leak. > -- > Dan Nelson > [EMAIL PROTECTED] 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB - Reclaiming ibdata space and various ibdata questions
Michael, - Original Message - From: "MIchael Johnson" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 07, 2004 1:03 AM Subject: INNODB - Reclaiming ibdata space and various ibdata questions > --0-1416817391-1076108604=:94234 > Content-Type: text/plain; charset=us-ascii > > Good Afternoon and thank you in advance for any help that you may be able to provide. > > QUESTION 1 : How do you reclaiming ibdata space? > > We have 3 databases that together use several ibdata files (ie each database does not have its own ibdata file) . We would like to drop 2 databases and reclaim the ibdata space that the 2 databases took up. What is the best way to do this? > > QUESTION 2 : Can you have multiple MySQL Instances that contain one database and a seperate ibdata spaces? Is this the best way to create seperate ibdata files for each database? What would you recommend? > please see http://www.innodb.com/ibman.php#Multiple.tablespaces That solves the disk allocation problems. > QUESTION 3: How can you defrag a database instead of a table? There is no such command. You have to write a script for that. > We have found that you can defrag a table: "From MySQL Manual: The way to do the defragmenting is to perform a 'null' alter table operation ALTER TABLE tablename TYPE=InnoDB." We would like to do this for the complete database. > > Thanks again for any help. > > Respectfully, > > Mike 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Leak using InnoDB ?
Geoffrey, - Original Message - From: "Geoffrey" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, February 07, 2004 10:11 PM Subject: Re: Memory Leak using InnoDB ? > Dan, Heikki, > > - Original Message - > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, February 07, 2004 11:39 AM > Subject: Re: Memory Leak using InnoDB ? > > > > Geoffrey, Dan, > > > > - Original Message - > > From: "Dan Nelson" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.myodbc > > Sent: Saturday, February 07, 2004 5:27 AM > > Subject: Re: Memory Leak using InnoDB ? > > > > > > > In the last episode (Feb 07), Geoffrey said: > > > > I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM. > > > > > > > > One application has to access the database (1 connection to the DB is > > > > open on startup and left open). However this application performs a > > > > lot of queries on the DB. > > > > > > > > Thanks to "top", I can see that the "used memory" is constantly > > > > increasing and never freed (up to 1 GB) when performing a lot of > > > > queries. In fact, I can see the total memory usage increasing but the > > > > mysqld process memory usage remains the same. > > > > > > > > When the DB is not accessed, the memory usage is stable. Stopping > MySQL > > > > server doesn't free the abnormaly allocated memory. > > > > > > Ideally, you should have very little "free" memory according to top > > > (most systems will see under 20MB free). Free memory is wasted memory. > > > Unix uses memory not allocated to processes for a disk cache. To > > > determine whether you are truly low on memory, run iostat and watch the > > > swap columns. Constant swap activity means you're low on memory. > > > > Dan is right. A memory leak in the mysqld process should show up in the > > 'top' line for that process. > > > > By the way, monitoring if InnoDB has memory leaks is very easy. Just look > > with SHOW INNODB STATUS at the 'total allocated memory'. If that keeps > > climbing without bounds, that is an indication of a memory leak. > > > > Thanks for your help. > > For testing, I tried the following my.cnf : > > server-id=1 > socket=/tmp/mysql.sock > innodb_data_file_path = ibdata1:750M:autoextend > set-variable = innodb_buffer_pool_size=50M > set-variable = innodb_additional_mem_pool_size=10M > set-variable = innodb_log_file_size=300M > set-variable = innodb_log_buffer_size=2M > innodb_flush_log_at_trx_commit=0 > skip-locking > set-variable = max_connections=5 > set-variable = read_buffer_size=1M > set-variable = sort_buffer=1M > set-variable = key_buffer=10M > > Here is the result of "top" after 20 hours : > > 8:26pm up 18:35, 1 user, load average: 0.42, 0.26, 0.20 > 46 processes: 44 sleeping, 2 running, 0 zombie, 0 stopped > CPU states: 3.7% user, 0.5% system, 0.0% nice, 95.6% idle > Mem: 1031048K av, 1021460K used,9588K free, 0K shrd, 55288K > buff > Swap: 2044072K av, 0K used, 2044072K free 845544K > cached > PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND > 960 mysql 15 0 67872 66M 2656 S 0.1 6.5 6:29 mysqld > > Here is the result of "top" after a reboot : > > 8:54pm up 9 min, 1 user, load average: 0.16, 0.17, 0.10 > 46 processes: 44 sleeping, 2 running, 0 zombie, 0 stopped > CPU states: 4.5% user, 0.3% system, 0.0% nice, 95.0% idle > Mem: 1031048K av, 136512K used, 894536K free, 0K shrd, 13276K > buff > Swap: 2044072K av, 0K used, 2044072K free 61620K > cached > PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND > 774 mysql 15 0 36276 35M 2496 S 0.7 3.5 0:03 mysqld > > Here is the SHOW INNODB STATUS after 20 hours : > > -- > BUFFER POOL AND MEMORY > -- > Total memory allocated 75115912; in additional pool allocated 1611008 > Buffer pool size 3200 > Free buffers 1 > Database pages 3091 > Modified db pages 160 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 7212, created 2274, written 801221 > 0.00 reads/s, 0.02 creates/s, 12.45 writes/s > Buffer pool hit rate 1000 / 1000 > > As far as I understand, the "Total Mem" increasing constantly when MySQL is > heavily accessed is not really memory allocated by MySQL but allocated by > Linux to take advantage of the unused memory ? > > So I should not pay attention to it (as long as used swap is 0). > > Correct ? correct! > Regards. 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup & new tablespace format
Chris, - Original Message - From: "Chris Elsworth" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 07, 2004 7:25 PM Subject: InnoDB Hot Backup & new tablespace format > Hello, > > Does anyone know if InnoDB Hot Backup supports the new tablespace > format being introduced in the latest versions of InnoDB? ibbackup-2.0 is ready to be rolled out. It supports backing up also the .ibd files. You can request an eval copy for Linux. > I'm quite tempted to switch from MyISAM to InnoDB using the new > tablespace format, but I'm put off by how inflexible InnoDB files seem > to be. I like being able to move .MYD and .MYI files around and have > any mysqld use them; InnoDB seems a bit picky about that. > Does the new one-file-per-tablespace format change any of that? Is > there any actual advantage to using it? Currently, .ibd files CANNOT be moved between different installations. Please read carefully http://www.innodb.com/ibman.php#Multiple.tablespaces But it is rather easy to add later a feature which would allow moving 'clean' .ibd files. That only requires resetting all lsn's and trx id's in the .ibd file. > Thanks for any replies, > -- > Chris 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Version 4.1.2. when?
Mirza, - Original Message - From: "Mirza" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, February 09, 2004 1:54 PM Subject: Version 4.1.2. when? > Is there any info on when 4.1.2. is scheduled for relase? I am stucked > in 4.1.1. with fulltext problem (bug #2490). > > Thanks for info, > mirza 2 months ago I predicted 4.1.2-beta would be released Jan 25th, 2004. But now I would guess March 5, 2004. 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Reference to a VARCHAR
Sven, are you using the UTF8 charset? Then a single character may use up to 3 bytes. Since MySQL cannot work with index columns longer than 255 bytes, for columns CHAR(86) or longer, MySQL must define a 'column prefix' index, if you define an index on the column. That is, MySQL internally creates an index of type INDEX (colname(85)) instead of an index on the full column. And FOREIGN KEYs do not work on 'column prefix' indexes :(. A workaround: use latin1 or latin1_german1_ci. Then 1 character only takes 1 byte. In the future, MySQL will get longer CHAR and VARCHAR columns. That will alleviate this problem. The current limit 255 bytes is rather short. I may also improve the foreign key check algorithm so that it can work also on column prefix indexes. http://www.mysql.com/doc/en/Charset-Unicode.html : " The UTF8 character set (transform Unicode representation) is an alternative way to store Unicode data. It is implemented according to RFC2279. The idea of the UTF8 character set is that various Unicode characters fit into byte sequences of different lengths. Basic Latin letters, digits, and punctuation signs use one byte. Most European and Middle East script letters fit into a two-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others. Korean, Chinese and Japanese ideographs use three-byte sequences. Currently, MySQL UTF8 support does not include four-byte sequences. " 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 List:MySQL General Discussion« Previous MessageNext Message » From:Sven WoltmannDate:February 10 2004 1:12am Subject:Foreign Key Reference to a VARCHAR Hi, I hope this is not a well known problem since I just signed up to this list. But I checked the February archive and couldn't find anything on this. I was trying for a couple of hours now to create a foreign key reference on a varchar field: CREATE TABLE users ( loginVARCHAR(20) NOT NULL, password VARCHAR(20) NOT NULL, email_addressVARCHAR(100) NOT NULL, -- PRIMARY KEY(login), INDEX(email_address) ) TYPE = InnoDB; create table email_alias ( aliasVARCHAR(100) NOT NULL, email_addressVARCHAR(100) NOT NULL, -- PRIMARY KEY(alias), INDEX(email_address), FOREIGN KEY (email_address) references users(email_address) ) TYPE = InnoDB; Actually my tables were a lot bigger, but I stripped them down to these short tables to resolve my problem, which is: When creating the second table, I get the error message: ERROR 1005 (HY000): Can't create table './test/email_alias.frm' (errno: 150) I admit, the first time I didn't put an INDEX on email_address. I figured that out quite fast. Then I searched again in the Newsgroups and did a SHOW INNODB STATUS. I got the following message: "Cannot find an index in the referenced table where the referenced columns appear as the first columns [...]" Well - this didn't help at all :( So I changed my table definitions a hundred times to find out what exactly the problem was. And after hours, I found out: The VARCHAR must not be longer than 85 characters. If you replace the "100" in the example above by a "85", IT WORKS!!! So... have I missed the fine print in the documentation or did I actually find a bug? Here's my configuration: - 4.1.1-alpha-standard-log - Official MySQL-standard binary - i686 - pc-linux (debian 3.0 woody) Sven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Reference to a VARCHAR
Sven, yes, the error message is misleading. And we must get longer CHARs and VARCHARs to MySQL. It is not good to silently change internal index definitions! I guess that 4-byte UTF8 characters are not needed. You can code 16 million characters with 3 bytes. That should be enough for all languages on Earth. Thank you, Heikki - Alkuperäinen viesti - Lähettäjä: "Sven Woltmann" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Lähetetty: Tuesday, February 10, 2004 3:53 PM Aihe: RE: Foreign Key Reference to a VARCHAR Heikki, yes, actually I use the UTF8 character set. Thanks for your clarification and the workaround! A few comments: - For e-mail-addresses it's ok to set the column's character set to latin1. And in case I need UTF8 support in a referenced VARCHAR, I'll limit it to 85 characters. But about those four-byte sequences: Will that be implemented _after_ MySQL gets longer VARCHAR columns? If not, a new version with four-byte sequences would not be able to handle my tables anymore, right? - Maybe you should change the error message in SHOW INNODB STATUS ;) Thanks again, Sven > -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Dienstag, 10. Februar 2004 14:19 > To: [EMAIL PROTECTED] > Subject: Re: Foreign Key Reference to a VARCHAR > > Sven, > > are you using the UTF8 charset? Then a single character may > use up to 3 bytes. Since MySQL cannot work with index columns > longer than 255 bytes, for columns CHAR(86) or longer, MySQL > must define a 'column prefix' index, if you define an index > on the column. That is, MySQL internally creates an index of > type INDEX (colname(85)) instead of an index on the full column. > And FOREIGN KEYs do not work on 'column prefix' indexes :(. > > A workaround: use latin1 or latin1_german1_ci. Then 1 > character only takes 1 byte. > > In the future, MySQL will get longer CHAR and VARCHAR > columns. That will alleviate this problem. The current limit > 255 bytes is rather short. > > I may also improve the foreign key check algorithm so that it > can work also on column prefix indexes. > > http://www.mysql.com/doc/en/Charset-Unicode.html : > > " > The UTF8 character set (transform Unicode representation) is > an alternative way to store Unicode data. It is implemented > according to RFC2279. The idea of the UTF8 character set is > that various Unicode characters fit into byte sequences of > different lengths. > > Basic Latin letters, digits, and punctuation signs use one byte. > > Most European and Middle East script letters fit into a > two-byte sequence: > extended Latin letters (with tilde, macron, acute, grave and > other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, > Syriac, and others. > > Korean, Chinese and Japanese ideographs use three-byte sequences. > > Currently, MySQL UTF8 support does not include four-byte sequences. > " > > 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 > > > > List:MySQL General Discussion< Previous MessageNext Message > > From:Sven WoltmannDate:February 10 2004 1:12am > Subject:Foreign Key Reference to a VARCHAR > > Hi, > > I hope this is not a well known problem since I just signed > up to this list. > But I checked the February archive and couldn't find anything on this. > > I was trying for a couple of hours now to create a foreign > key reference on a varchar field: > > CREATE TABLE users > ( > loginVARCHAR(20) NOT NULL, > password VARCHAR(20) NOT NULL, > email_addressVARCHAR(100) NOT NULL, > -- > PRIMARY KEY(login), > INDEX(email_address) > ) > TYPE = InnoDB; > > create table email_alias > ( > aliasVARCHAR(100) NOT NULL, > email_addressVARCHAR(100) NOT NULL, > -- > PRIMARY KEY(alias), > INDEX(email_address), > FOREIGN KEY (email_address) references users(email_address) > ) > TYPE = InnoDB; > > Actually my tables were a lot bigger, but I stripped them > down to these short tables to resolve my problem, which is: > > When creating the second table, I get the error message: > ERROR 1005 (HY000): Can't create table > './test/email_alias.frm' (errno: 150) > > I admit, the first time I didn't put an INDEX on > email_address. I figured that out quite fast. Th
Re: InnoDB Hot Backup + MySQL embedded?
Chris, - Original Message - From: "Chris Nolan" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, February 12, 2004 2:45 AM Subject: InnoDB Hot Backup + MySQL embedded? > Hi all, > > I'm looking at developing an (open source) server-style application with > the embedded MySQL library to be employed as the primary data store. > > Has anyone attempted to use InnoDB Hot Backup for such a beast? I ask as > I do not know whether communication between ibbackup and the database > engine is required (thus requiring a few changes to the embedded lib > before compilation). A quick scan through the docs involved doesn't seem > to point me in the right direction for a definitive answer on this. ibbackup can be used with the MySQL Embedded Server Library, no problem in that. ibbackup does not communicate with the mysqld server at all. It just reads the InnoDB data and log files. On the other hand, the innobackup Perl script does start a mysql connection to the server. > Regards, > > Chris 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 Order MySQL technical support from https://order.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.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 databasename.tablename. But
when parsing them we must also accept
databasename.tablename, 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: InnoDB Hot Backup + MySQL embedded?
Chris, - Original Message - From: "Chris Nolan" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, February 12, 2004 2:26 PM Subject: Re: InnoDB Hot Backup + MySQL embedded? > Dear Heikki, > > Thanks for the quick response! It never ceases to amaze me that such > compartively small teams at Innobase Oy and MySQL AB produce such > incredibly high-quality software. thank you for the praise :). > Being a final-year Software Engineering student, I'm curious as to what > you consider the most difficult problem to solve in building InnoDB and > ibbackup has been. Of technical matters, multiversioning (consistent read) has been the most difficult to get working reliably. I wrote it probably around 1996, and still last year a bug was found in it. Adaptive hash indexes and the insert buffer have also been difficult to debug. The reason is obvious: in these 3 things parallel execution of threads as well as background cleanup operations complicate things. One-threaded, deterministic processing is much easier to debug than multithreaded nondeterministic execution. > Given the fact that you set your goals extremely high > for InnoDB (and have acheived them quite handily), I want to ensure > that if anything remotely similar comes up in my final-year project that > I either put my hand up for it and take on the challenge or run > screaming in the other direction and have a somewhat easier year. In academic circles it is best not to put too much emphasis on programming work :). Referees tend to favor papers written in plain English, and want algorithms in pseudocode. They are not willing to dive into the full complexity of a real-world implementation. There has been quite a lot of debate and criticism around this gap between typical university research and practical applications, but things will probably not change in the next 2500 years. > Regards, > > Chris Best regards, Heikki > Heikki Tuuri wrote: > > >Chris, > > > >- Original Message - > >From: "Chris Nolan" <[EMAIL PROTECTED]> > >Newsgroups: mailing.database.myodbc > >Sent: Thursday, February 12, 2004 2:45 AM > >Subject: InnoDB Hot Backup + MySQL embedded? > > > > > > > > > >>Hi all, > >> > >>I'm looking at developing an (open source) server-style application with > >>the embedded MySQL library to be employed as the primary data store. > >> > >>Has anyone attempted to use InnoDB Hot Backup for such a beast? I ask as > >>I do not know whether communication between ibbackup and the database > >>engine is required (thus requiring a few changes to the embedded lib > >>before compilation). A quick scan through the docs involved doesn't seem > >>to point me in the right direction for a definitive answer on this. > >> > >> > > > >ibbackup can be used with the MySQL Embedded Server Library, no problem in > >that. ibbackup does not communicate with the mysqld server at all. It just > >reads the InnoDB data and log files. > > > >On the other hand, the innobackup Perl script does start a mysql connection > >to the server. > > > > > > > >>Regards, > >> > >>Chris > >> > >> > > > >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 > > > >Order MySQL technical support from https://order.mysql.com/ > > > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HOTBACKUP
Arnoldus, you should add log-bin to the [mysqld] section of my.cnf, so that mysqld writes the binlog. Please see the MySQL online manual about this option. 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 Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Arnoldus Th.J. Koeleman"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, February 13, 2004 8:34 PM Subject: HOTBACKUP > --=_NextPart_000_0041_01C3F235.BEE2A9E0 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > I was reading the manual for Hot Backup for InnoDB and i was missing one > fundamental thing in the doc and that is setting the log-bin > > The document only speaks about > > The my.cnf files must contain the following parameter values: > > innodb_data_home_dir=... > > innodb_data_file_path=... > > innodb_log_group_home_dir=... > > innodb_log_files_in_group=... > > innodb_log_file_size=... > > > > And for recovering purpose log-bin in the [mysqld] section am I correct > for POINT IN TIME RECOVERING > > > --=_NextPart_000_0041_01C3F235.BEE2A9E0-- > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPLACE INTO and CASCADEs.
Hi! The REPLACE + FOREIGN KEY behavior indeed changed between 4.0.17 -> 4.0.18, because the old behavior was wrong, according to the MySQL manual: " 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 states 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) " I am sorry for the inconvenience. The old behavior was maybe more intuitive, if you think of REPLACE as an 'UPSERT' command. But in MySQL it is a 'DELSERT' command. Best regards, Heikki - Original Message - From: "Tan Shao Yi" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 14, 2004 10:29 PM Subject: REPLACE INTO and CASCADEs. > Hello, > > I just upgraded from 4.0.17 to 4.0.18 on a Linux box and realised the > behaviour of REPLACE INTO appears to have changed. > > I have two tables: table2 references table1 with ON DELETE CASCADE and ON > UPDATE CASCADE on a primary key. > > Previously in 4.0.17 when I issued a REPLACE INTO on table1, only the > row in table1 gets "replaced". No rows in table2 get deleted. > > Now, in 4.0.18, when I issue a REPLACE INTO on table1, the row in table1 > gets "replaced", but this replacement gets cascaded into table2 and all > rows in table2 with the primary key get deleted. > > Has anyone encountered something similar? I have only one box available so > I am not able to re-test my observations again (I have reverted to > 4.0.17). > > Thanking in advance. > > > Regards, > Tan Shao Yi > > -- > 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]
The ID... messages are the Bagle.B worm
Hi! http://www.f-secure.com/v-descs/bagle_b.shtml A new Bagle.B worm epidemy is apparently causing the messages ID sent to the MySQL mailing list by my name. The worm has probably infected the computer of someone following this mailing list, and the worm has scanned my email address from the hard disk of the computer. The worm then uses my email address as the return email address. If someone has plenty of spare time, he could try to decipher the return path of those messages, and inform the ISP(s) of the owner(s) of the infected computer(s). Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL version 4.1.x
Hi! - Original Message - From: "Sasha Pachev" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, February 19, 2004 12:58 AM Subject: Re: MySQL version 4.1.x > lee wrote: > > Anyone know when 4.1.x is scheduled for going "Production?" The website says > > it's currently in Alpha. We'd like to upgrade past 4.0.18 if possible, to > > have the improved subqueries, etc. in 4.1.x. > > I would guess 4.1.1 is roughly as good on the old features as the latest 4.0. > However, I would stay away from subqueries for at least another six months > except for a few that you really benefit from and have tested to make sure they > work right and fast. A couple of months back my guess was that 4.1 would be labeled 'Production' in September 2004, or even a bit earlier. I would still give the same guess. > -- > Sasha Pachev > Create online surveys at http://www.surveyz.com/ 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 Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Hot Backup + MySQL embedded?
Chris, - Alkuperäinen viesti - Lähettäjä: "Chris Nolan" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Monday, February 16, 2004 1:56 PM Aihe: Re: InnoDB Hot Backup + MySQL embedded? > Heikki, > > Thank you greatly for answering my questions. Your obvious dedication to > the open source world and high-quality of software cannot be > understated. thank you :). > Upon reading some of the InnoDB source code, I've noticed that C seems > to be your language of choice. That said, it seems more and more of the > literature that is promoted around my university emphasises testing > methods related to OO languages like Java and C++ or looking at > different programming paradigms such as logical programming (my > university formally trains all students in Prolog and optionally > Mercury) and functional programming (Our very first CS subject involves > Haskell programming! It used to involve Miranda). Is there anything in > the way of strategies that you would recommend to developers (I'm > working on a MySQL/InnoDB based server app at the moment. Anyone else > who reads this message please hit me with your 2c) regarding C > programming and testing (they drill C into us with great rigor at the > university I attend, but after second year it boils down to assignment > submissions and that's about it)? C versus object-oriented lanuguages like C++/Java is a topic I have discussed a lot with programmers. I believe that traditional procedural approaches and languages, like C, are the best for 'systems programming', by which I mean implementing anything with complex data structures and lots of parallelism. A DBMS is a typical example of such a complex program. My opinion is based on: 1) The object-oriented paradigm recommends that algorithms are structured around 'objects', that is, around data structures. But I think it is easier for humans to understand complex algorithms, like the splitting of a node in a B-tree, if the algorithms are presented on their own terms, and not fragmented around the 'objects' involved. Donald Knuth of the Stanford University remarked that proving the correctness of an 'object-oriented' algorithm is hard, because keeping track of autonomous 'objects' is difficult. 2) C++ encourages programmers to use 'implicit' operations. In C++ functions can have the same name, even though they take a different number of parameters, and the data type of the parameters can differ. There can be implicit constructor and destructor operations for objects. I believe that the use implicit operations is prone to bugs, and makes a program harder to read. 3) A weakness of C compared to Java is memory management. In C you can easily write programs that leak memory or run over allocated buffers. In practice, it has turned out to be relatively easy to keep these memory management bugs at a tolerable level in our C programs, so that a move to a language with automatic memory management is not needed. In all programming languages I think the following are important programming conventions: 1) Remove redundancy from your code. Analogous to 'normalizing' your relational database. 2) Use a lot of comments. 3) Use a lot of assertions, which capture bugs early on. > The reason that I ask is that you have managed to create the world's > fastest transactional database handler that runs on such a large number > of different architectures and operating systems. Also, the fact that > you've taken everything that Oracle tout as making them brilliant and > doing it many times better than they have displays your commitment and > incredible set of skills. I must say that I have never before heard such praise in software world :). Often it is just the opposite. The Usenet is full of threads saturated with bashing and name-calling. > I hope that one day that the software I am currently building will also > contribute to both the commercial software world and the open source > community. The fact that you've achieved this with such success and that > you post to the MySQL mailing list so often is why I ask you these > questions. > > Regards, > > Chris Best regards Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C compared to C++/Java; Was: Re: InnoDB Hot Backup + MySQL embedded?
Jochem, - Original Message - From: "Jochem van Dieten" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 21, 2004 2:10 AM Subject: Re: InnoDB Hot Backup + MySQL embedded? > Sasha Pachev wrote: > > Heikki Tuuri wrote: > >> C versus object-oriented lanuguages like C++/Java is a topic I have > >> discussed a lot with programmers. I believe that traditional procedural > >> approaches and languages, like C, are the best for 'systems programming', by > >> which I mean implementing anything with complex data structures and lots of > >> parallelism. A DBMS is a typical example of such a complex program. > > >> 3) A weakness of C compared to Java is memory management. In C you can > >> easily write programs that leak memory or run over allocated buffers. In > >> practice, it has turned out to be relatively easy to keep these memory > >> management bugs at a tolerable level in our C programs, so that a move to a > >> language with automatic memory management is not needed. > > > > In Java is it easy to write a program that wastes large amounts of > > memory, which is worse than a leak. In C, you are full from the start, > > and then you leak a drop at a time until you are empty. In Java , you > > are empty from the start, and you have nothing to leak anyway even if > > you could :-) > > http://citeseer.nj.nec.com/shah01java.html here is a .pdf version of the paper: http://gist.cs.berkeley.edu/~mashah/java-paper/paper.pdf The authors used a 2 x Pentium III 667 MHz, Linux-2.2.16, Sun JDK 1.3, and Java HotSpot Server JVM 1.3.0. to implement a 'data-flow' query processor. Their conclusion is that the memory management and the garbage collection of Java is inefficient. The graph that they present shows an up to 2.5-fold performance degradation with the Java garbage collector, compared to their own tailored memory management system. I worked with Entity Systems Oy in the 1980s. We developed a Lisp interpreter and a compiler, and a Prolog interpreter. At that time, the inefficiency of the garbage collection in Lisp and Prolog was a serious problem. I am not familiar with more modern garbage collection algorithms, but the paper of Shah et al. suggests that there are still problems today. In the 1980s, the research group of Mike Stonebraker initially started implementing Postgres in a mixture of Lisp and C, but they later abandoned Lisp. > Jochem Regards, Heikki > -- > I don't get it > immigrants don't work > and steal our jobs > - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Name Case Sensitivity
Hi! Please observe the following about InnoDB table names on Windows: http://www.innodb.com/ibman.php#Moving " On Windows InnoDB stores the database names and table names internally always in lower case. To move databases in a binary format from Unix to Windows or from Windows to Unix you should have all table and database names in lower case. A convenient way to accomplish this is to add on Unix the line set-variable=lower_case_table_names=1 to the [mysqld] section of your my.cnf before you start creating your tables. On Windows set-variable=lower_case_table_names=1 is the default setting. " I think it is bad programming style to have tables whose name only differs in case. I recommend using in my.cnf lower_case_table_names=1 on all platforms. Regards, Heikki .. List: MySQL General Discussion From: Tim Hayes Date:February 23 2004 5:56pm Subject: Re: Table Name Case Sensitivity OK There is still the possibility of an in-compatability between the 2 platforms. However - in both Linux and Windows (MySQL 4.0.17) the variable is not recognized / updateable using the set command! I get - Unknown system variable 'lower_case_table_names' Tim Hayes - Original Message - From: "Peter Zaitsev" <[EMAIL PROTECTED]> To: "Tim Hayes" <[EMAIL PROTECTED]> Sent: Monday, February 23, 2004 4:19 PM Subject: Re: Table Name Case Sensitivity > On Mon, 2004-02-23 at 08:15, Tim Hayes wrote: > > Can anyone offer advice? > > Run with lower_case_table_names=1 > > > > > > > > I have come across a MySQL database on Linux with duplicate table names - "Accounts" and "accounts". This seems fine on Linux, but does not transfer to the Windows environment - it is rejected because of the duplicate name. However I do see that Column Names have to be unique in both environments. > > > > Q. Is there some sort of practical advantage for having case sensitive table names in Linux, or is this a design "shortcoming"? Postgress rejects duplicate table names. > > > > Thanks > > Tim Hayes > -- > Peter Zaitsev, Senior Support Engineer > MySQL AB, www.mysql.com > > Meet the MySQL Team at User Conference 2004! (April 14-16, Orlando,FL) > http://www.mysql.com/uc2004/ > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lower_case_table_names, Mac OSX, InnoDB and 4.0.18
Bruce, I have now filed bug report 2994 about this. Regards, Heikki List:MySQL General Discussion« Previous MessageNext Message » From:Bruce DembeckiDate:February 27 2004 2:01pm Subject:lower_case_table_names, Mac OSX, InnoDB and 4.0.18 There seems to be some issues we are experiencing with this new lower_case_table_names variable introduced in 4.0.17 and modified in 4.0.18. So much so that I can't upgrade to 4.0.18 at all. Here is the startup log from a 4.0.18 mysqld: 040227 02:00:22 mysqld started 040227 2:00:22 Warning: Setting lower_case_table_names=2 because file system for /mysqldata/ is case insensitive InnoDB: Error: tablespace size stored in header is 2438400 pages, but InnoDB: the sum of data file sizes is 256 pages 040227 2:00:23 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 040227 2:00:23 InnoDB error: Cannot find table jive_ebay_us/jiveuser from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. ERROR: 1016 Can't open file: 'jiveuser.InnoDB'. (errno: 1) 040227 2:00:23 Slave: Error 'Can't open file: 'jiveuser.InnoDB'. (errno:1)' on query 'UPDATE jiveUser SET some user data WHERE some qualifer'. Default database: 'jive_ebay_us', Error_code: 1016 040227 2:00:23 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binary-log.008' position 781142831 So, let's walk through these... For starters mysqld decides on it's own it will set lower_case_table_names=2 on it's own because "file system for /mysqldata/ is case insensitive"... Yes, that's true, the file system is case insensitive, it's Mac OSX 10.3. However because we have had issues with this in the 4.0.17 introduction of this "feature" we have a statement in our my.cnf file which specifically says "set-variable= lower_case_table_names=0". Despite us manually telling MySQL that we want it to be case sensitive in all cases it ignores our setting and chooses it's own. Now that it has ignored our request to be case sensitive all of the time for all of the requests we are experiencing problems with InnoDB not recognizing we have tables that we have. Specifically in the error cited above we have a table called jiveUser, that is evident in the way the request is made to to the database to "set jiveUser"... Yet InnoDB is failing because it can't find jiveuser. So it seems that MySQL has behaved as promised with a lower_case_table_names=2 value and converted the request to lower case... InnoDB however can't find a lower case version of this table. Of course all our table names have upper and lower case characters and this particular entry happened to score the jackpot because it was the first query too come along after we booted 4.0.18. It happens on all our databases, on all our tables. I don't know what we are doing wrong here... Any value of lower_case_table_names results in 4.0.18 not running (OK, it runs, but our applications don't run with it), so we can not upgrade to 4.0.18. MySQL seems to be ignoring our my.cnf startup value for lower_case_table_names and InnoDB seems to be unable to find our tables if MySQL changes the case to lower case. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or MySQL error ?
Dyego, http://www.mysql.com/doc/en/JOIN.html LEFT JOIN means an 'outer join'. I think it does NOT force the join order. STRAIGHT JOIN forces the join order. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html > - Alkuperäinen viesti - > Lähettäjä: "Dyego Souza Dantas Leal" <[EMAIL PROTECTED]> > Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> > Lähetetty: Tuesday, March 02, 2004 2:39 PM > Aihe: InnoDB or MySQL error ? > > > > > > The script of the database is attached. > > > > The name is sqlreg_italo.sql.gz > > > > I'am testing with MySQL 4.0.18-pro on Debian Linux Box. > > Kernel 2.4 and 2.6 > > > > There is an error in MySQL optimizer !!! > > > > test: > > > > > > mysql> SET FOREIGN_KEY_CHECKS=0; > > Query OK, 0 rows affected (0.00 sec) > > > > mysql> create database sqlreg3_italo;use sqlreg3_italo;source > sqlreg_italo.sql; > > Database changed > > . > > > > Query OK, 0 rows affected (0.02 sec) > > > > mysql> SET FOREIGN_KEY_CHECKS=1; > > Query OK, 0 rows affected (0.02 sec) > > > > mysql> explain select straight_join * > > -> from anparte as ap > > -> left join an on an.An_Id = ap.Ap_Anotacao > > -> left join destino_selo on ds_Destino = an.an_id > > -> left join selo on ds_selo = se_id > > -> left join l1 on L1_Protocolo = an.An_Protocolo > > -> left join l2 on an.An_Livro = "2" AND L2_Id = an.An_IdLivro > > -> left join l3 on an.An_Livro = "3" AND L3_Id = an.An_IdLivro > > -> left join tr on an.An_Livro = "T" AND Tr_Id = an.An_IdLivro > > -> left join lri on an.An_Livro = "I" AND lr_Id = an.An_IdLivro > > -> > > -> where an.An_Livro = "2" > > -> group by ap_Anotacao > > -> order by an.An_Num > > -> limit 20; > > > +--++--- > ---+-- -- > -+-+---+--+-+ > > | table| type | possible_keys > | key | key_len | ref | rows | Extra > | > > > +--++--- > ---+-- -- > -+-+---+--+-+ > > | destino_selo | system | PRIMARY > | NULL|NULL | NULL |0 | const row not found > | > > | selo | system | PRIMARY,Se_Id > | NULL|NULL | NULL |0 | const row not found > | > > | l1 | system | L1_Protocolo,IL1_ProtocoloEtapa > | NULL|NULL | NULL |0 | const row not found > | > > | l2 | system | L2_Id > | NULL|NULL | NULL |0 | const row not found > | > > | l3 | system | L3_Id > | NULL|NULL | NULL |0 | const row not found > | > > | tr | system | Tr_Id > | NULL|NULL | NULL |0 | const row not found > | > > | lri | system | Lr_Id,PRIMARY_IDX2 > | NULL|NULL | NULL |0 | const row not found > | > > | ap | index | > IAp_AnotacaoImportancia,IAp_AnotaOnusOnusCanc,IAp_AnotaCompCompCanc,IAp_Anot > aProp,IAp_AnotaClassifTransf | IAp_AnotacaoImportancia | 8 | NULL | > 0 | Using temporary; Using filesort | > > | an | ref| An_Id,IAn_L4_LivroIdLvNum,Ian_LivroNumLri > | Ian_LivroNumLri | 1 | const |1 | Using where > | > > > +--++--- > ---+-- -- > -+-+---+--+-+ > > 9 rows in set (0.01 sec) > > > > > > > > The word "straight_join" make it explicit for the MySQL to use the > > indexes in the left join order... but it isn't working... > > > > Is it a MySQL opt bug or InnoDB opt bug ? > > > > The correct response of mysql is: > > > > > +--++--
Re: Re[2]: InnoDB or MySQL error ?
Dyego, - Alkuperäinen viesti - Lähettäjä: "Dyego Souza Dantas Leal" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Tuesday, March 02, 2004 3:09 PM Aihe: Re[2]: InnoDB or MySQL error ? > Heikki: > > Hi, > >I would like to explain what is going on with the straight_join >when it is used with my database. > >Suppose the command bellow: > >explain select table1 left join table2 on A=B left join table3 on >C=D where ID = 1; > >When it is executed on one of our servers, mySql uses the right >order for the search. It indicates the order Table1, table2 and >table3, showing the indexes for each one. > >When it is executed on another of our servers, with the same >database, but with another data, mySql indicates a non-optimized >order for the search, and do not use the appropriated indexes, >using sequential search. It can indicate, for example, table3, >table1 and table2. > >To solve it I tried to modify the command, to use the >straight_join, that is intended to force mySql to use the order I >used to write the command: > >explain select STRAIGHT_JOIN table1 left join table2 on A=B >left join table3 on C=D where ID = 1; > >But it got no success is this case. I'd already used it with >success many times, but using it in this specific database >and in this specific situation, the Straight_Join didn't work. > >I think it would be great if you could do something, because it can >be a mySql bug, or innoDb bug instead. Because this, I sent to you >an e-mail with the exact command I used and with the database I >use. With that you will be able to reproduce the bug, as I could >before sending it to you. please file a bug report to bugs.mysql.com. This really cannot be an InnoDB bug because InnoDB does not determine the join order. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html > Tuesday, March 2, 2004, 10:02:06 AM, você escreveu: > > ---[inicio]-- > > HT> Dyego, > > HT> http://www.mysql.com/doc/en/JOIN.html > > HT> LEFT JOIN means an 'outer join'. I think it does NOT force the join order. > > HT> STRAIGHT JOIN forces the join order. > > HT> Best regards, > > HT> Heikki > HT> Innobase Oy > HT> http://www.innodb.com > HT> InnoDB - transactions, row level locking, and foreign keys for MySQL > HT> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > HT> tables > > ---[cortar]-- > > > InnoDB,MySQL,SQL,Query > - > ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento > - > E S C R I B A I N F O R M A T I C A > - > The only stupid question is the unasked one (somewhere in Linux's HowTo) > Linux registred user : #230601 > --ICQ : 1647350 > $ look into "my eyes" Phone : +55 041 2106-1212 > look: cannot open my eyes Fax : +55 041 296 -6640 > - >Reply: [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 4.1.1 crashes
Lior, this is probably the subquery bug that was fixed in the 4.1 source tree about 2 months ago. The seg fault happens because MySQL-4.1.1 releases table 'intention' locks too early, and InnoDB closes the consistent 'read view' of the transaction. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html List:MySQL General Discussion« Previous MessageNext Message » From: Nisim, Lior Date: March 3 2004 1:02 pm Subject: RE: Mysql 4.1.1 crashes Hi Here is the stack trace : 0x8106af3 handle_segfault + 423 0x40047a65 _end + 933903229 0x832b873 lock_clust_rec_cons_read_sees + 111 0x82774fc row_search_for_mysql + 9884 0x81783cb general_fetch__11ha_innobasePcUiUi + 75 0x8178496 index_next_same__11ha_innobasePcPCcUi + 34 0x813eecc join_read_next_same__FP14st_read_record + 52 0x813e57e sub_select__FP4JOINP13st_join_tableb + 330 0x813e226 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 434 0x8134b36 exec__4JOIN + 4234 0x8135068 mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st _orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 832 0x8131fde handle_select__FP3THDP6st_lexP13select_result + 174 0x81142b7 mysql_execute_command__FP3THD + 1427 0x8118d49 mysql_parse__FP3THDPcUi + 177 0x8112f3f dispatch_command__F19enum_server_commandP3THDPcUi + 1635 0x81128d1 do_command__FP3THD + 161 0x8112047 handle_one_connection + 563 0x40044c3f _end + 933891415 0x401b5b2a _end + 935402562 Thanks Lior -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:08 PM To: Nisim, Lior Cc: [EMAIL PROTECTED] Subject: Re: Mysql 4.1.1 crashes Did you run a stack trace? >> Original Message > << On 3/2/04, 9:02:15 AM, "Nisim," Lior <[EMAIL PROTECTED]> wrote regarding Mysql 4.1.1 crashes: > hi > My server crashes on sub qurey , can any one help ? > > --- > 040302 16:32:20 mysqld started > 040302 16:32:20 InnoDB: Started; log sequence number 0 60169 > /tmp/mysql/mysql_4.1.1/bin/mysqld: ready for connections. > Version: '4.1.1-alpha-max' socket: '/tmp/mysql.sock' port: 3306 > 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=8388600 > read_buffer_size=131072 > max_used_connections=0 > max_connections=100 > threads_connected=1 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections > = 225791 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > thd=0x867e9a0 > 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=0x424a6838, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x8106af3 > 0x40047a65 > 0x832b873 > 0x82774fc > 0x81783cb > 0x8178496 > 0x813eecc > 0x813e57e > 0x813e226 > 0x8134b36 > 0x8135068 > 0x8131fde > 0x81142b7 > 0x8118d49 > 0x8112f3f > 0x81128d1 > 0x8112047 > 0x40044c3f > 0x401b5b2a > New value of fp=(nil) failed sanity check, terminating stack trace! > Please read http://www.mysql.com/doc/en/Using_stack_trace.html > and > follow instructions on how to resolve the stack trace. Resolved > stack trace is much more helpful in diagnosing the problem, so please do > resolve it > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort... > thd->query at 0x8685398 = SELECT milestone,date,user,notes FROM > milestones_info WHERE block='agadderc' group by milestone having > date=(SELECT max(date) FROM milestones_info as tmp WHERE > block='agadderc' AND milestone=tmp.milestone) > thd->thread_id=1 > 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 > 040302 16:32:31 mysqld restarted > 040302 16:32:31 InnoDB: Started; log sequence number 0 60169 > /tmp/mysql/mysql_4.1.1/bin/mysqld: ready for connections. > Version: '4.1.1-alpha-max' socket: '/tmp/mysql.sock' port: 3306 > > --- > >Description: > mysql crashes on sub query > >How-To-Re
Re: Innodb table space getting filled up without any increase in actual rows!!
Sp. Raja, please check with SHOW INNODB STATUS\G if purge is still running and removing delete-marked rows. Also check that you do not have old, dangling transactions, which can prevent purge from running, as those old transactions could still see the delete-marked rows. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html List:MySQL General Discussion« Previous MessageNext Message » From:Sp.RajaDate:March 3 2004 8:48am Subject:Fw: Innodb table space getting filled up without any increase in actual rows!! I missed attaching the trace file! Sorry !! Regards, Sp.Raja > Original Message > From: "Sp.Raja" <[EMAIL PROTECTED]> > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > Date: Wed, Mar-3-2004 1:13 PM > Subject: Innodb table space getting filled up without any increase in actual rows!! > > Hi List, > > My tablespace is getting filled up so quick when no. of transactions increase without number of rows increasing considerably. > I have a test client which adds and deletes row dynamically maintaining number of rows at any instant between 20 and 60. > > When I run this test client, after some time the client emits the following error: > > "[MySQL][ODBC 3.51 Driver][mysqld-4.0.15a-debug]The table 'axactivealarmtbl' is full." > > I was confused on seeing this. So ran the test again but this time monitoring table status. I noticed that Rows, Data_length and Index_length column increased monotonically and InnoDB free decreased. I was not able to reason why ? > > But when I used select count(*) from it consistently gave me numbers between 20 and 60 > > I have attached output of "show table status"(trace.txt) as I observed taken at increasing time. > > Any Pointers/Ideas on this to help me resolve this issue?? > > Thanks, > Sp.Raja > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=1com > +--+++--++-- ---+-+--+---++-- ---+-+++--+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-- ---+-+--+---++-- ---+-+++--+ | axactivealarmtbl | InnoDB | Dynamic| 467 |210 | 98304 |NULL |98304 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 9216 kB | +--+++--++-- ---+-+--+---++-- ---+-+++--+ +--+++--++-- ---+-+--+---++-- ---+-+++--+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-- ---+-+--+---++-- ---+-+++--+ | axactivealarmtbl | InnoDB | Dynamic| 4946 |321 | 1589248 |NULL | 622592 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 6144 kB | +--+++--++-- ---+-+--+---++-- ---+-+++--+ +--+++--++-- ---+-+--+---++-- ---+-+++--+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-- ---+-+--+---++-- ---+---
Re: Major problem converting MyISAM to InnoDB
Cliff, please run CHECK TABLE ... on your table and look if mysqld prints anything to the .err log. Send the WHOLE .err log to me. What is your operating system version? > > amount of changes since today. I am trying all I can to recover the data > > from this table because I know it is in there. I deleted the frm file and > > recreated it because mysql was complaining about the file being corrupt. I > > used the same table definition as before. I pointed the datafile for innodb What does this mean: "mysql was complaining about the file being corrupt"? Did it say that the .frm file was corrupt? What did it print exactly? > > used the same table definition as before. I pointed the datafile for innodb > > to the correct file that is believe to be corrupt ... What does the above mean? Are you sure that you recreated the .frm file with the right CREATE TABLE? You can use the innodb_table_monitor to print the internal data dictionary of InnoDB: http://www.innodb.com/ibman.php#InnoDB.Monitor 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Original Message - From: "Sasha Pachev" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, March 04, 2004 7:18 AM Subject: Re: Major problem converting MyISAM to InnoDB > Cliff wrote: > > Recently I tried to convert our largest table from MyISAM to InnoDB. During > > the process I believe there was a problem where something was corrupt along > > the way. It was stupid, but I did not verify that our backup system was > > working correctly, since I assumed it had been running as usual. It was not > > however. Our latest backup was from November of 2003, which is a large > > amount of changes since today. I am trying all I can to recover the data > > from this table because I know it is in there. I deleted the frm file and > > recreated it because mysql was complaining about the file being corrupt. I > > used the same table definition as before. I pointed the datafile for innodb > > to the correct file that is believe to be corrupt and this is what happens. > > I can login to mysql and execute queries like usual, however some queries > > are crashing mysqld and restarting it. For instance, a record with id of > > 10027 was one that was edited just before the table altering. If I select * > > where id=10027, the server restarts. I have tried using set-variable = > > innodb_force_recovery = 4 in my.cnf with no luck either. I know the data is > > in the file because I can page through it and see portions of each IDs > > information. Here is the error I get when the server restarts from > > server.err: > > > > 040303 17:49:08 mysqld ended > > > > > > 040303 17:49:09 mysqld started > > 040303 17:49:10 InnoDB: Started > > /mnt/disk2/mysql/bin/mysqld: ready for connections. > > Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 > > 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=8388600 > > read_buffer_size=131072 > > max_used_connections=0 > > max_connections=100 > > threads_connected=1 > > It is possible that mysqld could use up to > > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = > > 225791 K > > bytes of memory > > Hope that's ok; if not, decrease some variables in the equation. > > > > Number of processes running now: 0 > > 040303 17:49:29 mysqld restarted > > 040303 17:49:29 InnoDB: Started > > /mnt/disk2/mysql/bin/mysqld: ready for connections. > > Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 > > > > > > > > > > > > > > As you can see I am running mysql 4.0.18. Here is the my.cnf file with > > innodb settings: > > > > basedir=/mysql > > long_query_time=3 > > log-slow-queries=/tmp/slowmysql.log > > innodb_data_home_dir = > > innodb_data_file_path = /mysql/data/ibdata1:10M:autoextend > &
Re: Innodb table space getting filled up without any increase in actual rows!!
Sp. Raja, you have 3 dangling transactions that have been active for almost 2 hours. They prevent purge from removing those delete-marked rows. ---TRANSACTION 0 832338, ACTIVE 6027 sec, OS thread id 65 MySQL thread id 41, query id 1036449 localhost root Trx read view will not see trx with id >= 0 832339, sees < 0 832214 ---TRANSACTION 0 832337, ACTIVE 6027 sec, OS thread id 57 MySQL thread id 42, query id 1036436 localhost root Trx read view will not see trx with id >= 0 832338, sees < 0 832214 ---TRANSACTION 0 832214, ACTIVE 6055 sec, OS thread id 110 MySQL thread id 51, query id 1036076 localhost root Trx read view will not see trx with id >= 0 832215, sees < 0 832215 You should commit these transactions. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Alkuperäinen viesti - Lähettäjä: "Sp.Raja" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Lähetetty: Thursday, March 04, 2004 4:38 PM Aihe: Re: Innodb table space getting filled up without any increase in actual rows!! Heikki, Thanks for your reply. As you said I have attached trace collected for SHOW INNODB STATUS. Please point me the things which are going wrong. Do you mean I have to use auto-commit transactions? What changes should I do (or) take care when using MySQL through ODBC to avoid old transactions ? To be more specific my client does a lot of inserts/deletes using a ODBC connection maintaining number of rows to be between 60 and 70. Thanks, Sp.Raja > ----Original Message > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Date: Thu, Mar-4-2004 1:30 AM > Subject: Re: Innodb table space getting filled up without any increase in actual rows!! > > Sp. Raja, > > please check with > > SHOW INNODB STATUS\G > > if purge is still running and removing delete-marked rows. Also check that > you do not have old, dangling transactions, which can prevent purge from > running, as those old transactions could still see the delete-marked rows. > > 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 > > Register now for the 2004 MySQL Users Conference! > http://www.mysql.com/events/uc2004/index.html > > > List:MySQL General Discussion« Previous MessageNext Message » > From:Sp.RajaDate:March 3 2004 8:48am > Subject:Fw: Innodb table space getting filled up without any increase in > actual rows!! > > I missed attaching the trace file! > Sorry !! > > Regards, > Sp.Raja > > > Original Message > > From: "Sp.Raja" <[EMAIL PROTECTED]> > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > Date: Wed, Mar-3-2004 1:13 PM > > Subject: Innodb table space getting filled up without any increase in > actual rows!! > > > > Hi List, > > > > My tablespace is getting filled up so quick when no. of transactions > increase without number of rows increasing considerably. > > I have a test client which adds and deletes row dynamically maintaining > number of rows at any instant between 20 and 60. > > > > When I run this test client, after some time the client emits the > following error: > > > > "[MySQL][ODBC 3.51 Driver][mysqld-4.0.15a-debug]The table > 'axactivealarmtbl' is full." > > > > I was confused on seeing this. So ran the test again but this time > monitoring table status. I noticed that Rows, Data_length and Index_length > column increased monotonically and InnoDB free decreased. I was not able to > reason why ? > > > > But when I used select count(*) from it consistently gave me > numbers between 20 and 60 > > > > I have attached output of "show table status"(trace.txt) as I observed > taken at increasing time. > > > > Any Pointers/Ideas on this to help me resolve this issue?? > > > > Thanks, > > Sp.Raja > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http:
Re: Major problem converting MyISAM to InnoDB
Cliff, - Original Message - From: ""Cliff"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, March 04, 2004 7:07 PM Subject: Re: Major problem converting MyISAM to InnoDB > Hi, I ran CHECK TABLE with the EXTENDED option on the table and it reports > everything ok: > > mysql> check table allusa extended; > +---+---+--+--+ > | Table | Op| Msg_type | Msg_text | > +---+---+--+--+ > | bb.allusa | check | status | OK | > +---+---+--+--+ > 1 row in set (4.74 sec) hmm... then the table most probably is not corrupt inside InnoDB. > I am running FreeBSD 4.9-STABLE. The error log did not show anything while > or after the query ran: > > 040304 09:09:32 mysqld started > 040304 9:09:33 InnoDB: Started > /mnt/disk2/mysql/bin/mysqld: ready for connections. > Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 > > If I do a select statement inside of mysql into an outfile, the server > crashes. However if I do a select on the same data to stdout, it works fine > most of the time. I am having my terminal program capture the screen output > from mysql to a file for each field and editing the records to a format that > is easy to insert back in. As you can imagine this is less than ideal, but > it is the only solution I can come up with at this point. Thanks for your > help, if you need anything else let me know. This sounds strange. I cannot recall a case where a simple SELECT consistently crashes, while CHECK TABLE reports the table as ok! An explanation might be that the .frm file is not the right one for the table, though I do not understand how the mysql client then could work. 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 Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum precision for DECIMAL column in MySQL
Maximum precision for DECIMAL column in MySQLMatt, in 4.1 this restriction has been removed: [EMAIL PROTECTED]:~/mysql-4.1/client> mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.1-alpha-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `scale_test6` (`a_dec` decimal(30,23) default NULL) TYPE=MyI SAM; Query OK, 0 rows affected (0.10 sec) mysql> INSERT INTO scale_test6 (a_dec) VALUES (12345678.123456789012345678901234 ); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM scale_test6; +--+ | a_dec| +--+ | 12345678.12345678918063640594482 | +--+ 1 row in set (0.03 sec) mysql> Regards, Heikki - Original Message ----- From: Matt Solnit To: [EMAIL PROTECTED] ; Heikki Tuuri Sent: Friday, May 30, 2003 8:02 PM Subject: Maximum precision for DECIMAL column in MySQL It looks like the maximum precision for a DECIMAL column is 17 in MySQL. Is this documented anywhere? I get the following results: mysql> CREATE TABLE `scale_test6` (`a_dec` decimal(30,23) default NULL) TYPE=MyISAM; mysql> INSERT INTO scale_test6 (a_dec) VALUES (12345678.123456789012345678901234); mysql> SELECT * FROM scale_test6; +--+ | a_dec| +--+ | 12345678.12345678900 | +--+ 1 row in set (0.00 sec) Am I doing everything correctly? I would have expected to see the entire decimal stored correctly in the table. -- Matt Solnit <[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.13 is released
Rainer,
- Original Message -
From: "Rainer Collet" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Saturday, May 31, 2003 5:49 PM
Subject: Re: MySQL/InnoDB-4.0.13 is released
> [EMAIL PROTECTED] ("Heikki Tuuri") wrote in message
news:<[EMAIL PROTECTED]>...
> > Hi!
> >
> > InnoDB is a MySQL table type which provides transactions, foreign key
> > constraints, and a non-free hot backup tool to MySQL.
>
> Where can I get this hot backup tool?
it is non-free and can be ordered from http://www.innodb.com. MySQL/InnoDB
support contracts also contain some complimentary Hot Backup licenses.
> > CONSTRAINT `0_16` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
ON
> > DELE
> > TE CASCADE
>
> How did you get this? I also use 4.0.13 but only get e.g.
> "CONSTRAINT `0_16` FOREIGN KEY (`parent_id`) REFERENCES `parent`
> (`id`)"
>
> Even I defined it with an "on update" or "on delete" statment. How can
> I retrieve these contraints afterwards?
Could you post a full example? They should be there when you do SHOW CREATE
TABLE. E.g.:
[EMAIL PROTECTED]:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.14-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE parent(id INT NOT NULL,
-> PRIMARY KEY (id)) TYPE=INNODB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE child(id INT, parent_id INT,
-> INDEX par_ind (parent_id),
-> FOREIGN KEY (parent_id) REFERENCES parent(id)
-> ON DELETE CASCADE
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> show create table child;
+---+---
-+
| Table | Create Table
|
+---+---
-+
| child | CREATE TABLE `child` (
`id` int(11) default NULL,
`parent_id` int(11) default NULL,
KEY `par_ind` (`parent_id`),
CONSTRAINT `0_14016` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
ON D
ELETE CASCADE
) TYPE=InnoDB |
+---+---
----
-+
1 row in set (0.00 sec)
mysql>
> Thanks very much,
> Rainer
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE
Hi! I have now updated the manual to reflect this restriction on InnoDB tables: " You cannot set the first AUTO_INCREMENT column value in InnoDB with CREATE TABLE ... AUTO_INCREMENT=... (or ALTER TABLE ...). To set the value insert a dummy row with a value one less, and delete that dummy row. " Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Becoming Digital" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, June 02, 2003 10:15 AM Subject: Re: ALTER TABLE > Is 'id' indexed? AUTO_INCREMENT fields must be to work correctly. Try this: > > ALTER TABLE users > ADD INDEX idx_id (id), > AUTO_INCREMENT=1000; > > > Quite honestly, your ID field should probably be the primary key, so: > > ALTER TABLE users > MODIFY id INT(9) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, > AUTO_INCREMENT=1000; > > Edward Dudlik > Becoming Digital > www.becomingdigital.com > > > - Original Message - > From: "Lilian" <[EMAIL PROTECTED]> > To: "Becoming Digital" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, 02 June, 2003 02:28 > Subject: Re: ALTER TABLE > > > I have check http://www.mysql.com/doc/en/ALTER_TABLE_problems.html but no > solution for my problem. > > I have table_name 'users' (TYPE=InnoDB) with one of field id > > id int(9) unsigned NOT NULL auto_increment, > > when I update last insert id to 1000 , mysql 3 add next data automatically > with 1001, 1002 etc. > version 4 add data with id 3, 4 etc, it not check the value of last ID. > > I want to know if there are some solution, I need to start that ID from 1000 > not from 1. > > lilian > > > > - Original Message - > From: "Becoming Digital" <[EMAIL PROTECTED]> > To: "Lilian" <[EMAIL PROTECTED]> > Sent: Monday, June 02, 2003 9:04 AM > Subject: Re: ALTER TABLE > > > > Did you check http://www.mysql.com/doc/en/ALTER_TABLE_problems.html for > info? > > > > Edward Dudlik > > Becoming Digital > > www.becomingdigital.com > > > > > > - Original Message - > > From: "Lilian" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Monday, 02 June, 2003 01:17 > > Subject: ALTER TABLE > > > > > > Hi, > > I have tested that function > > > > ALTER TABLE table_name_here AUTO_INCREMENT = 1000; > > > > on version 4.0.12 and it's not working. > > (table TYPE=InnoDB) > > > > in 3.23.54 that is OK > > > > does anybody know what's wrong here? > > > > regards, > > lilian > > > > -- > > 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]
Re: debuggine 1205 / LOCK wait timeout exceeded errors
Roman, you can use SHOW INNODB STATUS to look what active transactions you have and how many lock structs they have. Also innodb_lock_monitor helps. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Roman Neuhauser" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, June 03, 2003 7:51 PM Subject: debuggine 1205 / LOCK wait timeout exceeded errors > Hi there, > > I have a problem with LOCKs. I use InnoDB tables and transactions. > > [EMAIL PROTECTED] ~ 1001:0 > /usr/local/libexec/mysqld --version > /usr/local/libexec/mysqld Ver 4.0.13 for portbld-freebsd4.8 on i386 > [EMAIL PROTECTED] ~ 1002:0 > uname -sr > FreeBSD 4.8-RC > [EMAIL PROTECTED] ~ 1003:0 > httpd -v > Server version: Apache/1.3.26 (Unix) > Server built: Jul 23 2002 14:12:25 > > mod_php-4.3.1 linked with libmysqlclient 4.0.11a-gamma > > This is one such case (one PHP script run, taken from the query log): > > 030603 18:18:47 19 Init DB webed > 19 Init DB webed > 19 Query SELECT COUNT(*) > FROM editor > WHERE id = 1 > 19 Init DB webed > 19 Query SELECT e.login, e.authorized, > e.firstname, e.lastname, > e.validfrom, e.validtill, > e.createdby, e.createdon, > e.changedby, e.changedon, > e.caps, e.wysiwyg > FROM editor e > WHERE id = 1 > 030603 18:18:48 19 Init DB webed > 19 Query SELECT COUNT(*) > FROM server > WHERE id = 4 > 19 Init DB webed > 19 Query SELECT COUNT(*) > FROM editor > WHERE id = 1 > 19 Init DB webed > 19 Query SELECT g.id AS gid, g.name > FROM acl_group g, acl_member m > WHERE g.id = m.gid > AND uid = 1 > 19 Init DB webed > 19 Query SELECT > MAX(access) AS max, > MIN(access) AS min > FROM acl_access a > WHERE a.objtype = 128 > AND a.objid = 4 > AND a.gid IN (1) > GROUP BY objid > 19 Init DB webed > 19 Query DELETE FROM acl_access > WHERE objtype = 128 > AND objid = 4 > > Here the server emitted 1205. > > DELETE FROM acl_access WHERE objtype = 128 AND objid = 4 > [nativecode = 1205 * * LOCK wait timeout exceeded; Try restarting TRANSACTION ] > > I don't see what should cause it to behave so. I'm the only user ATM, > IOW the listing above is unaltered in any way, and represents exactly > one run of the PHP script. Notice the lack of any transaction-related or > LOCK commands (in most cases, however, the DML command is preceeded by a > BEGIN, and immediatelly [that is, after the timeout] followed by a > ROLLBACK). > > I'd love to see this resolved, and will happily provide any info > requested. > > TIA && HAND > > -- > FreeBSD 4.8-RC > 6:23PM up 14 days, 2:54, 16 users, load averages: 0.04, 0.04, 0.00 > > -- > 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: malloc'ing 2GB+ of memory in mysql
Per, I remember someone also reporting a problem that glibc or Linux does not allow creation of new threads if one has allocated >= 2 GB user memory. I think there are problems in where the OS places the excutable, thread stacks, etc. So it is uncharted territory. Oracle seems to have an option to use AWE memory on the Red Hat Advanced Server. Then the limit is 64 GB on a 32-bit Intel processor. InnoDB-4.1 has the same AWE option, but only on certain Windows versions. If Itanium and Opteron fail to take off, or a feature sponsor appears, I may consider implementing AWE also on Linux. The memory crunch is getting so severe that I believe some 64-bit processor must become common by 2005. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Per Andreas Buer" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 04, 2003 1:21 PM Subject: malloc'ing 2GB+ of memory in mysql > Hi > > The Mysql binary distribution for IA32-linux is statically linked with > glibc. glibc malloc limits memory allocations to 2GB, which means that a > buffer in mysql can't grow beyond 2GB. This is due to some paranoia in > glibc malloc - they don't rely on the size to be an unsigned int - which > limits the size to 2^31 on any 32-bit platform. > > Has anyone tried to remove this limit in glibc malloc or linking Mysql > with another malloc implementation? > > -- > Per Andreas Buer > > -- > 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't open privilege tables: Table 'mysql.host' doesn't exist
Nils, - Original Message - From: ""Nils Valentin"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 04, 2003 7:05 AM Subject: Re: Can't open privilege tables: Table 'mysql.host' doesn't exist > Sorry, I should have given more details.=20 > Its Mysql 4.1 alpha on a Suse Linux system Version 8.1. > > =2E..and yes, the mysql.host table and the other 5 exist and are well alive= > ;-). > > If I replace my.cnf with a version skipping innodb then it perfectly starts= > =20 > up, so it must be related to innodb, perhaps my.cnf and/or mysql itself. the problem is in your my.cnf or the access rights of the person running mysqld. The error means mysqld cannot access the file host.frm in the mysql subdir under the datadir. I suggest specifying the datadir explicitly in your my.cnf. > Best regards > > Nils Valentin Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ > > Hi Mysql list members, > > > > I added a Innodb database to the existing MyISAM databases and now MySQL > > doesnt start up. From the InnoDB logfile I saw this error message > > > > Can't open privilege tables: Table 'mysql.host' doesn't exist > > > > > > Does any body know what this error comes from ? > > > > innodb logfile extract. > > > > 030603 16:46:20 InnoDB: Log file ./ib_logfile0 did not exist: new to be > > created > > InnoDB: Setting log file ./ib_logfile0 size to 80 MB > > InnoDB: Database physically writes the file full: wait... > > 030603 16:46:31 InnoDB: Log file ./ib_logfile1 did not exist: new to be > > created > > InnoDB: Setting log file ./ib_logfile1 size to 80 MB > > InnoDB: Database physically writes the file full: wait... > > InnoDB: Doublewrite buffer not found: creating new > > InnoDB: Doublewrite buffer created > > InnoDB: Creating foreign key constraint system tables > > InnoDB: Foreign key constraint system tables created > > 030603 16:46:48 InnoDB: Started > > 030603 16:46:49 Fatal error: Can't open privilege tables: Table > > 'mysql.host' doesn't exist > > 030603 16:46:49 mysqld ended > > > > . > > > > > > 030604 09:30:28 mysqld started > > 030604 9:30:47 InnoDB: Database was not shut down normally. > > InnoDB: Starting recovery from log files... > > InnoDB: Starting log scan based on checkpoint at > > InnoDB: log sequence number 0 43892 > > InnoDB: Doing recovery: scanned up to log sequence number 0 43892 > > 030604 9:30:48 InnoDB: Flushing modified pages from the buffer pool... > > 030604 9:30:49 InnoDB: Started > > 030604 9:30:49 Fatal error: Can't open privilege tables: Table > > 'mysql.host' doesn't exist > > 030604 09:30:49 mysqld ended > > > > Any reply much appreciated. > > > > -- > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > Valentin Nils > > Internet Technology > > > > E-Mail: [EMAIL PROTECTED] > > URL: http://www.knowd.co.jp > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > =2D-=20 > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > Valentin Nils > Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > > -- > 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: malloc'ing 2GB+ of memory in mysql
Owen, - Original Message - From: "Owen Scott Medd" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, June 04, 2003 3:13 PM Subject: Re: malloc'ing 2GB+ of memory in mysql > I know we are facing this same question right now (I have 8 way servers > with 16GB of memory running MySQL, with 5 GB sitting unused while the poor > innodb buffer pool sits starved for memory). Do we replace these servers > with 4 way Opterons (are there 8 ways promised yet?) or is there another > answer? best to test first with a borrowed Opteron. I want to see the stability and that fsync() and other kernel calls work fast. A customer tested recently a big Itanium II box and its performance looked ok. > In particular, what is involved with being a "feature sponsor"? > (lol... show me the money, eh?). And, more importantly, have you done any > estimates about how long it would take to implement AWE in the RedHat AS > environment (which happens to be our environment too)? The sponsorship of multiple tablespaces will take all available time till September 15th, 2003. On that day you will be able to put every InnoDB table to its own file :). After that it might be a 2 week job to get AWE to Linux. The idea is to attach shared memory areas to the mysqld process on demand. Since you already have the appropriate hardware, testing is no problem. > Curious, > Owen Best regards, Heikki > On Wed, 4 Jun 2003, Heikki Tuuri wrote: > > Per, > > > > I remember someone also reporting a problem that glibc or Linux does not > > allow creation of new threads if one has allocated >= 2 GB user memory. I > > think there are problems in where the OS places the excutable, thread > > stacks, etc. > > > > So it is uncharted territory. Oracle seems to have an option to use AWE > > memory on the Red Hat Advanced Server. Then the limit is 64 GB on a 32-bit > > Intel processor. InnoDB-4.1 has the same AWE option, but only on certain > > Windows versions. > > > > If Itanium and Opteron fail to take off, or a feature sponsor appears, I may > > consider implementing AWE also on Linux. The memory crunch is getting so > > severe that I believe some 64-bit processor must become common by 2005. > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > http://www.innodb.com > > Transactions, foreign keys, and a hot backup tool for MySQL > > Order MySQL technical support from https://order.mysql.com/ > > > > > > - Original Message - > > From: "Per Andreas Buer" <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.mysql > > Sent: Wednesday, June 04, 2003 1:21 PM > > Subject: malloc'ing 2GB+ of memory in mysql > > > > > > > Hi > > > > > > The Mysql binary distribution for IA32-linux is statically linked with > > > glibc. glibc malloc limits memory allocations to 2GB, which means that a > > > buffer in mysql can't grow beyond 2GB. This is due to some paranoia in > > > glibc malloc - they don't rely on the size to be an unsigned int - which > > > limits the size to 2^31 on any 32-bit platform. > > > > > > Has anyone tried to remove this limit in glibc malloc or linking Mysql > > > with another malloc implementation? > > > > > > -- > > > Per Andreas Buer > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > -- > USMail: InterGuide Communications, 230 Lyn Anne Court, Ann Arbor, MI 48103 > phone: +1 734 997-0922 fax: +1 734 661-0324 > mailto:[EMAIL PROTECTED] http://www.interguide.com/~osm/ > > [ Sometimes wrong. Never in doubt. ] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: debuggine 1205 / LOCK wait timeout exceeded errors
Roman, looks like your application fails to commit this transaction: " 030604 13:52:21 INNODB MONITOR OUTPUT ... LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 407759, ACTIVE 2090 sec, OS thread id 10251 10 lock struct(s), heap size 1024, undo log entries 5 MySQL thread id 1, query id 914 localhost www ... " Why is it lingering idle for over 30 minutes? " TABLE LOCK table webed/section_txt trx id 0 407759 lock_mode IX " InnoDB table locks are 'intention locks' which just mark that the transaction has some row locks in the table. Currently, the only use of for them is in the query cache of MySQL-4.1. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Roman Neuhauser" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, June 04, 2003 3:44 PM Subject: Re: debuggine 1205 / LOCK wait timeout exceeded errors > # [EMAIL PROTECTED] / 2003-06-03 22:59:47 +0300: > > you can use SHOW INNODB STATUS to look what active transactions you have and > > how many lock structs they have. > > > > Also innodb_lock_monitor helps. > > Hi Heikki, > > thanks for the reply. I have dropped the database, recreated it, > stopped the server and started it again, turned on the > innodb_lock_monitor, and had a hard time getting it to deadlock. > > After some time, it finaly issued the LOCK wait timeout exceeded > error. > > I have the error log with innodb_lock_monitor messages, and the > query log. Both were empty when I started the server, and I shut > down the server right after I got the error (looks like the monitor > writes into the error log even when the server doesn't process any > queries). Plus, I was the only user accessing the server. > > Unfortunately, I've forgotten to run SHOW INNODB STATUS, is that > info crucial, or can you get it from the monitor? I hope the latter > is the case since the error is intermittent, and sometimes it's > quite hard to make it show up. > > I've put the log files online at http://roman.bellavista.biz/mysql/, > since they're quite big. > > An interesting fact: I was trying to get it to spit out the error, > and couldn't, then left the server alone for ~30 minutes, and then > got the error immediately: grep the logs for "13:53". > > What I don't understand is the TABLE LOCK lines: all the tables are > InnoDB tables, and I thought InnoDB doesn't use table locks. Or does > it. The only SQL statements in the query log that have to do with > locking are two pairs of GET_LOCK()/RELEASE_LOCK() wrapping the > creation of sequence tables. > > I will be very grateful for any info. > > -- > If you cc me or remove the list(s) completely I'll most likely ignore > your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables: Table 'mysql.host' doesn't exist
Nils, most probably you have some other difference in the my.cnf files. Regards, Heikki - Original Message - From: "Nils Valentin" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, June 04, 2003 6:22 PM Subject: Re: Can't open privilege tables: Table 'mysql.host' doesn't exist Hi Heikki, Thank you for the reply. I understand that this is related to the host.frm. Thank you very much. The part which I don't get is why would a standard my.cnf file (skipping innodb) start up and the my.cnf file containing the innodb settings give me such an error ? I understood your advice and I am in process of repairing this, however I would really appreciate if I could also technically understand why this is. This case I can learn and step up ;-) Do you mind explaining why this is ? Best regards Nils Valentin 2003年 6月 4日 水曜日 21:09、Heikki Tuuri さんは書きました: > Nils, > > - Original Message - > From: ""Nils Valentin"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.mysql > Sent: Wednesday, June 04, 2003 7:05 AM > Subject: Re: Can't open privilege tables: Table 'mysql.host' doesn't exist > > > Sorry, I should have given more details.=20 > > Its Mysql 4.1 alpha on a Suse Linux system Version 8.1. > > > > =2E..and yes, the mysql.host table and the other 5 exist and are well > > alive= > > > ;-). > > > > If I replace my.cnf with a version skipping innodb then it perfectly > > starts= > > > =20 > > up, so it must be related to innodb, perhaps my.cnf and/or mysql itself. > > the problem is in your my.cnf or the access rights of the person running > mysqld. > > The error means mysqld cannot access the file host.frm in the mysql subdir > under the datadir. I suggest specifying the datadir explicitly in your > my.cnf. > > > Best regards > > > > Nils Valentin > > Best regards, > > Heikki Tuuri > Innobase Oy > http://www.innodb.com > Transactions, foreign keys, and a hot backup tool for MySQL > Order MySQL technical support from https://order.mysql.com/ > > > > Hi Mysql list members, > > > > > > I added a Innodb database to the existing MyISAM databases and now > > > MySQL doesnt start up. From the InnoDB logfile I saw this error message > > > > > > Can't open privilege tables: Table 'mysql.host' doesn't exist > > > > > > > > > Does any body know what this error comes from ? > > > > > > innodb logfile extract. > > > > > > 030603 16:46:20 InnoDB: Log file ./ib_logfile0 did not exist: new to > > > be created > > > InnoDB: Setting log file ./ib_logfile0 size to 80 MB > > > InnoDB: Database physically writes the file full: wait... > > > 030603 16:46:31 InnoDB: Log file ./ib_logfile1 did not exist: new to > > > be created > > > InnoDB: Setting log file ./ib_logfile1 size to 80 MB > > > InnoDB: Database physically writes the file full: wait... > > > InnoDB: Doublewrite buffer not found: creating new > > > InnoDB: Doublewrite buffer created > > > InnoDB: Creating foreign key constraint system tables > > > InnoDB: Foreign key constraint system tables created > > > 030603 16:46:48 InnoDB: Started > > > 030603 16:46:49 Fatal error: Can't open privilege tables: Table > > > 'mysql.host' doesn't exist > > > 030603 16:46:49 mysqld ended > > > > > > . > > > > > > > > > 030604 09:30:28 mysqld started > > > 030604 9:30:47 InnoDB: Database was not shut down normally. > > > InnoDB: Starting recovery from log files... > > > InnoDB: Starting log scan based on checkpoint at > > > InnoDB: log sequence number 0 43892 > > > InnoDB: Doing recovery: scanned up to log sequence number 0 43892 > > > 030604 9:30:48 InnoDB: Flushing modified pages from the buffer > > > pool... 030604 9:30:49 InnoDB: Started > > > 030604 9:30:49 Fatal error: Can't open privilege tables: Table > > > 'mysql.host' doesn't exist > > > 030604 09:30:49 mysqld ended > > > > > > Any reply much appreciated. > > > > > > -- > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > > > > Valentin Nils > > > Internet Technology > > > > > > E-Mail: [
Re: RH 8.0 InnoDB: Assertion failure in thread 122911 in file mem0pool.c line 477
Richard,
please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it.
Also note that you should make sort buffer and record buffer smaller, say
512k. mysqld may be hogging too much memory.
record_buffer=2093056
sort_buffer=2097144
max_used_connections=385
max_connections=2000
threads_connected=382
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections =
3997872 K
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
..
Subject: RH 8.0 InnoDB: Assertion failure in thread 122911 in file
mem0pool.c line 477
From: Richard F. Rebel
Date: 04 Jun 2003 11:43:49 -0400
I have reported this before, but MySQL-Max-3.23.56-1 (official rpm's) is
repeatedly crashing.
Often when executing queries on an InnoDB table with about 1300 rows
that are similar to this:
select MEET, count(*)
from RATINGS_WHENU
where MEET in ('N','Y')
and SITE = '63'
group by MEET
I can run the query again and again, and cannot get it to crash myself,
but our applications run this query on regular occasion.
Below is output of some recent crashes this am.
Any help is appreciated. I reported this or atleast a very similar
problem before and I followed the advice to use the most recent MySQL
and to alter a few variables which did seem to help for a couple of
weeks atleast. Our db traffic really hasn't changed much since then so
I am flummoxed as to why now this would resurface.
Thanks,
Richard F. Rebel.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RH 8.0 InnoDB: Assertion failure in thread 122911 in filemem0pool.c line 477
Richard,
- Original Message -
From: "Richard F. Rebel" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, June 04, 2003 7:50 PM
Subject: Re: RH 8.0 InnoDB: Assertion failure in thread 122911 in
filemem0pool.c line 477
> Hello Heikki,
>
> Sorry, last version of the MySQL-Max rpm was missing the symbols file.
> This version has it, so I did as you asked. Here is the output of the
> most recent 4.
the memory deallocation seems to happen usually in
"
if (prebuilt->blob_heap != NULL) {
mem_heap_free(prebuilt->blob_heap);
prebuilt->blob_heap = NULL;
}
"
select MEET, count(*)
from RATINGS_WHENU
where MEET in ('N','Y')
and SITE = '63'
group by MEET
You have BLOBs in the table? How big are they? What does SHOW CREATE TABLE
give as the table definition?
What is really strange in the assertion failures on line 372 is that the hex
dump does show the memory block IS marked free:
"
InnoDB: Error: Removing element from mem pool free list 7 though the
InnoDB: element is not marked free! Dump of 100 bytes around element:
len 100; hex
544a5468652073697465206973206a75737420544f4f20534c4f572e204f7468657277697365
2c2077656c6c20646f6e652e810020646566696e6974656c79206265
7474657220636f6d707574657220626f6f6b2073656c6563; asc TJThe site is just TOO
SLOW. Otherwise, well done. definitely better computer book
selec;
030604 10:22:21 InnoDB: Assertion failure in thread 991475 in file
mem0pool.c line 372
"
Memory operations are protected by a mutex. As if the mutex would leak and
let other threads change the bit meanwhile.
> Regarding mem usage, the machine has 1gb, mysql is using 374MB which is
> fine as far as I am concerned. I can decrease the numbers tho, but the
> last time I did decrease the numbers you mentioned performance dropped
> and our batch jobs (not the web queries) now take about 20% longer to
> run. Do you think decreasing sort and record buffer figures might
> effect performance?
Best to test it. Note that if there is a spike of load then GROUP BY queries
can pile up and reserve lots of memory. The question is what is the memory
usage at the time of a crash.
> Thanks,
>
> Richard
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]
Re: RH 8.0 InnoDB: Assertion failure in thread 122911 infilemem0pool.c line 477
Richard,
- Original Message -
From: "Richard F. Rebel" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, June 04, 2003 8:38 PM
Subject: Re: RH 8.0 InnoDB: Assertion failure in thread 122911
infilemem0pool.c line 477
> Hello Heikki,
>
> On Wed, 2003-06-04 at 13:26, Heikki Tuuri wrote:
>
> > the memory deallocation seems to happen usually in
> >
> > "
> > if (prebuilt->blob_heap != NULL) {
> > mem_heap_free(prebuilt->blob_heap);
> > prebuilt->blob_heap = NULL;
> > }
> > "
> >
> > select MEET, count(*)
> > from RATINGS_WHENU
> > where MEET in ('N','Y')
> > and SITE = '63'
> > group by MEET
> >
> > You have BLOBs in the table? How big are they? What does SHOW CREATE
TABLE
> > give as the table definition?
> >
>
> mysql> show create table RATINGS_WHENU\G
> *** 1. row ***
>Table: RATINGS_WHENU
> Create Table: CREATE TABLE `RATINGS_WHENU` (
> `ID` int(11) NOT NULL auto_increment,
> `SITE` int(11) default NULL,
> `CLIENT_ID` varchar(64) default NULL,
> `STOCK` char(1) default NULL,
> `NAVIGATE` char(1) default NULL,
> `TIMELY` char(1) default NULL,
> `AGAIN` char(1) default NULL,
> `CUSTOMER` char(1) default NULL,
> `ENTERED_TIME` datetime default NULL,
> `FEEDBACK` text,
> `LAST_UPDATE` timestamp(14) NOT NULL,
> `MEET` char(1) default NULL,
> `CONTACTED` char(1) default NULL,
> `COMPLETE` char(1) default NULL,
> `SELECTION` char(1) default NULL,
> `PRICING` char(1) default NULL,
> PRIMARY KEY (`ID`)
> ) TYPE=InnoDB
> 1 row in set (0.02 sec)
>
> No blobs.
hmm... maybe mysqld is reporting a wrong query in a crash. Do you have any
BLOB (or TEXT) tables and what kind of queries you run on them? How big are
the BLOBs if any?
The hex dump looked like parts of some long CHAR or TEXT fiels.
By the way, you do not have an index on 'SITE' in the table. An index might
speed up your queries.
...
> Best,
>
> Richard
Regards,
Heikki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RH 8.0 InnoDB: Assertion failure in thread 122911infilemem0pool.c line 477
Richard,
- Original Message -
From: "Richard F. Rebel" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, June 04, 2003 10:28 PM
Subject: Re: RH 8.0 InnoDB: Assertion failure in thread
122911infilemem0pool.c line 477
>
> Hello Heikki,
>
>
> On Wed, 2003-06-04 at 14:29, Heikki Tuuri wrote:
> > Richard,
>
> > hmm... maybe mysqld is reporting a wrong query in a crash. Do you have
any
> > BLOB (or TEXT) tables and what kind of queries you run on them? How big
are
> > the BLOBs if any?
> >
> > The hex dump looked like parts of some long CHAR or TEXT fiels.
> >
> > By the way, you do not have an index on 'SITE' in the table. An index
might
> > speed up your queries.
>
> Okay, I by looking at that text, I found two tables with TEXT columns
> and then selected for the data that showed up in the error log. I found
> the following two columns that match.
>
> mysql> select * from RATINGS_WHENU where FEEDBACK like '%first order was
> very late%'\G
> *** 1. row ***
> ID: 180
> SITE: 144
>CLIENT_ID:
> 3LR0ZynFKovQmeffNkvFgfzrfB/d5b49a1512559abbd41350cfa46e4a61
>STOCK: Y
> NAVIGATE: Y
> TIMELY: N
>AGAIN: Y
> CUSTOMER: Y
> ENTERED_TIME: 2001-01-22 01:40:17
> FEEDBACK: first order was very late.
> second, was on time.
> LAST_UPDATE: 20010122014017
> MEET: Y
>CONTACTED: Y
> COMPLETE: Y
>SELECTION: Y
> PRICING: Y
> 1 row in set (0.01 sec)
>
> mysql> select * from RATINGS_WHENU where FEEDBACK like '%TOO SLOW%'\G
> *** 1. row ***
> ID: 247
> SITE: 221
>CLIENT_ID:
> KiAqHyjJGsvQmefvzfnrfNkrBC/1f856f57cb8bb2e77734a855460a505c
>STOCK: Y
> NAVIGATE: Y
> TIMELY: Y
>AGAIN: Y
> CUSTOMER: U
> ENTERED_TIME: 2001-01-29 11:43:27
> FEEDBACK: The site is just TOO SLOW. Otherwise, well done.
> LAST_UPDATE: 20010129114327
> MEET: Y
>CONTACTED: N
> COMPLETE: U
>SELECTION: Y
> PRICING: N
> 1 row in set (0.01 sec)
>
> mysql> show create table RATINGS_WHENU\G
> *** 1. row ***
>Table: RATINGS_WHENU
> Create Table: CREATE TABLE `RATINGS_WHENU` (
> `ID` int(11) NOT NULL auto_increment,
> `SITE` int(11) default NULL,
> `CLIENT_ID` varchar(64) default NULL,
> `STOCK` char(1) default NULL,
> `NAVIGATE` char(1) default NULL,
> `TIMELY` char(1) default NULL,
> `AGAIN` char(1) default NULL,
> `CUSTOMER` char(1) default NULL,
> `ENTERED_TIME` datetime default NULL,
> `FEEDBACK` text,
> `LAST_UPDATE` timestamp(14) NOT NULL,
> `MEET` char(1) default NULL,
> `CONTACTED` char(1) default NULL,
> `COMPLETE` char(1) default NULL,
> `SELECTION` char(1) default NULL,
> `PRICING` char(1) default NULL,
> PRIMARY KEY (`ID`)
> ) TYPE=InnoDB
>
> So perhaps mysql *is* reporting the wrong query. I was at least able to
> select them out of the table above.
actually, it probably is reporting the right query :). It is the TEXT column
`FEEDBACK` text. TEXT is essentially the same as a BLOB.
> I have exorcised the code we have that updates and interacts with this
> table in order to try and cause the server to crash to no avail.
>
> What's next?
I have added some diagnostic code to 4.0.14 which would reveal a leak in the
mutex.
I am also running a simulation of your workload on our SuSE Linux 4-way
server.
select MEET, count(*) from RATINGS_WHENU where MEET in ('N','Y') and SITE
= '21' group by MEET;
+--+--+
| MEET | count(*) |
+--+--+
| N| 137 |
| Y| 136 |
+--+--+
The assertions are so strange that I suspect thread stacks get corrupt. That
can be a mysqld bug, an out-of-memory problem, or a Linux/glibc bug. An
upgrade to Linux-2.4.20 is recommended.
Also try adding that index to SITE. That changes the query plan and might
mask the bug whatever it is.
> Best,
>
> --
> Richard F. Rebel
> [EMAIL PROTECTED]
> t. 212.239.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum statements per connection problem
John, - Original Message - From: ""John A. Sullivan III"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, June 08, 2003 7:58 AM Subject: Maximum statements per connection problem > I am having a nightmare of a time getting a single transaction across > multiple tables to work in Borland's Kylix3 with MySQL. It appears that > the Borland driver is saying that the MySQL database only supports a > single active statement per connection. > When I issue an explicit begin transaction, that appears to count as an > active statement. When I then try to issue the update (or insert or > delete) statement, that appears to be the second active command for the > existing connection. Since the Borland driver says the MySQL database > is only allowing a single active command per connection, it clones a new > connection to handle the update statement. Of course, this new > connection knows nothing of the transaction that was begun by the > original connection. This renders transaction support impossible with > Borland's dbExpress and MySQL (even with InnoDB tables). > Is there a way to configure MySQL to report that it will allow multiple > active statements per connection? Otherwise, I'm not sure what to do if > I want to use transactions (a must for my current project). Thanks. people have been complaining about this on Borland mailing lists for at least a year now. I saw a Borland employee reply there a couple a weeks ago that the MySQL transaction support is coming to dbExpress. Since Borland is marketing their own database Interbase, they have somewhat conflicting interests here. I believe feedback to Borland or their newsgroups can speed things up. > -- > John A. Sullivan III Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ > Chief Technology Officer > Nexus Management > +1 207-985-7880 > [EMAIL PROTECTED] > --- > If you are interested in helping to develop a GPL enterprise class > VPN/Firewall/Security device management console, please visit > http://iscs.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: start innodb without transactions
Carlos, - Original Message - From: ""Carlos Proal"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, June 08, 2003 2:14 PM Subject: Re: start innodb without transactions > > Hi Heikki et al. > I have solve my problem :). The fact about my missed table was my fault, > because indeed those tables doesnt exist anymore (one developer had deleted > them). ok, good :). There are no clear corruption reports from Solaris for almost 2 years now. > Even when my db is up and running im figuring out why there is not an option > to skip commit/rollback pending transactions ?, this way i would have a > quitely weekend, heheehe, no problem thanx a lot for your help. You definitely had some problem with log files because InnoDB was not able to scan them at all. Make sure innodb_log_files_in_group * innodb_log_file_size < 2G If you upgrade to 4.0.13, then the limit is 4G. To get the database to a consistent state, pending transactions have to be rolled back in crash recovery. If that is not possible because of corruption, you have to set innodb_force_recovery to: 3 (SRV_FORCE_NO_TRX_UNDO) do not run transaction rollbacks after recovery; and dump your tables and recreate the whole tablespace. > Carlos Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to install from source due to crash
Dennis, are you building on Red Hat 8.0? Our guess is that there is some bug in glibc-2.2.92 there. mysqld crashes just after thread creation on many Red Hat 8.0 computers if built from source on that computer. Workaround: use an official binary from www.mysql.com or build on Red Hat <= 7.3. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "Dennis van der Meer" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, June 01, 2003 4:04 PM Subject: Unable to install from source due to crash > >Description: > The file scripts/mysql_install_db crashes when trying to create the > databases. Since the system isn't configured MySQL cannot start > >How-To-Repeat: > Just a simple build from source will do the trick. There are no > compile errors and even the make install works without problems > >Fix: > Unknown > > >Submitter-Id: > >Originator: > >Organization: > >MySQL support: none > >Synopsis: Unable to install from source due to crash of mysql_install_db > >Severity: critical > >Priority: medium > >Category: mysql > >Class: support > >Release: mysql-4.0.13 (Source distribution) > > >C compiler:gcc (GCC) 3.2.2 > >C++ compiler: gcc (GCC) 3.2.2 > >Environment: > > System: Linux caveman 2.4.20-ac2 #14 Mon Apr 14 23:57:48 CEST 2003 i686 unknown > Architecture: i686 > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > /usr/bin/cc > GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/3.2.2/specs > Configured with: ../gcc-3.2.2/configure --prefix=/usr --enable-shared > --enable-threads=posix --enable-__cxa_atexit --disable-checking > --with-gnu-ld --verbose --target=i386-slackware-linux > --host=i386-slackware-linux > Thread model: posix > gcc version 3.2.2 > Compilation info: CC='gcc' CFLAGS='-O2' CXX='gcc' CXXFLAGS='-O2 > -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' > LIBC: > lrwxrwxrwx1 root root 13 Apr 13 19:44 /lib/libc.so.6 -> > libc-2.3.1.so > -rwxr-xr-x1 root root 1435624 Mar 5 06:57 /lib/libc-2.3.1.so > -rw-r--r--1 root root 2425490 Mar 5 06:57 /usr/lib/libc.a > -rw-r--r--1 root root 178 Mar 5 06:50 /usr/lib/libc.so > Configure command: ./configure '--prefix=/usr/local/mysql' > '--enable-assembler' '--with-mysqld-ldflags=-all-static' 'CFLAGS=-O2' > 'CXXFLAGS=-O2 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' > > >Full output from scripts/mysql_install_db > Preparing db table > Preparing host table > Preparing user table > Preparing func table > Preparing tables_priv table > Preparing columns_priv table > Installing all prepared tables > 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=8388600 > read_buffer_size=131072 > sort_buffer_size=2097144 > max_used_connections=0 > max_connections=100 > threads_connected=1 > It is possible that mysqld could use up to > key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = > 225791 K > bytes of memory > Hope that's ok; if not, decrease some variables in the equation. > > thd=0x83d8eb8 > 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=0xbf5fea48, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x8087af8 > 0x822811a > 0x8239057 > 0x8096d79 > 0x8098f69 > 0x8096b98 > 0x8092003 > 0x88e > 0x825cda4 > scripts/mysql_install_db: line 1: 31598 Segmentation > fault /usr/local/mysql/libexec/mysqld --bootstrap --skip-grant-tables > --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --skip-innodb > --skip-bdb > Installation of grant tables failed! > > Examine the logs in /usr/local/mysql/var for more information. > You can also try to start the mysqld daemon
Re: ADD CONSTRAINT on InnoDB tables
Marco, - Original Message - From: "Marco B" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, June 09, 2003 8:45 PM Subject: ADD CONSTRAINT on InnoDB tables > Hi all,=0D=0A=0D=0AI've just installed mysql 4.0.1 on my pc and I'm tryin= > g to migrate a set of old MyISAM table to InnoDB type table, to introduce= > referential integrity on my data.=0D=0A=0D=0AI found this problem. I cre= ... when I press 'Reply', your message gets scrambled with 0D and 0A codes, which are the carriage return and linefeed in Windows, I think :). Anyway, the answer to the question is that RESTRICT is the default if you do not specify an 'action'. That is why SHOW CREATE TABLE does not explicitly print the RESTRICT. > =0AMarco Barbato=0D=0A=0D=0A-----= Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Install on RH9
Alex, - Original Message - From: ""Alex Behrens"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 11, 2003 12:57 AM Subject: Install on RH9 > Hey All, > > Sorry I'm very new to the linux environment and I just recently installed > RH9 that comes bundled with Mysql 3.23 and I'm having troubles upgrading to > 4.0. > > When I try to install the latest client and server build it tells me I have > failed dependences for mod_auth_mysql, php_mysql, and perl_DBD_Mysql because > its missing libmysqlclient.so.10, what am I supposed to do to upgrade? I'm > very new to this and I need help. you have to install from http://www.mysql.com/downloads/mysql-4.0.html the rpm: "Dynamic client libraries (including 3.23.x libraries)" > Also, I'm not sure if Mysql is currently running and how to stop it to > perform an upgrade, etc. Please help! mysqladmin shutdown Look with ps -A that you do not have mysqld's running after that. > Thanks! > > -Alex "Big Al" Behrens Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ > E-mail: [EMAIL PROTECTED] > Urgent E-mail: [EMAIL PROTECTED] (Please be brief!) > Phone: 651-482-8779 > Cell: 651-329-4187 > Fax: 651-482-1391 > ICQ: 3969599 > Owner of the 3D-Unlimited Network: > http://www.3d-unlimited.com > Send News: > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock problem?
Hi! It is not a deadlock. These two transactions have been dangling uncommitted for 14 hours: > ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id > 98317 > 19 lock struct(s), heap size 2496, undo log entries 13 > MySQL thread id 9, query id 1213960 db2 10.10.7.2 web > Trx read view will not see trx with id >= 0 713900170, sees < 0 713875499 > ---TRANSACTION 0 713875499, ACTIVE 52484 sec, process no 17530, OS thread id > 155668 > 22 lock struct(s), heap size 2496, undo log entries 13 > MySQL thread id 27, query id 1214277 db2 10.10.7.2 web > Trx read view will not see trx with id >= 0 713888581, sees < 0 713875612 Remember to commit your transactions. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: "¶óÀϱ¸" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 11, 2003 4:42 PM Subject: deadlock problem? > dear all! > > I am running mysql 4.0.13 (Linux-X86) > my innodb table accasionally comes to a deadlock, > when i run some sql such a "delete from tb_memfee where no='206681'". > if it come to a deadlock, > i cannot update any column of the row - where no is '206681' in the > table tb_memfee. > > how can i solve the problem? please help. > thank you. > > # the following is a result of "show innodb status" when i met a deadlock. > > mysql> show innodb status; > = > 030611 9:43:50 INNODB MONITOR OUTPUT > = > Per second averages calculated from the last 25 seconds > -- > SEMAPHORES > -- > OS WAIT ARRAY INFO: reservation count 124502, signal count 123284 > Mutex spin waits 2765914, rounds 10463114, OS waits 34375 > RW-shared spins 144868, OS waits 71456; RW-excl spins 6603, OS waits 4664 > > TRANSACTIONS > > Trx id counter 0 714450041 > Purge done for trx's n:o < 0 713888592 undo n:o < 0 0 > Total number of lock structs in row lock hash table 33 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 714447966, not started, process no 12337, OS thread id > 794675 > MySQL thread id 12217, query id 1209966 db1 10.10.7.1 dbdba > > ... cut cut cut ... > > ---TRANSACTION 0 714440674, not started, process no 17431, OS thread id > 90124 > MySQL thread id 10, query id 1195297 db2 10.10.7.2 dbdba > ---TRANSACTION 0 714450019, ACTIVE 18 sec, process no 17756, OS thread id > 229405 starting index read > LOCK WAIT 2 lock struct(s), heap size 320 > MySQL thread id 65, query id 1214253 db2 10.10.7.2 web updating > delete from tb_memfee where no='206681' > --- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED: > RECORD LOCKS space id 0 page no 170373 n bits 152 table pay/tb_memfee index > PRIMARY trx id 0 71445001 > 9 lock_mode X waiting > Record lock, heap no 38 RECORD: info bits 32 0: len 10; hex > 32303030303036363831; asc 206681;; 1: > -- > ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id > 98317 > 19 lock struct(s), heap size 2496, undo log entries 13 > MySQL thread id 9, query id 1213960 db2 10.10.7.2 web > Trx read view will not see trx with id >= 0 713900170, sees < 0 713875499 > ---TRANSACTION 0 713875499, ACTIVE 52484 sec, process no 17530, OS thread id > 155668 > 22 lock struct(s), heap size 2496, undo log entries 13 > MySQL thread id 27, query id 1214277 db2 10.10.7.2 web > Trx read view will not see trx with id >= 0 713888581, sees < 0 713875612 > > FILE I/O > > I/O thread 0 state: waiting for i/o request > I/O thread 1 state: waiting for i/o request > I/O thread 2 state: waiting for i/o request > I/O thread 3 state: waiting for i/o request > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 1078482 OS file reads, 229960 OS file writes, 113777 OS fsyncs > 0.16 reads/s, 16384 avg bytes/read, 2.96 writes/s, 1.12 fsyncs/s > - > INSERT BUFFER AND ADAPTIVE HASH INDEX > - > Ibuf for space 0: size 1, free list len 4286, seg size 4288, > 36392 inserts, 36392 merged recs, 26899 merges > Hash table size 1593833, used cells 99888, node heap has 102 buffer(s) > 0.44 hash searches/s, 3.92 non-hash searches/s > --- > LOG > --- > Log sequence number 5 3656343850 > Log flushed up to 5 3656343850 > Last checkpoint at 5 3656343850 &g
Re: ON DELETE CASCADE ON UPDATE CASCADE
Vinita,
thank you for the bug report. The BLOB silently changes the CHAR(20) field
to a VARCHAR(20) field in te parent table. But the field in the child stays
CHAR(20).
There is a bug in ON UPDATE CASCADE which forgets to pad the field in the
child table with spaces. The child table becomes corrupt because a fixed
length field is shorter than it should be!
The fix will be in 4.0.14.
Thank you,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
- Original Message -
From: "vinita Vigine Murugiah" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Wednesday, June 11, 2003 9:03 AM
Subject: ON DELETE CASCADE ON UPDATE CASCADE
> HI I'm not sure whether it's a bug or my configuration problem??
>
> I have upgraded mysql from 3.23.53 to 4.0.12 so that the on update
> cascade will work! then the I got mysql crash when I did the update.
> Any idea??
>
> mysql> CREATE TABLE software (
> -> softwareID CHAR(20) NOT NULL,
> -> softwareName CHAR(100),
> -> softwareVers CHAR(20),
> -> installedDate DATE,
> -> softwareSource BLOB,
> -> PRIMARY KEY (softwareID)
> -> ) TYPE=INNODB;
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> CREATE TABLE software_machineOSs (
> -> softwareID CHAR(20) NOT NULL,
> -> osName CHAR(20) NOT NULL,
> -> osRevision CHAR(20),
> -> INDEX (softwareID),
> -> FOREIGN KEY (softwareID) REFERENCES software (softwareID)
> ON DELETE CASCADE ON UPDATE CASCADE,
> -> PRIMARY KEY (softwareID, osName)
> -> ) TYPE=INNODB;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into software (softwareID,softwareName,softwareVers)
> values ("test-1.1", "test", "1.1");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> insert into software_machineOSs (softwareID,osName,osRevision)
> values ("test-1.1", "win", "2002");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from software;
>
++--+--+---+
+
> | softwareID | softwareName | softwareVers | installedDate |
> softwareSource |
>
++--+--+---+
+
> | test-1.1 | test | 1.1 | NULL |
> NULL |
>
++--+--+---+
+
> 1 row in set (0.00 sec)
>
> mysql> select * from software_machineOSs;
> ++++
> | softwareID | osName | osRevision |
> ++++
> | test-1.1 | win| 2002 |
> ++++
> 1 row in set (0.00 sec)
>
> mysql> update software set softwareID="test-1.6" where
> softwareID="test-1.1";
> Query OK, 1 row affected (0.01 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select * from software;
>
++--+--+---+
+
> | softwareID | softwareName | softwareVers | installedDate |
> softwareSource |
>
++--+--+---+
+
> | test-1.6 | test | 1.1 | NULL |
> NULL |
>
++--+--+---+
+
> 1 row in set (0.00 sec)
>
> mysql> select * from software_machineOSs;
> ERROR 2013: Lost connection to MySQL server during query
> mysql>
>
> ** mungah.cs.mu.OZ.AU.err
>
> 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=16777216
> read_buffer_size=131072
> sort_buffer_size=524280
> max_used_connections=1
> max_connections=100
> threads_connected=1
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
> = 80383 K
> bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> 030611 13:59:39 mysqld restarted
> 030611 13:59:39 InnoDB: Database was not shut down normally.
> InnoDB: Starting recovery from log
Re: Can't delete on cascade
Miguel, if you are running MySQL-4.0.13, please use SHOW INNODB STATUS to look at a detailed error message of the latest foreign key error. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Miguel Perez"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, June 15, 2003 12:27 AM Subject: Can't delete on cascade > Hi every one does anyone know if there are bugs on the statement ON DELETE > CASCADE, cause I have 2 tables as follows: > > Reparacion |CREATE TABLE `Reparacion` ( > `folio` bigint(19) NOT NULL default '0', > `idTipo` int(4) NOT NULL default '0', > `idAlmacen` int(4) NOT NULL default '0', > `idStatusGeneral` int(2) NOT NULL default '0', > `usuario` varchar(16) NOT NULL default '', > `idProducto` int(11) NOT NULL default '0', > `fechaEntrada` date NOT NULL default '-00-00', > `observacion` varchar(255) default NULL, > `tecnico` varchar(100) NOT NULL default '', > `numSerie` varchar(20) NOT NULL default '', > `idCausaCancelacion` int(4) NOT NULL default '0', > PRIMARY KEY (`folio`,`idTipo`), > KEY `IDX4018` (`idAlmacen`), > KEY `IDX3309` (`idTipo`), > KEY `IDX3938` (`idStatusGeneral`), > KEY `IDX5631` (`usuario`), > KEY `IDX5623` (`idProducto`), > KEY `IDX1475` (`idCausaCancelacion`), > FOREIGN KEY (`idCausaCancelacion`) REFERENCES > `refacciones.CausaCancelacion` (`idCausaCancelacion`) ON DELETE CASCADE, > FOREIGN KEY (`idTipo`) REFERENCES `refacciones.Tipo` (`idTipo`), > FOREIGN KEY (`idAlmacen`) REFERENCES `refacciones.Almacen` (`idAlmacen`), > FOREIGN KEY (`idStatusGeneral`) REFERENCES `refacciones.StatusGeneral` > (`idStatusGeneral`), > FOREIGN KEY (`usuario`) REFERENCES `refacciones.Empleado` (`usuario`), > FOREIGN KEY (`idProducto`) REFERENCES `refacciones.Producto` > (`idProducto`) > ) TYPE=InnoDB COMMENT='Reparacion' > > RefaccionReparacion | CREATE TABLE `RefaccionReparacion` ( > `folio` bigint(19) NOT NULL default '0', > `idTipo` int(4) NOT NULL default '0', > `idRefaccion` int(11) NOT NULL default '0', > `cantidad` int(11) NOT NULL default '0', > `cantidadSolicitada` int(11) NOT NULL default '0', > PRIMARY KEY (`folio`,`idTipo`,`idRefaccion`), > KEY `IDX2530` (`folio`), > KEY `IDX3220` (`idTipo`), > KEY `IDX4101` (`idRefaccion`), > FOREIGN KEY (`folio`) REFERENCES `refacciones.Reparacion` (`folio`) ON > DELETE CASCADE, > FOREIGN KEY (`idTipo`) REFERENCES `refacciones.Tipo` (`idTipo`) ON DELETE > CASCADE, > FOREIGN KEY (`idRefaccion`) REFERENCES `refacciones.Refaccion` > (`idRefaccion`) ON DELETE CASCADE > ) TYPE=InnoDB COMMENT='Reparacion' > > > In the child table, I have the on delete cascade, but it doesn't work comes > out the next error: > "Cannot delete a parent row: a foreign key constraint fails" > > Any ideas. > > Thnx in advanced. > > Greetings > > _ > Charla con tus amigos en línea mediante MSN Messenger: > http://messenger.microsoft.com/es > > > -- > 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: free table memory shrinks when data deleted
Ethan, - Original Message - From: "Ethan Joffe" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, June 15, 2003 6:16 PM Subject: free table memory shrinks when data deleted > > After delete a large amount of data from an innodb table, the free memory > available to innodb tables as reported by 'show table status' decreased > significantly rather than increasing. did you wait for purge to clean up the undo log as well as the delete-marked rows? The undo log takes some space. That is why during a big delete the free space decreases until the delete transaction commits. > I am guessing this is caused by fragmentation? If the deleted rows are scattered then it is possible that no space is freed from index trees. But the free space should not decrease. Did you follow the table's data and index size in SHOW TABLE STATUS. > If so, I am guessing the > only way to regain the free memory is to dump the data an recreate the > table, but the table is several gigs so this would be seriously > inconvenient now, and pretty much impossible in the future. > > So my question is whether the free memory is actually lost or just not > reported correctly by 'show table status', and if lost, how can I regain it > short of dumping and recreating the table. > > Thanks > Ethan Joffe > CTO Nami Media Inc. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1005: Can't create table './db/table.frm' (errno: 150)
Ben, please print with SHOW INNODB STATUS what kind of foreign key error InnoDB complains about in the import. Can you produce a repeatable test case with table dumps that shows the error? Are you sure you are importing all the tables within the same connection where you have set foreign_key_checks=0? Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html . Subject: ERROR 1005: Can't create table './db/table.frm' (errno: 150) From: Ben Clewett Date: Mon, 16 Jun 2003 10:53:28 + Dear MySQL: I am getting the error 'ERROR 1005: Can't create table './db/table.frm' (errno: 150)' using InnoDB tables on 4.0.13-max-log. This is nothing to do with referential checking. The command: SET foreign_key_checks=0 Does alow the import of referential tables, but not all tables. Some still give a '150'. Generally the only consistent way I have found to avoid this is to drop all my tables and start again. But this is not practicle as I keep loosing all my data! This is a problem I have come accross with InnoDB since first using these. I know I am not alone with this problem from the number of hits from a popular search engine from this error code. None of which suggest working fix's. 1. Can somebody please tell me a consistent way of avoiding and dealing with this problem. 2. Can somebody please tell me when this will be fixed for good. 3. Can somebody please adjust the error messages so that the newbe has a fighting change of understanding what the hell is going on. Many thanks, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.13 Memory leakage?
Michael, in the case the memory leak would be in InnoDB, you can monitor its memory allocation with SHOW INNODB STATUS\G " Total memory allocated 50738427; in additional pool allocated 1762432 " Regards, Heikki - Original Message - From: "Michael Loftis" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, June 17, 2003 2:10 AM Subject: Re: MySQL 4.0.13 Memory leakage? > With editing removing any passwords or sensitive stuff. (paths is all) > > # Example mysql config file. > # You can copy this to one of: > # /usr/local/etc/my.cnf to set global options, > # mysql-data-dir/my.cnf to set server-specific options (in this > # installation this directory is /usr/local/var) or > # ~/.my.cnf to set user-specific options. > # > # One can use all long options that the program supports. > # Run the program with --help to get a list of available options > > # This will be passed to all mysql clients > [client] > port= 3306 > socket = /var/lib/mysql/mysql.sock > > # Here is entries for some specific programs > # The following values assume you have at least 32M ram > > # The MySQL server > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > datadir = /var/lib/mysql > enable-locking > > safe-show-database > > set-variable= wait_timeout=300 > set-variable= max_connections=250 > set-variable= max_user_connections=20 > set-variable= key_buffer=384M > set-variable= max_allowed_packet=4M > set-variable= table_cache=512 > set-variable= sort_buffer=2M > set-variable= record_buffer=2M > set-variable= thread_cache=8 > # Try number of CPU's*2 for thread_concurrency > set-variable= thread_concurrency=4 > set-variable= myisam_sort_buffer_size=64M > set-variable= ft_min_word_len=3 > > # Try some tuning with query caches. > set-variable= query_cache_size=64M > > # Start logging > log-bin > > server-id = 1 > > #log-slave-updates > > # Uncomment the following if you are using BDB tables > set-variable = bdb_cache_size=64M > set-variable = bdb_max_lock=10 > > # Uncomment the following if you are using Innobase tables > innodb_data_file_path = ibdata1:128M;ibdata2:128M > innodb_data_home_dir = /var/lib/mysql/ > innodb_log_group_home_dir = /data/mysql/ > innodb_log_arch_dir = /var/lib/mysql/ > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=5M > set-variable = innodb_log_buffer_size=8M > innodb_flush_log_at_trx_commit=1 > innodb_log_archive=0 > set-variable = innodb_buffer_pool_size=16M > set-variable = innodb_additional_mem_pool_size=2M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=50 > > > [mysqldump] > quick > set-variable= max_allowed_packet=16M > > [mysql] > no-auto-rehash > > [isamchk] > set-variable= key_buffer=256M > set-variable= sort_buffer=256M > set-variable= read_buffer=2M > set-variable= write_buffer=2M > > [myisamchk] > set-variable= key_buffer=256M > set-variable= sort_buffer=256M > set-variable= read_buffer=2M > set-variable= write_buffer=2M > > [mysqlhotcopy] > interactive-timeout > > > --On Monday, June 16, 2003 16:02 -0700 Jeremy Zawodny <[EMAIL PROTECTED]> > wrote: > > > On Mon, Jun 16, 2003 at 04:41:49PM -0600, Michael Loftis wrote: > >> I'm noticing that our MySQL 4.0.13 system is probably leaking RAM > >> (uptime ~10 days) > >> > >> ... > >> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND > >> 26046 mysql 9 0 548M 162M 44008 S 5.9 8.0 0:16 mysqld-max > >> ... > >> > >> And it just keeps growing. Even with our admittedly aggressive > >> cache settings it should have stopped a growing several days ago. > >> All processes are now at or about those memory stats. > >> > >> Any ideas? Need any more info? > > > > Without seeing your my.cnf file, it's difficult to say. > > > > Care to post it? > > -- > > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > > > MySQL 4.0.13: up 13 days, processed 440,106,660 queries (372/sec. avg) > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > Michael Loftis > Modwest Sr. Systems Administrator > Powerful, Affordable Web Hosting > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb error
John,
what Linux kernel version you are running? Did the first crash occur because
of the same assertion failure?
The InnoDB tablespace is probably corrupt and it asserts in purge or insert
buffer merge.
You can try starting with
innodb_force_recovery=4
in the [mysqld] section of your my.cnf. You can try to determine how
widespread the corruption is by running CHECK TABLE ... on your tables.
Then dump your tables and recreate the whole tablespace.
MySQL-4.0.14 will have the page checksum fixed. Then we will get more
information whether this kind of corruption originates in the file system.
It might also be an ordinary bug in InnoDB. It asserts in copying of records
to another page because a record does not fit though we have calculated it
should fit.
I have now added diagnostic code to 4.0.14 which will print hex dumps of the
pages if the assertion fails again.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
...
/* Copy records from the original page to the new page */
sup = page_get_supremum_rec(page);
while (sup != page_cur_get_rec(&cur1)) {
ut_a(
page_cur_rec_insert(&cur2, page_cur_get_rec(&cur1), mtr));
page_cur_move_to_next(&cur1);
page_cur_move_to_next(&cur2);
}
...
- Original Message -
From: "John Smith" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, June 19, 2003 12:21 AM
Subject: Innodb error
> I'm getting this error in my log:
>
> --
> 030618 15:08:24 mysqld started
> 030618 15:08:24 InnoDB: Started
> 030618 15:08:24 /usr/sbin/mysqld: Can't create/write
> to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
> /usr/sbin/mysqld: ready for connections.
> Version: '4.0.12' socket: '/var/lib/mysql/mysql.sock'
> port: 3306
> 030618 15:08:26 InnoDB: Assertion failure in thread
> 28680 in file page0page.c line 450
> InnoDB: We intentionally generate a memory trap.
> InnoDB: Send a detailed bug report to
> [EMAIL PROTECTED]
> 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=67108864
> read_buffer_size=1044480
> 030618 15:08:26 mysqld ended
> --
>
> Prior to this, I was doing 'mysqldump -A ...' and
> mysql crashed. After rebooting the server I can get
> mysql running again, but most queries show 'connection
> lost', 'mysql gone away', etc. errors - but usually
> the queries work after the client automatically
> reconnects. 'mysqlcheck [-r] ...' dies with
> 'connection lost'.
>
> Please help!
>
> TIA,
> John
>
> __
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.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: Innodb error
John,
actually, you should first try
innodb_force_recovery=2
and run CHECK TABLEs.
Regards,
Heikki
- Original Message -
From: "Heikki Tuuri" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, June 19, 2003 1:25 AM
Subject: Re: Innodb error
> John,
>
> what Linux kernel version you are running? Did the first crash occur
because
> of the same assertion failure?
>
> The InnoDB tablespace is probably corrupt and it asserts in purge or
insert
> buffer merge.
>
> You can try starting with
>
> innodb_force_recovery=4
>
> in the [mysqld] section of your my.cnf. You can try to determine how
> widespread the corruption is by running CHECK TABLE ... on your tables.
>
> Then dump your tables and recreate the whole tablespace.
>
> MySQL-4.0.14 will have the page checksum fixed. Then we will get more
> information whether this kind of corruption originates in the file system.
>
> It might also be an ordinary bug in InnoDB. It asserts in copying of
records
> to another page because a record does not fit though we have calculated it
> should fit.
>
> I have now added diagnostic code to 4.0.14 which will print hex dumps of
the
> pages if the assertion fails again.
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> http://www.innodb.com
> Transactions, foreign keys, and a hot backup tool for MySQL
> Order MySQL technical support from https://order.mysql.com/
>
> ...
> /* Copy records from the original page to the new page */
>
> sup = page_get_supremum_rec(page);
>
> while (sup != page_cur_get_rec(&cur1)) {
> ut_a(
> page_cur_rec_insert(&cur2, page_cur_get_rec(&cur1), mtr));
>
> page_cur_move_to_next(&cur1);
> page_cur_move_to_next(&cur2);
> }
> ...
> - Original Message -
> From: "John Smith" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Thursday, June 19, 2003 12:21 AM
> Subject: Innodb error
>
>
> > I'm getting this error in my log:
> >
> > --
> > 030618 15:08:24 mysqld started
> > 030618 15:08:24 InnoDB: Started
> > 030618 15:08:24 /usr/sbin/mysqld: Can't create/write
> > to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
> > /usr/sbin/mysqld: ready for connections.
> > Version: '4.0.12' socket: '/var/lib/mysql/mysql.sock'
> > port: 3306
> > 030618 15:08:26 InnoDB: Assertion failure in thread
> > 28680 in file page0page.c line 450
> > InnoDB: We intentionally generate a memory trap.
> > InnoDB: Send a detailed bug report to
> > [EMAIL PROTECTED]
> > 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=67108864
> > read_buffer_size=1044480
> > 030618 15:08:26 mysqld ended
> > --
> >
> > Prior to this, I was doing 'mysqldump -A ...' and
> > mysql crashed. After rebooting the server I can get
> > mysql running again, but most queries show 'connection
> > lost', 'mysql gone away', etc. errors - but usually
> > the queries work after the client automatically
> > reconnects. 'mysqlcheck [-r] ...' dies with
> > 'connection lost'.
> >
> > Please help!
> >
> > TIA,
> > John
> >
> > __
> > Do you Yahoo!?
> > SBC Yahoo! DSL - Now only $29.95 per month!
> > http://sbc.yahoo.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-3.23.57 is released
Hi! InnoDB is a MySQL table type which supports transactions, foreign keys, and a non-free hot backup tool. InnoDB is included in MySQL-Max-3.23 and in all downloads of MySQL-4.0 and MySQL-4.1. You can download all of these from http://www.mysql.com. 3.23.57 is a bugfix release of the old stable 3.23 branch. For production use I consider 4.0.13 already better than 3.23 branch versions. See http://www.innodb.com/ibman.html. Changelog: * Fixed a bug: InnoDB forgot to call pthread_mutex_destroy() when a table was dropped. That could cause memory leakage on FreeBSD and other non-Linux Unixes. * Fixed a bug: MySQL could erroneously return 'Empty set' if InnoDB estimated an index range size to 0 records though the range was not empty; MySQL also failed to do the next-key locking in the case of an empty index range. * Fixed a bug: GROUP BY and DISTINCT could treat NULL values inequal. The following bug fixes did not make it to 3.23.57 but will appear in 3.23.58: * An outstanding bug: InnoDB could make the index page directory corrupt in the first B-tree page splits after mysqld startup. A symptom would be an assertion in page0page.c, in function page_dir_find_slot(). * An outstanding bug: InnoDB could in rare cases return an extraneous row if a rollback, purge, and a SELECT coincided. 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-3.23.57 is released
Hi! The following essential change was forgotten from the changelog of 3.23.57 below, though it is listed in the MySQL-3.23.57 changelog: * Changed the default value of innodb_flush_log_at_trx_commit from 0 to 1. If you have not specified it explicitly in your my.cnf, and your application runs much slower with this new release, it is because the value 1 causes a log flush to disk at each transaction commit. Best regards, Heikki - Original Message - From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, June 20, 2003 10:49 AM Subject: MySQL/InnoDB-3.23.57 is released > Hi! > > InnoDB is a MySQL table type which supports transactions, foreign keys, and > a non-free hot backup tool. InnoDB is included in MySQL-Max-3.23 and in all > downloads of MySQL-4.0 and MySQL-4.1. You can download all of these from > http://www.mysql.com. > > 3.23.57 is a bugfix release of the old stable 3.23 branch. For production > use I consider 4.0.13 already better than 3.23 branch versions. See > http://www.innodb.com/ibman.html. > > Changelog: > > * Fixed a bug: InnoDB forgot to call pthread_mutex_destroy() when a table > was dropped. That could cause memory leakage on FreeBSD and other non-Linux > Unixes. > > * Fixed a bug: MySQL could erroneously return 'Empty set' if InnoDB > estimated an index range size to 0 records though the range was not empty; > MySQL also failed to do the next-key locking in the case of an empty index > range. > > * Fixed a bug: GROUP BY and DISTINCT could treat NULL values inequal. > > > The following bug fixes did not make it to 3.23.57 but will appear in > 3.23.58: > > * An outstanding bug: InnoDB could make the index page directory corrupt in > the first B-tree page splits after mysqld startup. A symptom would be an > assertion in page0page.c, in function page_dir_find_slot(). > > * An outstanding bug: InnoDB could in rare cases return an extraneous row if > a rollback, purge, and a SELECT coincided. > > 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: InnoDB file...
I.-A., - Original Message - From: ""I-A.Kotopoulos"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, June 25, 2003 10:05 AM Subject: InnoDB file... > --=_NextPart_000_00B5_01C33B01.642AD2C0 > Content-Type: text/plain; > charset="Windows-1252" > Content-Transfer-Encoding: quoted-printable > > When should I use the autoextend option for the InnoDB file...Even after = > emptying the tables the files keeps its size and for new insertions it = > gets even bigger instead of using the already allocated space(which I = > suppose should be available after emptyibg the tables). yes, it should free the space if you empty the table. The size of ibdata1 will stay the same, but other tables can use the freed space. Are you sure you do not have long-running transactions dangling? Use SHOW INNODB STATUS\G to print a list of transactions. The purge operation cannot remove delete-marked rows if there are old transactions which could still see them. > thank you friends > aik_b > > p.s. MySQL doesn't seem to do a good usage of the disk space and the = > InnoDB file size and its density..anyway Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB table recovery problem - not beeing able to start database server
Ivan, if you have already dumped all InnoDB tables, then there is no need to keep ibdata files and ib_logfiles. You can delete them and recreate them from scratch and reimport the tables. But in the printout which you pasted below you do not have innodb_force_recovery set to 4? When I test 4.0.14, I get a printout which tells the option is set to 4: " C:\mysql-4.0.13\mysql-4.0.13\client_debug>mysqld --console 030630 16:49:40 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 16 1350394126 InnoDB: Doing recovery: scanned up to log sequence number 16 1350394126 030630 16:49:41 InnoDB: Flushing modified pages from the buffer pool... 030630 16:49:41 InnoDB: Started InnoDB: !!! innodb_force_recovery is set to 4 !!! mysqld: ready for connections " Note that innodb_force_recovery only helps you to dump your tables. I have intentionally blocked inserts and updates if innodb_force_recovery > 0, because it is not a good idea to use a corrupt database. If the disk or the Windows OS lies to InnoDB about when it really has written a page to disk, you can easily get this kind of corruption at a power outage. The free page bitmap can be out-of-sync with other data structures in the tablespace. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html Subject: InnoDB table recovery problem - not beeing able to start database server From: Ivan Tomasic Date: Mon, 30 Jun 2003 12:10:08 +0200 Hi. Please help. During long delete operation from one InnoDB table there was PC shutdown due to power supply failure. After that I was unable to start database server so I have started it with set-variable = innodb force recovery=4 option and I have dropped all databases with InnoDB tables. Still I am unable to start database server normally. Following is the part of Err file: 030630 11:58:36 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3895685648 InnoDB: Doing recovery: scanned up to log sequence number 0 3895685632 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1777810 row operations to undo InnoDB: Trx id counter is 0 22060800 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 22060495, 1777810 rows to undo InnoDB: Progress in percents: 1InnoDB: Dump of the tablespace extent descriptor: len 40; hex 00950004aa aafeff; asc ...Ľ¬¬¬¬¬¬¬¬¬¬¬¬â- ...; InnoDB: Serious error! InnoDB is trying to free page 32306 InnoDB: though it is already marked as free in the tablespace! InnoDB: The tablespace free space info is corrupt. InnoDB: You may need to dump your InnoDB tables and recreate the whole InnoDB: database! InnoDB: If the InnoDB recovery crashes here, see section 6.1 InnoDB: of http://www.innodb.com/ibman.html about forcing recovery. 030630 11:58:37 InnoDB: Assertion failure in thread 580 in file D:\mysql-4.0.12 \innobase\fsp\fsp0fsp.c line 2689 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 030630 11:58:37 mysqld-max-nt: Got signal 11. Aborting! 030630 11:58:37 Aborting 030630 11:58:37 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 030630 11:58:37 mysqld-max-nt: Shutdown Complete Please help B.Sc.E.E Ivan Tomasic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete from InnoDB
Taylor, SHOW TABLE STATUS reports an estimate of the number of rows. What do you mean that you cannot access any data from the table? All SELECTs hang? What OS, what MySQL version? What does SHOW INNODB STATUS\G print? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Taylor Lewick"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, July 01, 2003 10:32 PM Subject: Delete from InnoDB > --=_154B26A2.4A2A23E3 > Content-Type: text/plain; charset=US-ASCII > Content-Transfer-Encoding: quoted-printable > > Hi all. I did a delete query on an InnoDB table where items were older t= > han a certain date. > That worked fine, and it told me that some 30,000 objects had been delete= > d. > > i did a commit, and then decided to delete a few more by changing the dat= > e parameter. > Now the query just hangs, and when I do a show table status, the number o= > f rows constantly changes even after I killed off the query and did more = > commits. =20 > > My question is what happened and what can I do to fix this? Right now I = > can't access any data from this table. > > Thanks, > Taylor > > > =09 Please Note > The information in this E-mail message is legally privileged > and confidential information intended only for the use of the > individual(s) named above. If you, the reader of this message, > are not the intended recipient, you are hereby notified that=20 > you should not further disseminate, distribute, or forward this > E-mail message. If you have received this E-mail in error, > please notify the sender. Thank you > * > > --=_154B26A2.4A2A23E3-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete from InnoDB
Taylor, you are running a very old alpha version of MySQL. Lots of bugs have been fixed since. Please upgrade to 4.0.13, or if you use replication, then 4.0.12 is a safer choice. Regards, Heikki - Original Message - From: "Taylor Lewick" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, July 01, 2003 11:04 PM Subject: Re: Delete from InnoDB Heikki, thanks for the quick response. I am on HP-UX 11.00 with MySQL version 4.0.1 Yes all selects hang. On other tables they work. When I used to do a select count(*) from this_table I would get about a 3 second response and 160,000 as the count. Now it just hangs. I tried to do show innodb status and I received a syntax error. Taylor Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013:Lost connection to MySQL server
Miguel, what exact MySQL version you are running? Have you created FOREIGN KEY constraints to those tables? Have you downgraded to a lower MySQL version? Regards, Heikki - Original Message - From: ""Miguel Perez"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, July 02, 2003 12:11 AM Subject: Re: ERROR 2013:Lost connection to MySQL server > The OS I'm using is Red Hat 7.3, and the mysql version is 4.0 > > The wierd thing is that I could access those tables but just after I > rebooted my PC > I couldn't access the 2 tables. > The log files has the following: > > "030701 14:27:38 InnoDB: Assertion failure in thread 36874 in file > dict0load.c line 677 > InnoDB: We intentionally generate a memory trap. > InnoDB: Send a detailed bug report to [EMAIL PROTECTED] > 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. thd=0x8711eb8 > 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=0xbfe7e0c8, backtrace may not be correct. > Stack range sanity check OK, backtrace follows: > 0x80741ea > 0x8286058 > 0x81193b5 > 0x8108df8 > 0x80cf9c9 > 0x80c9bf4 > 0x8096a76 > 0x809262b > 0x8091ccf > 0x8092989 > 0x80e1d18 > 0x807fdd6 > 0x8082638 > 0x807dd5d > 0x8083b6e > 0x807cf0f > 0x828380c 0x82b6f1a > New value of fp=(nil) failed sanity check, terminating stack trace! > Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow > instructions on how to resolve the stack trace. Resolved > stack trace is much more helpful in diagnosing the problem, so please do > resolve it > Trying to get some variables. > Some pointers may be invalid and cause the dump to abort... > thd->query at 0x87170a8 = show create table PEDIDOS > thd->thread_id=1 > " > > I have 500MB in ram. > > Greetings > > >From: Fred van Engen <[EMAIL PROTECTED]> > >To: Miguel Perez <[EMAIL PROTECTED]> > >CC: [EMAIL PROTECTED] > >Subject: Re: ERROR 2013:Lost connection to MySQL server > >Date: Tue, 1 Jul 2003 21:23:41 +0200 > > > >Hi, > > > >On Tue, Jul 01, 2003 at 02:13:23PM -0500, Miguel Perez wrote: > > > I have the following problem, when I try to connect to my DB servicios > > > I get connected succesfully, but when I try to access certain tables I > >get > > > the error > > > "ERROR 2013: Lost connection to MySQL server during query" but only with > > > those certain tables, to be more specific there's only 2 tables that i > > > can't access, I get that error, > > > the other ones works fine. > > > > > > >Which version, which OS? > > > > > > > Any ideas or suggestions. > > > > > > >Maybe mysqld crashes? It restarts automatically, on unices at least. > > > >Check the mysql error log to find out if it crashed. > > > > > >Regards, > > > >Fred. > > > >-- > >Fred van Engen XB Networks B.V. > >email: [EMAIL PROTECTED]Televisieweg 2 > >tel: +31 36 5462400 1322 AC Almere > >fax: +31 36 5462424 The Netherlands > > _ > Charla con tus amigos en línea mediante MSN Messenger: > http://messenger.microsoft.com/es > > > -- > 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: ERROR 2013:Lost connection to MySQL server
Miguel, the failing assertion is in dict_load_table(): ut_a(DB_SUCCESS == dict_load_foreigns(table->name)); Are you sure you do not have foreign keys which REFERENCE those tables? 4.0.13 has better diagnostics. Please upgrade to it if you are not using MySQL replication. " Fixed a bug: if you dropped a table to which there was a FOREIGN KEY reference, and later created the same table with non-matching column types, InnoDB could assert in dict0load.c, in function dict_load_table. " Regards, Heikki - Original Message - From: "Miguel Perez" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, July 02, 2003 12:47 AM Subject: Re: ERROR 2013:Lost connection to MySQL server > I am running the 4.0.12 version > For those tables I don't have FOREIGN KEYS here is the table desc. > > CREATE TABLE CLIENTE ( > FINGCIOID smallint(6) NOT NULL default '0', > FINOTIENDA smallint(6) NOT NULL default '0', > FICTEID int(11) NOT NULL default '0', > FIDIGITOVER smallint(6) NOT NULL default '0', > FIOCUID smallint(6) NOT NULL default '0', > FCCTENOMBRE char(18) NOT NULL default '', > FCCTEAPATERNO char(18) NOT NULL default '', > FCCTEAMATERNO char(18) NOT NULL default '', > FCCTEDIRCALLE char(25) NOT NULL default '', > FCCTEDIRNOEXT char(5) NOT NULL default '', > FCCTEDIRNOINT char(5) NOT NULL default '', > FCCTECP char(5) NOT NULL default '', > FCCTECOLONIA char(65) NOT NULL default '', > FCCTESEXO char(1) NOT NULL default '', > FCCTETEL char(15) NOT NULL default '', > FCCTERFC char(14) NOT NULL default '', > FCCTEFISMOR char(2) NOT NULL default '', > FITDAATIENDE smallint(6) NOT NULL default '0', > FDCTEFECALT datetime NOT NULL default '-00-00 00:00:00', > FLCTEECO smallint(6) NOT NULL default '0', > FIEDOCIVIL smallint(6) NOT NULL default '0', > FNLINEACRED decimal(8,2) NOT NULL default '0.00', > FDCTEFECNAC datetime NOT NULL default '-00-00 00:00:00', > FCNOCOBRADOR char(6) NOT NULL default '', > FITRANNO int(11) NOT NULL default '0', > KEY cliente (FCCTENOMBRE,FCCTEAPATERNO,FCCTEAMATERNO,FINOTIENDA) > ) TYPE=InnoDB; > > CREATE TABLE PEDIDO ( > FISUCURSAL int(11) NOT NULL default '0', > FINOPEDIDO int(11) NOT NULL default '0', > FINGCIOID smallint(6) NOT NULL default '0', > FICTEID int(11) NOT NULL default '0', > FINOTIENDA smallint(6) NOT NULL default '0', > FIDIGITOVER smallint(6) NOT NULL default '0', > FCEMPNO char(6) NOT NULL default '', > FITIPOVENTA smallint(6) NOT NULL default '0', > FDPEDFEC datetime NOT NULL default '-00-00 00:00:00', > FNPEDTOTAL decimal(12,2) NOT NULL default '0.00', > FIPEDFACT int(11) NOT NULL default '0', > FDFECFACT datetime NOT NULL default '-00-00 00:00:00', > FIPEDSTAT smallint(6) NOT NULL default '0', > FCUSERID char(10) NOT NULL default '', > FCWS char(10) NOT NULL default '', > FDFECSURT datetime NOT NULL default '-00-00 00:00:00', > FNPEDSALDO decimal(12,2) NOT NULL default '0.00', > FCFOLIOPARAM int(11) NOT NULL default '0', > FDFECIMPFACTURA datetime NOT NULL default '-00-00 00:00:00', > FIDIASSERV smallint(6) NOT NULL default '0' > ) TYPE=InnoDB; > > And I haven't tried to downgrade my version. > > Greetings > > > > >From: "Heikki Tuuri" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Subject: Re: ERROR 2013:Lost connection to MySQL server > >Date: Wed, 2 Jul 2003 00:35:09 +0300 > > > >Miguel, > > > >what exact MySQL version you are running? > > > >Have you created FOREIGN KEY constraints to those tables? > > > >Have you downgraded to a lower MySQL version? > > > >Regards, > > > >Heikki > > > >- Original Message - > >From: ""Miguel Perez"" <[EMAIL PROTECTED]> > >Newsgroups: mailing.database.mysql > >Sent: Wednesday, July 02, 2003 12:11 AM > >Subject: Re: ERROR 2013:Lost connection to MySQL server > > > > > > > The OS I'm using is Red Hat 7.3, and the mysql version is 4.0 > > > > > > The wierd thing is that I could access those tables but just after I > > > rebooted my PC > > > I couldn't
Re: ERROR 2013:Lost connection to MySQL server
Miguel, best to upgrade to 4.0.13 because it has better diagnostics. Regards, Heikki - Original Message - From: "Miguel Perez" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, July 02, 2003 1:03 AM Subject: Re: ERROR 2013:Lost connection to MySQL server > > Heikki: > > The only thing that I did with those tables is that I renamed both of them. > > And I didn't drop anything. Do you think that the renamed thing did affect > my tables?.. > > Greetings > > >From: "Heikki Tuuri" <[EMAIL PROTECTED]> > >To: "Miguel Perez" <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]> > >Subject: Re: ERROR 2013:Lost connection to MySQL server > >Date: Wed, 2 Jul 2003 00:55:38 +0300 > > > >Miguel, > > > >the failing assertion is in dict_load_table(): > > > > ut_a(DB_SUCCESS == dict_load_foreigns(table->name)); > > > >Are you sure you do not have foreign keys which REFERENCE those tables? > > > >4.0.13 has better diagnostics. Please upgrade to it if you are not using > >MySQL replication. > > > >" > >Fixed a bug: if you dropped a table to which there was a FOREIGN KEY > >reference, and later created the same table with non-matching > >column types, InnoDB could assert in dict0load.c, in function > >dict_load_table. > >" > >Regards, > > > >Heikki > > > >- Original Message - > >From: "Miguel Perez" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > >Sent: Wednesday, July 02, 2003 12:47 AM > >Subject: Re: ERROR 2013:Lost connection to MySQL server > > > > > > > I am running the 4.0.12 version > > > For those tables I don't have FOREIGN KEYS here is the table desc. > > > > > > CREATE TABLE CLIENTE ( > > > FINGCIOID smallint(6) NOT NULL default '0', > > > FINOTIENDA smallint(6) NOT NULL default '0', > > > FICTEID int(11) NOT NULL default '0', > > > FIDIGITOVER smallint(6) NOT NULL default '0', > > > FIOCUID smallint(6) NOT NULL default '0', > > > FCCTENOMBRE char(18) NOT NULL default '', > > > FCCTEAPATERNO char(18) NOT NULL default '', > > > FCCTEAMATERNO char(18) NOT NULL default '', > > > FCCTEDIRCALLE char(25) NOT NULL default '', > > > FCCTEDIRNOEXT char(5) NOT NULL default '', > > > FCCTEDIRNOINT char(5) NOT NULL default '', > > > FCCTECP char(5) NOT NULL default '', > > > FCCTECOLONIA char(65) NOT NULL default '', > > > FCCTESEXO char(1) NOT NULL default '', > > > FCCTETEL char(15) NOT NULL default '', > > > FCCTERFC char(14) NOT NULL default '', > > > FCCTEFISMOR char(2) NOT NULL default '', > > > FITDAATIENDE smallint(6) NOT NULL default '0', > > > FDCTEFECALT datetime NOT NULL default '-00-00 00:00:00', > > > FLCTEECO smallint(6) NOT NULL default '0', > > > FIEDOCIVIL smallint(6) NOT NULL default '0', > > > FNLINEACRED decimal(8,2) NOT NULL default '0.00', > > > FDCTEFECNAC datetime NOT NULL default '-00-00 00:00:00', > > > FCNOCOBRADOR char(6) NOT NULL default '', > > > FITRANNO int(11) NOT NULL default '0', > > > KEY cliente (FCCTENOMBRE,FCCTEAPATERNO,FCCTEAMATERNO,FINOTIENDA) > > > ) TYPE=InnoDB; > > > > > > CREATE TABLE PEDIDO ( > > > FISUCURSAL int(11) NOT NULL default '0', > > > FINOPEDIDO int(11) NOT NULL default '0', > > > FINGCIOID smallint(6) NOT NULL default '0', > > > FICTEID int(11) NOT NULL default '0', > > > FINOTIENDA smallint(6) NOT NULL default '0', > > > FIDIGITOVER smallint(6) NOT NULL default '0', > > > FCEMPNO char(6) NOT NULL default '', > > > FITIPOVENTA smallint(6) NOT NULL default '0', > > > FDPEDFEC datetime NOT NULL default '-00-00 00:00:00', > > > FNPEDTOTAL decimal(12,2) NOT NULL default '0.00', > > > FIPEDFACT int(11) NOT NULL default '0', > > > FDFECFACT datetime NOT NULL default '-00-00 00:00:00', > > > FIPEDSTAT smallint(6) NOT NULL default '0', > > > FCUSERID char(10) NOT NULL default &#
