Re: problem with INNODB tables
Am 15.03.2012 17:31, schrieb Malka Cymbalista: We are running MySQL version 5.0.45 on a Linux machine. Most of our tables are MyIASM but we have recently installed drupal 7 and drupal 7 requires INNODB tables. Every now and then when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. After much trial and error we came up with the following solution: We stop mysql We remove the files ib_logfile0 and ib_logfile1 We rename ibdata1 to ibdata1.old (mv ibdata1 ibdata1.old) We copy ibdata1.old back to ibdata1 (cp -a ibdata1.old ibdata1) We start mysql And everything is fine and the sites that use INNODB tables are fine. We clearly have a problem but we have no idea where to start looking. what about start with looking in the errorlog? signature.asc Description: OpenPGP digital signature
Re: problem with InnoDB
On 9/7/06, Paul McCullagh [EMAIL PROTECTED] wrote: It sounds like you program allows ad-hoc queries, so why don't you just limit the number of rows returned by a select? For example you could limit the number of rows to 1001. If the server returns 1001, then display 1000 and tell the user there are actually more rows. The user should then apply further conditions. Some things worth mentioning when using LIMIT: In MySQL the LIMIT clause is applied just before sending the result to the client... so a SELECT col1, col2, ... , colN FROM tableName LIMIT x, y will be performed as SELECT col1, col2, ... , colN FROM tableName and before sending the result to the client the LIMIT will be applied... There are some things to consider... if you have an ORDER BY clause MySQL will stop sorting after LIMIT clause is satisfied... To skit the COUNT(*) query you must use: SELECT SQL_CALC_FOUND_ROWS col1, col2, ... , colN FROM tableName LIMIT x, y This way MySQL will store internally the number of rows that would have been returned without the LIMIT clause [The drawback is that if you have an ORDER BY clause MySQL will not stop after sorting LIMIT x,y rows... as I mentioned above] But the gain is that the second query that will return the number of rows without the LIMIT clause: SELECT FOUND_ROWS() will return instantly. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
Hi Prasad
This question got me a bit interested as we're thinking of moving
some MyISAM tables to InnoDB and I haven't used it much.
I decided to test some of these ideas so I created an innodb table
and put some data into it and tried some selects:
(Running on MacBook Pro, 2.0ghz, 1gb RAM, OS X 10.4.7)
mysql select @@version;
+-+
| @@version |
+-+
| 5.0.19-standard-log |
+-+
1 row in set (0.00 sec)
mysql show create table t5;
+---
+---
-+
| Table | Create
Table
|
+---
+---
-+
| t5| CREATE TABLE `t5` (
`id` int(9) NOT NULL auto_increment,
`name` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---
+---
-+
1 row in set (0.00 sec)
Then I inserted a million rows of random strings:
#!/usr/bin/perl
use DBI;
$db = DBI-connect('DBI:mysql:test', 'test', 'test') || die Content-
type: text/html\n\nSorry could not connect to DBbr /;
foreach (1..100) {
$name='';
foreach (1..30) {$name.=chr(65+rand(25));}
$db-do('INSERT INTO t5 (name) VALUES ('.$db-quote($name).')');
print Now inserting $_\n;
}
and watched the server while it was inserting. I tried SELECT COUNT
(*) repeatedly as the table was filled:
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 16464|
+--+
1 row in set (0.02 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 27343|
+--+
1 row in set (0.02 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 63263|
+--+
1 row in set (0.03 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 118442 |
+--+
1 row in set (0.05 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 182230 |
+--+
1 row in set (0.42 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 272427 |
+--+
1 row in set (0.28 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 407541 |
+--+
1 row in set (0.60 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 506970 |
+--+
1 row in set (0.56 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 650197 |
+--+
1 row in set (0.58 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 756860 |
+--+
1 row in set (0.79 sec)
mysql show processlist;
++--+---+--+-+--+
+-+
| Id | User | Host | db | Command | Time | State |
Info|
++--+---+--+-+--+
+-+
| 37 | test | localhost | test | Query | 0|| show
processlist|
| 38 | test | localhost | test | Query | 0| update | INSERT
INTO t5 (name) VALUES ('BJWCKTSWFNIJCFKMUIKFPTUMEIAFDG') |
++--+---+--+-+--+
+-+
2 rows in set (0.01 sec)
mysql show processlist;
++--+---+--+-+--+
+-+
| Id | User | Host | db | Command | Time | State |
Info|
++--+---+--+-+--+
+-+
| 37 | test | localhost | test | Query | 0|| show
processlist|
| 38 | test | localhost | test | Query | 0| update | INSERT
INTO t5 (name) VALUES ('AICBWBYTSUXGKMKQCBDKAJSPIKETAV') |
++--+---+--+-+--+
+-+
2 rows in set (0.00 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 885597 |
+--+
1 row in set (1.02 sec)
mysql select count(*) from t5;
+--+
| count(*) |
+--+
| 100 |
+--+
1 row in set (0.85 sec)
mysql select max(id)
RE: problem with InnoDB
Hi Douglas, I need to know the number of rows that a query will return before actually executing the query. So I am sending select count(*) before sending select *. Actually I need to reject queries if the number of records that it will return is huge, to avoid my server running out of memory. My application has a huge database of around 10 millions. The selects with INNODB falls drastically as the size of records grow. A select count(*) that takes 4 secs with 1 million records takes 40 secs with 3 million records. Regards Prasad -Original Message- From: Douglas Sims [mailto:[EMAIL PROTECTED] Sent: Thursday, September 07, 2006 10:39 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: MySQL List Subject: Re: problem with InnoDB Hi Prasad A primary key automatically creates an index and not-null and unique constraints, too. So you don't need to explicitly create an index on a primary key column. If your queries are going to have WHERE clauses (as they most likely are) I'm not sure how the small-index suggestion would make the query any faster - the WHERE clause would preclude the use of that index in computing the rows - but I'm probably missing something here. When you say that you need to know the number of rows returned before executing the query, do you mean before you start getting rows back or before you actually execute the query? I don't think it's possible to know how many rows the query will return without actually executing it, but you might well want to know how many rows you get before you start processing rows. Have you looked at the SQL_CALC_FOUND_ROWS option on SELECT, and the accompanying FOUND_ROWS() function? http://dev.mysql.com/doc/refman/ 5.0/en/information-functions.html It will tell you the total number of rows which would have been found if you hadn't used a LIMIT clause. I think it is a connection- specific function; if you created a second statement handle and did a SELECT FOUND_ROWS() on the same connection, perhaps that would give what you want. Douglas Sims [EMAIL PROTECTED] On Sep 6, 2006, at 11:29 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi Douglas, I need to know the number of rows that a query will return before actually executing the query. So I am sending select count(*) before sending select *. Actually I need to reject queries if the number of records that it will return is huge, to avoid my server running out of memory. My application has a huge database of around 10 millions. The selects with INNODB falls drastically as the size of records grow. A select count(*) that takes 4 secs with 1 million records takes 40 secs with 3 million records. Regards Prasad -Original Message- From: Douglas Sims [mailto:[EMAIL PROTECTED] Sent: Thursday, September 07, 2006 10:39 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: MySQL List Subject: Re: problem with InnoDB Hi Prasad A primary key automatically creates an index and not-null and unique constraints, too. So you don't need to explicitly create an index on a primary key column. If your queries are going to have WHERE clauses (as they most likely are) I'm not sure how the small-index suggestion would make the query any faster - the WHERE clause would preclude the use of that index in computing the rows - but I'm probably missing something here. When you say that you need to know the number of rows returned before executing the query, do you mean before you start getting rows back or before you actually execute the query? I don't think it's possible to know how many rows the query will return without actually executing it, but you might well want to know how many rows you get before you start processing rows. Have you looked at the SQL_CALC_FOUND_ROWS option on SELECT, and the accompanying FOUND_ROWS() function? http://dev.mysql.com/doc/refman/ 5.0/en/information-functions.html It will tell you the total number of rows which would have been found if you hadn't used a LIMIT clause. I think it is a connection- specific function; if you created a second statement handle and did a SELECT FOUND_ROWS() on the same connection, perhaps that would give what you want. Douglas Sims [EMAIL PROTECTED] On Sep 6, 2006, at 11:29 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
On 9/7/06, [EMAIL PROTECTED] wrote: I need to know the number of rows that a query will return before actually executing the query. So I am sending select count(*) before sending select *. Actually I need to reject queries if the number of records that it will return is huge, to avoid my server running out of memory. My application has a huge database of around 10 millions. The selects with INNODB falls drastically as the size of records grow. A select count(*) that takes 4 secs with 1 million records takes 40 secs with 3 million records. Just read the fine manual, everything is explained there: http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html If you want more information on the fundamentals behind multiversioning I would recommend reading chapter 5 of Concurrency Control and Recovery in Database Systems http://research.microsoft.com/pubs/ccontrol/ If you want more information about the InnoDB specific implementation details there is always the source. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
On Sep 7, 2006, at 3:32 PM, [EMAIL PROTECTED] wrote: I need to know the number of rows that a query will return before actually executing the query. So I am sending select count(*) before sending select *. Actually I need to reject queries if the number of records that it will return is huge, to avoid my server running out of memory. My application has a huge database of around 10 millions. It sounds like you program allows ad-hoc queries, so why don't you just limit the number of rows returned by a select? For example you could limit the number of rows to 1001. If the server returns 1001, then display 1000 and tell the user there are actually more rows. The user should then apply further conditions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
[EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? A primary key already has an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
Hi Prasad A primary key automatically creates an index and not-null and unique constraints, too. So you don't need to explicitly create an index on a primary key column. If your queries are going to have WHERE clauses (as they most likely are) I'm not sure how the small-index suggestion would make the query any faster - the WHERE clause would preclude the use of that index in computing the rows - but I'm probably missing something here. When you say that you need to know the number of rows returned before executing the query, do you mean before you start getting rows back or before you actually execute the query? I don't think it's possible to know how many rows the query will return without actually executing it, but you might well want to know how many rows you get before you start processing rows. Have you looked at the SQL_CALC_FOUND_ROWS option on SELECT, and the accompanying FOUND_ROWS() function? http://dev.mysql.com/doc/refman/ 5.0/en/information-functions.html It will tell you the total number of rows which would have been found if you hadn't used a LIMIT clause. I think it is a connection- specific function; if you created a second statement handle and did a SELECT FOUND_ROWS() on the same connection, perhaps that would give what you want. Douglas Sims [EMAIL PROTECTED] On Sep 6, 2006, at 11:29 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? Regards Prasad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Monday, September 04, 2006 9:53 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: problem with InnoDB In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
In the last episode (Sep 07), [EMAIL PROTECTED] said: Hi Dan, Thanks for yur response. Does it makes sense to create an index on a primary key ..as that is my smallest field ? It might, because in an InnoDB table, your primary index also holds your row data. So it's actually your largest index. A full scan of a secondary index on your primary key may very well run faster than a scan of the primary index itself, for the purposes of SELECT COUNT(*). Best way to find out is to try it :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi Chris, Thanks for your response. Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Thanks Prasad -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 16, 2006 9:58 AM To: Prasad Ramisetti (WT01 - Broadband Networks) Cc: mysql@lists.mysql.com Subject: Re: problem with InnoDB [EMAIL PROTECTED] wrote: Hi , select count(*) is painfully slow in case of innoDB when the number of records are around 1 million. Ths select count(*) query in myISAM takes 0.01 secs and the same query in InnoDB takes around 20.15 secs. Can anybody suggest me how to speed up this query ? You can't. InnoDB is transactional which means it can't keep basic information like the number of rows it has up to date (which means any number of inserts/ updates/deletes can happen at one time). MyISAM is not transactional (so only one insert/update/delete can happen at one time) so it can keep this information. What context are you trying to do a count in? Maybe it would be better to have an extra field in another table and keep that counter up to date? I know this relates to postgresql but the same idea could be useful for you: http://www.designmagick.com/article/36/ The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
In the last episode (Sep 04), [EMAIL PROTECTED] said: Actually there is some requirement, where I need to know the number of rows that I will get for my queries before actually executing the query. Could you please suggest some way for this. Your best bet is to create an index on the smallest column you can find (maybe even create a char(1) and leave it empty), and SELECT COUNT(*) FROM innotable USE INDEX (smallcolumn). That way mysql only has to scan a small secondary index instead of the main table index. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with INNODB transactions
What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with INNODB transactions
Hi, We maintain a pool ..it is just a collection, where we maintain a list of connections. Regards prasad -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 11:41 PM To: Prasad Ramisetti (WT01 - Broadband Networks); mysql@lists.mysql.com Subject: RE: Problem with INNODB transactions What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. Could someone please let me know what could be the problem. There are some other processes running on the same box. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem with InnoDB
Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. Could someone please let me know what could be the problem. There are some other processes running on the same box. So it is making the other processes slower. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
[EMAIL PROTECTED] wrote: Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. As I said before, stuff like 'count(*)' queries cannot use an index in innodb - if you are doing a lot of those type of queries, it will cause a problem. Others include max(), min() (any aggregate function actually). http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html The easiest way to work out what queries are causing the problem is to enable the slow-log and go through that, 'explain'ing each query and adding indexes where necessary. http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with InnoDB
[EMAIL PROTECTED] wrote: Hi , select count(*) is painfully slow in case of innoDB when the number of records are around 1 million. Ths select count(*) query in myISAM takes 0.01 secs and the same query in InnoDB takes around 20.15 secs. Can anybody suggest me how to speed up this query ? You can't. InnoDB is transactional which means it can't keep basic information like the number of rows it has up to date (which means any number of inserts/ updates/deletes can happen at one time). MyISAM is not transactional (so only one insert/update/delete can happen at one time) so it can keep this information. What context are you trying to do a count in? Maybe it would be better to have an extra field in another table and keep that counter up to date? I know this relates to postgresql but the same idea could be useful for you: http://www.designmagick.com/article/36/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
Heiki Thx... it's work :D Sorry, one liner Heikki Tuuri [EMAIL PROTECTED] wrote: Ady, - Alkuperäinen viesti - Lähettäjä: Ady Wicaksono Vastaanottaja: Ady Wicaksono ; Heikki Tuuri Kopio: Lähetetty: Friday, September 03, 2004 10:41 AM Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9 :1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! Be very careful! From now on always follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html when you want to add a new ibdata file. Thx Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now.
Re: Problem on InnoDB - Tablespace enough but engine said table full
I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ibdata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/data3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:1500M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9:1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty Thx Ady Wicaksono [EMAIL PROTECTED] wrote: May i know, how could i know which of the data files that InnoDB MySQL engine is not used ? Did i i made a mistake when adding table space ? Heikki Tuuri wrote: Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! - Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard.
Re: Problem on InnoDB - Tablespace enough but engine said table full
Ady, - Alkuperäinen viesti - Lähettäjä: Ady Wicaksono [EMAIL PROTECTED] Vastaanottaja: Ady Wicaksono [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, September 03, 2004 10:41 AM Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9 :1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! Be very careful! From now on always follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html when you want to add a new ibdata file. Thx Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
May i know, how could i know which of the data files that InnoDB MySQL engine is not used ? Did i i made a mistake when adding table space ? Heikki Tuuri [EMAIL PROTECTED] wrote: Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers!
Re: Problem getting innodb enabled ...
Sorry, I should have included it originally. These are the only lines referencing innodb that I can find. # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 This is how it looks when I try to enable innodb, however, MySQL won't start with them uncommented, but it runs fine when I comment them out. -- Hugh mailto: [EMAIL PROTECTED] Victor Pendleton wrote: Can you post the exact syntax from your my.cnf file? -Original Message- From: Hugh Taylor To: [EMAIL PROTECTED] Sent: 7/12/04 3:17 PM Subject: Problem getting innodb enabled ... Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem getting innodb enabled ...
After much searching (and trying to read non-English posts), I've stumbled across the solution. I installed the mysql-MAX rpm, changed the my.cnf file and Viola! it works! -- Hugh mailto: [EMAIL PROTECTED] Hugh Taylor wrote: Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem getting innodb enabled ...
Can you post the exact syntax from your my.cnf file? -Original Message- From: Hugh Taylor To: [EMAIL PROTECTED] Sent: 7/12/04 3:17 PM Subject: Problem getting innodb enabled ... Running MySQL 4.0.15 on SuSE 9 Professional. MySQL works fine until I try to allow innodb by uncommenting the lines in the my.cnf file. Once I do this MySQLwill not start, the error message in the log is: 040712 15:25:39 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/lib/mysql/' 040712 15:25:39 mysqld ended I found one thread in the SuSE listserv where someone fixed the problem by deleting all the files and directories in /var/lib/mysql except mysql and test, but that didn't work for me. The file permissions on /var/lib/mysql are: drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 mysql and drwxr-xr-x9 mysqldaemon544 2004-07-12 15:58 . drwxr-xr-x 41 root root 1056 2004-07-12 15:12 .. drwx--2 mysqldaemon576 2004-07-12 11:12 CPIAInventory drwx--2 mysqldaemon 17976 2004-02-26 08:50 egroupware drwx--2 mysqldaemon336 2004-06-28 16:21 EmployeeLog drwxr-xr-x2 mysqldaemon 48 2004-07-12 15:22 innodb drwx--2 mysqldaemon528 2003-11-12 18:13 mysql -rw-rw1 mysqldaemon 2877 2004-07-12 15:58 mysqld.log -rw-rw1 mysqldaemon220 2004-06-29 15:56 mysqld.log-20040630.gz -rw-rw1 mysqldaemon220 2004-06-30 18:20 mysqld.log-20040701.gz -rw-rw1 mysqldaemon284 2004-07-07 18:04 mysqld.log-20040708.gz -rw-rw1 mysqldaemon223 2004-07-10 08:56 mysqld.log-20040710.gz -rw-rw1 mysqldaemon237 2004-07-11 11:55 mysqld.log-20040712.gz -rw-rw1 mysqldaemon 5 2004-07-12 15:58 mysqld.pid srwxrwxrwx1 mysqldaemon 0 2004-07-12 15:58 mysql.sock drwx--2 mysqldaemon816 2004-06-28 15:29 phpmyadmin drwx--2 mysqldaemon 48 2003-11-12 18:13 test Any ideas? (I'm also posting this to the SuSE list.) -- Hugh mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Re: Problem creating InnoDB tables.
David, Wednesday, November 06, 2002, 10:00:45 PM, you wrote: DAF - Original Message - DAF From: Victoria Reznichenko [EMAIL PROTECTED] DAF To: [EMAIL PROTECTED] DAF Sent: Wednesday, November 06, 2002 11:47 AM DAF Subject: re: Problem creating InnoDB tables. David, Tuesday, November 05, 2002, 4:05:57 PM, you wrote: DAF I'm have trouble creating InnoDB tables. I'm using MySQL version DAF 3.32.51-max. I have innodb_data_file_path = ibdata1:30M:autoextend DAF in my DAF my.ini file. According to the manual, I should be able to use and DAF create DAF InnoDB tables. When I specify type = InnoDB in my table creation DAF statement, MySQL doesn't give me any errors, but when I use show DAF table DAF status;, the tables show up as MyISAM. Any help would be DAF appreciated. Use SHOW VARIABLES LIKE have_% to see if InnoDB is enabled. DAF I did that, and across from have_innodb it says NO. How could I enable DAF InnoDB? How did you run MySQL server? Which file of mysqld-*.exe did you run? -- 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: Problem creating InnoDB tables.
David, Tuesday, November 05, 2002, 4:05:57 PM, you wrote: DAF I'm have trouble creating InnoDB tables. I'm using MySQL version DAF 3.32.51-max. I have innodb_data_file_path = ibdata1:30M:autoextend in my DAF my.ini file. According to the manual, I should be able to use and create DAF InnoDB tables. When I specify type = InnoDB in my table creation DAF statement, MySQL doesn't give me any errors, but when I use show table DAF status;, the tables show up as MyISAM. Any help would be appreciated. Use SHOW VARIABLES LIKE have_% to see if InnoDB is enabled. -- 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: Problem creating InnoDB tables.
- Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 11:47 AM Subject: re: Problem creating InnoDB tables. David, Tuesday, November 05, 2002, 4:05:57 PM, you wrote: DAF I'm have trouble creating InnoDB tables. I'm using MySQL version DAF 3.32.51-max. I have innodb_data_file_path = ibdata1:30M:autoextend in my DAF my.ini file. According to the manual, I should be able to use and create DAF InnoDB tables. When I specify type = InnoDB in my table creation DAF statement, MySQL doesn't give me any errors, but when I use show table DAF status;, the tables show up as MyISAM. Any help would be appreciated. Use SHOW VARIABLES LIKE have_% to see if InnoDB is enabled. -- 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 -End Original Message- I did that, and across from have_innodb it says NO. How could I enable 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
