Mutable mysql servers accessing the same data dir
I would like to know how feasible it would be to have more than one mysql server accessing the same data dir. e.g. having two different computers accessing the data dir over a nfs mount my logic tells me that it will work correctly seeing that mysql spans a new process for each query dose anyone know for sure if this will work Thanks a bunch Kenneth W Lichtenberger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Hi, No, MySQL never uses multiple CPUs for the same query at the same time (the OS could switch the thread between CPUs over time, but that doesn't count :-)). Each connection gets one thread and one thread can only run on one CPU at a time. It would be pretty hard (if not impossible) to have multiple threads for a connection. You'd have to figure out *how* to split up the work for one query and try to get them to finish at the same time, otherwise one will be waiting for the other(s) to catch up too long. At least that's what I think with my knowledge of threading. ;-) However, for the original question about creating indexes, MySQL 4.0.13 added a new feature: Added multi-threaded MyISAM repair optimisation and myisam_repair_threads variable to enable it. And from http://www.mysql.com/doc/en/SHOW_VARIABLES.html myisam_repair_threads. If this value is greater than one, MyISAM table indexes during Repair by sorting process will be created in parallel - each index in its own thread. Note: multi-threaded repair is still *alpha* quality code. Just thinking though, I wonder how much this will help during index creation? Is that more I/O bound...? Well, since it's used for Repair by sorting (and I assume not for with keycache), maybe that is fairly CPU intensive to sort the indexes -- and only writing an index chunk every few seconds (I think). Anyway, hope that helps. Matt - Original Message - From: Ware Adams Sent: Friday, October 03, 2003 7:49 PM Subject: Re: Newbye speed question - which setup to use for indexing Jeremy Zawodny wrote: On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) I haven't seen any documentation, and I'm not sure I'm explaining things properly, but here's what I've observed running MySQL on a decent sized data set over almost a year on OS X: When only one query is active in MySQL (observed via show processlist, all connection IDs show 'sleep' except one) the mysqld process in top never shows more than 100% (or never more than 105-110% to be absolutely truthful) When multiple queries are active in MySQL the mysqld process frequently approaches 200% (assuming each can hit 100% when run on it's own) When a single MySQL query is active and another heavy load process is running on the machine (e.g. running rsync on a big directory) mysqld will go to 100% and the other process will approach the level it would hit without mysqld running This is on a G4 1.42GHz dual proc running OS X and hooked up to an XServe RAID. My conclusions from this were that MySQL on OS X cannot use more than one processor for a single query, but it uses multiple ones fine when it has multiple queries to process. Also, it performs fine sharing the two processors with other applications. Can MySQL use multiple processors for a single query on other OS's? It's pretty key for us as we tend to run relatively few, long duration queries as opposed to lots of quick ones. Thanks, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installing
Make all gives me this libmysql.c: In function `mysql_real_connect': libmysql.c:2177: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type libmysql.c:2177: too few arguments to function `gethostbyname_r' libmysql.c:2177: warning: assignment makes pointer from integer without a cast make[2]: *** [libmysql.lo] Error 1 make[2]: Leaving directory `/mysqltemp/mysql-4.1.0-alpha/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/mysqltemp/mysql-4.1.0-alpha' make: *** [all] Error 2 -Original Message- From: Adam Carmichael [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 11:44 PM To: Michael Cupp, Jr. Subject: Re: Installing I'm attempting to install using INSTALL-BINARIES and get this message while executing mysql_install_db: Did you download the binaries or compile from source? [cuppjr mysql]$ scripts/mysql_install_db scripts/mysql_install_db: ./bin/my_print_defaults: cannot execute binary file WARNING: The host 'raq2.homeunix.org' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible If you downloaded binaries, are they correct for your OS? Example: I run a bunch of OpenBSD and FreeBSD systems, and the Linux binaries won't work on them (not unless I do some emulation). I find it easiest to download the source, uncompress it, and then run the following set of commands: ./configure [any options such as ssl] make all make install /usr/local/bin/mysql_install_db then I log in and set the root password (can be done in mysqladmin I know, but there's normally other things I want to do without having to log in again). Those stages usually work on *BSD and Linux. Posting your OS, version of download, will be very helpful. There's also a section about incompatible versions of libc (see above) with MySQL, consider checking the version of libc that you have against what the MySQL download page says. Adam -- Adam Carmichael [EMAIL PROTECTED] with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing privilege tables scripts/mysql_install_db: ./bin/mysqld: cannot execute binary file Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: /bin/mysqld --skip-grant You can use the command line tool /bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! Then if I try to run mysql or mysqld I get this: bash: /usr/local/mysql/bin/mysql: cannot execute binary file -- 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]
Convert Foxpro database to Mysql
Hello, i need some help, i want to convert the existing foxpro database into mysql database. Any idea? == Dwi Suharto [EMAIL PROTECTED] System and Network Administratorhttp://www.revlinux.dhs.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tomcat, Connection Pooling, and MySQL
you need to put mysql-connector-javax in tomcat/common/lib and then specify parameter namedriverClassName/name valuecom.mysql.jdbc.Driver/value /parameter in your server.xml Adam On 10/03/2003 04:35 PM Dan Greene wrote: I got it working... unfournately it's on my laptop at home, not here at work with me I think that the issues was that the class names given in the documentation for the jdbc driver for MySQL were wrong look at the listing of the contents of the jar file, and see if you can find the right one... Sorry that I'm being incredibly vague I set it up months ago Dan Greene -Original Message- From: Steven Nakhla [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 9:42 AM To: MySQL Subject: Tomcat, Connection Pooling, and MySQL Has anyone managed to setup Tomcat to use MySQL for database connection pooling? I've found this document which gives information on it: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasourc e-examples-howto.html However, when I try and run it I get messages about not being able to find the hsql driver class. From searching on Google, it seems that this is a common error, but there are no solutions posted. Has anyone managed to get it up and running successfully? I'd really appreciate any advice! Thanks! Steve Nakhla - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- Running mySQL 4.1.0 on Linux 2.4.20 RH9 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL, Second Edition, now available in French
MySQL, Second Edition, is now available in French. Information is available at: http://www.kitebird.com/mysql-book/ http://www.pearsoneducation.fr/espace/livre.asp?idEspace=73idLivre=1771 -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Cookbook now available in German
MySQL Cookbook (O'Reilly Associates) is now available in German (translated by Lars Schulten, Stefan Hinz Peter Klicman). Information is available at: http://www.kitebird.com/mysql-cookbook/ http://www.oreilly.de/catalog/mysqlckbkger/ -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL w/dual-master replication?
Hi there, I realize it's not supported, but nonetheless, I need to investigate how possible it is. The primary goal is high-availability, not performance scaling. It sounds like if I'm careful, it might work out ok. Careful means: - No auto_increment columns... handle unique key assignment in my app - Update/insert/delete on primary master only except in case of failure - Prevent possibly 'flap' by automatically using secondary master exclusively after primary master failure. Bring up primary master manually and manually tell the app when to start using it again after it's allowed to catch back up. Are there any other gotchas I need to worry about? Is anyone actually doing this in a production environment? I'd love to hear from you. Another option is to use master/slave, and have a monitoring app change the slave's status to master after the master has died. There's info about this in the FAQ (http://www.mysql.com/doc/en/Replication_FAQ.html), but I'm afraid the documentation on the sequence of events is pretty vague. Does anyone have any insight as to exactly how it works? In particular, one part of the doc says to use 'STOP SLAVE; RESET MASTER; CHANGE MASTER TO' and another part of the doc says 'STOP SLAVE; CHANGE MASTER TO' ... which is appropriate? Certainly, I understand why 'STOP SLAVE', but why 'RESET MASTER'? Would the sequence of events differ if we're just dealing with 1 master/1 slave instead of 1 master/multiple slaves? Once the old master joins back up, I can let it be a slave for awhile to catch back up, then swap back, correct? Thanks in advance! Don MacAskill http://onethumb.smugmug.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql account
Hi, folks. I use WinXP + MySQL. I deleted all the rows from the 'user' table of the 'mysql' database except my own account (host: localhost, user: alan, password:alan). --- I was hoping only I have the access to the MySQL databases. The problem is that I still can login to MySQL by the DOS command: 'c:\mysql\bin\mysql' without the '-h localhost -u alan -p' followed. (NOTE: MySQL was installed under the c: drive) And even the 'c:\mysql\bin\mysql -h localhost' and 'c:\mysql\bin\mysql -h localhost -u root' *work* too!!! Terrible!!! (BTW, in the 'my.ini' file, the user=alan, password=alan.) --- But I don't think this is important as all of the user info are stored in the 'user' table of the 'mysql' databse. Right? What am I gonna do? Please advise. cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql account
do u have a .my.cnf in c:\ dir or c:\windows dir ? from DOS when u issue a mysql command it usually picks up the username and password from the .my.cnf file. - Shanta -Original Message- From: Wang Feng To: [EMAIL PROTECTED] Sent: 10/4/2003 2:35 PM Subject: mysql account Hi, folks. I use WinXP + MySQL. I deleted all the rows from the 'user' table of the 'mysql' database except my own account (host: localhost, user: alan, password:alan). --- I was hoping only I have the access to the MySQL databases. The problem is that I still can login to MySQL by the DOS command: 'c:\mysql\bin\mysql' without the '-h localhost -u alan -p' followed. (NOTE: MySQL was installed under the c: drive) And even the 'c:\mysql\bin\mysql -h localhost' and 'c:\mysql\bin\mysql -h localhost -u root' *work* too!!! Terrible!!! (BTW, in the 'my.ini' file, the user=alan, password=alan.) --- But I don't think this is important as all of the user info are stored in the 'user' table of the 'mysql' databse. Right? What am I gonna do? Please advise. cheers, feng -- 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]
remote connection 4
when i try to connect to my mysql database from another machine i get a error like this %mysql -h domain.com -u tellus ERROR 2003: Can't connect to MySQL server on 'domain.com' (61) The port don't listen and this is disabled in my.cfg # #skip-networking whats wrong and how do i allow remote connections? _ Hitta rätt på nätet med MSN Sök http://search.msn.se/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql account
At 4:35 +1000 10/5/03, Wang Feng wrote: Hi, folks. I use WinXP + MySQL. I deleted all the rows from the 'user' table of the 'mysql' database except my own account (host: localhost, user: alan, password:alan). --- I was hoping only I have the access to the MySQL databases. The problem is that I still can login to MySQL by the DOS command: 'c:\mysql\bin\mysql' without the '-h localhost -u alan -p' followed. (NOTE: MySQL was installed under the c: drive) And even the 'c:\mysql\bin\mysql -h localhost' and 'c:\mysql\bin\mysql -h localhost -u root' *work* too!!! Terrible!!! (BTW, in the 'my.ini' file, the user=alan, password=alan.) --- But I don't think this is important as all of the user info are stored in the 'user' table of the 'mysql' databse. Right? What am I gonna do? Please advise. If you deleted the rows with a DELETE statement, you'll need to also issue a FLUSH PRIVILEGES statement to cause the server to reread the grant tables. I am assuming that the 'alan' account that you're planning to use has all privileges. Otherwise, you will find after flushing the privileges that you'll no longer be able to administer your server without starting it with the --skip-grant-tables option... -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remote connection 4
At 22:55 +0200 10/4/03, Ms Carlsson wrote: when i try to connect to my mysql database from another machine i get a error like this %mysql -h domain.com -u tellus ERROR 2003: Can't connect to MySQL server on 'domain.com' (61) The port don't listen Apparently the MySQL server on domain.com is not running. Or perhaps domain.com is firewalled and doesn't allow connections to the MySQL port. and this is disabled in my.cfg # #skip-networking whats wrong and how do i allow remote connections? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing TMPDIR for tempory file location
At 13:20 -0500 10/3/03, dan orlic wrote: Hello all, My query is about changing the tmpdir on mysql. As stated in on the mysql.com site it states that if it is not set it uses the default, in my case, the default was /tmp. It also states that to change it you can do so in the mysqld_safe startup file. It makes no mention of changing it in the my.cnf file. What page on mysql.com are you looking at? my.cnf *is* the startup file, so you can set tmpdir using by placing the appropriate option in the [mysqld] group of the file: [mysqld] tmpdir=/the/path/you/want/to/use You can put these lines in any of the startup files that the server reads, such as /etc/my.cnf. My questions are: 1) Can you set the tmpdir in the conf file my.cnf? if so, how? 2) if you can only make that change in mysqld_safe, how do you? I did not mention the change at command line, e.g. mysqld_safe -tmpdir=/whatever because I don't want to have to change the startup scripts. Thanks for the help, dan -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mutable mysql servers accessing the same data dir
At 2:45 -0400 10/4/03, Kenneth Lichtenberger wrote: I would like to know how feasible it would be to have more than one mysql server accessing the same data dir. Not recommended. e.g. having two different computers accessing the data dir over a nfs mount And *especially* not recommended over NFS. my logic tells me that it will work correctly seeing that mysql spans a new process for each query Hmm ... well, for one thing, the server *doesn't* span (spawn?) a new process per query, so your assumption is incorrect in the first place. But even if that were true, how would that have anything to do with whether it's safe to point two servers at the same database? dose anyone know for sure if this will work Thanks a bunch Kenneth W Lichtenberger -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0.15 and latest redhat enterprise AS Beta - segmention fault
At 12:01 +0200 10/3/03, Thomas Gusenleitner wrote: HI List! mysql 4.0.15 won't start on the latest beta of the redhat enterpise AS. (will be released this month) i used the std. rpms for linux x86. i get the following error: Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 339: 14874 Speicherzugriffsfehler $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 031003 10:42:17 mysqld ended Did you look at the error log? What did it say? i've compiled the SRPM package - everything ok. bye, thomas -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY/ORDER BY Problem
At 5:52 -0700 10/3/03, Ed Smith wrote: Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function I don't believe that aggregate functions are legal in an ORDER BY clause. The solution, as you've found, is to select the value you want to order by, alias it, and refer to the alias in the ORDER BY clause. but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-Column Unique Index Redundancy
At 18:06 -0400 10/3/03, John Kornet wrote: I've been over a few books, the site, and the archives... Can someone please confirm that if I create a unique index over 2 columns, it will be redundant to create a regular index for the first? In other words, does the left-prefix rule apply to unique indexes that specify uniqueness over multiple columns? It will be redundant to create an index on the first column, yes. However, the values in the first column of the index will not necessarily be unique. Thanks in advance for taking a few minutes for me! John Kornet You may want to read about leftmost prefixes in the MySQL Reference Manual. http://www.mysql.com/doc/en/MySQL_indexes.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ip range lookup
I have a table that maps ip-ranges to countries: each record consists of 2 ip numbers (unsigned int's) and the country in which all ip's between those two are located. How should I setup the table to have fast ip lookups ? Making a primary key of the 2 ip's and doing a 'select .. between ip1 and ip2' doesn't use the index: mysql explain select * from ipcountry where 123456789 between ip1 and ip2; +---+--+---+--+-+--+---+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+---+ -+ | ipcountry | ALL | NULL | NULL |NULL | NULL | 58229 | Using where | +---+--+---+--+-+--+---+ -+ Should I add a helper column - f.e. a column that contains the 'leftmost' byte of the ip - and index that ? Thanks, Willem Bison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ip range lookup
Hi Willem, I don't *think* MySQL optimizes BETWEEN like that to use an index. Have you tried this?: SELECT * FROM ipcountry WHERE ip1 = 123456789 AND ip2 = 123456789; Matt - Original Message - From: Willem Bison Sent: Saturday, October 04, 2003 6:08 PM Subject: ip range lookup I have a table that maps ip-ranges to countries: each record consists of 2 ip numbers (unsigned int's) and the country in which all ip's between those two are located. How should I setup the table to have fast ip lookups ? Making a primary key of the 2 ip's and doing a 'select .. between ip1 and ip2' doesn't use the index: mysql explain select * from ipcountry where 123456789 between ip1 and ip2; +---+--+---+--+-+--+---+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+---+ -+ | ipcountry | ALL | NULL | NULL |NULL | NULL | 58229 | Using where | +---+--+---+--+-+--+---+ -+ Should I add a helper column - f.e. a column that contains the 'leftmost' byte of the ip - and index that ? Thanks, Willem Bison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ip range lookup
Hi Willem: I have the same database and the only thing different is that I included the country iso name into the primary key: mysql describe ip_country_database; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | ipstart | int(10) unsigned | | PRI | 0 | | | ipend | int(10) unsigned | | PRI | 0 | | | iso | char(2) | | PRI | | | +-+--+--+-+-+---+ And my system indeed uses the indexes: mysql explain SELECT * FROM ip_country_database WHERE 12456789 BETWEEN ipstart AND ipend; +-+---+---+-+-+--+-- -+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| +-+---+---+-+-+--+-- -+--+ | ip_country_database | index | NULL | PRIMARY | 10 | NULL | 42971 | Using where; Using index | +-+---+---+-+-+--+-- -+--+ System: MySQL server 4.0.13 on a Red Hat 7.2 box. Cheers, Jose Miguel. - Original Message - From: Willem Bison [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, October 05, 2003 1:08 AM Subject: ip range lookup I have a table that maps ip-ranges to countries: each record consists of 2 ip numbers (unsigned int's) and the country in which all ip's between those two are located. How should I setup the table to have fast ip lookups ? Making a primary key of the 2 ip's and doing a 'select .. between ip1 and ip2' doesn't use the index: mysql explain select * from ipcountry where 123456789 between ip1 and ip2; +---+--+---+--+-+--+---+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+---+ -+ | ipcountry | ALL | NULL | NULL |NULL | NULL | 58229 | Using where | +---+--+---+--+-+--+---+ -+ Should I add a helper column - f.e. a column that contains the 'leftmost' byte of the ip - and index that ? Thanks, Willem Bison -- 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: mysql account
Hi, Shanta I do have a 'my.cnf' in 'c:\windows' dir, and it's called 'my.ini' in WinMySQLadmin. As I mentioned early, the ONLY user and password in 'my.cnf' is: user=alan, password=alan. Wait, this is amazing! (I just gave it another try) After about 6hrs, it works now!!! Now only 'alan' can log in and others can NOT!!! :-) Is it because that I restarted the computer just now and that makes the *changes* valid? --- at least for Windows? cheers, feng It seems that when I install MySQL server, some - Original Message - From: Thada, Shantalaxmi (NIH/CC/PET) [EMAIL PROTECTED] To: 'Wang Feng ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, October 05, 2003 5:57 AM Subject: RE: mysql account do u have a .my.cnf in c:\ dir or c:\windows dir ? from DOS when u issue a mysql command it usually picks up the username and password from the .my.cnf file. - Shanta -Original Message- From: Wang Feng To: [EMAIL PROTECTED] Sent: 10/4/2003 2:35 PM Subject: mysql account Hi, folks. I use WinXP + MySQL. I deleted all the rows from the 'user' table of the 'mysql' database except my own account (host: localhost, user: alan, password:alan). --- I was hoping only I have the access to the MySQL databases. The problem is that I still can login to MySQL by the DOS command: 'c:\mysql\bin\mysql' without the '-h localhost -u alan -p' followed. (NOTE: MySQL was installed under the c: drive) And even the 'c:\mysql\bin\mysql -h localhost' and 'c:\mysql\bin\mysql -h localhost -u root' *work* too!!! Terrible!!! (BTW, in the 'my.ini' file, the user=alan, password=alan.) --- But I don't think this is important as all of the user info are stored in the 'user' table of the 'mysql' databse. Right? What am I gonna do? Please advise. cheers, feng -- 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: Convert Foxpro database to Mysql
On Saturday, October 4, 2003, at 11:05 AM, Dwi Suharto Panese wrote: i want to convert the existing foxpro database into mysql database. Any idea? Sure, I've done it a lot. What exactly do you need to know? ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql account
Paul,
I am assuming that the 'alan' account that you're planning to use has
all privileges. Otherwise, you will find after flushing the privileges
that you'll no longer be able to administer your server without starting
it with the --skip-grant-tables option...
The *alan* account does have all privileges. :-) That account works fine.
And the problem I asked early seems to be solved.
If you deleted the rows with a DELETE statement, you'll need to also
issue a FLUSH PRIVILEGES statement to cause the server to reread the
grant tables.
In order to try the FLUSH PRIVILEGES statement, I inserted a new user
account into the 'user' table of the 'mysql' database:
mysql insert into user (host, user, password) values ('localhost', 'brian',
'brian');
After doing this, I subsequently use FLUSH PRIVILEGES to let the MySQL
server reread the grant tables.
mysql flush privileges
Query OK, 0 rows affected (0.01 sec)
Then, I quit the MySQL and try to login by 'c:\mysql\bin\mysql -h
localhost -u brian -p' followed by the password brian. I got the following
error message:
Error: 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES).
Then, I restart the computer, and repeat the process above again, same
error. It seems I don't have to restart the computer (in WindowsXP) to
make the change valid.
Something cause my attention is that if I do the
mysql select host, user, password from user;
I found that the password of *alan* is something like 51df199bcd85 while
*brian's* new account password is brian. That is, the later one has not
been encrypted.
I can see the Alan's *actual* password characters('alan') in WinMySQLadmin
or the my.conf file, not the user table; but I DO see Brian's *actual*
password ('brian') from the user table.
BTW, I can't find any thing in the my.conf or WinMySQLadmin related to the
*brian* --- e.g. in the my.conf file, I only can see 'user=alan,
password=alan'.
Please help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow performance with large or list in where
If I do a query such as: SELECT * from foo where fooid = 10 or fooid = 20 or fooid = 03 ... with a total of around 1900 or fooid = parts on a given table with 500k rows, it takes about four times longer than doing 1900 separate queries in the form: SELECT * from foo where fooid = 10 fooid is the primary key. My real goal is to do updates in a similar way, they have the same issue. And my real goal is actually doing the same thing where instead of fooid=10 I have a multicolumn primary key and use (keycol1=10 and keycol2=2 and ...) My examples here are the simplest case I can find that exhibit the issue. Explain plan on the select shows: table typepossible_keys key key_len ref rowsExtra adsummary range PRIMARY PRIMARY 31 NULL1915Using where so it is doing a range scan in some manner. Given that the rows I'm updating will normally be cached in memory, is there anything I can do to force it to just do repeated index lookups like the individual queries are doing? The reason I don't want to use individual queries is because my performance is then bottlenecked by latency, and when trying to update thousands of rows a second that doesn't work out. Just opening multiple connections and doing them in parallel is a problem because of where the updates are coming from and the fact that they have to be done inside a transaction so other connections would just end up blocked anyway. Currently running 4.0.15a. I'm trying to avoid going to 4.1 at this point, but it does look like the INSERT ... ON DUPLICATE KEY UPDATE ... syntax may do individual index lookups the way I want... I haven't verified that yet though. Any suggestions are greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blob fields
Hi, I have the question related to this topic, hopefully you could help me on this. Is that possible that we insert an encrypted image into blob fields where the encryption is done by using mysql existing function (AES_Encrypt) from the client side? If yes, how is the procedure ? Thanks in advance. Jeremy Zawodny [EMAIL PROTECTED] wrote: On Wed, Oct 01, 2003 at 05:51:18PM +0100, Angelo Carmo wrote: I people, Who knows how to insert an image file into blob fileds. Lots of us know how. And we've discussed it on the list about 600 times already. I'm sure you'll find an answer in the list archives. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 17 days, processed 630,933,987 queries (412/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
