two-column indexes and joins with ranges
I have a performance/index usage problem, and I am hoping somebody can tell me why this problem exists, and whether there is a better solution than the workaround that I'm using now. The problem: I have a table with a two column index, such as CREATE TABLE transactions ( account_id INT NOT NULL, when DATETIME NOT NULL, INDEX(account_id, when) ); I frequently need to do selections like: SELECT * FROM accounts, transactions WHERE accounts.name = 'dave' AND accounts.account_id = transactions.account_id AND when BETWEEN '2004-02-05' AND '2004-02-10'; If dave has a huge number of transactions (and some of the accounts do), this runs very slowly. EXPLAIN tells me that only the account_id part of the transactions index is being used, with the ref index usage type - apparently, every single one of dave's transactions is being examined, and the when BETWEEN ... part of the join is only being looked at after fetching the rows, even though the index itself had all the data needed! If I do this query in two parts, like this: SELECT account_id FROM accounts WHERE name = 'dave'; SELECT * FROM transactions WHERE account_id = dave's account ID AND when BETWEEN '2004-02-05' AND '2004-02-10'; Suddenly the performance is great, and the full index is used, with a range type of index usage. The MySQL documentation says that the range type can only be used on constants - but I'm wondering, why does it count this as non-constant? The dates I'm using are constant, so it seems that it should be very easy for mysql to do it fast all in one query. Only the first column of the index is variable. I thought maybe the range index type didn't work when there could be multiple hits on the first half of the index, but SELECT * FROM transactions WHERE account_id IN (3, 10, 50) AND when BETWEEN ... also uses the range index type and runs very fast! So, can anybody mysql can't do the right thing with the single-statement query? And is there a better option for me than to do it in two queries? I'm using MySql 4.0.x, I was curious about whether using subqueries in 4.1.x would help, but the point is moot right now since my production systems won't be using 4.1.x until it leaves gamma testing. Bill Shubert ([EMAIL PROTECTED]) signature.asc Description: This is a digitally signed message part
Re: two-column indexes and joins with ranges
Thanks for the suggestion, but no luck. Here's the explain output for, in order, my original query, the 2-part query, and the explicit join (note, not quite like my first post, I had cleaned up my tables to simplify the situation): mysql explain SELECT * FROM accounts, transactions WHERE canon_name = 'wms' AND state = 'active' AND id = account_id AND date_stamp BETWEEN 109658881 AND 109926721; +--+--+---++-+-+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--+-+ | accounts | ref | PRIMARY,canon_name| canon_name | 11 | const,const | 1 | Using where | | transactions | ref | date_stamp,account_id | account_id | 3 | accounts.id | 35 | Using where | +--+--+---++-+-+--+-+ 2 rows in set (0.01 sec) The account_id key is a 2 column key, date_stamp is an 8 byte int (java-style date stamp), so you can see only the 3-byte account_id part of the key is used. If I look up the wms account ID and plug that in directly, basically doing two seperate selects to get the data I want, I get: mysql explain SELECT * FROM transactions WHERE account_id = 45 AND date_stamp BETWEEN 109658881 AND 109926721; +--+---+---++-+--+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+---+---++-+--+--+-+ | transactions | range | date_stamp,account_id | account_id | 11 | NULL |1 | Using where | +--+---+---++-+--+--+-+ 1 row in set (0.02 sec) Now you can see that the whole index (all 11 bytes) is being used, with a range type, so this is fast. I tried using a join as you recommended, it gave me: mysql EXPLAIN SELECT * FROM accounts JOIN transactions ON account_id = id AND date_stamp BETWEEN 109658881 AND 109926721 WHERE canon_name = 'wms' AND state = 'active'; +--+--+---++-+-+--+-+ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--+-+ | accounts | ref | PRIMARY,canon_name| canon_name | 11 | const,const | 1 | Using where | | transactions | ref | date_stamp,account_id | account_id | 3 | accounts.id | 35 | Using where | +--+--+---++-+-+--+-+ 2 rows in set (0.03 sec) So, same as the first case - it is just refusing to use a range type of index, even though in some cases this would reduce the number of rows that must be fetched by a factor of 10 or more. On Fri, 2004-10-01 at 07:36, Michael Stassen wrote: Well, you haven't posted the output of EXPLAIN, but I'll take a guess. I expect mysql sees your query as having a JOIN condition of accounts.account_id = transactions.account_id and two WHERE conditions: WHERE accounts.name = 'dave' AND when BETWEEN '2004-02-05' AND '2004-02-10' The optimizer first considers the 2 WHERE conditions, looking for the one it believes will result in fewer rows. Presumably there is an index on accounts.name, but there is no usable index on `when`, as it doesn't come first in the multi-column index. Even if there were a usable index on `when`, I expect there would be fewer rows in accounts with the correct name than rows in transactions within the date range. So, the optimizer chooses accounts as the first table. For each row found in accounts with the right name, it matches that up with rows in transactions according to the JOIN condition, using the first part of the index. Finally, it applies the remaining WHERE condition on the results to filter the matching rows. This may be a case where Shawn's usual advice is the way to go. That is, change your join to an explicit join rather than an implicit join, and move all the relevant conditions to the ON clause. In other words, see if SELECT * FROM accounts JOIN transactions ON accounts.account_id = transactions.account_id AND when BETWEEN '2004-02-05' AND '2004-02-10' WHERE accounts.name = 'dave'; does any better. Michael Bill Shubert ([EMAIL PROTECTED]) signature.asc Description: This is a digitally signed message part
Any workaround for no union operation?
Hi. I just finished a database that will be supporting another system, and right now it is running on Postgres, but I was unhappy with that for various reasons. I converted my data to mysql, and most things look good: in the ways I care about, performance is much better, EXCEPT for one issue which is unfortunately one of the most-performed operations. I need to frequently do a query that does an OR on two columns. For example, SELECT * FROM sales WHERE seller='bob' OR buyer='bob'; (the actual query is much more complex than this, but you get the idea). Under both Postgres and mysql this turns into a scan of the entire (largish) table, even though I have indexes for both seller and buyer. Under Postgres, I was able to make this fast by rewriting it as: SELECT * FROM sales WHERE seller='bob' UNION SELECT * FROM sales WHERE buyer='bob'; This became very fast! Two index scans in a row, then a combine step at the end, no longer any need to scan the whole table, and the end result was exactly the same. I have found that mysql doesn't have union, can anybody on this list think of a similar way to rewrite the query that will work under MySql and gets good performance? Unfortuantely this will force me to stay with Postgres if I can't do it efficiently, because I do it often and it takes *way* too long under MySql right now. It would really be a shame, because all my other queries are much faster under MySql... :-( Anyway, any ideas are appreciated. Thanks. PS - I considered just doing two selects, then combining them in my client...but by the time I write the code to combine the two result sets, sort them, etc., it seems like the database just isn't making my life easier any more, and that was the whole point of using it! -- Bill Shubert ([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
GUI Bug
In the Win 32 version, I seem to have problem scrolling the tables. If I grab the vertical scroll bar and try to pull it down, it won't work, unless I use the arrow keys to move it down about 1/10 of the way, then and only then and lower will it let me scroll by grabbing the scroll bar. Unknown if this bug is present in the linux version, will check in the morning. -- William M. Quarles Giovanetti Research Group Department of Physics James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: Trying to install GUI, flvw and fl_editor compile errors
--On Wednesday, July 11, 2001 13:44 +0300 Sinisa Milivojevic [EMAIL PROTECTED] wrote: William M. Quarles writes: Not sure if this sent properly before, but when I try to unzip the static pacakge, it says that it is not in gzip format. -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University If you downloaded version for Linux you should unpack it with: tar xzvf mysqlgui... if it is Windows version then unzip mysqlgui -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com [root@mach /junk]# tar -xvzf mysqlgui-linux-static-1.7.5-1.tar.gz gzip: stdin: not in gzip format tar: Child returned status 1 tar: Error exit delayed from previous errors Now do you believe me? -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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
Trying to install GUI, flvw and fl_editor compile errors
Does somebody know what might be wrong here? I'm running RedHat Linux 7.1, and fltk installed properly. [root@mach fl_editor-0.4.1]# make Compiling src/Fl_Editor.cxx... c++ -I. -I/usr/local -g -O2 -I/usr/X11R6/include -c src/Fl_Editor.cxx -o src/Fl_Editor.o In file included from src/Fl_Editor.cxx:5: FL/Fl_Editor.H:4:29: FL/Fl_Scrollbar.H: No such file or directory FL/Fl_Editor.H:5:19: FL/Fl.H: No such file or directory FL/Fl_Editor.H:6:24: FL/fl_draw.H: No such file or directory FL/Fl_Editor.H:7:26: FL/Fl_Window.H: No such file or directory FL/Fl_Editor.H:8:24: FL/Fl_Tile.H: No such file or directory FL/Fl_Editor.H:9:23: FL/Fl_Box.H: No such file or directory In file included from src/Fl_Editor.cxx:5: FL/Fl_Editor.H:19:18: FL/x.H: No such file or directory src/Fl_Editor.cxx:6:23: FL/fl_ask.H: No such file or directory src/Fl_Editor.cxx:7:25: FL/Fl_Group.H: No such file or directory src/Fl_Editor.cxx:8:24: FL/fl_draw.H: No such file or directory make: *** [src/Fl_Editor.o] Error 1 [root@mach flvw]# make === making src === make[1]: Entering directory `/home/quarles/MySQL_stuff/GUI/flvw/src' Compiling Flv_Style.cxx... Flv_Style.cxx:14:26: FL/Fl_Widget.H: No such file or directory In file included from Flv_Style.cxx:15: ../FL/Flv_Style.H:45:19: FL/Fl.H: No such file or directory make[1]: *** [Flv_Style.o] Error 1 make[1]: Leaving directory `/home/quarles/MySQL_stuff/GUI/flvw/src' === making test === make[1]: Entering directory `/home/quarles/MySQL_stuff/GUI/flvw/test' make[1]: *** No rule to make target `../lib/libflvw.a', needed by `testlist'. Stop. make[1]: Leaving directory `/home/quarles/MySQL_stuff/GUI/flvw/test' make: *** [all] Error 2 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: Question (fwd)
-- Forwarded Message -- Date: Tuesday, July 03, 2001 1:03 PM -0400 From: William M. Quarles [EMAIL PROTECTED] To: Rafael Marcus [EMAIL PROTECTED] Subject: Re: Question Yes, absolutely, those are fundamental commands. You probably have not set up your access privileges properly. If you go to http://www.devshed.com/, you can read some tutrials that might help you better understand MySQL and user administration. Then read the appropriate sections in the MySQL documentation to fill in some of the holes. If you still need more assistance, O'Reilly publishing has a good MySQL book out. --On Tuesday, July 03, 2001 12:32 PM -0400 Rafael Marcus [EMAIL PROTECTED] wrote: Hello, The product is very helpful and works fine in general. I tried to use create table and edit tables in the Commands, Tables menu but there is no response. Are these supposed to work in this version? If yes how I should set it up. Thanks, Rafael. -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. -- End Forwarded Message -- -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: Cannot start the server after restart.
We would need to know what operating system you are using, that would be helpful in telling you what steps to take to fix it. Make sure that you put a space and a after that command so that you don't get your terminal stuck. As for your problem of starting the darn thing, there are two possiblities I can think of, you might be suffering from one or both (these suggestions assume that you are using Linux, since that is what I know, so if you are not, tough cookies for not telling what you are using): 1. I would say that it seems like you installed it with a tar file, and it didn't make properly. I would install with an rpm if you can, and you are using an outdated version anyway, so it is suggestable. 2. I can't say I can recognize the missing file, but if it is not from MySQL, it is most likely from a GNU C library or some other vitally important operating system library, since hose are the only other things that the MySQL server depends on. If you have been screwing with those files or if they have been corrupted, I'm surprised that you aren't having otehr problems. If you have a Linux Rescue Disk or your installation CD, you may be able to copy missing files directly off of there or extract them from an rpm on the CD. You will also be able to download updated versions of RPMs for your libraries from the RedHat site or another Linux packager. --On Saturday, June 23, 2001 09:27 +0100 Tomas Norre [EMAIL PROTECTED] wrote: Hello.. Friends I have just installed my OpenBSD 2.9 with mysql 3.23.37 and when i wanted to startet i can do it first time, but if i have to restart the server. I cannot start it again, why? i come with this error. w3# safe_mysqld /usr/libexec/ld.so: my_print_defaults: libpthread.so.14.20: No such file or directory Starting mysqld daemon with databases from /var/mysql 010622 17:20:08 mysqld ended From Tomas - 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 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: SV: Cannot start the server after restart.
Sorry, never heard of it. I thouth OpenBSD was just one of those web development packages that they throw MySQL into. Some of what I said still applies, except I don't know whether or not you can use RPMs. --On Saturday, June 23, 2001 21:06 +0100 Tomas Norre [EMAIL PROTECTED] wrote: We would need to know what operating system you are using, that would be helpful in telling you what steps to take to fix it. As you can see i wrote OpenBSD 2.9 as operation System. --On Saturday, June 23, 2001 09:27 +0100 Tomas Norre [EMAIL PROTECTED] wrote: Hello.. Friends I have just installed my OpenBSD 2.9 with mysql 3.23.37 and when i wanted to startet i can do it first time, but if i have to restart the server. I cannot start it again, why? i come with this error. w3# safe_mysqld /usr/libexec/ld.so: my_print_defaults: libpthread.so.14.20: No such file or directory Starting mysqld daemon with databases from /var/mysql 010622 17:20:08 mysqld ended From Tomas - 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 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: Fw: confirm subscribe to mysql@lists.mysql.com
-- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: Possible Bug in mysql 3.23.38
I would like to note that bug reports should only be sent out if you are
using the latest version, which now is 3.23.39.
--On Friday, June 15, 2001 17:37 -0600 Chris Bolt [EMAIL PROTECTED]
wrote:
After creating a new database, I ran:
update user set password = 'SomeJunk' where user = 'root';
This inserts the string 'SomeJunk' literally into the database,
unencrypted. This is SQL expected but the result is a database
lockout (not to mention the security breach of having the clear
password stored in a file.
I got the smart idea to assign a root password like this from the
Bugzilla-Guide.txt file that comes with Bugzilla.
I just opened Bugzilla-Guide.txt and it says:
mysql UPDATE user SET Password=PASSWORD ('new_password') WHERE
user='root'; mysql FLUSH PRIVILEGES;
note the PASSWORD('new_password') instead of just 'new_password'
If there is a better way to assign passwords, please let me know.
I tried
grant all on mysql.* to foo identified by bar;
Seems to work for creating new accounts.
Try
GRANT ALL PRIVILEGES ON table.* TO user@localhost IDENTIFIED BY
'password';
Since in most cases (web server and mysql on same box) you don't need to
allow users to connect from anywhere. If you want to include a hostname or
wildcard, put it in '' (like '%.host.com' or just '%')
Also, the user table looks like:
mysql select host, user, password from user;
+---+-+--+
| host | user | password |
+---+-+--+
| localhost | root| 7d30d6e5796d165e |
| nomis | root| 7d30d6e5796d165e |
| localhost | | |
| nomis | | |
| localhost | ShimonR | 7d30d6e5796d165e |
+---+-+--+
5 rows in set (0.00 sec)
Does this mean that any user from localhost or from nomis can connect?
Don't know, but unless they have corresponding rows in another table to
actually give them privileges, they can't do anything.
I don't have rows like that in my user table so they should be safe to
delete.
-
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
--
William M. Quarles
Vice Pres, Society of Physics Students
Jr. Warden, Canterbury Episcopal Campus Ministry
James Madison University
[EMAIL PROTECTED]
* This e-mail was sent using Mulberry, JMU's new official e-mail client.
-
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: how to install mysql replication ?
Okay, I will get in on this, too. I found the replication procedure in the documentation to be very complete (we are all talking about http://www.mysql/com/doc/, right? Read all parts of Chapter 11, right?). I had no problem following the procedure. However, I will admit that the replication didn't work. But my slave doesn't think it's a slave. I get an error message telling me that it is not properly configured to be a slave. Do I need anything else in the my.cnf file other than what is listed there? Something seems kind of fishy about all of that. If somebody had successfully performed replication and feels that they may be able to review the example in the documentation, please let me know. Afterwards, I will post a copy of my my.cnf file (with the privileged information deleted) to the list to make sure that it is correct. --On Tuesday, June 12, 2001 3:41 PM -0400 Jim Ziegler [EMAIL PROTECTED] wrote: On Tue, Jun 12, 2001 at 12:50:20AM -0700, Jeremy Zawodny wrote: On Tue, Jun 12, 2001 at 02:15:41PM +0700, Said Madrus wrote: Dear all, can anybody tell me how to install mysql replication step by step :-). thanks in advance. Best regards, It is described step-by-step in the manual already. If you find it to be lacking, please let us know and someone will make it more clear (and update the manual). Did that, it didn't work, asked for help, got none. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 17 days, processed 115,979,732 queries (76/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 -- [EMAIL PROTECTED] (Jim Ziegler) - 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 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: my.cnf mystery
I don't think that's what Dennis person wants to know, I think he wants to know every single variable that he can control in my.cnf. --On Tuesday, June 12, 2001 3:09 PM -0500 Paul DuBois [EMAIL PROTECTED] wrote: At 3:36 PM -0400 6/12/01, Dennis wrote: Of the many mysql resources I have (including a book and searching the website), there doesnt seem to be a definitive outline of what variables can be controlled by my.cnf. set-variable = var_name=var_value should work for pretty much any variable that a program knows about... Is there a list somewhere outlining the possibilities? Dennis - 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 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: how to install mysql replication ?
I think we would need much more information than what you have given to verify what is going wrong, but I am not the expert on replication yet, since I haven't got it working yet. Although I am confused, for you requested that somebody tell you how to install it, and last I heard you basically cursed the list, and now you do have it installed and you have these error logs? --On Tuesday, June 12, 2001 4:10 PM -0400 Jim Ziegler [EMAIL PROTECTED] wrote: On Tue, Jun 12, 2001 at 12:45:15PM -0700, Jeremy Zawodny wrote: On Tue, Jun 12, 2001 at 03:41:25PM -0400, Jim Ziegler wrote: On Tue, Jun 12, 2001 at 12:50:20AM -0700, Jeremy Zawodny wrote: On Tue, Jun 12, 2001 at 02:15:41PM +0700, Said Madrus wrote: Dear all, can anybody tell me how to install mysql replication step by step :-). thanks in advance. Best regards, It is described step-by-step in the manual already. If you find it to be lacking, please let us know and someone will make it more clear (and update the manual). Did that, it didn't work, asked for help, got none. What happened? What failed? What error messages did you get? Were there parts of the documentation that were unclear? I updated the replication docs a few months ago when *I* found them unclear, but that doesn't mean that they're as good as they could be... From my previous post: Envelope-to: [EMAIL PROTECTED] Delivery-date: Fri, 13 Apr 2001 09:04:18 -0400 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: replication problems From: Jim Ziegler [EMAIL PROTECTED] X-UIDL: d8dfffe8bce601f2bac2c6bf2bb666c5 does anyone that is using replication have any suggestions at all about how to get past this problem? is anyone using replication successfully? - Forwarded message from Jim Ziegler [EMAIL PROTECTED] - Envelope-to: [EMAIL PROTECTED] Delivery-date: Tue, 10 Apr 2001 16:42:26 -0400 Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: replication problems From: Jim Ziegler [EMAIL PROTECTED] X-UIDL: bf6be71aa70b2cf435089831f4dbdcb3 mysql sql db I am unable to get replication running. I have set up both master and slave systems with version 3.23.33-log of musql. I can do a load table from master but when I start the slave, all I get in the slave log is: 010410 16:37:24 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 010410 16:37:24 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'FIRST' at position 4 010410 16:37:24 Error reading packet from server: Binlog has bad magic number, fire your magician (read_errno 0,server_errno=65535) and in the master log: 010410 16:35:583006 Connect[EMAIL PROTECTED] on 3007 Connect[EMAIL PROTECTED] on 3007 Quit 3006 Binlog Dum What is wrong? Where do I get a better magician? - 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 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: my.cnf mystery
Which program? --On Tuesday, June 12, 2001 15:52 -0500 Paul DuBois [EMAIL PROTECTED] wrote: At 4:34 PM -0400 6/12/01, William M. Quarles wrote: I don't think that's what Dennis person wants to know, I think he wants to know every single variable that he can control in my.cnf. Running a program with the --help option will list the program's variables. The syntax for setting any of them is the same. --On Tuesday, June 12, 2001 3:09 PM -0500 Paul DuBois [EMAIL PROTECTED] wrote: At 3:36 PM -0400 6/12/01, Dennis wrote: Of the many mysql resources I have (including a book and searching the website), there doesnt seem to be a definitive outline of what variables can be controlled by my.cnf. set-variable = var_name=var_value should work for pretty much any variable that a program knows about... Is there a list somewhere outlining the possibilities? Dennis - 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 -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. -- Paul DuBois, [EMAIL PROTECTED] -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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: my.cnf mystery
The command lines are divided up by topics in the manual, I think. I know that there is a specific section on replication. A table of all of them in one place would be a helpful appendix in a future edition of the documentation. [hint, hint, MySQL AB developers...] --On Tuesday, June 12, 2001 20:30 -0400 Philip Mak [EMAIL PROTECTED] wrote: On Tue, 12 Jun 2001, William M. Quarles wrote: Which program? Here's what the /etc/my.cnf file on my system looks like: [client] port=3306 socket=/home/mysql/mysql.sock [mysqld] port=3306 socket=/home/mysql/mysql.sock The first section affects the mysql client. The second section affects the mysqld server. So, type mysql --help for a list of things that you can put in the client section, and /usr/sbin/mysqld --help (substitute the correct path name for mysqld) for a list of things that you can put in the server section. That said, I do think the MySQL manual should include a section that explains my.cnf better, if it doesn't have one already. -Philip Mak ([EMAIL PROTECTED]) -- William M. Quarles Vice Pres, Society of Physics Students Jr. Warden, Canterbury Episcopal Campus Ministry James Madison University [EMAIL PROTECTED] * This e-mail was sent using Mulberry, JMU's new official e-mail client. - 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
[Q] I need to pass one database from the computer A to computer B, they have MySQL installed
Hi, I have been designing a database in a computer 'A' with MySQL, now i've finished and i need to copy the database to the computer 'B' which is also with MySQL. The computers operating system is Linux Mandrake 7.2. Where does it store the MySQL databases? , the name of the database is 'Administrator', so, suposse that it stores the databases in the directory /usr/mysql/databases/Administrator in the computer 'A', i copy the directory '/Administrator' to the computer 'B' in the same directory '/usr/mysql/databases/Administrator' (i don't know anything about, if i'm in a wrong way please make me know) How can i make mySQL know that in the computer 'B' there is a new Database to manage?. -i want to know which instruction do i have to use to make this- Well, Thank you William M. _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.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
