[Replication] - urgent
Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks & regards, Ratheesh
Re: [Replication] - urgent
Ratheesh K J schrieb: Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks & regards, Ratheesh You have 2 options: 1. on the slave, enter "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;" and then "SLAVE START;" on the slave. This skips the upcoming entry in the binlog which is the create table command that causes your problem. 2. if you don't have any data in the table on the slave, just drop the table and do a "slave start;", it will then create the table again as this is the next command in the binlog. Remember: never write on the slave without knowing what you do and you'll be happy with your replication ;) Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB Schema Comparison Utility ?
On Mon, 2007-10-01 at 12:56 -0700, Daevid Vincent wrote: > This has been asked for many many times on this list, not sure why mySQL AB > doesn't just release a command line tool like a 'mysql diff' and also a > 'mysql lint'. The lint one should be totally trivial for them to do, as they > already have a SQL parser! I can't tell you how many times our daily build > was broken by a missing semi-colon or some other SQL syntax error. We run > all commits through "php -l" and ruby's checker, but mysql is the only one > we have to sweat over. > > While I'm glad that pretty GUI tools like Upscene's exist, that doesn't do > us any good on a linux build system where it does an "svn checkout", runs > automated BVT tests, compiles code, uploads to a daily build directory, etc. > > We need command line tools that run on linux. > > :( :) Theres a perl tool called SLQ Fairy. Its main use is translating DDL to other dialects, html, images, templates etc but it also has a very handy sqlt-diff that does want you want from the command line. http://search.cpan.org/~jrobinson/SQL-Translator-0.08001/ http://sqlfairy.sourceforge.net/ # cpan SQL::Translater hth, mark > > MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? -Original Message- > > From: John Comerford [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 27, 2007 9:49 PM > > To: mysql@lists.mysql.com > > Subject: DB Schema Comparison Utility ? > > > > Hi Folks, > > > > I am new to MySQL. We have a development environment where we have > > three systems > > > > 1) Developement Database on Machine A > > 2) Test Databasae on Machine B > > 3) Live Database on Machine C > > > > So we make changes to the Developement Database, then move > > them to test > > then to live. My question is, is there a way of > > automatically migrating > > the changes from Dev to Test, Test to Live ? > > > > I have worked with a DB which had an admin function wherein you could > > connect two databases and it would run a comparison between the two > > databases and produce a file of schema differences which you could > > import to make the two DB schema's the same. Is there something like > > this for MySQL ? > > > > TIA, > > John > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Innodb Table Locks deadlocking with AUTO_INC locks.
Hello List,
I discovered an unusual problem with the way Innodb handles the AUTO_INC lock
with a full table lock. I was wondering if this is a known issue, or I'm doing
something completely wrong. I'm working with MYSQL Server version:
5.0.42-debug-log on Gentoo Linux.
So lets say I have this table:
CREATE TABLE `quicktable` (
`x` int(11) NOT NULL auto_increment,
`quicktext` varchar(50) default NULL,
PRIMARY KEY (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Several connections are inserting concurrently to the table with normal single
statements such as:
INSERT INTO quicktable (quicktext) VALUES ('Bob 25 item 5');
The value inserted into quicktable changes for every insert to indicate which
connection and which insert its doing
Now in a unique connection is locking the table using the INNODB suggested
method:
SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;
For demonstration purposes this thread sleeps for a second to simulate
processing that might be going on in the application.
After 1 Second:
COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;
The result is a dead lock where all queries wait until one of the INSERT's
times out then the LOCK statement manages to get the table lock. But it happens
repeatedly with as few as two connections sending inserts. The server can
handle many many more concurrent inserts if the lock is removed , with out
resulting in any locks.
Here is the output of show processlist:
SHOW PROCESSLIST;
+--+--+---++-+--+++
| Id | User | Host | db | Command | Time | State | Info
|
+--+--+---++-+--+++
| 1470 | bob | localhost | test | Query | 19 | update | INSERT INTO
quicktable (quicktext) VALUES ('Bob 26816 item 5') |
| 1471 | bob | localhost | test | Query | 19 | Locked | INSERT INTO
quicktable (quicktext) VALUES ('Bob 6817 item 2') |
| 1472 | bob | localhost | test | Query | 19 | Locked | LOCK TABLES
quicktable WRITE |
+--+--+---++-+--++--+
SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6
LATEST DETECTED DEADLOCK
071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721
setting table lock
mysql tables in use 1, locked 0
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1472, query id 24493 localhost bob System lock
LOCK TABLES quicktable WRITE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382727 lock mode X waiting
*** (2) TRANSACTION:
TRANSACTION 0 26382726, ACTIVE 0 sec, process no 6819, OS thread id 24248336
inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 1471, query id 24483 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 37')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode IX waiting
*** WE ROLL BACK TRANSACTION (2)
TRANSACTIONS
Trx id counter 0 26382734
Purge done for trx's n:o < 0 26382636 undo n:o < 0 0
History list length 39
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 26382731, not started, process no 6818, OS thread id 24231951
mysql tables in use 1, locked 1
MySQL thread id 1470, query id 24519 localhost bob Table lock
INSERT INTO quicktable (quicktext) VALUES (' Bob 6816 item 38')
---TRANSACTION 0 0, not started, process no 3631, OS thread id 17858573
MySQL thread id 1081, query id 7444 localhost bob
---TRANSACTION 0 26375280, not started, process no 2153, OS thread id 16531468
MySQL thread id 1000, query id 24536 localhost bob
show innodb status
---TRANSACTION 0 0, not started, process no 1894, OS thread id 16318475
MySQL thread id 987, query id 1621 localhost bob
---TRANSACTION 0 26382733, ACTIVE 17 sec, process no 6819, OS thread id
24248336 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1471, query id 24507 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 38')
--- TRX HAS BEEN WAITING 17 SEC FOR THIS L
Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Hi William,
William Newton wrote:
Hello List,
I discovered an unusual problem with the way Innodb handles the AUTO_INC lock
with a full table lock. I was wondering if this is a known issue, or I'm doing
something completely wrong. I'm working with MYSQL Server version:
5.0.42-debug-log on Gentoo Linux.
So lets say I have this table:
CREATE TABLE `quicktable` (
`x` int(11) NOT NULL auto_increment,
`quicktext` varchar(50) default NULL,
PRIMARY KEY (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Several connections are inserting concurrently to the table with normal single
statements such as:
INSERT INTO quicktable (quicktext) VALUES ('Bob 25 item 5');
The value inserted into quicktable changes for every insert to indicate which
connection and which insert its doing
Now in a unique connection is locking the table using the INNODB suggested
method:
SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;
For demonstration purposes this thread sleeps for a second to simulate
processing that might be going on in the application.
After 1 Second:
COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;
The result is a dead lock where all queries wait until one of the INSERT's
times out then the LOCK statement manages to get the table lock. But it happens
repeatedly with as few as two connections sending inserts. The server can
handle many many more concurrent inserts if the lock is removed , with out
resulting in any locks.
Here is the output of show processlist:
SHOW PROCESSLIST;
+--+--+---++-+--+++
| Id | User | Host | db | Command | Time | State | Info |
+--+--+---++-+--+++
| 1470 | bob | localhost | test | Query | 19 | update | INSERT INTO
quicktable (quicktext) VALUES ('Bob 26816 item 5') |
| 1471 | bob | localhost | test | Query | 19 | Locked | INSERT INTO
quicktable (quicktext) VALUES ('Bob 6817 item 2') |
| 1472 | bob | localhost | test | Query | 19 | Locked | LOCK TABLES
quicktable WRITE |
+--+--+---++-+--++--+
SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6
LATEST DETECTED DEADLOCK
071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721
setting table lock
mysql tables in use 1, locked 0
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1472, query id 24493 localhost bob System lock
LOCK TABLES quicktable WRITE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382727 lock mode X waiting
*** (2) TRANSACTION:
TRANSACTION 0 26382726, ACTIVE 0 sec, process no 6819, OS thread id 24248336
inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 1471, query id 24483 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 37')
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/quicktable` trx id 0 26382726 lock mode IX waiting
*** WE ROLL BACK TRANSACTION (2)
TRANSACTIONS
Trx id counter 0 26382734
Purge done for trx's n:o < 0 26382636 undo n:o < 0 0
History list length 39
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 26382731, not started, process no 6818, OS thread id 24231951
mysql tables in use 1, locked 1
MySQL thread id 1470, query id 24519 localhost bob Table lock
INSERT INTO quicktable (quicktext) VALUES (' Bob 6816 item 38')
---TRANSACTION 0 0, not started, process no 3631, OS thread id 17858573
MySQL thread id 1081, query id 7444 localhost bob
---TRANSACTION 0 26375280, not started, process no 2153, OS thread id 16531468
MySQL thread id 1000, query id 24536 localhost bob
show innodb status
---TRANSACTION 0 0, not started, process no 1894, OS thread id 16318475
MySQL thread id 987, query id 1621 localhost bob
---TRANSACTION 0 26382733, ACTIVE 17 sec, process no 6819, OS thread id
24248336 setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1471, query id 24507 localhost bob update
INSERT INTO quicktable (quicktext) VALUES ('Bob 6817 item 38')
--- T
Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Thanks for the quick reply Barron, but doesn't SET AUTOCOMMIT = 0; disable
AUTOCOMMIT ?
from http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html :
The correct way to use LOCK TABLES and
UNLOCK TABLES with transactional tables,
such as InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the
transaction explicitly. When you call LOCK
TABLES, InnoDB internally takes
its own table lock, and MySQL takes its own table lock.
InnoDB releases its table lock at the
next commit, but for MySQL to release its table lock, you
have to call UNLOCK TABLES. You should
not have AUTOCOMMIT = 1, because then
InnoDB releases its table lock
immediately after the call of LOCK
TABLES, and deadlocks can very easily happen. Note
that we do not acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.
So it says deadlocks can happen very easily if AUTOCOMMIT=1, but we do not
acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.
In this particular situation the deadlock does not happen if AUTO COMMIT =1,
but that could cause other deadlocks.
Am I confused, or is it that really unclear?
- Original Message
From: Baron Schwartz <[EMAIL PROTECTED]>
To: William Newton <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Tuesday, October 2, 2007 12:05:41 PM
Subject: Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Hi William,
William Newton wrote:
> Hello List,
>
> I discovered an unusual problem with the way Innodb handles the AUTO_INC lock
> with a full table lock. I was wondering if this is a known issue, or I'm
> doing something completely wrong. I'm working with MYSQL Server version:
> 5.0.42-debug-log on Gentoo Linux.
>
> So lets say I have this table:
>
> CREATE TABLE `quicktable` (
> `x` int(11) NOT NULL auto_increment,
> `quicktext` varchar(50) default NULL,
> PRIMARY KEY (`x`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
>
>
> Several connections are inserting concurrently to the table with normal
> single statements such as:
>
> INSERT INTO quicktable (quicktext) VALUES ('Bob 25 item 5');
>
> The value inserted into quicktable changes for every insert to indicate which
> connection and which insert its doing
>
> Now in a unique connection is locking the table using the INNODB suggested
> method:
>
> SET AUTOCOMMIT = 0;
> LOCK TABLES quicktable WRITE;
>
> For demonstration purposes this thread sleeps for a second to simulate
> processing that might be going on in the application.
> After 1 Second:
>
> COMMIT;
> UNLOCK TABLES;
> SET AUTOCOMMIT =1;
>
>
>
> The result is a dead lock where all queries wait until one of the INSERT's
> times out then the LOCK statement manages to get the table lock. But it
> happens repeatedly with as few as two connections sending inserts. The server
> can handle many many more concurrent inserts if the lock is removed , with
> out resulting in any locks.
>
> Here is the output of show processlist:
> SHOW PROCESSLIST;
>
> +--+--+---++-+--+++
> | Id | User | Host | db | Command | Time | State | Info
> |
> +--+--+---++-+--+++
> | 1470 | bob | localhost | test | Query | 19 | update | INSERT INTO
> quicktable (quicktext) VALUES ('Bob 26816 item 5') |
> | 1471 | bob | localhost | test | Query | 19 | Locked | INSERT INTO
> quicktable (quicktext) VALUES ('Bob 6817 item 2') |
> | 1472 | bob | localhost | test | Query | 19 | Locked | LOCK TABLES
> quicktable WRITE |
> +--+--+---++-+--++--+
>
> SHOW INNODB STATUS:
> =
> 071002 16:51:55 INNODB MONITOR OUTPUT
> =
> Per second averages calculated from the last 9 seconds
> --
> SEMAPHORES
> --
> OS WAIT ARRAY INFO: reservation count 91, signal count 91
> Mutex spin waits 0, rounds 0, OS waits 0
> RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6
>
> LATEST DETECTED DEADLOCK
>
> 071002 16:51:37
> *** (1) TRANSACTION:
> TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721
> setting table lock
> mysql tables in use 1, locked 0
> LOCK WAIT 1 lock struct(s), heap size 320
> MySQL thread id 1472, query id 24493 localhost bob System lock
> LOCK TABLES
Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Yes. Sorry, I wasn't verbose enough. I agree with you. And I also
agree that this part of the manual is kind of nonsensical. I have never
understood it fully. Part of what I was saying is "I wonder whether the
manual is wrong and you are getting a deadlock anyway."
William Newton wrote:
Thanks for the quick reply Barron, but doesn't SET AUTOCOMMIT = 0; disable
AUTOCOMMIT ?
from http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html :
The correct way to use LOCK TABLES and
UNLOCK TABLES with transactional tables,
such as InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the
transaction explicitly. When you call LOCK
TABLES, InnoDB internally takes
its own table lock, and MySQL takes its own table lock.
InnoDB releases its table lock at the
next commit, but for MySQL to release its table lock, you
have to call UNLOCK TABLES. You should
not have AUTOCOMMIT = 1, because then
InnoDB releases its table lock
immediately after the call of LOCK
TABLES, and deadlocks can very easily happen. Note
that we do not acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.
So it says deadlocks can happen very easily if AUTOCOMMIT=1, but we do not
acquire the InnoDB table
lock at all if AUTOCOMMIT=1, to help old
applications avoid unnecessary deadlocks.
In this particular situation the deadlock does not happen if AUTO COMMIT =1, but that could cause other deadlocks.
Am I confused, or is it that really unclear?
- Original Message
From: Baron Schwartz <[EMAIL PROTECTED]>
To: William Newton <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
Sent: Tuesday, October 2, 2007 12:05:41 PM
Subject: Re: Full Innodb Table Locks deadlocking with AUTO_INC locks.
Hi William,
William Newton wrote:
Hello List,
I discovered an unusual problem with the way Innodb handles the AUTO_INC lock
with a full table lock. I was wondering if this is a known issue, or I'm doing
something completely wrong. I'm working with MYSQL Server version:
5.0.42-debug-log on Gentoo Linux.
So lets say I have this table:
CREATE TABLE `quicktable` (
`x` int(11) NOT NULL auto_increment,
`quicktext` varchar(50) default NULL,
PRIMARY KEY (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Several connections are inserting concurrently to the table with normal single
statements such as:
INSERT INTO quicktable (quicktext) VALUES ('Bob 25 item 5');
The value inserted into quicktable changes for every insert to indicate which
connection and which insert its doing
Now in a unique connection is locking the table using the INNODB suggested
method:
SET AUTOCOMMIT = 0;
LOCK TABLES quicktable WRITE;
For demonstration purposes this thread sleeps for a second to simulate
processing that might be going on in the application.
After 1 Second:
COMMIT;
UNLOCK TABLES;
SET AUTOCOMMIT =1;
The result is a dead lock where all queries wait until one of the INSERT's
times out then the LOCK statement manages to get the table lock. But it happens
repeatedly with as few as two connections sending inserts. The server can
handle many many more concurrent inserts if the lock is removed , with out
resulting in any locks.
Here is the output of show processlist:
SHOW PROCESSLIST;
+--+--+---++-+--+++
| Id | User | Host | db | Command | Time | State | Info |
+--+--+---++-+--+++
| 1470 | bob | localhost | test | Query | 19 | update | INSERT INTO
quicktable (quicktext) VALUES ('Bob 26816 item 5') |
| 1471 | bob | localhost | test | Query | 19 | Locked | INSERT INTO
quicktable (quicktext) VALUES ('Bob 6817 item 2') |
| 1472 | bob | localhost | test | Query | 19 | Locked | LOCK TABLES
quicktable WRITE |
+--+--+---++-+--++--+
SHOW INNODB STATUS:
=
071002 16:51:55 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 9 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 91, signal count 91
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 170, OS waits 85; RW-excl spins 7, OS waits 6
LATEST DETECTED DEADLOCK
071002 16:51:37
*** (1) TRANSACTION:
TRANSACTION 0 26382727, ACTIVE 0 sec, process no 6820, OS thread id 24264721
setting table lock
mysql tables in u
image_getindb.php?rob=bert
-- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multipart + IN comparison on the second part + JOIN does not use full index
Hi, all. I couldn't find this mentioned in the docs or in
the archives, so I'd figure I'd ask. I have a table with a
multipart index on three columns. When querying the table
alone using IN operators on any of the three columns, all
parts of the index are used. However, if I do a JOIN with
another table on the first column, the first part of the
index is used, but not the rest-- but only when searching
for multiple values on col2. Best explained by example, so
here's the table:
CREATE TABLE `table1` (
`col1` char(1) default NULL,
`col2` char(1) default NULL,
`col3` char(1) default NULL,
KEY `col1` (`col1`,`col2`,`col3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So with multiple IN operators against table1 alone, EXPLAIN
gives the expected key_len of 6:
EXPLAIN
SELECT SQL_NO_CACHE COUNT(*)
FROM table1
WHERE table1.col1 IN ('A', 'B') AND table1.col2 IN ('A', 'B') AND
table1.col3 IN ('A', 'B')
and if I JOIN against another table with single values in
the IN operators, I again get a key_len of 6:
EXPLAIN
SELECT SQL_NO_CACHE COUNT(*)
FROM table1, table2
WHERE table1.col1 = table2.col1 AND table1.col2 IN ('A') AND table1.col3 IN
('A')
This one, however, results in a key_len of 2:
EXPLAIN
SELECT SQL_NO_CACHE COUNT(*)
FROM table1, table2
WHERE table1.col1 = table2.col1 AND table1.col2 IN ('A', 'B') AND
table1.col3 IN ('A', 'B')
Is this expected behavior? It surprised me that the second
query would take full advantage of the index but not the
third. We're using MySQL 4.1.20.
Eamon Daly
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] - urgent
Thanks, It helped me a lot. I wanted to know 1.. what are the various scenarios where my replication setup can fail? (considering even issues like network failure and server reboot etc). What is the normal procedure to correct the failure when something unpredicted happens? 2.. What are the scenarios where the SQL THREAD stops running and what are the scenarios where the IO THREAD stops running? 3.. Does SQL_SLAVE_SKIP_COUNTER skip the statement of the master binlog from being replicated to the slave relay log OR Has the statement already been copied into the slave relay log and has been skipped from the relay log? 4.. How do I know immediately that replication has failed? ( have heard that the enterprise edition has some technique for this )? Thanks & regards, Ratheesh - Original Message - From: "Jan Kirchhoff" <[EMAIL PROTECTED]> To: "Ratheesh K J" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, October 02, 2007 4:16 PM Subject: Re: [Replication] - urgent > Ratheesh K J schrieb: >> Hello all, >> >> I issued a create table statement on the master for a table which was not >> present on the master but present on the slave. >> I did this purposely to see the error on slave. >> >> I am a newbie to replication. Now when i see SLave status on the slave >> machine it shows that the SQL Thread has stopped. >> >> When I start the SQL thread it does not start and gives the error message >> that the table exists. How do i correct this and how do I calculate the next >> position that the slave must start executing from the relay log. >> >> Is there any article on MySQL replication that tells me how to deal when >> errors occur. >> >> Thanks & regards, >> Ratheesh >> > > You have 2 options: > > 1. > on the slave, enter "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;" and then > "SLAVE START;" on the slave. This skips the upcoming entry in the binlog > which is the create table command that causes your problem. > > 2. > if you don't have any data in the table on the slave, just drop the > table and do a "slave start;", it will then create the table again as > this is the next command in the binlog. > > Remember: never write on the slave without knowing what you do and > you'll be happy with your replication ;) > > Jan
Re: Multipart + IN comparison on the second part + JOIN does not use full index
It is way past bed time so excuse me if I am way off...
What is the order of tables in the explain? What is shown as the select_type?
On 10/2/07, Eamon Daly <[EMAIL PROTECTED]> wrote:
> Hi, all. I couldn't find this mentioned in the docs or in
> the archives, so I'd figure I'd ask. I have a table with a
> multipart index on three columns. When querying the table
> alone using IN operators on any of the three columns, all
> parts of the index are used. However, if I do a JOIN with
> another table on the first column, the first part of the
> index is used, but not the rest-- but only when searching
> for multiple values on col2. Best explained by example, so
> here's the table:
>
> CREATE TABLE `table1` (
> `col1` char(1) default NULL,
> `col2` char(1) default NULL,
> `col3` char(1) default NULL,
> KEY `col1` (`col1`,`col2`,`col3`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> So with multiple IN operators against table1 alone, EXPLAIN
> gives the expected key_len of 6:
>
> EXPLAIN
> SELECT SQL_NO_CACHE COUNT(*)
> FROM table1
> WHERE table1.col1 IN ('A', 'B') AND table1.col2 IN ('A', 'B') AND
> table1.col3 IN ('A', 'B')
>
> and if I JOIN against another table with single values in
> the IN operators, I again get a key_len of 6:
>
> EXPLAIN
> SELECT SQL_NO_CACHE COUNT(*)
> FROM table1, table2
> WHERE table1.col1 = table2.col1 AND table1.col2 IN ('A') AND table1.col3 IN
> ('A')
>
> This one, however, results in a key_len of 2:
>
> EXPLAIN
> SELECT SQL_NO_CACHE COUNT(*)
> FROM table1, table2
> WHERE table1.col1 = table2.col1 AND table1.col2 IN ('A', 'B') AND
> table1.col3 IN ('A', 'B')
>
> Is this expected behavior? It surprised me that the second
> query would take full advantage of the index but not the
> third. We're using MySQL 4.1.20.
--
Rob Wultsch
(480)223-2566
[EMAIL PROTECTED] (email/google im)
wultsch (aim)
[EMAIL PROTECTED] (msn)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: "Out of memory; check if mysqld or some other process uses all available memory;" error
Hi Mathieu, I found that innodb_buffer pool value isn't set..The whole innodb settings are commented. I found the below values from the my.cnf file: # 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 = 256M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 64M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 here is the memory settings: total used free shared buffers cached' mem: 4054 4038 15 0 6 1426 buffers/cache 2605 1448 swap: 4094 150 3943 Please help me out to change the parameter values Regards, Amarnath S Amarnath Shivashankar wrote : > We have found that the MYSQL on all Email DB servers starts throwing "Out of > memory; check if mysqld or some other process uses all available memory;" > error. The error goes once we restart MySQL. But after a week again the same > problem occurs. We have 4 GB of physical memory on the server but Mysql > utilizes only up to 2.5 GB & starts throwing Out of memory error > > Please help me to resolve this. > Mathieu Bruneau wrote: This looks like the traditionnal 32 bits limitation ... You're using a 32 bits system right ? Because of many reasons (lots of documentation on the net about that) MySQL is in practice limited to about 2.4-2.6G of memory, thus the error you see. When I experienced this errors, I lowered the mysql_buffer and innodb_buffer so that mysql would stay below this limit and never had other issue with it. (It was crashing with an error 11 before). I kept this settings till I could upgrade to a 64 bits host. -- Mathieu Bruneau aka ROunofF Regards, Amarnath Shivashankar SQL Database Management 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
