mysqldump JOIN?
I would like to do the following: mysqldump -w users.user_id=enews.user_id sotx users c:/enews_users.sql Ideally this would dump all records in table users where the user_id field value is also present in the enews table. Is this possible? TIA, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump JOIN?
I've tried about 20 different variations with no luck except when i modify the where statement to use only one table. Then it works fine but it's not the results i want. I was suspecting exactly what you said: you can only refer to one table, and the first one you specify at that. I was trying to avoid creating a temp table and all that as this is a dump that i'll have to do a lot. Just thinking off the top of my head: I could probably write a batch file that executes the necessary commands to create the temp table, dump the contents, and then remove the temp table. I'll see if it works. Thanks, Rhino. -dan -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 5:58 PM To: [EMAIL PROTECTED] Subject: Re: mysqldump JOIN? According to the mysqldump article in the manual - http://www.mysql.com/doc/en/mysqldump.html - you can select specific rows to dump with the --where or -w options. I've never tried making the where clause refer to a different table; I suspect from the examples given that you can only refer to the same table. If you haven't given it a try yet, try it now and see if it works. If you have tried it and it doesn't accept a --where that refers to a second table, you could try creating a temporary table, copying the desired rows into it, then doing mysqldump on the temporary table. I can't promise this will work but it should be easy enough to try. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 6:15 PM Subject: mysqldump JOIN? I would like to do the following: mysqldump -w users.user_id=enews.user_id sotx users c:/enews_users.sql Ideally this would dump all records in table users where the user_id field value is also present in the enews table. Is this possible? TIA, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUG?
Arithmetic results in a value of zero but mysql is treating the value as 0. This is reproducible in a lot of different ways. Below is a pretty clear example. mysql select version(); +---+ | version() | +---+ | 4.0.17-max-nt | +---+ 1 row in set (0.00 sec) mysql create table t (a decimal(12,2) not null, b decimal(12,2) not null, c decimal(12,2) not null); Query OK, 0 rows affected (0.72 sec) mysql insert into t values (260.22,18.81,279.03); Query OK, 1 row affected (0.00 sec) mysql select a+b-c from t where a+b-c = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y = 0; Empty set (0.00 sec) mysql select a+b-c as y from t having y 0; +--+ | y| +--+ | 0.00 | +--+ 1 row in set (0.00 sec) mysql select a+b-c from t where a+b-c 0; +---+ | a+b-c | +---+ | 0.00 | +---+ 1 row in set (0.00 sec) Anybody? Thanks, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Memory Problems on a G5/OSX/MySql4.0.17
So should we always use InnoDB over BerkeleyBD? I was under the impression Berkeley was faster and better at handling transactions. Dan -Original Message- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 11:01 AM To: [EMAIL PROTECTED] Subject: Re: Memory Problems on a G5/OSX/MySql4.0.17 I don't think there would be any benefit to using InnoDB, at least not from a transaction point of view For the longest time I was reading the books and listening to the experts and all I was hearing is InnoDB is great because it handles transactions. Having little interest in transactions per se I pretty much started tuning things out whenever people mentioned InnoDB. One day when talking to some MySQL AB folks they asked why I wasn't using InnoDB... I kind of looked at them blankly and replied that I don't need transactions, and they looked back as if I was mad. Turns out InnoDB is far better at handling large databases than MyISAM, we had a massive (and I do mean massive) increase in performance just by switching to InnoDB. Uses a little more disk space, but it's worth it, and with a 5GByte database and a G5 server you have room to spare, even if you only got the smaller disks. InnoDB is a major thing for us now, everything is InnoDB. If an Engineer complains something they have done is running slowly it usually turns out to be they made some new thing and didn't make the table InnoDB. The fix is easy and quick. I also suspect that you could do away with that nightly table repair that ties up the machine for hours at a time if you were using InnoDB. We have 4 G5 towers serving MySQL for us, all DP2GHz machines with 4GBytes of RAM. If your data is changing rapidly, as it appears from your samples most pages include some sort of insert, you will have limited benefit from the Query cache - every time a table receives any type of change to it's data any queries in the query cache that use that table are dumped. In February we are adding to the mix with 2 G5 XServes... These are for new projects, the current servers are handling their loads fine. On the Disk side we got the dual 250GBytes and mirrored them for redundancy, speed isn't an issue as far as we can tell. We chose to replace our old database servers with G5s. The old machines were quad processor Sun boxes, and one was an 8 CPU machine. The G5s left them all for dead in terms of performance, although I'd prefer a couple of extra processors, something inside me still feels better knowing that when a process goes AWOL it's not holding up 50% of the server's resources. The Application servers are still typically Sun, although new ones won't be. We average about 140 Queries per second per machine (of course the load isn't that well distributed... but it gives you an idea), and typical high points are about 400 - 500 qps on any given machine without stressing the machines (replication catch up can see 1500 - 2000 queries per second, but that's not so common and of course is mostly inserts). Before we did the upgrade to 4.0.17 during last Friday's maintenance window we were over 1.5 billion queries total for the 28 days the machines had been up. So.. My tips for you: 1) Consider a switch to InnoDB, the performance hit was dramatic, and it's about SO much more than transactions (which we still don't do)! 2) Drop the query cache to something more practical, a gigabyte is fine if your data is static, if it's not it's way too much. We use 128MBytes and typically have about a 30% hit rate on the Query cache and the busiest server is showing 80MBytes unused memory in the query cache and a 41% hit rate, and our databases take about 40G of disk space. Remember having a big query cache doesn't help if it's mostly sitting unused (in fact if ours are still sitting with 80M free in a week I'll drop all of them 64MBytes). 3) Give lots of memory to InnoDB, I'll share my settings below. 4) Take most of the non InnoDB memory settings and drop them down real low, InnoDB does well on it's own and if you convert all tables you don't need to leave much in the way of resources for MyISAM. 5) Turn on and use the slow query log (and if need be change the time needed to qualify as a slow query, the default 10 seconds is a lifetime). You may not code the queries yourself, but you can identify the queries that are causing problems and from there you can advise the client on changes to the database structure (indexes etc) or at least tell him exactly what the problem queries are. 6) Go get MyTOP from Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mytop/ - personally I like version 1.3 but that may just be what I am used to... You may not be able to control the coding part but you can at least monitor the server and see what it's up to and quickly and easily see problems. 7) If you decide to stay with MyISAM and not InnoDB then you will want as much memory as you can in the Key Buffer while leaving some space in the sort/read/join
question regarding MAX() and INSERT
I would like to do the following:
INSERT INTO tt (a,i) VALUES ('text',MAX(i)+1);
This doesn't work b/c MAX() isn't allowed here. Does
anyone know how I can still achieve this with ONE
query?
Thanks.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: web hosting quesiong (slightly off topic)
What?!?! That wasn't called for. I am in no way affiliated with them and do not stand to gain anything if anyone would choose to host with them. I've worked with over a dozen ISPs and SonicCommerce is my favorite. That's it, plain and simple. -Original Message- From: Jeremy Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 9:29 AM To: [EMAIL PROTECTED] Subject: RE: web hosting quesiong (slightly off topic) snip i really love 'em. how often do you hear that about an ISP? /snip Usually everytime someone is looking for an ISP and an owner/person-with-something-to-gain happens to sniff out the opportunity. -J -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:33 AM To: [EMAIL PROTECTED] Subject: RE: web hosting quesiong (slightly off topic) www.soniccommerce.com They're awesome, all linux, and expect you to grow over time so they make upgrading your plan simple. i really love 'em. how often do you hear that about an ISP? -dan -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: web hosting quesiong (slightly off topic) I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Issue with DATE_FORMAT() call returning wrong year
Try %Y instead of %X -Original Message- From: PAUL MENARD [mailto:[EMAIL PROTECTED] Sent: Monday, January 05, 2004 10:08 AM To: [EMAIL PROTECTED] Subject: Issue with DATE_FORMAT() call returning wrong year Hello all, I have a query that for some reason is now returning the incorrect year. First here is my server configuration. Windows 2003 Advanced server MySQL version 4.0.15-nt Here is my SQL statement: SELECT DATE_FORMAT(E.DateTime, '%X-%m-%d %h:%i:%s %p' ) AS DateTime, E.SendCount AS SendCount, E.TransactionStatus AS TransactionStatus, E.Message AS Message, TS.StatusDescription AS StatusDescription, TS.ErrorWarningInd AS ErrorWarningInd FROM errlog As E LEFT JOIN TransStatusTable AS TS ON E.TransactionStatus=TS.TransStatusCode WHERE AccountName='INSTILL000' AND Filename='SYSCO076_20040103125040.850' AND AccountScheduleDetailID=331 ORDER BY SendCount, DateTime, TransactionStatus The first column returned is the date. The values returned are such as '2003-01-03 02:50:16 PM' If I replace the DATE_FORMAT() call 'DATE_FORMAT(E.DateTime, '%X-%m-%d %h:%i:%s %p' ) AS DateTime' with a simple 'E.DateTime AS DateTime'. the returned value is now correct but the format is not. I have run this SQL statement from the mysql command line interface. I did initially use phpMyAdmin to see this. I just wanted to make sure this was not a PHP issue. Any thoughts? Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Subtracting date fields
Matt, Good point, although he didn't specify what version he was using so I assumed a newer one. Perhaps a disclaimer should have been included? Anyways, Kenneth, if you're using an older version try this: SELECT id, TO_DAYS(firstdate) - TO_DAYS(postdate) AS diff FROM calendar Dan -Original Message- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 4:42 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Subtracting date fields Dan, DATEDIFF() only works in MySQL 4.1.1+. RTFM! ;-) Matt - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 2:10 PM Subject: RE: Subtracting date fields Kenneth, try SELECT id, DATEDIFF(firstdate, postdate) AS diff FROM calendar RTFM! hope that helps, dan -Original Message- From: Kenneth Letendre Sent: Saturday, January 31, 2004 1:51 PM Subject: Subtracting date fields Hello, I'm trying to get the difference (in days) between dates stored in two date fields. My query: SELECT id,(firstdate- postdate) AS diff FROM calendar This works fine if the two dates are in the same month, but not otherwise. MySQL appears to be treating the two dates as base-10 integers rather than dates. E.g.: 2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) = 8876 How do I get MySQL to treat these date fields as date fields in this case? Thanks, Kenneth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Subtracting date fields
Kenneth, try SELECT id, DATEDIFF(firstdate, postdate) AS diff FROM calendar RTFM! hope that helps, dan -Original Message- From: Kenneth Letendre [mailto:[EMAIL PROTECTED] Sent: Saturday, January 31, 2004 1:51 PM To: [EMAIL PROTECTED] Subject: Subtracting date fields Hello, I'm trying to get the difference (in days) between dates stored in two date fields. My query: SELECT id,(firstdate- postdate) AS diff FROM calendar This works fine if the two dates are in the same month, but not otherwise. MySQL appears to be treating the two dates as base-10 integers rather than dates. E.g.: 2004-01-07 (20,040,107) - 2003-12-31 (20,031,231) = 8876 How do I get MySQL to treat these date fields as date fields in this case? Thanks, Kenneth -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
