Distinct max() and separate unique value
I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
On Tue, 20 Oct 2009, DaWiz wrote: I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. You end up with the same object_id. select max(object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id order by term_taxonomy_id; ++--+ | max(object_id) | term_taxonomy_id | ++--+ | 1503 | 122 | | 1503 | 127 | ++--+ I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Copying tables between databases
I've got two databases Foo and Bar. Both databases have a table called 'zoop_t'. Foo's is the master copy (i.e. the one that gets updated) and Bar needs to be updated if/when changes are made. Currently, I'm mysqldump'ing that table from Foo at midnight via cron and feeding it back into Bar via cron. Is there a way to set up real-time replication of that table? -- WANT TO MODEL FOR MAC BUMBLE? APPLY AT http://casting.macandbumble.com - Eric Anderson Mac and Bumble Bumble Cash ICQ 3849549 http://www.macandbumble.com http://www.bumblecash.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - SEE OUR LATEST PARTY PICTURES -- http://events.macandbumble.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
--replicate-wild-do-table=db_name.tbl_name replicates only updates that use the specified table in the given database. If any wild cald patterns specified, then match the specified database and table name patterns. Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. I tried this, and it's not replicating. --replicate-wild-ignore-table=db_name.tbl_name Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. So to replicate only the merge_t table, try a) set-variable= binlog-do-db=DatabaseB set-variable= replicate-wild-do-table=DatabaseB.merge_t Except that you can't replicate a MRG table. to replicate all the tables except merge_t b) set-variable = binlog-do-db=DatabaseB set-variable = replicate-wild-do-table=DatabaseB.% set-variable = replicate-wild-ignore-table=DatabaseB.merge_t Ref: http://www.mysqlpress.com/doc/refman/5.0/en/replication-options.html Yes, this works, but as I said in my message, I don't want to replicate all the tables, just one. This DOESN'T work: set-variable= binlog-do-db=DatabaseB set-variable= replicate-do-table=DatabaseB.bar_t Though it should. I suspect that it's because of the MRG table, but I don't know what's wrong or how to fix it. (I should note this is Mysql 4.1.22) -- WANT TO MODEL FOR MAC BUMBLE? APPLY AT http://casting.macandbumble.com - Eric Anderson Mac and Bumble Bumble Cash ICQ 3849549 http://www.macandbumble.com http://www.bumblecash.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - SEE OUR LATEST PARTY PICTURES -- http://events.macandbumble.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication
I'm having a problem with my replication settings in my.cnf. I know it's probably something stupid. I've got an existing database configured to replicate as follows: set-variable= binlog-do-db=DatabaseA set-variable= replicate-do-table=DatabaseA.foo_t and that replicates that one single table just fine. I'm trying to replicate a single table from another database: set-variable= binlog-do-db=DatabaseB set-variable= replicate-do-table=DatabaseB.bar_t but nothing gets replicated. There is a MRG table in DatabaseB though, but adding the following: set-variable= binlog-do-db=DatabaseB set-variable= replicate-do-table=DatabaseB.bar_t set-variable= replicate-ignore-table=DatabaseB.merge_t doesn't help. The following works though: set-variable= binlog-do-db=DatabaseB set-variable= replicate-wild-do-table=DatabaseB.% set-variable= replicate-ignore-table=DatabaseB.merge_t but that replicates all tables except for the MRG table and I'd rather not replicate all the tables. Any ideas? -- WANT TO MODEL FOR MAC BUMBLE? APPLY AT http://casting.macandbumble.com - Eric Anderson Mac and Bumble Bumble Cash ICQ 3849549 http://www.macandbumble.com http://www.bumblecash.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - SEE OUR LATEST PARTY PICTURES -- http://events.macandbumble.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: solaris build mysql from source using gcc
Well if you'd share your specific problem I'm sure someone can help, but more importantly why are you building from source anyway? There are binary builds for most Solaris platforms, including 2.10. Sorry I meant 10 theremy solaris experience comes from the 'old days' before they went and dropped the 2. from the version scheme. Here's my config.nice for Solaris 2.x on Sparc with gcc: CC=gcc CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex --enable-thread-safe-client --enable-loca l-infile --disable-shared --without-innodb --with-named-curses-libs=-lncurses Just in case anyone's curious, here's my config.nice for Solaris 2.x on x86 with gcc: CC=gcc CFLAGS=-O3 -fno-omit-frame-pointer -march=pentiumpro CXX=gcc CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -march=pentiumpro ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex --with-unix-socket-path=/tmp/mysql.sock --enable-thread-safe-client --enable-local-infile --disable-shared --without-innodb --with-named-curses-libs=-lncurses --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static Couple of notes: I build without INNODB and ncurses is installed. This works great... very stable. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE table problem
I've got a master (Master) with a MERGE table of foo_t (comprising of bar_a, bar_b, bar_c) in database 'Igloo'. There are 5 slaves that replicate the Igloo table, but ignore the Igloo.foo_t table. This setup was working fine. If the Master server crashed or had a prblem for some reason, I could always: STOP SLAVE RESET SLAVE FLUSH TABLES LOAD DATA FROM MASTER START SLAVE The Master's motherboard failed last night, and after getting the server back up, replication seems to be broken. The LOAD DATA FROM MASTER command fails on ALL slaves with: mysql load data from master\g ERROR 1188 (HY000): Error from master: 'Can't find file: 'foo_t.MRG' (errno: 2)' mysql I've myisamchk'd all the tables on the Master server. I've dropped the 'foo_t' table and recreated it. Still get the above error on ALL slaves.. It seems to me there's definitely a problem on the Master server but I'm just not sure what it is...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave to multiple masters?
Are there any plans to address multiple masters to a single slave? I've been Googling this and it seems like it's a fairly desired feature. Or has this been addressed in 5.x? I know you can run multiple mysqld's on the slave, but it could (should) be done in a thread on the slave. -- - Eric Anderson Mac and Bumble Bumble Cash ICQ 3849549 http://www.macandbumble.com http://www.bumblecash.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - SEE OUR LATEST PARTY PICTURES -- http://events.macandbumble.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replacted MERGE table?
Here's a question for you guys: is it possible to have a replicated table of type MERGE? Scenario: Server: WWW1 Table: A.local_table (MASTER) replicates to: Server: MEMBERS Table: B.www1 (SLAVE) and Server: WWW2 Table: A.local_table (MASTER) replicates to: Server: MEMBERS Table: B.www2 (SLAVE) where B.www1 and B.www2 are type MERGE? Then I could a SELECT on the type merge (B.www1,B.www2,etc)? Possible? The more I look at it, the more complicated it looks. (sigh) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date query question
I don't have much experience working with dates in Mysql; I've always prefered to manage dates in code, so bear with me. I've got the following definition (yes, no indexes right now): CREATE TABLE se_t ( datestamp timestamp(14) NOT NULL, remote_addr char(16) NOT NULL default '', request_uri char(128) NOT NULL default '', remote_agent char(32) NOT NULL default '', domain char(32) NOT NULL default '', http_referer char(128) NOT NULL default '', query char(64) NOT NULL default '', status int(11) NOT NULL default '0', spider_type int(11) NOT NULL default '0', ) TYPE=MyISAM; And what I'm looking to be able to do is to be able to sum up totals based on date, whether it be today, yesterday, last week, last month, etc. I'm currently using 4.0.14, so I don't have access to certain functions (like date()), otherwise I'd be able to do something like this: SELECT count(*), sum(spider_type=0), sum(spider_type=1), sum(spider_type=2), sum(spider_type=3), sum(spider_type=4), sum(spider_type=5), sum(spider_type=6), sum(spider_type=7) FROM se_t WHERE date(datestamp)=date_sub(curdate(),interval 7 day) Is there an easier/cleaner/better way of doing this? Or should I just upgrade Mysql? (how stable is 4.1.x?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very strange problem
A little explaination: We've got two servers, e450 (3.23.47) and 1U (4.0.14). These two machines have a private connection between them (192.168.0.1 and 192.168.0.2). The e450 is the master server, and the 1U has a copy (no replication) of some basic data, so it can run server-intensive pages. However, it needs to connect back to the e450 to insert some data. Everything runs fine until the e450 does something that blocks other threads (including the threads that are connecting from the 1U) on the table 'vip_t'. Let's say for example that I run a lengthy query on table 'vip_t' which causes all the other threads to be blocked while that query runs. The threads connecting from the 1U (192.168.0.2) all start to block up as expected, but when the length query on the e450 stops running, the threads from the 1U stay blocked, and all NEW threads end up in a limbo state: | 726136 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726135 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726134 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726138 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726140 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726142 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726143 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726148 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726149 | nobody | localhost | bm | Sleep | 11 | | NULL | | 726150 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726153 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726158 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726161 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726164 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726170 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726172 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726173 | nobody | localhost | bm | Sleep | 4| | NULL | | 726181 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726185 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726191 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | | 726201 | unauthenticated user | 192.168.0.2 | NULL | Connect| NULL | login| NULL | No connections from 1U (192.168.0.2) are possible - they just hang, even the interactive client just hangs. In order to clear things, I have to shutdown both httpd and mysql on both machines and restart them. And on the e450, mysql will not shut down completely. 'mysqladmin shutdown' just hangs (ostensibly waiting for the above threads to clear). I end up flushing everything and killing both mysql and the starting script. I'm sure the solution is to upgrade the e450's version of mysql first because that seems to be where the problem is, but I hate to do it at gunpoint. I'm wondering if anyone else has heard/seen any of these problems? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very strange problem
Is anything being logged in the error log? Nope. My 'wait_timeout' was set to 120 seconds, I reduced that down to 60 seconds but I don't think that will help because once it gets stuck in this blocked thread thing, it never recovers.. any subsequent connections never actually process, until the max_connections are hit. It's very strange.. :-/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This is confusing..?
Given the following table: CREATE TABLE campaign_t ( acct_id int(11) unsigned NOT NULL default '0', site_id tinyint(3) unsigned NOT NULL default '0', ref_id int(11) unsigned NOT NULL default '0', datestamp char(10) NOT NULL default '', raws int(11) unsigned NOT NULL default '0', uniques int(11) unsigned NOT NULL default '0', trial_signups int(11) NOT NULL default '0', full_signups int(11) NOT NULL default '0', annual_signups int(11) unsigned NOT NULL default '0', PRIMARY KEY (acct_id,site_id,ref_id,datestamp), KEY acct_id (acct_id), KEY site_id (site_id), KEY ref_id (ref_id), KEY datestamp (datestamp) ) TYPE=MyISAM; How come it doesn't use the 'datestamp' index on this query: mysql explain SELECT * FROM campaign_t WHERE datestamp 20041105\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438166 | where used | ++--+---+--+-+--+++ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. What's the syntax for 'show keys'? -- -- Eric Anderson - ICQ: 3849549 - San Diego, CA [EMAIL PROTECTED] | Mac and Bumble | http://www.macandbumble.com [EMAIL PROTECTED] | VIP BumbleCash | http://vip.bumblecash.com [EMAIL PROTECTED] | Online Net-Entertainment | http://www.on-e.com -- You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This is confusing..?
Possibly because datestamp and 20041105 have different datatypes. There's an implicit data conversion required for the comparison, which in some cases prevents use of an index. Try writing '20041105' rather than 20041105 and see if that makes a difference. Nope.. :-/ mysql explain SELECT * FROM campaign_t WHERE datestamp '20041105'\g ++--+---+--+-+--+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--+++ | campaign_t | ALL | datestamp | NULL |NULL | NULL | 438473 | where used | ++--+---+--+-+--+++ I think someone's onto something though regarding the 'optimize' command. The above selection is actually a DELETE, and it results in about a 3% deletion of the table per day. This has been running for months (if not years) with no optimization. Is it possible that this table is so fragmented? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: This is confusing..?
Can you send show keys from campaign_t. I bet you can solve your problem by running analyze table. mysql show index from campaign_t\g +++---+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++---+--+-+---+-+--++-+ | campaign_t | 0 | PRIMARY |1 | acct_id | A | 1898 | NULL | NULL | | | campaign_t | 0 | PRIMARY |2 | site_id | A | 5621 | NULL | NULL | | | campaign_t | 0 | PRIMARY |3 | ref_id | A | 109627 | NULL | NULL | | | campaign_t | 0 | PRIMARY |4 | datestamp | A | 438511 | NULL | NULL | | | campaign_t | 1 | acct_id |1 | acct_id | A | 1898 | NULL | NULL | | | campaign_t | 1 | site_id |1 | site_id | A | 36 | NULL | NULL | | | campaign_t | 1 | ref_id|1 | ref_id | A | 54813 | NULL | NULL | | | campaign_t | 1 | datestamp |1 | datestamp | A | 76 | NULL | NULL | | +++---+--+-+---+-+--++-+ I have no idea how to interpret this.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Excluding records?
If I've got the following two tables:
CREATE TABLE password_log (
time_stamp int(11) unsigned NOT NULL default '0',
remote_host char(15) NOT NULL default '',
remote_user char(50) NOT NULL default '',
status smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (remote_host,remote_user,status),
KEY time_stamp (time_stamp),
KEY remote_user (remote_user),
KEY status (status)
) TYPE=MyISAM;
CREATE TABLE exclude_log (
ip_block char(15) NOT NULL default '',
PRIMARY KEY (ip_block)
) TYPE=MyISAM;
#
# Dumping data for table 'exclude_log'
#
INSERT INTO exclude_log VALUES ('152.163.188');
INSERT INTO exclude_log VALUES ('152.163.189');
INSERT INTO exclude_log VALUES ('152.163.206');
INSERT INTO exclude_log VALUES ('152.163.207');
INSERT INTO exclude_log VALUES ('195.93.64');
INSERT INTO exclude_log VALUES ('195.93.65');
INSERT INTO exclude_log VALUES ('195.93.66');
INSERT INTO exclude_log VALUES ('195.93.72');
INSERT INTO exclude_log VALUES ('195.93.73');
INSERT INTO exclude_log VALUES ('195.93.74');
INSERT INTO exclude_log VALUES ('205.188.208');
INSERT INTO exclude_log VALUES ('205.188.209');
And I want a list of everything in the 'password_log' table that doesn't
match up with any entry in the 'exclude_log' table, something along the
lines of:
select remote_user, substring_index(remote_host,'.',3) As ip_subnet from
password_log, exclude_log where remote_user != '-' and status=200 and
substring_index(remote_host,'.',3) != exclude_log.ip_block group by
ip_subnet order by remote_user, ip_subnet\g
+---+-+
| remote_user | ip_subnet |
+---+-+
| adamvernau| 207.79.8|
| amos | 24.53.232 |
| badmilk | 62.57.227 |
| [EMAIL PROTECTED] | 80.103.137 |
| beerbomb60| 12.80.11|
| BogusBob | 65.58.37|
| brendenm123 | 172.190.203 |
| brize | 217.39.73 |
| bruneau | 195.242.80 |
| chicken | 24.101.127 |
| cracking | 213.122.143 |
| DanielNoble | 172.151.183 |
| DESIO | 204.213.78 |
| diamond | 4.60.97 |
| dlgeo | 68.42.127 |
| ewing | 195.29.35 |
| fnadeau | 64.228.196 |
| frogman | 67.234.8|
| fujerome | 156.143.132 |
| geno6969 | 65.58.94|
| gravy01 | 81.86.119 |
| iftkharmaan | 62.255.64 |
| jamesz| 204.186.14 |
| jaysap| 12.235.160 |
| karak | 80.63.120 |
| kevin | 152.163.188 |
| kevin | 152.163.189 |
| kevin | 152.163.201 |
| kjelljanssonx | 213.66.154 |
| ksm70512 | 172.195.152 |
| leinad| 210.120.128 |
| leinad| 66.68.138 |
| lemurs| 24.60.185 |
| leolebr | 81.48.138 |
| Malakon | 24.186.21 |
| martisr | 217.39.29 |
| matglew | 81.98.84|
| mikeestela| 129.106.169 |
| Mirhos| 80.11.19|
| newyork | 62.134.74 |
| ordinary3 | 12.37.234 |
| pcomdh| 212.185.249 |
| pp-hosereed | 24.61.65|
| pp-lobeneath | 67.82.86|
| prodrifter72 | 66.75.124 |
| RbrtMackay| 81.77.108 |
| revrendpoe| 65.43.0 |
| rockey| 62.64.135 |
| rockey| 80.225.68 |
| stwgolfer | 64.221.53 |
| thebear | 205.188.208 |
| thebear | 205.188.209 |
| tooms | 63.225.249 |
| ulyanov | 12.5.196|
| WHATSUP | 172.173.81 |
| [EMAIL PROTECTED] | 80.143.42 |
| xmartyx | 68.5.149|
| xym180| 216.41.132 |
| zook10| 213.89.57 |
+---+-+
59 rows in set (0.03 sec)
As you can see, I still end up with records from the 'exclude_log'
table.. ?
-
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
Can someone explain this?
Dec 3rd, 2002 mysql select sum(trial_signups) from campaign_t where datestamp='20021203'\g ++ | sum(trial_signups) | ++ | 64 | ++ Dec 4th, 2002 mysql select sum(trial_signups) from campaign_t where datestamp='20021204'\g ++ | sum(trial_signups) | ++ |100 | ++ And a full detail of Dec 3rd shows the following (CORRECT): mysql SELECT site_id, sum(raws) As RawHits, sum(uniques) As UniqueHits, sum(trial_signups) As Sales FROM campaign_t WHERE datestamp = '20021203' AND datestamp = '20021203' GROUP BY site_id\g +-+-++---+ | site_id | RawHits | UniqueHits | Sales | +-+-++---+ | 1 | 37171 | 25137 |31 | | 2 |1645 | 1010 | 0 | | 3 |8590 | 6353 | 1 | | 4 | 729 |610 | 0 | | 5 | 2 | 2 | 0 | | 6 | 430 |180 | 0 | | 7 |2280 | 1460 |14 | | 8 |2680 | 1608 |14 | | 9 |3310 | 2349 | 1 | | 10 | 17341 | 7038 | 3 | +-+-++---+ Yet the same request for the next day drops a TON of records (FAILED): mysql SELECT site_id, sum(raws) As RawHits, sum(uniques) As UniqueHits, sum(trial_signups) As Sales FROM campaign_t WHERE datestamp = '20021204' AND datestamp = '20021204' GROUP BY site_id\g +-+-++---+ | site_id | RawHits | UniqueHits | Sales | +-+-++---+ | 1 |6231 | 3672 | 1 | | 2 | 143 | 96 | 0 | | 3 | 256 |128 | 0 | | 4 | 16 | 11 | 0 | | 6 | 9 | 9 | 0 | | 7 | 88 | 45 | 2 | | 8 |1801 | 1055 |11 | | 9 |2805 | 1979 | 2 | | 10 |2251 |669 | 0 | +-+-++---+ It's JUST the date '20021204'.. the rest of the dates (for the past 4 months) have been working fine. Any ideas? The simple fix is to write some code to detect if the start date equals the end date and modify the query, but why does it work for every other datestamp?!? Very weird. -- - Eric Anderson Wild Web Amateurs CyberIron Bodybuilding ICQ 3849549 http://www.wildwebamateurs.com http://www.cyberiron.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - 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
MySQL connect failure
I will occasionally get the following message in some of my cronjobs: Mysql-connect(database=mb_pw;host=localhost) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146) at /usr/home/members/filter/Filter.exe line 15 Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146) /usr/local/mysql/include/my_base.h defines error #146 as: my_base.h:#define HA_ERR_LOCK_WAIT_TIMEOUT 146 But I don't lock any of my tables, and it usually happens for two different databases. Is there any kind of general locking that might take place? - 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
Mysql SELECT question (LEFT JOIN?)
Assume two tables: CREATE TABLE block_ip ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', PRIMARY KEY (remote_addr), KEY datestamp (datestamp) ) TYPE=MyISAM; CREATE TABLE brute_force ( datestamp int(11) NOT NULL default '0', remote_addr char(15) NOT NULL default '', remote_user char(35) NOT NULL default '', KEY remote_addr (remote_addr), KEY datestamp (datestamp), KEY remote_user (remote_user) ) TYPE=MyISAM; Contents of the 'brute_force' table (remote_addr): 1.2.3.4 2.3.4.5 3.4.5.6 4.5.6.7 5.6.7.8 6.7.8.9 Contents of the 'block_ip' table (remote_addr): 2.3.4.5 4.5.6.7 Can someone help me with the query that will select all the 'remote_addr' rows from 'brute_force' that are NOT in the 'block_ip' table? Something like: select brute_force.* from brute_force, block_ip where brute_force.remote_addr != block_ip.remote_addr maybe? I have a feeling it's some sort of left join, and I was never very good at those. :-/ - 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: Crashing under high load
Well, most programs, in my experience, will keep running, even if load is 200, they just run slowly since they aren't getting processor load. How's the memory? Typically things (especially Mysql) croak if they bonk on memory - if you're into the swap on high load, you're screwed. -- - Eric Anderson Wild Web Amateurs CyberIron Bodybuilding ICQ 3849549 http://www.wildwebamateurs.com http://www.cyberiron.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - 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: MySQL hardware concerns
On Tue, 30 Jul 2002, Adam Nelson wrote: Seems to me like a better architecture might be: N apache servers with mysql clients 1 Master Mysql Server 1 Slave Mysql Server/admin/backup server If you have 5 slave servers (one on each apache server), that would cause much more traffic on the internal network than each apache machine just being a client and running the queries on the master server. We do the kind of traffic you're talking about with the above architecture and it works great (plus you're not administering N+1 mysql servers with all their security/maintenance issues). Having the database totally removed from the internet is definitely safer. I highly recommend the DL360 G2 from compaq/HP with dual 1.4Ghz processors and 1 GB ram and raid 1 scsi drives. This machine is more expensive than the generic equivalent, but it's 100% worth it. If you're looking to double or triple your traffic, you may need to think about a dl380 with quad processors (but that's probably overkill Uhh.. either way, why wouldn't you just double-NIC it (one public NIC, one private NIC) and run a private network to keep the database off the Net? And if you '--skip-networking' the mysql slaves aren't exposed to the Net either (rightly so). Personally, I think having the data pushed out to the slave servers is the way to go, since if something happens to the master server (even a reboot) the slaves will continue to serve pages because of the local data. -- - Eric Anderson Wild Web Amateurs CyberIron Bodybuilding ICQ 3849549 http://www.wildwebamateurs.com http://www.cyberiron.com San Diego, CA[EMAIL PROTECTED] [EMAIL PROTECTED] - You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - 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: SELECT .. OR from multiple tables
How about a UNION query...? Too bad MySQL doesn't support them yet ;) Oops...sorry...I see it is on the TODO list. Would a MERGE table not achieve the same result as a UNION query? It appears that a MERGE table acts like a VIEW. Eric if your Current and Temporary tables have identical structure, you may consider a MERGE table as an option. Unfortunately, they are not similar. I appreciate everyone's assistance - I just created two quick SQL SELECTS testing whether or not the value was in either table and I'm over it. As always, I was looking to minimize SQL calls but in this case it's not a big deal - it's not a high-volume transaction. Thanks everyone for all your help and suggestions. -- Eric Anderson Online Net-Entertainment, Inc CyberIron Bodybuilding ICQ 3849549 http://www.on-e.comhttp://www.cyberiron.com [EMAIL PROTECTED]San Diego, CA[EMAIL PROTECTED] ..and then my doctor said my nose wouldn't bleed so much if I just kept my finger outta there! -- Ralph Wiggum You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - 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
SELECT .. OR from multiple tables
I've got two tables, Current and Temporary, Current has a row with login='keric', Temporary doesn't. The following query: mysql select * from Current, Temporary where Current.login='username' or Temporary.login='username' - \g Empty set (0.01 sec) obviously doesn't work. I want to know if that row exists in either table in one SQL call. Maybe it's just me today.. Anyone? -- Eric Anderson Online Net-Entertainment, Inc CyberIron Bodybuilding ICQ 3849549 http://www.on-e.comhttp://www.cyberiron.com [EMAIL PROTECTED]San Diego, CA[EMAIL PROTECTED] ..and then my doctor said my nose wouldn't bleed so much if I just kept my finger outta there! -- Ralph Wiggum You've got the brain and talent to do anything you want, and when you do, I'll be right there to borrow money from you! -- Bart Simpson - 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: SELECT .. OR from multiple tables
Your query will work in every case, EXCEPT when either Current or Temporary has 0 rows, because then there's nothing to join. I'm not sure if doing a join is a clean way of doing this though. If you know that Current will never be empty (but temporary might be), then this query would work: SELECT * FROM current LEFT JOIN temporary ON 1=1 WHERE current.login = 'keric' OR temporary.login = 'keric' That won't work if Current is empty. There's got to be a better way of doing this though... anyone else want to take a crack at this problem? Sorry, the most likely scenario is that neither table has the value, but either Current or Temporary could have it, though not both at the same time. - 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
One-way data transfer
Let's say I've got a database on primary server (cluster1) and periodically I need to purge data from it to a remote archive database. What's the best way? I thought about replication, but I don't want the data to stick around in the cluster1 database. Is a dump via SSH the best/easiest way? - 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
Mysql and NAS?
I've done quite a bit of searching but I've not run across any experiences with multiple Mysql servers running on separate computers using the same data files off a NAS server. Anyone? - 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
