Re: order of items in a WHERE...IN clause
Andrew Martin wrote: Hello, Is it permissible to order a clause such that the search term is the first item (in the clause)? standard: field1 IN (123, 654, 789) in question: 123 IN (field1, field2, field3) I am interested to know if the optimizer treats this any differently if anybody can shed any light on it (except for the obvious difference in the above queries!) Thanks, Andy Both are valid syntax where 1 is returned if the expression is equal to any of the values in the list. I can't see the optimizer treating these any differently. Thanks, Gary M. Josack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure host and user name for non static ip address
Sign up for dyndns.com or some other similiar service. Create permissions to the domain and run a script that updates you IP with dyndns whenever it changes. (such scripts already exist). As far as 'username'@'%' with no password with SELECT, INSERT, UPDATE and DELETE privileges... worst idea ever in my opinion. Anyone that knows your server/username can get in query and delete records. Stephen Sunderlin wrote: QUESTION: What are the most secure permissions settings for administrator access to connect to my server without using a static IP address? MY ISP changes my DSL ip address almost daily so when I log on to MySQL Administrator with 'myusername'@'currentipaddress' using password 'mypassword' I have grant permission to the new ip address. I also have and account: 'username'@'%' with no password with SELECT, INSERT, UPDATE and DELETE privileges only for general users for this membership site. Are there any security issues with this? Any input or direction for informed reading on the issue would be appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: funky characters in columns
Try:
replace(replace(dealerLong, '\n', ''), '\r', '')
Jay Blanchard wrote:
I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;
mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
|.9040
++
(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.
For troubleshooting purposes, once I had narrowed down the problem
column I did the following
mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
| |
+--+
You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.
update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';
has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.
Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Daniel Kasak wrote: Greetings. I've just returned from holidays, and it seems that all but 1 ibdata file ( there were 10! ) have been deleted by a co-worker. He apparently was able to delete them with nautilus ( he was looking to reclaim some space and these were 1GB files each ... and yes, the Trash was emptied as well ). I would have assumed these would be protected from being deleted while mysql was up, but this clearly wasn't the case ( or was it? perhaps they are still around? ). The astonishing thing is that mysql doesn't seem to care about this. It happened 1 week ago, and no-one has complained about any database-related problems since. I've got a nightly backup script which does a 'mysqldump' on each database, and then restarts the server ( which gives me daily transaction logs ). Tonight, I obviously plan on doing a complete reinstall from an old backup, and running the transaction logs. But, just out of curiosity ... what the hell is going on? Why is mysql not complaining bitterly, crashing, and worse? Note the lack of ibdata1 ... ibdata9 screamer mysql # ls -l total 2885424 drwx-- 2 mysql mysql 480 Nov 26 2006 Assets drwx-- 2 mysql mysql 168 Sep 21 14:57 EPricing drwx-- 2 mysql mysql 12512 Sep 25 21:04 NUS drwx-- 2 mysql mysql 656 Nov 26 2006 Timekeeper drwx-- 2 mysql mysql 1216 Jul 4 08:46 dbmail drwx-- 2 mysql mysql 648 Aug 13 14:18 dspam drwx-- 2 mysql mysql 16584 Sep 18 09:32 ebills drwx-- 2 mysql mysql 824 Sep 24 14:45 energy -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Sep 26 09:08 ib_logfile1 lrwxrwxrwx 1 root root 15112077312 Sep 26 09:08 ibdata10 drwx-- 2 mysql mysql 1848 Nov 26 2006 mysql drwx-- 2 mysql mysql 648 Dec 20 2006 roundcubemail drwx-- 3 mysql mysql 6240 Sep 26 08:54 sales -rw-rw 1 mysql mysql 239845598 Sep 14 20:40 screamer-bin.000201 -rw-rw 1 mysql mysql 591774120 Sep 17 20:44 screamer-bin.000202 -rw-rw 1 mysql mysql 289226895 Sep 18 20:38 screamer-bin.000203 -rw-rw 1 mysql mysql 281415790 Sep 19 20:36 screamer-bin.000204 -rw-rw 1 mysql mysql 286245722 Sep 20 20:37 screamer-bin.000205 -rw-rw 1 mysql mysql 331061564 Sep 21 20:43 screamer-bin.000206 -rw-rw 1 mysql mysql 411650198 Sep 24 20:38 screamer-bin.000207 -rw-rw 1 mysql mysql 309576301 Sep 25 20:37 screamer-bin.000208 -rw-rw 1 mysql mysql 140007700 Sep 26 09:08 screamer-bin.000209 -rw-rw 1 mysql mysql 3520 Sep 25 20:37 screamer-bin.index -rw-rw 1 mysql mysql 60432327 Sep 26 09:06 screamer-slow.log drwx-- 2 mysql mysql80 Sep 20 11:16 test screamer mysql # The only way I can explain the fact that things are still working is that these files are in fact *not* deleted, and are still in use by mysql, but not visible to anything else. But that's ridiculous as well. So what's going on? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Daniel Kasak wrote: On Tue, 2007-09-25 at 19:27 -0400, Gary Josack wrote: Did the space become available when deleted? try: lsof | grep deleted see if they're still running in memory. if so you might be able to save them. Thanks for the quick response :) They're there: mysqld 5460 mysql 10uW REG8,3 1073741824 761001 /root/.Trash/ibdata2 (deleted) mysqld 5460 mysql 11uW REG8,3 1073741824 6852461 /root/.Trash/ibdata3 (deleted) mysqld 5460 mysql 12uW REG8,3 1073741824 7376938 /root/.Trash/ibdata4 (deleted) mysqld 5460 mysql 13uW REG8,3 1073741824 6859981 /root/.Trash/ibdata5 (deleted) mysqld 5460 mysql 14uW REG8,3 1073741824 7376491 /root/.Trash/ibdata6 (deleted) mysqld 5460 mysql 15uW REG8,3 1073741824 7376500 /root/.Trash/ibdata7 (deleted) mysqld 5460 mysql 16uW REG8,3 1073741824 1369981 /root/.Trash/ibdata8 (deleted) mysqld 5460 mysql 17uW REG8,3 1073741824 7377058 /root/.Trash/ibdata9 (deleted) ( sorry about the text wrapping thing ) How do I recover them, and do you think this is wise? At this point, I still think it might be a better idea to do a complete reinstall / restore / transaction log run. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ouch! ibdata files deleted. Why no catastrophe?
For future reference. The files do actually continue to be written to. I experience this all the time when people delete logs files and space keeps filling up. Daniel Kasak wrote: On Tue, 2007-09-25 at 23:11 -0400, Gary Josack wrote: Well if you can stop all instances of writes to the databases you should be able to recover them. Each file is going to be in /proc/5460/fd/10-17 the file number corresponds to the fd you see in lsof output ex: cp /proc/5460/fd/10 ibdata2 This is still risky and i reccomend you get a dump immediately. As soon as you restart mysql those files are gone forever. Yes I've been reading / thinking more about this, and I've decided against it. There's no real guarantee that MySQL will have written everything to the ibdata files at the point when I make a snapshot of them ( at least as far as I know ). Since the nightly backups are still working perfectly, I'll just shut down MySQL when the backup completes, delete /var/lib/mysql, and import the backup. That sounds a lot safer than anything else at the moment. Thanks again for your response. At least I learned about lsof and recovering deleted files :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding empty feilds
Stephen Sunderlin wrote: I executed an insert...select and some empty fields were inserted into a table. I'm trying to delete these empty fields but a look up for: FIELD = '' FIELD = 'null' FIELD = '0' FIELD = '[SPACE]' Returns nothing. What should I be looking for to delete these empty fields? Thanks. have you tried: WHERE field IS NULL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding empty feilds
NULL does not equal , , null, 0, 0 or any other variation. Those are all values. NULL is the lack of a value. IS NULL and IS NOT NULL are operators specific for working with NULL values. Stephen Sunderlin wrote: I was able to delete them using the CreateDate timestamp. For future reference what is the difference between = and is if I may ask. Thanks. -Original Message- From: Gary Josack [mailto:[EMAIL PROTECTED] Sent: Saturday, September 22, 2007 1:20 PM To: Stephen Sunderlin Cc: mysql@lists.mysql.com Subject: Re: Finding empty feilds Stephen Sunderlin wrote: I executed an insert...select and some empty fields were inserted into a table. I'm trying to delete these empty fields but a look up for: FIELD = '' FIELD = 'null' FIELD = '0' FIELD = '[SPACE]' Returns nothing. What should I be looking for to delete these empty fields? Thanks. have you tried: WHERE field IS NULL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Editing fields in bulk
Brian Dunning wrote: I have a column where I need to replace all instances of the text US-Complete (contained within a long sentence) with US Complete. There are probably 50 or 100 of them. I'm really scared to do it since I can't risk screwing up that column - what's the correct syntax? --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] mysql select * from replacetest; +++ | id | reptest| +++ | 1 | this is a test US-Complete wii | | 2 | look US-Complete is here | | 3 | Fun test | +++ 3 rows in set (0.00 sec) mysql update replacetest set reptest=replace(reptest, 'US-Complete', 'US Complete') where reptest like '%US-Complete%'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql select * from replacetest; +++ | id | reptest| +++ | 1 | this is a test US Complete wii | | 2 | look US Complete is here | | 3 | Fun test | +++ 3 rows 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: Database architecture and security
I'd never have a separate database for everyone or even a separate table
for everyone. Here's a rough idea of how I'd do it
mysql CREATE TABLE customer (
- `custid` INT NOT NULL AUTO_INCREMENT,
- `lastname` VARCHAR(25) not null,
- `firstname` VARCHAR(25) NOT NULL,
- PRIMARY KEY(custid)
- );
Query OK, 0 rows affected (0.03 sec)
mysql CREATE TABLE address (
- `addressid` INT NOT NULL AUTO_INCREMENT,
- `custid` INT NOT NULL,
- `address` VARCHAR(100) NOT NULL,
- `city` VARCHAR(50),
- `state` CHAR(2) NOT NULL,
- `zip` MEDIUMINT(5) NOT NULL,
- PRIMARY KEY(addressid)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM customer;
++--+---+
| custid | lastname | firstname |
++--+---+
| 1 | Bolton | Mike |
| 2 | Vader| Darth |
++--+---+
2 rows in set (0.00 sec)
mysql INSERT INTO address (custid, address, city, state, zip) VALUES
- (1, '123 house drive.', 'Davie', 'FL', 33314),
- (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
- (2, 'The Death Star', 'SPACE', 'NA', 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * FROM address;
+---++---+-+---+---+
| addressid | custid | address | city| state | zip |
+---++---+-+---+---+
| 1 | 1 | 123 house drive. | Davie | FL| 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL| 33521 |
| 3 | 2 | The Death Star| SPACE | NA| 6 |
+---++---+-+---+---+
3 rows in set (0.00 sec)
mysql SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+--+---+---+-+---+---+
| lastname | firstname | address | city| state | zip |
+--+---+---+-+---+---+
| Bolton | Mike | 123 house drive. | Davie | FL| 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL| 33521 |
| Vader| Darth | The Death Star| SPACE | NA| 6 |
+--+---+---+-+---+---+
3 rows in set (0.01 sec)
mysql SELECT address, city, state, zip FROM customer JOIN address USING
(custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+---+-+---+---+
| address | city| state | zip |
+---+-+---+---+
| 123 house drive. | Davie | FL| 33314 |
| 54325 awesome way | Sunrise | FL| 33521 |
+---+-+---+---+
Now each customer/person can have multiple addresses listed.
Jason Pruim wrote:
Hi Everyone,
Just had a quick question about a database I'm working on.
I am planning on having the database open to customers of mine to
store their mailing addresses on-line, and be able to manage the records.
Is it safe, to have 1 database with lots of tables? Or am I safer
setting up separate databases for everyone?
I should mention, no one will be accessing the database directly,
it'll be through a web interface and php to display it.
Any info would be greatly appreciated!
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New Value From Concatenated Values?
Kebbel, John wrote:
Is there a way to update a table so that a column's values can be changed to a concatenation of two other column values? For instance, something like ...
UPDATE TABLE tablename SET colA = colB.colC;
Is this what you're looking for?
mysql create table concattest (
- field1 varchar(25),
- field2 varchar(25),
- field3 varchar(25)
- );
Query OK, 0 rows affected (0.07 sec)
mysql insert into concattest (field2, field3) values ('hi', 'there');
Query OK, 1 row affected (0.00 sec)
mysql select * from concattest;
++++
| field1 | field2 | field3 |
++++
| NULL | hi | there |
++++
1 row in set (0.00 sec)
mysql update concattest set field1 = concat(field2, field3);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from concattest;
+-+++
| field1 | field2 | field3 |
+-+++
| hithere | hi | there |
+-+++
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: Find record between 10 minutes
[EMAIL PROTECTED] wrote: Hi, I have a table with records dated 01-01-2007 00:00:00 to 01-02-2007 23:59:59. What I want to do is grab 1 random record in every 10 minutes between the date. Please help me. Regards, Willy Does it really need to be random? This works from me: mysql select `date` from table where mod(unix_timestamp(`date`), 600) = 0 limit 10; +-+ | date| +-+ | 2007-01-01 00:00:00 | | 2007-01-01 00:10:00 | | 2007-01-01 00:20:00 | | 2007-01-01 00:30:00 | | 2007-01-01 00:40:00 | | 2007-01-01 00:50:00 | | 2007-01-01 01:00:00 | | 2007-01-01 01:10:00 | | 2007-01-01 01:20:00 | | 2007-01-01 01:30:00 | +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recommend a good database book
Jonathan Horne wrote: i finally have a real need that i need to sit down and learn the basics of databases. i need to learn to create a database that i can use to track assets at my company, and have it be readable/updatable from a web interface (and hopefully also export data to .csv). can someone recommend a book that i might learn this from? ill be starting from total db-novice. thanks in advance, Do you want to learn database design theory in general or learn how to use MySQL? The MySQL 5.0 Certification Study Guide is a really good book for getting started with MySQL and covers almost everything you'll need to know. As far as theory... I think Database Design For Mere Mortals - A Hands-On Guide To Relational Database Design, 2nd Edition is a pretty good starting point. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
Jason Pruim wrote: First line of my .csv file is: First,Last,Add1,Add2,City,State,Zip,Date,Xcode,Reason DESCRIBE is: mysql describe test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | First | varchar(20) | YES | | NULL| | | Last | varchar(20) | YES | | NULL| | | Add1 | varchar(50) | YES | | NULL| | | Add2 | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(10) | YES | | NULL| | | Zip| varchar(20) | YES | | NULL| | | XCode | varchar(20) | YES | | NULL| | | Reason | varchar(50) | YES | | NULL| | | Date | varchar(20) | YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.09 sec) I've also tried adding the filed names at the end of my load data command but that didn't help... As it sits right now this is the command I'm attempting to use: mysql LOAD DATA LOCAL INFILE '/volumes/raider/aml.master.8.6.07.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; Which displays this: Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 if I remove the IGNORE 1 LINES; from the end then I get this added into the table: A. DREW | Last | Add1 | Add2 | City | State | Zip | Date | Xcode | Reason Which is a combination of the first address and the column names. On Aug 8, 2007, at 3:34 PM, Gary Josack wrote: Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning All Rows That Repeat
John Kopanas wrote: I want to be able to return all rows that have one or more other rows with the same customer_number and job_number. So for instance. If their are two jobs in my jobs table with a customer_number = '0123' and job_number ='12' then I want both of those jobs to return one right after another so I can compare their other fields. And I want to run a query once a day over the whole table to see if their are any repeats. Does anyone have a clue how to do this? Thanks :-) Your Friend, John Not sure how to do this with one command right now because i'm tired and cant think straight but heres one way: create temporary table tempjobdupes as select customer_number, job_number from jobs group by customer_number, job_number having count(*) 1; select * from jobs where (customer_number, job_number) = any (select * from tempjobdupes); drop table tempjobdupes; theres got to be another way but the subqueries i'm trying aren't working how i want. If i think of anything else i'll try again. Let me know how this works for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Returning All Rows That Repeat
John Kopanas wrote: Does it makes sense that on a table of 100,000 rows that my DB is crapping out with the following query? SELECT * FROM jobs GROUP BY customer_number, job_number HAVING count(*) 1 ORDER BY customer_number; :-) On 7/29/07, John Trammell [EMAIL PROTECTED] wrote: From: John Kopanas [EMAIL PROTECTED] Subject: Returning All Rows That Repeat I want to be able to return all rows that have one or more other rows with the same customer_number and job_number. So for instance. If their are two jobs in my jobs table with a customer_number = '0123' and job_number ='12' then I want both of those jobs to return one right after another so I can compare their other fields. And I want to run a query once a day over the whole table to see if their are any repeats. One way would be a self-join, e.g.: SELECT * FROM mytable t1, mytable t2-- same table twice WHERE t1.customer_number = t2.customer_number-- with same cust. no. AND t1.job_number = t2.job_number-- and same job no. AND t1.id t2.id;-- but the records are distinct INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. Really depends on how big the table is and whether it's properly indexed. I believe group by sorts for you so you shouldn't have to put that order by clause. I tested this on a table with over a million records and over 2.8 gigs worth of data and it was fairly quick so maybe you bad hardware or improper config settings. there's really a lot of things it could be. What storage engine are you using? What is the output of show create table jobs;? What settings do you have specified in your my.cnf? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb to be removed? and...
Christian Parpart wrote: Hi all, recently someone said to know alot about mysql told us that InnoDB is about to be removed from the mySQL server. however, InnoDB seems to be the fastest storage engine in our case, as myisam take a hell longer to insert new rows e.g. so is it true, that innodb is to be removed? (coudn't find any trace in the net yet) and what about potential future storage engines in mysql, or will there be myisam as the one and only feature blown one? Thanks in advance, Christian Parpart. One word. Falcon ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Red Hope wrote: I'm curious about one thing. When I go into MySQL folder on the hard drive. I go into the 'bin' folder, should there be an .exe program called mysqldump? or not? Lillian --- Carlos Proal [EMAIL PROTECTED] wrote: Yep, good for you, welcome to the real world You are changing the prompt but are still inside the dbms, you need to get out, because mysqldump is an application (.exe file) not a sql command, ie Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.0.41-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysql quit Bye D:\lillianmysqldump -u root -ppassword test test.sql; -- but maybe after the quit, the window will close, if that happens, open a new command prompt from start - programs - accesories -- command prompt and then run mysqldump Carlos Red Hope wrote: Hey y'all, I use charming Windows XP on here. I've taken database classes but lucky for me we never used *real* MySQL. Below I typed up exactly what I put into the MySQL prompt and this is what it kicks back to me. Lillian mysql mysql \R shell PROMPT set to 'shell' shell shellmysqldump -u root -ppassword test test.sql; ERROR 1064 4200: You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -ppassword test test.sql' at line 1 --- Carlos Proal [EMAIL PROTECTED] wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mailp=summer+activities+for+kidscs=bz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mailp=graduation+giftscs=bz As far as i know there SHOULD be a mysqldump.exe in your mysql bin folder. On windows it should be: Start, Run, Type cmd, Enter At the Command Prompt cd to your MySQL directory. For Example: cd C:\mysql\bin, Enter then mysqldump -u root -ppassword test test.sql, Enter That will place that .sql file in that directory for now which you can move as you please through Explorer. You do NOT want to run this from the MySQL client. mysqldump is a completely separate program that is run from the Operating Systems command line. I hope this helps. if not, just keep trying. you are very close. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL LINKING TABLE Command
Thufir wrote: SELECT product_name, customer.name, date_of_sale FROM `sales` , product, customer WHERE product.product_id = sales.product_id and customer.customer_id = sales.customer_id LIMIT 0, 30 The above SQL command links three tables and display the required result. The tables are linked by their ID fields. http://www.plus2net.com/sql_tutorial/sql_linking_table.php how is this different/better than a many-to-many, such as http://www.plus2net.com/sql_tutorial/sql_inner_join.php? Isn't that a better way of doing the same thing? Or, not? thanks, Thufir Maybe i'm crazy and it's just late but i'm pretty sure what you have there IS an inner join. Inner join is the default join type if you don't explicitly specify the join type. If you do specify the join type you'd have to use ON or USING. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
