Finding Data in One of Two Tables
I'm using this query in a Perl program: SELECT Distinct x.Search FROM $source.Searches AS x LEFT JOIN searching.Status AS s ON x.Search=s.Search AND s.Source='$source' WHERE x.RedoTime'$now' AND s.Search IS NULL This program runs other programs that do internet searches. I have different sources (stored in $source, of course), and source has it's own DB with a table, Searches. Each row of searches describes a different search that can be done and each search has a name, which is stored in the Searches column (within the Searches table, so, yes, I use that name for a table and a column). When a search is being executed, an entry is placed in searching.Status, with one row in that table showing the status of the search. So if I have a source named alpha and searches named one and two and the system is executing the search one, not only is there a row in alpha.Searches describing one in depth, but there is a row in searching.Status describing the progress with one. When each search is done, the RedoTime is set so it's easy to see when it needs to be executed again. What I want to do is get a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time). From what I've read, the query above should do it, but I have this nagging feeling I've done something wrong. Will that query pick up all rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status? Thanks for any help on this! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Data in Table Is Different When Seen From Different Sources
I'm having the strangest issue. I am using a Perl program to test out some other Perl programs and all the Perl connections with MySQL are normal, as in I use the standard interface. But in the test program I'm just using this: $out = `mysql --table -eSELECT * FROM search.Status`; print $out\n; as a quick and dirty way to check on the data tables while I'm working. This test program gives this output: +++---+---+--++-+--+---+ | Source | Search | Stage | Status| Resource | TotalItems | CurrentItem | CheckIn | PID | +++---+---+--++-+--+---+ | alpha | one| 3 | waiting |1 | -1 | -1 | 2011-0126-160720 | 0 | | alpha | two| 1 | searching | 201 | -1 | -1 | 2011-0126-160115 | 12331 | +++---+---+--++-+--+---+ I have two items in the first row that are my concern, the first is in the column Status, the 2nd is in the column PID. These are updated. Also, this program is a loop and after it displayed this table, I locked the screen with ctrl-q (in a BASH shell using Konsole on OS X with MacPorts). (Also, this system is a VM, under parallels, on another Mac and I'm using ssh to connect to that system to run all these commands.) But when I go to the console where I've logged into MySQL and display this table, I get: mysql SELECT * FROM search.Status; +++---+---+--++-+--+---+ | Source | Search | Stage | Status| Resource | TotalItems | CurrentItem | CheckIn | PID | +++---+---+--++-+--+---+ | alpha | one| 3 | searching |1 | -1 | -1 | 2011-0126-160120 | 12336 | | alpha | two| 1 | searching | 201 | -1 | -1 | 2011-0126-160115 | 12331 | +++---+---+--++-+--+---+ 2 rows in set (0.00 sec) When I go to a command line and cut and paste the command from in the program, I get this: hal@tnet-search:threshNet]$ mysql --table -eSELECT * FROM search.Status +++---+---+--++-+--+---+ | Source | Search | Stage | Status| Resource | TotalItems | CurrentItem | CheckIn | PID | +++---+---+--++-+--+---+ | alpha | one| 3 | searching |1 | -1 | -1 | 2011-0126-155546 | 12294 | | alpha | two| 1 | searching | 201 | -1 | -1 | 2011-0126-155545 | 12289 | +++---+---+--++-+--+---+ So I'm checking this data table from THREE sources, from within MySQL, from the command line, and from within the Perl program. How can I get different values in two cells in the table? Is there some kind of buffering along the way that could effect the output to the Perl program? Thanks for any ideas on this! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Data in Table Is Different When Seen From Different Sources
On Jan 26, 2011, at 5:11 PM, mis...@poczta.fm wrote: Hal Vaughan h...@halblog.com wrote: I'm having the strangest issue. I am using a Perl program to test out some other Perl programs and all the Perl connections with MySQL are normal, as in I use the standard interface. But in the test program I'm just using this: [... cut ...] Transaction isolation level? Add a timestamp column showing last update and then compare values. Thanks, actually, after sending that, I put in more time fields (since I would need them anyway) and that led me to finally finding a rogue process that was being forked in Perl, and finishing up fast enough that I didn't see it when I tried ps -ax. So it's solved now -- rogue process. Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Running Queries When INSERTing Data?
I'm redesigning some software that's been in use since 2002. I'll be working with databases that will start small and grow along the way. In the old format, data would come to us in mega-big text files that had to be parsed and manipulated and so on with Perl to remove crap and finally produce one tab delimited file. Once that file was created, another simple program would go through and use the data in each line for an INSERT statement that would put the data in a table. This table also has an Idx field that is an auto-incrementing primary key for the table. Each night at 3:30 am, a program would run and would go through the same process for each client. I never timed it, but it could take something like 30-60 seconds per client, but timing wasn't a major issue, since it had a LONG time from then until new data would be inserted into the DB. The SELECT statements to pull the data for each client involve a number of AND and OR conditions. The first one of these would create a temporary table with its results, then another long SELECT statement would create a 2nd temporary table by filtering the data out more. This would continue for a few temporary tables until the data was filtered. Then it would be packaged up and encrypted, then sent out to the client, who has a program on his computer to read that data and print it out if desired. This has worked, but for a number of reasons, a once-a-day data pull and send won't work as well with the new design. The program on the clients' computers will be able to access a special server just for them directly. (I know the concept of a server for each human client sounds inefficient, but it actually improves operations in a number of ways.) So each server will only have to provide data for one client. The big difference is that I'd like to make it so they can access the data live, or almost live. I don't mean all the data, but the subset that meets their needs. In other words, the equivalent of what was sent to them daily in the old system. Their individual servers will still get the big tab-delimited file that will still be INSERTed in to their DB line by line. But I'd like to be able to select from the new data as it comes in, once it's been given a new number in the Idx field. Is there any way to run a row of data through SELECT queries as it is being INSERTed into a table -- or just after? The reason for doing this, instead of INSERTing all the data, then running a program is that as the database grows, pulling out the data will take longer and longer, so if there were a way to screen data as it comes in, that would make it easier to provide instantly available data. I also know my knowledge of MySQL is quite limited, so if this can be done in better ways, I'd be interested in hearing about them. Thank you! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Running Queries When INSERTing Data?
On Nov 8, 2010, at 1:23 PM, Gavin Towey wrote: I'm not sure I understand exactly what you mean, but I think you just need to keep a timestamp associated with each row as it is inserted, put an index on it, then you can select new data just by using the appropriate time range. But won't that take just as long as any other queries? Or will it be speeded up because all the matching records would be adjacent to each other -- like all at the end? Also, if you're parsing files into tab delimited format, you don't need to write a separate parser to insert rows line by line. MySQL has LOAD DATA INFILE which takes delimited text files and inserts data in bulk, which is much faster than inserting line by line. THANKS! Is this new? I never read about it before, but then again, from about 2005-2006 until now the system was running smoothly and that's a long gap with no programming work! Hal Regards, Gavin Towey -Original Message- From: Hal Vaughan [mailto:h...@halblog.com] Sent: Monday, November 08, 2010 10:18 AM To: mysql@lists.mysql.com Subject: Running Queries When INSERTing Data? I'm redesigning some software that's been in use since 2002. I'll be working with databases that will start small and grow along the way. In the old format, data would come to us in mega-big text files that had to be parsed and manipulated and so on with Perl to remove crap and finally produce one tab delimited file. Once that file was created, another simple program would go through and use the data in each line for an INSERT statement that would put the data in a table. This table also has an Idx field that is an auto-incrementing primary key for the table. Each night at 3:30 am, a program would run and would go through the same process for each client. I never timed it, but it could take something like 30-60 seconds per client, but timing wasn't a major issue, since it had a LONG time from then until new data would be inserted into the DB. The SELECT statements to pull the data for each client involve a number of AND and OR conditions. The first one of these would create a temporary table with its results, then another long SELECT statement would create a 2nd temporary table by filtering the data out more. This would continue for a few temporary tables until the data was filtered. Then it would be packaged up and encrypted, then sent out to the client, who has a program on his computer to read that data and print it out if desired. This has worked, but for a number of reasons, a once-a-day data pull and send won't work as well with the new design. The program on the clients' computers will be able to access a special server just for them directly. (I know the concept of a server for each human client sounds inefficient, but it actually improves operations in a number of ways.) So each server will only have to provide data for one client. The big difference is that I'd like to make it so they can access the data live, or almost live. I don't mean all the data, but the subset that meets their needs. In other words, the equivalent of what was sent to them daily in the old system. Their individual servers will still get the big tab-delimited file that will still be INSERTed in to their DB line by line. But I'd like to be able to select from the new data as it comes in, once it's been given a new number in the Idx field. Is there any way to run a row of data through SELECT queries as it is being INSERTed into a table -- or just after? The reason for doing this, instead of INSERTing all the data, then running a program is that as the database grows, pulling out the data will take longer and longer, so if there were a way to screen data as it comes in, that would make it easier to provide instantly available data. I also know my knowledge of MySQL is quite limited, so if this can be done in better ways, I'd be interested in hearing about them. Thank you! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission
File Permissions On Database Files
My MySQL databases are stored in a directory that is with the rest of my project. I am using rsync to back up the entire project, including the database files. I'd much rather do it this way than to create files with mysqldump. Whenever MySQL creates a new database, the file is owned by the user mysql, and has limited read and write permissions (this is on Linux, btw). It would be a HUGE help if I could make sure every time MySQL creates any files, it creates them with at least group if not all read access for backup purposes. Is there a simple way to do this? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Numbering Rows on Output
I have a table that lists the tasks a program has to do. Lately I've found I can have an at-a-glance status report of how things are going on by writing a loop (in bash scripting, on Linux, btw) that uses mysql -e to display the list of tasks and their current state. It's quick and a lot simpler than I thought it would be to create a self-updating status display. The only thing missing is that it would be helpful to be able to add an extra column on the left for a row count -- preferably so each selected row has a number beside it, but putting a summary count on the last line (or adding an extra line with a summary count below it) would be helpful. I've Googled, but it seems this is almost impossible to do. Is it? Or is there a simple way to have a count next to the rows being displayed? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering Rows on Output
On Monday 28 November 2005 04:45 pm, Dan Nelson wrote: In the last episode (Nov 28), Hal Vaughan said: I have a table that lists the tasks a program has to do. Lately I've found I can have an at-a-glance status report of how things are going on by writing a loop (in bash scripting, on Linux, btw) that uses mysql -e to display the list of tasks and their current state. It's quick and a lot simpler than I thought it would be to create a self-updating status display. The only thing missing is that it would be helpful to be able to add an extra column on the left for a row count -- preferably so each selected row has a number beside it, but putting a summary count on the last line (or adding an extra line with a summary count below it) would be helpful. I've Googled, but it seems this is almost impossible to do. Is it? Or is there a simple way to have a count next to the rows being displayed? SET @row=0; SELECT @row:[EMAIL PROTECTED] AS row, otherfields from mytable; If you're doing it with mysql -e, this does it with one command: SELECT @row:=(ifnull(@row,0))+1 AS row, otherfields from mytable; Actually, this kept printing 1 for each row, so I did this: mysql -e SET @row=0; SELECT @rwo:[EMAIL PROTECTED] AS Row... which, obviously, is using the first way, but setting the variable in the same command line in mysql -e. That worked perfectly! It's interesting how there were so many pages on the net that indicated this was not possible or easily done. Thank you! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Transaction Delay?
I have a number of Perl programs running on a Linux system, all using MySQL. I have to keep track of programs, so I know which ones are working with which set of data, so programs check in when they start by creating a table entry and log out when they end by removing that table entry, but ALSO by making an entry, with their Process ID and the time of exit in a Checkout table, so in case there is a delay in a program exiting, I can track it. I was looking through system logs and noticed one case of a program that *should* have logged out by removing it's entry from a table before exiting, however the tracking program, one second after the program said it was logging out, said it found the program's entry in the tracking table. In other words, after the program should have removed its entry in the tracking table and exited, the entry was still in the tracking table for at least a second. This particular program would have performed at least 1,500 queries in, according to my logs, what would have been 61 seconds. There is also a good chance that other programs were also performing a number of queries during that same period of time. Is there any chance, that, due to the number of transactions being performed, that when I sent this particular query to Perl (it would have been a DELETE to remove one line from a table), that it could have been delayed -- even if for less than a second, but that it was accepted in some type of queue and the program was allowed to continue before the transaction was actually completed? Since all my programs use the same exit routines (before calling Perl's exit() function), at the moment this is the only explanation I can think of for the program still being listed in the table for possibly a second (or less) after it had left and its PID was no longer listed as running. (And, btw, when I mention times in seconds, my log timings are in seconds, so the delay could be less than a second or more.) Any insight is appreciated. Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
On Friday 25 November 2005 01:44 pm, Johan wrote: Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Use this option but use a third table that contains just ClipartID and KeywordID to create the m:n relationship. Like this: Clipart: ClipartID (primary key) Clipartname Keywords: KeywordID (primary key) Keyword (just one so must be unique) Linktable: ClipartID KeywordID (ClipartID + KeywordID = primary key) In the Clipart table, are names required to be unique? If so, then you can use Clipartname as the index, and that would eliminate the 3rd table. Or so I think -- I'm still learning this. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions on INSERT IGNORE
I want to be sure I understand INSERT IGNORE... correctly before I start depending on it. Up until now, I have not been using any kind of key or unique index, since many of my tables are created automatically and, until now, it has been difficult for me to create a way to distinguish between the tables that would have multiple matching records and the tables that need to have only unique values. So now that I can start using keys, I have a few questions: 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10 minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead, and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? 3) While this has been stated on the mysql.com, it is not what I was originally taught by a friend and some books, so I want to be clear I understand this correctly before I depend on it. As I understand it, INSERT IGNORE... compares the data being inserted with the keys of all records in the table and will not insert it if it duplicates an existing multi-column key. The IGNORE basically tells MyQL to not generate an error message if the data I'm inserting is a duplicate, so I can use INSERT IGNORE... in a Perl program to be sure I'm not duplicating records and not getting error messages on it if the data is a duplicate. Is this correct? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions on INSERT IGNORE
On Tuesday 01 November 2005 03:35 pm, [EMAIL PROTECTED] wrote: Which tables can have duplicate records in them should be something decided BEFORE you begin to populate the tables. 99.999% of the time, each row of any one table should be different from every other row on the same table. That difference may exist as a combination of values or as a single value but it is NEARLY ALWAYS in your best interest to have no two rows identical. It's not too late to make this decision. However, by putting it off for so long, implementing your uniqueness rules may be much more difficult. That's a problem with being self-taught -- there are many things like that one can miss. I was not aware any indexing sped things up. As for the design -- I always knew which tables required unique values, but it was a matter of what I had time to do and when I could do it. The priority was to get the system working, and make sure all the smaller programs did their job and played nicely together. Now that everything works, I can spare time to write code that will go through and easily distinguish which tables, when they are created, will need indexing. ... So now that I can start using keys, I have a few questions: Keys make finding records much faster. You should probably have created a few long before now. 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. No, the same routine will not be used. A hash of the values of the columns that participate in each PRIMARY KEY or UNIQUE KEY will be computed for each row (and stored as part of the key's index structure) and also for each new row as it is being INSERTed. If the new row's hash matches the hash of any UNIQUE or PRIMARY KEY an error will be thrown by the server. An INSERT with the IGNORE modifier will ignore that particular error (basically skipping that row) and continue inserting records. Okay -- great. That also answers my last question -- I needed to be sure IGNORE did what I thought it did. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead,... none. The speed-up will come from the combination of INSERT IGNORE and the UNIQUE or PRIMARY key(s) defined on the target table. It will not come from simply changing INSERT to INSERT IGNORE. In fact, defining ANY keys on that table will cut your processing time considerably. You should be able to insert several hundred records every second (1000s per minute). Your total lack of all indexes has absolutely killed your performance. So, unless I misunderstand, adding both keys and IGNORE will speed things up by a factor of 100 to 1000 or more. That is a huge relief. (Again, the first step was developing the system and making sure it works, so now I'm speeding it up and adding other useful features, like keys. ... and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? The DELAYED modifier asks the server to buffer your INSERTs so that they can be interleaved with any active read requests allowing your client to believe it has finished inserting records much sooner than if it had waited on all of those inserts to actually happen. It should not be necessary to use DELAYED under most circumstances. You will need to benchmark both techniques to determine which one works best for your situation. Which leads to another question: If my program things the data is inserted, and it is delayed, is the queue of DELAYed transactions kept anywhere -- so if MySQL or the system crashes, none of the DELAYed transactions are lost? 3) While this has been stated on the mysql.com, it is not what I was originally taught by a friend and some books, so I want to be clear I understand this correctly before I depend on it. As I understand it, INSERT IGNORE... compares the data being inserted with the keys of
Re: Questions on INSERT IGNORE
Follow up at bottom: On Tuesday 01 November 2005 04:15 pm, Hal Vaughan wrote: On Tuesday 01 November 2005 03:35 pm, [EMAIL PROTECTED] wrote: Which tables can have duplicate records in them should be something decided BEFORE you begin to populate the tables. 99.999% of the time, each row of any one table should be different from every other row on the same table. That difference may exist as a combination of values or as a single value but it is NEARLY ALWAYS in your best interest to have no two rows identical. It's not too late to make this decision. However, by putting it off for so long, implementing your uniqueness rules may be much more difficult. That's a problem with being self-taught -- there are many things like that one can miss. I was not aware any indexing sped things up. As for the design -- I always knew which tables required unique values, but it was a matter of what I had time to do and when I could do it. The priority was to get the system working, and make sure all the smaller programs did their job and played nicely together. Now that everything works, I can spare time to write code that will go through and easily distinguish which tables, when they are created, will need indexing. ... So now that I can start using keys, I have a few questions: Keys make finding records much faster. You should probably have created a few long before now. 1) I've been using SELECT FirstName, LastName, Birthdate FROM division.People WHERE FirstName = '$fname' AND LastName = '$lname' AND Birthdate = '$bday' to check for preexisting records. This means before I inserted a record, I would select on specific fields and see if they matched the fields of the current record. If they did, I threw out the record I was going to enter, if there was not a match, I'd INSERT the new record. If I use INSERT IGNORE..., MySQL will still have to verify that the new record does not match any old records. How much faster is it to do it that way than the way I was? I'd think the same routines to find matching data would be used. No, the same routine will not be used. A hash of the values of the columns that participate in each PRIMARY KEY or UNIQUE KEY will be computed for each row (and stored as part of the key's index structure) and also for each new row as it is being INSERTed. If the new row's hash matches the hash of any UNIQUE or PRIMARY KEY an error will be thrown by the server. An INSERT with the IGNORE modifier will ignore that particular error (basically skipping that row) and continue inserting records. Okay -- great. That also answers my last question -- I needed to be sure IGNORE did what I thought it did. 2) Right now I'm creating an archival database to store older records in. In one of these archives, there are over 250,000 records. At this point, by doing things the old way (checking for a match, then inserting), it is now able to insert about 750 records in 10 minutes. Earlier, when it was only about 180,000 records, it was inserting at about 1,000 records in 10minutes. So, first, how much of a speed up can I count on if I use INSERT IGNORE instead,... none. The speed-up will come from the combination of INSERT IGNORE and the UNIQUE or PRIMARY key(s) defined on the target table. It will not come from simply changing INSERT to INSERT IGNORE. In fact, defining ANY keys on that table will cut your processing time considerably. You should be able to insert several hundred records every second (1000s per minute). Your total lack of all indexes has absolutely killed your performance. So, unless I misunderstand, adding both keys and IGNORE will speed things up by a factor of 100 to 1000 or more. That is a huge relief. (Again, the first step was developing the system and making sure it works, so now I'm speeding it up and adding other useful features, like keys. ... and second, if I use INSERT IGNORE DELAYED, will the program finish faster, leaving MySQL to catch up with the queued INSERT statements as it can? The DELAYED modifier asks the server to buffer your INSERTs so that they can be interleaved with any active read requests allowing your client to believe it has finished inserting records much sooner than if it had waited on all of those inserts to actually happen. It should not be necessary to use DELAYED under most circumstances. You will need to benchmark both techniques to determine which one works best for your situation. Which leads to another question: If my program things the data is inserted, and it is delayed, is the queue of DELAYed transactions kept anywhere -- so if MySQL or the system crashes, none of the DELAYed transactions are lost? 3) While this has been stated on the mysql.com, it is not what I was originally taught
Problem starting MySQL With Moved Data Directory
I'm using MySQL 4.0.24-10 on Debian Sarge (Linux, and the stable branch of Debian, just to clarify). I had a setup of a program I've been working on for several years that is on a RAID. I changed MySQL's data directory so it will be on the same RAID and backed up, along with all my other data and programs. Originally this was set up under a Debian-based distro that was based on the testing and unstable branches of Debian. Before putting the server into production, I wanted to change it to Debian Stable (Sarge) for safety. (I'll sleep easier at night if it's on Sarge with regular security updates than depending on unstable packages!) So I backed up all the data on the RAID, wiped the original boot/system drive, installed Debian Sarge, did an fsck on the RAID, found errors, wiped it out, restored the original data, including the MySQL db files. When I installed MySQL on the re-done system, it worked. When altered the line in /etc/mysql/my.cnf from: datadir = /var/lib/mysql to: datadir = /thresh/tNet/db and restarted MySQL with /etc/init.d/mysql start, I get the following: Starting MySQL database server: mysqld. Checking for crashed MySQL tables in the background. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)' However, after I got this, I entered mysql and got a connection. The databases and tables seemed intact. I checked, and it turned out that somewhere in the backup/restore process the ownership of the files was changed. I went through and changed the ownership of /thresh/tNet/db to mysql:mysql and changed the ownership of /thresh/tNet/db/mysql to mysql:root. Basically, I changed the ownership of the files in the new data directory to match what I found in /var/lib/mysql, then I stopped and restarted MySQL. I still got the same error messages. While MySQL is working, I don't want to do anything with it because I'm not clear why debian-sys-maint needs access, as opposed to just the user mysql. To test, I made sure all the files from the db directory on down were set for all to read and write and all directories set for all to read, write, and execute. That doesn't help. So what is wrong, what do I need to do, and why didn't I get this error when using the same database directory before? Thanks for any help! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limiting DISTINCT To One Column
I have a query like this: SELECT DISTINCT Channel, ChannelType, Source FROM ChannelStatus; Each channel is supposedly listed in this table only 1 time, but just in case, what I really want to do is make sure that no channels are duplicated. Is there some way to make the keyword DISTINCT apply to Channel only? Sort of a shortcut to (example in pseudocode, although it'd be in Perl): SELECT DISTINCT Channel FROM ChannelStatus; FOR EACH Channel SELECT Channel, ChannelType FROM Source WHERE Channel = 'channel' ENDLOOP Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
On Thursday 25 August 2005 04:44 am, [EMAIL PROTECTED] wrote: Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is How can I do a job without using the tool designed for the job?. If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec I have some routines for entering large amounts of data into different tables. *IF* INSERT IGNORE worked, it was easy for me to simply add IGNORE to a query string (this is all in Perl) for tables where I did not want dupes. I also have a number of tables where there are reasons for allowing multiple entries. There are also some tables where items from one source must not be duplicated, where entries from another source should be, since they are counted later. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
On Wednesday 24 August 2005 02:25 am, [EMAIL PROTECTED] wrote: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/430c12dd47626c9c/hal=thresholddigital.com This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: For help and a description of available commands, send a message to: [EMAIL PROTECTED] To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 14829 invoked by uid 48); 24 Aug 2005 06:25:31 - Date: 24 Aug 2005 06:25:30 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 24.125.140.72. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT IGNORE Doesn't Seem To Work
I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
On Wednesday 24 August 2005 02:47 am, Hal Vaughan wrote: I may have a misunderstanding of this, but as I have been told, if I have a table with 3 columns, Idx (an Index column, unique, auto-increment), Name, Value (both varchar), and I try a command like this: INSERT IGNORE INTO myTable SET Name = Variable1, Value = 100; or INSERT IGNORE INTO myTable (Name, Value) VALUES(Variable1, 100); AND I already have a row with the matching Name and Value columns matching in value, that MySQL will detect that and not insert the redundant values. I've also tried this without a unique, auto-increment column, just trying to insert by specifying values for all 3 columns that already match an existing row, and it still doesn't work. I thought the IGNORE keyword was intended to be used to prevent duplicating values, and that it matched the values in the INSERT statement (even if not all columns in the table were given a value) against the ones in the table and would NOT INSERT the row if it matched. I'm using MySQL 4.023 on Debian Linux (installed through apt-get, not through downloading). So this brings up a few questions: 1) Am I doing something wrong? 2) Is this what INSERT IGNORE is supposed to do -- if not, what does it do?, and 3) If this isn't what INSERT IGNORE does, how can I do what I *thought* it did -- insert only if the value doesn't already exist? Thanks! Hal Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 10:18 am, Hassan Schroeder wrote: Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. I'm trying to get 2 different instances of mysqld running at the same time, each using a different port and different data directory. Hal -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 10:56 am, Hassan Schroeder wrote: Hal Vaughan wrote: I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. Uh, that doesn't really make sense -- a *running* instance isn't going to switch data directories because another process started up :-) That's what's happening. I have no idea why. Here's what I'm doing now: (each command is in a separate console) mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Sounds like something in the way you're invoking this is causing the first instance to be restarted, and then it's using the second set of parameters. I do a ps-ax and get this (only mysqld tasks pasted in): 13391 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13401 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13402 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13403 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13450 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13451 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13452 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13453 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. Hal HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 11:48 am, Hassan Schroeder wrote: Hal Vaughan wrote: mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Aha. Light bulb :-) My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. They don't, but it's what you're *not* specifying here -- the two processes are sharing the default /tmp/mysql.sock socket, I'll wager. So the last daemon started is listening on it... Bingo! I tried adding --socket=/var/run/mysqld/mysqld6.sock for the process running with port 3306 and mysqld7.sock for the process running on port 3307. It works perfectly! Thanks! This will really help me out in getting rid of some extra boxen that are too slow and need to be retired to the parent's e-mail computers. Making the change for the one routine in my Perl modules that creates the connection to the database is trivial, too. Thanks to all who have helped with this. Hal I think you'll find creating two config files will make it a *lot* easier to get this going :-) FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Different Database Groups On the Same Computer
I'm not quite sure what search terms to use, otherwise I'm sure I could find this on Google. I have a working install of MySQL on an older computer, running the current version of my program. I am about to start developing a newer version of my program on a new computer. I'd like to have both versions (current and new) on the same computer, however, that will cause a LOT of problems in interference between the current and development versions of my program. Is there any way to separate these, so I can run mysql one way to access the databases for the current version, and run it another way to access the newer databases for the development version (and yes, many databases in the development version will have the same name as those in the current version). For example, if I ran a command mysql, I'd get these tables: CaseData Clients GeneralSetup And if I ran an alias, like, say, new-mysql, I'd get these tables: CaseData (same name, different table) Clients (same name, different table) GeneralSetup (same name, different table) DataFilters I am running MySQL on Linux, if that makes a difference. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Wednesday 16 February 2005 09:04 pm, you wrote: Hi Hal, Do you mean using the same version but accessing 2 different sets of tables? I'm a little confused as to what your requirements are. Or databases. Basically, I want it all on one system, but I don't want the current databases, which are in use by the current and running version of the program, to be effected in any way by the new version of my program (actually a suite of programs), which will be using databases and tables with the same names as the current version. You could install both versions and use aliases to ensure that you can access only one instance at a time by the method you described. You would have new-mysql aliases to a mysql command with either a different port number or hostname to access the newer version whereas the mysql would point to localhost on the standard port. Let me put it in my words, so I can be sure I understand you. So I could install MySQL and leave everything normal for my current program to access everything it can access now (I'm accessing them through Perl, using DBI), AND I can also specify another group of databases that are accessed if I try a different port from Perl, or perhaps, a different specification on the command line. Is that it? If so, is there a technical term for what I'm trying to do, or what each set of databases is called? And what does this come under in the manuals and info so I can find out how to configure my system to do this? I run 2 different versions on one of my smaller linux boxes for precisely the reason you are talking about. If you are accessing these via php for example, you can use an environment variable to set which port, host etc. you wish to talk to. That's basically what I want to do, so I think we're talking about the same thing. Thanks! Hal Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Hal Vaughan [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 12:23 PM To: mysql@lists.mysql.com Subject: Using Different Database Groups On the Same Computer I'm not quite sure what search terms to use, otherwise I'm sure I could find this on Google. I have a working install of MySQL on an older computer, running the current version of my program. I am about to start developing a newer version of my program on a new computer. I'd like to have both versions (current and new) on the same computer, however, that will cause a LOT of problems in interference between the current and development versions of my program. Is there any way to separate these, so I can run mysql one way to access the databases for the current version, and run it another way to access the newer databases for the development version (and yes, many databases in the development version will have the same name as those in the current version). For example, if I ran a command mysql, I'd get these tables: CaseData Clients GeneralSetup And if I ran an alias, like, say, new-mysql, I'd get these tables: CaseData (same name, different table) Clients (same name, different table) GeneralSetup (same name, different table) DataFilters I am running MySQL on Linux, if that makes a difference. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Wednesday 16 February 2005 09:27 pm, Logan, David (SST - Adelaide) wrote: Ooops, sorry for the second time, I forgot to include the list. Yes, you can install the normal version of mysql (in my case 4.0.22) and leave everything default. You can then install a new version of mysql (4.1.3 or something) and then set up the my.cnf so that it listens on a different tcp port. MySQL uses port 3306 by default but you can change this via the my.cnf. I also set up a different startup file in the rc3.d directory that starts the second server using different parameters passed via the command line. Okay -- I follow most of this. Just one other question: Is there a way to set this up with only ONE install of MySQL? (I'm running a Debian system and, for the sake of updates and stuff, I'm trying to put as little as possible on the system and keep it all so it can be easily updated through security.debian.org.) In other words, could I make it so if I access it through 3306, it goes to the current version, but port 3307 makes it use the new databases? The docs do quite a good job on describing this. You can then specify this in your DBI call to MySQL like my $dsn = DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber; my $dbh = DBI-connect($dsn, 'username', 'password', \%err_handle); You could also use a unix socket rather than tcp by specifying the socket name rather than a port number. This is documented in the DBI docs at CPAN. You can do the same thing from PHP, eg. specifying a different port number etc. You can do this from the command line by using mysql --username username --password --port=3307 --socket=somethingdifferent. Just make sure that you specify 2 different data directories, 1 for the production or normal version and 1 on a different filesystem or whereever for the development version. It will not matter that the databases have the same name as they are in different mysql instances. I figured I'd have to have different directories. That also makes backing up data easier. (I'm backing everything up with rsync, so if I specify directories that are in the same directory tree the rest of the programs and data are in, I can back up everything with one cron job instead of using mysqldump AND backing up the file I generate with it.) I also use environment variables set in the apache config to ensure separation. I have 2 servers that I use, one production and one development. I have the following 2 statements in the virtual host section for the website At this point, fortunately, I won't have to worry about doing anything through Apache. I think I'll miss that entirely. httpd.config on development SetEnv DB_HOST devhost SetEnv DB_USER root httpd.config on production SetEnv DB_HOST prodhost SetEnv DB_USER prod_username and then access these through the %ENV hash in perl or getenv function in PHP. That makes it easy, all I have to do is transfer the scripts over and they pick up the correct names. You could do this on a single server by having virtual hosts and setting the variables to different values dependent upon the url called. I'm using Perl for the web stuff (which is only for a setting editor). I'm not a professional programmer. I hadn't touched a line of code in over a decade when I started this. I discovered Perl quickly, and I've done as much as possible in Perl so I can give myself a sense of unity. (I had to do another part in Java, so I'm trying to stick with only those two languages for now.) HTH Thank you for all the info. It's a big help! Hal Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Hal Vaughan [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 12:45 PM To: Logan, David (SST - Adelaide) Subject: Re: Using Different Database Groups On the Same Computer On Wednesday 16 February 2005 09:04 pm, you wrote: Hi Hal, Do you mean using the same version but accessing 2 different sets of tables? I'm a little confused as to what your requirements are. Or databases. Basically, I want it all on one system, but I don't want the current databases, which are in use by the current and running version of the program, to be effected in any way by the new version of my program (actually a suite of programs), which will be using databases and tables with the same names as the current version. You could install both versions and use aliases to ensure that you can access only one instance at a time by the method you described. You would have new-mysql aliases to a mysql command with either a different port number or hostname to access the newer version whereas the mysql would point to localhost on the standard port. Let me put it in my words, so I can be sure I understand you. So I could
Re: Using Different Database Groups On the Same Computer
On Wednesday 16 February 2005 09:48 pm, Logan, David (SST - Adelaide) wrote: You could start a second copy of the same server the way that I described below. Just start the second copy listening on a different port. You could use command line overrides to start it up with a different my.cnf file with the changed parameters. eg. different datadir, different port etc. This way you don't have to install a second newer version but can run with the old one. instance 1 starts with /etc/my.cnf (default) instance 2 starts with /etc/my.cnf.dev and listens on port 3307 with a datadir of /some/where/else/on/the/disk See chapter 4 of the manual http://dev.mysql.com/doc/mysql/en/using-mysql-programs.html before I give you a bum steer 8-) that has all the docs on how to set up the correct option files you will require Since you gave me this, I was able to find most of what I needed with mysqld --help. I knew I could set the port, but I still wanted to be sure I could have two instances (that's the word I couldn't remember when I was trying to figure out what to Google for) running, using different data sources without them interfering with each other. From what I see, I can take /etc/init.d/mysql and copy it to /etc/init.d/mysql2. I can modify the 2nd version to specify a different port and a different config file (which may not be needed if I specify the different data directory) on the command line. I'll try this tomorrow, when I'm more alert. I just didn't want to poke around with something like that and find out later that I was doing something that had effects I wouldn't see until it was too late. Thank you for all the info. This is huge help to me (it lets me get rid of 2 old boxen that'll go to my parents and another relative and move everything onto one system -- at long last!). Hal Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Hal Vaughan [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 1:10 PM To: mysql@lists.mysql.com Subject: Re: Using Different Database Groups On the Same Computer On Wednesday 16 February 2005 09:27 pm, Logan, David (SST - Adelaide) wrote: Ooops, sorry for the second time, I forgot to include the list. Yes, you can install the normal version of mysql (in my case 4.0.22) and leave everything default. You can then install a new version of mysql (4.1.3 or something) and then set up the my.cnf so that it listens on a different tcp port. MySQL uses port 3306 by default but you can change this via the my.cnf. I also set up a different startup file in the rc3.d directory that starts the second server using different parameters passed via the command line. Okay -- I follow most of this. Just one other question: Is there a way to set this up with only ONE install of MySQL? (I'm running a Debian system and, for the sake of updates and stuff, I'm trying to put as little as possible on the system and keep it all so it can be easily updated through security.debian.org.) In other words, could I make it so if I access it through 3306, it goes to the current version, but port 3307 makes it use the new databases? The docs do quite a good job on describing this. You can then specify this in your DBI call to MySQL like my $dsn = DBI:mysql:host=$hostname;database=lcscreative;port=$newportnumber; my $dbh = DBI-connect($dsn, 'username', 'password', \%err_handle); You could also use a unix socket rather than tcp by specifying the socket name rather than a port number. This is documented in the DBI docs at CPAN. You can do the same thing from PHP, eg. specifying a different port number etc. You can do this from the command line by using mysql --username username --password --port=3307 --socket=somethingdifferent. Just make sure that you specify 2 different data directories, 1 for the production or normal version and 1 on a different filesystem or whereever for the development version. It will not matter that the databases have the same name as they are in different mysql instances. I figured I'd have to have different directories. That also makes backing up data easier. (I'm backing everything up with rsync, so if I specify directories that are in the same directory tree the rest of the programs and data are in, I can back up everything with one cron job instead of using mysqldump AND backing up the file I generate with it.) I also use environment variables set in the apache config to ensure separation. I have 2 servers that I use, one production and one development. I have the following 2 statements in the virtual host section for the website At this point, fortunately, I won't have to worry about doing anything through Apache. I think I'll miss that entirely. httpd.config
Re: Using Different Database Groups On the Same Computer
On Wednesday 16 February 2005 08:52 pm, Hal Vaughan wrote: I'm not quite sure what search terms to use, otherwise I'm sure I could find this on Google. I have a working install of MySQL on an older computer, running the current version of my program. I am about to start developing a newer version of my program on a new computer. I'd like to have both versions (current and new) on the same computer, however, that will cause a LOT of problems in interference between the current and development versions of my program. Is there any way to separate these, so I can run mysql one way to access the databases for the current version, and run it another way to access the newer databases for the development version (and yes, many databases in the development version will have the same name as those in the current version). For example, if I ran a command mysql, I'd get these tables: CaseData Clients GeneralSetup And if I ran an alias, like, say, new-mysql, I'd get these tables: CaseData (same name, different table) Clients (same name, different table) GeneralSetup (same name, different table) DataFilters I am running MySQL on Linux, if that makes a difference. Hal I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. In all test cases, I've always run the plain version first, and the one using the 2nd port and new data dir last. No matter what I do, it always uses the new data dir, whether I try mysql or mysql --port=3307. Any ideas? The goal is to have 2 instances of MySQL running on the same computer (preferably without needing 2 different versions or 2 different installs), with each instance using different data directories (and, of course, to reach the separate instances, each listens on a different port). Thanks for any help. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pulling numbers out of a column for a query
Thanks for the responses on this. I've gotten so used to using the ref
materials I have on hand that, unfortunately, I forgot to check online
manuals from time to time. (My measly sources had helped with what I needed
until now and my best source, MySQL Cookbook, is on loan to a friend.)
I neglected to mention that sometimes the column with the speeds in them has
extra data (like 22/25 School Zone, or School Zone 22/25), so it took
some extra work, like adding RIGHT() and LEFT(). (Full query below...)
I had not split the data into separate columns because 1) sometimes the data
is just one number, like -3 for 3 miles under the limit, or 5 for 5 miles
over (and sometimes in other forms), 2) I often need to make sure the actual
line of data is included, so I didn't want to separate out figures. At this
point, I'm adding an extra column to the table, SpeedOver (since most of the
differences are over the speed limit). Since the data is already in the
table, using the query help I got here, I can easily drop the new data into
the new column. If I get more data (likely), I'll be able to process it with
Perl to include the extra column (easier than in queries, since Perl is so
good with regexes and can handle 3 digit speeds better).
On Monday 05 January 2004 09:37 pm, Hal Vaughan wrote:
I've found an odd problem in queries.
I have a lot of data regarding vehicle speeds in a survey. All the data is
in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43
miles per hour in a 55 miles per hour zone. 80/55 means we have a serious
speed demon, going 80 in a 55 zone.
I need to be able to pull out data depending on speeds in relation to the
speed limit, for example, find out how many are less than 55/55 or 45/45,
or how many are more than 5 miles over (like 61/55 or 32/25).
Here's what I used as a query to create 2 columns, the first is the actual
speed info and the 2nd is the difference between the speed limit and clocked
speed. I do not, in this case, take into account 3 digit speeds. In this
example, I'm pulling out all records where the speed exceeded the speed limit
by 20 miles per hour (since that is legally reckless driving, and one data
point we're looking for):
SELECT Speed, RIGHT(SUBSTRING_INDEX(Speed, '/',
1),2)-LEFT(SUBSTRING_INDEX(Speed, '/', -1),2) AS Diff FROM TrafficSurvey
WHERE Speed REGEXP '.*[0-9]{1,2}/[0-9]{1,2}.*' AND
(ABS(RIGHT(SUBSTRING_INDEX(Speed, '/', 1),2)-LEFT(SUBSTRING_INDEX(Speed, '/',
-1),2)) 20);
Thanks for the help!
Hal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pulling numbers out of a column for a query
I've found an odd problem in queries. I have a lot of data regarding vehicle speeds in a survey. All the data is in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43 miles per hour in a 55 miles per hour zone. 80/55 means we have a serious speed demon, going 80 in a 55 zone. I need to be able to pull out data depending on speeds in relation to the speed limit, for example, find out how many are less than 55/55 or 45/45, or how many are more than 5 miles over (like 61/55 or 32/25). I know how to use regular expressions and can SELECT only tables with the data in this form (many are easier and have an extra column filled in with the speed in relation to the speed limit), so I can pull out all the records with the formatting I'm discussing. Is there any way, within a SELECT, to pull out the numbers and subtract them? For instance, if a record has 45/55, I'd like to be able to separate them into 45 and 55 and subtract them, like 45-55 to get -10, which tells me the speed is 10 miles under the limit. I can program around this with Perl (another topic, which I'm not raising here), but that means selecting data, putting it into a table and going through each record individually, which will take MUCH longer than doing it by a SELECT statement within MySQL. Thanks for any help or ideas. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting identical rows from 2 tables (basically Row AND Row)
I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! Hal - 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: Selecting identical rows from 2 tables (basically Row AND Row)
On Friday 21 March 2003 05:42 pm, Brian McCain wrote: Do you have two identical tables? If not, then doing a select the way you propose won't yield any rows. If they are identical, then you've already got the data, and so wouldn't need to select anything. I'm sure I'm misunderstanding exactly what you're trying to do, so maybe you could explain it a bit further. I have a table where different people will select items. The items Person1 selects will go into Table1 (described as TestCases below), the items Person2 selects go in Table2 (described as TestTemp below). After Person1 and Person2 have each selected items, I want to be able to look at which items they have BOTH selected and list only those items -- only the rows selected by both. Hal remainder of correspondence follows... In any case, if you've got a unique key that tied the two tables together (or if you could set it up so that you did), that would be your best bet. Using all those values in your where clause is going to slow your selects down considerably if there aren't keys on them (and if the number of columns is variable, it would probably be difficult to have the keys to handle all combinations of columns). Brian McCain - Original Message - From: Hal Vaughan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:29 PM Subject: Selecting identical rows from 2 tables (basically Row AND Row) I posted earlier this week about how to select from two different tables with an OR -- selecting rows from either table and putting them into one. That was easily accomplished with a UNION statement (AFTER upgrading to MySQL 4.0.x). Now I'm doing almost the opposite. I have two tables, TestCases, and TestTemp (a temporary table). I want to select any rows in TestCases AND TestTemp. I know I can do that if I go through and match field by field: SELECT * FROM TestCases AS C, TestTemp AS T WHERE C.Field1 = T.Field1 AND C.Field2 = T.Field2; (and so on if there are more fields). Is there a shortcut to this? Is there a quick way to say SELECT * FROM TestCases AS C, TestTemp AS T WHERE (allfields = allfields); I'm calling from Perl, so I can write a routine that will go through and list every darn field, but I'd like to find a shorter and faster way to do it. Thanks! Hal - 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 - 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 - 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
Selecting only ONCE from multiple tables
I'm just getting used to SQL/MySQL, so there is likely a name for this or it may be well known -- I just haven't either come across it, or haven't made the associations between all the parts yet. I have 2 tables, one a temp table, and they have the same columns. I'd like to be able to select from both tables and get one listing. Table 1 is Cases, Table 2 is Temp. They have columns Name, Amount, Zip. SELECT * FROM Cases AS C, Temp AS T WHERE (C.Amount 500 OR T.Amount 500); produces a list of 38 rows w/ 6 columns (the first 3 columns from Cases, the 2nd 3 columns from Temp). This should select 2 rows from Temp and 4 from Cases. (The 2 rows in Temp are duplicates of the ones in Temp.) Instead of getting one list with 3 columns, this list iterates through each row in Temp once for each row in Cases and also includs the duplicated rows a 2nd time. While I expect the duplicated rows to show up twice, how do I produce a combined list. Another way to put it is that I have 2 tables w/ similar columns and want to select from the 2 of them and take the results and either output it or put it into a new table. Thanks for any suggestions or help. Hal - 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
Trouble upgrading to 4.0 on Mandrake Linux
I am running Mandrake Linux 8.2, with the RPMs upgraded to 9.0. It had MySQL 3.23 running on it. I downloaded the current version of 4.0 from mysql.com tonight and isntalled it. I changed the old files from /usr/share/mysql to /usr/share/mysql3.23 and /var/lib/mysql to /var/lib/mysql3.23 (where the databases are stored). I created a new /usr/share/mysql and unpacked the MySQL 4.0.x tarball into the directory, then followed the install instructions (basically changing /usr/local/ to /usr/share/ in bin/mysqlaccess and running scripts/mysql_install_db). I copied the 4.0 executables to /usr/bin (which I did without thinking -- realizing I forgot to backup the original 3.23 binaries in that directory -- my big mistake). Now when I type mysqld_safe --user=mysql the daemon starts, but I get another message almost immediately that it is exiting. What's wrong and what do I need to do to get 4.0 working? Why is the daemon quitting on me? Is anyone else using Mandrake and encountering similar problems? Thanks! Hal - 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
Problem matching on only part of a field
I've been reading through documentation (including the O'Reilly book links on the mysql.com website), and I'm sure this is simple, but I can't seem to find it. I'm managing a mailing list for a client and I want to match on United States zip codes that correspond to different areas. (For non-US residents, the USPS used a 5 digit system for decades, then started a +4 system, which added 4 more digits to the original, so if your old zip was 12345, that would still work, but you could add 4 more digits to help for faster sorting -- like 12345-6789.) I'm using 2 tables, MailingList and ZipCodes. In MailingList I have the regular exepected columns -- Name, Street, City, State, and Zip. In ZipCodes I have ZipCodes, Name, District. The name is for the area the zip code covers and the district is the city or county it is in. For example, I am in the Bon Air zip code, but I'm in Chesterfield County, so the Name is Bon Air, but the District is Chesterfield. I want to be able to select all the names in MailingList in a specific county. Here is what I tried first: SELECT Name, City, Zip FROM MailingList AS M, ZipCodes AS Z, WHERE (M.Zip = Z.ZipCodes) AND (Z.District = Chesterfield); I did this and it pulled out 1 name, so I looked and realized a LOT of records had Zip+4, so they were 9 digit zip codes. So I tried to match only the first 5 characters of the Zip and ZipCode fields like this: SELECT Name, City, Zip FROM MailingList AS M, ZipCodes AS Z, WHERE ( left(M.Zip, 5) = left(Z.ZipCodes,5) ) AND (Z.District = Chesterfield); And it returns the same record, and nothing more. I tried it on 4 digits: SELECT Name, City, Zip FROM MailingList AS M, ZipCodes AS Z, WHERE ( left(M.Zip, 4) = left(Z.ZipCodes,4) ) AND (Z.District = Chesterfield); And it returned all the records it should have returned on 5 digits, but some of the records were retruned 6 times, some were returned only 1 time. So how can I select on a limited part of a field and why doesn't the 2nd line -- selecting the left 5 characters of both Zip and ZipCode work? Thank you! Hal - 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
