Re: OT: SQL Question
Jeff, I would use a join table, teacher_flights. create table teacher_flights( teacher_id int(11) not null, flight_id int(11) not null, primary key(teacher_id, flight_id)); Dave From: Mark Phillips m...@phillipsmarketing.biz To: Mysql List mysql@lists.mysql.com Sent: Friday, March 23, 2012 7:28 PM Subject: OT: SQL Question My question is not specific to MySQL, even though I am using a MySQL db for this project. I have a servlet/jsp/MySQL web site in production, and there are about 2,000 records in the flights table. One of the foreign keys is teacher_id. Up to this point, there is a one to many relationship between teacher_id and the data in the flights table. I need to change the data model to allow for a many to many relationship between teacher_id and the data in the flight table. What is the best way to do this? Thanks, Mark
Re: big character constant
select 'U02714','U02718';
insert into my_table values('U02714');
insert into my_table values('U02718');
Let me know if this is what you intended.
Dave
From: h...@tbbs.net h...@tbbs.net
To: mysql@lists.mysql.com
Sent: Friday, March 23, 2012 5:14 PM
Subject: big character constant
How does one enter characters U02714 and U02718 in a query? or insert them
into a record?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
Re: InnoDB Indices
- Original Message From: Robert DiFalco [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, March 9, 2006 9:32:44 AM Subject: InnoDB Indices I have some questions regarding InnoDB indices. Say I have a table with millions of records. On of the fields is a type field that has a possible value of 1,2,3, or 4. I sometimes query by the type field and may at other times order on it. Do queries benefit from an index with this low of a selectivity? ++ For the most part no. I come from Oracle where you can use histograms to help. So, someone feel free to correct me if I'm wrong. If through the query browser I find that they do, should I increase the selectivity of the index by making it a compound index and adding my Primary Key to the TYPE index? ++ If your primary key will be included in the where clause then definitely include it. If I make it a compound index, does it have any positive impact on INSERT, UPDATE, or maybe just DELETE? ++ I can't see it helping with insert, but depending on the where clause on your updates and deletes it could. Dave R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find missing rows from subset of table using Left Join?
It appears mysql recommends against the solution given to you http://dev.mysql.com/doc/refman/5.0/en/join.html You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule. If you could send a sample of table1, table2, and the result set you want to arrive at I'd appreciate it. Dave --- mos [EMAIL PROTECTED] wrote: At 09:54 PM 11/8/2005, David Turner wrote: If you could present sample data of both table1, table2, and an example of the result set it would be easier to give you the sql. I believe you could eliminate the temporary table with a subselect in the original query. The subselect is where you would specify 'Smith'. Dave Hi Dave, Another user privately e-mailed me the errors of my ways.g Here is the problem (returns 0 rows): select * from table1 t1 left join table2 t2 on t1.date1=t2.date2 where t2.date2 is null where t2.name='Smith' Here is the solution: select * from table1 t1 left join table2 t2 on t1.date1 = t2.date2 AND t2.name = 'Smith' WHERE t2.date2 is null; I had to move another reference of t2 from the Where clause to the Join, namely t2.name='Smith' gets moved to the join. I thought I had done this before but I left one reference in the Where clause that prevented any rows from being returned.It appears the conditional in the join clause gets executed before the rows are joined, and the Where clause gets executed after the join. Mike --- mos [EMAIL PROTECTED] wrote: I would like to find the missing subset of rows in table2 based on the rows in table1. Normally it would look like this: select * from table1 t1 left join table2 t2 on t1.date1=t2.date2 where t2.date2 is null Well this works fine except I only want to compare a subset of rows in table2 for a particular person. I tried: select * from table1 t1 left join table2 t2 on t1.date1=t2.date2 where t2.date2 is null where t2.name='Smith' and of course this doesn't work because the t2 row can't be missing if it finds 'Smith'. (I've created a Paradox-bad pun I knowg) The only solution I've found is to create a temporary table with the rows from t2 that belong to 'Smith', then run the left join on the temporary table. I'm wondering if there is a better way that doesn't involve temporary tables? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find missing rows from subset of table using Left Join?
If you could present sample data of both table1, table2, and an example of the result set it would be easier to give you the sql. I believe you could eliminate the temporary table with a subselect in the original query. The subselect is where you would specify 'Smith'. Dave --- mos [EMAIL PROTECTED] wrote: I would like to find the missing subset of rows in table2 based on the rows in table1. Normally it would look like this: select * from table1 t1 left join table2 t2 on t1.date1=t2.date2 where t2.date2 is null Well this works fine except I only want to compare a subset of rows in table2 for a particular person. I tried: select * from table1 t1 left join table2 t2 on t1.date1=t2.date2 where t2.date2 is null where t2.name='Smith' and of course this doesn't work because the t2 row can't be missing if it finds 'Smith'. (I've created a Paradox-bad pun I knowg) The only solution I've found is to create a temporary table with the rows from t2 that belong to 'Smith', then run the left join on the temporary table. I'm wondering if there is a better way that doesn't involve temporary tables? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to SELECT something (CONCAT) and search the field
select first_name, lastname from user where first_name like '%$user%' or last_name like '%$user%' ; --- Matt Babineau [EMAIL PROTECTED] wrote: Hey All- Got a fun question - I hit the manual but not much luck on my question. I want to combine 2 fields and then search them SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ', last_name) LIKE '%$user%' Does this make sense? The CONCAT function was the closest I found to try and do what I want to do. I alread tried this: SELECT concat(first_name, ' ', last_name) as fullname FROM user... This did not work. If anyone has any ideas on how to search for users when the first_name and last_name fields are broken up I'm all ears! Thanks, Matt Babineau Criticalcode 858.733.0160 [EMAIL PROTECTED] http://www.criticalcode.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Make Yahoo! your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexing for OR clauses
Thanks for the suggestions over the weekend! I will be looking in to this in a few days - for now I think I am just going to have to re-write my PHP script to make 2 separate queries and array_merge() them - which invariably seems to solve OR problems. But there should be a better way, no? -Dave -Original Message- From: David Turner [mailto:[EMAIL PROTECTED] Sent: October 1, 2004 4:04 PM To: [EMAIL PROTECTED] Subject: Indexing for OR clauses Wondering if anyone can give me advice on indexing for OR clauses. I have a table with a number of fields, two of which are sender_id and receiver_id. I also have a query such as this: SELECT ... WHERE (sender_id = 98765 OR reciever_id = 98765) The query is OK for a limit of 10, but if I increase that to 25, it becomes inordinately slower (it is a large table). Is it better for me to have two separate indexes, one for each of sender_id and receiver_id, or one index with both sender_id and receiver_id? Or should I just avoid the use of OR? Or am I simply missing something? -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing for OR clauses
Wondering if anyone can give me advice on indexing for OR clauses. I have a table with a number of fields, two of which are sender_id and receiver_id. I also have a query such as this: SELECT ... WHERE (sender_id = 98765 OR reciever_id = 98765) The query is OK for a limit of 10, but if I increase that to 25, it becomes inordinately slower (it is a large table). Is it better for me to have two separate indexes, one for each of sender_id and receiver_id, or one index with both sender_id and receiver_id? Or should I just avoid the use of OR? Or am I simply missing something? -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Eliminating downtime
Has anyone architected an oltp database using mysql where downtime is virtually eliminated? I understand it's fairly simple to set up a readonly system using mysql replication or another type of replication to maintain the uptime, but I have yet to see how to do this with a heavy transaction system. Basically what I want is a guaranteed insert or update. Has anyone had any luck setting up an environment where upgrades, regular maintenance, and other activities do not interupt service? Any pointers to products or documents that will help me with this are appreciated. Dave Turner - 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: Eliminating downtime
The thing is I'm looking for a solution with guaranteed transaction success. I have thought about building a layer that would guarantee transaction success. Say I had two duplicate databases and if a transaction failed on one it would still succeed on the other. Once the transaction failed it should take the failed database out of service. I believe this could be fairly straightforward but thought I would check the list to see if someone had already built a product or setup some system that would allow for this. Dave Turner On Wed, May 29, 2002 at 05:21:39PM -0400, Moyer, Andy wrote: I don't think this is specific for MySQL - I believe you can have it monitor any processes you want it to. It also gets feedback from the system (core temperature, power fluxuations, etc). We would have it monitor Apache, MySQL, and any other core system components. The heartbeat cable is also designed so that if one system dies (and stops sending the heartbeat), the other system comes online. Also not sure about this, but I believe the systems share an IP address on the network port, but the slave doesn't enable its network port until the heartbeat dies or tells it to. If this isn't the case, they might include something to update a NAT firewall configuration on the local network, but I think it's actually the former. If it is the former, the slave is still accessible through the heartbeat cable [probably]. You can get systems like this for under $10,000, but going with IBM and super high redundancy, you're more likely to maintain the goal of 100%(99.9%) uptime. - Andy - 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
Downgrade
Are there any steps I need to take to ensure no corruption of the database if I downgrade from say 3.23.49 to 3.23.48 Thanks, Dave query,sql - 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: prefix
I guess make install prefix=/home/turner/mysql/test just isn't an option. How would I request this functionality be provided in the make file? Thanks, Dave Turner On Tue, Apr 09, 2002 at 05:45:04PM -0700, David Turner wrote: Thanks, but what I need to do is have make install install everything into /home/turner/mysql/test then I'll check everything out to make sure it is how I want it and last I'll copy it all to /usr/local. Any way to do this ./configure --prefix=/usr/local (default) make make install prefix=/home/turner/mysql/test Thanks, Dave On Tue, Apr 09, 2002 at 05:41:08PM -0700, Jeremy Zawodny wrote: On Tue, Apr 09, 2002 at 05:30:06PM -0700, David Turner wrote: I would like to make install to my home directory but can't seem to get it to work. make install prefix=/home/turner/mysql/test It keeps wanting to install to /usr/local even though I pass the prefix parm. ./configure --prefix=/home/turner/mysql/test make make install Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 61 days, processed 1,654,532,924 queries (311/sec. avg) - 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
Re: prefix
Thanks, but what I need to do is have make install install everything into /home/turner/mysql/test then I'll check everything out to make sure it is how I want it and last I'll copy it all to /usr/local. Any way to do this ./configure --prefix=/usr/local (default) make make install prefix=/home/turner/mysql/test Thanks, Dave On Tue, Apr 09, 2002 at 05:41:08PM -0700, Jeremy Zawodny wrote: On Tue, Apr 09, 2002 at 05:30:06PM -0700, David Turner wrote: I would like to make install to my home directory but can't seem to get it to work. make install prefix=/home/turner/mysql/test It keeps wanting to install to /usr/local even though I pass the prefix parm. ./configure --prefix=/home/turner/mysql/test make make install Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 61 days, processed 1,654,532,924 queries (311/sec. avg) - 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: backup databases
Check out cygwin.com. I think it's free. Great for unix guys having to run on NT. Dave On Tue, Apr 02, 2002 at 05:25:15PM -0600, Russell E Glaue wrote: On Tue, 2 Apr 2002, Mark Stringham wrote: What would the script look like if I'm on Win2k ? Good luck!! But seriously; there is a $300 software package out there (can't remember name) that will give your billy-boy OS some unix shell-like capabilities, including TAR. It was featured in like a January (or December) Network Magazine article. If your interested in knowing what this software package is, e-mail me and I'll look and get the name (and URL) to you. -RG Mark -Original Message- From: Rodney Broom [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Tuesday, April 02, 2002 12:22 PM Subject: Re: backup databases From: David McInnis [EMAIL PROTECTED] Could you share your script for doing the date thing with the rest of us? That sounds useful. From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] mysqldump -uUsername -pPassword --all-databases tmp.sql tar -cf MySQL-Backup-DATE.tar.gz -z tmp.sql rm -f tmp.sql I use a script (to determine DATE)... How about: mysqldump -uUsername -pPassword --all-databases tmp.sql \ tar -cf MySQL-Backup-`date +%Y-%m-%d`.tar.gz -z tmp.sql \ rm -f tmp.sql Try this for an example that doesn't change anything: echo MySQL-Backup-`date +%Y-%m-%d`.tar.gz --- Rodney Broom Programmer: Desert.Net sql - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql slave status
Does anyone have a script to check on the replication status of the slave to make sure it isn't falling too far behind? Thanks, Dave Turner sql - 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: LOAD DATA INFILE and how to ignore garbage lines at end of load file?
Is this a true export? Because I've never had garbage lines in my files. Dave On Mon, Mar 04, 2002 at 10:33:14AM -0600, Paul DuBois wrote: At 10:58 -0500 3/4/02, Richard Bolen wrote: I'm exporting data from Oracle and importing it into MySQL. The problem is Oracle puts garbage lines at the end of it's output files. As you've noted, the problem is Oracle. If you're using Unix, you could use tail to see how many of these lines there are and wc to count the total number of lines in the file. With that information, you can construct the proper value of n and use head -n to get only the initial part of the file that contains the non-garbage lines. Or you could reverse the order of the lines in the file (expensive?) and then use IGNORE n LINES in your LOAD DATA statement to ignore the first n lines. Better if you can get Oracle just to suppress these lines in the first place. Perhaps someone else will have a suggestion how to do that. Lines like 300 rows selected and input truncated to 9 chars as well as empty lines. When MySQL loads these files, I'm getting rows inserted for the empty lines at the end of these files. Can I get MySQL to ignore empty lines at the end of these files? or if anyone has Oracle experience can I get it to suppress the output of these line? Thanks, Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ - 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
Re: Linux optimizations for MySQL
If you do that don't you run a greater risk of corruption of the datafiles if the host unexpectedly goes down? Dave On Mon, Feb 25, 2002 at 01:07:06PM -0800, Steven Roussey wrote: I was reading an article on speeding up Oracle on Linux(1) and thought their two optimizations for Linux would work for MySQL as well. Would: chattr -R +A mysql_datadir_path help? Also, what about editing /ect/sysctl.cong file and add an entry to improve filesystem performance, as follows: vm.bdflush = 100 1200 128 512 15 5000 500 1884 2 Would this work as well in 2.4.16+ kernels (tweaking the numbers accordingly for your system)? Sincerely, Steven Roussey http://Network54.com/?pp=e (1) http://www.linuxjournal.com/article.php?sid=5840 - 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
Re: Linux optimizations for MySQL
I thought there was a problem with the datafile corruption if the cache was lost when you chattr a file. That the file would become corrupt. Dave On Mon, Feb 25, 2002 at 03:55:23PM -0800, Steven Roussey wrote: I assume you are talking about bdflush only at 100% (I'm actually trying 90%). Thing is, all our tables are constantly changing. Unless the server crashes just after a flush tables command, the tables are likely to get corrupt anyhow. The data altering flow of queries is over 1000/s at the moment. Right now I want every last ounce of speed while our external RAID array is being rebuilt. 2 disk SCSI RAID 0 is not enough. 4 disk SCSI RAID 0 or 4 disk SCSI 0+1 is much better. Sincerely, Steven Roussey http://Network54.com/?pp=e -Original Message- From: David Turner [mailto:[EMAIL PROTECTED]] If you do that don't you run a greater risk of corruption of the datafiles if the host unexpectedly goes down? Dave On Mon, Feb 25, 2002 at 01:07:06PM -0800, Steven Roussey wrote: I was reading an article on speeding up Oracle on Linux(1) and thought their two optimizations for Linux would work for MySQL as well. Would: chattr -R +A mysql_datadir_path help? Also, what about editing /ect/sysctl.cong file and add an entry to improve filesystem performance, as follows: vm.bdflush = 100 1200 128 512 15 5000 500 1884 2 Would this work as well in 2.4.16+ kernels (tweaking the numbers accordingly for your system)? Sincerely, Steven Roussey http://Network54.com/?pp=e (1) http://www.linuxjournal.com/article.php?sid=5840 - 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 mysql-unsubscribe- [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
Re: max suffice
On Sat, Feb 23, 2002 at 12:39:43AM +0100, [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I was curious. I thought you needed --with-server-suffix=-max to use innodb and berkeley db, but a friend of mine says he has been using those table types fine without them. If this is the case what does the flag do? Thanks, Dave - 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: ORDER BY an ABSolute value
select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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
Re: ORDER BY an ABSolute value
Paste your sql exactly as it is and your version of mysql. Dave On Tue, Feb 12, 2002 at 10:08:10AM +1000, David Mackay wrote: Thanks for your quick response Dave, Have tried this, but no bannana... I get: You have an error in your SQL syntax near 'abs(id)' at line 1 Seems a not-valid thing to do these days... Is there a work around? Dave from Oz select id from table_name order by abs(id); Dave Dundee! On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote: G'Day folks, New to PHP/MySQL. Want to order the results of a SELECT by their 'absolute' value, not their sign. So regardless of whether it's +37 or -37, they are both 37 and thus both come between 38 36, regardless of whether they're + or -. eg. 100 -99 -92 91 72 -38 37 -37 etc... I have the order DESC but it puts 100 first, and -100 last, (that's what you'd usually want, but I need the extremes grouped at one end, down to the middle point zero..). In my fantasy world I could do a ORDER BY ABS(number) DESC If there was any other way to achieve the same result that would be just dandy... I bet this is obvious. Thanks for your time, David Mackay - 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
Re: Missing values in an INT type column
MAX_EXTENSION= select a.id + 1 from dude a left join dude b on a.id +1 = b.id where b.id is null and a.id MAX_EXTENSION; You'll have to have one record in the table for this to work. I've done a better job of this in Oracle because of nested queries, but I think this could give you a good start in mysql. Dave On Fri, Feb 01, 2002 at 10:03:08AM +1000, Neil Silvester wrote: I hope this doesn't sound like a stupid question. Is there anyway of finding missing values in an INT column type? Say I have a telephone extension guide. Using the MIN() and MAX() functions I am able to find the start and end ranges. But what about finding the extensions that are not allocated. At the moment I am thinking I will need to use an external script that will query the individual entries in the array of my returned result (SELECT extensions FROM table ORDER BY extensions). If the difference between consecutive rows row[x] and row[y] is +1 (or negative depending on ascending or descending ordering), then all is good. But if the result is +x, I will need to echo an array to the screen of row[x] ++1 while row[x] row[y]. It will take some work, but shouldn't be too hard to implement. I guess my question is, is there a function in MySQL that will do this, or at least help me along with it, or has anyone come across a need for this before. Neil Silvester Webmaster Heat and Control Ph: +61 7 3877 6429 [EMAIL PROTECTED] http://www.heatandcontrol.com - 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
Run a file from mysql prompt
How do I get a file full of sql commands to run from the mysql prompt? In oracle I would type @thefilename I know how to do run the file from the unix prompt mysql thefilename.sql Thanks, Dave - 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: Run a file from mysql prompt
Cool, thx. Dave On Fri, Feb 01, 2002 at 01:21:18PM -0600, Paul DuBois wrote: At 11:12 -0800 2/1/02, David Turner wrote: How do I get a file full of sql commands to run from the mysql prompt? mysql source filename; or mysql \. filename; In oracle I would type @thefilename I know how to do run the file from the unix prompt mysql thefilename.sql Thanks, Dave - 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
Re: three-way join
Sorry not real familar with MYSQL syntax yet but I think you'll be able to translate. select m.magazinename from s,m,b where s.name = 'fred' and s.id=b.subscriberid and m.id=b.magazineid ; On Wed, Jan 23, 2002 at 03:59:11PM -0700, Christopher Thompson wrote: At 02:51 PM 1/23/2002 -0800, you wrote: i understand that creating a M:N relationship in mysql involves 3 tables, one of which being a bridge, but how do i join the first and third tables by way of a bridge? example: a table of subscribers and a table of magazines with a bridge in the middle. how do i select just the magazines fred is subscribed to? thanks in advance and sorry if this is a confusing question. Table S (subscribers) ID Name Table M (magazines) ID MagazineName Table B (bridge) SubscriberID MagazineID Sample data in S: 1 - Fred 2 - John 3 - Chris Sample data in M: 1 - Playboy 2 - Computer Gaming World 3 - Maxim Sample data in B: 1 - 1 1 - 3 2 - 1 2 - 2 3 - 2 How do you select just the magazines Fred subscribes to? SELECT MagazineName FROM M, B WHERE M.ID = B.MagazineID AND B.SubscriberID = (Fred's ID); Or something. My SQL is a little rusty, it's been about six months since I used it last. - 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
Re: Backups
Another suggestion I saw someone make that seems reasonable is to use a break away mirror for backups. I think they lock the tables for a minute break the mirror and unlock the tables. Then they backup the broken mirror at their leisure. Dave On Tue, Dec 18, 2001 at 06:00:49PM -0500, Dave Greco wrote: Looking over the documentation for MySQL, there doesn't seem to be a consensus on the best way to perform backups of MySQL databases. So far, I have come up with the following ideas: 1. Just copy the files in the data directory to wherever I want them backed up. This doesn't work too well because the files are in use as I copy them. 2. Shutdown the database server, copy the actual files in the data directory. I can't use this method because I can't have the database down for the amount of time it would take to copy this data. At least, I can't have the database server down like this on a daily basis. 3. Perform a database dump (using mysqldump) of the data to a file. The problem with this is that the data is very big in that format, and the backup takes a long time. 4. Perform a hot copy of the databases (using mysqlhotcopy). This also seems to take a while, but at least the database server does not have to shutdown. The trouble with this is that it locks the table from writes when it does the copy. Some of my tables take a while to copy (they are hundreds of MBs in size), and are written to quite often, so this option (while the best so far) also has its shortcomings. 5. Use MySQL replication to create another database server with the identical data. Then shutdown the database server on that machine and run a backup (like in #2). This way will not require shutting down the real backup server at all, so therefore the backup can take as long as I want. The downside to this approach is that the data being backed up is not necessarily the most up-to-date. If the backup takes 2 hours, the databases near the end of the backup can have some significant changes by then. However, this disadvantage is diminished because when the backup is done, I restart the slave server and the data replicates back down again. So, we have online database backups on the actual slave server and then we do daily (probably could be every couple of hours) tape backups of the data on the slave. Does anyone see a problem with idea #5? Does anyone have any better ideas for performing backups? Dave Greco [EMAIL PROTECTED] Network Administrator Gleim Publications, Inc. (800) 87-GLEIM x312 http://www.gleim.com - 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
Re: sequence and nextval
Try sequences.
Dave
On Thu, Dec 13, 2001 at 06:18:32PM -, Matthew Smith wrote:
the SELECT LAST_INSERT_ID() gets the last autoincremented number for the
current connection.
See http://www.mysql.com/doc/G/e/Getting_unique_ID.html
The auto_incremenet field is sadly lacking in Oracle (IMHO).
M
-Original Message-
From: Henrik Erlandsson [mailto:[EMAIL PROTECTED]]
Sent: 13 December 2001 17:25
To: [EMAIL PROTECTED]
Subject: SV: sequence and nextval
Thanks, but how secure is this. Is it possible for two computers to do
the select at the same time, i.e. get the new incremented value?
Computer one insert, computer two insert, computer one select, computer
two select?
If this is possible how can I solve this? To make the column that insert
the new nextval unique and then check for errors (non-unique insert) and
if error then re-ask for a new nextval? Is this the best solution?
Thanks / Henrik
---
create table MySequence ( nextval int(10) not null default '0'
auto_increment ) ;
insert into MySequence values ('');
select LAST_INSERT_ID();
is what I use
M
-Original Message-
From: Henrik Erlandsson [mailto:[EMAIL PROTECTED]]
Sent: 13 December 2001 15:41
To: [EMAIL PROTECTED]
Subject: sequence and nextval
Does the above functions exist in MySQL? If not, when do you think it's
going to be implemented and how can I simulate nextval the easiest way?
/ Thanks Henrik
-
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
-
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: Circular replication
Just tell your boss that if you ever have turnover the former employee will be able to log into all the customers' accounts and do whatever he wants. Dave On Thu, Dec 13, 2001 at 03:29:41AM +1100, Duncan Maitland wrote: My questions concern a setup where a public server is running at our hosting company and a local office server is behind a firewall (connected to the net via a somewhat unreliable ADSL). The servers are configured in a circular master-slave relationship but only a limited number of tables in the database are replicated between the two (public doesn't need all of them, so no use in replicating). Of these tables only 3 need to accept writes from both the public and office server (all the other writes happen at the office). Of those 3 tables only 1 makes use of a unique primary key. So my questions are: 1) Replicating a table with a primary key raises the possibility of conflicts if, while the office link is broken, two records are created with the same key. So I plan to generate my own keys in the project source code (without auto_increment) - the public site generates records with even numbers, the office site with odd numbers. Is this a reasonable setup or is there a more correct way? Out of interest, how will MySQL 4.0 replication handle this situation? 2) MySQL docs state It is possible for client A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client B could make an update to co-master 2 that will make the update of client A work differently than it did on co-master 1. Thus when the update of client A will make it to co-master 2, it will produce tables that will be different than what you have on co-master 1, even after all the updates from co-master 2 have also propagated. Say the office link is down, and a particular record in the above-mentioned table is edited on both the public and office servers. When the servers re-sync will one record take precedence (if so, which one?) or does the public get one and the office get the other? The former seems to be the case when doing basic testing on my LAN at home, but the MySQL doc is confusing in that it implies the latter. To those of you who have read all the way down to here, I thank you very much! :) Cheers, from Duncan Maitland [EMAIL PROTECTED] - 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
Re: Very large table load/index questions
I've always thought it would be great if there was a way to drop an object but keep the meta data so I can recreate objects without having to store the ddl somewhere. alter table drop index retain metadata Would be helpful. Dave On Tue, Dec 04, 2001 at 11:19:43AM -0500, Robert Alexander wrote: Hi Barry, It is, indeed, faster to load the data then create the indexes. Instead of 'disable' and 'enable', you could try: - ALTER TABLE DROP index (or drop the table and create it without indexes.) - load your data - ALTER TABLE ADD index HTH, /Rob At 09:40 -0500 2001/12/04, Barry Roomberg wrote: The actual load of a single table takes about 1/2 hour, but indexing takes DAYS. I've recompiled '--with-raid' which allows me to create a table big enough. snip According to the docs, it seems that the following sequence should be faster: Create WITH indexes defined. Alter table disable indexes. Load data. Alter table enable indexes. The 'enable' and 'disable' seem to be strictly a MySQL 4.0 command, which I didn't initially DL, so I'm setting that up now. ~ Robert Alexander, Alpha Geek, Workmate.ca WWW Database Applications and Web Hosting http://www.workmate.ca 416-823-6599 mailto:[EMAIL PROTECTED] Life's unfair - but root password helps! - 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
Re: Very large table load/index questions
Thanks, but I'm looking at having this for all objects drop table mytable retain metadata; etc Dave On Tue, Dec 04, 2001 at 06:52:17PM +0100, Sergei Golubchik wrote: Hi! On Dec 04, David Turner wrote: I've always thought it would be great if there was a way to drop an object but keep the meta data so I can recreate objects without having to store the ddl somewhere. alter table drop index retain metadata Would be helpful. That's what ALTER TABLE ... DISABLE KEYS is for Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL performance and limit
BTW, this is unique in MySQL - you can have tables mixed to be transactional (InnoDB) and nontransactional (MyISAM) and use them mixed in same query. All other SQL-s (as much I know) have transactions on Oracle's Global Temporary Tables don't write to redo or rollback, and on normal tables you can alter table tablename nologging to shut transaction logging off. Dave every table even if user don't need this. But usually tables contain different information, they have different usage and this affects things :) I do not know about ext3 much but rumours talk that it is just some kind of add-on on top of ext2. We have compared ext2 and ReiserFS when latter came out and ReiserFS was in some tests 30% faster in writes. Also ReiserFS can handle issues with directories containing 1 files much faster. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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
Re: MySQL performance and limit
No problem, MYSQL is great and I know far less about it than I do Oracle. Dave On Fri, Oct 05, 2001 at 06:08:07PM +0200, Tonu Samuel wrote: On Fri, 2001-10-05 at 17:06, David Turner wrote: BTW, this is unique in MySQL - you can have tables mixed to be transactional (InnoDB) and nontransactional (MyISAM) and use them mixed in same query. All other SQL-s (as much I know) have transactions on Oracle's Global Temporary Tables don't write to redo or rollback, and on normal tables you can alter table tablename nologging to shut transaction logging off. Thank you for information. As you see I have not much Oracle knowledge :( -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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
Re: Creating indexes on large tables
If this is the case. What are the steps necessary for index rebuilds? Can I specify where the index file is rebuilt? Any idea when we can specify the location of datafiles and indexfiles? Thanks, Dave On Wed, Oct 03, 2001 at 07:57:35AM -0700, Adams, Bill TQO wrote: Perhaps your index file (.MYI) is growing larger than 2GB, the file-size limit on 2.2.x kernels? Or, if you have moved the tables with symlinks, MySQL will put the new file (most of the time?) in the configured data directory and not where the symlink points. --Bill Mike Lucente wrote: I'm running out of space while creating indexes on some fairly large (1.8 GB) tables, even though I have quite a bit of space available in the partition (utilization is at 30%). I know that the create process works as follows (from the manual): Create a new table named `A-xxx' with the requested changes. All rows from the old table are copied to `A-xxx'. The old table is renamed `B-xxx'. `A-xxx' is renamed to your old table name. `B-xxx' is deleted. Given that, I should have plenty of room. Disk utilization doesn't appear to exceed 60% during the process. Any idea what could be causing this? ** Running RH 7.1 on a Dell 2450. MySQL version 3.23.29a-gamma-log. - 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 -- Bill Adams TriQuint Semiconductor - 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
Re: RE: Info Needed to Promote MySQL!!
I've had the same issues. I have really avoided the dev side of things for some time so I'm not sure whether there's a way around it. Mainly just devoting myself to administration. Funny though I'm starting to write jsp stuff to monitor the databases more effectively. I agree with you on the MYODBC from unix to windows and vice versa. Shouldn't be that different. Dave On Mon, Sep 24, 2001 at 03:09:50PM -0600, Adam Douglas wrote: What type of schema do mean here ? Because as you know, MySQL doesn't explicitly supports any schema's except the catalogs( in MySQL term 'database'). If you can elaborate what exactly the problem you are facing, then we can provide a rapid solution. Also, as for as I know, when I change something in my table in MySQL server side (including the table structure), it does reflect on the linked tables without any problems, and vice versa too. I have the following setup... - MySQL 3.22.32 on OpenBSD v2.9 - MyODBC on Windows '98 SE - MS Access 2000 Now I've found when I do changes to a table schema the changes are not entirely at least reflected in a the same table linked in Access. I had once case where I changed a data type on one of the columns and that data type was not changed on the linked table in Access. I had to remove the link and then recreate it. The table type I'm using is MyISAM. From my experience with MyODBC it seems that it's not a live link correct? This problem has occurred on three machines including mine. Do I have something setup wrong then? Is there anything else that you need to know? Also if I may ask, is there any difference between MyODBC for Unix going to Windows to MyODBC for Windows going to Unix? I'm assuming that all the difference is the starting point but achieves the same tasks. - 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
Re: RE: Info Needed to Promote MySQL!! - Solved
On Mon, Sep 24, 2001 at 03:02:42PM -0700, Venu wrote: Hi Another small research in Access made me to see the changed table structure completely. Please use this procedure to view or to refresh links when the structure or location of a linked table has changed. 1. Open the database in access that contains links to tables. 2. On the Tools menu, point to Database Utilities, and then click Linked Table Manager. 3. Select the check box for the tables whose links you want to refresh. 4. Click OK to refresh the links. Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of table name dialog box in which you can specify the table's new location. If several selected tables have moved to the new location that you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step. So, this solves all your problems. Let me know whether it helps you or not. Regards, venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu mailto:[EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com -Original Message- From: Adam Douglas [mailto:[EMAIL PROTECTED]] Sent: Monday, September 24, 2001 2:43 PM To: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; Mysql@Lists. Mysql. Com (E-mail) Subject: RE: RE: Info Needed to Promote MySQL!! Now I've found when I do changes to a table schema the changes are not entirely at least reflected in a the same table linked in Access. I had once case where I changed a data type on one of the columns and that data type was not changed on the linked table in Access. I had to remove the link and then recreate it. The table type I'm using is MyISAM. From my experience with MyODBC it seems that it's not a live link correct? This problem has occurred on three machines including mine. Do I have something setup wrong then? Is there anything else that you need to know? Ok. I tried now, and even for me the latest table strcuture is not visible in the linked tables. I am using Access 2002. But I can see the inserted/deleted rows of data in the linked table after I do the refresh from records menu. If it doesn't work, you need to use filters from record menu as follows: - Refresh - Apply filter / sort - Remove filter / sort I can see inserted/deleted rows as well it's just the schema issue that bothers me and was wondering if this was normal. I will crosss check with the MyODBC code and Access calls, why it is not able to display the latest changes to the table structure, and if I find something, let you know. Also, what about other vendors ? Does Access displays the changed table structure in linked tables ? That would be great, thanks! - 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: RE: Info Needed to Promote MySQL!! - Solved
What I did to handle global changes was have one access file on the fileserver that everyone had a shortcut to. That way the updates were global since they were all to the same file. Dave On Mon, Sep 24, 2001 at 04:19:55PM -0600, Adam Douglas wrote: Another small research in Access made me to see the changed table structure completely. Please use this procedure to view or to refresh links when the structure or location of a linked table has changed. 1. Open the database in access that contains links to tables. 2. On the Tools menu, point to Database Utilities, and then click Linked Table Manager. 3. Select the check box for the tables whose links you want to refresh. 4. Click OK to refresh the links. All I can say is, Wow! Excellent response! Thanks! So, this solves all your problems. Let me know whether it helps you or not. Looks like it works great but how about this (smile).. Is there a way to have this update done on linked tables automatically and is it possible to have this done on a global scale. Meaning that the linked tables would work on all machines not a specific machine? Would having a general ODBC username/password setup on multiple machines resolve the problem? Sorry meant to ask this question before. - 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
Error Compiling mysql under Solaris
Does anyone have a good configure statement I could try for making MYSQL on Solaris for Sparc? I've got it compiling on X86 but I get a parser error I've only seen mention of once on the lists, and I did not see how to identify the reserved word I am using? I'll continue digging around, but if someone has a quick fix I would really appreciate it. Here's the error sql_yacc.yy, line 71: fatal: invalid escape, or illegal reserved word: pure_parser Os and mysql versions: 5.7 Generic_106541-09 sun4u sparc SUNW,UltraSPARC-IIi-cEngine mysql-3.23.41 Thanks, Dave - 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: Stored Procedures and Triggers
A friend of mine mentioned something about perl stored procedures for MYSQL. Has anyone heard about this? I have searched everywhere and only seen posts related to POSTGRESQL. If they have this for MYSQL it would be really helpful. Thanks, Dave On Mon, Sep 03, 2001 at 01:37:23AM -0700, Jeremy Zawodny wrote: On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui wrote: How many types of triggers MySQL have? And what about stored procedures? Zero and Zero, as explained in the docs. If you need them today, I'd suggest looking at PostgreSQL. If you're patient, they'll likely appear in MySQL someday--I believe both are on the TODO list. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg) - 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
Re: Stored Procedures and Triggers
Great thanks, I'll check it out. Dave On Mon, Sep 03, 2001 at 01:49:31PM -0700, Steve Edberg wrote: At 1:10 PM -0700 9/3/01, David Turner wrote: A friend of mine mentioned something about perl stored procedures for MYSQL. Has anyone heard about this? I have searched everywhere and only seen posts related to POSTGRESQL. If they have this for MYSQL it would be really helpful. That would be MyPerl: http://software.tangent.org/ It looks to be very early on in development, so I'm not sure how much I'd rely on it... -steve Thanks, Dave On Mon, Sep 03, 2001 at 01:37:23AM -0700, Jeremy Zawodny wrote: On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui [EMAIL PROTECTED] wrote: How many types of triggers MySQL have? And what about stored procedures? Zero and Zero, as explained in the docs. If you need them today, I'd suggest looking at PostgreSQL. If you're patient, they'll likely appear in MySQL someday--I believe both are on the TODO list. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg) -- + Open source questions? + | Steve Edberg University of California, Davis | | [EMAIL PROTECTED] Computer Consultant | | http://aesric.ucdavis.edu/ http://pgfsun.ucdavis.edu/ | +--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+ - 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: Berkeley or InnoDB?
Can you explain this further? Dave On Sat, Sep 01, 2001 at 12:52:43PM +0400, Ilya Martynov wrote: AM I'm porting an application to MySQL and I need to support transactions. AM I will appreciate so much if someone could give me some impressions about AM which one is better. BerkeleyDB or InnoDB? I belive that at least for big databases InnoDB is better because BerkeleyDB perfomance is good only if databases fit RAM. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.com/) | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - 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
Re: Changing Database Location ?
This is one thing I really can't stand about mysql. We should have the option when creating databases and tables of specifying where the database is located and the datafile and indexfile. Dave On Fri, Aug 31, 2001 at 07:03:40PM +0200, Simon J Mudd wrote: On Fri, 31 Aug 2001, Peter Moscatt wrote: Can you change the location of a MySQL database ? The default is '/var/lib/mysql'. The easy way I've always used is to change the name of the /var/lib/mysql directory and make a symbolic link to another directory. You need to reset up the mysql database and check the permissions but this at least avoids you having to check and/or mess about with the script start up options. There may be better ways, but this works fine. Regards, Simon -- Simon J Mudd, Madrid SPAIN. email: [EMAIL PROTECTED] Tel: +34-91-408 4878, Mobile: +34-605-085 219 - 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
Re: Hot Backups
If your SLA allows for inconsistent data you're covered. Dave On Thu, Aug 30, 2001 at 05:41:43PM -0600, Matthew Walker wrote: I just got mysqldump working. And it doesn't necessarily lock the tables before it does the dump, so it doesn't interrupt service unless you tell it to. Thanks for everyone's help. I now have a working backup script. -Original Message- From: David Turner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 29, 2001 1:00 PM To: Joshua J. Kugler Cc: David Turner; Matthew Walker; MySQL Mailling List Subject: Re: Hot Backups Didn't read your last item before I fired my last message off. If mysqldump locks the table before it dumps then it is interupting service. If I have a high number of transactions they're blocked until the lock is released. Dave On Wed, Aug 29, 2001 at 10:52:26AM -0800, Joshua J. Kugler wrote: I'm doing a mysqldump and then backing up the resulting text file. Works fine, doesn't interupt anything, and doesn't have any problems with a live server. IIRC, mysqldump locks a table before it dumps, so there won't be any funny records. Or course, mysqldump could catch the database in the middle of a multi-table backup. But so can any live backup system. j- k- On Wednesday 29 August 2001 10:52, David Turner wrote: The way I am planning on doing it is setting up another server that I replicated to and backing that server up. I would like to hear how other people are backing up servers without interupting service. Dave On Wed, Aug 29, 2001 at 12:50:06PM -0600, Matthew Walker wrote: What's the best way of doing a hot backup on a database? Our server is set up in such a way that it's not a simple matter to shut down apache/mysql and do the backup then. So, is it safe to just copy the mysql directories, or is there some recommended procedure for this? -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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
Re: multiple PRI indexes
He may mean a composite primary key, which is a primary key based on two columns. On whether this is a good idea or not it's really a question of whether you want to use natural or surrogate keys on your tables. A composite key is typically a natural key. A natural key is a key with information attached to the columns in the key. Examples of a natural key would be a user's email address. The problem with natural keys is that the data in the key frequently needs to be changed. In the example of an email address as the key you would have to update the key whenever anyone changed their email. Also on performance , joins tend to be slower because the natural keys take more space then say a number to uniquely identify the row. I prefer surrogate keys which are keys that don't have any information tied to them. They are simply a sequence or autoincrement. Their only purpose is to to uniquely identify the row. They're better on join performance and you don't find people updating them because of changes in the data. If I were he, I would create an additional autoincrement field for the primary key and create a unique index on the two fields he planned on being the primary key. Dave Turner On Wed, Aug 29, 2001 at 12:22:22PM -0500, Paul DuBois wrote: At 10:33 AM -0500 8/29/01, shawn reed wrote: is it generally recommended / a good idea to have more than one PRI index in the same table? i've never seen that before until today while trying to track down bugs in someone else's system and noticed a table with 2 PRI indexes. will this cause any problems? ? In MySQL, you cannot have two PRIMARY KEY indexes. You can have a PRIMARY KEY and and a UNIQUE index, or two UNIQUE indexes, but that's a slightly different situation. tia. ~shawn - 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 -- Paul DuBois, [EMAIL PROTECTED] - 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
Re: Hot Backups
The way I am planning on doing it is setting up another server that I replicated to and backing that server up. I would like to hear how other people are backing up servers without interupting service. Dave On Wed, Aug 29, 2001 at 12:50:06PM -0600, Matthew Walker wrote: What's the best way of doing a hot backup on a database? Our server is set up in such a way that it's not a simple matter to shut down apache/mysql and do the backup then. So, is it safe to just copy the mysql directories, or is there some recommended procedure for this? Matthew Walker System Administrator Mountain Top Herbs - 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
Re: Hot Backups
Any problems with data inconsistencies? Dave On Wed, Aug 29, 2001 at 10:52:26AM -0800, Joshua J. Kugler wrote: I'm doing a mysqldump and then backing up the resulting text file. Works fine, doesn't interupt anything, and doesn't have any problems with a live server. IIRC, mysqldump locks a table before it dumps, so there won't be any funny records. Or course, mysqldump could catch the database in the middle of a multi-table backup. But so can any live backup system. j- k- On Wednesday 29 August 2001 10:52, David Turner wrote: The way I am planning on doing it is setting up another server that I replicated to and backing that server up. I would like to hear how other people are backing up servers without interupting service. Dave On Wed, Aug 29, 2001 at 12:50:06PM -0600, Matthew Walker wrote: What's the best way of doing a hot backup on a database? Our server is set up in such a way that it's not a simple matter to shut down apache/mysql and do the backup then. So, is it safe to just copy the mysql directories, or is there some recommended procedure for this? -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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: Hot Backups
Didn't read your last item before I fired my last message off. If mysqldump locks the table before it dumps then it is interupting service. If I have a high number of transactions they're blocked until the lock is released. Dave On Wed, Aug 29, 2001 at 10:52:26AM -0800, Joshua J. Kugler wrote: I'm doing a mysqldump and then backing up the resulting text file. Works fine, doesn't interupt anything, and doesn't have any problems with a live server. IIRC, mysqldump locks a table before it dumps, so there won't be any funny records. Or course, mysqldump could catch the database in the middle of a multi-table backup. But so can any live backup system. j- k- On Wednesday 29 August 2001 10:52, David Turner wrote: The way I am planning on doing it is setting up another server that I replicated to and backing that server up. I would like to hear how other people are backing up servers without interupting service. Dave On Wed, Aug 29, 2001 at 12:50:06PM -0600, Matthew Walker wrote: What's the best way of doing a hot backup on a database? Our server is set up in such a way that it's not a simple matter to shut down apache/mysql and do the backup then. So, is it safe to just copy the mysql directories, or is there some recommended procedure for this? -- Joshua Kugler, Information Services Director Associated Students of the University of Alaska Fairbanks [EMAIL PROTECTED], 907-474-7601 - 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: replace/insert into ... 150,000 -plus rows
Besides that theoretical stuff, though, the real problem is that INSERT is for inserting new records into a database table. What you're trying to do is UPDATE existing records with new data. And there's no INSERT ... SELECT counterpart in the UPDATE syntax. What about replace? replace tablenamewithbaddata select * from tablenamewithgooddata; It uses the unique index to decide whether or not to delete the old records and insert, or to just insert the new record. I would like more flexibility in replace but it might help with this. I would make copies of your tables before playing with this. Dave What you probably need to do is write a program in some language (Perl, Python, C/C++) that can do some of the work for you. It's probably not going to be easy just using SQL statements. You might be able to get part of the way there by copying the good data into one table, the bad data into another, and merging the results, but it's not the way I'd approach the problem. I'll not trust this task to anyone other than myself. Then why are you asking us? :-) Hope this helps in some way... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 4 days, processed 60,117,446 queries (139/sec. avg) - 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
Re: Failover
Yes, that's probably where we'll end up. Thanks, Dave On Tue, Aug 21, 2001 at 03:28:51PM -0700, Jeremy Zawodny wrote: On Mon, Aug 20, 2001 at 01:21:22PM -0700, David Turner wrote: It looks like this will only run on Linux and I must either use Sparc Solaris or X86. I hate to implement the failover within the application. Perhaps you need some tiny piece of middleware that acts as a MySQL proxy to do the work, then. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 4 days, processed 56,808,794 queries (141/sec. avg) - 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: Failover
You would think this would get built into MYSQL though. Dave On Tue, Aug 21, 2001 at 03:28:51PM -0700, Jeremy Zawodny wrote: On Mon, Aug 20, 2001 at 01:21:22PM -0700, David Turner wrote: It looks like this will only run on Linux and I must either use Sparc Solaris or X86. I hate to implement the failover within the application. Perhaps you need some tiny piece of middleware that acts as a MySQL proxy to do the work, then. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 4 days, processed 56,808,794 queries (141/sec. avg) - 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
Failover
I have two identical primarily readonly databases that I want to be able to upgrade on the fly. What I want to be able to do is take one down and have all my connections redirected to the second database automatically. I would also like the same auto failover when a database crashes, because of cpu, disk, etc. In oracle I can setup tnsnames failover. Is there a similar mechanism in MYSQL to redirect failed connections to a secondary database? Thanks, Dave Turner - 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: Failover
Great, thanks, Dave On Mon, Aug 20, 2001 at 01:04:10PM -0700, Jeremy Zawodny wrote: On Mon, Aug 20, 2001 at 09:28:20AM -0700, David Turner wrote: I have two identical primarily readonly databases that I want to be able to upgrade on the fly. What I want to be able to do is take one down and have all my connections redirected to the second database automatically. I would also like the same auto failover when a database crashes, because of cpu, disk, etc. In oracle I can setup tnsnames failover. Is there a similar mechanism in MYSQL to redirect failed connections to a secondary database? There is not mechanism built into MySQL currently. You might look at the Linux Virtual Server (LVS) for a solution. I believe it'll give you just what you're looking for... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 44,686,278 queries (146/sec. avg) - 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: Failover
It looks like this will only run on Linux and I must either use Sparc Solaris or X86. I hate to implement the failover within the application. Thanks anyway, Dave On Mon, Aug 20, 2001 at 01:04:10PM -0700, Jeremy Zawodny wrote: On Mon, Aug 20, 2001 at 09:28:20AM -0700, David Turner wrote: I have two identical primarily readonly databases that I want to be able to upgrade on the fly. What I want to be able to do is take one down and have all my connections redirected to the second database automatically. I would also like the same auto failover when a database crashes, because of cpu, disk, etc. In oracle I can setup tnsnames failover. Is there a similar mechanism in MYSQL to redirect failed connections to a secondary database? There is not mechanism built into MySQL currently. You might look at the Linux Virtual Server (LVS) for a solution. I believe it'll give you just what you're looking for... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 3 days, processed 44,686,278 queries (146/sec. avg) - 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: Help with a simple query ..
Try this. I couldn't find if MYSQL supports not in's so I followed there outer joins. You can read up on in in the mysql manual, do a search on outer joins. select base.zipcode,properties.zipcode from base right join properties on base.zipcode=properties.zipcode where base.zipcode is null; On Mon, Aug 20, 2001 at 04:39:11PM -0400, Chad Day wrote: I have 2 tables: bases, and properties base has a ZIPCODE field in the table, as does the properties table. I'm trying to find all bases that do NOT have a property in that zip code. What I've tried is: select distinct cb.*, cp.* from classified_bases as cb left join classified_properties as cp ON cb.ZIP != cp.ZIPCODE where cb.BASEORLOC = 'B' and cb.STATE = 'VA' AND cb.STATE = cp.STATE; but this returns 4 entries for every base (except the one where there is a record, where it returns 3), since there are 4 entries in the properties table. I know WHY it's doing it, it's comparing every base record to each single record in the property table .. I just don't know how to fix it to do what I want. I tried to explain this as best I could, if I need to clarify it some more, let me know. Thanks, Chad - 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
Re: Downsides of MySQL?
No offense but I've seen people on the list throwing replication around like MySQL has replication end of story. There's alot more to replication than just a master and a slave. What MySQL has is simple unidirectional replication. If you want advanced or bidirectional replication you'll have to be able to handle issues such as conflict resolution , queuing, etc. I think a mixed bag approach of Oracle and Mysql would probably be a good solution, but better yet, get the manager to really define what he wants to do. You may find the unidirectional replication is sufficient. Dave No surprise that these folks haven't been following MySQL development for quite a while, and probably don't know about its replication features. I haven't used 'em myself, though, so I can't vouch for their robustness. As far as the feature set manageability, it's true - there's a lotta things MySQL made a conscious decision to leave out (unions, views, triggers, stored procedures, subselects [i know, coming soon], foreign key support, etc.) in favor of speed/small memory footprint. And you have to go to third-parties for reverse-engineering/diagramming tools. If your application requires such, then maybe MySQL _isn't_ the right solution; however - depending on your app - Oracle/DB2/whatever might be sheer overkill. Administrative overhead for systems like those might far outweigh any advantages they have for you. There are question marks around the scalability of the product, I'm not sure of the locking algorithms used (whether row level or record level) - the It depends on table type; AFAIK, it can be table (ISAM/MyISAM), page-level (BDB), or row-level (InnoDB). See: http://www.mysql.com/doc/L/o/Locking_methods.html http://www.mysql.com/doc/T/a/Table_locking.html http://www.mysql.com/doc/I/n/InnoDB_Next-key_locking.html You've got a choice! This used to be considered a good thing... fact that it is not generally used in multi-user solutions is a good enough indication that this is not accepted database technology for industrial-strength multi-user systems. The fact that it is unsupported freeware would mean that an end user would potentially be held to ransom by a DBA with specific knowledge. This kinda of statement is beginning to REALLY rile me when I hear it. Even if you discount the fact that this mailing list provides better support than the majority of PAID support programs, if you want to, the MySQL folks would be more than happy to take a large amount of your $$$ to provide excellent support: http://www.mysql.com/support/arrangements/types.html - this can include customizing MySQL for you! There are also individual consultants firms that will support you as well. How anyone could actually back up a claim of MySQL being 'unsupported' is beyond me. The mySQL security model is also not sufficiently developed for any system that involves money. I dunno, with some combination of encrypted fields, database server behind a firewall, SSH-tunnelled communication and good DB/system administration, you'd have a plenty secure system. After all, I don't think any of the recent and not-so-recent credit-card number thefts have been on MySQL systems. OK, back to work for me. But first, some Mountain Dew... -steve -- + Open source questions? + | Steve Edberg University of California, Davis | | [EMAIL PROTECTED] Computer Consultant | | http://aesric.ucdavis.edu/ http://pgfsun.ucdavis.edu/ | +--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+ - 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
