Re: RE: InnoDB and Windows
Alexandre, Monday, June 03, 2002, 8:08:14 PM, you wrote: AZ Maybe this thread has already been solved. If so im sorry for the disturb. AZ Here is my problem: AZ I want to use InnoDB transactionnal table types with mysql on Windows 2000 . AZ I set my ini file correctly, start the console and my ibdata table is AZ created and I can now create InnoDB tables. AZ The problem is I cnat specify the autoincrement option for the Innodb data AZ file . AZ Here is what my ini looks like now : AZ innodb_data_home_dir = C:\mysql\ibdata AZ innodb_data_file_path =ibdata1:100M AZ This setting works perfectly but the ibdata1 file is not autoincrementing AZ when it is full . AZ And here is what it should look like to have an auto extending innodb data AZ file AZ innodb_data_home_dir = C:\mysql\ibdata AZ innodb_data_file_path =ibdata1:100M:autoextend:max:2000M AZ Now if I try this last setting I get a syntax error on the AZ innodb_data_file_path line when I try to start the Mysql-max server in AZ console mode. AZ I know it is supposed to work as said in the manual. Now is it a known bug AZ on windows platform ??? What version of MySQL server are you using? Autoextend is only supported since version 3.23.50 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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 and Windows
-Message d'origine- De : Alexandre Zglav [mailto:[EMAIL PROTECTED]] Envoyé : lundi, 3. juin 2002 19:05 À : mysql (Elektronikus levelek) Objet : InnoDB and Windows Hello all . Nice to meet you. Maybe this thread has already been solved. If so im sorry for the disturb. Here is my problem: I want to use InnoDB transactionnal table types with mysql on Windows 2000 . I set my ini file correctly, start the console and my ibdata table is created and I can now create InnoDB tables. The problem is I cnat specify the autoincrement option for the Innodb data file . Here is what my ini looks like now : innodb_data_home_dir = C:\mysql\ibdata innodb_data_file_path =ibdata1:100M This setting works perfectly but the ibdata1 file is not autoincrementing when it is full . And here is what it should look like to have an auto extending innodb data file innodb_data_home_dir = C:\mysql\ibdata innodb_data_file_path =ibdata1:100M:autoextend:max:2000M Now if I try this last setting I get a syntax error on the innodb_data_file_path line when I try to start the Mysql-max server in console mode. I know it is supposed to work as said in the manual. Now is it a known bug on windows platform ??? Thanks . Any help apreciated . --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.365 / Virus Database: 202 - Release Date: 24.05.2002 - 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 rollbacks
Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, May 30, 2002 8:43 PM Subject: Re: innodb rollbacks On Thu, May 30, 2002 at 12:16:28PM +0300, Heikki Tuuri wrote: However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. Heikki, That reminds me of an InnoDB wish-list item I have. I like the level of detail provided in the InnoDB monitor output. However, I'd really like to be available via MySQL rather than just in the logs. That will make it a lot easier to collect the data remotely and write apps that can monitor and make use of the data. Have you given any thought to that? (I have no idea what the effort would be like, but it's can't hurt to ask...) it will take me 5 hours effective work time to add something like SHOW INNODB STATUS; I have to add it soon (June 2002), because the upcoming BMC Patrol Knowledge Module for InnoDB needs these stats to draw nice graphs. 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.51: up 0 days, processed 18,745,524 queries (295/sec. avg) Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, 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: InnoDB Delete On Cascade
Hi! Please check that you are using = 3.23.50. I tested this on Win NT-4.0 with 3.23.52, and it worked: mysql CREATE TABLE parent(id INT NOT NULL, - PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.14 sec) mysql CREATE TABLE child(id INT, parent_id INT, - INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) REFERENCES parent(id) - ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql mysql insert into parent values(5); Query OK, 1 row affected (0.00 sec) mysql insert into child values(0,5); Query OK, 1 row affected (0.00 sec) mysql mysql delete from parent where id=5; Query OK, 1 row affected (0.01 sec) mysql mysql mysql select * from parent; Empty set (0.00 sec) mysql select * from child; Empty set (0.01 sec) 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: Me [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, May 31, 2002 11:36 AM Subject: InnoDB Delete On Cascade Hello People, I was just designing my database and I was planning to finally use the new feature provided by InnoDB : ON DELETE CASCADE So I tried the 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) ON DELETE CASCADE ) TYPE=INNODB; Created two records : insert into parent values(5); insert into child values(0,5); Ok this works all fine. Also geives me errors when I give it a parent ID of an unexisting record, so works like it should. Now isn't the goal of ON DELETE CASCADE is that when the parent record is deleted that the child records that reference the parent record id get deleted aswell? Because this doesn't seem to work. I get : mysql delete from parent where id=5; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails Do I have the wrong idea baout the feature or what might be wrong? Using Version 3.23.50-max-nt and 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 - 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 rollbacks
Walt, Jeremy, the undo logs (= rollback segments) in the tablespace generally take much less space than the inserted rows. Thus it is best just to look with SHOW TABLE STATUS what is the available free space. I think in Oracle rollback segments use a lot more space than in InnoDB. However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. 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: Jeremy Zawodny [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 30, 2002 6:31 AM Subject: Re: innodb rollbacks On Tue, May 21, 2002 at 10:41:51AM -0400, walt wrote: Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Right. Do you know if there is a way to view the rollback stats? I checked the InnoDB monitor (details in the InnoDB docs here http://www.innodb.com/ibman.html) but don't see anything. 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.51: up 0 days, processed 3,071,354 queries (252/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 - 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 and My ISAM Tables
Arul, do the equivalent of mysqladmin variables | grep 'inno' | less and check that innodb is up and running. Thomas -- sql, query On Thu, 30 May 2002, Arul wrote: Hi All First of all i would like to know the basic difference between Inno DB and My ISAM Table types.. I wanted mySQL to support AutoIncrement , Transactions,Foreign Key Constraints,Blob, Text etc...so as per the documents i thought of using a InnoDB Table... Currently i am using 3.23.49 Max on Win 2K...I created two tables parent and child 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) ON DELETE CASCADE) TYPE=INNODB; After creating the tables when i executed the Query mysqlshow table status This says that the Table is of type MyISAM. This confuses me a lottt...Also the table i had created is not working for Foreign Key Relations.. What could be the soltuion...Kindly Advice -Arul - 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 and My ISAM Tables
Arul, Thursday, May 30, 2002, 2:05:52 PM, you wrote: A First of all i would like to know the basic difference between Inno DB and A My ISAM Table types.. Check the manual: http://www.mysql.com/doc/T/a/Table_types.html A I wanted mySQL to support AutoIncrement , Transactions,Foreign Key A Constraints,Blob, Text etc...so as per the documents i thought of using a A InnoDB Table... A Currently i am using 3.23.49 Max on Win 2K...I created two tables parent A and child A CREATE TABLE parent(id INT NOT NULL,PRIMARY KEY (id)) TYPE=INNODB; A CREATE TABLE child(id INT, parent_id INT,INDEX par_ind (parent_id),FOREIGN A KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB; A After creating the tables when i executed the Query A mysqlshow table status A This says that the Table is of type MyISAM. If you want to support InnoDB you should set up startup InnoDB option: http://www.mysql.com/doc/I/n/InnoDB_start.html and then run mysqld-max or mysqld-max-nt. You can check if you have sopport fo InnoDB using command: SHOW VARIABLES LIKE have%; If InnoDB is supported, the value of variable have_innodb must be 'YES'. A This confuses me a lottt...Also the table i had created is not working for A Foreign Key Relations.. A What could be the soltuion...Kindly Advice A -Arul A - A Before posting, please check: Ahttp://www.mysql.com/manual.php (the manual) Ahttp://lists.mysql.com/ (the list archive) A To request this thread, e-mail [EMAIL PROTECTED] A To unsubscribe, e-mail [EMAIL PROTECTED] A Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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: innodb rollbacks
On Thu, May 30, 2002 at 12:16:28PM +0300, Heikki Tuuri wrote: However, if there are lots of updates, and the user has a dangling uncommitted consistent read to the database for a long time, then the size of the undo logs may become significant also in InnoDB. I have to consider adding the size info to the InnoDB Monitor. Heikki, That reminds me of an InnoDB wish-list item I have. I like the level of detail provided in the InnoDB monitor output. However, I'd really like to be available via MySQL rather than just in the logs. That will make it a lot easier to collect the data remotely and write apps that can monitor and make use of the data. Have you given any thought to that? (I have no idea what the effort would be like, but it's can't hurt to ask...) 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.51: up 0 days, processed 18,745,524 queries (295/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: innodb rollbacks
On Thu, May 30, 2002 at 10:43:58AM -0700, Jeremy Zawodny wrote: : Heikki, : : That reminds me of an InnoDB wish-list item I have. I like the level : of detail provided in the InnoDB monitor output. However, I'd really : like to be available via MySQL rather than just in the logs. That : will make it a lot easier to collect the data remotely and write apps : that can monitor and make use of the data. : : Have you given any thought to that? (I have no idea what the effort : would be like, but it's can't hurt to ask...) Just for the record, we second that notion. It's a pain in the ass using the InnoDB table monitor simply because it dumps so much output into the log files. There are times when we just want to grab the status at a particular moment, like how one would do SHOW TABLE STATUS or SHOW PROCESSLIST. * 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: innodb rollbacks
On Thu, May 30, 2002 at 02:30:07PM -0500, Philip Molter wrote: On Thu, May 30, 2002 at 10:43:58AM -0700, Jeremy Zawodny wrote: : Heikki, : : That reminds me of an InnoDB wish-list item I have. I like the level : of detail provided in the InnoDB monitor output. However, I'd really : like to be available via MySQL rather than just in the logs. That : will make it a lot easier to collect the data remotely and write apps : that can monitor and make use of the data. : : Have you given any thought to that? (I have no idea what the effort : would be like, but it's can't hurt to ask...) Just for the record, we second that notion. It's a pain in the ass using the InnoDB table monitor simply because it dumps so much output into the log files. There are times when we just want to grab the status at a particular moment, like how one would do SHOW TABLE STATUS or SHOW PROCESSLIST. Right... Something along the lines of SHOW INNODB STATUS would be excellent. 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.51: up 0 days, processed 21,365,291 queries (302/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: InnoDB crash repeated under 3.23.51...
On Wed, May 29, 2002 at 12:13:49AM -0700, Jeremy Zawodny wrote: Heikki, After a good 6-8 hours of runnning, the server just restarted itself (running the most recent build from the 3.23.xx tree, as you suggested). Just for the record, it happened again. So it's reproducable to a dregree. Again, it died on: ut_a(trx-magic_n == TRX_MAGIC_N); in the code. Same stack trace. 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.51: up 0 days, processed 63,534 queries (547/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: InnoDB crash repeated under 3.23.51...
Jeremy,
I think this happens because the InnoDB transaction handle has already been
freed in end_thread when MySQL still writes one log item for the connection.
Please test the following patch. Replace the function
innobase_close_connection with the one below. I forgot to set innobase_tid
to NULL when the handle is freed.
Best regards,
Heikki
/*
Frees a possible InnoDB trx object associated with the current
THD. */
int
innobase_close_connection(
/*==*/
/* out: 0 or error number */
THD* thd) /* in: handle to the MySQL thread of the user
whose transaction should be rolled back */
{
if (NULL != thd-transaction.all.innobase_tid) {
trx_rollback_for_mysql((trx_t*)
(thd-transaction.all.innobase_tid));
trx_free_for_mysql((trx_t*)
(thd-transaction.all.innobase_tid));
thd-transaction.all.innobase_tid = NULL;
}
return(0);
}
- Original Message -
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, May 29, 2002 10:13 AM
Subject: InnoDB crash repeated under 3.23.51...
Heikki,
After a good 6-8 hours of runnning, the server just restarted itself
(running the most recent build from the 3.23.xx tree, as you
suggested). I got this message:
InnoDB: Assertion failure in thread 42527756 in file ha_innobase.cc
line 316
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
And then the stack trace, which resolved as:
0x807d764 handle_segfault__Fi + 428
0x824cc98 pthread_sighandler + 176
0x80cc16a innobase_commit__FP3THDPv + 174
0x80ceba6 innobase_report_binlog_offset_and_commit__FP3THDPvPcUx + 42
0x80c774c ha_report_binlog_offset_and_commit__FP3THDPcUx + 44
0x80b11d2 write__9MYSQL_LOGP15Query_log_event + 1346
0x8051a90 item_user_lock_release__FP3ULL + 548
0x807502d _._3THD + 277
0x807d444 end_thread__FP3THDb + 64
0x80830c1 handle_one_connection__FPv + 1109
I hope that's meaningful to you...
The machine was doig about 800 - 1,000 queries per second when this
happened. Most of them were *not* InnoDB, but some were.
Let me know if there's any other data I can send you to help track
this down. I'll probably convert my tables back to MyISAM soon.
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.51: up 0 days, processed 318,464 queries (1061/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: innodb rollbacks
On Tue, May 21, 2002 at 10:41:51AM -0400, walt wrote: Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Right. Do you know if there is a way to view the rollback stats? I checked the InnoDB monitor (details in the InnoDB docs here http://www.innodb.com/ibman.html) but don't see anything. 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.51: up 0 days, processed 3,071,354 queries (252/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: InnoDB crash on 3.23.49a...
Jeremy, can you build your own 3.23.51? There is actually the same memory overwrite bug in gethostname_r of glibc/Linux in .49a as in .50. If you build mysqld yourself you can run it inside gdb and do bt full when it crashes. That might help in tracing the bug. The assertion failure below means the magic number of a trx struct was wrong. A resolved stack trace might help a lot if this is not random memory corruption. Regards, Heikki - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Heikki Tuuri [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 9:16 PM Subject: InnoDB crash on 3.23.49a... I just converted several tables to InnoDB on our Linux 3.23.49a (binary from MySQL.com). After running our production system against it for about 10 minutes, InnoDB crashed with a stack trace: ---snip--- /home/mysql/bin/mysqld: ready for connections InnoDB: Assertion failure in thread 159781 in file ha_innobase.cc line 302 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 0x806dc04 0x82ad1a8 0x80be1ca 0x80c0aee 0x80b4604 0x809f782 0x804ec80 0x806784d 0x806d8e4 0x80731a1 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instr uctions 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 (nil) is invalid pointer thd-thread_id=3 ---snip--- However, there are no symbols in the mysqld binary so that I can resolve the bug. The trace was identical both times. I've just converted the tables back to MyISAM and will run like this I guess. Is anyone using the pre-release 3.23.50 in production yet? Or should I build my own 3.23.51 and try that? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 4.0.2: up 0 days, processed 3,903,167 queries (96/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: InnoDB crash on 3.23.49a...
On Tue, May 28, 2002 at 10:59:45PM +0300, Heikki Tuuri wrote: Jeremy, can you build your own 3.23.51? There is actually the same memory overwrite bug in gethostname_r of glibc/Linux in .49a as in .50. I'll install 3.23.51 tonight. I have been doing daily builds of the 3.23 and 4.0 tree for a while now, so I have a binary I can use. I just tend not to use custom-built ones on Linux. If you build mysqld yourself you can run it inside gdb and do bt full when it crashes. That might help in tracing the bug. Will do. Let's hope there is no crash next time. :-) The assertion failure below means the magic number of a trx struct was wrong. A resolved stack trace might help a lot if this is not random memory corruption. If it is random memory corruption, it has to be related to MySQL/InnoDB. This box was rock solid and stable until recently. I upgraded from 3.23.47 to 3.23.49a about 36 hours ago. Things ran fine. It didn't get interesting until altered a few tables to convert to InnoDB. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 4.0.2: up 0 days, processed 3,944,927 queries (81/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: InnoDB crash on 3.23.49a...
On Tue, May 28, 2002 at 10:59:45PM +0300, Heikki Tuuri wrote: Jeremy, can you build your own 3.23.51? There is actually the same memory overwrite bug in gethostname_r of glibc/Linux in .49a as in .50. Come to think of it, if that's what caused the problem, wouldn't this solve it for now in my.cnf: skip-name-resolve skip-host-cache ? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 4.0.2: up 0 days, processed 3,945,404 queries (81/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: InnoDB crash on 3.23.49a...
Jeremy, I do not know. I am not an expert on the hostname_r memory overrun bug :). Also, I guess the crash you get does not come from it. Regards, Heikki - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, May 28, 2002 11:18 PM Subject: Re: InnoDB crash on 3.23.49a... On Tue, May 28, 2002 at 10:59:45PM +0300, Heikki Tuuri wrote: Jeremy, can you build your own 3.23.51? There is actually the same memory overwrite bug in gethostname_r of glibc/Linux in .49a as in .50. Come to think of it, if that's what caused the problem, wouldn't this solve it for now in my.cnf: skip-name-resolve skip-host-cache ? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 4.0.2: up 0 days, processed 3,945,404 queries (81/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: InnoDB crash on 3.23.49a...
On Tue, May 28, 2002 at 01:16:28PM -0700, Jeremy Zawodny wrote: On Tue, May 28, 2002 at 10:59:45PM +0300, Heikki Tuuri wrote: Jeremy, can you build your own 3.23.51? There is actually the same memory overwrite bug in gethostname_r of glibc/Linux in .49a as in .50. I'll install 3.23.51 tonight. I have been doing daily builds of the 3.23 and 4.0 tree for a while now, so I have a binary I can use. I just tend not to use custom-built ones on Linux. The 3.23.51 (or pre-3.23.52, I should call it) went well. It has been up for a few hours now and hasn't had a problem yet. And my tables are back in InnoDB format. So I'm keeping my fingers crossed. If nothing else, I'll be able to get a good stack trace if the problem recurs. 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.51: up 0 days, processed 4,161,826 queries (244/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: InnoDB logs
Guys, i get the following error. What does this mean? DBI-connect(myskycastle) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at teamstats.pl line 16 - 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 logs
On Mon, May 27, 2002 at 01:33:30PM -0600, Sherzod B. Ruzmetov wrote: Guys, i get the following error. What does this mean? DBI-connect(myskycastle) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at teamstats.pl line 16 Please *don't* hijack message threads. This has *nothing* to do witih InnoDB logs. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 4.0.2: up 3 days, processed 34,969,270 queries (114/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: InnoDB, possible bug?
Hi! Heikki == Heikki Tuuri [EMAIL PROTECTED] writes: Heikki Andrei, Heikki this is probably not a bug in InnoDB. In theory, 4000 random disk seeks Heikki would use more time than scanning the whole table of 700 000 rows. Heikki The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over Heikki table scans. That may solve the problem here. Heikki On the other hand, the fact that MySQL refuses to use the index specified in Heikki the USE INDEX clause may be a bug. I have forwarded this email to MySQL Heikki developers. USE INDEX ... only tells MySQL that it should only consider using one of the named index to resolve the query. MySQL is however still free to use a table scan if finds the given index not suitable for resolving the query. Heikki Best regards, Heikki Heikki Tuuri Heikki Innobase Oy Heikki --- Heikki Order technical MySQL/InnoDB support at https://order.mysql.com/ Heikki See http://www.innodb.com for the online manual and latest news on InnoDB Heikki - Original Message - Heikki From: Andrei Cojocaru [EMAIL PROTECTED] Heikki To: Heikki Tuuri [EMAIL PROTECTED]; Mysql List Heikki [EMAIL PROTECTED] Heikki Sent: Wednesday, May 22, 2002 3:16 AM Heikki Subject: Re: InnoDB, possible bug? I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 4218 | +--+ 1 row in set (13.81 sec) mysql select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 3705 | +--+ Do you know why the result differs in this case ? mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp Heikki from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; Heikki +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | Heikki +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || Heikki +---++---+-+-+---+-- show index from newsentries10; cut | newsentries10 | 1 | list_news |1 | playerid | A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |2 | type cut Heikki, something is a bit strange here. In this case MySQL will ask the InnoDB table handler of how many rows matches the key range (ne.playerid,ne.type) [28575, 2] In this case, InnoDB should return about 4000 rows, but it appears that it returns 77 rows. Andrei, could you upload a copy of the tables to ftp://support.mysql.com/pub/mysql/secret so that Heikki could test this ? 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
Re: innodb rollbacks
On Mon, May 20, 2002 at 12:25:26PM -0400, walt wrote: Does anyone know where rollbacks are stored for innodb tables? I assume memory since I keep getting a table full error when trying to drop an index. sql,query I'm not exactly sure what you're asking, but the rollback information needs to be stored on disk. Otherwise it cannot recover form a crash. Anyway, I suspect that your problem is that your tablespace is full. Can you try added another file and see if that makes the problem go away? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.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: innodb rollbacks
On Tuesday 21 May 2002 12:24 am, Jeremy Zawodny wrote: On Mon, May 20, 2002 at 12:25:26PM -0400, walt wrote: Does anyone know where rollbacks are stored for innodb tables? I assume memory since I keep getting a table full error when trying to drop an index. sql,query I'm not exactly sure what you're asking, but the rollback information needs to be stored on disk. Otherwise it cannot recover form a crash. Anyway, I suspect that your problem is that your tablespace is full. Can you try added another file and see if that makes the problem go away? Jeremy Thanks for your reply Jeremy! I'm an Oracle person, so I tend to think of things being in multiple files/tablespaces. Do you know if there is a way to view the rollback stats? I've got 6GB of datafiles and only 1 table loaded with 46 rows, 14 indexes, and approx 1.6million records. I'm trying now to load all the data again in a seperate table to see what it takes to fill up the datafiles. Thanks again! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - 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, possible bug?
Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? 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: Andrei Cojocaru [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table structures for the two tables are: mysql desc newsentries10; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | newsid| int(10) unsigned| | MUL | 0 | | | playerid | int(10) unsigned| | MUL | 0 | | | hidestamp | int(10) unsigned| | | 0 | | | viewpoint | tinyint(3) unsigned | | | 0 | | | type | tinyint(3) unsigned | | | 0 | | | delmarker | tinyint(3) unsigned | | MUL | 0 | | +---+-+--+-+-+---+ 6 rows in set (0.00 sec) mysql desc pnews; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | newsid| int(10) unsigned| | PRI | NULL| auto_increment | | type | tinyint(3) unsigned | | | 0 | | | id| int(10) unsigned| | MUL | 0 | | | timestamp | int(10) unsigned| | | 0 | | +---+-+--+-+-++ 4 rows in set (0.00 sec) the indexs are: mysql show index from newsentries10; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | newsentries10 | 1 | delmarker |1 | delmarker | A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |1 | playerid| A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |2 | type| A | 0 | NULL | NULL | | | newsentries10 | 1 | delete_news |1 | newsid | A | 76379 | NULL | NULL | | +---++-+--+-+--- +-+--++-+ 4 rows in set (0.20 sec) mysql show index from pnews; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | pnews | 0 | PRIMARY |1 | newsid | A | 139047 | NULL | NULL | | | pnews | 1 | id |1 | id | A | 139047 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 2 rows in set (0.07
Re: InnoDB, possible bug?
I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 4218 | +--+ 1 row in set (13.81 sec) mysql select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 3705 | +--+ 1 row in set (9.72 sec) mysql select count(*) from newsentries10 where type=2; +--+ | count(*) | +--+ | 611932 | +--+ 1 row in set (9.92 sec) mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.01 sec) mysql mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) mysql mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 21, 2002 12:40 PM Subject: Re: InnoDB, possible bug? Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? 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: Andrei Cojocaru [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table
Re: InnoDB, possible bug?
Andrei, this is probably not a bug in InnoDB. In theory, 4000 random disk seeks would use more time than scanning the whole table of 700 000 rows. The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over table scans. That may solve the problem here. On the other hand, the fact that MySQL refuses to use the index specified in the USE INDEX clause may be a bug. I have forwarded this email to MySQL developers. 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: Andrei Cojocaru [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 3:16 AM Subject: Re: InnoDB, possible bug? I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 4218 | +--+ 1 row in set (13.81 sec) mysql select count(*) from newsentries10 where playerid=28575 and type=2; +--+ | count(*) | +--+ | 3705 | +--+ 1 row in set (9.72 sec) mysql select count(*) from newsentries10 where type=2; +--+ | count(*) | +--+ | 611932 | +--+ 1 row in set (9.92 sec) mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.01 sec) mysql mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) mysql mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 21, 2002 12:40 PM Subject: Re: InnoDB, possible bug? Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? 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: Andrei Cojocaru [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow
Re: --- InnoDB for Win
maxim wrote: Hi? all Can i use InnoDB tables in MySQL Max for Win9x ? Thanks... Hi Maxim, Yes, if you use the -max version, you can. -- -- \|/ __\|/ `@ / o . \ @' Microsoft? Por acaso é alguma nova /___| \/ |___\ marca de papel higiênico? \___U__/ .^. Luciano Barcaro - Linux User # 99517 /v\ Registre-se gratuitamente em // \\http://counter.li.org //(.)\\ ICQ # 17266954 ^`~'^ -- - 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 Key Constraints
Daniel, - Original Message - From: Daniel Rand [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 13, 2002 5:24 PM Subject: InnoDB Foreign Key Constraints Hi, Does anyone know if it's possible to set up a foreign key constraint where one table references the primary key of another table in a different database (both tables being of InnoDB type). yes, you can use the databasename.tablename syntax: heikki@hundin:~/mysql/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.51-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show create table aaa; +---+--- ---+ | Table | Create Table | +---+--- ---+ | aaa | CREATE TABLE `aaa` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) TYPE=InnoDB | +---+--- ---+ 1 row in set (0.01 sec) mysql use test11 Database changed mysql create table mmm (a int not null, b int, primary key (a), index (b), fore ign key (b) references test.aaa (a)); Query OK, 0 rows affected (0.00 sec) mysql show create table mmm; +---+--- + | Table | Create Table | +---+--- + | mmm | CREATE TABLE `mmm` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`), FOREIGN KEY (`b`) REFERENCES `test.aaa` (`a`) ) TYPE=InnoDB | +---+--- + 1 row in set (0.00 sec) mysql Thanks, DAN Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, 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: Innodb
At 17:26 -0300 5/10/02, Edilson Vasconcelos de Melo Junior wrote: Hi, What versions of mysql support innodb tables? What about 3.23.45 or 3.23.47? Both versions, but only if you - Compile from source using the --with-innodb option when you configure MySQL - Use a -max distribution, if you use a binary distribution Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 - 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
mysqladmin variables * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Edilson Vasconcelos de Melo Junior [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 4:04 PM To: Paul DuBois; MYSQL Subject: RES: Innodb Hi, The server is not mine :( How do i know if the source was compiled with --with-innodb option? Thank u very much, Edilson. -Mensagem original- De: Paul DuBois [mailto:[EMAIL PROTECTED]] Enviada em: sexta-feira, 10 de maio de 2002 17:47 Para: Edilson Vasconcelos de Melo Junior; MYSQL Assunto: Re: Innodb At 17:26 -0300 5/10/02, Edilson Vasconcelos de Melo Junior wrote: Hi, What versions of mysql support innodb tables? What about 3.23.45 or 3.23.47? Both versions, but only if you - Compile from source using the --with-innodb option when you configure MySQL - Use a -max distribution, if you use a binary distribution Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 - 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 tables problem
Oren,
what MySQL version you are running? What OS?
Send me a copy of your my.cnf, the error log 'hostname'.err, and the exact
sequence of SQL statements you used to crash InnoDB, including the table
CREATE statements. Did InnoDB run out of tablespace?
Regarding the performance, did you set innodb_flush_log_at_trx_commit=0 when
you tried to use the script to pipe inserts to 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
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, May 07, 2002 3:16 PM
Subject: innodb tables problem
I have defined some InnoDB tables and try to feed one of them
with 3,000,000 records.
Using a script piped to mysql.
First I tried to insert the rows one by one. It worked very slow. I didn't
wait to see if it ends.
Next try was to fill some temporary table with the numbers 1..3,000,000. I
was able to do it with Log(3,000,000) inserts.
select count(*) from TmpTbl; returns 3,000,000 successfully.
And now I used 'insert null,Num,concat('user',Num) into .. select Num from
TmpTbl'
(null is for a auto_increment primary key).
This fails, the mysqld crashed, restarts, trying to roll back the
transaction,
and I've been wating for a long time and the database is still not
responsive.
Do I need to set some parameters?
Do you know a better way to do this?
P.S.
I'm also using log-bin (so another mysqld will be the slave of this one).
Oren Zeev-Ben-Mordehai
Infrastructure Engineer
PhoneDo Networks
office: +972-9-951-7771 ext. 204
fax:+972-9-951-7772
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
-
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 problem
Oren,
I did not find any clear crash from the error log. Looks like MySQL somehow
failed in shutdown:
...
020507 9:59:12
/usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: Forcing close
of thread 109 user: 'root'
A mysqld process already exists at Tue May 7 09:59:39 IDT 2002
...
Below we see that the big rollback actually finished successfully in 45
minutes:
020507 10:25:39 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 94594158
InnoDB: Doing recovery: scanned up to log sequence number 0 94659584
InnoDB: Doing recovery: scanned up to log sequence number 0 94725120
...
InnoDB: Rolling back trx with id 0 315126
020507 10:51:38 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
97 98 99
InnoDB: Apply batch completed
InnoDB: Rolling back of trx id 0 315126 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Last MySQL binlog file offset 0 7074979, file name ./sun06-bin.024
020507 11:11:26 InnoDB: Flushing modified pages from the buffer pool...
020507 11:11:27 InnoDB: Started
Status information:
Current dir: /usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/data/
Current locks:
key_cache status:
blocks used: 0
not flushed: 0
w_requests: 0
writes: 0
r_requests: 0
reads: 0
handler status:
read_key:0
read_next: 0
read_rnd 0
read_first: 0
write: 0
delete 0
update: 0
Table status:
Opened tables: 0
Open tables:0
Open files: 2
Open streams: 0
/usr/local/mysql-max-3.23.48-sun-solaris2.8-sparc/bin/mysqld: ready for
connections
020507 11:11:27 Error in accept: Software caused connection abort
...
Looks like the communication between the client and the server is somehow
blocked. You can try killing all mysqld and client processes, and restarting
mysqld.
To get good performance please set my.cnf options as recommended in section
2 of http://www.innodb.com/ibman.html.
Regards,
Heikki
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Sent: Tuesday, May 07, 2002 5:26 PM
Subject: RE: innodb tables problem
my.cnf
--
[mysqld]
innodb_data_file_path = ibdata1:2000M
log-bin
server-id=37
hostname.err
(Attached)
--
I didn't use 'set innodb_flush_log_at_trx_commit=0'. I'm going to try
this.
Table creation:
create table A (
A1 int(11) not null auto_increment,
A2 varchar(32) not null,
A3 int(11),
A4 varchar(32) not null,
primary key (A1),
index (A4),
unique index (A3,A4),
foreign key (A4) B (B4)
) type = InnoDB;
insertion:
printf insert into A select null,concat('user',Num+1),1,$base_number+Num
from TmpTbl;\n
Where TmpTbl contains the numbers 1..3,000,000
Regards,
Oren.
-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 07, 2002 3:11 PM
To: Oren Zeev-Ben-Mordehai
Cc: [EMAIL PROTECTED]
Subject: Re: innodb tables problem
Oren,
what MySQL version you are running? What OS?
Send me a copy of your my.cnf, the error log 'hostname'.err, and the
exact
sequence of SQL statements you used to crash InnoDB, including the table
CREATE statements. Did InnoDB run out of tablespace?
Regarding the performance, did you set innodb_flush_log_at_trx_commit=0
when
you tried to use the script to pipe inserts to 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
- Original Message -
From: Oren Zeev-Ben-Mordehai [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Tuesday, May 07, 2002 3:16 PM
Subject: innodb tables problem
I have defined some InnoDB tables and try to feed one of them
with 3,000,000 records.
Using a script piped to mysql.
First I tried to insert the rows one by one. It worked very slow. I
didn't
wait to see if it ends.
Next try was to fill some temporary table with the numbers 1..3,000,000.
I
was able to do it with Log(3,000,000) inserts.
select count(*) from TmpTbl; returns 3,000,000 successfully.
And now I used 'insert null,Num,concat('user',Num) into .. select Num
from
TmpTbl'
(null is for a auto_increment primary key).
This fails, the mysqld crashed, restarts, trying to roll back the
transaction,
and I've been wating for a long time and the database is still
RE: innodb problem (with JDBC/transactions)
Hi, We are currently facing the same problem (Deadlock found when trying to get lock; Try restarting transaction) in our production environnement. We are using InnoDB tables (mysqk 3.23.48-max) with Jboss 2.4.4 and JDBC driver mm.mysql-2.0.11-bin.jar / RedHat 7.1. Could you please tell me how to fix this ? Thanks, jb - 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 transactions with Connection Pooling
On Mon, Apr 22, 2002 at 09:02:54AM +0300, Heikki Tuuri wrote: Mark, if you do not explicitly do SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. Make that: SET AUTOCOMMIT=1 Heikki is probably low on coffee. :-) 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.47-max: up 73 days, processed 1,941,489,847 queries (304/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: InnoDB transactions with Connection Pooling
Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, April 22, 2002 9:37 AM Subject: Re: InnoDB transactions with Connection Pooling On Mon, Apr 22, 2002 at 09:02:54AM +0300, Heikki Tuuri wrote: Mark, if you do not explicitly do SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. Make that: SET AUTOCOMMIT=1 MySQL has AUTOCOMMIT=1 as the default. Thus if you do not explicitly change the value with SET AUTOCOMMIT=0, then MySQL calls commit after each SQL statement. Heikki is probably low on coffee. :-) Jeremy, it is midnight there, morning here :). 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.47-max: up 73 days, processed 1,941,489,847 queries (304/sec. avg) 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: InnoDB, BDB
Samim, - Original Message - From: Samim [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, April 22, 2002 11:31 AM Subject: InnoDB, BDB Hello, This question is probably repeated each week, but I am an absolute beginner with MySQL. Sorry for that. I've installed the precompiled binaries for Win, and I am very satisfied with MySQL. The only thing I couldn't find is support for transactions, although it was written in the documentation that support for BDB and InnoDB table types is activated in precompiled binaries. Is there something I've missed to configure (I've configured the necessary innodb_data_file_path and innodb_data_home_dir for InnoDB) or do I have to recompile MySQL with support for BDB and InnoDB activated? I've tried mysqld-max-nt and mysqld-max, but have_bdb and have_innodb variables were 'NO'. I use version 3.23.49. the following manual section is a guide to creating an InnoDB database. If you have already installed mysqld... as a service, stop the service from the Control Panel of Windows. Start mysqld-max.exe from an MS-DOS prompt as instructed below. 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 [mysqld] # You can write your other MySQL server options here # ... # innodb_data_home_dir = c:\ibdata #Data files must be able to #hold your data and indexes innodb_data_file_path = ibdata1:2000M;ibdata2:2000M #Set buffer pool size to 50 - 80 % #of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M innodb_log_group_home_dir = c:\iblogs #.._log_arch_dir must be the same #as .._log_group_home_dir innodb_log_arch_dir = c:\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=10M 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 Note that InnoDB does not create directories: you have to create them yourself. Use the Unix or MS-DOS mkdir command to create the data and log group home directories. Check also that the MySQL server has the rights to create files in the directories you specify. Note that data files must be 2G in some file systems! The combined size of data files must be = 10 MB. The combined size of the log files must be 4G. If you do not specify innodb_data_home_dir, then the default is that InnoDB creates its data files to the datadir of MySQL. Then you cannot use absolute file paths in innodb_data_file_path. When you the first time create an InnoDB database, it is best that you start the MySQL server from the command prompt. Then InnoDB will print the information about the database creation to the screen, and you see what is happening. See below in section 3 what the printout should look like. For example, in Windows you can start mysqld-max.exe with: your-path-to-mysqldmysqld-max --standalone --console Where to put my.cnf or my.ini in Windows? The rules for Windows are the following: Only one of my.cnf or my.ini should be created. The my.cnf file should be placed in the root directory of the drive C:. The my.ini file should be placed in the WINDIR directory, e.g, C:\WINDOWS or C:\WINNT. You can use the SET command of MS-DOS to print the value of WINDIR. If your PC uses a boot loader where the C: drive is not the boot drive, then your only option is to use the my.ini file. Thanks! Samim - 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, BDB
Samim, Monday, April 22, 2002, 11:20:48 AM, you wrote: S This question is probably repeated each week, but I am an absolute beginner S with MySQL. Sorry for that. I've installed the precompiled binaries for Win, S and I am very satisfied with MySQL. The only thing I couldn't find is S support for transactions, although it was written in the documentation that S support for BDB and InnoDB table types is activated in precompiled binaries. S Is there something I've missed to configure (I've configured the necessary S innodb_data_file_path and innodb_data_home_dir for InnoDB) or do I have to S recompile MySQL with support for BDB and InnoDB activated? I've tried S mysqld-max-nt and mysqld-max, but have_bdb and have_innodb variables were S 'NO'. I use version 3.23.49. You should install MySQL-Max for InnoDB and BDB tables support, you can read about mysqld-max at: http://www.mysql.com/doc/m/y/mysqld-max.html If you want to use InnoDB tables you must specify startup options in my.cnf file. You can find examples and info about configuration parameters at: http://www.mysql.com/doc/I/n/InnoDB_start.html S Thanks! S Samim -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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: InnoDB and Fulltext Search
Ang, Monday, April 22, 2002, 12:24:58 PM, you wrote: AHK Quote from mysql online documentations: AHK As of Version 4.0.1, MySQL can also perform boolean AHK full-text AHK searches using the IN BOOLEAN MODE modifierA AHK boolean full-text AHK search can also work even without a FULLTEXT index, AHK although it would AHK be slow. AHK I know that InnoDB does not support FULLTEXT INDEXING AHK but can i AHK perform a fulltext search on an InnoDB table? I tried AHK but it doesn't AHK seem to work. The 'match... against()' returns a value AHK of '-1'. Any AHK idea? No, Full-text search still works only on MyISAM tables ... AHK Thanx. AHK ahk -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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: InnoDB data files created but not used...how to fix ?
Jesper, what may have happened is that InnoDB created the data files, but because memory ran out, it did not have time to update the tablespace header to reflect the size increase by those two data files. Later when you added yet another data file, it increased the size stored in the tablespace header. The new data you have added has probably gone to those two data files you added first. InnoDB is aware of the data files you specify, but it thinks that the tablespace size is smaller than the combined size of the data files! What to do? The safest solution is to dump your tables and recreate the whole InnoDB tablespace. If you can afford losing the data in the database, then you can experiment by removing 1 or 2 last files from innodb_data_file_path and look if the database crashes in an assertion failure when you insert new data. Regarding your other question about my.cnf parameter, section 2 of http://www.innodb.com/ibman.html is your best 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: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, April 22, 2002 3:35 PM Subject: InnoDB data files created but not used...how to fix ? Hi! A couple of days ago I stopped a MySQL server to change a couple of parameters in the my.cnf file and to add 2 data files (InnoDB). When restarting it, MySQL exited because I'd resized the log file size but forgot to delete/move the old ones. Secondly, I had another error because I allocated more memory than ulimit allowed. I fixed both, restarted, but then it exited because it had created the first datafile with a size of 0. I deleted the ibdata file and restarted, and now it created both data files and started correctly. However, the two new datafiles are not used, and the data they were supposed to add doesn't show up in a show table status. I've later created another data file, and this one works. ...so the question is : How to fix those two ? Can I just shutdown MySQL, rename them and then re-start MySQL and have the files re-created, or is there another solution ? /Jesper - 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 Error 150, MySQL 4.0.1 alpha 3.23.49
Richard,
- Original Message -
From: Richard Harms [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Monday, April 22, 2002 2:32 AM
Subject: InnoDB Error 150, MySQL 4.0.1 alpha 3.23.49
Hello,
When attempting to create some tables using the MySQL 4.0.1 alpha and
3.23.49, I'm getting a generic error message, General error: Can't
create table './industria/ORDEREDITEMS.frm' (errno: 150). The other 6
tables, all using similar features, are created without a problem.
perror just says, Error code 150: Unknown error 150 which isn't
terribly helpful.
Some assistance with this would be appreciated. :-)
-rh
CREATE TABLE VENDORS ( V_VENDORID BIGINT NOT NULL, V_VENDORNAME
VARCHAR(20) NOT NULL, V_DESCRIPTION VARCHAR(80) NOT NULL, V_LOCKCNT
BIGINT NOT NULL DEFAULT '0', V_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(V_VENDORID), UNIQUE KEY VENDORS_VENDORNAME_IDX (V_VENDORNAME) )
TYPE=InnoDB;
CREATE TABLE ITEMS ( I_ITEMID BIGINT NOT NULL, I_V_VENDORID BIGINT NOT
NULL, I_ITEMNAME VARCHAR(20) NOT NULL, I_DESCRIPTION VARCHAR(80) NOT
NULL, I_ACTIVE ENUM('T','F') NOT NULL, I_PRICE DECIMAL(10, 2) NOT NULL,
I_LOCKCNT BIGINT NOT NULL DEFAULT '0', I_TIMESTAMP TIMESTAMP NOT NULL,
PRIMARY KEY (I_ITEMID), UNIQUE KEY ITEMS_VENDORITEM_IDX (I_V_VENDORID,
I_ITEMNAME), FOREIGN KEY (I_V_VENDORID) REFERENCES VENDORS(V_VENDORID)
ON DELETE CASCADE ) TYPE=InnoDB;
CREATE TABLE CATALOGS ( C_CATALOGID BIGINT NOT NULL, C_CATALOGNAME
VARCHAR(20) NOT NULL, C_DESCRIPTION VARCHAR(80) NOT NULL, C_LOCKCNT
BIGINT NOT NULL DEFAULT '0', C_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(C_CATALOGID), UNIQUE KEY CATALOGS_CATALOGNAME_IDX (C_CATALOGNAME) )
TYPE=InnoDB;
CREATE TABLE CATEGORIES ( CG_CATEGORYID BIGINT NOT NULL, CG_C_CATALOGID
BIGINT NOT NULL, CG_CATEGORYNAME VARCHAR(20) NOT NULL, CG_CG_CATEGORYID
BIGINT NOT NULL, CG_DESCRIPTION VARCHAR(80) NOT NULL, CG_LOCKCNT BIGINT
NOT NULL DEFAULT '0', CG_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(CG_CATEGORYID), UNIQUE KEY CATEGORIES_CATALOGCATEGORY_IDX
(CG_C_CATALOGID, CG_CATEGORYNAME), KEY CATEGORIES_CATALOGID_IDX
(CG_C_CATALOGID), KEY CATEGORIES_PARENTCATEGORYID_IDX
(CG_CG_CATEGORYID), FOREIGN KEY (CG_C_CATALOGID) REFERENCES
CATALOGS(C_CATALOGID) ON DELETE CASCADE, FOREIGN KEY (CG_CG_CATEGORYID)
REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE CASCADE ) TYPE=InnoDB;
CREATE TABLE CATAGORYITEMS ( CI_CG_CATEGORYID BIGINT NOT NULL,
CI_I_ITEMID BIGINT NOT NULL, CI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
CI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (CI_CG_CATEGORYID,
CI_I_ITEMID), KEY CATAGORYITEMS_ITEMID_IDX (CI_I_ITEMID), FOREIGN KEY
(CI_CG_CATEGORYID) REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE
CASCADE, FOREIGN KEY (CI_I_ITEMID) REFERENCES ITEMS(I_ITEMID) ON DELETE
CASCADE ) TYPE=InnoDB;
CREATE TABLE ORDERS ( O_ORDERID BIGINT NOT NULL, O_ORDEREDITEMSEQUENCE
INT NOT NULL, O_LOCKCNT BIGINT NOT NULL DEFAULT '0', O_TIMESTAMP
TIMESTAMP NOT NULL, PRIMARY KEY (O_ORDERID) ) TYPE=InnoDB;
CREATE TABLE ORDEREDITEMS ( OI_O_ORDERID BIGINT NOT NULL,
OI_ORDEREDITEMSEQUENCE INT NOT NULL, OI_I_ITEMID BIGINT NOT NULL,
OI_QUANTITY INT(4) NOT NULL, OI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
OI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (OI_O_ORDERID,
OI_ORDEREDITEMSEQUENCE), FOREIGN KEY (OI_O_ORDERID) REFERENCES
ORDERS(O_ORDERID) ON DELETE CASCADE, FOREIGN KEY (OI_I_ITEMID)
REFERENCES ITEMS(I_ITEMID) ON DELETE CASCADE ) TYPE=InnoDB;
there is no index on OI_I_ITEMID. InnoDB requires that a suitable index is
explicitly defined for a foreign key. Also note that ON DELETE CASCADE only
works from 3.23.50 up. When you migrate to 3.23.50, you must recreate your
tables so that InnoDB is aware of ON DELETE CASCADE.
java.sql.SQLException: General error: Can't create table
'./industria/ORDEREDITEMS.frm' (errno: 150)
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Statement.executeQuery(Unknown Source)
at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Unknown Source)
at
com.darkrealms.entities.DatabaseUtilities.createMissingTable(DatabaseUti
lities.java:432)
at
com.darkrealms.entities.DatabaseUtilities.checkTables(DatabaseUtilities.
java:464)
at
com.darkrealms.entities.DatabaseUtilities.openDatabaseConnection(Databas
eUtilities.java:38)
at
com.darkrealms.entities.DatabaseUtilitiesForMySQL.init(DatabaseUtiliti
esForMySQL.java:19)
at
com.darkrealms.entities.DatabaseFromXML.main(DatabaseFromXML.java:17)
Exception in thread main
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
Re: InnoDB transactions with Connection Pooling
Mark, if you do not explicitly do SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. If you set AUTOCOMMIT=0, then you should yourself call COMMIT after each SELECT so that you do not leave a dangling transaction open in the database and that you get a fresh snapshot of the database in each consistent read. 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: Mark Hazen [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, April 22, 2002 3:35 AM Subject: InnoDB transactions with Connection Pooling Fellow MySQL gurus, I am using Apache::DBI to accomplish connection pooling. I am working with an InnoDB table that gets updated very frequently. My question is this: Since my connections are pooled and stay open for days at a time, am I essentially always going to read from that connection the same version of the database (even from request to request). My guess is yes and that I would need to do a COMMIT before every request (or after). Maybe someone can shed some light on this... Example: Table innodb_test has 2 rows. Connection ID 1, Apache Request 1 SELECT * FROM innodb_test; It spits back 2 rows. Then some other thread adds 3 rows to the table, and COMMITs them. Connection ID 1, Apache Request 2 (notice that it is the same connection, just a new web page request) SELECT * FROM innodb_test; My guess is that it would spit back the same 2 rows again and not 5. I would need to do a COMMIT either before or after each request. Is this right? Does anyone have an opinion on whether I should do it after or before. I would assume after because the request could then already be served to the user (I don't need up-to-the-picosecond results). Thanks! Mark - 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
Rodrigo, from the manual: If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 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: Rodrigo Gonzalez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, April 12, 2002 4:20 PM Subject: InnoDB I install mysql-max 3.23.50 in my test box and start testing innodb tables I found two questionsfirst of all I used autoextend feature (it's really great) but I cannot now add another file to the tablespace The second question is if I am configuring something wrong, the same query which takes 80 seconds in MyIsam tables takes 640 in InnoDb tables Here is my InnoDB definition in my.cnf # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:256M;ibdata2:256M;ibdata3:128M:autoextend:max:2000M innodb_data_home_dir = /usr/local/mysql_50/data/ innodb_log_group_home_dir = /usr/local/mysql_50/data/ innodb_log_arch_dir = /usr/local/mysql_50/data/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=64M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 The test box is a PIII with 192MB - 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
You are right, I didnot see this section so well BTW: I am really interested in InnoDB features, do you have an answer for the second question about queries taking so long time? Regards, Rodrigo Gonzalez. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 12, 2002 3:51 PM Subject: Re: InnoDB Rodrigo, from the manual: If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 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: Rodrigo Gonzalez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, April 12, 2002 4:20 PM Subject: InnoDB I install mysql-max 3.23.50 in my test box and start testing innodb tables I found two questionsfirst of all I used autoextend feature (it's really great) but I cannot now add another file to the tablespace The second question is if I am configuring something wrong, the same query which takes 80 seconds in MyIsam tables takes 640 in InnoDb tables Here is my InnoDB definition in my.cnf # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:256M;ibdata2:256M;ibdata3:128M:autoextend:max:2000M innodb_data_home_dir = /usr/local/mysql_50/data/ innodb_log_group_home_dir = /usr/local/mysql_50/data/ innodb_log_arch_dir = /usr/local/mysql_50/data/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=64M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 The test box is a PIII with 192MB - 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: InnoDB
Rodrigo, - Original Message - From: Rodrigo Gonzalez [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Friday, April 12, 2002 10:27 PM Subject: Re: InnoDB You are right, I didnot see this section so well BTW: I am really interested in InnoDB features, do you have an answer for the second question about queries taking so long time? I am sorry, but MySQL AB and Innobase Oy developers can help in tuning only if you have bought a technical support contract. Regards, Rodrigo Gonzalez. 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: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 12, 2002 3:51 PM Subject: Re: InnoDB Rodrigo, from the manual: If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 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: Rodrigo Gonzalez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, April 12, 2002 4:20 PM Subject: InnoDB I install mysql-max 3.23.50 in my test box and start testing innodb tables I found two questionsfirst of all I used autoextend feature (it's really great) but I cannot now add another file to the tablespace The second question is if I am configuring something wrong, the same query which takes 80 seconds in MyIsam tables takes 640 in InnoDb tables Here is my InnoDB definition in my.cnf # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:256M;ibdata2:256M;ibdata3:128M:autoextend:max:2000M innodb_data_home_dir = /usr/local/mysql_50/data/ innodb_log_group_home_dir = /usr/local/mysql_50/data/ innodb_log_arch_dir = /usr/local/mysql_50/data/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=64M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 The test box is a PIII with 192MB - 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: InnoDB
Is just a tunning problem?Well, no problem, I continue testing Regards. - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: Rodrigo Gonzalez [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Friday, April 12, 2002 4:38 PM Subject: Re: InnoDB Rodrigo, - Original Message - From: Rodrigo Gonzalez [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Friday, April 12, 2002 10:27 PM Subject: Re: InnoDB You are right, I didnot see this section so well BTW: I am really interested in InnoDB features, do you have an answer for the second question about queries taking so long time? I am sorry, but MySQL AB and Innobase Oy developers can help in tuning only if you have bought a technical support contract. Regards, Rodrigo Gonzalez. 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: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 12, 2002 3:51 PM Subject: Re: InnoDB Rodrigo, from the manual: If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of ibdata1, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of ibdata1 explicitly in innodb_data_file_path. After that you can add another data file: innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 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: Rodrigo Gonzalez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, April 12, 2002 4:20 PM Subject: InnoDB I install mysql-max 3.23.50 in my test box and start testing innodb tables I found two questionsfirst of all I used autoextend feature (it's really great) but I cannot now add another file to the tablespace The second question is if I am configuring something wrong, the same query which takes 80 seconds in MyIsam tables takes 640 in InnoDb tables Here is my InnoDB definition in my.cnf # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:256M;ibdata2:256M;ibdata3:128M:autoextend:max:2000M innodb_data_home_dir = /usr/local/mysql_50/data/ innodb_log_group_home_dir = /usr/local/mysql_50/data/ innodb_log_arch_dir = /usr/local/mysql_50/data/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=64M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 The test box is a PIII with 192MB - 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: InnoDB tables
On Tue, Apr 09, 2002 at 10:17:56AM +0200, Luke van Blerk wrote: Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables That's a great sign for you. :-) but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Of course things will be slower with foreign keys. The question is how much slower. I suspect that the difference will be very, very minor. But why not run a benchmark with FKs and see? 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.47-max: up 63 days, processed 1,719,288,043 queries (312/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: InnoDB tables
Luke, How exactly will the foreign keys save you time? Please explain. Ric. - Original Message - From: Luke van Blerk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 9:17 AM Subject: InnoDB tables Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Thanks in advance Luke van Blerk mysql - 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 tables
Hi Richard I'm writing a web application in PHP with a MySQL backend. I'm a novice at both PHP and MySQL having started with both about November last year and I'm also new to programming at large so please excuse any question which may seem obvious. The foreign keys are to be used to enforce referential integrety in the application -- e.g. If I try to delete a specific entry from the 'town' table it won't allow me to if there are entries in the 'advertiser' table which refer to that town. At the moment I'm using a table called 'advertisers_towns' which stores the ID of each entity and thereby the relationship is maintained. But if I can have the foreign keys operating this would cut out the need for such a table and all the extra coding work which goes with it. I hope that explains why I'd like to use foreign keys. What do you think? Regards Luke - Original Message - From: Richard Clarke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 2:11 PM Subject: Re: InnoDB tables Luke, How exactly will the foreign keys save you time? Please explain. Ric. - Original Message - From: Luke van Blerk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 9:17 AM Subject: InnoDB tables Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Thanks in advance Luke van Blerk mysql - 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: InnoDB
Heiki, I have a question concerning the new auto-extending function (which sounds really cool by the way!). How does it handle the case where it runs out of disk space while expanding? Will it gracefully handle this scenerio or will some sort of recovery be necessary? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 The auto-extending data file of InnoDB-3.23.50 will solve your problem. No need to preallocate tablespace any more. Best regards, Heikki Tuuri Innobase Oy sql sql mysql query query 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: InnoDB
Carl, when it runs out of disk space the auto-extend feature prints to the MySQL error log a complaint that disk space has run out, and stops extending the file. If you do not give it more free disk space, InnoDB behaves as if it would have a fixed size data file. That is, you will get the 'Table is full' errors. But when you give it more disk space, auto-extension continues. I have not tested what happens on those file systems where max file size is 2 GB. If you run on such a file system, best to specify ibdata1:10M:autoextend:max:2000M 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: Carl McNamee [EMAIL PROTECTED] To: 'Heikki Tuuri' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 4:53 PM Subject: RE: InnoDB Heiki, I have a question concerning the new auto-extending function (which sounds really cool by the way!). How does it handle the case where it runs out of disk space while expanding? Will it gracefully handle this scenerio or will some sort of recovery be necessary? Carl McNamee Systems Administrator Billing Concepts (210) 949-7282 The auto-extending data file of InnoDB-3.23.50 will solve your problem. No need to preallocate tablespace any more. 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 tables
FOREIGN KEYs by themselves make it faster and easier to validate that your code is correctly maintaining referrential integrity. They also let O/R mappers do more of the work in generating code for accessing your data (Blatant Plug: http://freshmeat.net/projects/easyorm/ -- EasyORM is an O/R mapper that introspects a MySQL DB and generates PHP code (eventually it'll also do Java (JDBC and EJB) and Perl.), and uses hints from an XML document you provide to build object-oriented code to access your schema. It's really crude right now -- the version in development is vastly more robust -- but we're using it successfully in our production environment.) FOREIGN KEYs combined with CASCADE ON DELETE/UPDATE (which InnoDB does not support yet) make it easier to write correct code: It manages changes/removals of dependant objects so you don't have to even think about it, let alone code it. -JF -Original Message- From: Richard Clarke [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 09, 2002 5:11 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB tables Luke, How exactly will the foreign keys save you time? Please explain. Ric. - Original Message - From: Luke van Blerk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 09, 2002 9:17 AM Subject: InnoDB tables Hi everyone, I've been reading up about InnoDB tables and they seem to have lots of advantages. I'm particularly interested in using the foreign keys as this will save me some much need time. In the benchmark tests on the InnoDB website they show up faster than MyISAM tables but will foreign keys have a slow down effect? And are there any issues I need to know about before switching to InnoDB tables? Thanks in advance Luke van Blerk mysql - 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: InnoDB is better than MyISAM ?
Patrick, - Original Message - From: Patrick Hsieh [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, April 06, 2002 10:43 AM Subject: Re: InnoDB is better than MyISAM ? Hello Heikki Tuuri [EMAIL PROTECTED], Where can I find InnoDB Hot Backup product? you can join the InnoDB Hot Backup beta testing program at http://www.innodb.com/hotbackup.html Since no bugs have been reported from ibbackup-0.34 by any of the beta testers, Innobase Oy may declare ibbackup stable already during this month. 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 On Sat, 6 Apr 2002 10:35:33 +0300 Heikki Tuuri [EMAIL PROTECTED] wrote: Hi! - Original Message - From: Eric S [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, April 06, 2002 2:10 AM Subject: Re: InnoDB is better than MyISAM ? On Fri, 5 Apr 2002, BD wrote: At 01:54 PM 4/5/2002, you wrote: I have seen many people saying that InnoDB is a great deal, that InnoDB rocks, etc. and I am concerced about how much better InnoDB is compared to MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB have foreign keys support, but I deal very well without then since now. My interests are justified becaus eI got out of a very old struct ( DBM + Text Files ) and jumped head first into MySQL - MyISAM tables, but my site has a good deal of visitors ( about 30.000 unique visitors by day ) and speed and reliability are my primary concerns. I plan to use replication in MySQL, and I would like to know if InnoDB is better than MyISAM for this. MySQL replication works with InnoDB type tables, and is currently used at several sites. InnoDB Hot Backup (non-free software) makes it possible to set up a new slave without stopping the master or setting any locks on the master. This is a useful feature at sites requiring high availability. One note here is that transactions aren't preserved for replication with InnoDB, so you loose part of one of the major advantages of InnoDB. Rollbacks are O.K., since I don't think that goes out to the slaves until the commit, but if the master or slave goes down after part of a transaction is sent to the slave, you get a partially committed transaction on the slave. How critical this is depends on the application, and still is no worse than MyISAM which has no transactions to begin with. I think Heikki Tuuri has mentioned plans to get this fixed, though I think I remember that he said that the problem was in MySQL, not in the actual InnoDB code, which makes sense. A potential embedded license buyer is interested in getting this fixed. If the deal is closed, we may add the commit marks to the binlog rather soon. Have you ever heard the old saying, If it ain't broke, don't fix it?.bg If your website is mainly for read access to your database then you're not going to need InnoDb. Agreed 100%, but it doesn't hurt too much (except for index sizes) on readonly databases, so on our production system, we standardized on InnoDB for all tables for consistency, though we will allow for exceptions for tables that need features that aren't in InnoDB yet, such as full text searching. InnoDb inserts (for a single user) are much slower (for me it is around 10x slower) than MyISAM because InnoDb does a lot more work. This was not my experience. Without batching the commits, InnoDB lost out to MyISAM on our initial testing by about 1.5x rather than 10x. However, when I committed every 100 or so inserts, InnoDB beat out MyISAM by a small (25%) margin. This was with a single user hitting the database, a perl program that read in a text file, split it into fields, and stuffed it into the database one record at a time (identical programs except for handling the commits()). Now, this wasn't normal database activity, pure inserts into a freshly created table, but the results were still quite impressive. Also, I'm dealing with single-user activity, so there may have been some differences there as well. 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 -- Patrick Hsieh
RE: InnoDB and lock wait timeout
Hi, Generally, an apache setup will kill a PHP enabled page when it has run for 30 seconds. Also, when a php script exits, all connections to the database are closed, so somwhere you need to keep the results of your script somwhere (hidden HTML variables, URI string), otherwise, when you change pages (and scripts of course), you lose your connection... and the transaction will probably abort... IMHO, you can only use a transaction within 1 PHP program (therefore within 1 page only), and remember your 30 second execution limit). If you are implementing a PHP / HTML application, I would guide the user over PHP enabled HTML form pages, and pass the result via a post or a URI string to the next page, etc. When you have all the info that you need, you send all the information from your previous pages to 1 parser script, which inserts all info into the database, with or without a transaction. This is the price you have to pay for a stateless connection - Your script is only active when your page is active. Hope that helps ! Cheers, Daniel Page -Original Message- From: Victor [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 06, 2002 10:58 PM To: MySQL Subject: InnoDB and lock wait timeout Hi. I have a question about rollbacks and innodb. Suppose that a query begins with begin. Then a couple inserts happen but before the commit statement is executed, the client hits stop in the browser. What is going to do the rollback? If there is a persistent connection, is it going to wait for 8 hours or so and what would happen then? Is there a significant increase in speed using persistent connections in MySQL? I am using PHP and I am cuious how one would do a persistent connection example. Does php would keep a class loaded in mem like java would? It doesn't sound plausible since php is loaded with an apache thread, which gets recycled every now and then, leaving the connection open on the mysql side but never used by apache agian. What are some approaches to solving these issues? 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: InnoDB and lock wait timeout
Hi, On Sat, Apr 06, 2002 at 03:57:39PM -0500, Victor wrote: Suppose that a query begins with begin. Then a couple inserts happen but before the commit statement is executed, the client hits stop in the browser. What is going to do the rollback? If there is a persistent connection, is it going to wait for 8 hours or so and what would happen then? When the user hits the STOP button, your script (most likely) will have no idea that the browser closed the connection until it tries to send it some output, at which point it would probably receive SIGPIPE or some kind of indicator that the client is gone. By default, applications that receive SIGPIPE will terminate (Broken pipe). The connection to the mysql server would be broken, and the mysql server would issue a rollback. If your web server does any buffering, you may not know until the entire page is sent that the client disappeared. Your environment may provide different default behavior, but you should be able to modify these things to taste if you're willing to get low-level enough. PHP (since you mention it in the next paragraph) will by default kill running scripts if a timeout value is reached. This timeout can be modified accordingly. Is there a significant increase in speed using persistent connections in MySQL? I am using PHP and I am cuious how one would do a persistent connection example. Does php would keep a class loaded in mem like java would? It doesn't sound plausible since php is loaded with an apache thread, which gets recycled every now and then, leaving the connection open on the mysql side but never used by apache agian. We don't do persistent connections at all with our sites and we don't notice a speed problem (mod_perl). PHP provides a mysql_pconnect() function which is supposed to do persistant connections according to the documentation. If you don't maintain persistent connections, you may want to take measures to ensure that each mysql connection attempt doesn't involve a DNS lookup (either put the hostname in /etc/hosts or use an IP address instead of a name) if your mysql server is not localhost.. -- Michael Bacarella | Netgraft Corporation | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | [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
Hi! - Original Message - From: Rodrigo Gonzalez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, April 05, 2002 8:59 PM Subject: InnoDB First of all excuse my english...I hope someone can understand me I am thinking in converting from MyIsam to InnoDb tables. I have this table: du -sm table* 323 table.MYD 276 table.MYI 0 table.frm How much tablespace I need to convert it to InnoDb, I found that I need a lot more space for this table type, but I could not find how to calculate this space... The auto-extending data file of InnoDB-3.23.50 will solve your problem. No need to preallocate tablespace any more. I think the following formula usually gives a rough estimate of the needed size: 1.5 x size of the .MYD file + 5 x size of .MYI file. Regards, Rodrigo Gonzalez. sql,query 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
How can I download this version? Regards - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 05, 2002 4:07 PM Subject: Re: InnoDB Hi! - Original Message - From: Rodrigo Gonzalez [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, April 05, 2002 8:59 PM Subject: InnoDB First of all excuse my english...I hope someone can understand me I am thinking in converting from MyIsam to InnoDb tables. I have this table: du -sm table* 323 table.MYD 276 table.MYI 0 table.frm How much tablespace I need to convert it to InnoDb, I found that I need a lot more space for this table type, but I could not find how to calculate this space... The auto-extending data file of InnoDB-3.23.50 will solve your problem. No need to preallocate tablespace any more. I think the following formula usually gives a rough estimate of the needed size: 1.5 x size of the .MYD file + 5 x size of .MYI file. Regards, Rodrigo Gonzalez. sql,query 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 - 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 is better than MyISAM ?
At 01:54 PM 4/5/2002, you wrote: I have seen many people saying that InnoDB is a great deal, that InnoDB rocks, etc. and I am concerced about how much better InnoDB is compared to MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB have foreign keys support, but I deal very well without then since now. My interests are justified becaus eI got out of a very old struct ( DBM + Text Files ) and jumped head first into MySQL - MyISAM tables, but my site has a good deal of visitors ( about 30.000 unique visitors by day ) and speed and reliability are my primary concerns. I plan to use replication in MySQL, and I would like to know if InnoDB is better than MyISAM for this. Have you ever heard the old saying, If it ain't broke, don't fix it?.bg If your website is mainly for read access to your database then you're not going to need InnoDb. InnoDb is great for multi-user updates because of its row locking. If you have more than say 30-50 users at a time updating records in the *same* table, then yes row locking will help. You will need to analyze the log file to determine how many processes are waiting for table locks on your MyISAM tables to determine if the switch to InnoDb is warranted. InnoDb inserts (for a single user) are much slower (for me it is around 10x slower) than MyISAM because InnoDb does a lot more work. This may dismay a few users initially, but then they realize InnoDb will maintain its speed much better than MySQL when there are a lot of heavy updates. So if your database is mostly readonly, then stick to MyISAM tables. I don't know if InnoDb supports replication or not. You'll have to check with Heikki. Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.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: InnoDB is better than MyISAM ?
On Fri, 5 Apr 2002, BD wrote: At 01:54 PM 4/5/2002, you wrote: I have seen many people saying that InnoDB is a great deal, that InnoDB rocks, etc. and I am concerced about how much better InnoDB is compared to MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB have foreign keys support, but I deal very well without then since now. My interests are justified becaus eI got out of a very old struct ( DBM + Text Files ) and jumped head first into MySQL - MyISAM tables, but my site has a good deal of visitors ( about 30.000 unique visitors by day ) and speed and reliability are my primary concerns. I plan to use replication in MySQL, and I would like to know if InnoDB is better than MyISAM for this. One note here is that transactions aren't preserved for replication with InnoDB, so you loose part of one of the major advantages of InnoDB. Rollbacks are O.K., since I don't think that goes out to the slaves until the commit, but if the master or slave goes down after part of a transaction is sent to the slave, you get a partially committed transaction on the slave. How critical this is depends on the application, and still is no worse than MyISAM which has no transactions to begin with. I think Heikki Tuuri has mentioned plans to get this fixed, though I think I remember that he said that the problem was in MySQL, not in the actual InnoDB code, which makes sense. Have you ever heard the old saying, If it ain't broke, don't fix it?.bg If your website is mainly for read access to your database then you're not going to need InnoDb. Agreed 100%, but it doesn't hurt too much (except for index sizes) on readonly databases, so on our production system, we standardized on InnoDB for all tables for consistency, though we will allow for exceptions for tables that need features that aren't in InnoDB yet, such as full text searching. InnoDb inserts (for a single user) are much slower (for me it is around 10x slower) than MyISAM because InnoDb does a lot more work. This was not my experience. Without batching the commits, InnoDB lost out to MyISAM on our initial testing by about 1.5x rather than 10x. However, when I committed every 100 or so inserts, InnoDB beat out MyISAM by a small (25%) margin. This was with a single user hitting the database, a perl program that read in a text file, split it into fields, and stuffed it into the database one record at a time (identical programs except for handling the commits()). Now, this wasn't normal database activity, pure inserts into a freshly created table, but the results were still quite impressive. Also, I'm dealing with single-user activity, so there may have been some differences there as well. - 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 is better than MyISAM ?
Hi! - Original Message - From: Eric S [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, April 06, 2002 2:10 AM Subject: Re: InnoDB is better than MyISAM ? On Fri, 5 Apr 2002, BD wrote: At 01:54 PM 4/5/2002, you wrote: I have seen many people saying that InnoDB is a great deal, that InnoDB rocks, etc. and I am concerced about how much better InnoDB is compared to MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB have foreign keys support, but I deal very well without then since now. My interests are justified becaus eI got out of a very old struct ( DBM + Text Files ) and jumped head first into MySQL - MyISAM tables, but my site has a good deal of visitors ( about 30.000 unique visitors by day ) and speed and reliability are my primary concerns. I plan to use replication in MySQL, and I would like to know if InnoDB is better than MyISAM for this. MySQL replication works with InnoDB type tables, and is currently used at several sites. InnoDB Hot Backup (non-free software) makes it possible to set up a new slave without stopping the master or setting any locks on the master. This is a useful feature at sites requiring high availability. One note here is that transactions aren't preserved for replication with InnoDB, so you loose part of one of the major advantages of InnoDB. Rollbacks are O.K., since I don't think that goes out to the slaves until the commit, but if the master or slave goes down after part of a transaction is sent to the slave, you get a partially committed transaction on the slave. How critical this is depends on the application, and still is no worse than MyISAM which has no transactions to begin with. I think Heikki Tuuri has mentioned plans to get this fixed, though I think I remember that he said that the problem was in MySQL, not in the actual InnoDB code, which makes sense. A potential embedded license buyer is interested in getting this fixed. If the deal is closed, we may add the commit marks to the binlog rather soon. Have you ever heard the old saying, If it ain't broke, don't fix it?.bg If your website is mainly for read access to your database then you're not going to need InnoDb. Agreed 100%, but it doesn't hurt too much (except for index sizes) on readonly databases, so on our production system, we standardized on InnoDB for all tables for consistency, though we will allow for exceptions for tables that need features that aren't in InnoDB yet, such as full text searching. InnoDb inserts (for a single user) are much slower (for me it is around 10x slower) than MyISAM because InnoDb does a lot more work. This was not my experience. Without batching the commits, InnoDB lost out to MyISAM on our initial testing by about 1.5x rather than 10x. However, when I committed every 100 or so inserts, InnoDB beat out MyISAM by a small (25%) margin. This was with a single user hitting the database, a perl program that read in a text file, split it into fields, and stuffed it into the database one record at a time (identical programs except for handling the commits()). Now, this wasn't normal database activity, pure inserts into a freshly created table, but the results were still quite impressive. Also, I'm dealing with single-user activity, so there may have been some differences there as well. 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: InnoDB is better than MyISAM ?
Hello Heikki Tuuri [EMAIL PROTECTED], Where can I find InnoDB Hot Backup product? On Sat, 6 Apr 2002 10:35:33 +0300 Heikki Tuuri [EMAIL PROTECTED] wrote: Hi! - Original Message - From: Eric S [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, April 06, 2002 2:10 AM Subject: Re: InnoDB is better than MyISAM ? On Fri, 5 Apr 2002, BD wrote: At 01:54 PM 4/5/2002, you wrote: I have seen many people saying that InnoDB is a great deal, that InnoDB rocks, etc. and I am concerced about how much better InnoDB is compared to MyISAM tables. Can someone tells me wich one is better ? I know that InnoDB have foreign keys support, but I deal very well without then since now. My interests are justified becaus eI got out of a very old struct ( DBM + Text Files ) and jumped head first into MySQL - MyISAM tables, but my site has a good deal of visitors ( about 30.000 unique visitors by day ) and speed and reliability are my primary concerns. I plan to use replication in MySQL, and I would like to know if InnoDB is better than MyISAM for this. MySQL replication works with InnoDB type tables, and is currently used at several sites. InnoDB Hot Backup (non-free software) makes it possible to set up a new slave without stopping the master or setting any locks on the master. This is a useful feature at sites requiring high availability. One note here is that transactions aren't preserved for replication with InnoDB, so you loose part of one of the major advantages of InnoDB. Rollbacks are O.K., since I don't think that goes out to the slaves until the commit, but if the master or slave goes down after part of a transaction is sent to the slave, you get a partially committed transaction on the slave. How critical this is depends on the application, and still is no worse than MyISAM which has no transactions to begin with. I think Heikki Tuuri has mentioned plans to get this fixed, though I think I remember that he said that the problem was in MySQL, not in the actual InnoDB code, which makes sense. A potential embedded license buyer is interested in getting this fixed. If the deal is closed, we may add the commit marks to the binlog rather soon. Have you ever heard the old saying, If it ain't broke, don't fix it?.bg If your website is mainly for read access to your database then you're not going to need InnoDb. Agreed 100%, but it doesn't hurt too much (except for index sizes) on readonly databases, so on our production system, we standardized on InnoDB for all tables for consistency, though we will allow for exceptions for tables that need features that aren't in InnoDB yet, such as full text searching. InnoDb inserts (for a single user) are much slower (for me it is around 10x slower) than MyISAM because InnoDb does a lot more work. This was not my experience. Without batching the commits, InnoDB lost out to MyISAM on our initial testing by about 1.5x rather than 10x. However, when I committed every 100 or so inserts, InnoDB beat out MyISAM by a small (25%) margin. This was with a single user hitting the database, a perl program that read in a text file, split it into fields, and stuffed it into the database one record at a time (identical programs except for handling the commits()). Now, this wasn't normal database activity, pure inserts into a freshly created table, but the results were still quite impressive. Also, I'm dealing with single-user activity, so there may have been some differences there as well. 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 -- Patrick Hsieh [EMAIL PROTECTED] GPG public key http://pahud.net/pubkeys/pahudatpahud.gpg - 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 3.23.50 Release
Hi! We apologize for the delay in releasing 3.23.50. InnoDB-3.23.50 has been ready for 2 weeks now. The problem is that Monty wants to compile on Linux the MySQL binaries with gcc-3.0.4 and a rather new glibc version, and that combination does not seem to produce stable binaries of MySQL. I asked Monty today to downgrade to earlier versions, but he did not want to give up yet. We will consider making a source release of MySQL-Max-3.23.50 if the problems with the compiler continue. Best regards, Heikki Tuuri Innobase Oy - Original Message - From: Chandran Richard Honour [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, April 04, 2002 6:09 PM Subject: InnoDB 3.23.50 Release All Could someone please update me as to when the 3.23.50 version of MySQL/InnoDB will be released ? We are experiencing issues with the loss of foreign key constraints when using the ALTER TABLE command. regards, Chandran Honour. Technical Director, BrowserAngel. [EMAIL PROTECTED] +44 (0)20 70251518 (off) +44 (0)7866 263138 (mob) - 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 and Table Corruption
Graig, I've been using mysql for about a year now on a linux machine and it has not crashed or had any corrupted tables in that time. I believe mysql is just as good in stability and safety of data has it is in speed. Mike - Original Message - From: Scalper [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 01, 2002 8:01 PM Subject: InnoDB and Table Corruption I am working with a low-load, mission-critical database. Although I have yet to have a corrupted table, I seem to read about it a lot on the list. This is a scary thought for me, considering the importance of the data I manage. Can anyone tell me if this is a rare occurrence? Is it even rarer if I choose to use InnoDB tables? Are there any statistics on corruption? There seems to be plenty of material on MySQL and InnoDB and speed vs the other major dbs, but what about stability and safety of data? Craig query,sql,mysql - 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.330 / Virus Database: 184 - Release Date: 2/28/02 - 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
-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
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
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: 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: 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: innodb or Berkeley DB default for mysql max
We have the same problem as Victoria, and the --default-table-type option does not work. - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 15, 2002 1:38 PM Subject: innodb or Berkeley DB default for mysql max Kemp, Thursday, March 14, 2002, 8:37:00 PM, you wrote: KRW What is the default table engine used in the binary version of mysql-max -- KRW innodb or Berkeley? If not innodb, how do I get the innodb to be the default? KRW Can someone point me to the manual section? The default table type of MySQL server is MyISAM. To set up another table type by default use '--default-table-type' option. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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 - 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, you can see the monitor's output in the logfile. In my case (mandrake linux), it is in /var/lib/mysql and I do: tail -f /var/lib/mysql/SERVERNAME.err Also, where do I designate autocommit = 0; Use SET AUTOCOMMIT = 0 as a SQL query. Just issue it where you do your SELECT, UPDATE etc. queries, and don't forget to SET AUTOCOMMIT = 1 afterwards. - 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
Hi, does any1 know what is the earliest version of Mysql for Solaris, with support for InnoDB? If I am not mistaken, the version that we have is 3.23.32. Can MySql Max be added or some patch be added? This is not on a personal machine. It's on a server in my department (computer's science) and this is part of my project. thanks, Sanjay __ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.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: InnoDB and hosts.allow/deny
On Wed, Mar 06, 2002 at 06:19:53AM +0100, Eberhard Lisse wrote: Hi, I run SuSe 7.2 and have upgraded to the current suse versions of mysql. Server version 3.23.41-Max-log Protocol version10 Connection linux.lisse.na via TCP/IP TCP port3306 I have added mysqld and mysqld-max to the /etc/hosts.allow and can access the (MyISAM) tables from outside. However, when I configure an InnoDB table it behaves as if the TCP wrapper triggers (same messages to the syslog/syswarn files, not to the mysql logs as when I left it out of the hosts.allow). Hm. That doesn't make a lot of sense. MySQL handles the connections before it knows if you're using InnoDB or not. 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.47-max: up 28 days, processed 992,589,408 queries (406/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: 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: 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 issues - tables not found
At 04:18 2002-03-05, you wrote:
Tomasz,
are you running on Windows?
No, Linux 2.2.17
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
--
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: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote: 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, Re: 9.1: Well, can't find innodb* anywhere on the system Re: 15.1: The second problem seems to be mine, the one about orphaned' .frm files. That would seem to account for me seeing a tables list, but nothing about them. Obviously, the .frm files did exist. Couldn't drop the tables or the database (Unknown table ...,...,...). Let me repeat: everything was just fine until I quit mysql. When I logged back in - with the same command, I use ksh and 'set -o vi' - that's when the issues started. I got out of this trouble by shutting down the server, manually removing all InnoDB files (including .frm), then starting it again - which created the IDB files. I then used my script to create database and all the tables. And everything was fine until I logged out. I now logged back in and everything is still OK: I can desc tables, and so on. Just one issue: when I do SHOW CREATE ..., it still doesn't show any REFERENCES ... that I had when I created the tables. Either I'm exceptionally thick, or the docs really say that InnoDB has the capability to use the foreign keys... I took special care to reference only tables that already existed, in case it's a concern. Any idea? Also about how long will the tables stay healthy? I don't care about those 5 records I put in each, but to really test, I need to put a bit more... snip! THX! -- 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: InnoDB frightens me...
Hi! Tobias Hi! Tobias I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias selects, and is starting to suffer from the table-locking policy... Tobias And like Steve, I'm also scared! :) cut Tobias Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias that describes InnoDB from a MyISAM-point-of-view :) Tobias Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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: InnoDB frightens me...
Thanks - that would be great! I'm sure a lot of people will find this very helpful. Regards, Tobias - Original Message - From: Michael Widenius [EMAIL PROTECTED] To: Tobias Lind - Telia Internet [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 12:36 PM Subject: Re: InnoDB frightens me... Hi! Tobias Hi! Tobias I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias selects, and is starting to suffer from the table-locking policy... Tobias And like Steve, I'm also scared! :) cut Tobias Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias that describes InnoDB from a MyISAM-point-of-view :) Tobias Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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: InnoDB frightens me...
You can use logical lock - set a STATUS int 0 (probably the ID of user) to indicate someone started to edit it interactively to block other user to edit the same record. Before you write it back to disk, read it again in a buffer to keep all data possible changed by batch updates. Or separate the table to 2 tables, one for each kind of update. Also I use a kind of transactions before I know that exists, I don't subtract from items directrly but I put it in a field named keeps. If user change his mind or something goes wrong, I only subtruct from keeps fields.Otherwise I subtract from items and keeps. Of course I have create another temp file with the invoice. I use these methods several years now mainly with C-tree from Faircom, they work fine. But to tell you the true, I use Delphi with Interbase for this kind of application on the net. I don't know if it's possible with PHP. Can someone directs me to the avantages of Inno base comparing wtih default MySQL? Row locking is the bigest one as I can read here? Thanks Makis -Original Message- From: Tobias Lind - Telia Internet [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 2:46 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: InnoDB frightens me... Thanks - that would be great! I'm sure a lot of people will find this very helpful. Regards, Tobias - Original Message - From: Michael Widenius [EMAIL PROTECTED] To: Tobias Lind - Telia Internet [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 12:36 PM Subject: Re: InnoDB frightens me... Hi! Tobias Hi! Tobias I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias selects, and is starting to suffer from the table-locking policy... Tobias And like Steve, I'm also scared! :) cut Tobias Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias that describes InnoDB from a MyISAM-point-of-view :) Tobias Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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 - 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...
I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. Steve Rapaport. ] -Original Message- ] From: Tobias Lind - Telia Internet [mailto:[EMAIL PROTECTED]] ] Sent: Tuesday, March 05, 2002 1:46 PM ] To: [EMAIL PROTECTED] ] Cc: [EMAIL PROTECTED] ] Subject: Re: InnoDB frightens me... ] ] ] Thanks - that would be great! ] I'm sure a lot of people will find this very helpful. ] ] Regards, ] Tobias ] ] ] - Original Message - ] From: Michael Widenius [EMAIL PROTECTED] ] To: Tobias Lind - Telia Internet [EMAIL PROTECTED] ] Cc: [EMAIL PROTECTED] ] Sent: Tuesday, March 05, 2002 12:36 PM ] Subject: Re: InnoDB frightens me... ] ] ] ] Hi! ] ] Tobias Hi! ] Tobias I'm in the same position as Steve: Considering ] switching from MyISAM to InnoDB because of ] the ] Tobias row-level locking capabilities. My application has ] quite a lot of updates/inserts mixed ] with ] Tobias selects, and is starting to suffer from the ] table-locking policy... ] Tobias And like Steve, I'm also scared! :) ] ] cut ] ] Tobias Maybe some of this confusion and fear could be avoided ] with a section in the InnoDB ] documentation ] Tobias that describes InnoDB from a MyISAM-point-of-view :) ] ] Tobias Explaining how all common things, principles and ] routines with MyISAM works with InnoDB. ] I'm sure a ] Tobias lot of people have very good knowledge of MyISAM and ] comes from the same direction as I ] do... ] ] I have forwarded this to our docmentation team and we shall try to do ] this in the near future. ] ] Regards, ] Monty ] ] -- ] For technical support contracts, goto https://order.mysql.com/ ] __ ___ ___ __ ]/ |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] ] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO ] /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ] ___/ 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 ] - 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...
On Tue, Mar 05, 2002 at 09:58:13PM +0100, Steve Rapaport wrote: I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. If you're not using transactions, the code is identical. Really. It's easy. :-) 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.47-max: up 26 days, processed 896,354,110 queries (394/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: InnoDB frightens me...
Sure seems that way to me. Same SQL engine no matter what table type you use. InnoDB didn't scare me. Pretty friendly to use. :) --Walt -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 2:09 PM To: [EMAIL PROTECTED] Cc: Tobias Lind - Telia Internet; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: InnoDB frightens me... On Tue, Mar 05, 2002 at 09:58:13PM +0100, Steve Rapaport wrote: I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. If you're not using transactions, the code is identical. Really. It's easy. :-) 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.47-max: up 26 days, processed 896,354,110 queries (394/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 - 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...
Hi! I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the row-level locking capabilities. My application has quite a lot of updates/inserts mixed with selects, and is starting to suffer from the table-locking policy... And like Steve, I'm also scared! :) I've been working quite a lot with MyISAM-tables and I have a good feeling for how it works (backups, data-files, index-files, size of these files, etc) But with InnoDB, I'm - hmmm - confused - that's probably the best word to describe it! The auto-extendible-feature on the todo-list is a great thing! That's one of the big concerns I've had with InnoDB. Another concern is the actual file size - I understand that InnoDB-tables will use a lot more space than the same table in MyISAM... Question is HOW much more (the largest table I currently have is ~2 Gb datafile, 360 Mb indexfile) I'm also confused with how it all works with tablespaces, log-files etc. Is all this common knowledge with transactional databases? If I'm not interested in transactions, do I have to care about all that? And all these limits and parameters! To quote from the InnoDB-documentation: Note that data files must be 2G in some file systems! The combined size of data files must be = 10 MB. The combined size of the log files must be 4G. Hmmm - does any of this put any limitation for my application? (it's a retorical question - I know it depends on my app, but you get the point: It's a lot of new things to learn and consider before switching) And what about backups? Now I'm using mysqldump every night to backup my database - will that be ok for InnoDB too? (I know that it _works_ but what about performance - will it take longer to make a dump with InnoDB-tables? I have to take the website offline while dumping so it's important) I have been looking forward to online-backups, and it is on the todo-list for MySQL 4.1 - will that feature work with InnoDB-tables as well? I noticed that hotbackups is also on the todo-list for InnoDB, but that it will not be free... So... As you can see I'm a bit confused, and I think a lot of people are! It seems to be a whole lot more to learn, more administration, tweaking, etc with InnoDB than with MyISAM. Maybe I'm wrong, but I get that feeling from reading the InnoDB-docs... Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation that describes InnoDB from a MyISAM-point-of-view :) Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a lot of people have very good knowledge of MyISAM and comes from the same direction as I do... Thanks for your time! :) /Tobias 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
Re: InnoDB frightens me...
Hi Heikki, I don't know if this has been requested, but what about a tool to 'pre-create' dataspace? This tool would allow someone to create a new dataspace, then a quick restart (After adding the name of the space to 'my.cnf') and the new dataspace is available! Maybe just extract the pieces from MySQL code and make it a separate tool? I know I don't want to have a server shutdown while it creates 10GB of Dataspace! Of course autoextending is one answer. One could just create a small dataspace and let it autoextend. Thanks, Ken - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, March 03, 2002 6:46 AM Subject: 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 - 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...
On Mon, Mar 04, 2002 at 09:30:16AM -0500, Ken Menzel wrote: Hi Heikki, I don't know if this has been requested, but what about a tool to 'pre-create' dataspace? This tool would allow someone to create a new dataspace, then a quick restart (After adding the name of the space to 'my.cnf') and the new dataspace is available! Maybe just extract the pieces from MySQL code and make it a separate tool? I know I don't want to have a server shutdown while it creates 10GB of Dataspace! Of course autoextending is one answer. One could just create a small dataspace and let it autoextend. I'll second the request. I'm planning to test InnoDB with large tablespaces soon (on the order of .5TB) and would love to be able to create the data files off-line and add them as needed. 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.47-max: up 25 days, processed 860,216,505 queries (388/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: InnoDB frightens me...
On Monday 04 March 2002 09:32 pm, you wrote: On Mon, Mar 04, 2002 at 09:30:16AM -0500, Ken Menzel wrote: Hi Heikki, I don't know if this has been requested, but what about a tool to 'pre-create' dataspace? This tool would allow someone to create a new dataspace, then a quick restart (After adding the name of the space to 'my.cnf') and the new dataspace is available! Maybe just extract the pieces from MySQL code and make it a separate tool? I know I don't want to have a server shutdown while it creates 10GB of Dataspace! Of course autoextending is one answer. One could just create a small dataspace and let it autoextend. I'll second the request. I'm planning to test InnoDB with large tablespaces soon (on the order of .5TB) and would love to be able to create the data files off-line and add them as needed. Jeremy Ditto, I know this is one thing keeping us from going to Innodb full scale. Jayce^ - 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: InnoDB frightens me...
On Sat, Mar 02, 2002 at 11:14:42PM +0100, Steve Rapaport wrote: 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. Hm? 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. Only if you want transactions. 2. Decide with zero experience on a lot of maximum sizes which will not be adjustable in future, including dataspace. You can always add more space later. 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? Try out BDB tables, which have page-level locking? 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.47-max: up 23 days, processed 763,640,350 queries (376/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: InnoDB frightens me...
On Sat, Mar 02, 2002 at 06:52:57PM -0800, Jeff Kilbride wrote: I'm looking to make the move to InnoDB, too. All I've heard is positve. Here's a reply I got on another list: If you have a very busy read/write op database, MyISAM can't handle it. It's very efficient for when you have many more reads than writes, but once you start hitting a balance between the two and then get some load, MyISAM just locks up. It's just known. Table-level locks lead to lengthy delays. And from what I've seen, things start to get problematic with MyISAM when the percentage of read-only queries drops below about 70% or so. 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.47-max: up 23 days, processed 765,311,906 queries (376/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: InnoDB frightens me...
Hi Jeremy, My follow-up for this answer from the other list was: Does replication hurt MyISAM performance in the same way? If I'm running a master that takes all the inserts and one or more slaves to take all the select queries, would it be better implemented in MyISAM or InnoDB? I don't need the transaction ability of InnoDB, but if concurrent replication affects MyISAM performance in the same way as concurrent insert/select ops, it makes me wonder which table type I should use on my high insert activity tables. My assumption was MyISAM, but I'm not so sure now... I may have posted the same question here, earlier. I'd appreciate any input. Thanks, --jeff - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Jeff Kilbride [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Saturday, March 02, 2002 7:11 PM Subject: Re: InnoDB frightens me... On Sat, Mar 02, 2002 at 06:52:57PM -0800, Jeff Kilbride wrote: I'm looking to make the move to InnoDB, too. All I've heard is positve. Here's a reply I got on another list: If you have a very busy read/write op database, MyISAM can't handle it. It's very efficient for when you have many more reads than writes, but once you start hitting a balance between the two and then get some load, MyISAM just locks up. It's just known. Table-level locks lead to lengthy delays. And from what I've seen, things start to get problematic with MyISAM when the percentage of read-only queries drops below about 70% or so. 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.47-max: up 23 days, processed 765,311,906 queries (376/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 - 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: Innodb tables lose foreign keys after creating an index...
--- Heikki Tuuri [EMAIL PROTECTED] wrote: 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. Heikki: Is there a fix planned for the alter table/foreign key issue ? Best regards, [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.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: InnoDB question
Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, 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 25, 2002 2:26 PM To: Demirchyan Oganes-AOD098 Subject: Re: InnoDB question 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: Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 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: InnoDB question
Oganes, It sounds like what you want is row-level-locking. This is a feature of InnoDB tables. It allows users to write to a table while other users are reading from the same table. Of course, they cannot read and write the same rows simultaniously. Have you tried to do this and had some problem? If so you need to be much more specific about the problem you're encountering. So, to answer your question: You can already do this. Eric Mayers Software Engineer I Captus Networks -Original Message- From: Demirchyan Oganes-AOD098 [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 2:34 PM To: '[EMAIL PROTECTED]' Subject: RE: InnoDB question Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, 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 25, 2002 2:26 PM To: Demirchyan Oganes-AOD098 Subject: Re: InnoDB question 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: Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 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 - 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 question
On Mon, Feb 25, 2002 at 02:44:45PM -0800, Eric Mayers wrote: Oganes, It sounds like what you want is row-level-locking. This is a feature of InnoDB tables. It allows users to write to a table while other users are reading from the same table. Of course, they cannot read and write the same rows simultaniously. That's not entirely true. Readers don't generally don't wait on writers (and vice-versa). 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.47-max: up 18 days, processed 573,142,672 queries (360/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: INNODB conversion
Hi Nilesh, On Thu, 2002-02-21 at 15:14, Nilesh Deshpande wrote: I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\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 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 Ok, by the looks of this you're running on Windows (I see c:\ etc), right? After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections Here's where things start getting curious I see Unix-style pathnames here, with a /home/heikki/ etc. I don't see that happening on a Windows system, so the question is: where did you start the server? Like by chance on a *nix system you were connected to from the Win box? See, the printed info does not even match up with the config info you posted above you configured 2x 2GB data files, but the first one is initialised to something like 134 MB and the second one to 262 MB. I think you're looking at a completely different server there! After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? This is typical of a server that does not have InnoDB support built-in or enabled. I think you have to sort out what you're doing where, make sure you're configuring, starting, and connecting to the same server, and I'm confident that once that is the case, the above problem will disappear. Do let us know on the list how you fare with this Regards, Arjen. -- Get MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ___/ 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: INNODB conversion
At 21:14 20/02/2002 -0800, Nilesh Deshpande wrote: Hi! Very strange the printed messages you sent. What MySQL release version are you using ? I did the test with your start InnoDB set with 3.23.48 and got: c:\mysql\binmysqld-max --standalone --console InnoDB: The first specified data file c:\ibdata\ibdata1 did not exist: InnoDB: a new database to be created! 020221 2:42:46 InnoDB: Setting file c:\ibdata\ibdata1 size to 200 MB InnoDB: Database physically writes the file full: wait... 020221 2:42:59 InnoDB: Data file c:\ibdata\ibdata2 did not exist: new to be cr eated 020221 2:42:59 InnoDB: Setting file c:\ibdata\ibdata2 size to 200 MB InnoDB: Database physically writes the file full: wait... 020221 2:43:13 InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 30 MB InnoDB: Database physically writes the file full: wait... 020221 2:43:15 InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 30 MB InnoDB: Database physically writes the file full: wait... 020221 2:43:18 InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 30 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 020221 2:43:30 InnoDB: Started mysqld-max: ready for connections Notice the paths of the files. You showed: /home/heikki/data/ibdata1 did not exist: The above is should be the Heikki's Unix machine !. Please send for us the release version and the complete configuration data file (my.ini/my.cnf). Regards, Miguel Dear sir, I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\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 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? so any one can please tell me, if i missed any steps __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.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 -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/
Re: INNODB conversion
At 21:14 20/02/2002 -0800, Nilesh Deshpande wrote: Hi, Now I assume that the text you sent is from our documentation. For to build an InnoDB table: mysql create table table_name (id int)type=innodb; Query OK, 0 rows affected (0.14 sec) Regards, Miguel Dear sir, I am using MySQL database I just wanted to use transaction.so as per manual i have to make table type as Innodb. Then i had set Innodb startup options as follows innodb_data_file_path = ibdata1:2000M;ibdata2:2000M innodb_data_home_dir = c:\ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\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 innodb_log_arch_dir = c:\iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 After this i gave command as mysqld-max --standalone --console Then it printed somthing like this InnoDB: The first specified data file /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections After that i tried to create one table by specifying type as Innodb as follows. CREATE TABLE test1 (Id INT, Name CHAR (20)) TYPE = InnoDB; But it is still creating tables in MyISAM? and even i cant't change table type to INNODB using alter query?? so any one can please tell me, if i missed any steps __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.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 -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ 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
