replication of RENAME TABLE d1.t TO d2.t
i've a need to change the name of a database and haven't done this before in our live server. while the tables are myisam, i'm not inclined to rename the dirname of d1's datafiles because i'd rather not interrupt service for other databases and i'd prefer if the renaming would replicate. can i do it like this: in the application, kick users of d1 out create database d2 foreach t in d1: RENAME TABLE d1.t TO d2.t tell user to use d2 ? tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication : request DELETE is not executed on slave
16.3.1.9. Replication and LIMIT Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause. http://dev.mysql.com/doc/refman/5.0/en/replication-features-limit.html On 4/28/10 11:24 AM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: David Florella [mailto:dflore...@legos.fr] Sent: Wednesday, April 28, 2010 10:51 AM To: mysql@lists.mysql.com Cc: 'Krishna Chandra Prajapati' Subject: RE: Replication : request DELETE is not executed on slave Hi, In the MySQL documentation, it is written that the two versions are compatible to make a replication. It seems that if I make a DELETE without the 'LIMIT 7500', the query is replicated to the slave. [JS] This might be my ignorance speaking, but since record order is not defined for a database how would the slave know WHICH records to delete. The DELETE... LIMIT 7500 would have to be translated into 7500 separate DELETEs, and there would have to be some unique way of identifying the individual records. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Regards, David. -Message d'origine- De : Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Envoy� : mercredi 28 avril 2010 11:15 � : dflore...@legos.fr Cc : mysql@lists.mysql.com Objet : Re: Replication : request DELETE is not executed on slave Hi dflorella, The important thing about mysql replication is same mysql version for both master as well as slave should be used. It should be taken as good practice. You need to check that master and slave are in sync. Is there any error (replication) on the slave server. Check the mode, strict or some thing else. Does the delete command exits in binlog. Regards, Krishna On Wed, Apr 28, 2010 at 2:37 PM, David Florella dflore...@legos.fr wrote: Hi, I am using MySQL replication : - The version of the master is 4.1.12-log - The version of the slave is 5.0.41 When I use the query '' DELETE QUICK FROM [TABLE] WHERE [field] 'xx' LIMIT 7500, the query is executed on the master but not on the slave. Do you know why the request is not executed on the slave? Regards, David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication : request DELETE is not executed on slave
it's worth a try -- the manual for 4.1 has the same text about ORDER BY in section 14.7. On 4/28/10 12:30 PM, David Florella dflore...@legos.fr wrote: Hi, Thanks to you and everyone. I will test the same request with the ORDER BY clause. Regards, David. -Message d'origine- De : Mattia Merzi [mailto:mattia.me...@gmail.com] Envoyé : mercredi 28 avril 2010 17:54 À : mysql@lists.mysql.com Objet : Re: Replication : request DELETE is not executed on slave AFAIR you can use LIMIT with replication only if you use row-based replication (or mixed), that means that you must use mysql 5.1. Greetings, Mattia. 2010/4/28 Tom Worster f...@thefsb.org: 16.3.1.9. Replication and LIMIT Replication of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements is not guaranteed, since the order of the rows affected is not defined. Such statements can be replicated correctly only if they also contain an ORDER BY clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using a join-table twice in one query
Say tables a and b each have their own id column (primary key) and sundry other columns. Table j has columns aid and bid to join a and b many-to-many. Now, I want to select the joined a/b rows where the b rows are joined to (a different set of) rows in a that meet condition x (which refers only to a). Saying it in two sentences: Find the set Bx of rows in b joined to rows in a that meet x. Now select a table of all joined a/b rows where the b rows are in set Bx. Using an IN-subquery to mimic the two-sentence formulation was very slow, as the manual warns it can be: SELECT ... FROM b INNER JOIN j ON j.bid=b.id INNER JOIN a ON j.aid=a.id WHERE b.id IN ( SELECT jx.bid FROM a ax INNER JOIN j jx ON jx.aid=ax.id WHERE x ) Joining b to a via j twice, once on the way out and again on the way home, was very fast but only with STRAIGHT_JOIN: SELECT STRAIGHT_JOIN ... FROM a ax JOIN j jx ON jx.aid=ax.id JOIN b ON jx.bid=b.id JOIN j ON j.bid=b.id JOIN a ON j.aid=a.id WHERE x What other approaches should I consider? (Is standard stuff in database text books?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join syntax problem
On 4/26/10 7:36 PM, Gary gp...@paulgdesigns.com wrote: I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . looks like there's no space between 'im' and 'JOIN' in the line above WHERE ky.image_id = im.image_id; try: print($query); Gets me this error message. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: More CPU or More RAM?
I'd go with the 4G 4-core server. If you're running apache and a sensible OS, the extra cores can be helpful. So, unless you know you have a need for very large key buffers, 4G should leave the OS plenty for FS cache. Not that I actually have a clue. I really just wanted to be the first to answer the original question. On 4/21/10 11:17 AM, shamu...@gmail.com shamu...@gmail.com wrote: OK, let's get back to the original question. for a database like mine (1.5GB), will 4GB or 8GB RAM make any difference performance wise? On Wed, Apr 21, 2010 at 10:04 AM, Johan Gant johan.g...@groupgti.comwrote: I guess this is a DB list, but I strongly disagree with Johan's suggestion to avoid using Views or Taxonomy. The advantages far outweigh the disadvantages in most cases. -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: 21 April 2010 15:44 To: shamu...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: More CPU or More RAM? Switch CMSes, you'll be better off. I have the pain of running Drupal, too. Your DB host is probably good enough, unless you're doing insane amounts of page views. What you need is Drupal optimisations. Here's just a few: - drupal keeps both it's sessions and cache in the DB. Change to memcache - the views module is horrible. Get rid of it and write your own queries - for pete's sake don't turn on the watchdog module, especially on debug. That, too goes in your db - avoid taxonomy - it does evil hiearchical queries et cetera ad nauseam :-) On Wed, Apr 21, 2010 at 4:31 PM, shamu...@gmail.com shamu...@gmail.com wrote: I have a 1.5G database which feeds a CMS web application (Drupal). Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. IO and CPU are high. So I am planning to upgrade it to a dedicated serer. Here are two choice of my server: 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. I know the best way to do this is to benchmark the two servers, but I can't do that, can only pick one. Could anyone of you tell me which one is better for higher MySQL performance, based on your experience? Thanks. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=shamu...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does the order of tuples in a bulk insert impact query performance?
On 2/7/10 7:28 AM, Anthony Urso antho...@cs.ucla.edu wrote: Does the order of tuples in a bulk insert impact later query performance? E.g. will sorting the rows before a bulk insert cause queries to perform better for indexed or non-indexed fields? when i load a large body of data (using INFILE), i remove the indexes before the load and add them back after. i work on the assumption that if the data is sorted already then the recreation of the indexes in the last step is faster. but then if you have orthogonal indexes, you have to choose one to sort the rows by. as for performance on accessing the table later in normal use, i guess it depends on the usage patterns. if there's a lot of clustered or sequential table reads then caching might be more effective if the rows are sorted. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
On 1/29/10 5:03 PM, mos mo...@fastmail.fm wrote: I noticed the article didn't say how much money you'll save by not paying through the nose for Oracle per server licensing, the cost of upgrading your hardware to get some speed out of Oracle, or the cost of having to hire one or more Oracle administrators to manage and tweak the database. how much does an oracle programmer who can maintain your queries with more than 61 joins cost, in, say, usd/hr? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: auto_increment without primary key in innodb ?
it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Probability Selects
On 1/7/10 3:10 PM, Matt Neimeyer m...@neimeyer.org wrote: I'm trying to select names at random from a table that contains the name and the frequency at which it is actually used in society. The table is defined as follows: CREATE TABLE `MaleNames` ( `Name_ID` int(11) NOT NULL auto_increment, `Name` char(50) default NULL, `Frequency` decimal(5,3) default NULL, PRIMARY KEY (`Name_ID`) ) Some examples: 1, Aaron, 0.240 3, Abe, 0.006 13, Adrian, 0.069 What's the best way to select names at random from this but still take into account frequency of use? after reading the source, i'd avoid using MySQL's RAND(). you can probably easily get better quality pseudorandom number in your app's environment. to get a random row, LIMIT 1, r, where offset r is a random number between 0 and (tablelength - 1), should work. if you must use RAND(), FLOOR(tablelength*RAND()) will work for r. if you want a random row from a constrained subset of rows based on frequency, e.g. WHERE Frequency 0.001, you can include that condition in the query and repeat it until you get a non-empty response. but if the constrained subset is only a small fraction of the table's rows then it might be more efficient to compute its size (with COUNT() in another query or a subquery) and use that in generating the offset r. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Aborted_connects is incresing repidly
On 12/30/09 1:13 AM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, My MySQL server Aborted_connects status is showing 8692 and is rapidly increasing. What are reasons and how do i decrease the same? We are using connect() method in PHP code and have tried below command mysqladmin flush-hosts but still the value is same. have you recently upgraded to php 5.3? if so, the default value for default_socket_timeout changed. lots of people experienced mysql connections aborting on long queries. i set it to -1, i.e. doesn't time out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help Save MySQL
On 12/18/09 12:41 PM, Douglas Nelson douglas.nel...@sun.com wrote: You guys are incredibly naive, Oracle has promised to support MySQL for the next 5 years. thanks. you too. Who better to have stewardship of a database product then the leading database supplier. almost anyone, since you ask. I think you guys have visions of grandeur to think you could spend this kind of effort or resources to build a better MySQL then Oracle. indeed we do. and the vision is sufficiently supported by experience to date. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
copying a static table
i have a large myisam table (about 3gb) that is updated once a day in the middle of the night. when it is not being updated, is there any reason not to copy it out with rsync without shutting down the server or flush tables with read lock or whatever? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about MySQL he would have not sold it or prevent from selling it to Sun. i don't see the logic in this sentence. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
On 12/13/09 11:23 AM, Neil Aggarwal n...@jammconsulting.com wrote: Doug: I do not see anyone willing to put up anything to support mysql... you don't like it... put up a billion dollars an take control or shut the heck up! Assuming MySQL as it stands today is 100% open source, I think an easier path is to create a new project from the currently existing sources. Call it something else, dbXYZ for instance. Then, Oracle can do anything it wants to MySQL. I am pretty sure if Michael leads the project, the open source community will follow. Can that be done or did I miss something? i think it can be done. If that is the case, what can Oracle really do to hurt MySQL? my guess is that it would be better if mysql did not need to fork. the harm would be the confusion caused to users. it would be sad if users chose not to use mysql because they had listened to scare stories about its possible future. if trust busters can be cajoled into making sure oracle doesn't harm oss mysql, that would be better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
and thanks for sharing your view. here's mine: mysql was sold to sun, a company with a long and deep commitment to oos. while there were obviously risks to the sale, one plausible motive (among others) is that a company like sun would be better placed to further develop, market and support mysql, get it into the hands of more users (sun is a trusted name even among the conservative and risk-averse parts of the market), leverage their service and support organization, etc. and if they can make money off it then maybe they will invest in development too. so i see it as reasonable to have believed that sun would be good for mysql, indeed that sun would be good next step for mysql in its journey. hence i don't see that this sale necessarily implies that monty did not really care about mysql. i'm not advocating these arguments. i'm simply saying that, whether one agrees with such arguments or not, there could plausibly exist conditions under which sale of mysql to sun was compatible with really caring about it. On 12/14/09 2:11 PM, Claudio Nanni claudio.na...@gmail.com wrote: You build a green park where children can play. Then you sell the park to a private company. The company can: not mantain it so that the park becomes junkies place, have people pay to access it or even close it. I, the builder, would not start a crusade or weep after I have sold it, Once it is on the market it is like any other goods. I am on MySQL almost ten years but I am not scared of switching to Postgres, to a fork, start a new project, or quit dba for other real open source spirit journey. The community and open spirit is important not the product. Thats my view. Thanks Monty, always, for your gift. Claudio On 14 dec 2009 19:36, Tom Worster f...@thefsb.org wrote: On 12/14/09 1:49 AM, Claudio Nanni claudio.na...@gmail.com wrote: If he really cared about My... i don't see the logic in this sentence. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
does mysqlhotcopy affect the key buffer?
i run mysqlhotcopy on each database once every three hours. i happened to be running my cache warmer this afternoon, which logs timing for a bunch of standard randomized queries, that when the backup script ran, the query times increased by a factor of about 20 from a nice quick pace back down to what i'd expect for an empty cache. hot times then slowly settled back down to the faster pace as i would expect as cold caches warm up. (i really don't know if key or table caches are the bottleneck.) so i was wondering if mysqlhotcopy has affecting the caches. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Second Request: Challenging Select Statement
On 12/7/09 5:26 AM, Victor Subervi victorsube...@gmail.com wrote: Hi; I posted this Saturday. Perhaps it's too challenging for those who read it to answer. I hope someone can. I need to write a select statement that enables me to select column 'ID' from a table where a certain value is found in an enum of a specific column. For example... select column_type from information_schema.columns where table_name='products' and column_name='Categories'; will give me the column and its enumerations. How do I supply one of the enumerations from a column 'Categories' and get the ID? WHERE col LIKE '%val%' i didn't answer this before not because it is challenging. on the contrary. i didn't answer because it is explained in the manual and on any number of easily found web pages. when you apparently don't make an effort to find the answer for yourself before you ask us, i don't feel much like making the effort to answer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
i have two questions. (1) innodb? (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? tom On 12/4/09 6:34 AM, Robinson, Eric eric.robin...@psmnv.com wrote: Let's face it, sometimes the master and slave get out of sync, even when 'show slave status' and 'show master status' indicate that all is well. And sometimes it is not feasible to wait until after production hours to resync them. We've been working on a method to do an emergency hot-resync during production hours with little or no user downtime. What do you guys think of this approach? It's only for Linux, though... 1. Shut down the slave and remove its replication logs (master.info and *relay* files). 2. Do an initial rsync of the master to the slave. Using rsync's bit-differential algorithm, this quickly copies most of the changed data and can be safely be done against a live database. This initial rsync is done before the next step to minimize the time during which the tables will be read-locked. 3. Do a 'flush tables with read lock;reset master' on the master server. At this point, user apps may freeze briefly during inserts or updates. 4. Do a second rsync, which goes very fast because very little data has changed between steps 2 and 3. 5. Unlock the master tables. 6. Restart the slave. When you're done, you have a 100% binary duplicate of the master database on the slave, with no worries that some queries got missed somewhere. The master was never stopped and users were not severely impacted. (Mileage may vary, of course.) We've tried this a few times and it has seemed to work well in most cases. We had once case where the slave SQL thread did not want to restart afterwards and we had to do the whole thing again, only we stopped the master the second time. Not yet sure what that was all about, but I think it may have been a race issue of some kind. We're still exploring it. Anyway, comments would be appreciated. -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
On 12/4/09 11:59 AM, Robinson, Eric eric.robin...@psmnv.com wrote: (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? Well... I guess mainly because I didn't know about that option! I thought I needed to fake out mysql on this, but it sounds like I can just do 'flush tables with read lock;reset master;' on the master and 'change master to...;' on the slave. So cool. Thanks for the input! 16.1.1 is probably my favorite chapter of the manual. 16.1.1.8 is particularly worth a read. http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
On 12/4/09 3:14 PM, Gavin Towey gto...@ffn.com wrote: I would say that it's very important to know why data is getting out of sync between your master and slave. Fixing those root causes would eliminate the need for this. i very much agree. the only instances of slaves getting out of whack that i've experienced was when i screwed something up administratively. There are cases where non-deterministic queries will produce different results, but that's what row based replication is supposed to solve =) 16.3.1 lists some interesting cases to consider: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html There are ways to resync data that don't involve all this as well: Maatkit has some tools that compare data between servers, and can fix them with queries. No stopping the slave or locking the master necessary. I've used them in production with good results. thanks for the pointer. looks handy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How can I improve this query?
On 12/2/09 11:13 AM, David Shere dsh...@steelerubber.com wrote: Tom Worster wrote: how about using LEFT JOIN: SELECT ... FROM listings a LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber This gives me a result set of 456,567 lines. I'm looking for a result set of 60-70 lines. (That's how many part numbers we have.) gosh. unless you have 456,567 listings, i'm not sure how that could happen other than through a CROSS JOIN or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How can I improve this query?
On 12/1/09 2:21 PM, David Shere dsh...@steelerubber.com wrote: select distinct a.PartNumber as 'Part Number', ( select count(1) from Transactions b where b.PartNumber = a.PartNumber) as 'No. Sold' from listings a order by a.PartNumber It currently takes 30 seconds to run. Transactions has 1200 records and listings has 7000. Multiple listings can have the same part number, as can transactions. We'd like to know how many transactions there are for each part number, including those part numbers for which there are listings but no transactions. Given the and zero transactions requirement, I can't figure out how to do this query with a join. how about using LEFT JOIN: SELECT ... FROM listings a LEFT JOIN Transactions b ON b.PartNumber = a.PartNumber ... wouldn't that tabulate also the unsold parts? and for speed, does Transactions.PartNumber have an index? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is LOAD INDEX INTO CACHE replicated?
Thank you for the answer. This makes sense. As such, the questions of preloading the key buffer in a replication system are probably resolved. I think I can safely put CACHE INDEX and LOAD INDEX INTO CACHE statements in the --init-file of both masters and slaves. tom On 11/29/09 8:39 AM, Johan De Meersman vegiv...@tuxera.be wrote: They're not data modification statements, so no, they're not replicated. On Sun, Nov 29, 2009 at 2:06 AM, Tom Worster f...@thefsb.org wrote: Are SQL statements like CACHE INDEX or LOAD INDEX INTO CACHE replicated? If so, is there a way to prevent that replication? If a slave mysqld restarts, wouldn't it need to execute CACHE INDEX and LOAD INDEX INTO CACHE statements from its --init-file? And if a master mysql restarts, would the slave execute CACHE INDEX and LOAD INDEX INTO CACHE statements the master reads from its --init-file and writes to the big-log? tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Is LOAD INDEX INTO CACHE replicated?
Are SQL statements like CACHE INDEX or LOAD INDEX INTO CACHE replicated? If so, is there a way to prevent that replication? If a slave mysqld restarts, wouldn't it need to execute CACHE INDEX and LOAD INDEX INTO CACHE statements from its --init-file? And if a master mysql restarts, would the slave execute CACHE INDEX and LOAD INDEX INTO CACHE statements the master reads from its --init-file and writes to the big-log? tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
