A new engine under MySQL with trx row-level locking
Hi! This is the first time I am posting to the MySQL mailing list. So let us see if this message gets through. Monty recommended that I should write to this list. I am currently putting my database engine called Innobase under MySQL as a new transactional table handler. It is projected to come out with MySQL version 4 as free open source software. The Windows and Linux versions of the combined MySQL/Innobase database already work. Innobase supports transactions, rollback, recovery, row-level locking, and also consistent non-locking reads in the Oracle style. I noticed from the messages posted yesterday to this list that the lack of transactions has been a major complaint of some free database users about MySQL. Transactions will come to MySQL with Innobase, and also with the Berkeley DB handler, which is an alternative transaction-safe table handler currently being installed under MySQL. You will be able to use Innobase through MySQL by creating your tables with CREATE TABLE FOO () TYPE = INNOBASE option. Otherwise they will appear like any MySQL tables. Transaction commit and rollback are done in MySQL through COMMIT and ROLLBACK commands. Row-level locking is automatically performed in SQL commands like INSERT and UPDATE. SELECTs will by default use the consistent read mechanism, which means that no locks are placed, rather the SELECT will read a consistent snapshot of the database at a point of time. This means that Innobase is a multi-versioned database: it stores old versions of rows as long as they might be needed in SELECTs. The row-level locks in Innobase are next-key locks: this means that transactions are serializable and so-called phantom rows will not appear. SELECTs will have also options ...FOR UPDATE and ...IN SHARE MODE, which mean that the SELECT places exclusive or shared locks on rows it reads. These options are useful in some applications. Innobase tables are placed in files you specify in the my.cnf file, along with the file sizes. The files form an Oracle-style tablespace where all Innobase tables and other data structures are stored. The CPU performance of MySQL/Innobase should be the best of all disk-based relational transactional databases. At least the simple join and insert tests I have run suggest this. A restriction currently is that a row in Innobase can be at maximum 7.5 kB long. BLOBS should appear some time in the future when I have time to implement them. If you readers of the MySQL mailing list have comments or questions about this, I would be pleased to receive feedback on this mailing list. Best regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A new engine under MySQL with trx row-level locking
Hi! I hope that this message goes to the right 'thread' in the mailing list; not replied to a thread before. Monty recommended that we should wait still for a few weeks before releasing MySQL 4.0/Innobase-alpha, to keep source code versions in control. But I can now report here the current status of the work, and I will post an update on this list every week. The current status of MySQL/Innobase is that it runs on Intel Windows NT and Intel Linux. It runs a shortened version of the 'bench' test suite of MySQL without errors, and also can run my own multithreaded stress tests. The current TODO list I have is the following: 1. Optimize the MySQL/Innobase interface regarding to CPU time, and add support for small, less than 7.5 kB BLOBS. 2. Test correct handling of all MySQL data types and the SQL NULL value. 3. Give appropriate information to the MySQL query optimizer about table sizes, and also to users about the file space occupied by each table, index, and other data structures. 4. Tell the MySQL query optimizer if a secondary index contains all the required columns: no need then to search the clustered index (in Innobase every table has a clustered index where the data is stored, this is similar to Sybase clustered indexes). 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks within its own lock table, but if a user uses also MySQL LOCK TABLES... or BDB locks, a deadlock can occur where Innobase does not know of all the locks: this is resolved by adding a timeout for a lock wait, say 100 s., after which the transaction is rolled back. 6. Make the (implicit) select in UPDATE TABLE ... SET ... an x-locking read, not a consistent read. 7. Interface SELECT ... FOR UPDATE and SELECT ... IN SHARE MODE to Innobase, (MySQL parser in 4.0 already knows these syntactic constructs). 8. Move the SQL NULL value as the first in the alphabetical order. 9. Prevent MySQL from using 'generate_table' in DELETE FROM ... . That is not good if we want a rollback. 10. Port to Solaris, HP-UX and all different Unix flavors. --- 11. Add support for 7.5 kB BLOBS. 12. Writing a direct CREATE INDEX (currently MySQL internally uses ALTER TABLE if an index is created to a table already holding rows). 13. Writing a lock monitor. 14. Writing a disk i/o monitor. Items 1 - 10 mean mainly small changes in source code, and they are necessary to make the database useful. Items 11 - 14 are long-term projects. Lots of work :), but Innobase now contains 100 000 lines of C, so the above changes 1-10 might be small in proportion to the work already done. If you readers of the list have opinions, you have a chance to influence the TODO list above: I can try to mould the list according to what people feel is the most relevent thing to do. Best regards, Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Status of MySQL 4.0/Innobase engine/trx and row level locks
Hello! I promised to report to the mailing list how the interfacing of MySQL and Innobase engine progresses. Innobase will provide transactions, rollback, row level locking, and Oracle-style non-locking consistent read to MySQL. It currently runs on Intel Linux and NT. Following items from the TODO list from last week have been done: - 1. Optimize the MySQL/Innobase interface regarding to CPU time, and add support for small, less than 7.5 kB BLOBS. 6. Make the (implicit) select in UPDATE TABLE ... SET ... an x-locking read, not a consistent read. 7. Interface SELECT ... FOR UPDATE and SELECT ... IN SHARE MODE to Innobase, (MySQL parser in 4.0 already knows these syntactic constructs). - Following items remain: - 2. Test correct handling of all MySQL data types and the SQL NULL value. 3. Give appropriate information to the MySQL query optimizer about table sizes, and also to users about the file space occupied by each table, index, and other data structures. 4. Tell the MySQL query optimizer if a secondary index contains all the required columns: no need then to search the clustered index (in Innobase every table has a clustered index where the data is stored, this is similar to Sybase clustered indexes). 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks within its own lock table, but if a user uses also MySQL LOCK TABLES... or BDB locks, a deadlock can occur where Innobase does not know of all the locks: this is resolved by adding a timeout for a lock wait, say 100 s., after which the transaction is rolled back. 8. Move the SQL NULL value as the first in the alphabetical order. 9. Prevent MySQL from using 'generate_table' in DELETE FROM ... . That is not good if we want a rollback. 10. Port to Solaris, HP-UX and all different Unix flavors. From the progress we can give an estimate that the interfacing is done 3 weeks from now. But a distribution version requires MySQL 4.0 source, and that may still be farther out. Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update: Status of MySQL 4.0/Innobase/transactions row level locks
Hi! The timeout in Innobase deadlock resolution is configurable in my.cnf file: you will be able to set it in units of one second (or would someone need an even greater precision?). In the future we might modify the MySQL code so that Innobase gets the information of MySQL LOCK TABLES commands before MySQL tries to set the locks. Then Innobase will be able to detect these deadlocks immediately and roll back the transaction. In this case we could also allow a user to issue several consecutive LOCK TABLES commands without the need to call UNLOCK TABLES between these. Currently, in MySQL you must call UNLOCK TABLES before issuing another LOCK TABLES, because there is no deadlock detection in MySQL itself. The MySQL mailing list is currently the only place to discuss the Innobase table handler. You can also send me email directly, I will be happy to reply! Regards, Heikki Tuuri Innobase Oy At 04:45 AM 2/11/01 +0100, you wrote: Hi. On Fri, Feb 09, 2001 at 05:34:38PM +0200, you wrote [...] 5. Add a timeout to lock waiting: Innobase can detect and resolve deadlocks within its own lock table, but if a user uses also MySQL LOCK TABLES... or BDB locks, a deadlock can occur where Innobase does not know of all the locks: this is resolved by adding a timeout for a lock wait, say 100 s., after which the transaction is rolled back. Maybe I misunderstand this point: But a 100 secs timeout (in worst case of a deadlock) would be absolutely inacceptable within my applications. :-/ I mean, okay, if I am going to block a table for 100 secs, that's my problem. But it sounds as if the handler waits 100 secs before it "solves" a dead-lock condition by failing. Is there a better mechanism planned for later? At least one should be able to change the timeout (I would set it below 5 seconds...). Bye, Benjamin. PS: If there is a better place to discuss the Innobase table handler for MySQL, let me know. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Update: Status of MySQL 4.0/Innobase/transactions row level locks
[...]. Currently, in MySQL you must call UNLOCK TABLES before issuing another LOCK TABLES, because there is no deadlock detection in MySQL itself. I believe a LOCK TABLES does an automatic atomic UNLOCK TABLES of any that were locked before if it's able to get the requested locks. Tim. Right, sorry, I was thinking the internal workings of the database. By the way, has anyone felt a need for 'gradual' table locking: i.e., if you do not know in advance which tables you should lock, and only find out during the transaction? Then you might want to set more table locks during the transaction. I guess such a need for gradual table locking is not very common? Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tr: Concurrent insert/select with BDB
Philippe, did you try to do the insert and select on the same database page? In BDB there is page level locking used, and the behavior you describe sounds like that the select has placed a page level lock on the page where you try to insert. Then it is correct behavior of the database that your insert has to wait until the commit which releases the locks placed by the select. A possible solution would be to use 'dirty read', i.e., non-locking read in select, but I do not know if BDB provides such. My own Innobase table handler allows you to read and insert data concurrently with no interference from locks, because Innobase does in a select a consistent non-locking read, in the style of Oracle. But MySQL/Innobase is not available yet. I have virtually completed the interfacing work to MySQL, and Monty just said that Innobase should come out in version 3.23 of MySQL next weekend. Regards, Heikki --- Original message --- Hi I post once more this message, because I didn't receive any answer. - Message d'origine - De : Philippe MORIN : [EMAIL PROTECTED] Envoy : vendredi 9 fvrier 2001 09:49 Objet : Concurrent insert/select with BDB Hi I'm using MySQL 3.23.32 on Linux. I try to run concurrent insert/select requests on BDB tables, but it doesn't work. I started mysqld with --bdb-shared-data option. I run mysql on 2 clients (on the same computer). The first one runs a select request and the second one runs an insert request. If I use autocommit, the insert waits for the end of the select. If I don't use autocommit, the insert waits for the commit of my first client. Did someone ever use concurrent insert/select with BDB databases ? Are there some special parameters to initialize, to allow insert and select at the same time (it doens't matter for me if my first client reads duplicated records). Thanks a lot ! Philippe Morin - Prologue Software (France) Software Engineer Email: [EMAIL PROTECTED] Internet : http://www.prologue-software.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innobase + transactions row level locks will be released in a few days in MySQL 3.23.3x
Hi, readers of the mailing list! Monty will roll a new distribution of MySQL 3.23.3x this weekend. Innobase will be included there and it will provide transactions, rollback, recovery, row level locking, and Oracle-like consistent read. Monty will leave for a vacation in Rio on Wednesday, so the new distribution should be ready at the latest on that day. Currently, Intel NT and Intel Linux versions have been tested. We will compile and test Solaris 32-bit and Linux Alpha 64-bit versions this weekend. Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions -- whole table locked from reads too?
Scott, did you try to do the insert and select on the same database page? In BDB there is page level locking used, and the behavior you describe sounds like that the insert has placed a page level lock on the page where you try to do the select. Then it is correct behavior of the database that your select has to wait until the commit which releases the locks placed by the insert. A possible solution would be to use 'dirty read', i.e., non-locking read in select, but I do not know if BDB provides such. My own Innobase table handler allows you to read and insert data concurrently with no interference from locks, because Innobase does in a select a consistent non-locking read, in the style of Oracle. But MySQL/Innobase is not available yet, it will come out in MySQL 3.23.3x in a few days (see my posting a couple of hours ago). Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase + transactions row level locks will be released in a few days in MySQL 3.23.3x
Hi Jeremy! Very cool! I see the change log entries in the on-line manual already, but the source tarball isn't on SourceForge yet. :-( Anyone have one yet? No, we are still busy working on the distribution, and no one has it yet. I just 15 minutes ago got the Solaris version running :). Monty will be in Sweden tomorrow (Monday). The boss of MySQL AB, Allan Larsson, has his 50th birthday. When Monty gets back, he will continue rolling the tarball. But more info on Innobase later, it is 2 a.m. now and sleep is needed now. Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: iNNOBASE
Peter, I guess you mean the source tree at www.bitkeeper.com? The interface file ha_innobase.cc can be found there, but not yet the Innobase source, I think. Then it cannot yet be compiled from the source tree, because it needs Innobase header files. But we should have the source out by Wednesday, when Monty leaves for a trip to the Rio carnival. Regards, Heikki At 03:43 PM 2/19/01 +0300, you wrote: Hello , Good day. I'm now trying to build mysql 3.23.34 aviable in bitkeeper tree, to get a chance to check innobase table handler before release is out, to make chance for release to be more stable :) Currently I have the following problem - innobase is allready in tree but configure script does not know enything about it and so it's not compiled in. I know you should have an ideas to make it work. Thanks in advance. P.S Sorry I've Sent a copy to mysql list ocasionally. -- Best regards, Peter mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase + transactions row level locks will be released in a few days in MySQL 3.23.3x
Hi again! There were several questions on the mailing list, which I try to answer: - The MySQL manual says that you can convert between different table types using ALTER TABLE, the same should hold for the Innobase table type, though I must admit I have not yet tested it. ALTER TABLE is a high-level function, which should be taken care in MySQL code higher than the the table handler. - There is a Win NT version of MySQL/Innobase, and also Win95/98 version should work, but I have not tested it lately. I have developed Innobase on Win NT, and therefore the NT version should be in teh best shape of all versions. - There is currently no website dedicated to Innobase. I have left registration application for www.innobase.net, and a page should appear there in a few days. The website www.innobase.com is not my website, that is a German IT consultant firm specializing in Baan services. In MySQL manual, Section 8.6, on the MySQL website you can read some technical info about Innobase. - The origin of the Innobase database is in my work at University of Helsinki 1993-95. I studied how the performance of traditional disk-based relational databases could be improved. I wrote the 100 000 lines of code in Innobase during the next 3 years. When I was looking at commercializing my engine, it caught my eye that free databases are catching steam. I started collaborating with MySQL AB last September. - Innobase engine has not been used in applications yet. I have run MySQL tests and my own multithreaded stress tests on MySQL/Innobase, and it seems to work, but the final word of course will come from the people who will use it in real-world applications. I have designed Innobase so that it should be easy to maintain and debug: there are lots of comments in the source code, and in the full debug version there are lots of assertions: in that version it spends some 99 % of CPU time doing consistency checks to data structures. I have also written memory debugging to Innobase: it can notice memory leaks and overwrites. Semaphores in Innobase are ranked in the order they should be acquired when code is processed: the debug version constantly checks that the order is obeyed. The debug version also automatically detects deadlocks of threads, and prints diagnostic information about them (of course these deadlocks should not occur if the correct semaphore acquisition order is obeyed). - The main strengths of Innobase should lie in its speed and also in transaction management. To my knowledge MySQL/Innobase is the fastest relational transactional disk-based database engine currently existing. In transaction management an innovation is to store row-level locks as a bitmap: the locks fit in a space so small that lock escalation is not needed in real-world applications. Query processing in Innobase is optimized with an adaptive hash index mechanism: the database constantly checks if it would benefit from building partial hash indexes to the data existing in the buffer pool. The mechanism is such that it can use the hash index also to search for right places to insert index records for new rows: thus the hash indexes also speed up insertions to normal B-tree indexes. But, now I will continue with my porting project to Alpha 64-bit Linux. I will report to the mailing list tomorrow what is the release status then :). Best regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sorry, delay in MySQL/Innobase distribution
Hi, readers of the mailing list! Sorry, we were not yet able to roll a distribution of MySQL-3.23.3x/Innobase yesterday. Monty is now traveling in South America for a week, and the distribution will take still some time. Currently, MySQL/Innobase runs on Win NT, Linux Intel, Linux Compaq Alpha 64-bit, and Solaris Sparc 32-bit. On FreeBSD there are still problems. I will tell more as there is progress. Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: More Innobase questions (Was: RE: Innobase)
o check repair tools are provided which would be real showstopper for production usage even if the tables will be quite stable - what should I do if I have file system dammage so I need to check the things up. Here we have even more problems as many tables share one file so we probably can't just check them one by one. I will have to write CHECK TABLE (in Oracle called ANALYZE). I am not sure if repair table is a good idea, Oracle DBA manual at least does not mention such a procedure for Oracle. If the database gets corrupted, then the safest method is to recover it from a backup which is old enough so that corruption does not occur in the backup. Or recover the tables from dumps and MySQL archived log files. 8) As I understand innobase works with large files self mannaging usage in them - so Is it planned to support RAW devices with innobase Yes, Innobase will support raw disks. I have to test it still. ? -- Best regards, Peter mailto:[EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: More Innobase questions (Was: RE: Innobase)
Hi! There were more questions about Innobase: Here is the comming question - can you speak some more about apace allocation consepts used in innobase. For example how would it like to work with huge number (10.000+ of the tables) and how space allocation The data dictionary is hashed, a huge number of tables is no problem. Space allocation is done for small tables one database page at a time (default 16 kB). For big tables space is allocated in chunks of consecutive pages, up to 256 pages big. is done - I know innobase supports clusterisation, but if I'm not Yes, all tables in Innobase are clustered by their primary key, that is, rows are physically in the order of their primary key. If you do not specify a primary key, then a generated row id is internally used as the primary key: the rows will be ordered according to their insertion sequence. The support for the Oracle-like clustering, physically mixing several tables, is built in but I do not have a user interface for it. mistaken only for rows from one table. The next thing is is there a difference made in handling dynamic and static rows and how fragmentation is avoided for last ones ? You probably mean what happens if the size of a row changes? Innobase uses no overflow pages. If a row is updated so that it cannot fit on the page, we split the B-tree page to get more space. This will of course slow down table scans. Recreating tables where this happens may be sensible from time to time. As far as I know some space allocation problems are qute different in file systems and in databases, so I'm surprising you're speaking about similar algorithm. Yes, in file systems you cannot grow a file in the middle, only at the end. Other thing is - does innobase supports hash indexes internally ? Yes, it automatically creates hash indexes, no user configuration is needed. Other thing - can I not add files but increase it size ? How innobase will handle if I'll do so ? Currently, you cannot increase a size of a data file, but you have to add a new. Increasing the size of the last data file would of course be technically easy to implement. Few words about creation. As I looked at innobase files they are mostly zero filled while created. I think it would be nice to add a possibility to create files not by write() but by lseek() this will make creation faster and files taking less space on file system which support holes in files - last one is good as you can just create huge files in the very begining and do not care much about wasting thespace. The problem is that the OS may fragment the files if they are not physically allocated. D) can you safely add new files when there is data in them already? HT Yes you can, but you have to shut down the database first. Shurely special command may be added for this later :) Yes, in Oracle you can add a data file while the database is up (ALTER TABLESPACE ADD DATAFILE), and it is easily implementable. Other thing is - don't you think it's not quite good idea to store database configuration in config file. For now it's quite simple and can be easyly recovered by looking at the files anf their sizes but then you will have more complex configuration (i.e several tablespaces with external mirroring) it will be hard to recover. You have to keep backups of .cnf files also. Other question - files sizes. Does innobase itself support 4GB+ files? The support is built in, but not tested yet. 4) Currently ATIS test fails with innobase table: Retrieving data Warning: Query 'select city.city_name,state.state_name,city.city_code from state,city where city.state_code=state.state_code' returned 1 rows when it should have returned 11 rows Got error: when executing select flight.flight_code,aircraft.aircraft_type from flight,aircraft where flight.aircraft_code=aircraft.aircraft_code got 0 instead of 579 *** HT You may have an outdated version of the MySQL source distribution. HT (I am currently exchanging lots of emails with Peter and looking at the HT problem.) I've currently patched a version but still get the same error. Other tests (i.e big tables) now passes. The Linux version runs the test without errors on my machine. Maybe the source repository you are using is still somewhat outdated. HT physically writes its data files full at database creation, so that HT the space is physically occupied. Yes of couse. But I think this should be mentioned in the manual so users not surprised mysqld is doing something several minutes after startup before it's ready. Will be added to the manual, and to the message the database prints when a new data file is created. Do you have plans for external RAID support I.E stripping and mirroring. At leas in row device configuration I thing this may give better perfomance then OS/Hardware RAID. No, I have no plans for simulating RAID in the database server, it is done on a lower level, like RAID disk hardware. We have to measure the speed of hardware RAID systems, to
Re: Temporary tables, BDB tables, and Innobase questions
Hi Stephen, 1. Can temporary tables be of type BDB? Now before the question is asked as to why I need transactional support on a temporary table, let me first say that I really don't need it; this is just something we stumbled across during development. If we create a temporary table of type BDB and attempt to rollback a transaction, we get an error during rollback and the data is not rolled back. However, doing the exact same transaction on a BDB table that is not specified as being temporary work as expected, ie, a rollback undoes theupdates. The idea of a temporary table is that it is a fast temporary storage for rows. The user does not need recovery for it. Then one can save writing the log records, and insertions are faster. Also rollback may be unavailable for a temporary table, for a similar performance reason. 3. I've read several posts regarding the Innobase tables. Where can I download the source from to start trying to use Innobase rather than BDB tables? Innobase is projected to be released in MySQL-3.23.34. Monty will be back from South America on Thursday or Friday, and the work to build the release will continue. By the way, Innobase now has a web page at www.innobase.fi (innobase.com and innobase.net were already reserved). Stephen L. Faustino Senior Software Engineer SecureLogix Corporation Direct/Vmail (210)402-9669x949 mailto:[EMAIL PROTECTED] Regards, Heikki Tuuri Innobase Oy Helsinki, Finland - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:Innobase
Hi Phil, Looked at the Innobase web site - very exciting looking specs. SQL: -a small interpreter with stored procedure syntax like in Oracle, no SQL optimizer Does this mean that MySQL using Innobase tables, will this allow for stored procedures of multiple SQL statements stored on the server that execute or not as whole ie. a series of INSERT UPDATE ... SELECT . UPDATE .. held in a script at the server and executed by calling a stored proc name that either fails and rolls everything back to before the proc was started or completes? Also server side cursors? Reducing network overhead. There is a small internal SQL interpreter inside Innobase with stored procedures. But, unfortunately, it is not accessible through MySQL, because MySQL currently does all the SQL interpreter functionality in the combined MySQL/Innobase database. Stored procedures and triggers are in the TODO list for MySQL, but I do not know the timetable. LPhil Daintree - Dux Industries Ph:+64 (04) 567 8900 Fax: +64 (04) 567 8904 Regards, Heikki Tuuri Innobase Oy Helsinki, Finland email: [EMAIL PROTECTED] website: www.innobase.fi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innobase released in MySQL-3.23.34a source distribution: transactions and row level locking now in MySQL
Hi, readers of the mailing list! the Innobase table handler has finally been released as a part of the MySQL-3.23.34a source distribution Tarball. Note that the Windows source distribution or the binary distributions do not contain yet Innobase. I have copied below the release note. I will post more information to my website www.innobase.fi as I get feedback and bug reports from users. --- Innobase engine released in the MySQL-3.23.34a source distribution for Unix. (Helsinki, March 12th, 2001) The Innobase engine source code has been released under the GNU GPL License 2 as a part of MySQL-3.23.34a source distribution for Unix. You can find the source distribution file from the MySQL website: http://www.mysql.com/downloads/mysql-3.23.html, look at source downloads for 3.23.34a Tarball. Innobase adds transactions, rollback, commit, row level locking, and an Oracle-style consistent non-locking read to MySQL, the popular open-source database. The combination MySQL/Innobase is probably the world's fastest disk-based relational transactional database. How to compile MySQL-3.23.34a with Innobase? After downloading, unzipping, and untarring the 'Tarball', go to the main directory of MySQL (usually named mysql-3.23.34a), and type: ./configure --with-innobase Then you have to compile and install MySQL. For information on this look at the MySQL documentation. To create tables in the Innobase format you have to first specify Innobase startup options in the my.cnf file. For instructions how to specify them, see section 8.7 of the MySQL online manual at the MySQL website. You can create tables in the Innobase format by specifying TYPE=INNOBASE after the table creation statement: CREATE TABLE t10 (a int not null, b int, primary key (a)) TYPE=INNOBASE; Questions, comments, bug reports on MySQL/Innobase: Please send your feedback on Innobase to [EMAIL PROTECTED] You can also post your feedback on the MySQL mailing lists: see the Documentation section of the MySQL website on instructions how to subscribe and post to the mailing lists. The main mailing list, simply named mysql, is the liveliest one. If you can report a repeatable bug, you can also post to the bugs mailing list. I would also like to receive reports from those who have tested Innobase, but who have no problems. It is helpful to know where the system is working well. Helsinki, March 12th, 2001 Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Innobase in MySQL
Hi Rick, In my C code, I use SQL statements, but I use the mySQL C API to pass on the SQL statements, check for errors, number of rows returned, and access the results. I would assume that this will still work; they still work with BDB tables. ok, it should work like for BDB. There may be small differences when the API tells you the number of rows deleted or updated, because I have not had time to check that these numbers exactly agree for diffrent table types. Also SHOW TABLE STATUS will return different numbers, because for example the row count is only approximate in Innobase, calculated from a small sample of the table. I noticed in many list comments about others having problems with corrupt tables (MyISAM), and having to repair them. Since you don't have any such tools, can I assume that you are not having any problems with data loss or corrupt tables? I have not noticed table corruption in my own tests of Innobase since last fall. But, of course you have to take backups regularly. You should also use mysqldump to take logical (= text format file) backups of your tables. In case of table corruption you can recover from a backup using the MySQL log. The safest backup to use in recovery is the logical backup produced by mysqldump. I do not think a repair table procedure is the best way to implement safety, because neither MS SQL Server nor Oracle have such a procedure, and there is a vast user base for those databases. Recovering from a backup is a more reliable way. Remember that Innobase uses transactions and logging. From a benign server process crash it will automatically recover using the log files. Table corruption will only result from a bug in the low-level source code, or a media failure of the disk. I have crashed Innobase hundreds of times this winter during my development work, and it has always been able to revover from the log files. Regards, Heikki Thanks, Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Innobase in MySQL
Rick, Everything you wrote sounds good, except for one thing. I use the result count from a select or delete for logical branching within my code. I.e. I perform a select and if rowcount is zero, then I can do an update. That is a problem. Any thoughts? The basic rowcount functionality should work ok, because the mysql client returns right values when I do SELECTs, UPDATEs, INSERTs, or DELETEs. What I meant was that small differences between table types may occur if you receive, for example, a duplicate key error when you do an insert of a set of rows, or a transaction deadlock which results in a rollback. We have to test these special cases and check that they return the same (sensible) values for all table types. Thus, if you get an error from your SQL statement, currently you have to be extra careful to check what the rowcount returned really means. I looked at the MySQL manual section 7.21 and found the following: If you specify the keyword IGNORE in an INSERT with many value rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE, the insert is aborted if there is any row that duplicates an existing key value. You can determine with the C API function mysql_info() how many rows were inserted into the table. But when I tested with Innobase and MyISAM tables, I noticed that MySQL seems to assume the IGNORE option for all INSERTS, for both table types. On the other hand, ANSI SQL specifies that a duplicate key error should result in the rollback of the whole SQL statement, i.e., no rows should be inserted. The conclusion is that you should not rely on the current behavior if the SQL statement returns an error. The error handling procedure is likely to change. Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Flags problem 3.23.34a with BDB support/Innobase on HP-UX 10.20
Lutz, what compiler are you using? GCC? HP:s own commercial compiler, I assume. I am currently trying to compile 3.23.34a coming with BDB support. I have zlib installed in the /usr/local (not normally searched for libraries), so that until now I have [CFLAGS etc ] LDFLAGS=-L/usr/local/lib ./configure BTW, the compilation of innobase fails with cc -DHAVE_CONFIG_H -I. -I. -I.. -I../../include -I../include -DHPUX -Dinline= - I/opt/dce/include -I/usr/local/include -O -DDBUG_OFF -Ae +O3 +Olibcalls +Onolim it +DAportable -DHAVE_BROKEN_SNPRINTF -c os0thread.c cc: warning 454: Option +O3 overrides previous setting of +O2. cc: "os0thread.c", line 29: error 1527: Incompatible types in cast: Must cast fr om scalar to scalar or to void type. cc: "os0thread.c", line 69: error 1554: Indirection must be through a pointer. cc: "os0thread.c", line 69: warning 563: Argument #2 is not the correct type. cc: "os0thread.c", line 69: error 1718: Types are not assignment-compatible. cc: "os0thread.c", line 69: warning 563: Argument #3 is not the correct type. cc: "os0thread.c", line 69: warning 604: Pointers are not assignment-compatible. cc: "os0thread.c", line 69: warning 563: Argument #4 is not the correct type. gmake[4]: *** [os0thread.o] Error 1 I have not yet analyzed the precise reason of the failure. Since it affects thread functions and the platform is HP-UX 10.20, I am not sure whether there is a solution at all. The solution might be to turn the compiler warning level to be less strict. I think the flag +DAportable is a HP compiler flag which produces an error from some less serious compiler warnings. Also the flag -Ae is suspect. But the easiest solution probably is to use the GNU C compiler if you have one on your machine. I have not compiled Innobase on the HP's commercial compiler and it will take some time to eliminate all compiler warnings there. Also the 'inline' keyword and inlined functions will probably cause problems on the HP compiler. I will check how one could generate makefiles with less strict warnings control. One possibility is to create a file Makefile.i to the directory: ...your mysql source dir... /innobase/include and put the following define there: CFLAGS = -DHAVE_CONFIG_H -I. -I. -I.. -I../../include -I../include -DHPUX -Dinline= -I/opt/dce/include -I/usr/local/include -O -DDBUG_OFF It should override the flags generated by automake. Then go to the innobase subdirectory and regenerate new Makefiles: automake autoconf ./configure Ignore the warnings automake and autoconf give. Then type make Probably you will get new errors from the HP compiler now :), I can help you tomorrow if you report the next problems you encounter. Best regards, Heikki Tuuri Innobase Oy Best regards, Lutz -- Lutz Jaenicke [EMAIL PROTECTED] BTU Cottbus http://www.aet.TU-Cottbus.DE/personen/jaenicke/ Lehrstuhl Allgemeine Elektrotechnik Tel. +49 355 69-4129 Universitaetsplatz 3-4, D-03044 Cottbus Fax. +49 355 69-4153 --- - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase in MySQL
Joshua, I hope you can also use MySQL dump, in which case, you don't have to shut down, right? yes, you can use mysqldump without shutting down. It did not come to my mind that actually mysqldump is a kind of online backup mechanism :). Since Innobase is multiversioned, you will get consistent snapshots of your tables, and since the consistent read does not set any locks, your users should be able to update the tables concurrently. Here I have to check if mysqldump sets a full table read lock on the table you dump: for Innobase that is not needed, but maybe MySQL currently does this because of other table types. Regards, Heikki j- k- 2) Can you perform backups as with other tables? You have to shut down the database, make sure that it shuts down without errors, and then copy the Innobase data files to a safe place (it is safest also to copy the log files). Note that the .frm files only contain table definitions, not table data, which resides in the data files you specify in my.cnf. Currently there is no incremental backup nor on-line backup in Innobase. -- Joshua Kugler Associated Students of the University of Alaska Fairbanks Information Services Director [EMAIL PROTECTED] 907-474-7601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Flags problem 3.23.34a with BDB support/Innobase on HP-UX 10.20
Lutz, I think you have found the problem. Ok, let's try to narrow things down: I have switched to GCC for the time being and got a similar compile error: gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../../include -I../include-O6 -DDBUG_OFF -DHPUX -I/opt/dce/include -DHAVE_BROKEN_SNPRINTF -c os0thread.c os0thread.c: In function `os_thread_get_curr_id': os0thread.c:29: aggregate value used where an integer was expected Here I try to cast (convert) a thread object into a long integer. Maybe a (handle to) thread object in HP-UX is represented as a struct, and the typecast is not possible. Solution: change the typedef in os0thread.h to: typedef pthread_t os_thread_id_t and replace comparisons like thr_id1 == thr_id2 by pthread_equal(thr_id1, thr_id2) Well, this needs some work from me, as the source code must be changed possibly on several lines. Another possibility is that if you from the header file pthread.h for HP-UX see how to convert a thread handle to a (unique) unsigned long integer, you could try that fix on line 29. os0thread.c: In function `os_thread_create': os0thread.c:69: warning: dereferencing `void *' pointer os0thread.c:69: invalid use of void expression gmake[4]: *** [os0thread.o] Error 1 Here some of the parameters I pass are not correct for the HP-UX pthread header file definitions, possibly the NULL that I pass as the second parameter. I looked in an online manual of HP-UX 10.30 that the second parameter should be pthread_attr_default, if default attributes are wanted for a pthread creation. You could try replacing the NULL on line 69 by pthread_attr_default. Since threads are not well supported in HP-UX 10.20 I don't have experience with threads. I can send you the thread header files, if you think this might help. It would help. If you can, please send them to [EMAIL PROTECTED] (especially pthread.h). I will check how one could generate makefiles with less strict warnings control. One possibility is to create a file Makefile.i to the directory: ...your mysql source dir... /innobase/include and put the following define there: CFLAGS = -DHAVE_CONFIG_H -I. -I. -I.. -I../../include -I../include -DHPUX -Dinline= -I/opt/dce/include -I/usr/local/include -O -DDBUG_OFF It should override the flags generated by automake. Then go to the innobase subdirectory and regenerate new Makefiles: automake autoconf ./configure Ignore the warnings automake and autoconf give. Then type make Probably you will get new errors from the HP compiler now :), I can help you tomorrow if you report the next problems you encounter. Actually, I don't get any error. It steps into each directory and has nothing to do at all. Seems the .o and .c files are not propagated into the Makefiles. Sorry, forgot to mention you should delete all .o files in subdirs. Type in the innobase directory: rm ./*/*.o and only after that do the make. Thank you for trying on HP-UX. On Solaris I was able to compile, but obviously I still have overlooked some Posix thread subtleties. Best regards, Heikki Best regards, Lutz -- Lutz Jaenicke [EMAIL PROTECTED] BTU Cottbus http://www.aet.TU-Cottbus.DE/personen/jaenicke/ Lehrstuhl Allgemeine Elektrotechnik Tel. +49 355 69-4129 Universitaetsplatz 3-4, D-03044 Cottbus Fax. +49 355 69-4153 --- - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Flags problem 3.23.34a with BDB support/Innobase on HP-UX 10.20
Lutz,
I looked at the pthread header file which is, by the way, 52000 lines long :).
It seems that pthread_attr_t and pthread_t are not pointers like in some
other Unixes, but structs of three fields:
struct {
field1; /* this is a pointer */
field2; /* a short int */
field3; /* a short int */
};
Obviously, one cannot cast such a struct to a long integer, and neither
can one pass NULL in place of such a struct. The compilation might
work if you on line 29 of os0thread.c write something like:
return((os_thread_id_t) (pthread_self()-field1));
and on line 69 replace the NULL parameter with pthread_attr_default
In os0thread.h you should return to the original typedef of os_thread_id_t:
typedef unsigned long int os_thread_id_t
These are of course just temporary fixes, and I will modify the next
version to adhere to strict Posix definitions.
Since threads are not well supported in HP-UX 10.20 I don't have experience
with threads. I can send you the thread header files, if you think this
might help.
It would help. If you can, please send them to [EMAIL PROTECTED]
(especially pthread.h).
In the meantime sent in private email.
Thank you!
Sorry, forgot to mention you should delete all .o files in subdirs. Type
in the innobase directory:
rm ./*/*.o
and only after that do the make.
Well, I decided for 'find . -name "*.o" | xargs rm' but that did not solve
my problem. The point is, that for reasons I don't see, the target definitions
are not propagated into the Makefiles, so there is nothing to be done.
To be more precise: In the "mysql configured" innobase, the os/Makefile has:
libos_a_OBJECTS = os0proc.o os0shm.o os0sync.o os0thread.o os0file.o
This line (and other lines) are missing in the one for which I called
automake and autoconf seperately.
It seems, that for the inclusion into the mysql distribution things have
been changed.
(Just like for berkeley-db there is no "make clean" target, so that you
cannot successfully "make clean" the mysql package anymore.)
Yes, there was a last minute change to the Innobase automake files I was
not aware of. The original ones would have worked :). I can send you
the original automake files I have, but only tomorrow, as I am leaving
for home now. I will also email Tim Smith, our automake expert.
Do you have a "standalone" version of innobase available that I can test
independent from the mysql distribution?
Sorry not currently. I do have an old 'standalone' version, but its
interface is very primitive compared to MySQL. I only wrote my own interface
to run the TPC-C benchmark, it has absolutely no conveniences of a
mature product like MySQL. Furthermore, the interface only works in NT.
Best regards,
Heikki
Best regards,
Lutz
--
Lutz Jaenicke [EMAIL PROTECTED]
BTU Cottbus http://www.aet.TU-Cottbus.DE/personen/jaenicke/
Lehrstuhl Allgemeine Elektrotechnik Tel. +49 355 69-4129
Universitaetsplatz 3-4, D-03044 Cottbus Fax. +49 355 69-4153
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Transactions
Ryan, How exactly does the BEGIN/COMMIT/ROLLBACK transaction process work? The examples I have seen are like: BEGIN; statement 1; statement 2; statement n; COMMIT; Does this imply that if any of the statements fail then a ROLLBACK will be called, otherwise it will COMMIT? you have to code the rollback logic in your application. Since you send SQL statements to MySQL one at a time, the MySQL server cannot know what statements are still coming. In your example, MySQL/Innobase would do the following: if any single SQL statement fails, then depending on the error, Innobase will roll back part of the statement, the whole statement, or everything you have done since the last BEGIN (= the whole transaction). Then, if you continue sending statements to MySQL, they will be processed like nothing would have happened to the previous statement. Currently, in MySQL/Innobase a duplicate key error in an insert of a row only rolls back that single row insert. A deadlock with other transactions rolls back the whole transaction. ANSI SQL standard specifies that an error in an SQL statement should result in a rollback of that whole statement. We have to consider in future if MySQL should by default follow the ANSI spec. Regards, Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase compile problems
Rick, I tried to build from source the latest MySQL 3.23.34a and I found the following problems: 1) The Makefile in the 'pars' and 'mem' directory have missing ../include/ paths on some dependencies. Do you mean that when you compile, on the compiler line there is not the flag -I../include? The compiler line should look like the following (except that -g is my own addition): . gcc -DHAVE_CONFIG_H -I. -I. -I.. -I../../include -I../include-g -O2 -c pars0 grm.c . If the flag is missing, there obviously is something wrong with our Makefile.am's or configure.in's. You probably know how to edit the Makefile's automake has generated? A quick fix is to add the missing -I's to the compiler lines there. 2) In the 'mem' directory, the file mem0mem.c tries to include mem0dbg.c, but I think this should be mem0dbg.ic (I changed it to this anyway???) No, it really should include mem0dbg.c :). I divided debug code and non-debug code into two files. 3) The mysqld doesn't link. The function body for mem_init doesn't resolve. I looked in the innobase directory and couldn't find it anywhere. The function is in mem0dbg.c. If you do not include mem0dbg.c, the function is not compiled in. We have to fix our automake files. Could you mail me the generated Makefile's which you think are wrong, so that we could look at them. Regards, Heikki I did configure with --with-innobase and the following other configure options: --with-mysqld-ldflags=-all-static --without-bench --with-extra-charsets=none --enable-static=yes --without-debug Thanks, Rick - / Rick Mehalick Senior Consultant / Shell Services International SSI-GPAX / Phone: 281-544-5092(WCK) / Fax:281-544-2646(WCK) / email: [EMAIL PROTECTED] - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innobase table check.
Peter, could you ftp your binary (compiled with -g option) to support.mysql.com directory /pub/mysql/Incoming I can then try to run the ATIS test on your binary on our SMP Linux. One possible reason for the errors you get is that your version of GCC is buggy in inlining of code, but that is only a wild guess. Regards, Heikki At 12:40 PM 3/15/01 +0300, you wrote: Hello Heikki, Finally I was able to check the innobase tables included into mysql 3.23.34. Well first several times I was quite happy about them, but later understud that this is because option --create-options is broken :) So now I must say On my system innobase seems to work as bad as it worked before :( - ATIS test fails. Retrieving data Warning: Query 'select city.city_name,state.state_name,city.city_code from state,city where city.state_code=state.state_code' return ed 1 rows when it should have returned 11 rows Got error: when executing select flight.flight_code,aircraft.aircraft_type from flight,aircraft where flight.aircraft_code=aircraft .aircraft_code got 0 instead of 579 *** - mysqld is restarted during alter table test and one more time during the tests: Innobase: Started /usr/local/mysql/libexec/mysqld: ready for connections Innobase: Warning: out of memory in additional memory pool. Innobase: Innobase will start allocating memory from the OS. Innobase: You should restart the database with a bigger value in Innobase: the MySQL .cnf file for innobase_additional_mem_pool_size. 010314 19:00:00 Warning: Checking table: './oldgoodcounter/stop_recs' 010314 19:00:01 Warning: Checking table: './oldgoodcounter/registrants_stats' mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 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... Bogus stack limit or frame pointer, aborting backtrace. Thread 5126 stopped in file buf0lru.c line 371 Thread 5126 stopped in file buf0lru.c line 371 Number of processes running now: 0 010314 21:13:33 mysqld restarted Innobase: Database was not shut down normally. Innobase: Starting recovery from log files... Innobase: Starting log scan based on checkpoint at Innobase: log sequence number 0 3385030377 - It seems like error message for error 139 should be changed because it says about 16M there innobase and gemini has their own limits about it. 139 = Too big row (= 16 M) Now few words about reasons why this may happen (I'll try to check them out soon) 1) I'm using 2.4.2 kernel,SMP - so there may be some incompatibilities. 2) I'm usung patched for 2GB limit GLIBC 3) I'm using ReiserFS file system. 4) The parameters I'm using. (Like bdb does not work with big tablecache) Anyway MYSQL with MYISAM works with no problem on this system, and I use the same system for production on 20 machines so this looks for me more like incomportability problem. -- Best regards, Peter mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql crash during large insert from MYIASM to INNODB table.
Johannes, which version of MySQL you are running? From http://www.innodb.com/bugfixes.html : November 17, 2001: The insert buffer tree can get into an inconsistent state, causing a crash, and also crashing the recovery. This bug may appear especially in large table imports or alterations. Fixed in 3.23.45. The bug could cause an assertion failure in fil0fil.c. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Johannes B. Ullrich wrote in message ... -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I just started using innodb tables to help with some locking problems. The table I am trying to convert is large (100 Million rows, 23 GigByte). I setup 20 2Gig ibdata files. (I choose a size of 2 Gig as this is the maximum size I can easily transfer to another machine for backups. The files system can handle larger files). I did create a new table, and transfered the old table to the new tables using 'insert into newtable select * from oldtable'. (as a test, I did try 10 Million rows first which worked fine and took about 30 minutes). After a while (1 hr?), mysql crashed. Here are the details: Resolved stack: 0x80b39c6 handle_segfault__Fi + 406 0x4003b935 _end + 937197517 0x8184f3f fil_io + 607 0x816ca99 buf_read_page + 89 0x8164e3a buf_page_get_gen + 278 0x814a835 btr_cur_search_to_nth_level + 1101 0x812a970 ibuf_insert_low + 1256 0x812b2a0 ibuf_insert + 380 0x814a89a btr_cur_search_to_nth_level + 1202 0x812d6fb row_ins_index_entry_low + 143 0x812dbc8 row_ins + 504 0x812dd30 row_ins_step + 272 0x812e697 row_insert_for_mysql + 151 0x80fbc82 write_row__11ha_innobasePc + 394 0x80de7ff write_record__FP8st_tableP12st_copy_info + 527 0x80e036d send_data__13select_insertRt4List1Z4Item + 93 0x80dd68f end_send__FP4JOINP13st_join_tableb + 111 0x80d8788 sub_select__FP4JOINP13st_join_tableb + 240 0x80d84a1 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 393 0x80d1a75 mysql_select__FP3THDP13st_table_listRt4List1Z4ItemP4ItemRt4List1Z15Item_fun c_matchP8st_orderT5T3T5UiP13select_result + 6953 0x80bb154 mysql_execute_command__Fv + 5736 0x80bcb77 mysql_parse__FP3THDPcUi + 211 0x80b9252 do_command__FP3THD + 1286 0x80b8798 handle_one_connection__FPv + 632 /usr/local/mysql/libexec/mysqld: ready for connections InnoDB: Assertion failure in thread 38951 in file fil0fil.c line 977 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 agaist 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=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=45 max_connections=200 threads_connected=6 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1211610 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 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... Stack range sanity check OK, backtrace follows: 0x80b39c6 0x4003b935 0x8184f3f 0x816ca99 0x8164e3a 0x814a835 0x812a970 0x812b2a0 0x814a89a 0x812d6fb 0x812dbc8 0x812dd30 0x812e697 0x80fbc82 0x80de7ff 0x80e036d 0x80dd68f 0x80d8788 0x80d84a1 0x80d1a75 0x80bb154 0x80bcb77 0x80b9252 0x80b8798 Stack trace seems successful - bottom reached 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 Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0xbf602658 is invalid pointer thd-thread_id=3604 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 3604 did to cause the crash. In some cases of really bad corruption, the above values may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Number of processes running now: 0 020203 20:10:16 mysqld restarted 020203 20:10:16 Can't start server: Bind on TCP/IP port: Address already in use 020203 20:10:16 Do you already have another mysqld server running on port: 3307 ? 020203 20:10:16 Aborting 020203 20:10:16 /usr/local/mysql/libexec/mysqld: Shutdown Complete 020203 20:10:16 mysqld ended - As I start things back up
Re: mysqld crash stack trace from 3.23.47-max (with InnoDB)
Hi! InnoDB seems to have created the database and started ok. This does not look like an InnoDB bug. I think gethostbyaddr asks from a connection the hostname, and subsequent code checks that the host has access rights to mysqld. For some reason gethostbyaddr_r crashes. Hmm.. if you could run inside gdb and look what parameters mysqld passes to gethostbyaddr in mysql/sql/hostname.cc, then it would be easier to determine what goes wrong. Regards, Heikki Innobase Oy Jeremy Zawodny wrote in message ... I just attempted to upgrade a 3.23.41-max server on Linux 2.4.12 to 3.23.47-max. Before starting up 3.23.47-max, I added several Innodb options to the my.cnf file: ---snip--- innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M innodb_data_home_dir = /home/mysql/yahoo/ibdata innodb_log_group_home_dir = /home/mysql/yahoo/iblog innodb_log_arch_dir = /home/mysql/yahoo/iblog set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=128M set-variable = innodb_log_buffer_size=32M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=384M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 ---snip--- And then started the server. It created the 4 data files as well as the the log files. Then I got: 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 020204 1:24:13 InnoDB: Started /home/mysql/bin/mysqld: ready for connections mysqld got signal 11; and a stack trace, which resolved into: 0x807bb5f handle_segfault__Fi + 383 0x82a94aa pthread_sighandler + 154 0x7cb80076 __evoke_link_warning_llseek + 1954074198 0x7cb7fe19 __evoke_link_warning_llseek + 1954073593 0x7cb713e6 __evoke_link_warning_llseek + 1954013638 0x7c97db8b __evoke_link_warning_llseek + 1951967595 0x7c980668 __evoke_link_warning_llseek + 1951978568 0x7c981200 __evoke_link_warning_llseek + 1951981536 0x7c96dee4 __evoke_link_warning_llseek + 1951902916 0x82ddd0a gethostbyaddr_r + 346 0x82ddb44 gethostbyaddr + 196 0x807ff98 ip_to_hostname__FP7in_addrPUi + 232 0x808082c check_connections__FP3THD + 152 0x8080c79 handle_one_connection__FPv + 321 I'm going back to 3.23.41-max for now. Any idea what's up? The configuration, other than InnoDB was virtually identical to the previous version. In fact, I copied the working my.cnf file and added the innodb bits. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 0 days, processed 4,634,010 queries (446/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: increasing InnoDB search performance
Eric, Eric Mayers wrote in message ... I have an application that stores a large (up to 6gb) database in an InnoDB table. I'm using an InnoDB table to have concurrent inserts and selects and to get beyond the 2gb limitation. I have to allow read-only remote database connections and cannot enforce that users use any kind of special query to access the database (e.g., implementing my own fulltext-like index and using the index at an application layer). The content consists of log entries which are each under 255 characters long. use the VARCHAR type instead of CHAR type. Otherwise your columns will take a full fixed length of space and the table will be bigger and slower. I'd like to know what I can do to improve the response time of the database under these restrictions. So I have a few questions: Would adding ram help? I can't imagine that an operation that requires searching 6gb (a disk bandwidth or processing bottleneck problem) would be improved much by adding ram.. ? If every query needs to scan the whole 6 GB table, or access it totally at random, then adding RAM does not help. But usually queries have a restricted working set, and a much smaller buffer pool can serve most queries quickly. Adding RAM and incresing the buffer pool size helps in most cases. Can I do anything (short of hardware changes) to increase performance? The usual way is to use EXPLAIN SELECT, and in very problematic cases use the STRAIGHT JOIN and USE INDEX (index1, index2, ...) clauses of MySQL to force a query plan. The MySQL 'slow query log' can be used to track slow queries. The Unix 'top' and the Windows Task Manager are the best way to determine if the load is CPU-bound or I/O-bound. innodb_monitor prints very detailed statistics on the internal working of InnoDB, and you can also innodb_lock_monitor to track lock waits, if they are a problem. And long term question: I've noticed that a fulltext index feature doesn't appear on the InnoDB todo list. From my (limited) research, it looks like this is a feature a lot of people would find very useful. Is this a long term goal, or has it been excluded for some reason? There has been demand of a fulltext indexing capacity on InnoDB type tables. If there are enough paying customers requesting it, we can assign a developer to port the MyISAM fulltext search on InnoDB. Fulltext indexes will work also on a transactional database. Thanks Eric Mayers Software Engineer Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql create table error
Hi! You have not created an index on your foreign key. From http://www.innodb.com/ibman.html : ... An example: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. If MySQL gives the error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, then the table creation failed because a foreign key constraint was not correctly formed. . Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Demirchyan Oganes-AOD098 wrote in message ... I have the following create table, and I get the following error when I create it. It used to work before. Can anyone help? CREATE TABLE PROTOCOL_GROUPING( PROTOCOL_GROUPING_ID MEDIUMINT(20), GROUP_NAMEVARCHAR(50)NOT NULL, PROTOCOL_ID MEDIUMINT(20) NOT NULL, PROTOCOL_STEP_NO MEDIUMINT(10) NOT NULL, foreign key (PROTOCOL_ID, PROTOCOL_STEP_NO) REFERENCES PROTOCOL_STEP(PROTOCOL_ID, PROTOCOL_STEP_NO), UNIQUE INDEX PROTOCOL_GROUPING_PK (PROTOCOL_GROUPING_ID))Type=InnoDB; I get the following error: can't create table .\test\protocol_grouping.frm (errno 150) Oganes Demirchyan Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, February 04, 2002 11:02 AM To: Demirchyan Oganes-AOD098 Subject: Re: mysql create table error Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Guilhem, this is a known problem and mentioned in the Restrictions section of the InnoDB online manual. It is not the correct way to run SQL statements in the autocommit mode on the slave server, while the master has normal transaction processing on. I have asked Sasha to add COMMIT marks to the binlog, so that people would not see partial transactions on the slave server. I am sending a carbon copy of this message to Sasha. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. Description: As I have no test case, only strong presomptions, I sent this to [EMAIL PROTECTED] but got no feedback. I use MySQL-Max 4.0.1 with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) :BEGIN; UPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12;# I don't work in a bank !!COMMIT; then onlyUPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave :SELECT sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave.Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log.Am I misunderstanding something ? If this problem really exists, is it to be fixed ?Thanks anyway ! How-To-Repeat: This is not repeatable, it depends on the milliseconds I suppose Fix: Synopsis: BEGIN/COMMIT statements not written to the binary log Submitter-Id: submitter ID Originator: BICHOT Organization: your organization MySQL support: [none] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld]Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-4.0.1Exectutable: [mysqld-nt] Environment: machine descriptionSystem:Win2000 Compiler: VC++ 6.0Architecture: i586Guilhem BICHOTIPSN/DIR/SG/SI 01 46 54 92 31mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL RE: insertion problem !!
Chetan, the type of the column user_id is different in your two tables: in the parent table it is UNSIGNED, but signed in the child table. The internal storage format of a signed integer is different from an unsigned integer. That is why adding the child row fails. I changed now InnoDB-3.23.48 so that it gives an error in CREATE TABLE if the integer type signedness is not consistent in the foreign key declarations. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Chetan Lavti wrote in message ... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 2:22 PM To: Chetan Lavti Subject: Re: insertion problem !! Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: hi, I have two tables as:=20 please concentrate on domain_id and INDEX=20 Create table ls_domain ( domain_id INT(4) UNSIGNED AUTO_INCREMENT , domain_addr VARCHAR(80), domain_name CHAR(50) UNIQUE KEY, domain_port INT(6) UNSIGNED, domain_ip VARCHAR(15), created_by VARCHAR(40), created_date DATE, PRIMARY KEY (domain_id) ) TYPE=3DInnoDB; I have created and inserted values in it then.. Create table ls_user ( user_id INT(4) UNSIGNED AUTO_INCREMENT , domain_id INT(4), INDEX do_id (domain_id), user_name VARCHAR(30) UNIQUE KEY, user_flag CHAR(1) , user_passwd VARCHAR(8) , user_addr VARCHAR(100), user_email VARCHAR(50), created_by VARCHAR(30), created_date DATE, FOREIGN KEY (domain_id) REFERENCES ls_domain(domain_id), PRIMARY KEY (user_id) ) TYPE=3DInnoDB; It has also been created. Now when I am inserting values in ls_user it is giving error as mentioned below..( I have made sure that the value I am inserting for the 'domain_id' exists in the ls_domain table.) please tell me what could be the problem... INSERT INTO ls_user(ls_user,domain_id,user_name,user_flag,user_passwd,user_addr,user _email,created_by, created_date) VALUES(1,'Chetan Lavti','y','chetan','116-C,Mayur Vihar','[EMAIL PROTECTED]','Chetan Lavti','2002-02-07'); ERROR 1216: Cannot add a child row: a foreign key constraint fails Please, help me for the same... Thanks and regards, Chetan Lavti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB File Size
Hi! From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Harald Fuchs wrote in message ... (MySQL 4.0.1-alpha-Max-log, your Linux binary) The following works fine (just a slight variation of what is in the manual): CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; The following, however, crashes MySQL: CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; The log file says: InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 45067 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; I can send you the stack trace if necessary. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
test/t1 ( id ) -- TABLE: name test/t2, id 0 14, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t1id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t2, id 0 16, fields 1/4, type 3 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t1id INDEX: name t1id, table name test/t2, id 0 17, fields 1/2, type 0 root page 52, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t1id id FOREIGN KEY CONSTRAINT 0_15: test/t2 ( t1id ) REFERENCES test/t1 ( id ) FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) -- TABLE: name test/t3, id 0 16, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t2id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t3, id 0 18, fields 1/4, type 3 root page 53, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t2id INDEX: name t2id, table name test/t3, id 0 19, fields 1/2, type 0 root page 54, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t2id id FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) --- END OF INNODB TABLE MONITOR OUTPUT == -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, February 08, 2002 3:50 PM Subject: Re: InnoDB foreign keys crash MySQL In article 003b01c1b0a4$351665f0$540ec5c2@omnibook, Heikki Tuuri [EMAIL PROTECTED] writes: Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. I just tried it again on a fresh MySQL/InnoDB installation (a new database to be created etc) and it still crashes. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Before the crash: === 020208 14:42:39 INNODB TABLE MONITOR OUTPUT === -- TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type 3 root page 46, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type 0 root page 47, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type 0 root page 48, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: REF_NAME ID -- TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6, type 3 root page 49, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME -- TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1, appr.rows 0 COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id 0 20, fields 0/4, type 1 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a --- END OF INNODB TABLE MONITOR OUTPUT == No tables whatsoever
Re: InnoDB File Size
Hi! I just tried with 3.23.48 on Linux-2.4.16-SMP-64GB (ext2 I think), and a 5000 MB data file was created. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Gurupartap Davis [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Saturday, February 09, 2002 2:32 AM Subject: Re: InnoDB File Size Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
Harald, I was now able to repeat the error and fixed it. If one defines a non-latin1 character set as the default (german1 in your case), then the sorting order of the InnoDB internal datatype DATA_VARCHAR was undefined, and foreign key system tables did not work, because they contain DATA_VARCHAR columns. The fix probably makes it to upcoming MySQL-4.0.2, if Monty has not yet frozen the 4.0.2 codebase. It will also be in 3.23.49. Best regards, Heikki Innobase Oy -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Date: Saturday, February 09, 2002 3:59 PM Subject: Re: InnoDB foreign keys crash MySQL Harald, Hi, Heikki! ... I tested now with the official Linux binary of 4.0.1 (not -max) and it worked ok on our dual Xeon Linux-2.4.16-SMP-64GB. I did not define any InnoDB startup options in my.cnf. I just downloaded mysql-4.0.1-alpha-pc-linux-gnu-i686.tar.gz (now also not -max) from the German mirror and tried it. I still get the crash. My system is a 550MHz AMD K6-2 running kernel 2.2.18, if this should matter. I don't think this is a hardware bug: I tried the same script on another Linux box (dual 450MHz Pentium II, 2.2.18-SMP) running 4.0.1-max and also get the crash. When you recreated the InnoDB data files, did you remember to remove the .frm files of the InnoDB tables t1, t2, t3 in the database directory 'test'? Yes. Actually, I did rm -rf test/* ib* before starting MySQL. What is your my.cnf like? After removing all InnoDB directives, there's not much left: [client] port = 3306 [mysqld] port = 3306 user = mysql skip-locking set-variable = join_buffer_size=512k set-variable = key_buffer_size=64M set-variable = record_buffer=256k set-variable = sort_buffer=2M set-variable = table_cache=16 set-variable = tmp_table_size=32M log-slow-queries default-character-set=german1 The SQL command sequence causing the crash is as follows: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t1; Any other information I could supply? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign keys in InnoDB tables
Martin, there is a bug in 4.0.1 which can make a foreign key definition to fail in an assertion failure in dict0crea.c, if you have set default-character-set to something else than latin1 in my.cnf. Harald Fuchs reported the bug on this mailing list a couple of days ago, and the bug is now fixed in 4.0.2. Please send your my.cnf or my.ini to this mailing list, and post the exact sequence of SQL statements which in your opinion produces a wrong response. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Martin Bratbo wrote in message ... According to the manual it should in fact be possible to enforce foreign key constraint in MySql if both the referreing and referred tables are of type InnoDB. But I haven't been able to, make the foreign keys work, they did'nt blok any insertions. Are foreign keys still only for compability, or is there a way to actually make the constraints work if the tables are InnoDB ? I am running 4.0.1-alpha-max on win98 Regards Martin Bratbo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL/InnoDB-3.23.48 is released
Hi! InnoDB is a MySQL table type which adds transactions, row level locking, and foreign key constraints to MySQL. InnoDB is included in the MySQL-Max-3.23.48, MySQL-4.0, and MySQL-Max-4.0 distributions of MySQL. These can be downloaded from http://www.mysql.com. For more information about InnoDB see the online refererence manual at http://www.innodb.com/ibman.html. InnoDB version 3.23.48 contains some small improvements, and several bug fixes. Since the bug in SHOW CREATE TABLE could cause memory corruption and random crashes, especially in mysqldump, I recommend that InnoDB users upgrade to 3.23.48. The version 3.23.48 will be the first MySQL/InnoDB version which is officially declared as stable by Innobase Oy, unless some serious bug is reported from it. . MySQL/InnoDB-3.23.48 changelog February 9, 2002: * Tuned the SQL optimizer to favor more often index searches over table scans. * Fixed a performance problem when several large SELECT queries are run concurrently on a multiprocessor Linux computer. Large CPU-bound SELECT queries will now also generally run faster on all platforms. * If MySQL binlogging is used, InnoDB now prints after crash recovery the latest MySQL binlog name and the offset InnoDB was able to recover to. This is useful, for example, when resynchronizing a master and a slave database in replication. * Added better error messages to help in installation problems. * One can now recover also MySQL temporary tables which have become orphaned inside the InnoDB tablespace. * InnoDB now prevents a FOREIGN KEY declaration where the signedness is not the same in the referencing and referenced integer columns. * Fixed a bug: calling SHOW CREATE TABLE or SHOW TABLE STATUS could cause memory corruption and make mysqld to crash. Especially at risk was mysqldump, because it calls frequently SHOW CREATE TABLE. * Fixed a bug: if on Unix you did an ALTER TABLE to an InnoDB table and simultaneously did queries to it, mysqld could crash with an assertion failure in row0row.c, line 474. * Fixed a bug: if inserts to several tables containing an auto-inc column were wrapped inside one LOCK TABLES, InnoDB asserted in lock0lock.c. * In 3.23.47 we allowed several NULLS in a UNIQUE secondary index. But CHECK TABLE was not relaxed: it reports the table as corrupt. CHECK TABLE no longer complains in this situation. * Fixed a bug: on big-endian processors like Sparc SHOW VARIABLES showed boolean-values InnoDB startup parameters always off though they were switched on. . There is one known bug which was not yet fixed in 3.23.48. InnoDB foreign key constraints cannot be declared if you specify a non-latin1 character set as the default character set in my.cnf. A source code patch to this is available at http://www.innodb.com/foreign.txt. In the MySQL layer of code there were also some minor bug fixes. Please consult the MySQL-3.23.48 release note about those. Best regards, Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB File Size
Hi! From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Harald Fuchs wrote in message ... (MySQL 4.0.1-alpha-Max-log, your Linux binary) The following works fine (just a slight variation of what is in the manual): CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; The following, however, crashes MySQL: CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; The log file says: InnoDB: foreign constraint creation failed; InnoDB: internal error number 17 InnoDB: Assertion failure in thread 45067 in file dict0crea.c line 1241 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; I can send you the stack trace if necessary. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
test/t1 ( id ) -- TABLE: name test/t2, id 0 14, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t1id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t2, id 0 16, fields 1/4, type 3 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t1id INDEX: name t1id, table name test/t2, id 0 17, fields 1/2, type 0 root page 52, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t1id id FOREIGN KEY CONSTRAINT 0_15: test/t2 ( t1id ) REFERENCES test/t1 ( id ) FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) -- TABLE: name test/t3, id 0 16, columns 6, indexes 2, appr.rows 0 COLUMNS: id: DATA_INT len 4 prec 0; t2id: DATA_INT len 4 prec 0; DB_ROW_ID: DA TA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; D B_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, table name test/t3, id 0 18, fields 1/4, type 3 root page 53, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: id DB_TRX_ID DB_ROLL_PTR t2id INDEX: name t2id, table name test/t3, id 0 19, fields 1/2, type 0 root page 54, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: t2id id FOREIGN KEY CONSTRAINT 0_17: test/t3 ( t2id ) REFERENCES test/t2 ( id ) --- END OF INNODB TABLE MONITOR OUTPUT == -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, February 08, 2002 3:50 PM Subject: Re: InnoDB foreign keys crash MySQL In article 003b01c1b0a4$351665f0$540ec5c2@omnibook, Heikki Tuuri [EMAIL PROTECTED] writes: Harald, I tested the below with 3.23.48, and it worked ok. Could it be that you have not used DROP TABLE or DROP DATABASE to remove InnoDB tables? Then the internal data dictionary may be out-of-sync from the .frm files of your tables. I just tried it again on a fresh MySQL/InnoDB installation (a new database to be created etc) and it still crashes. Please use innodb_table_monitor (section 9.1 in http://www.innodb.com/ibman.html ) to print the contents of the internal data dictionary, and compare it to the .frm files you have for the tables. Please send the output to the mailing list. Before the crash: === 020208 14:42:39 INNODB TABLE MONITOR OUTPUT === -- TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN, id 0 11, fields 1/6, type 3 root page 46, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS INDEX: name FOR_IND, table name SYS_FOREIGN, id 0 12, fields 1/2, type 0 root page 47, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, table name SYS_FOREIGN, id 0 13, fields 1/2, type 0 root page 48, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: REF_NAME ID -- TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 0 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, table name SYS_FOREIGN_COLS, id 0 14, fields 2/6, type 3 root page 49, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME -- TABLE: name test/innodb_table_monitor, id 0 18, columns 5, indexes 1, appr.rows 0 COLUMNS: a: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name GEN_CLUST_INDEX, table name test/innodb_table_monitor, id 0 20, fields 0/4, type 1 root page 51, appr.key vals 0, leaf pages 1, size pages 1 FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a --- END OF INNODB TABLE MONITOR OUTPUT == No tables whatsoever
Re: InnoDB File Size
Hi! I just tried with 3.23.48 on Linux-2.4.16-SMP-64GB (ext2 I think), and a 5000 MB data file was created. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Gurupartap Davis [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Saturday, February 09, 2002 2:32 AM Subject: Re: InnoDB File Size Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL RE: insertion problem !!
Chetan, the type of the column user_id is different in your two tables: in the parent table it is UNSIGNED, but signed in the child table. The internal storage format of a signed integer is different from an unsigned integer. That is why adding the child row fails. I changed now InnoDB-3.23.48 so that it gives an error in CREATE TABLE if the integer type signedness is not consistent in the foreign key declarations. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Chetan Lavti wrote in message ... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 2:22 PM To: Chetan Lavti Subject: Re: insertion problem !! Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: hi, I have two tables as:=20 please concentrate on domain_id and INDEX=20 Create table ls_domain ( domain_id INT(4) UNSIGNED AUTO_INCREMENT , domain_addr VARCHAR(80), domain_name CHAR(50) UNIQUE KEY, domain_port INT(6) UNSIGNED, domain_ip VARCHAR(15), created_by VARCHAR(40), created_date DATE, PRIMARY KEY (domain_id) ) TYPE=3DInnoDB; I have created and inserted values in it then.. Create table ls_user ( user_id INT(4) UNSIGNED AUTO_INCREMENT , domain_id INT(4), INDEX do_id (domain_id), user_name VARCHAR(30) UNIQUE KEY, user_flag CHAR(1) , user_passwd VARCHAR(8) , user_addr VARCHAR(100), user_email VARCHAR(50), created_by VARCHAR(30), created_date DATE, FOREIGN KEY (domain_id) REFERENCES ls_domain(domain_id), PRIMARY KEY (user_id) ) TYPE=3DInnoDB; It has also been created. Now when I am inserting values in ls_user it is giving error as mentioned below..( I have made sure that the value I am inserting for the 'domain_id' exists in the ls_domain table.) please tell me what could be the problem... INSERT INTO ls_user(ls_user,domain_id,user_name,user_flag,user_passwd,user_addr,user _email,created_by, created_date) VALUES(1,'Chetan Lavti','y','chetan','116-C,Mayur Vihar','[EMAIL PROTECTED]','Chetan Lavti','2002-02-07'); ERROR 1216: Cannot add a child row: a foreign key constraint fails Please, help me for the same... Thanks and regards, Chetan Lavti - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Guilhem, this is a known problem and mentioned in the Restrictions section of the InnoDB online manual. It is not the correct way to run SQL statements in the autocommit mode on the slave server, while the master has normal transaction processing on. I have asked Sasha to add COMMIT marks to the binlog, so that people would not see partial transactions on the slave server. I am sending a carbon copy of this message to Sasha. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. Description: As I have no test case, only strong presomptions, I sent this to [EMAIL PROTECTED] but got no feedback. I use MySQL-Max 4.0.1 with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) :BEGIN; UPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12;# I don't work in a bank !!COMMIT; then onlyUPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave :SELECT sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave.Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log.Am I misunderstanding something ? If this problem really exists, is it to be fixed ?Thanks anyway ! How-To-Repeat: This is not repeatable, it depends on the milliseconds I suppose Fix: Synopsis: BEGIN/COMMIT statements not written to the binary log Submitter-Id: submitter ID Originator: BICHOT Organization: your organization MySQL support: [none] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld]Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-4.0.1Exectutable: [mysqld-nt] Environment: machine descriptionSystem:Win2000 Compiler: VC++ 6.0Architecture: i586Guilhem BICHOTIPSN/DIR/SG/SI 01 46 54 92 31mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB foreign keys crash MySQL
Harald, I was now able to repeat the error and fixed it. If one defines a non-latin1 character set as the default (german1 in your case), then the sorting order of the InnoDB internal datatype DATA_VARCHAR was undefined, and foreign key system tables did not work, because they contain DATA_VARCHAR columns. The fix probably makes it to upcoming MySQL-4.0.2, if Monty has not yet frozen the 4.0.2 codebase. It will also be in 3.23.49. Best regards, Heikki Innobase Oy -Original Message- From: Harald Fuchs [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Date: Saturday, February 09, 2002 3:59 PM Subject: Re: InnoDB foreign keys crash MySQL Harald, Hi, Heikki! ... I tested now with the official Linux binary of 4.0.1 (not -max) and it worked ok on our dual Xeon Linux-2.4.16-SMP-64GB. I did not define any InnoDB startup options in my.cnf. I just downloaded mysql-4.0.1-alpha-pc-linux-gnu-i686.tar.gz (now also not -max) from the German mirror and tried it. I still get the crash. My system is a 550MHz AMD K6-2 running kernel 2.2.18, if this should matter. I don't think this is a hardware bug: I tried the same script on another Linux box (dual 450MHz Pentium II, 2.2.18-SMP) running 4.0.1-max and also get the crash. When you recreated the InnoDB data files, did you remember to remove the .frm files of the InnoDB tables t1, t2, t3 in the database directory 'test'? Yes. Actually, I did rm -rf test/* ib* before starting MySQL. What is your my.cnf like? After removing all InnoDB directives, there's not much left: [client] port = 3306 [mysqld] port = 3306 user = mysql skip-locking set-variable = join_buffer_size=512k set-variable = key_buffer_size=64M set-variable = record_buffer=256k set-variable = sort_buffer=2M set-variable = table_cache=16 set-variable = tmp_table_size=32M log-slow-queries default-character-set=german1 The SQL command sequence causing the crash is as follows: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t3; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) TYPE=InnoDB; CREATE TABLE t2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t1id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t1id), FOREIGN KEY (t1id) REFERENCES t1(id) ) TYPE=InnoDB; CREATE TABLE t3 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, t2id INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY (t2id), FOREIGN KEY (t2id) REFERENCES t2(id) ) TYPE=InnoDB; DROP TABLE IF EXISTS t3; DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t1; Any other information I could supply? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL/InnoDB-3.23.48 is released
Hi! InnoDB is a MySQL table type which adds transactions, row level locking, and foreign key constraints to MySQL. InnoDB is included in the MySQL-Max-3.23.48, MySQL-4.0, and MySQL-Max-4.0 distributions of MySQL. These can be downloaded from http://www.mysql.com. For more information about InnoDB see the online refererence manual at http://www.innodb.com/ibman.html. InnoDB version 3.23.48 contains some small improvements, and several bug fixes. Since the bug in SHOW CREATE TABLE could cause memory corruption and random crashes, especially in mysqldump, I recommend that InnoDB users upgrade to 3.23.48. The version 3.23.48 will be the first MySQL/InnoDB version which is officially declared as stable by Innobase Oy, unless some serious bug is reported from it. . MySQL/InnoDB-3.23.48 changelog February 9, 2002: * Tuned the SQL optimizer to favor more often index searches over table scans. * Fixed a performance problem when several large SELECT queries are run concurrently on a multiprocessor Linux computer. Large CPU-bound SELECT queries will now also generally run faster on all platforms. * If MySQL binlogging is used, InnoDB now prints after crash recovery the latest MySQL binlog name and the offset InnoDB was able to recover to. This is useful, for example, when resynchronizing a master and a slave database in replication. * Added better error messages to help in installation problems. * One can now recover also MySQL temporary tables which have become orphaned inside the InnoDB tablespace. * InnoDB now prevents a FOREIGN KEY declaration where the signedness is not the same in the referencing and referenced integer columns. * Fixed a bug: calling SHOW CREATE TABLE or SHOW TABLE STATUS could cause memory corruption and make mysqld to crash. Especially at risk was mysqldump, because it calls frequently SHOW CREATE TABLE. * Fixed a bug: if on Unix you did an ALTER TABLE to an InnoDB table and simultaneously did queries to it, mysqld could crash with an assertion failure in row0row.c, line 474. * Fixed a bug: if inserts to several tables containing an auto-inc column were wrapped inside one LOCK TABLES, InnoDB asserted in lock0lock.c. * In 3.23.47 we allowed several NULLS in a UNIQUE secondary index. But CHECK TABLE was not relaxed: it reports the table as corrupt. CHECK TABLE no longer complains in this situation. * Fixed a bug: on big-endian processors like Sparc SHOW VARIABLES showed boolean-values InnoDB startup parameters always off though they were switched on. . There is one known bug which was not yet fixed in 3.23.48. InnoDB foreign key constraints cannot be declared if you specify a non-latin1 character set as the default character set in my.cnf. A source code patch to this is available at http://www.innodb.com/foreign.txt. In the MySQL layer of code there were also some minor bug fixes. Please consult the MySQL-3.23.48 release note about those. Best regards, Heikki Tuuri Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Static or Dynamic rows -- which is faster?
Hi! -Original Message- From: David Felio [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Date: Tuesday, February 12, 2002 12:47 AM Subject: Re: Static or Dynamic rows -- which is faster? On Monday, February 11, 2002, at 12:12 PM, Heikki Tuuri wrote: for InnoDB 'dynamic rows', that is, rows where you define char columns as VARCHAR, are faster because tables and indexes fit in smaller space. Is there reasoning specific to InnoDB? Dynamic tables would be smaller regardless of the table type, I would think. In Kaj Arno's talk on optimizing MySQL at the O'Reilly Open Source Convention last year, he said that fixed tables are preferable and that if you must have dynamic columns, you should try to put them in a table separate from your static columns (slides 67 and 68 for those who have them). Has this changed? I am no expert on MyISAM, but I guess also there it is better to define VARCHAR(255) rather than CHAR(255), because in big tables disk i/o is often the bottleneck. I have understood MyISAM searches are faster if the row format is 'fixed length', but I doubt that this offsets the increased i/o. http://www.mysql.com/doc/D/y/Dynamic_format.html : You can use OPTIMIZE table or myisamchk to defragment a table. If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation: For InnoDB, VARCHAR is almost always better than CHAR, except in rare cases where you want to avoid fragmentation caused by updates which change a field length. David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Static or Dynamic rows -- which is faster?
Eric, for InnoDB 'dynamic rows', that is, rows where you define char columns as VARCHAR, are faster because tables and indexes fit in smaller space. Only in some rare cases where you want to avoid fragmentation caused by updates which change a column length, a fixed-length CHAR(...) column can be a better option. I guess also for MyISAM dynamic rows are usually faster, because you win in smaller disk i/o. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Eric Mayers wrote in message ... I have a large data set (15 mil rows) consisting of a datetime column and a char(255) column. I seem to recall seeing something about performance benefits for using static length rows with MyISAM tables, and I've heard some argument for using dynamic length rows (e.g., use varchar rather than char) in InnoDB tables. So for performance sake (assuming no index can be used), which is better for these table types? I can imagine that using dynamic length would be better in the case of an IO bottleneck, but maybe there is an efficiency gain from knowing where the records start and stop ahead of time..? Thanks, Eric uugh. sql. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Guilhem, this is a known problem and mentioned in the Restrictions section of the InnoDB online manual. It is not the correct way to run SQL statements in the autocommit mode on the slave server, while the master has normal transaction processing on. I have asked Sasha to add COMMIT marks to the binlog, so that people would not see partial transactions on the slave server. I am sending a carbon copy of this message to Sasha. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. Description: As I have no test case, only strong presomptions, I sent this to [EMAIL PROTECTED] but got no feedback. I use MySQL-Max 4.0.1 with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) :BEGIN; UPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12;# I don't work in a bank !!COMMIT; then onlyUPDATE accounts set sum=sum-100 where num=10; UPDATE accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave :SELECT sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave.Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log.Am I misunderstanding something ? If this problem really exists, is it to be fixed ?Thanks anyway ! How-To-Repeat: This is not repeatable, it depends on the milliseconds I suppose Fix: Synopsis: BEGIN/COMMIT statements not written to the binary log Submitter-Id: submitter ID Originator: BICHOT Organization: your organization MySQL support: [none] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld]Class: [ sw-bug | doc-bug | change-request | support ] Release: mysql-4.0.1Exectutable: [mysqld-nt] Environment: machine descriptionSystem:Win2000 Compiler: VC++ 6.0Architecture: i586Guilhem BICHOTIPSN/DIR/SG/SI 01 46 54 92 31mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does delete from .. where (condition) use an index?
Eric, MySQL is very pessimistic about key accesses: it assumes many of them cause a random disk read. Please print EXPLAIN SELECT * FROM Syslog WHERE datestamp ... to see how it accesses the table. I have tuned the optimization in 3.23.48 so that it would favor index searches more often. Please try also with 3.23.48. Regards, Heikki Innobase Oy Eric Mayers wrote in message ... I haven't been able to find details about how/if MySQL (InnoDB) optimizes deletes. Does it use indexes? Can I force it to use an index? My table is defined as: CREATE TABLE Syslog( id int(11) not null, datestamp timestamp(14), message char(255), KEY ds_index(datestamp), KEY id_index(id) ) type=InnoDB; There are about 15 mil rows, and I'm just trying to delete a small portion of them (0.05% perhaps). My delete looks like: DELETE FROM Syslog WHERE datestamp = 2002021310712; (the magic number there comes from SELECT (min(datestamp) + 30) AS min FROM Syslog in a previous query). .. and its taking a very long time.. watching the innodb monitor I'm seeing a large number of reads/s (29000) and a small number of deletes/s (10) which makes me believe its not using the index.. Any way to improve this? Eric Mayers Software Engineer I - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does delete from .. where (condition) use an index?
Eric, print what EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE ... says. Please also show what the InnoDB monitor prints. Regards, Heikki -Original Message- From: Eric Mayers [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 14, 2002 3:44 AM Subject: RE: Does delete from .. where (condition) use an index? Heikki, mysql EXPLAIN SELECT * FROM Syslog WHERE datestamp = 2002021310712; ++--+---+--+-+--+-+- ---+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+- ---+ | Syslog | ALL | ds_index | NULL |NULL | NULL | 2204932 | where used | ++--+---+--+-+--+-+- ---+ 1 row in set (0.00 sec) How can I force it to use the index? (the indexes look like: mysql show index from Syslog; +++--+--+-+- --+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+- --+-+--++-+ | Syslog | 1 | ds_index |1 | datestamp | A |5893 | NULL | NULL | | | Syslog | 1 | ca_index |1 | caid| A |9730 | NULL | NULL | | +++--+--+-+- --+-+--++-+ 2 rows in set (6.90 sec) ) Thanks, Eric -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 5:34 PM To: [EMAIL PROTECTED] Subject: Re: Does delete from .. where (condition) use an index? Eric, MySQL is very pessimistic about key accesses: it assumes many of them cause a random disk read. Please print EXPLAIN SELECT * FROM Syslog WHERE datestamp ... to see how it accesses the table. I have tuned the optimization in 3.23.48 so that it would favor index searches more often. Please try also with 3.23.48. Regards, Heikki Innobase Oy Eric Mayers wrote in message ... I haven't been able to find details about how/if MySQL (InnoDB) optimizes deletes. Does it use indexes? Can I force it to use an index? My table is defined as: CREATE TABLE Syslog( id int(11) not null, datestamp timestamp(14), message char(255), KEY ds_index(datestamp), KEY id_index(id) ) type=InnoDB; There are about 15 mil rows, and I'm just trying to delete a small portion of them (0.05% perhaps). My delete looks like: DELETE FROM Syslog WHERE datestamp = 2002021310712; (the magic number there comes from SELECT (min(datestamp) + 30) AS min FROM Syslog in a previous query). .. and its taking a very long time.. watching the innodb monitor I'm seeing a large number of reads/s (29000) and a small number of deletes/s (10) which makes me believe its not using the index.. Any way to improve this? Eric Mayers Software Engineer I - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does delete from .. where (condition) use an index?
Hi! I tested with .48 and it seems that if the estimator thinks you are going to delete 10 % of the rows in your table, it scans the whole table. In .47 this % limit is smaller, because I changed .48 to favor more index searches over table scans. But I tested with a very uniform table. Your table is less uniform and the estimator may be less accurate. Make sure you commit your transactions often, because only then purge can remove delete marked records from indexes. If purge cannot run, you may have 10 % of records delete marked but not yet removed at the old end of the index. That will mislead the estimator. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Eric Mayers [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 14, 2002 3:44 AM Subject: RE: Does delete from .. where (condition) use an index? Heikki, mysql EXPLAIN SELECT * FROM Syslog WHERE datestamp = 2002021310712; ++--+---+--+-+--+-+- ---+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+- ---+ | Syslog | ALL | ds_index | NULL |NULL | NULL | 2204932 | where used | ++--+---+--+-+--+-+- ---+ 1 row in set (0.00 sec) How can I force it to use the index? (the indexes look like: mysql show index from Syslog; +++--+--+-+- --+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+- --+-+--++-+ | Syslog | 1 | ds_index |1 | datestamp | A |5893 | NULL | NULL | | | Syslog | 1 | ca_index |1 | caid| A |9730 | NULL | NULL | | +++--+--+-+- --+-+--++-+ 2 rows in set (6.90 sec) ) Thanks, Eric -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 5:34 PM To: [EMAIL PROTECTED] Subject: Re: Does delete from .. where (condition) use an index? Eric, MySQL is very pessimistic about key accesses: it assumes many of them cause a random disk read. Please print EXPLAIN SELECT * FROM Syslog WHERE datestamp ... to see how it accesses the table. I have tuned the optimization in 3.23.48 so that it would favor index searches more often. Please try also with 3.23.48. Regards, Heikki Innobase Oy Eric Mayers wrote in message ... I haven't been able to find details about how/if MySQL (InnoDB) optimizes deletes. Does it use indexes? Can I force it to use an index? My table is defined as: CREATE TABLE Syslog( id int(11) not null, datestamp timestamp(14), message char(255), KEY ds_index(datestamp), KEY id_index(id) ) type=InnoDB; There are about 15 mil rows, and I'm just trying to delete a small portion of them (0.05% perhaps). My delete looks like: DELETE FROM Syslog WHERE datestamp = 2002021310712; (the magic number there comes from SELECT (min(datestamp) + 30) AS min FROM Syslog in a previous query). .. and its taking a very long time.. watching the innodb monitor I'm seeing a large number of reads/s (29000) and a small number of deletes/s (10) which makes me believe its not using the index.. Any way to improve this? Eric Mayers Software Engineer I - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does delete from .. where (condition) use an index?
Eric, thank you for the printouts. Looks like MySQL refused to obey the USE INDEX clause! Did you run the EXPLAIN on a very small table? Otherwise I do not understand why it reports only 4700 rows. You have a very long-running mass delete below. It has row locks on 13000 pages = 200 MB. It has delete marked (= deleted) 650 000 rows (= number of undo log entries). There are quite a lot of disk reads and writes per second: 23 + 28. The load is probably disk-bound. You have a very small buffer pool, only 1024 pages = 16 MB. I assume this is a stress test for a very small buffer pool. There are no dangling open transactions: only the mass delete and a single insert exist. In short, everything looks ok in the monitor output. I think it is worth to try 3.23.48 because the optimizer is tuned there. I tested deleting 15 000 rows from a 150 000 row table, and .48 chose to use the right index. Make sure the delete batches are small enough so that the optimizer picks the right index. EXPLAIN SELECT is the way to study optimizer choices. Note that because of the insert buffer, InnoDB can make inserts with less disk i/o than deletes. In the monitor output you see 9 000 000 insert buffer records were merged in 700 000 merges: on the average 13 records were inserted at a time. Deleting these records will use more disk i/o because there is no similar optimization in deletes. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Eric Mayers [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 14, 2002 7:28 PM Subject: RE: Does delete from .. where (condition) use an index? Heikki, Here is what you asked for: mysql EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp = 20020213185230; ++--+---+--+-+--+--+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+--+ + | Syslog | ALL | ds_index | NULL |NULL | NULL | 4719 | where used | ++--+---+--+-+--+--+ + 1 row in set (0.00 sec) And here is a single InnoDB Monitor output: = 020214 9:07:25 INNODB MONITOR OUTPUT = -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 85376, signal count 83573 Mutex spin waits 107640, rounds 1033422, OS waits 1 RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits 34400 TRANSACTIONS Trx id counter 0 10816708 Purge done for trx's n:o 0 475530 undo n:o 0 0 Total number of lock structs in row lock hash table 13009 ---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or sleeps MySQL thread id 205, query id 12109132 localhost root INSERT INTO logs.Syslog set caid='630', datestamp=NULL, message='4187481481 1481418748 418748 418748 ---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting, active, runs or sleeps, has 13010 lock struct(s), undo log entries 650408 MySQL thread id 344, query id 6056015 localhost root updating DELETE FROM logs.Syslog where (datestamp = (20020213190141 + (3600))) 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: 1 1164459 OS file reads, 1266570 OS file writes, 100823 OS fsyncs 23.25 reads/s, 27.88 writes/s, 2.06 fsyncs/s - INSERT BUFFER - Ibuf for space 0: size 518, free list len 272, seg size 791, 9152117 inserts, 8984010 merged recs, 675487 merges --- LOG --- Log sequence number 0 4190770295 Log flushed up to 0 4190657162 Last checkpoint at 0 4178363488 1 pending log writes, 0 pending chkp writes 52481 log i/o's done, 0.88 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 33029216; in additional pool allocated 269312 Free list length 121 LRU list length 856 Flush list length 805 Buffer pool size 1024 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1256311, created 255856, written 1602244 24.31 reads/s, 3.88 creates/s, 36.00 writes/s Buffer pool hit rate 994 / 1000 -- ROW OPERATIONS -- 1 queries inside InnoDB; main thread: flushing log Number of rows inserted 10806106, updated 0, deleted 806508, read 6850315 181.44 inserts/s, 0.00 updates/s, 11.69 deletes/s, 11.69 reads/s END OF INNODB MONITOR OUTPUT Eric
Re: Undefined reference to 'btr_search_check_guess' ??
Hi! January 12, 2002: There is a bug in some GCC compiler versions and consequently compilation of the function btr_search_check_guess fails. Fix: replace the declaration 'UNIV_INLINE' in that function in mysql/innobase/btr/btr0sea.c by the declaration 'static'. Fixed in 3.23.48. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Lou Picciano wrote in message ... Dear Listers: In configuring for a 4.0.1 compile, all goes well. The compiler, however, reports (apparently in the InnoBase section of the compile): ../innobase/btr/libbtr.a(btr0sea.o): In nunction 'btr_search_guess_on_hash': btr0sea.o(.text+0x1c58): undefined reference to 'btr_search_check_guess' collect2: ld returned 1 exit status make[3]: *** [mysqld] Error 1 make[3]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha/sql' make[2]: *** [all-recursive] Error 1 make[2]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha/sql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory '/usr/local/src/mysql-4.0.1-alpha make: *** [all-recursive-am] Error 2 I am no expert in using gcc, so have no idea how to recover from these messages. Does anyone have a hint? Thanks - Lou - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does delete from .. where (condition) use an index?
Eric, -Original Message- From: Eric Mayers [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Date: Friday, February 15, 2002 1:10 AM Subject: RE: Does delete from .. where (condition) use an index? Heikki, I thought I'd bring this off the list to reduce list traffic (if you think its useful please feel free to respond on the list). I only used I respond to the mailing list because I think this is useful info for other users. the USE INDEX syntax in the EXPLAIN SELECT ... statement, not in the delete. It gives me a syntax error if I include USE INDEX in a DELETE statement... DELETE FROM SYSLOG USE INDEX(ds_index) WHERE datestamp 37827382; Is this the wrong syntax? Can I tell it to use the index? Sorry, no. There is no such syntax for DELETE FROM. ... I'm working on getting more memory in the box so I can increase the size of the buffer pool considerably. I've also put a limit on the delete statement and put it in a loop checking affected rows and that seems to have helped signifigantly A great idea, which did not come to my mind: using the LIMIT clause in DELETE FROM you can easily control the size of deletions! Again, thanks for your help! Best Regards, Eric Mayers Software Engineer I Captus Networks Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 14, 2002 1:23 PM To: Eric Mayers; [EMAIL PROTECTED] Subject: Re: Does delete from .. where (condition) use an index? Eric, thank you for the printouts. Looks like MySQL refused to obey the USE INDEX clause! Did you run the EXPLAIN on a very small table? Otherwise I do not understand why it reports only 4700 rows. You have a very long-running mass delete below. It has row locks on 13000 pages = 200 MB. It has delete marked (= deleted) 650 000 rows (= number of undo log entries). There are quite a lot of disk reads and writes per second: 23 + 28. The load is probably disk-bound. You have a very small buffer pool, only 1024 pages = 16 MB. I assume this is a stress test for a very small buffer pool. There are no dangling open transactions: only the mass delete and a single insert exist. In short, everything looks ok in the monitor output. I think it is worth to try 3.23.48 because the optimizer is tuned there. I tested deleting 15 000 rows from a 150 000 row table, and .48 chose to use the right index. Make sure the delete batches are small enough so that the optimizer picks the right index. EXPLAIN SELECT is the way to study optimizer choices. Note that because of the insert buffer, InnoDB can make inserts with less disk i/o than deletes. In the monitor output you see 9 000 000 insert buffer records were merged in 700 000 merges: on the average 13 records were inserted at a time. Deleting these records will use more disk i/o because there is no similar optimization in deletes. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Eric Mayers [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 14, 2002 7:28 PM Subject: RE: Does delete from .. where (condition) use an index? Heikki, Here is what you asked for: mysql EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp = 20020213185230; ++--+---+--+-+--+ --+ + | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+ --+ + | Syslog | ALL | ds_index | NULL |NULL | NULL | 4719 | where used | ++--+---+--+-+--+ --+ + 1 row in set (0.00 sec) And here is a single InnoDB Monitor output: = 020214 9:07:25 INNODB MONITOR OUTPUT = -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 85376, signal count 83573 Mutex spin waits 107640, rounds 1033422, OS waits 1 RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits 34400 TRANSACTIONS Trx id counter 0 10816708 Purge done for trx's n:o 0 475530 undo n:o 0 0 Total number of lock structs in row lock hash table 13009 ---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or sleeps MySQL thread id 205, query id 12109132 localhost root INSERT INTO logs.Syslog set caid='630', datestamp=NULL, message='4187481481 1481418748 418748 418748 ---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting
Re: InnoDB create table error 150
Hi! It is a bug in the FOREIGN KEY syntax parser. It is confused by the column name e_foreigncurrency The bug appears if the string 'foreign' is succeeded by a non-space character in a CREATE TABLE statement. Workaround: change the column name to e_currencyforeign, for example. Fixed in 3.23.50. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB Richard R. Harms wrote in message ... The create table command included at the end of this message gives the following error: ERROR 1005: Can't create table './catalogmgr/macitm_vendordfrom.frm' (errno: 150) Creating it with type=myisam, and then doing an alter table and converting it to innodb works okay. It just cannot be initially created as an innodb table. The error occurs on both Red Hat Linux 6.2 and Mac OS X 10.1.2. MySQL was compiled on both systems with the --enable-raid flag. my.cnf settings are included below as well. =rh CREATE TABLE macitm_vendordfrom ( e_vendorno varchar(10) default NULL, e_apvendor varchar(20) default NULL, e_company char(2) default NULL, e_division char(2) default NULL, e_name varchar(30) default NULL, e_lastname varchar(16) default NULL, e_initial char(2) default NULL, e_firstname varchar(16) default NULL, e_title char(2) default NULL, e_ref1 varchar(30) default NULL, e_ref2 varchar(30) default NULL, e_street varchar(30) default NULL, e_city varchar(30) default NULL, e_state char(2) default NULL, e_zip varchar(10) default NULL, e_countrycode varchar(4) default NULL, e_dayphone varchar(16) default NULL, e_freightppcode char(2) default NULL, e_freightppbasis bigint(10) default NULL, e_freightpct int(5) default NULL, e_fobcity varchar(30) default NULL, e_foreigncurrency char(2) default NULL, e_termscode varchar(4) default NULL, e_termspct int(4) default NULL, e_termsdays int(5) default NULL, e_standarddays int(5) default NULL, e_faxno varchar(16) default NULL, e_miscdata40 varchar(40) default NULL, e_vendorcomments001 varchar(60) default NULL, e_vendorcomments002 varchar(60) default NULL, e_vendorcomments003 varchar(60) default NULL, e_vendorcomments004 varchar(60) default NULL, e_vendorcomments005 varchar(60) default NULL, e_vendorcomments006 varchar(60) default NULL, e_vendorcomments007 varchar(60) default NULL, e_vendorcomments008 varchar(60) default NULL, e_addldata varchar(20) default NULL, e_email varchar(48) default NULL ) TYPE=InnoDB; [mysqld] set-variable = delayed_queue_size=10 set-variable = sort_buffer=8M set-variable = join_buffer=4M set-variable = tmp_table_size=4M set-variable = max_sort_length=16384 set-variable = max_allowed_packet=1M set-variable = query_cache_size=2M set-variable = query_cache_startup_type=1 # innodb_data_home_dir = /usr/local/var/.ibdata #Data files must be able to #hold your data and indexes innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M #Set buffer pool size to 50 - 80 % #of your computer's memory set-variable = innodb_buffer_pool_size=32M set-variable = innodb_additional_mem_pool_size=8M # innodb_log_group_home_dir = /usr/local/var/.iblogs #.._log_arch_dir must be the same #as .._log_group_home_dir innodb_log_arch_dir = /usr/local/var/.iblogs innodb_log_archive=0 set-variable = innodb_log_files_in_group=3 #Set the log file size to about #15 % of the buffer pool size set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M #Set ..flush_log_at_trx_commit to #0 if you can afford losing #a few last transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Foreign keys in InnoDB tables
Martin, -Original Message- From: Martin Bratbo [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Saturday, February 16, 2002 4:46 PM Subject: Re: Re: Foreign keys in InnoDB tables Heikki here is the statements that i cant get to work: first I create one innoDB table: fk1 create table fk1( noegle integer primary key, tekst varchar(20))type=InnoDB; then I create a second InnoDB table: fk2which references the first: create table fk2( prim integer primary key, frem integer, Foreign key fk (frem) references fk1(noegle))type=InnoDB; you have a syntax error here: after FOREIGN KEY you have a symbol 'fk'. MySQL+InnoDB ignores the constraint. The syntax of a foreign key constraint definition in InnoDB: FOREIGN KEY (index_col_name, ...) REFERENCES table_name (index_col_name, ...) Note that you should not use quoted table or column names in a FOREIGN KEY clause. The InnoDB parser does not currently know that notation. An example: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB I then insert a tuple into the first table insert into fk1 values (1,'xx'); no problem then I insert a tuple into the second table a tuble that shoud have been rejected because it violates the referntial integrity insert into fk1 values (1,'xx'); but this tuple is accepted and inserted into fk2 it seem that the foreig keyconstraint doesn't do anythig even thoug both tabels are InnoDB tables, is the any settings that is neccesarry to activate the constraint ? my my.ini file is as follows: [mysqld] basedir=d:/mysql datadir=d:/mysql/data innodb_data_file_path=ibdata1:500M;ibdata2:500M innodb_data_home_dir=D:\mysql\innodb set-variable=innodb_mirrored_log_groups=1 innodb_log_group_home_dir=D:\mysql\iblogs set-variable=innodb_log_files_in_group=3 set-variable=innodb_log_file_size=30M set-variable=innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 #.._arch_dir must be the same as .._log_group_home_dir innodb_log_arch_dir=D:\mysql\iblogs innodb_log_archive=0 set-variable=innodb_buffer_pool_size=70M #454 MySQL Technical Reference for Version 4.0.1-alpha set-variable=innodb_additional_mem_pool_size=10M set-variable=innodb_file_io_threads=4 set-variable=innodb_lock_wait_timeout=50 Regards Martin, there is a bug in 4.0.1 which can make a foreign key definition to fail in an assertion failure in dict0crea.c, if you have set default-character-set to something else than latin1 in my.cnf. Harald Fuchs reported the bug on this mailing list a couple of days ago, and the bug is now fixed in 4.0.2. Please send your my.cnf or my.ini to this mailing list, and post the exact sequence of SQL statements which in your opinion produces a wrong response. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Martin Bratbo wrote in message ... According to the manual it should in fact be possible to enforce foreign key constraint in MySql if both the referreing and referred tables are of type InnoDB. But I haven't been able to, make the foreign keys work, they did'nt blok any insertions. Are foreign keys still only for compability, or is there a way to actually make the constraints work if the tables are InnoDB ? I am running 4.0.1-alpha-max on win98 Regards Martin Bratbo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: operating system error number 3
Loretta, please look in the manual http://www.innodb.com/ibman.html : If InnoDB prints an operating system error in a file operation, look from section 13.2 what that error code means. Usually the problem is one of the following: You did not create InnoDB data or log directories. mysqld does not have the rights to create files in those directories. mysqld does not read the right my.cnf or my.ini file, and consequently does not see the options you specified. The disk is full or a disk quota is exceeded. You have created a subdirectory whose name is equal to a data file you specified. There is a syntax error in innodb_data_home_dir or innodb_data_file_path. If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. 3 The system cannot find the path specified. ERROR_PATH_NOT_FOUND Error number 3 means you have not created the InnoDB data or log directories. Please use the MS-DOS mkdir command to create the directories. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Loretta [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, February 17, 2002 6:27 AM Subject: operating system error number 3 Hello everyone: I am hoping you can help me. I have Windows 98 SE as my operating system. I have installed MySQL 4.0.1. I originally had Win ME and MySQL 3.23.48 but could not make them work together. Hopefully, someone will be able to help me. The error I am getting is InnoDB: Warning: operating system error number 3 in a file operation. InnoDB: Cannot continue operation. I have never used MySQL in my life before this. I have read the manual and done an online search to figure out how to fix this error but have had no luck. If you are able to help me please be detailed in how I am to fix this problem. Any help will be appreciated. I am trying to develop this database for a Church to use. Thank-you, Loretta - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL/InnoDB-3.23.49 is released
Hi! InnoDB is a MySQL table type which adds transactions, row level locking, and foreign key constraints to MySQL. InnoDB is included in the MySQL-Max-3.23, MySQL-4.0, and MySQL-Max-4.0 distributions of MySQL. These can be downloaded from http://www.mysql.com. For more information about InnoDB see the online refererence manual at http://www.innodb.com. MySQL AB decided to release 3.23.49 so quickly after .48 because the Linux binary of .48 was linked against a wrong glibc, and it did not work properly in some computers. There are the following bug fixes in InnoDB: * Fixed a bug: if you called DROP DATABASE for a database on which there simultaneously were running queries, the MySQL server could crash or hang. Crashes fixed, but a full fix has to wait some changes in the MySQL layer of code. * Fixed a bug: on Windows one had to put the database name in lower case for DROP DATABASE to work. Fixed in 3.23.49: case no longer matters on Windows. On Unix the database name remains case-sensitive. * Fixed a bug: if one defined a non-latin1 character set as the default character set, then definition of foreign key constraints could fail in an assertion failure in dict0crea.c, reporting an internal error 17. In the MySQL part of code the changelog is the following: http://www.mysql.com/doc/N/e/News-3.23.49.html : * Don't give warning for statement that is only a comment; This is needed for mysqldump --disable-keys to work. * Fixed unlikely caching bug when doing a join without keys. In this case the last used field for a table always returned NULL. * Added options to make LOAD DATA LOCAL INFILE more secure. * MySQL binary release 3.23.48 for Linux contained a new glibc library, which has serious problems under high load and RedHat 7.2. The 3.23.49 binary release doesn't have this problem. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL off-the-shelf product
Hi! I believe NuSphere does not distribute MySQL-Max-3.23. Tergat MySQL Studio contains MySQL-Max-3.23.47. Do you literally need an 'off-the-shelf' product? Tergat is available as a downloadable version for $66. The physical boxset version is not available yet, I think. ... http://www.mysqlstudio.com/ Tergat MySQL Studio contents: - Navicat (Version 4.1) - Tergat MySQL launcher (Version 3.1) - Tergat MySQL installer - MySQL database server (MySQL Max 3.23.47) - Apache web server (Apache 1.3.20) - PHP scripting languages (PHP 4.0.6) - Comprehensive documentation Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Nick Wilson [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Wednesday, February 20, 2002 9:17 AM Subject: Re: MySQL off-the-shelf product -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 * and then Demirchyan Oganes-AOD098 declared Would someone know of off-the-self MySQL product that will contain latest MySQL server in it? Something like 3.23.46a max-nt and above. I don't care what it's bundled with, PHP or perl. I just need to use the features of MySQL only. The reason I need it, is for FDA (Food and Drug Administration) standards approval. Try nusphere.com, I believe they might have what you're after. - -- - --- www.explodingnet.com |Projects, Forums and +Articles for website owners - -- Nick Wilson -- |and designers. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) iD8DBQE8c029HpvrrTa6L5oRAv0KAKCHcho4UkbYNGUEYEwz7A+4WGrDEwCffSRe Xe8bDDQbSkUwyJn5a6m7RHs= =m5b9 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: -fno-implicit-templates, -fno-exceptions,-fno-rtti problems
Hi! You may want to try out Intel's C++ compiler for Linux if you are using an ix86 CPU. We used it here at work and it resulted in code that ran more than twice as fast, though we have never tried it with MySQL. I wonder whether Intel's C++ compiler accept those flags. Are those flags included in some standards? My problem is that I don't want the mysqld crashed randomly when I do benchmarking on it. I compiled MySQL with the Intel compiler a year ago. There was no noticeable difference in speed compared to GCC. And greetings to a fellow UofA student (though I've now graduated). thank you. I am honored. -- Regards Peng -- Peng Zhao [EMAIL PROTECTED] http://www.cs.ualberta.ca/~pengzhao TEL (Lab): (780)492-3725 Lab: CSC251 Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fake interval data type
Hi! Its kind of a subtle point, but innodb's locking not only locks rows, it locks the ABSENCE of rows. If I understand correctly this means that something like: BEGIN WORK SELECT COUNT(*) FROM table WHERE starttime? AND endtime? (check the count) INSERT INTO table (if count is zero) COMMIT SHOULD do the trick. The transaction should block anything else that tries to do an insert into the same range before you do your commit. I must add you have to use a LOCKING read in the SELECT. Either SELECT ... FROM ... WHERE .. FOR UPDATE; which sets exclusive locks, or SELECT ... FROM ... WHERE .. FOR LOCK IN SHARE MODE; which sets shared locks on the rows (to be precise, index records) it encounters. Without these additional clauses the default SELECT mode of InnoDB is the consistent non-locking read of Oracle. Locking reads in InnoDB also lock the absence of rows, as Tod states. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fake interval data type
Oops, the syntax is SELECT ... FROM ... WHERE .. LOCK IN SHARE MODE; Heikki -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, February 21, 2002 9:27 AM Subject: Re: Fake interval data type Hi! Its kind of a subtle point, but innodb's locking not only locks rows, it locks the ABSENCE of rows. If I understand correctly this means that something like: BEGIN WORK SELECT COUNT(*) FROM table WHERE starttime? AND endtime? (check the count) INSERT INTO table (if count is zero) COMMIT SHOULD do the trick. The transaction should block anything else that tries to do an insert into the same range before you do your commit. I must add you have to use a LOCKING read in the SELECT. Either SELECT ... FROM ... WHERE .. FOR UPDATE; which sets exclusive locks, or SELECT ... FROM ... WHERE .. FOR LOCK IN SHARE MODE; which sets shared locks on the rows (to be precise, index records) it encounters. Without these additional clauses the default SELECT mode of InnoDB is the consistent non-locking read of Oracle. Locking reads in InnoDB also lock the absence of rows, as Tod states. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Gemini Tables and NuSphere Update
Hi! Or should I look at the InnoDB tables (which are still considered beta). InnoDB tables are classified as gamma by Innobase Oy and MySQL AB. The text on the MySQL-Max download page is lagging behind (says beta), but to balance it, on the front page MySQL AB classifies MySQL-Max as stable :). A December quickpoll at the MySQL website showed InnoDB market share at 10 %, BDB 3 %, MyISAM 80 %, ISAM 5 %, of 3000 people who responded. Mytrix, Inc. is maybe the largest MySQL database site in the world (if someone has more than 1 TB data, please step forward), and they use InnoDB tables. See http://www.innodb.com/userstories.html. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql dies with Signal 11
Richard, (gdb) thread 1 [Switching to thread 1 (process 31905, thread 1)] #0 0x82011ce in memcpy () (gdb) bt #0 0x82011ce in memcpy () #1 0x82a33c0 in mysql_bin_log () #2 0x80b7cd8 in ha_commit_trans () #3 0x8076e24 in mysql_execute_command () #4 0x80788c8 in mysql_parse () #5 0x8073454 in dispatch_command () #6 0x80784c5 in do_command () #7 0x80728f4 in handle_one_connection () #8 0x81d446d in _thread_start () #9 0x0 in ?? () looks like mysqld crashed when it was writing to the MySQL binlog. If you can repeat the crash easily, please do CFLAGS=-g CXXFLAGS=-g ./configure --with-innodb gmake so that you get a mysqld binary with the debug info compiled in. Then run mysqld inside gdb, and do gdbbt full when it crashes. Also do 'bt full' for other 'interesting' threads than the one which crashed. What is your my.cnf like? What table types you use? Lots of bugs have been fixed since 4.0.0, but I did not notice anything connected to the binlog. When Monty gets back from the Galapagos Islands on March 4, he will start building 4.0.2. Why do you run 4.0? 3.23.49 might be a safer bet. What FreeBSD version you run? What threads you use? There are problems with some thread implementations in FreeBSD. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Richard Clarke [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, February 24, 2002 2:42 AM Subject: Mysql dies with Signal 11 Hi, I seem to be getting intermittant crashes of mysql. The error log prints the following, 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=268431360 record_buffer=1044480 sort_buffer=1048568 max_used_connections=5 max_connections=100 threads_connected=5 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 466539 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 020223 13:00:01 mysqld restarted 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 82 1555315003 etc... Can some please suggest what steps I can take to discovering what is wrong. I have attached server info and gdb output to bottom of email. This is a very active server processing 150 apache hits a second. A seperate looped process takes cgi data inserted into an IPC MSGQ and sticks it in the db and another seperate looped process takes data from this db and dumps to a db on a remote machine. Richard p.s. Your MySQL connection id is 7 to server version: 4.0.0-alpha-log I'm running a dual 1ghz FreeBSD box with 1gig of ram. (gdb) run -u mysql Starting program: /usr/local/mysql/libexec/mysqld -u mysql 020223 14:09:16 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections (after about 8hrs.) Program received signal SIGSEGV, Segmentation fault. 0x82011ce in memcpy () (gdb) info threads 14 process 31905, thread 14 0x81df52d in _thread_kern_sched () 13 process 31905, thread 13 0x81df52d in _thread_kern_sched () 12 process 31905, thread 12 0x81df52d in _thread_kern_sched () 11 process 31905, thread 11 0x81df52d in _thread_kern_sched () 10 process 31905, thread 10 0x81df52d in _thread_kern_sched () 9 process 31905, thread 9 0x81df52d in _thread_kern_sched () 8 process 31905, thread 8 0x81df52d in _thread_kern_sched () 7 process 31905, thread 7 0x81df52d in _thread_kern_sched () 6 process 31905, thread 6 0x81df52d in _thread_kern_sched () 5 process 31905, thread 5 0x81df52d in _thread_kern_sched () 4 process 31905, thread 4 0x81df52d in _thread_kern_sched () 3 process 31905, thread 3 0x82013dc in _thread_sys_close () 2 process 31905, thread 2 0x81df52d in _thread_kern_sched () * 1 process 31905, thread 1 0x82011ce in memcpy () (gdb) thread 1 [Switching to thread 1 (process 31905, thread 1)] #0 0x82011ce in memcpy () (gdb) bt #0 0x82011ce in memcpy () #1 0x82a33c0 in mysql_bin_log () #2 0x80b7cd8 in ha_commit_trans () #3 0x8076e24 in mysql_execute_command () #4 0x80788c8 in mysql_parse () #5 0x8073454 in dispatch_command () #6 0x80784c5 in do_command () #7 0x80728f4 in handle_one_connection () #8 0x81d446d in _thread_start () #9 0x0 in ?? () (gdb) thread 2 [Switching to thread 2 (process 31905, thread 2)] #0 0x81df52d in _thread_kern_sched () (gdb) bt #0 0x81df52d in _thread_kern_sched () #1 0x81dfdb7
Re: MySQLdMax crashed (for unknown reasons), please help
Jonathan, the bug is probably the SHOW CREATE TABLE bug which was fixed in 3.23.48. Please upgrade to 3.23.49a. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: JW [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, February 26, 2002 7:05 PM Subject: MySQLdMax crashed (for unknown reasons), please help Hello, We're been running a pretty large MySQLd with InnoDB support, last night it crashed on us in the middle of the night. I have never sent in a bug report like this before so please give me a little slack. I do not have any clue as to what actually caused the crash, I only have the logs and confs. In order: 1. System specs 2. my.cnf directives and 3. MySQL error log = 1. System Specs Dell PowerEdge 2450 Dual PIII 850 2G RAM 5-disk RAID5 for a total of 67G in / (27 used, 39 free) I think swap is also 2GB but I'm not sure (1060258+ blocks). SuSE Linux 7.3 ccs012:~ # uname -a Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown ccs012:~ # free -m total used free sharedbuffers cached Mem: 2013 2007 5 0 23695 -/+ buffers/cache: 1288724 Swap: 1035 0 1035 Not running any major service except MySQL, standalone sshd and inetd (for telnet) = 2. my.cnf ccs012:~ # grep -v # /etc/my.cnf [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= max_connections=150 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 innodb_data_file_path = ibdata1:2G innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=156M set-variable = innodb_log_buffer_size=12M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=1024M set-variable = innodb_additional_mem_pool_size=8M 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 [safe_mysqld] open-files-limit=256 = 3. MySQLd-Max error log output ccs012:~ # less /var/lib/mysql/ccs012.err 020216 19:46:15 mysqld started 020216 19:46:20 InnoDB: Started /usr/sbin/mysqld-max: ready for connections InnoDB: Error: undo-id is 137339008 InnoDB: Assertion failure in thread 27591729 in file trx0undo.c line 1316 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 agaist 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=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=150 max_connections=150 threads_connected=68 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1007010 K bytes of memory Hope that's ok, if not, decrease some variables in the equation InnoDB: Thread 27614285 stopped in file btr0pcur.c line 202 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... Stack range sanity check OK, backtrace follows: 0x806c9b9 0x8249998 0x81acb51 0x81a27d4 0x81949f3 0x817d565 0x817d949 0x817dc42 0x8170a1d 0x80baff9 0x809b855 0x80749a5 0x8076548 0x80725d4 0x8071ac7 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the 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
[ANNOUNCE] Innobase Oy declares MySQL/InnoDB-3.23 stable
Innobase Oy declares MySQL/InnoDB-3.23 stable starting from version 3.23.49. Helsinki, February 28, 2002 Innobase Oy has decided to declare MySQL/InnoDB-3.23 officially stable starting from the version 3.23.49. InnoDB tables are included in the MySQL-Max-3.23 distribution which is available for download from http://www.mysql.com. InnoDB tables provide transactions, row level locking, and foreign key constraints to MySQL. There is now extensive real-world experience from the use of MySQL/InnoDB-3.23. In a December 2001 poll at the MySQL AB website, 10 % of the 3000 answerers said that they use primarily InnoDB type tables in MySQL. Based on the daily download rate, MySQL is estimated to have 3 million users worldwide. InnoDB tables are used at several database sites to get the best performance under a high database load. The InnoDB user stories page http://www.innodb.com/userstories.html lists some of these InnoDB users. Mytrix, Inc. is maybe the largest MySQL database site in the world, storing more than a terabyte of data, and it runs on InnoDB tables. SelectBourse tracks Nasdaq and and Paris Stock Exchange trades in its application, which runs on InnoDB tables and handles 1200 inserts/second. Also the popular Internet news site Slashdot.org runs on InnoDB tables. For more information on InnoDB, please see the InnoDB website http://www.innodb.com. Best regards, Heikki Tuuri CEO Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb tables lose foreign keys after creating an index...
Rick, sorry, it is not mentioned in the manual that MySQL performs a CREATE INDEX by doing an ALTER TABLE. And ALTER TABLE has the feature (= documented bug) that it removes foreign key definitions. You should define all your indexes within the table create statement, like in: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB; Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Rick Flower [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Friday, March 01, 2002 2:32 AM Subject: Innodb tables lose foreign keys after creating an index... Hi all.. Are any of you aware of a way to get indexes to work at all with Innodb tables containing foreign keys? I'm finding that after doing a create index on a table which *had* foreign keys, after the create, the foreign keys are gone.. I've included a simple test below which shows off the problem quite well.. I've searched around on Google to see if anyone had run into this problem, but didn't find any reference.. This really makes foreign keys worthless in MySQL.. The more I dig into MySQL, the less I like it due to missing features or wierd side effects of existing ones.. Perhaps someone can shed some light on what I may be doing wrong.. By the way, I'm using MySQL 3.23.47 on a Solaris box -- if it matters.. To reproduce the problem: 1) create table test_fk_parent(id int not null, primary key (id)) type=innodb; 2) create table test_fk_child ( id int not null unique, parent_id int, index par_ind(parent_id), foreign key(parent_id) references test_fk_parent(id)) type=innodb; 3) create index CHILD_KEY on test_fk_child (id); Below is the sample output : mysql create table test_fk_parent(id int not null, primary key (id)) type=innodb; Query OK, 0 rows affected (0.04 sec) mysql create table test_fk_child ( id int not null unique, parent_id int, index par_ind(parent_id), foreign key(parent_id) references test_fk_parent(id)) type=innodb; Query OK, 0 rows affected (0.05 sec) mysql show table status; ++++--++--- --+-+--+---++-- ---+-+++--- --+ | 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 | ++++--++--- --+-+--+---++-- ---+-+++--- --+ | test_fk_child | InnoDB | Fixed |0 | 0 | 16384 |NULL |32768 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 5807104 kB; (parent_id) REFER vista/test_fk_parent(id) | | test_fk_parent | InnoDB | Fixed |0 | 0 | 16384 |NULL |0 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 5807104 kB | ++++--++--- --+-+--+---++-- ---+-+++--- --+ 2 rows in set (0.01 sec) [[[ NOTICE THE EXISTING FOREIGN KEY ABOVE ]]] mysql create index CHILD_KEY on test_fk_child (id); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show table status; ++++--++--- --+-+--+---++-- ---+-+++-+ | 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 | ++++--++--- --+-+--+---++-- ---+-+++-+ | test_fk_child | InnoDB | Fixed |0 | 0 | 16384 |NULL |49152 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 5807104 kB | | test_fk_parent | InnoDB | Fixed
Re: Error creating InnoDB table/MySQL parser bug?
Boris, thank you for the bug report! This is the only currently known bug in MySQL/InnoDB-3.23.49: February 15, 2002: If a CREATE TABLE statement contains the string 'foreign' anywhere, and that is succeeded by a non-space character, then the CREATE TABLE fails, reporting errno 150. Fixed in 3.23.50. Best regards, Heikki Innobase Oy -Original Message- From: Borislav Bankov TEEC [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Friday, March 01, 2002 5:32 PM Subject: Error creating InnoDB table/MySQL parser bug? Hello, Today, I compiled MySQL (mysql-3.23.49.tar.gz) on a Debian Potato system. I configure'd with following options: ./configure --prefix=/usr/local/mysql --with-innodb make and make install ran without any problems. Then I followed the instructions in the INSTALL-SOURCE file. Everything worked fine. Then, I tried to create a InnoDB-Table in the test database: shell /usr/local/mysql/bin/mysql -u root -p mysql use test mysql create table workorders (wo_no varchar(12) not null, foreign_log_no varchar(12) not null default '', primary key(wo_no)) type=innodb; Table creation didn't succeed and I've an error message: ERROR 1005: Can't create table './test/workorders.frm' (errno: 150) After checking my statement and trying to create other InnoDB tables (which succeeded!) I tried: mysql create table workorders (wo_no varchar(12) not null, foreig_log_no varchar(12) not null default '', primary key(wo_no)) type=innodb; Here is what I've got: Query OK, 0 rows affected (0.00 sec) The only different thing is that I changed foreign_log_no to foreig_log_no. It seems that the MySQL parser got irritated when I tried to use foreign as a beginning of a column name. Regards, Borislav -- Borislav Bankov mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql innobd ibdata file size?
Ron, Ronnie wrote in message ... --Boundary_(ID_3Wgj3oHvYWg449KlsCktkA) Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7BIT Hi everyone, SQL Iv'e been on innodb.com trying to find out the largest ibdata file size acceptable to my system RH Linux 7.2, at one part it states data and log files must be 2GB, depending on the OS. I specified 1M as the size for ibdata, current ibdata size reflects1.8G. Does innodb build on that 1.8G to a max of 10G, or RH Linux 7.2 only handles 2G? probably your OS/file system configuration only supports 2 GB files. Otherwise InnoDB (versions from October up) would have created and initialized a 10 GB file. You have free disk quota? It is safest to use 2000M files if you do not know your file system. Their example of creating ibdata on the Website shows multiple ibdataX on 3 Hard Drives of 2G each, does the database span these 3 ibdata files for an aggregate of 6G? Someone please enlighten me. Trying to get this right the first time, so sql development can start. Ron Arenas Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb tables lose foreign keys after creating an index...
Hi! -Original Message- From: j.random.programmer [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Saturday, March 02, 2002 5:21 AM Subject: Re: Innodb tables lose foreign keys after creating an index... --- Heikki Tuuri [EMAIL PROTECTED] wrote: Heikki: Is there a fix planned for the alter table/foreign key issue ? Full foreign key support is in the TODO list of MySQL. For InnoDB, the top priority now is to get the non-free hot backup tool of InnoDB to beta testing. You can speed up adding of individual features to MySQL by buying a support contract. Best regards, [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Steve, I added an item to the TODO list at http://www.innodb.com/todo.html .. May, 2002: Make a data file auto-extendible. You can specify the last data file in innodb_data_file_path like this: ibdata1:50Mautoextend It will create a data file whose initial size is 50 MB, and InnoDB will automatically extend it in units of 10 MB when the data file becomes full. .. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Steve Rapaport [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, March 03, 2002 12:18 AM Subject: InnoDB frightens me... I'm seriously considering switching to mysql-max so I can make my session handling table an Innodb type. Currently the mysql locking policy allows big traffic jams when several sessions are active simultaneously, and it's the only table that has frequent updates. I need row-locks! BUT, and it's a big but, I just read through the InnoDB manual pages in the mysql site, and it seems I can't have row-locking without a lot of programming and worse, admin overhead. And scary a-priori decisions. At first glance (correct me) I need to 1. Check through all my programs handling this table to add AUTOCOMMIT or Commit/Rollback as appropriate. 2. Decide with zero experience on a lot of maximum sizes which will not be adjustable in future, including dataspace. 3. If I run up against one of those limits in future I am guaranteed a nightmare of table copying, deleting, restoring, and woe if I happen to get a runaway rollback. I am also required to back up my database table and all its update logs in case of this situation, although my chances of restoring them successfully look dim. I am sufficiently frightened to just accept table-lock traffic jams instead. Can anyone tell me how I can use row-locking without getting into this frightening world? Best, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't restart MySQL after innodb table filled up
Erik, this is probably not a bug. Your log files probably are not the ones which belong together with the current data file(s). When you start the database it tries to use the obsolete log files in recovery. From the manual: If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all data files, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding .frm files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Erik Barker [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Monday, March 04, 2002 10:07 PM Subject: Can't restart MySQL after innodb table filled up I'm having problems restarting a MySQL server after a certain table reached maximum capacity. I managed to dump my entire database to a flat file before I restarted the server however when I tried to re-import my data the operation failed. My database size set to 500M in the innodb section of my my.cnf file. I'm running RH 7.2, kernel 2.4.19pre1 with 512M of RAM and have rebooted the server without any change in results. I've also upgraded MySQL-Max from 3.23.48 to 3.23.49a using the RPMS on mysql.com. I've tried removing all the innodb files within my database directory and MySQL seems to build the new 500M file properly but then crashes with the following message in the mysql.log: 020304 11:33:01 mysqld started 020304 11:33:02 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 1819350038 InnoDB: Assertion failure in thread 4096 in file ../include/buf0buf.ic line 265 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 agaist 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=268431360 record_buffer=1044480 sort_buffer=4194296 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 773739 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 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... Bogus stack limit or frame pointer, fp=0xbfffe0b8, stack_bottom=0x49043190, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x47f89f20 is invalid pointer thd-thread_id=138727664 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 138727664 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 020304 11:33:02 mysqld ended - I've also tried changing a few values in the my.cnf file including changing the number of threads to 1 and increasing the size of the DB to 600M. Is this a known bug? Thanks, -- Erik Barker Sr. Systems Engineer NetNation Communications Inc. http://www.netnation.com | http://www.domainpeople.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB issues - tables not found
Tomasz,
are you running on Windows?
Please use innodb_table_monitor as explained in section 9.1 of
http://www.innodb.com/ibman.html, and also look into section 15.1.
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
Speed up adding of features to MySQL/InnoDB through support contracts
See http://www.innodb.com for the online manual and latest news on InnoDB
-Original Message-
From: Tomasz Korycki [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Date: Tuesday, March 05, 2002 10:30 AM
Subject: InnoDB issues - tables not found
Hi,
Newbie here, so please be kind...
I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE
TABLE) and I hit two problems. Maybe it's my clumsiness with search
specification, but I couldn't find answers in the archive. Oh, I tried it
on InnoDB tables created just for this test. So here goes:
First problem: SHOW CREATE does _not_ use the same syntax I did when
creating tables - it omits REFERENCES... completely. This is not a big
deal, I can just add those in the scripts if I need them, but it is
somewhat worrisome;
Second problem - and this is a big one - after I looked around in those
tables, inserting, selecting and updating data, I disconnected from the DB,
disconnected from the server and shut down the client. Until then I _could_
see the table structures, the fact they were InnoDB type, column
definitions, and so on. I did also see free space and so on. When I came
back after dinner and connected back... no such luck. Any attempt to get
any rows out (like SELECT * FROM `table_name`) gives me:
[FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno:
1)
I can't see structure, number of rows, free space, table type, size,
nothing. Logged out and in using different (sufficiently privileged) names
- no cigar. Looked through the hostname.err log - nothing there, as well.
Disconnected the client, shut down the MySQL - nothing seems wrong. It
started back without complaint as well - but I still can't see my tables...
I checked mysql database -everything is working as expected, things show
up...
In a way I hope it is a FAQ, in which case I would appreciate a gentle
nudge. In case it's not, though, Is there something glaring I did/didn't
do?
MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from
MySQL site. Here is how I created one of the tables in question (in a new
database):
CREATE TABLE `ALBUM` (
`Ix` int(11) NOT NULL auto_increment,
`Title` varchar(255) binary default NULL,
`Released` date default NULL,
`Billing` int(11) REFERENCES PERFORMER(Ix),
`Tracks` int(11) REFERENCES TRACK(Ix),
`Remarks` blob,
`Live` enum('Live','Studio','Both','Unknown') default 'Unknown',
`Compilation` enum('Yes','No') default No,
`RecordedFrom` date default NULL,
`RecordedTo` date default NULL,
`Company` varchar(255) binary default NULL,
`ID` tinytext,
PRIMARY KEY (`Ix`)
) TYPE=InnoDB;
The rest of them were similar.
Speak up, sages, I'm hanging on Your... well, fingers, in this case ;)
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb problems
Wendell, InnoDB writes a checksum to a database page when it is written to disk. If the checksum does not correspond to the page contents when the page is read back in, you get the below error. Below page 36819 in table registrydb_tn/TBL_AllNames appears to be corrupt, like it says. The checksum is 0, which does not correspond to page contents. When you encounter this kind of error, the first thing to try is rebooting the computer, like it says below. Next you can try dumping, dropping, and reimporting the corrupt table. The last resort is to recover from a backup. 020306 09:01:16 mysqld restarted 020306 9:01:16 Can't start server: Bind on TCP/IP port: Address already in use 020306 9:01:16 Do you already have another mysqld server running on port: 3306 ? The last error you got was probably that Linux had not killed the entire mysqld process though mysqld had called exit(1). Then you should look with ps -A what processes are running, and kill -9 them manually. Please send me all error messages and hex dumps you have, from both servers. What version you are running? Linux kernels 2.2 -2.5 seem to have bugs in the i/o system, especially in connection with RAID disks. But let us look first at the hex files. Regards, Heikki -Original Message- From: Wendell Dingus [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Thursday, March 07, 2002 6:16 AM Subject: Innodb problems I've been using MyISAM tables for a long time on a lot of machines and (knock on wood) have basically never had a problem. Now though on a larger server I decided we should use INNODB tables and am having problems. I didn't realize until today that it's been 'crashing' and recovering repeatedly for the past few weeks. Today it died though and could not repair itself. Since I'm doing replication to a second server I just copied databases from the backup server to the primary and got things back going. Looking at the log files on the second/backup server it has the same type of errors in the log file though. Not bad hardware unless both (identical) servers have the same bad hardware. Here's where it crashed and dumped a heck of a lot of hex data into the log file: ..;Inno D B: End of page dump InnoDB: Page checksum 1558702454 stored checksum 0 InnoDB: Page lsn 4 226263974, low 4 bytes of lsn at page end 0 InnoDB: Page may be an index page where index id is 0 565 InnoDB: Database page corruption or a failed InnoDB: file read of page 36819. 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. Number of processes running now: 0 020306 08:55:21 mysqld restarted 020306 8:55:25 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 4 229089185 InnoDB: Last MySQL binlog file offset 0 465620, file name ./shelby1-bin.001 020306 8:55:26 InnoDB: Flushing modified pages from the buffer pool... 020306 8:55:26 InnoDB: Started /usr/sbin/mysqld-max: ready for connections InnoDB: Database page corruption or a failed InnoDB: file read of page 36819. InnoDB: You may have to recover from a backup. InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 8fd30005851f00058 Here's where it gave up the ghost entirely: ..;Inno D B: End of page dump InnoDB: Page checksum 1558702454 stored checksum 0 InnoDB: Page lsn 4 226263974, low 4 bytes of lsn at page end 0 InnoDB: Page may be an index page where index id is 0 565 InnoDB: and table registrydb_tn/TBL_AllNames index LastName InnoDB: Database page corruption or a failed InnoDB: file read of page 36819. 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. Number of processes running now: 0 020306 09:01:16 mysqld restarted 020306 9:01:16 Can't start server: Bind on TCP/IP port: Address already in use 020306 9:01:16 Do you already have another mysqld server running on port: 3306 ? 020306 9:01:16 Aborting 020306 9:01:16 /usr/sbin/mysqld-max: Shutdown Complete 020306 09:01:16 mysqld ended Here's the my.cnf file: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin server-id=1 default-table-type=innodb innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdat a 6:2000M innodb_data_home_dir = /var/lib/innodb/ set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /var/lib/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M
Re: Foreign keys in mysqldump?
Bob, I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Monday, March 11, 2002 8:11 PM Subject: Foreign keys in mysqldump? When I do a mysqldump and include table creation, it does not seem to include the foreign key constraints in the create table statements of the InnoDB tables. Is there a command line switch or other way to make this happen? Thanks, -Bob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: foreign keys for relation ships
Hi! The message I sent 5 minutes ago answers also this :). I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: van den Heuvel, Frank [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, March 12, 2002 10:16 AM Subject: foreign keys for relation ships Hello, I am creating tables within MySql 3.23.49. I want to add relation ships between different tables. I tried to do this with foreign key statements. For example: CREATE TABLE domains ( domain varchar(100) NOT NULL, registrarid int(10) NOT NULL, foreign key (registrarid) REFERENCES registrars(registrarid), PRIMARY KEY (domain) ) TYPE=INNODB; CREATE TABLE registrars ( registrarid int(10) unsigned NOT NULL auto_increment, url varchar(200) default NULL, whois varchar(100) default NULL, tld varchar(100) default NULL, PRIMARY KEY (registrarid) ) TYPE=INNODB So the column registrarid in the domain table is a reference to the registrars table(registrarid). When I put this in mysql it doesn't complain about it. Then I use ODBC to link the database to Access. I use Access as my front-end. There I cannot see the relationship. Also when I do a show create table within mysql I cannot see the relationship. Is this the wrong way to do this ? In other words, what is the right way to do this ? Thanks Frank - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign keys in mysqldump?
Bob, thank you! I believe 3.23.50 will be out in two weeks. It is a stable version. The changes in foreign keys were actually rather small. I hope they do not degrade .50 to the beta category. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; MySQL Mailing List [EMAIL PROTECTED] Date: Tuesday, March 12, 2002 4:00 PM Subject: RE: Foreign keys in mysqldump? Heikki, You are the man! I am glad you are addressing these issues in 3.23.50. They will make foreign key support in MySQL much more robust (in my opinion, anyway..) Is the .50 release available yet? If not, do you have a timeline for when it will be, and when it would be recommended to use in production work? Thanks much, -Bob -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 12, 2002 4:50 AM To: Bob McLaughlin; MySQL Mailing List Subject: Re: Foreign keys in mysqldump? Bob, I have now improved foreign key support so that version 3.23.50 does 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this should also show them in mysqldumps; 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; 3. allow backquotes around column and table names in foreign key definitions: backquotes are produced by SHOW CREATE TABLE; 4. allow adding a new foreign key constraint ot a table through ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); remember though that you first have to create the appropriate indexes on the parent and child table so that InnoDB approves the constraint. I try to get also in .50 a command SET FOREIGN KEY CONSTRAINT CHECKS=0 which allows you to disable them when you import dumped tables in a wrong order regarding to the foreign key definitions. In versions 3.23.50 the only way to 'dump' foreign key definitions is to call SHOW TABLE STATUS FROM ... which prints them in the table comments. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Bob McLaughlin [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Monday, March 11, 2002 8:11 PM Subject: Foreign keys in mysqldump? When I do a mysqldump and include table creation, it does not seem to include the foreign key constraints in the create table statements of the InnoDB tables. Is there a command line switch or other way to make this happen? Thanks, -Bob - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign keys in mysqldump?
Philip, -Original Message- From: Philip Molter [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Bob McLaughlin [EMAIL PROTECTED]; MySQL Mailing List [EMAIL PROTECTED] Date: Tuesday, March 12, 2002 4:21 PM Subject: Re: Foreign keys in mysqldump? On Tue, Mar 12, 2002 at 11:50:12AM +0200, Heikki Tuuri wrote: : Bob, : : I have now improved foreign key support so that version 3.23.50 does : : 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this : should also show them in mysqldumps; : 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; : 3. allow backquotes around column and table names in foreign key : definitions: backquotes are produced by SHOW CREATE TABLE; : 4. allow adding a new foreign key constraint ot a table through : ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); : remember though that you first have to create the appropriate indexes on the : parent and child table so that InnoDB approves the constraint. Heikki, That is fantastic news! As far as everyday usage of InnoDB tables goes, those are the number one issues we have to work around. Thanks for taking the time to implement that. thank you! Speaking of InnoDB development, what is the status on the InnoDB backup utility? The beta testing of InnoDB Hot Backup (a non-free add-on tool) started on Monday. If you want to take part in the beta testing, look at http://www.innodb.com * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
Tomasz, InnoDB in 3.23 and 4.0 is the same codebase. InnoDB versions are best counted from the 3.23 series, because they appear more frequently. I am sorry that this is confusing. MySQL/InnoDB-3.23.50 has not been released yet. It will probably be out at the end of March. From section 16 of http://www.innodb.com/ibman.html you find detailed information about every InnoDB version. For example, 4.0.1 == 3.23.47. Foreign keys should work in 4.0.1. Starting from version 3.23.50 InnoDB returns the foreign key definitions of a table when you call SHOW CREATE TABLE yourtablename You can also list the foreign key constraints for a table T with SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' The foreign key constraints are listed in the table comment of the output. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Wednesday, March 13, 2002 1:05 AM Subject: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1? Here is an excerpt from http://www.innodb.com/ibman.html#InnoDB_distros, section 4.2: Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints... Now, I assumed the version number above was suspiciously similar to MySQL one - and since I use 4.0.1, I thought I was OK (I need them references... ON DELETE and friends would be great, but plain references save most of the hassle). After trying to (and failing to) create my own tables, I did tables as in the example on InnoDB site: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=InnoDB; And what do I see? I see indices in the tables, but no FK! Yes, tables _are_ InnoDB. I have proper indices - so on to the next step (there was no error return), just in case - it says: Starting from version 3.23.50 InnoDB allows you to add a new foreign key constraint to a table... So, here I go: ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent(id); No error. No effect, either... So, here comes the big question: What am I doing wrong? -- sql, query Tomasz Korycki [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Converting MyISAM to InnoDB
Hi! You should add a new data file as explained in section 5 of http://www.innodb.com/ibman.html. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: ImpactNET - Edgar R Gutierrez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Wednesday, March 13, 2002 9:25 AM Subject: Converting MyISAM to InnoDB Hello, We are currently in the process of converting our MyISAM tables to InnoDB to make our tables transaction safe. Unfortunately, I am getting the following error on one of the tables... mysql alter table SUBSCRIBERS type = InnoDB; ERROR 1114: The table '#sql-fcc_1' is full mysql What value should I set the following. Currently, I set it to 512M... set-variable = innodb_buffer_pool_size=512M My SYSTEM runs V4.0.1-alpha and my server has 1GB of RAM. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign keys in mysqldump?
David, a Solaris version of InnoDB Hot Backup is planned, as well as a FreeBSD version. Currently I am working with beta testers trying to get the Large file support on Linux to work :). Regards, Heikki Tuuri Innobase Oy -Original Message- From: David Felio [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, March 12, 2002 7:18 PM Subject: Re: Foreign keys in mysqldump? InnoDB Hot Backup-0.33 beta is now available for Windows NT/2000/XP and Linux. Any plans for Solaris by the May 2002 release date? From: Heikki Tuuri [EMAIL PROTECTED] The beta testing of InnoDB Hot Backup (a non-free add-on tool) started on Monday. If you want to take part in the beta testing, look at http://www.innodb.com sql David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Transactional sql tables with columns that can have NULL values
Hi! Where did you read that? That text should be updated! At least InnoDB supports indexes on columns which can have a NULL value. Regards, Heikki Tuuri Innobase Oy -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Wednesday, March 13, 2002 5:23 PM Subject: Transactional sql tables with columns that can have NULL values Hi, I would like to use transactional sql tables that support columns with NULL values. I think that only BDB and InnoDB tables types support transactions, but I have read that: Only the MyISAM table type supports indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results. The problem is that MyISAM table is non-transactional. How can I solve this? Thank you very much TERE - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
Tomasz, are you sure you are running 4.0.1? In the rpm of 4.0.0 there were no foreign keys. I tested this on mysql-max-4.0.1, and it worked. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB ... heikki@hundin:~/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bin mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha-max-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.10 sec) mysql CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INN ODB; Query OK, 0 rows affected (0.00 sec) mysql show table status from test; ++++--++-+-- ---+--+---++-+-- ---+ ++-- -+ | 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 | ++++--++-+-- ---+--+---++-+-- ---+ ++-- -+ | child | InnoDB | Fixed |0 | 0 | 16384 | NULL |16384 | 0 | NULL | NULL| NULL | NULL || InnoDB free: 700416 kB; (parent_id) REFER test/pa rent(id) | | parent | InnoDB | Fixed |0 | 0 | 16384 | NULL |0 | 0 | NULL | NULL| NULL | NULL || InnoDB free: 700416 kB | ++++--++-+-- ---+--+---++-+-- ---+ ++-- -+ 2 rows in set (0.03 sec) mysql -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Thursday, March 14, 2002 6:53 AM Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1? At 16:57 2002-03-13, Rick Flower wrote: Tomasz writes: From section 16 of http://www.innodb.com/ibman.html you find detailed information about every InnoDB version. For example, 4.0.1 == 3.23.47. Foreign keys should work in 4.0.1. Hmmm... That's what I read, too. And after several unsuccesful attempts to create my own tables, I did those contained on Your site, verbatim (as I put in my original message). Still, no effect. I guess the question then becomes: is 4.0.1 really able to keep track of constraints but unable to show them? In which case, how can one find out what they are (if extant)? Are you sure that you've got a MySQL-Max server, or at least one built with InnoDB support enabled? If you didn't, you might not get an InnoDB table even if you asked for one.. Unfortunately, the SQL parser is somewhat stupid and doesn't bother telling you that you did something dumb or that doesn't make sense in regards to how the server was built.. I've run into things like that numerous times.. SHOW TABLE STATUS sez it's InnoDB... As for listing out the foreign key constraints, that only works if you issue a show table status; for MySQL 3.23.4x, and you will get something like the following REFER statement : OK, which version, exactly, do You get following output from? Oh, never mind, I see it's .47. Which according to Heikki Tuuri of InnoDB is the same as mine... | ITEM | InnoDB | Dynamic| 0 | 0 | 16384 |NULL |0 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 4901888 kB | | ITEM_DEF | InnoDB | Dynamic| 0 | 0 | 16384 |NULL |16384 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 4901888 kB; (ITEM_NAME) REFER dbname/ITEM(ITEM_NAME) Because mine stops after InnoDB free: whatever kB snip! Hopefully this might shed some light on your problem.. No, it didn't. It turns out we have (according to InnoDB) the same version of the DB, yet mine behaves differently than mine. Oh, well. Just so You needn't fish out beginning of this thread, mine is 4.0.1. I don't quite know what to think at this point... Below are the samples from above that you can feed
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
Tomasz, are you using a non-latin1 character set? The bug fixed in 3.23.49 MySQL/InnoDB-3.23.49, February 17, 2002 * Fixed a bug: if one defined a non-latin1 character set as the default character set, then definition of foreign key constraints could fail in an assertion failure in dict0crea.c, reporting an internal error 17. might be the root of your problem with 4.0.1. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, March 14, 2002 8:57 AM Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1? Tomasz, are you sure you are running 4.0.1? In the rpm of 4.0.0 there were no foreign keys. I tested this on mysql-max-4.0.1, and it worked. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB ... heikki@hundin:~/mysql-max-4.0.1-alpha-pc-linux-gnu-i686/bin mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.1-alpha-max-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.10 sec) mysql CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INN ODB; Query OK, 0 rows affected (0.00 sec) mysql show table status from test; ++++--++-+- - ---+--+---++-+- - ---+ ++- - -+ | 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 | ++++--++-+- - ---+--+---++-+- - ---+ ++- - -+ | child | InnoDB | Fixed |0 | 0 | 16384 | NULL |16384 | 0 | NULL | NULL| NULL | NULL || InnoDB free: 700416 kB; (parent_id) REFER test/pa rent(id) | | parent | InnoDB | Fixed |0 | 0 | 16384 | NULL |0 | 0 | NULL | NULL| NULL | NULL || InnoDB free: 700416 kB | ++++--++-+- - ---+--+---++-+- - ---+ ++- - -+ 2 rows in set (0.03 sec) mysql -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Thursday, March 14, 2002 6:53 AM Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1? At 16:57 2002-03-13, Rick Flower wrote: Tomasz writes: From section 16 of http://www.innodb.com/ibman.html you find detailed information about every InnoDB version. For example, 4.0.1 == 3.23.47. Foreign keys should work in 4.0.1. Hmmm... That's what I read, too. And after several unsuccesful attempts to create my own tables, I did those contained on Your site, verbatim (as I put in my original message). Still, no effect. I guess the question then becomes: is 4.0.1 really able to keep track of constraints but unable to show them? In which case, how can one find out what they are (if extant)? Are you sure that you've got a MySQL-Max server, or at least one built with InnoDB support enabled? If you didn't, you might not get an InnoDB table even if you asked for one.. Unfortunately, the SQL parser is somewhat stupid and doesn't bother telling you that you did something dumb or that doesn't make sense in regards to how the server was built.. I've run into things like that numerous times.. SHOW TABLE STATUS sez it's InnoDB... As for listing out the foreign key constraints, that only works if you issue a show table status; for MySQL 3.23.4x, and you will get something like the following REFER statement : OK, which version, exactly, do You get following output from? Oh, never mind, I see it's .47. Which according to Heikki Tuuri of InnoDB is the same as mine... | ITEM | InnoDB | Dynamic| 0
Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1?
Tomasz, I tested also with mysql-4.0.1 and it worked. But are you using a non-latin1 character set? If yes, the bug which was fixed in 3.23.49 might cause the problem. Did you compile MySQL yourself? If yes, what compiler? Please download the official MySQL-Max-3.23.49a binary from www.mysql.com and test it with a freshly created InnoDB database. .49a is the recommended stable version. Regards, Heikki -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Thursday, March 14, 2002 5:52 PM Subject: Re: constraints in InnoDB, or is 3.23.43b _really_ 4.0.1? At 01:57 2002-03-14, Heikki Tuuri wrote: Tomasz, are you sure you are running 4.0.1? In the rpm of 4.0.0 there were no foreign keys. I tested this on mysql-max-4.0.1, and it worked. Well... [root@flow11 httpd]# mysqladmin -p version Enter password: mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 9 days 6 hours 12 min 44 sec Threads: 1 Questions: 2261 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 14 Queries per second avg: 0.003 Best regards, Heikki Tuuri Innobase Oy --- - sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql error
Kannan, error 101 used to mean a lock wait timeout. In 4.0.1 and later 3.23 versions that has been replaced by a new clear-English error code. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, March 17, 2002 3:48 PM Subject: mysql error Placed At : Hi ... I have a database having 30 tables..i run mysql-4.0.0 with innodb..updates and delete on one paritcular table fail ..when i run the query the prompt jus t hangs and i get the following error.. ERROR 1030: Got error 101 from table handler I'm also not able to create a new table by selecting from this table.. would be great if someone cld help me with this.. thanx Kannan *** *** Quality Policy To be the best-in-class service provider by practising international standards in providing world-class services and end-to-end network solutions. *** *** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb replication and Database renaming question
Nico, -Original Message- From: Nico Sabbi [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Friday, March 15, 2002 1:42 PM Subject: Innodb replication and Database renaming question Hi, I'm still using Mysql 3.23.46 because, as far as I remember, in .47 was reintroduced the limit of 500 chars max in Innodb primary keys (limit that would break my application). Is it still present ? Is it present in Mysql 4 ? I also need to know if Innodb is reliable in replication mode. I remember that with past versions, if the master broke the slave would lose synchrony, or something of this kind that would make a self-recovery impractical. replication of InnoDB tables is used at several database sites. There are currently no known bugs. I need to implement a read-only fall-back DB, and I'm planning to use a combination of cron script to dump the live db to the slave after having renamed the good (old) versions, and after having verified that all the data in the new dump is fine, renaming the new db to the right name. The problem is that I can't find in the manual a command to rename a database. There is none. You may write a Unix script which generates the RENAME TABLE commands from the .frm files. I will appreciate any suggestion, Thanks, Nico Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Extending tablespace for innodb
Alex, -Original Message- From: Varshavchick Alexander [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Friday, March 15, 2002 8:00 PM Subject: Extending tablespace for innodb Hello, How can I increase the size of the tablespace for innodb tables? Can you say if this is a correct procedure: 1. Dumping the database; 2. Stopping it; 3. Deleting innodb log files (ib_logfile0, ...) and innodb data file specified in innodb_data_file_path; 4. Changing size of data file in innodb_data_file_path; 5. Starting mysql; 6. Restoring the databases from the dump file. Does it seems to be a correct procedure, or I've messed up something? please look in section 5 of http://www.innodb.com/ibman.html about adding a new data file. Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574
Alex,
the assertion means that when InnoDB looks for a father node pointer to a
page in the B-tree, it ends up on a pointer which does not point to the
child page. In other words, the B-tree is corrupt.
I see that the lsn of your database is rather small. Did you do anything
special before the crash?
You can try addding the following code on line 572 of btr0btr.c so that we
see what index in what table is corrupt and how. That can give a clue what
might be the bug:
if (btr_node_ptr_get_child_page_no(node_ptr) !=
buf_frame_get_page_no(page))
{
printf(Table %s, index %s, father ptr page no %lu, child page no
%lu\n,
(UT_LIST_GET_FIRST(tree-tree_indexes))-table_name,
(UT_LIST_GET_FIRST(tree-tree_indexes))-name,
btr_node_ptr_get_child_page_no(node_ptr),
buf_frame_get_page_no(page));
page_rec_print(page_rec_get_next(page_get_infimum_rec(page)));
page_rec_print(node_ptr);
}
I have added the above code now to 3.23.50.
When you have printed the above info, you may want to recover your database.
Look in section 6.1 of http://www.innodb.com/ibman.html about forcing
recovery.
Regards,
Heikki Tuuri
Innobase Oy
-Original Message-
From: BAUMEISTER Alexandre [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; Michael Widenius
[EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Tuesday, March 19, 2002 11:51 AM
Subject: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574
Bonjour,
We have Mysql continually crashing with this assertion failure in
InnoDB.
Mysql-3.23.49 configured with by :
===
=
./configure --prefix=/usr/local/mysql--without-bench
--enable-thread-safe-client --with-innodb --without-debug
--without-berkeley-db --without-raid --enable-local-infile
===
=
Here are the logs :
===
=
020319 10:43:13 mysqld restarted
020319 10:43:16 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 722513183
InnoDB: Doing recovery: scanned up to log sequence number 0 722514672
020319 10:43:16 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
74
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
020319 10:43:18 InnoDB: Flushing modified pages from the buffer pool...
020319 10:43:18 InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
020319 10:43:18 Warning: Checking table: './popup/campagne'
020319 10:43:24 Warning: Checking table: './boursoscan/abonnes'
InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574
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 agaist 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=134213632
record_buffer=1044480
sort_buffer=2097144
max_used_connections=82
max_connections=650
threads_connected=32
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2125262 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
===
=
And here are the config options :
===
=
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
skip-name-resolve
local-infile
set-variable= max_connections=650
set-variable= key_buffer=128M
set-variable= max_allowed_packet=5M
set-variable= table_cache=512
set-variable= sort_buffer=2M
set-variable= record_buffer=1M
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_cache=8
set-variable= thread_concurrency=8 # Try number of CPU's*2
myisam-recover = BACKUP,FORCE
innodb_data_home_dir = /usr/local/mysql/innobase_var/innobase_data/
innodb_data_file_path = ibdata1:2000M
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /usr/local/mysql/innobase_var/innobase_logs/
set-variable = innodb_log_files_in_group=5
set-variable
Re: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574
Alex, since you are French, I assume you have accent characters in the strings you have stored into your database. The ordering of accent characters changed in 3.23.44! You must dump and reimport your tables to 3.23.49 so that the index trees are sorted in the right order. Regards, Heikki Innobase Oy -Original Message- From: BAUMEISTER Alexandre [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; Michael Widenius [EMAIL PROTECTED] Date: Tuesday, March 19, 2002 12:18 PM Subject: Re: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574 Bonjour, BA We have Mysql continually crashing with this assertion failure in BA InnoDB. I have attached to this email and extract from the error log which starts at the origin of the problem. First lines are : 020318 18:24:42 mysqld started InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 020318 18:24:45 InnoDB: Started /usr/local/mysql/libexec/mysqld: ready for connections InnoDB: error in sec index entry update in InnoDB: index log table popup/popup InnoDB: tuple 0: len 20; hex 616e647265747469202020202020202020202020; asc andretti;; 1: len 6; hex 0004364d; asc 6M;; InnoDB: record RECORD: info bits 0 0: len 20; hex 616e6472e9737069202020202020202020202020; asc andr.spi;; 1: len 6; hex 000313f0; asc .. ;; InnoDB: Make a detailed bug report and send it InnoDB: to [EMAIL PROTECTED] TRANSACTION 0 11033371, OS thread id 46058 updating or deleting, active, runs or sleeps, has 600 lock struct(s), undo log entries 1920 MySQL thread id 437808, query id 1064058 localhost root Sending data replace into popup select '9',p.log,'1' from compte.portefeuilles p, compte.contenus_portefeuilles c InnoDB: error in sec index entry update in InnoDB: index log table popup/popup InnoDB: tuple 0: len 20; hex 70736f7564e92020202020202020202020202020; asc psoud. ;; 1: len 6; hex 0004aadd; asc ª.;; InnoDB: record RECORD: info bits 0 0: len 20; hex 70736f7564652020202020202020202020202020; asc psoude ;; 1: len 6; hex 0003c357; asc .. ...W;; As you can see, I did an upgrade of Mysql on this server yesterday. From 3.23.41 to 3.23.49 . Please see attached file for complete logs. As this server is no more usable we had to stop it, delete all InnoDB files (data+log). We are currently restarting it and recreating databases. Regards, Alex. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[2]: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574
Alex, 616e647265747469 andretti 616e6472e9737069 andr.spi there is a character e9 in your string. It is not an ordinary character and can cause an error in the sorting order between InnoDB-.41 and InnoDB-.49. -Original Message- From: BAUMEISTER Alexandre [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Michael Widenius [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Tuesday, March 19, 2002 3:27 PM Subject: Re[2]: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574 Heikki, HT since you are French, I assume you have accent characters in the strings you HT have stored into your database. HT The ordering of accent characters changed in 3.23.44! No accents. InnoDB tables on this server are mainly used for statistics, counters etc ... HT You must dump and reimport your tables to 3.23.49 so that the index trees HT are sorted in the right order. Since the crash we re-created everything so ... :) But I have still something strange with this server. I have two tables with the same create : create table popup( campagne int(11) unsigned not null, log char(20) not null, cpt int(11), unique(campagne,log), index(log) )type=InnoDB; and create table popup2 ... (same create). Table popup is not empty and is selected/updated from web pages (counting popup screened). I'm inserting some data in table popup2 : mysql insert into popup2 select '9',p.log,'1' from compte.portefeuilles p, compte.contenus_portefeuilles c where c.idport=p.idport and c.symbole='1rPEAD'; Query OK, 41495 rows affected (27.06 sec) Records: 52454 Duplicates: 10959 Warnings: 0 And then trying to insert into table popup all what is in table popup2 : mysql insert into popup select * from popup2; ERROR 1213: Deadlock found when trying to get lock; Try restarting transaction mysql replace into popup select * from popup2; ERROR 1213: Deadlock found when trying to get lock; Try restarting transaction I don't see why this deadlock ... MySQL binlogging requires that INSERT INTO ... SELECT ... sets shared locks on the SELECT table. To avoid the shared locks, use SELECT INTO OUTFILE and LOAD DATA INFILE. Regards, Alex. Regards, Heikki Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re[4]: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574
Alex, -Original Message- From: BAUMEISTER Alexandre [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: Michael Widenius [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Tuesday, March 19, 2002 5:14 PM Subject: Re[4]: InnoDB: Assertion failure in thread 10 in file btr0btr.c line 574 HT 616e647265747469 andretti HT 616e6472e9737069 andr.spi HT there is a character e9 in your string. It is not an ordinary character and HT can cause an error in the sorting order between InnoDB-.41 and InnoDB-.49. ok :) mysql insert into popup select * from popup2; ERROR 1213: Deadlock found when trying to get lock; Try restarting HT transaction mysql replace into popup select * from popup2; ERROR 1213: Deadlock found when trying to get lock; Try restarting HT transaction I don't see why this deadlock ... HT MySQL binlogging requires that INSERT INTO ... SELECT ... sets shared locks HT on the SELECT table. HT To avoid the shared locks, use SELECT INTO OUTFILE and LOAD DATA INFILE. ? MySQL binlogging ? I don't have option log-bin in my.cnf. Is it what you are talking about ? I don't understand, INSERT INTO ... SELECT ... used to work with InnoDB tables. Is it because there are to much selects and updates on the table and so while doing insert InnoDB reaches a lock limit ? no, there are no lock limits in InnoDB. Maybe the updates you are running on popup cause a deadlock with the mass insert? Also in this case the OUTFILE INFILE trick solves the problem because then every insert is run as a separate transaction (I assume you have autocommít=1), and the probability of a deadlock is smaller. Regards, Alex. Regards, Heikki Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inserting records with null foreign keys?
Rick, this is fixed in 3.23.50. I changed InnoDB so that it ignores foreign key checks on keys containing SQL NULL values. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Rick Flower [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Date: Wednesday, March 20, 2002 10:56 PM Subject: Inserting records with null foreign keys? Hi all.. I noticed that the current version of InnoDB that is part of MySQL 3.23.47 does not allow using null foreign keys when doing inserts.. Is this intentional or a bug? This is allowed by SQL Server as a minimum (a test is being done by a co-worker currently against Oracle 8i to see how it behaves). If you don't specify the foreign key, you get a constraint error on the insert.. We're just curious if that is supposed to be that way or not.. If that is a feature, I guess we will have to remove some foreign keys that we've got defined currently.. Thanks for any input on this! -- Rick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Status of InnoDB support.
Gabriel, MySQL-Max-3.23 is now officially stable both in the opinion of MySQL AB and Innobase Oy. It is listed as stable both in the MySQL online manual and on the MySQL AB website front page. The reason for separate MySQL-3.23 and MySQL-Max-3.23 is commercial: there are different support and embedded license prices for these versions. InnoDB version numbers are the same as MySQL version numbers, because new versions appear as part of MySQL distributions. There is currently no standalone version of InnoDB available. Best regards, Heikki Tuuri CEO Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Copied message: . I was just perusing the Innobase Oy site and read that they've declared MySQL/InnoDB 3.23 stable (it actually says they did this in February). I read somewhere on the MySQL site a while ago stating that InnoDB wasn't included in the non-Max distribution because it was still considered beta. I can't find that notice anymore so I guess it's gone. Is InnoDB actually in a stable/1.0/non-beta state now officially? And does anyone know what the schedule is for the build process for the binary distribution of mysql from mysql.com? I'd like to know when the MacOS X binary will be up to 3.23.49(a?), or if I should just download the source and build it myself? Thanks, Gabriel Ricard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump : help needed very urgent
Hi! FOREIGN KEY definitions are stored inside InnoDB data files. Thus, if you make a 'binary' backup, it will preserve foreign key definitions. Starting from 3.23.50 and 4.0.2 mysqldump dumps also foreign key definitions. Previously it forgot them because it did not read them from inside InnoDB. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Monday, March 25, 2002 10:44 AM Subject: Re: mysqldump : help needed very urgent Hi, as far as we are waiting for 3.23.50 this sounds as a stable version to do a backup. :-) But I wonder weather there is also a binary backup for this case? Lets say copy all tables and store in a zip (for ISAM tables ok but InnoDB???), or make a mysqlhotcopy. Adib. Chetan Lavti wrote: thank u very much... but I am planning for one approach is it right .. as by mysqldump we can take back up of Table structure and Insert values separately.. As my database will remain constant through out for the application I can insert the foreign key lines in the dumped file once and as the insert values are in the separate file, when ever I will do backup first I will use the Table structure file which will be constant(will not be backup every time) and then the backup data file. This way I am able to make get the Foreign key even my using the mysqldump.. What do u say ..?? anybody who can suggest me any idea.. I have also tried for the Alter table Add but it is not working. Thanks and regards. Chetan Lavti -Original Message- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 24, 2002 4:33 PM To: Chetan Lavti Cc: [EMAIL PROTECTED] Subject: mysqldump : help needed very urgent Hi! Chetan == Chetan Lavti [EMAIL PROTECTED] writes: Chetan Hi, Chetan I am using Mysql version 3.23.47-Max client and server and using Innodb Chetan table type. Chetan I am doing my database backup using the mysqldump utility. Chetan Once I have done with database backup, I am deleting all my database and Chetan again recovering(creating) the database using the dumped file. Chetan Now, the question is after recovery of the database, I am not getting Chetan the foreign key relationship between the tables. Chetan Why this happens...? is it the problem with mysqldump...?? and if it Chetan really the case then how can i re-create my whole database and keep all Chetan my database retaining the foreign keys. Chetan If anyone can suggest me alternative method for doing the same then it Chetan will be very usful for me. Chetan Waiting for response. The problem here is that in MySQL 3.23 FOREIGN KEY definitions is not part of the mysqldump output. This will be fixed in MySQL 3.23.50 and 4.0.2, both which will be available shortly. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB monitor
Hi! Please look in section 9.1 of http://www.innodb.com/ibman.html Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: [EMAIL PROTECTED] [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Sunday, March 24, 2002 10:50 PM Subject: InnoDB monitor Hi, Can anyone tell me how to use the InnoDB monitor please. After creating the table with the following command: CREATE TABLE innodb_monitor(a int) type = innodb; I don't know where to watch the output from the InnoDB monitor. Can anyone help? Balteo. (sql,query) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB books
Sean, since InnoDB development progresses rapidly, no book except the InnoDB online manual is fully up-to-date. The page http://www.innodb.com/books.html contains a link to Michael Kofler's MySQL book which treats InnoDB and BDB in appendixes. There is also a link to a recent Terra Lycos Webmonkey online article by Jay Greenspan about transactions in MySQL. I have not seen a copy of 'Core Mysql' by Leon Atkinson. Therefore I do not know how comprehensive the InnoDB coverage is in that book. From the contents I see there is a chapter on transactions in MySQL. Since the most important aspect in InnoDB is transactions and multiversion concurrency control, and those are close to Oracle, some general SQL books which are aware of Oracle might also help. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Gabriel Ricard [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Monday, March 25, 2002 7:12 PM Subject: Re: InnoDB books Core MySQL ISBN: 0130661902 http://shop.barnesandnoble.com/booksearch/isbnInquiry.asp?isbn=0130661902 [EMAIL PROTECTED] wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 25 March 2002 07:59 To: Sean O'Donnell Subject: Re: InnoDB books Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: does anyone know of any books that give innodb good coverage? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: newbie question about InnoDB
Olivier, please check that the data types of corresponding columns in the referenced tables are the same, and that you have created the necessary indexes in also the referenced tables. I tested the following: C:\m\client_debugmysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.46-max-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE evaluation(id_evaluation INTEGER NOT NULL, primary key (id_e valuation)) type = innodb; Query OK, 0 rows affected (1.12 sec) mysql mysql CREATE TABLE question(id_question INTEGER NOT NULL, primary key (id_quest ion)) type = innodb; Query OK, 0 rows affected (0.10 sec) mysql mysql CREATE TABLE answer( - id_answer INTEGER NOT NULL, - text_answer VARCHAR (255), - id_evaluation INTEGER NOT NULL, - id_question INTEGER NOT NULL, - scale_answer INTEGER, - PRIMARY KEY(id_answer), - INDEX evaluation_ind (id_evaluation), - INDEX question_ind (id_question), - FOREIGN KEY (id_evaluation) REFERENCES evaluation (id_evaluation), - FOREIGN KEY (id_question) REFERENCES question (id_question)) type=Inn oDB; Query OK, 0 rows affected (0.88 sec) mysql Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB .. hi, I've downloaded mysql-max-nt v.3.23.49, have setup InnoDB startup support. I am now in the process of creating tables. When these are standard tables no problem, eg: create table test (id integer not null, primary key(id)) type=InnoDB; works fine ! I have problems when I have tables with foreign keys. Here is my sql script: CREATE TABLE answer( id_answer INTEGER NOT NULL, text_answer VARCHAR (255), id_evaluation INTEGER NOT NULL, id_question INTEGER NOT NULL, scale_answer INTEGER, PRIMARY KEY(id_answer), INDEX evaluation_ind (id_evaluation), INDEX question_ind (id_question), FOREIGN KEY (id_evaluation) REFERENCES evaluation (id_evaluation), FOREIGN KEY (id_question) REFERENCES question (id_question)) type=InnoDB; There I end up with an error 1005 errno 150 I've seen in the doc that my foreign key syntax might be wrong, but what is wrong ?? Note the same script works fine without type=InnoDB. Any idea ?? Olivier - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
