INSERT IGNORE BUG?
I am importing a file into a table in which I want it to ignore duplicate rows. When I specify --ignore (this also happens if I do a SELECT IGNORE INTO from the client also) I get a duplicate key error. If I run the command again it skips the first such instance of a duplicate key and gives me an error (and exits) for the next instance of a duplicate key. If I run the command over and over it finally goes through the whole file. Then the fun starts over. The primary key is on 9 columns but the index shown in the error only has 6 of the columns listed. Is this a bug? I am running ver. 5.0.27 on Linux. -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
-Ed
Logan, David (SST - Adelaide) wrote:
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
-Ed
Logan, David (SST - Adelaide) wrote:
Hi Ed,
You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.
Regards
--replicate-do-db=db_name
Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.
An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
The main reason for this just check the default database behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.
If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, How
Servers Evaluate Replication Rules.
---
** _/ ** David Logan
*** _/ *** ITO Delivery Specialist - Database
*_/* Hewlett-Packard Australia Ltd
_/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED]
_/ _/ _/ _/ Desk: +61 8 8408 4273
_/ _/ _/_/_/ Mobile: +61 417 268 665
*_/ **
** _/ Postal: 148 Frome Street,
_/ ** Adelaide SA 5001
Australia
invent
---
-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?
Ed, this is unfortunately by design. Personally I don't get why this
choice was made...
Reference:
http://lists.mysql.com/mysql/190869
Regards,
Atle
FCI, Inc. - Unix Systems Administrator
On Tue, 12 Dec 2006, Ed Pauley II wrote:
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1')
no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming
in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
Ed Pauley II
Internet Software Developer
Bloodstock Research Information Services
859.223.
800.354.9206 ext. 297
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Problem?
We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form INSERT INTO test.test_table VALUES('test','1') no
longer replicate. If you connect to or change to the test database and
then execute INSERT INTO test_table VALUES('test','1') the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming in
the query?
Any help would be greatly appreciated!
--
Ed Pauley II
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More than 4 CPUs?
It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! -- Ed Pauley II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Renato Golin wrote: Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. Yeah, known problem... We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. Why do you need a backup site to write things to your master ? Or did I got wrong ? This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. Let me see if I got it right: you have two sites, one master on each, one slave on each., and you want both master to replicate to the other and both slaves to receive data from them as well. Right ? If so, MySQL does not support multi-master setup. It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. As far as I've heard there is no direct move into that direction from MySQL developers, although a voting system for a new master among one of the slaves can happen in the near future. What may work is to have only one master on one of your sites and both sites update the same master (reducing speed for the slave site) and in the case of failure you switch them (manually or with some tool). hope that helps, --renato -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Peter Zaitsev wrote: On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. As some people mentioned there is Continuent solution, this is what was Emic networks previously. If you're building solution on your own such as master and number of slaves there are plenty of things to think, regarding load balancing, for example if replication breaks for any reason of falls behind on one of the slaves you might want to kick it up. For very basic setup you even can use DNS for load balancing, which does not solve many of the problems describe. The same kind of simple load balancing is build in MySQL JDBC Driver. In general everyone seems to implement something on their own, working well for their application. Continuent's m/cluster will not work for me as it does not allow replication across a WAN. We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. I have not been able to find an appliance that will balance the reads for me. I have looked into possible solutions such as Sequoia, which I know would require a different setup. Is anyone actually using Sequoia? Does anyone use an appliance for load balancing MySQL? LVS seems like a good possibility but I would rather have an out-of-box solution since I will be repeating this setup at a remote location. -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load Balancing
I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Threaded on RedHat ES 4
I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Threaded on RedHat ES 4
I think this article explains what I am seeing but I don't know how to determine which threading I am using. I just know it is not user threads in a separate process. http://dev.mysql.com/doc/refman/4.1/en/thread-packages.html Ed Pauley II wrote: I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: mysqldumps from java program]
-- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com ---BeginMessage--- If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. William Fong wrote: Is it possible to setup replication so you would have another server to do backups on? Replicate the data, do whatever you want to the spare, and then delete the data from the production server. On 4/19/06, balaraju mandala [EMAIL PROTECTED] wrote: Hi Everybody, I need a suggestion regarding mysqldump. My problem is my application is creatiing around 500Mb of data per day. As i want my application run 24*7*365. I need a mechanisem where i can move, previous day's data to another location(i.e) at any given time i just want to store one or two days data only in my current DB. So i planned to make this by using mysqldump, as u know it will create files which we can upload where ever we need. Is this is a good idea, or we have another better mechanisem? I am planning to automate this using Java. Is we have any prebiuild tools for this? -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
out of curiosity, how do you do that? Daniel da Veiga wrote: If I am not mistaken deleting from the production server would delete the data on the slave in a replication environment. Not if you disable the binlog of the query that will delete data... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: mysqldumps from java program]
Daniel da Veiga wrote:
On 4/19/06, Ed Pauley II [EMAIL PROTECTED] wrote:
out of curiosity, how do you do that?
Daniel da Veiga wrote:
If I am not mistaken deleting from the production server would delete
the data on the slave in a replication environment.
Don't get me wrong, try not to top-post, it makes the message harder to read...
SET SQL_LOG_BIN = {0|1}
Disables or enables binary logging for the current connection
(SQL_LOG_BIN is a session variable) if the client connects using an
account that has the SUPER privilege. The statement is ignored if the
client does not have that privilege.
So, if you DELETE data with an account that has the SUPER privilege,
you just issue this SET command before any statment and it won't log
your subsequent queries.
BTW, it was quoted fromt he MySQL Manual.
--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--
I just found it myself. Thanks for the detailed reply.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Query Using Index
I have notices some slow queries showing up in my slow query log lately. Two of these queries are relatively simple queries using the index of their respective tables. Both of these tables are very large and I suspect this to be the problem. This problem appears to have started within the last couple of months. These tables grow in data daily. Table 1 Rows: 17,794,256 Index Size: 511,596,544 Table 2 Rows: 43,513,707 Index Size: 1,009,502,208 Is there anything that I can do? I have optimized the tables, but it did not appear to help. Is there a limit to MySQL efficiency and table size? -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suggestions on db server configuration - Replication load balancing or Clustering??
I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and DNS problem
I have 3 different MySQL server setups. On two of the servers, one running 4.0 and one running 4.1, I have seen a peculiar problem. It seems that when a client server is taken offline for a period of time (few hours to couple of days) and is brought back online, MySQL has a problem resolving the hostname and the clients are denied access. Obviously my privileges are setup by hostname instead of IP. I have also seen a weird problem with one client that makes and closes a lot of connections. MySQL cannot resolve this servers hostname for brief periods around the same time every day. Are there any known issues with MySQL and its internal DNS caching? Could there be something wrong with my DNS servers configuration? It just seems odd that it works fine 99.9% of the time. I suppose I will have to change my privileges to use IPs. Any help or input would be appreciated. Thanks, Ed -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
