ODBC gets to mysql db with incorrect password
Nestor: You might not have entered the SQL FLUSH PRIVILEGES; command after you changed the password. Without it, the old data can remain in effect. Cheers, Kent Hoover - 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: Rapid-fire connections causing MySQL grief
On Wed, 11 Sep 2002, Jeremy Zawodny ([EMAIL PROTECTED]) wrote: On Wed, Sep 11, 2002 at 02:55:01PM -0400, Kent Hoover wrote: Version: MySQL.3.22.32-log Does this problem ring a bell with anyone? I'm seeing two undesired behaviors, both, *I think* seem to be because I'm pounding a lot of connections against the same server/table, all from the same remote host. The workload that reaches MySQL is a succession of CONNECT-QUERY (very simple)- QUIT, say 200 times per second. After about the first 4000 connections, MySQL restarts, complaining that mysqld is hanging. Which OS are you running? Also, that's a pretty old MySQL. Have you considered upgrading? Many bugs have been fixed since then. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! Jeremy: We're running Red Hat Linux 6.2. Oh, yes, we've considered upgrading. Kind of hesitant to take that step so far. I've read through the Fixed in version { } sections of the manual, didn't recognize what I have here, and was hoping that someone out here would say Yes, I remember that, and it was fixed! Thanks, Kent Hoover - 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
Rapid-fire connections causing MySQL grief
Version: MySQL.3.22.32-log Does this problem ring a bell with anyone? I'm seeing two undesired behaviors, both, *I think* seem to be because I'm pounding a lot of connections against the same server/table, all from the same remote host. The workload that reaches MySQL is a succession of CONNECT-QUERY (very simple)- QUIT, say 200 times per second. After about the first 4000 connections, MySQL restarts, complaining that mysqld is hanging. Secondly, prior to the restart, an occasional connection is denied because of an invalid password! All connections are requested by the same user with the same password. Please, someone, tell me that these 2 problems are both load related, were found and corrected by a later release than the one I have. Thanks, Kent Hoover - 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
help with MySQL SELECT statement
I would use this query: SELECT DISTINCT SUBSTRING(email,LOCATE('@', email) ) FROM AddressList ; Cheers, Kent sql, query, y'hear - 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
Irritating Discovery
This works for me: mv /mysql/log /mysql/Oldlog mysqladmin flush-logs MySQL follows the oldlog, keeps writing to it until the flush, when it closes its Oldlog, and open/create-s its logfile. Cheers, Kent - 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: Can't get MySQL to use available memory (performance very slow)
I believe the answer will be for you to create a single index on *just* the cust column. (I assume that a specific cust id occurs only once or a few times in the whole table.) This will allow MySQL to use that index to find the small set of records (quickly) where cust=1, then to screen the email value against that set of records. Cheers, Kent query,database - 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
What have i done? adding new db
Kevin: A database is, in some sense, a container for your tables. It sounds like you want to compose a new container. Thus, To list your databases (containers): mysql show databases; ... it seems that the list should contain your dbb and mysql, probably also test. To create a new database (container): mysql create database new_dbb; Next, you would be able to use new_dbb; and begin creating tables in it, using the 'create table' SQL statement. Cheers, Kent. - 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
Select *,count(*) not returning 0 when needed
Doc: Try this... SELECT C.domain, email, telephone, count(*) FROM Customers AS C LEFT JOIN Orders AS O ON O.domain=C.domain GROUP BY C.domain, email, telephone ; Cheers, Kent [fodder=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: Simple SQL Query?
Read all about Join in the manual -- it includes hints like this query: SELECT DISTINCT(PersonID) FROM PersonProject AS P1 LEFT JOIN PersonProject AS P2 ON P2.PersonID=P1.PersonID AND P2.ProjectID=2 WHERE P2.PersonID IS NULL; Cheers, Kent - 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: Having problems with a query..
LEFT JOIN should do the trick for you... SELECT players.PLAYERID, players.FIRSTNAME, players.LASTNAME, COUNT(goals.SCORER) FROM players LEFT JOIN goals ON goals.SCORER = players.PLAYERID WHEREplayers.TEAMID = '$TEAMID' GROUP BY PLAYERID ORDER BY player.LASTNAME; Cheers, Kent Hoover filter wants me to type 'database' here - 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: why does it not work
You didn't show the code where the mysql_query() is done. Further, you have RAND as r coded in your select statement, not RAND( ) as r. Is this live code? Cheers, Kent (filter feeder: database) - 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: Ugly output
The excess space could've come from the very long column name of the first field. Shrink that with an alias: select if(substring) AS shortname, sec_to_time( ) AS session ... Cheers, Kent Hoover filter fodder:database,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: Re: Ugly output
The excess space could've come from the very long column name of the first field. Shrink that with an alias: select if(substring) AS shortname, sec_to_time( ) AS session ... Cheers, Kent Hoover filter fodder:database,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: Re: RTFM - Show locks
The GET_LOCK() and RELEASE_LOCK() functions might help you, if you need this feature enough to build it yourself. Cheers, Kent Hoover Feed the filter: database, query - 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
Seg Fault/No core, WHY?
Does the mysql user have write-permission in the directory where core would be written? Cheers, Kent - 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
How update 1 of two identical rows.
You can update the "first" row by adding LIMIT 1 to your update query. If the rows are identical, "first" vs. "third" doesn't matter. If it does matter, you should probably add a unique, tiebreaking field (sequence or DateTime, if you can assure there will never be 2 identical records inserted per second). Cheers, Kent - 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
Replication
I would try running multiple MySQL servers on your backup host machine, where each one is a slave to its own respective master DB. I think that keeps maintenance of replication/recovery/restore scenarios much cleaner. If your Master DB A breaks, what you have to do to recover it from Slave A is simpler if you don't have to deal with the current states of Slaves B and C, for instance. Cheers, Kent - 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
a query which cannot be fast even theoretically?
Your table bb is not indexed in a way that helps this query. Creating an index on min_number may help somewhat. If your intervals DON'T overlap each other, there's probably a much better algorithm for you to use than an SQL join. (e.g., select each number from aa in sequence, form a query for each one to determine the range into which it fits: "Select range_type from bb where some_number = min_number order by min_number limit 1" or something like that. You might be able to complete these 50,000 queries faster than 7 minutes.) Cheers, Kent - 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.server ignoring my.cnf?
Tried /etc/my.cnf in place of /etc/mysql/my.cnf ? Cheers, Kent - 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.server ignoring my.cnf?
Sorry. Sent the last message before I was done If you have a stray $DATADIR/my.cnf or a $HOME/.my.cnf, their contents will override what you have set in /etc/my.cnf. Cheers, Kent - 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.server ignoring my.cnf?
Joel: I'll take another shot. Do you have spaces in your command? Remove them. Change : socket = /var/run/mysqld/mysqld.sock To: socket=/var/run/mysqld/mysqld.sock Cheers, Kent - 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
Help with slow select count(*)
I see in your 'Explain's, the 2 queries use different indexes, the fast one uses soc_date_idx, and the slower one uses q_idx. The trick, perhaps is to force soc_date_idx to be used in the 2nd case. (Adding ORDER BY soc_date might do it, 'soc_date=X and (queue_id=Y and server_id=Z) ) might do it. I dunno. Perhaps if you do ANALYZE TABLE, it will comply with your desire. Cheers, Kent - 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: Performance of Mysql updation ..
You could very well shorten the overall elapsed time if you can configure your script into 2 (or more) scripts, each doing 1/2 of your updates, and run them in parallel. Cheers, Kent - 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
I can not alter the new index num.....
$ myisamchk --set-autoincrement-value=1 YourTable.MYI would reset the number to 1 for you. If you do not remove existing records from your table, your MySQL could generate errors as the new value collides with an existing record. Cheers, Kent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with update logging under Solaris
That log rotation script smells like the culprit to me. Exactly what is it doing? If it is clipping the file in a way that doesn't let MySQL reset the file pointer when you do the flush logs command, you could be max-ing the file out to its ulimit. Cheers, Kent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Selecting * from 2 tables
You can create a "MERGE TABLE" (read all about it in the manual)... CREATE MERGE TABLE everything ( animalname varchar(25)) TYPE=MERGE UNION=(table_1,table_2); Thereafter, you can select * from everything; Cheers, Kent Hoover - 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: Drop Database
Outside of MySQL, use a native OS command to rename or remove the directory named '#Muffin' . If you rename it 'foobar', you should be able to DROP it with MySQL. Cheers, Kent Hoover - 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: two versions of mysql running, DBI trying to connect to the wrong one.
I don't use DBI, but when I connect via 'localhost', the client does not use an IP port, but rather uses the MySQL socket file. You must be sure to have the second instance of your MySQL server come up with its own socket file, and connect to that socket instead of the default one. The other solution is to not connect via 'localhost', but via your server's IP address, for which port 3400 should work. Cheers, Kent Hoover - 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