Re: Narrowing a SELECT statement by multiple hits
On Feb 12, 2014, at 6:30 PM, Larry Martell wrote: Try adding a having clause, e.g.: SELECT `ip`,`page`,`url`,`time_stamp` FROM `ip_addresses` WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND CURDATE() - INTERVAL 1 SECOND) AND TRIM(`referrer`) LIKE '' HAVING COUNT(ip) 1 ORDER BY INET_ATON(`ip`), `time_stamp` Thank you Larry for the response. Unfortunately, I can't get it to work. The code above only returns one row. It should return 35 rows. If I remove the HAVING COUNT line, 52 rows are returned. If I add COUNT(`ip`) AS UniqueIPs to the SELECT line, that shows that there are 52 records, but still only returns one row. So I added GROUP BY `ip` and that gave me 7 rows with counts that added up to 35. Closer, but each row was a group of IP addresses where there was more than one hit. I want each hit to be returned, not a summary of hits per IP, so I don't think GROUP BY is what I need(?). I've run across a couple of sites that seem to say that an INNER JOIN would give me what I want. If that's true, then that's above my head. BTW, this on MySQL 5.5.34-cll-lve Thank you, Jenni Superior Shelving Systems The (Storage|Office|Home|Warehouse) Shelving Specialists Since 1984 Wire LAN Shelving: http://www.superiorshelving.com/mfg/nexel/pages/wire-shelving-chrome.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Narrowing a SELECT statement by multiple hits
On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote: please try filtering with a subquery that locates ip addresses with more than 1 attempt: Hi Roy, That did it! Thank you so much!!! Now I just need to study that IN clause to see what's going on there. If this is too slow, you may try materializing the subquery in a temporary table and use that table instead of the subquery. Yes, it is slow. It takes about 15 seconds to finish, but since it's run once a day via cron to email me a report, I don't think it's too much of a problem. However, I did try to see if I could do it, but there's an error somewhere in the SQL. What am I doing wrong? CREATE TEMPORARY TABLE temp_ip AS (SELECT `ip` FROM `ip_addresses` WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND CURDATE() - INTERVAL 1 SECOND) AND TRIM(`referrer`) LIKE '' GROUP BY `ip` HAVING COUNT(*) 1 ); SELECT `ip`, `page`, `url`, `time_stamp` FROM `ip_addresses` WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND CURDATE() - INTERVAL 1 SECOND) AND TRIM(`referrer`) LIKE '' AND `ip` IN (temp_ip) ORDER BY INET_ATON(`ip`), `time_stamp`; Thank you again, Jenni Superior Shelving Systems The (Storage|Office|Home|Warehouse) Shelving Specialists Since 1984 Workstation Chairs/Stools: http://www.superiorshelving.com/mfg/nexel/pages/stools.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Narrowing a SELECT statement by multiple hits
On Feb 17, 2014, at 12:36 PM, Roy Lyseng wrote: Try... `ip` IN (SELECT ip FROM temp_ip) Wow! Only 1 second to return the results now!! That's 15x faster!!! Each subquery needs to be a complete SELECT query. That's good to know. I figured that since temp_ip referenced a complete SELECT statement from before, that was good enough, but no. There's always something new to learn! Thanks again, Jenni Superior Shelving Systems The (Storage|Office|Display|Warehouse) Shelving Specialists Since 1984 Metro Shelving: http://www.superiorshelving.com/mfg/metro/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Narrowing a SELECT statement by multiple hits
Hello, I have the following SQL statement that I'd like to add to. It's used to create a report that's emailed to me showing hits to our site that didn't provide a referrer. However, I only want to report on multiple hits from the same IP address - not just a single hit by someone. How can I add a condition to only show hits by someone who's hit the site 2 or more times with the same IP? I tried GROUP BY but that didn't return all the hits - one one per IP. SELECT `ip`,`page`,`url`,`time_stamp` FROM `ip_addresses` WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND CURDATE() - INTERVAL 1 SECOND) AND TRIM(`referrer`) LIKE '' ORDER BY INET_ATON(`ip`), `time_stamp` I hope I'm explaining this correctly. Thank you, Jenni Superior Shelving Systems The (Storage|Office|Home|Warehouse) Shelving Specialists Since 1984 Computer Workstations: http://www.superiorshelving.com/mfg/nexel/pages/lan-workstations-nexel.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MySQL Daylight Savings Time Patch
Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
Thanks. Below is the notes from the version 5 manual, does that mean after I patch my OS, I may need to reload the timezone tables ? How I can determine that I have to reload the timezone tables, not might need? Or will it hurt anything if I just reload the tables anyway? Thanks. Note Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. For example, the rules for Daylight Saving Time in the United States, Mexico, and parts of Canada changed in 2007. When such changes occur, applications that use the old rules become out of date and you may find it necessary to reload the time zone tables to keep the information used by your MySQL server current. See the notes at the end of this section. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 5:03 PM To: Chris White Cc: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch NTP won't solve this problem for you. NTP, as well as most computer clocks, know nothing about daylight savings time, or about time zones. What they know is how many seconds have elapsed since the epoch. The epoch, in the case of most UNIX-based OSes, is midnight January 1, 1970. I think Windows is the same. The original Mac OS was 1/1/1904, for example. It's up to the operating system to apply rules that determine that X number of seconds (as reported by the clock hardware) since the epoch translates to some human time, based on local settings for time zone and with any daylight savings time rules for that time zone applied. My understanding is that MySQL needs no patch, but your underlying OS most likely does. I know there have been patches issued for Solaris 2.x, 9 and 10, Windows XP, and Mac OS X 10.4, and almost certainly others. HTH, Dan On 2/20/07, Chris White [EMAIL PROTECTED] wrote: Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David If you're using NTP then what's the problem? Sync to one of the ntp pools, boom your clocks are updated, MySQL uses system time and yay. I'm fairly sure you could sync 500 server this way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQLDUMP not responding to the PORT option
It's my understanding that when you specify localhost, the mysql client program will use the UNIX socket (ie, mysql.sock), and not look at port information. cheers, jenni Michael Williams wrote: Hi all, I'm trying to use mysqldump over SSH. I'm pretty familiar with port forwarding in and of itself, so I doubt that's the issue. No matter what port I forward (or to where for that matter), mysqldump still connects to the local MySQL server and dumps local data. It's as if it completely disregards my PORT option. Any ideas? My commands are as follows: %ssh -L 8080:localhost:3306 remote_server_ip %mysqldump -u user_name --port=8080 --compact --quick --all- databases dumpfile Depending on the user_name it either fails or dumps my local data. It doesn't even use --port Regards, Michael -- Jennifer Snyder Database Administrator Tribe Networks, Inc. www.tribe.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Issue on Large DB
Here how I created the table: CREATE TABLE syslogTB ( facility char(10), # OPTIONAL field for facility priority char(10), # OPTIONAL field for priority date date, # date of this log message time time, # time of this message host varchar(128), # host logging, If you have a host with # 128 characters you probably # have other issues to worry about than #someone being l33t. 8-) message text, INDEX host_index (host), INDEX date_index (date), INDEX message_index (message (50)) , #Index the first 50 characters seq int unsigned auto_increment primary key # optional sequencenumber ); And here is the query SELECT host, date, time, message FROM syslogTB WHERE host = '$host' AND date LIKE '%$date%' AND message LIKE '%$message%' ORDER BY date DESC,time DESC LIMIT . $start . , . $numresults . ); Thanks Jenn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, May 21, 2005 1:56 PM To: Jennifer Fountain Cc: mysql@lists.mysql.com Subject: Re: Query Issue on Large DB Hi, have you a query sample + show create table TBL + show indexes from TBL ? Mathias Selon Jennifer Fountain [EMAIL PROTECTED]: Hi, I am new to the list and mysql for that matter. I am trying to configure a centralized syslog server using mysql, msyslog and php. Works great until the DB grows to about 2GB. After the database gets over 2GB, running queries on a particular hostname (that has a lot of records) takes a long time and drains memory to the point I have to reboot the system. Here is the status after a single large query is ran: total used free sharedbuffers cached Mem: 38896163871504 18112 0 21336 3654652 -/+ buffers/cache: 1955163694100 Swap: 4194232 04194232 Here is a copy of my.cnf: [mysqld] datadir=/data socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer_size=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable = read_rnd_buffer_size=2M # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Any thoughts as to what I am missing? Thanks for any information! Kind Regards, Jennifer Fountain Systems Administrator/Security RB Distribution 3400 E Walnut Street Colmar, PA 18915 -- 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]
Query Issue on Large DB
Hi, I am new to the list and mysql for that matter. I am trying to configure a centralized syslog server using mysql, msyslog and php. Works great until the DB grows to about 2GB. After the database gets over 2GB, running queries on a particular hostname (that has a lot of records) takes a long time and drains memory to the point I have to reboot the system. Here is the status after a single large query is ran: total used free sharedbuffers cached Mem: 38896163871504 18112 0 21336 3654652 -/+ buffers/cache: 1955163694100 Swap: 4194232 04194232 Here is a copy of my.cnf: [mysqld] datadir=/data socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer_size=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= read_rnd_buffer_size=2M # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Any thoughts as to what I am missing? Thanks for any information! Kind Regards, Jennifer Fountain Systems Administrator/Security RB Distribution 3400 E Walnut Street Colmar, PA 18915 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1105: Unknown error with flush logs
Hi, We are running mysql-4.0.22 on Gentoo Linux. We do regular mysqlhotcopy of all databases and do regular flush logs for transaction logs. However, My flush logs failed since yesterday, the mysqladmin flush-logs gave me error: /usr/bin/mysqladmin: refresh failed; error: 'Unknown error' When I login to server, do 'flush logs' in command line, got error ' ERROR 1105: Unknown error ' Anyone has idea on how to resolve this issue, please share with us. Thanks. Jennifer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect
-- Original message -- From: none none [EMAIL PROTECTED] Perhaps you did'nt grant access to for your home workstation IP address to have permission to log into the mysql server? Check out the grant command: http://dev.mysql.com/doc/mysql/en/GRANT.html If that was the case the error would be permission denied, not error 2003. Error 2003 usually comes up when the server cannot be reached because of firewall type issues. Previous Message: = I am trying to connect from my windows box at home using mysql.exe to a MySQL server at work on a Gentoo box. My command line is: mysql -h host.domain.com -u root -p (of course the host.domain.com is replaced with my server's FQDM) The output is: ERROR 2003: Can't connect to MySQL server on 'host.domain.com' (10061) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] Using an array(-ish) in SQL queries
-- Original message --
From: -{ Rene Brehmer }- [EMAIL PROTECTED]
Task at hand: deleting or selecting (same difference) several numbers of
records using only 1 query.
My first version simply looped through all the ticked off IDs and ran a
single query for each delete routine. I've still not suceeded in getting
the delete queries to work on multiple tables at once, despite the column
names being the same. But besides this:
Multi-table deletes are new to mySQL 4.0, so if you are running a 3.x release they
won't work.
http://dev.mysql.com/doc/mysql/en/DELETE.html
My current version generates, for multi-select cases, queries like this:
DELETE FROM the_table WHERE `ID`='1' OR ID`='2' OR `ID`='3' OR `ID`='4' OR
`ID`='5' OR `ID`='6'
or similar with the SELECT statement.
[snip lots of stuff]
DELETE FROM the_table WHERE `ID` ISIN(1,2,3,4,5,6)
use IN http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html#IDX1268
If you know all the values in the array are escaped and safe you can just use
implode() to make the list for IN
$string = implode(',',$array);
$sql = SELECT FROM $table WHERE col_name IN('$string');
Notice I added single quotes around the string, that is because they will be missing
since implode only sticks the string between array elements.
However, you'd need a join that makes sense for a multi-table delete. I don't know if
it will work with a union, I have never tried, maybe somone else will chime in.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: next autoindex
-- Original message from Richard Whitney [EMAIL PROTECTED]: -- Hello! I have situation where I need to find the next autoindex of a table - not just max(field) because the next autoindex may not jive with the number created by max(field) http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html SHOW TABLE STATUS will return a field called Auto_Increment that holds the next auto increment value. There might be a more elegant way to accomplish this, but I can't remember off the top of my head. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with mysqldump
-- Original message -- From: Tom Crimmins [EMAIL PROTECTED] output $ mysqldump -u root -p govern test.sql Enter password: mysqldump: Got error: 1017: Can't find file: './govern/VT_SY_LANGUAGE.frm' (errno: 24) when using LOCK TABLES /output perror says 24 is too many open files http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html The manual says how to fix the problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
warming the cache after restart
Good Afternoon. I'm interested if any DBAs on the list have a set of scripts they run after a server restart to pull commonly accessed data into the the query and key caches. I'm currently working on a script that will run various queries from our application against the database servers after restart, in hopes that it will shorten the amount of time it takes for the caches to fill and stabalize. I'm excited to see that MySQL 4.1.1 will have direct commands to do some of this, (see http://dev.mysql.com/doc/mysql/en/LOAD_INDEX.html), but that doesn't help me now... Is there anyone else on this list who has looked at this issue or can point me in the direction of more information about it? thanks a bunch, jenni -- Jennifer Snyder Database Administrator Tribe Networks, Inc. www.tribe.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tuning suggestion for large query
The command is issued from mysql command line. Is there any parameters or options I can use without locking the table? -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Thursday, September 02, 2004 2:24 PM To: Sun, Jennifer Cc: [EMAIL PROTECTED] Subject: Re: tuning suggestion for large query On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer [EMAIL PROTECTED] wrote: Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. How exactly are you running this select * from big-table? From the mysql command line client? Is that what is using memory? It isn't clear from your post if it is the server or something else using memory. If it is the mysql command line client that is the issue, try adding a -q parameter. If you are using myisam tables, however, keep in mind that table will be effectively locked for the whole duration... but if it is the mysql command line client (which defaults to buffering everything in memory), it may be faster to use -q anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tuning suggestion for large query
I did 'handler table_name read limit large_numbers'. Is there a way I can use lower number, but automatically loop through the number and display all of the table records? Thanks. -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, September 02, 2004 10:37 AM To: [EMAIL PROTECTED] Subject: RE: tuning suggestion for large query At 04:13 PM 9/1/2004, Sun, Jennifer wrote: Thanks Mike. Seems like even with handler, the big query process is still consuming all my RAM and swap and being killed with error 'VM: killing process mysql __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)' I would like to find a startup parameter either for client or server to limit per thread memory usage. Can't help you there, sorry. I don't think that would work because MySQL is putting the retrieved rows into memory to get a snapshot of the table, so memory has to decrease. If you decrease the memory for the thread, then it will hit a wall. I thought Handler would work because it is using a cursor into the table and shouldn't be consuming much memory. You fetch 10,000 rows at a time, then fetch another 10,000 rows etc.. It should only be consuming enough memory to hold 10,000 rows. Can you post the code showing how you're using Handler? The only other thing I can suggest is to put a select statement inside of a loop and select using either a primary key, say rcd_id 1 to , export them, then select 1 to 1 and export it etc until you reach eof. This should be quite fast and consumes low amount of memory. I would recommend using the primary key instead of say select ... LIMIT offset,limit because the offset will need to read over the first 'n' offset rows and this can be quite slow. Because you're doing only part of the table at a time, you won't get an exact snapshot if people are updating the table as you are exporting it, but it will be very low on memory. Mike -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 4:39 PM To: [EMAIL PROTECTED] Subject: Re: tuning suggestion for large query At 10:40 AM 9/1/2004, you wrote: Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. Thanks. - Try the Handler command with an Index if you need the results sorted. It may do the trick. :) Mike -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tuning suggestion for large query
Thanks Marc, What version of myisam table you are talking about? We are on 4.0.20, when I ran the big table query, I tried to insert to it twice without any issues. The -q worked good for mysql client. Thanks. Jennifer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Thursday, September 02, 2004 2:41 PM To: Sun, Jennifer Cc: [EMAIL PROTECTED] Subject: Re: tuning suggestion for large query Due to the nature of myisam tables, when you are doing a query then the table will be locked for writes. Reads will still be permitted until another write request is made, at which time all further reads and writes will be blocked until the query completes. This, however, is already happening even without -q and adding the -q will likely significantly shorten the time to execute, depending on exactly how large this table is. myisam is a very limiting table type as soon as you want to do anything more than read from or write to a single row at a time using indexed lookups. innodb tables do not have this problem, although they have limitations of their own. On Thu, 2 Sep 2004 14:30:24 -0400, Sun, Jennifer [EMAIL PROTECTED] wrote: The command is issued from mysql command line. Is there any parameters or options I can use without locking the table? -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Thursday, September 02, 2004 2:24 PM To: Sun, Jennifer Cc: [EMAIL PROTECTED] Subject: Re: tuning suggestion for large query On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer [EMAIL PROTECTED] wrote: Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. How exactly are you running this select * from big-table? From the mysql command line client? Is that what is using memory? It isn't clear from your post if it is the server or something else using memory. If it is the mysql command line client that is the issue, try adding a -q parameter. If you are using myisam tables, however, keep in mind that table will be effectively locked for the whole duration... but if it is the mysql command line client (which defaults to buffering everything in memory), it may be faster to use -q anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tuning suggestion for large query
Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tuning suggestion for large query
Thanks Mike. Seems like even with handler, the big query process is still consuming all my RAM and swap and being killed with error 'VM: killing process mysql __alloc_pages: 0-order allocation failed (gfp=0x1d2/0)' I would like to find a startup parameter either for client or server to limit per thread memory usage. -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 01, 2004 4:39 PM To: [EMAIL PROTECTED] Subject: Re: tuning suggestion for large query At 10:40 AM 9/1/2004, you wrote: Hi, We have a job that do 'select * from big-table' on a staging mysql database, then dump to data warehouse, it is scheduled to run once a day, but may be run manually. Also we have several other small OLTP database on the same server. When the big job run, it would use all the physical mem and swap, all other process slow down because of this. I would like to limit the resource usage for each mysql client so that they can use only certain max amount of RAM, and don't select everything into memory before display it to users. However, I couldn't find any parameter would let me implement it. Anybody ever encounter the same issue before? Please share your experience. Thanks. - Try the Handler command with an Index if you need the results sorted. It may do the trick. :) Mike -- 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: Date Conversion Function
Not being an MSSQL expert, I don't know what MM does, and since you did not specify what type of conversions you wish to perform, I can't say exactly which function would suit your needs, but here is a link to the manual page that lists all date and time functions -- http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html -- Original message from [EMAIL PROTECTED]: -- M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (simple select) bug? version problem? or just bad SQL?
I think you need to be using: select * from clients_tb where active='on' AND first_name LIKE '%ja%' OR middle_name LIKE '%ja%' OR last_name LIKE '%ja%'; Your query is looking for names where the first, middle and last name all have 'ja' in them. If you're looking for names where -any- of them can have 'ja' in them, use the OR. Jennifer Horne Panda Voice Systems Inc. 1.888.767.2632 ext 23 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 22, 2004 9:41 AM To: [EMAIL PROTECTED] Subject: (simple select) bug? version problem? or just bad SQL? Hi, I am running this (simple?) query on mysql 3.23 : select * from clients_tb where active='on' AND first_name LIKE '%ja%' AND middle_name LIKE '%ja%' AND last_name LIKE '%ja%'; I know there are people in the table with: first name James last name James middle name Jacob etc but it does not display them...if i try with just first name then it displays the people whose first names start with ja whats wrong? is this a bug or not supported in 3.23 or I have written bad SQL? How can I do the above? Thanks, -Ryan A -- 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]
Setting SQL_BIG_SELECTS=1 for MySQL server
We've recently started using MySQL for customers with a large number of records on their systems. For the first time, one of our customers has gone over the 4 million record mark, and we're running into some problems with the MAX_JOIN_SIZE and the SQL_BIG_SELECTS. Using the control center, or command line option, we can successfully 'Set SQL_BIG_SELECTS=1' and solve the problem. So the question is, is there a way to set the server so that the default is SQL_BIG_SELECTS=1? Setting it as a variable in the my.cnf file doesn't seem to work, it causes the server to be unable to start. Is it something that needs to be set through the application accessing the database each time it opens a new connection? Or can we set it through the application globally? Forgive me if this is an obvious answer, I have spent a long time searching the documentation and other places online, but can't seem to find the solution. Thanks in advance for any assistance, Jen Jennifer Horne, Software Developer [EMAIL PROTECTED] Panda Voice Systems Inc. 1.888.767.2632 ext 23
MySQL certification [slightly-ot]
I have recently re-entered the job market and I was wondering if anyone has found that having certification really helps in landing a position. If so, which cert do you have? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there a way to find out if a table exists?
Is there some way to do something like: SELECT * FROM tables WHERE name = table_name; And get a result I could test for truth, and thus run my script? Show tables like 'table_name'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query performance
2 index on this table:
- one unique index on user_id and att_id (pk)
- one index on att_id and user_id.
I need to have the following query:
select value from user_att where att_id = ? and value like '?'
(no wildcard)
1. when I do a explain, this query use the second index. But, if
I change my
second index to att_id and value, will the performance improve?
You could add it as a third index and see which works better, but on 18
million rows that's going to probably take quite a bit of time. From
looking at your query it seems like it would be a better index than the
current one.
2. what is the difference if I change the query to
select value from user_att where att_id = ? and lower(value) =
lower('?')
will this query slower?
I could be wrong, but I believe the query won't use the index if you use
lower(). Run an explain on this query and see.
3. when compare string, is mysql sql case sensitive? It seems that it is
case in-sensitive. If case in-sensitive, the following query will
be faster?
select value from user_att where att_id = ? and value = '?'
Mysql is only case sensitive on binary and blob fields.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DESPERATE - strange access behavior after creating user priviledge
I then try to connect to the server from a remote location and get ERROR 1130: Host 'xxx.yyy.zzz' is not t allowed to connect to this MySQL server which is normal since i did not create a user yet I do this: GRANT ALL PRIVILEGES ON *.* TO stew@'%' IDENTIFIED BY '123' WITH GRANT OPTION; flush privileges; and I get this again when i try to connect using mysql -h the_host_name -u stew -p ERROR 2013: Lost connection to MySQL server during query In other words, the server was not able to connect the first time because it did not have permission, but the second time it did, and i get this lousy error again. However, I am able to do the same command and connect from my XP workstation where mysql client is installed. so I have no clue why it is not working from the server where I really need it to work. I am getting really desperate after 3 weeks of this same error. Can you please help Assuming mysql server is running on a unix box, does the connecting client have permissions to mysqld in the hosts.allow file? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: tellme Timestamp
When I run a query that has a timestamp field, How can I query on just the
date. Time stamp puts in the min, hour, and sec. All I need is the year,
month, and day.
SELECT * FROM table_name WHERE date = '2003-08-25';
select * FROM table_name WHERE DATE_FORMAT(date,'%Y-%m-%d') = '2003-08-25
or
select * FROM table_name WHERE LEFT(date,8) = '20030825'
or
select * FROM table_name WHERE date LIKE '20030825%'
or
select * FROM table_name WHERE TO_DAYS(date) = TO_DAYS('2003-08-25')
or
select * FROM table_name WHERE DAYOFMONTH(date) = '25' AND MONTH(date) =
'08' AND YEAR(date) = '2003'
or
select * from table_name WHERE date between '2003082500' AND
'20030825235959' (check the range on this, I don't use it so it might be
wrong '2003082600' might be better)
Some are quicker than others, I'm just demonstrating that there's a lot of
different ways to do it, mysql has a lot of built in functions to deal with
date/time values. I'm sure I missed a few ways. All the date and time
functions can be found in the manual
http://www.mysql.com/doc/en/Date_and_time_functions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corrupt index = mysqld freeze?
I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, but I think it's a P3 1GHz. It has several tables with 20-40 million rows and a ton of smaller tables with less than 1 million rows. All tables are MyISAM and we have fewer than 10 queries per second. The super large tables are write only for the most part, with most reads taking place in off peak hours (a cron to generate aggregate data). The smaller tables are read/write. We've been experiencing a problem where mysqld stops responding to new connections. Any active connection is fine and can run any query it wants, but all new connections get stuck in the authenticating user phase. CPU and load drop to about zero when this happens, so I don't think it's the notorious threading issue. This freeze happens when more than 10-12 connections drop at the same time, usually when a queue caused by a table lock clears out. One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. It has also been stated that multicolumn indexes are a problem, especially if they contain more than 3 columns. This goes against everything I know about mysql. In my experience if there is file corruption an error gets returned promptly. I also believe multicolumn indexes are a valuable feature. I have been told that I need to get rid of all multicolumn indexes in order to make the server stable. Needless to say, I am not very happy with this solution and don't have a lot of faith in it working. Has anyone else experienced anything similar to this, and if so what did you do to fix it? Anyone want to weigh in on the index theory because it doesn't really sound right to me, but I'm not exactly an expert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupt index = mysqld freeze?
Setting skip-name-resolve will avoid this code path and the bug. It looks like some DNS funkyness... I've implented this and so far I can't get the box to hang in authentication no matter how mean I am to it. I feel so dumb, for not thinking of this before, I swore I had already done it about a year ago when all of the other boxes were set up to skip name-resolve. I'll post if I can get it to lock up again (let's hope I can't). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupt index = mysqld freeze?
One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) A crash was recreated by running a specific query. When myisamchk ran upon restart it said the index file for the table that was being queried was corrupt. After careful observation, it was discovered that this is often the case, indexes for tables mentioned in the update log right before a crash were corrupt upon restart. I'm more inclined to believe that they are corrupt due to us killing mysqld with the tables still open, since we can't authenticate to shutdown. We also get a lot of table handler errors from myisamchk after a crash and kill, go figure. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. We aren't running Linux threads. We didn't seem to be experiencing any of the issues it helps. For a while we'd only have this happen once a month, then it was once a week. Lately it has been a few times a day, but everyone is messing with box. In my opinion, for us it definitely happens when an expensive query is run on an active table. Looking at the logs, there's always a bunch of disconnects all at once right before connections stop. I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupt index = mysqld freeze?
A crash was recreated by running a specific query. Oh. You didn't mention crashes in your first note. That changes everything. Sorry, I'm a dork. Buy crash I mean all new connections getting stuck in authentication mode. As the day wears on my Jennifer Vernacular - English translater is starting to buggy. :) Yeah, if you're killing MySQL by force, you really ought to check all tables and repir broken ones. Otherwise it's a craps shoot. My thoughts exactly. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. We aren't running Linux threads. We didn't seem to be experiencing any of the issues it helps. At least not the obvious ones. :-) We've found that on moderately busy machines here, upgrading to a LinuxThreads-based MySQL reduced CPU utiliization by 30% or so. This box sits around 1% all day unless something crazy's going on, but we've got another mysql box (more qps, more tables, but way less data) that could really benefit from this, thanks for the tip. For a while we'd only have this happen once a month, then it was once a week. Lately it has been a few times a day, but everyone is messing with box. Ugh. Yeah. Don't get me started, I can go on for days. Hmm. I hadn't noticed that yet. But I hadn't thought to look at disconnect rates either. It could be nothing, it's just the only pattern I have noticed. Unfortunately my systems knowledge isn't very strong, so I don't know if my suggestions are completely insane. The one thing I was thinking was it's has something to with releasing a lot of resources at once and not signalling that they are available again so the request just waits and waits. For some reason I'm thinking semaphores and starvation, but the only experience I really have is one OS theory course a few years ago. I don't know how to do this with threads but with LT, I'd like to identify a few of the pads for the struck threads and then get a snapshot of the call stack to see where they're waiting. I'll run this by the systems guys, thanks for the suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is query possible? (Newbie)
I have 2 tables used for an online calendar... first table fields: primary_key , start_date, event_name, event_description second table fields: primary_key, end_date Tables fields are shortened and can't be changed. My second table only contains events that have a end date. I want to create a query that will take all the fields in. If no end_date exists then set to NULL. Been playing with it all day. Hoping some advance function exists. I thought of using a temp table but there must be a better way. I am confused by your question. It think it is missing words. If you are trying to select all records from first_table that do not have a record in second_table you can use a left join and is null... SELECT * from first_table LEFT JOIN second_table USING (primary_key) WHERE second_table.primary_key IS NULL Maybe I'm missing something here, but I don't see why you would want to split your tables up that way. You can't be saving that much room, and I don't think it really goes with standard normalization conventions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tracking a delete
Ok, I know it WAS there because we have two similar tables that should contain sister records. One table has a record the other doesn't so it had to have been deleted. I need to find out WHEN it was deleted. How do I create a log of record deletes? If you keep the update log or the binlog you can look through it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is this query not working?
I am running php 4.2.2 and mysql 3.23.54. This is a PHP question, not mySQL. This is my query: $sql = SELECT MAX(esn) FROM address; // line 37 $max_esn_result = mysql_query($sql) or print mysql_error(); // line 38 $max_esn = mysql_result($max_esn_result,$i,esn); // line 39 What is the value of $i? Are you sure you want to jump to row $i of your result set? This is the error I get: esn not found in MySQL result index 5 in /var/www/html/address_entry.php on line 39 I think since you are selecting MAX(esn) is doesn't get returned as esn, it gets returned as MAX(esn), I could be wrong. A simple fix would be to get rid of the third argument you are passing to mysql_result since your query is only returning one field you do not need an offset or name. Or you can change the query to SELECT MAX(esn) as max_esn FROM address; and then use max_esn as your offset/fieldname. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Regular expresion replace possibility?
Worked like a charm! I couldn't find anything about this in MySQL docs You have to search for 'string functions' to find it. Problem is that a search for REPLACE will bring up the REPLACE syntax, not the REPLACE() Or you can look at the function index http://www.mysql.com/doc/en/Function_Index.html It lists all the functions, so whatever function you are looking for, it is usually there. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's wrong with this query?
what would be causing the error:
SELECT certificate.cert_num, master_info.uid
FROM certificate
JOIN master_info ON ( certificate.uid = master_info.uid )
LEFT JOIN endorsements ON ( certificate.cert_num =
endorsements.cert_num )
WHERE certificate.active = 1
AND certificate.referred = 0
AND certificate.status IN ('O', 'C')
AND endorsements.endorse_mode='cancel'
ORDER BY master_info.company, certificate.bound, certificate.cert_num;
ERROR 1064: You have an error in your SQL syntax near 'ON (
certificate.uid =
master_info.uid ) LEFT JOIN endorsements ON ( certificate' at line 1
I don't think the [CROSS] JOIN gets a join condition. Check the manual on
JOIN syntax
http://www.mysql.com/doc/en/JOIN.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stumped on error messages -OOPS
I am changing a database and changing the user and password of an
existing
database. when I enter the line.
mysql insert into user (host, user, password) values ('localhost',
'newuser', password ('newpswrd'));
the error 1062: Duplicate entry 'localhost-newuser' for key 1
mysql
Since you are changing the password you should be updating not inserting.
The error means you already have an entry for [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: @@identity
One should always use the LAST_INSERT_ID() or after an insert, select max(id) from myTable. Select max(id) from myTable should never be used to get the last insert id. On a high traffic table you are not guaranteed it will be the ID for the record you just inserted, another could have been inserted between the two queries. Where as last_insert_id is per connection, so you don't have to worry about getting someone else's ID. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Now() and time function bug??
After I set starttime to now(), anytime I run another update query against the row starttime changes to a new value. If starttime is the timestamp data type, and the first timestamp in the row, it will automatically update every time you update the row. This is not a bug, it is expected behavior. Read up on the timestamp data type (http://www.mysql.com/doc/en/DATETIME.html). If it is a type other than timestamp (you did not specify), then this could be a bug. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to exporting MySQL table for web publishing
Could you please advise me on how to export data in LINUX/MySQL table for web publishing? If you need the data in a mysql table displayed in an HTML table, a quick and dirty way to get a static file is to run this from the command line... #mysql -uroot -p -H -eSELECT * FROM Employee Intranet employees.html In this instance I am putting Intranet.Employee into an html file named eployees. It is just basic html for a table, you'll need to go in and add stuff like head and body tags. If you want it to be dynamic you are going to need to develop something using a scripting language. PHP and Perl could both easily accomplish this task, as could a multitude of other languages. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [MySQL] RE: Question: Connecting MySQL with DreamWeaver MX on Windows 2000
They are only sending one. It is proper form when replying to reply to both the sender and the list. Since you are on the list you are getting the list copy as well as the copy sent directly to you. I think their problem was the fact that you admitted you were just asking (vauge) questions because you did not feel like putting any effort into figuring out how to do it yourself. It is annoying when people don't put effort into helping themselves and just expect to have everything spoon fed to them. It is irksome to people who actually put a lot of effort into finding out the answer to their questions before posting to have to sort through a bunch of RTFM type posts. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 9:55 AM To: [EMAIL PROTECTED] Subject: Re: [MySQL] RE: Question: Connecting MySQL with DreamWeaver MX on Windows 2000 not sure what the hell your problem is, but if you don't want to help then don't. complaining that i need help when 99% of the manual refers to remost server usage isn't really necessary. all i need to do is make a connection between dwmx and mysql. that's it. please don't respond to anything else i write. it's a waste of time. btw: there's no need to send two messages. one is enough. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with sum()
Because you are using a float. There has been much discussion of this on the list, search the archives. Floats aren't so good if you need your math to be right. http://www.mysql.com/doc/en/Column_types.html -Original Message- From: LeTortorec, Jean-Louis [mailto:[EMAIL PROTECTED] Sent: Thursday, April 03, 2003 1:40 PM To: '[EMAIL PROTECTED]' Subject: Problem with sum() Hello every one, I have a simple table, with a float field called quantity. In this table, there are 2 records: 1 with quantity=0.7, and 1 with quantity=0.1. When I do a select sum(quantity) from mytable, I got 0.798956919 instead of 0.8. Does any body know why? Thanks. Jean-Louis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opposite of DISTINCT()
Why the join? Why not just select p1.email, count(*) as occurances from table p1 group by p1.email having occurances 1? Am I missing something? -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 12:29 PM To: [EMAIL PROTECTED] Mysql. Com Subject: RE: Opposite of DISTINCT() Bob, You have to do a self join - try this off the top of my head... - Select p1.email FROM tblperson p1, tblperson p2 WHERE p1.email = p2.email GROUP BY p1.email HAVING count(p1.email) 1 Andy -Original Message- From: Bob Sawyer [mailto:[EMAIL PROTECTED] Sent: 01 April 2003 21:04 To: MySQL List Subject: Opposite of DISTINCT() I know that using SELECT DISTINCT(colname) will result in output that does not contain any duplicates from that column. But how would I output JUST the duplicates? If I have as part of a table a column containing email addresses, and I want to list just the duplicate addresses rather than the distinct addresses, what's the syntax there? Thanks, Bob -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rename/Copy a Table????
You can rename a table by using the alter syntax http://www.mysql.com/doc/en/ALTER_TABLE.html ALTER TABLE tbl_name RENAME [TO] new_tbl_name -Original Message- From: Mark Wilson [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 4:41 PM To: Mysql Mailing List Subject: Rename/Copy a Table (I tried to post this once, but it didn't show up - sorry if this is a duplicate!) Can you rename a table? If so, how? If not, is there an easy query to run to copy a table? (Preserving the data as well as the structure, of course.) Couldn't find it in my MySQL on-line manual... -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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: Replication don't work.
It the master actually writting to the binlog? What does the master say when you run SHOW MASTER STATUS? Is the size of the binlog growing? -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 1:29 PM To: [EMAIL PROTECTED] Subject: Replication don't work. There are two weeks i try it...but...the replication don't start!! I've follow the istruction http://www.mysql.com/doc/en/Replication_HOWTO.html without success. In the slave i have: Show slave status; *** Headeranswer note *** Slave_Running YES Last_Error none SHOW PROCESSLIST *** Header answer *** User system user Host none Db NULL Queryconnect Time (the time passed from the mysql start) StateReading master update I think the database is in updating...but the table are the samei don't have new data!! What can i do??? :-( Help! Massi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication don't work.
What position and in what binlog does your slave show it is at? It might be helpful if you post the entire output of show slave status (minus user and host if you are concerned about that for some reason). -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 2:04 PM To: 'Jennifer Goodie'; [EMAIL PROTECTED] Subject: R: Replication don't work. The error.log on the master dont' report anything... MySql: ready for connections Other info Show master status; *** Headeranswer note *** File webserver-bin.008 Position 73 SHOW PROCESSLIST *** Header answer *** User userreplica Host X Db NULL Command Binlog dump Time (the time passed from the mysql start) Queryconnect Time (the time passed from the mysql Stateslave connection: waiting for binlog update :-/ Massi -Messaggio originale- Da: Jennifer Goodie [mailto:[EMAIL PROTECTED] Inviato: lunedì 31 marzo 2003 23.41 A: trashMan; [EMAIL PROTECTED] Oggetto: RE: Replication don't work. It the master actually writting to the binlog? What does the master say when you run SHOW MASTER STATUS? Is the size of the binlog growing? -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 1:29 PM To: [EMAIL PROTECTED] Subject: Replication don't work. There are two weeks i try it...but...the replication don't start!! I've follow the istruction http://www.mysql.com/doc/en/Replication_HOWTO.html without success. In the slave i have: Show slave status; *** Headeranswer note *** Slave_Running YES Last_Error none SHOW PROCESSLIST *** Header answer *** User system user Host none Db NULL Queryconnect Time (the time passed from the mysql start) StateReading master update I think the database is in updating...but the table are the samei don't have new data!! What can i do??? :-( Help! Massi -- 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: Could we make this a web discussion forum?
Just my two cents -Original Message- From: Seth Brundle [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 3:29 PM To: David Brodbeck; [EMAIL PROTECTED] Subject: Re: Could we make this a web discussion forum? I really hate on-line forums. They're difficult to track because I must remember visit them daily. Agreed. I don't need yet another web page to keep track of. I prefer to let my mail reader thread up the messages on this list, then I browse through every now and then and delete everything with a subject that doesn't look interesting. It's much more convenient than a web forum, and much faster too, because Email lists are essentially push technology. It assumes that becuase you are interested in something, you are insterested in it every day. There is absolutely no information that I need pushed to me every day. If I want it, i will go there and participate. So don't subscribe to the list, check one of the many online archives when you feel like checking them (I like http://marc.theaimsgroup.com/ ). I like getting posts in my email box, that is why I subscribed the the mailing list, to get them as email. If it were a web forum I would have to wait for everyone to check the forum after I posted my question, then check back after they had checked in order to get replies. This way, people get my question immediately and the answers come to me immediately. Think of it this way - lets say you use 1,000 pieces of software over the course of the year - this is easy to imagine if you consider ls(1) to be a piece of software. Its pretty easy to imagine how impossible it would be to maintain subscription to 1,000 mailing lists (I get annoyed with 10!). So only subscribe to the ones that interest you. I have Microsoft Office installed on my computer but I am not on any Microsoft lists, because I don't care to be. So what makes MySQL so special? I am interested in it so I thought it was special enough to sign up for the list, if you don't, don't. I don't have to wait for some remote, overloaded server to respond. I dont understand this one at all. The amount of people on the list checking a single website constantly (messages come in all day long, you'd have to check often, even more frequently if you were waiting for an answer) would probably overload a webserver and make it slow to respond. The amount of hardware you'd have to throw at it would be a lot more than required to run a mailing list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication don't work.
If they are at the same position everything should be fine. What makes you think replication is not working? How are you determining that there has been no change in data? How are you determining that something was done on the master that the slave should have replicated? -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 2:20 PM To: 'Jennifer Goodie'; [EMAIL PROTECTED] Subject: R: Replication don't work. The binlog and the pos are the same in master and slave ! Webserver-bin.008 pos 73 Ehmm...i don't know how to save on file the output of show master/slave status -Messaggio originale- Da: Jennifer Goodie [mailto:[EMAIL PROTECTED] Inviato: martedì 1 aprile 2003 0.11 A: trashMan; [EMAIL PROTECTED] Oggetto: RE: Replication don't work. What position and in what binlog does your slave show it is at? It might be helpful if you post the entire output of show slave status (minus user and host if you are concerned about that for some reason). -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 2:04 PM To: 'Jennifer Goodie'; [EMAIL PROTECTED] Subject: R: Replication don't work. The error.log on the master dont' report anything... MySql: ready for connections Other info Show master status; *** Headeranswer note *** File webserver-bin.008 Position 73 SHOW PROCESSLIST *** Header answer *** User userreplica Host X Db NULL Command Binlog dump Time (the time passed from the mysql start) Queryconnect Time (the time passed from the mysql Stateslave connection: waiting for binlog update :-/ Massi -Messaggio originale- Da: Jennifer Goodie [mailto:[EMAIL PROTECTED] Inviato: lunedì 31 marzo 2003 23.41 A: trashMan; [EMAIL PROTECTED] Oggetto: RE: Replication don't work. It the master actually writting to the binlog? What does the master say when you run SHOW MASTER STATUS? Is the size of the binlog growing? -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Monday, March 31, 2003 1:29 PM To: [EMAIL PROTECTED] Subject: Replication don't work. There are two weeks i try it...but...the replication don't start!! I've follow the istruction http://www.mysql.com/doc/en/Replication_HOWTO.html without success. In the slave i have: Show slave status; *** Headeranswer note *** Slave_Running YES Last_Error none SHOW PROCESSLIST *** Header answer *** User system user Host none Db NULL Queryconnect Time (the time passed from the mysql start) StateReading master update I think the database is in updating...but the table are the samei don't have new data!! What can i do??? :-( Help! Massi -- 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: Slow Inserts
I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). It will change a char to varchar if there is another column of variable size in the table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: granting privileges using wildcards
That would grant the user select on everything in every database no matter what host they are coming from. He wants to only grant on specific tables, and did not mention anything about allowing from all hosts. To answer the original question, I have tried a lot of different ways, but the only solution I have found is granting on the entire database or specifing each table in the tables_priv table. I go with the second option because although it is more work I like only allowing as much access as needed. The wild cards just don't seem to work in this instance. Has anybody had any success with it? -Original Message- From: Black, Kelly W [PCS] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:14 AM To: 'Dimitar Haralanov'; [EMAIL PROTECTED] Subject: RE: granting privileges using wildcards I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Dimitar Haralanov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:03 AM To: [EMAIL PROTECTED] Subject: granting privileges using wildcards Hi, I have been trying to find information on the following question but have been unable to do so: Is it possible to grant options to a user using wildcards. For example: let's say that I have a database named 'db', and a user 'admin' who has full privileges. The user 'admin' will add and delete tables to 'db' but any table that is added/deleted will have the following name format 'table_[A-z0-9]'. What I would like to be able to do is grant select privileges to any and all of the tables 'table_*' to a user 'non_admin'. Is this possible and if it is, how can I do that? Thank you for your help! -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql install---help please
You said that you changed the password to the password you wanted, but in all the examples you are trying to access the server without a password. Try using the -p flag so it prompts you for your password, then type in whatever you set the password to be. -Original Message- From: katherine bjork [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 3:02 PM To: mysql Subject: mysql install---help please Tried for the second time to install mysql on my mac and again a problem during the install related to the password. I typed in /usr/local/mysql/bin/mysqladmin -u root password 'new-password' and changed 'new-password' to the password I wanted. Got the -- on the next line but nothing else so I assumed it worked. When I try to do anything such as check to see if mysql and php are happy I get Warning: mysql_connect() [function.mysql-connect]: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /Library/WebServer/Documents/mysql_up2.php on line 5 Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /Library/WebServer/Documents/mysql_up2.php on line 9 Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /Library/WebServer/Documents/mysql_up2.php on line 9 Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) When I go back and try /usr/local/mysql/bin/mysqladmin -u root password 'new-password' again I get /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Also tried /usr/local/mysql/bin/mysqladmin -u root -h $hostname password 'new-password' but was denied. How the heck do I give myself access to mysql? Please don't point me to the mysql.org page...been there I do not understand the grants et al. Need plain english as in explicit instruction on what to do. BTW version is 4 on mac osx running apache 1.3 with php 4.3 Katherine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Maybe OT: Anyone Familiar w/ Dreamweaver MX: PHP Web Development?
I don't use Dreamweaver so I have no clue what the interface is like. But basically the php function mysql_connect or mysql_pconnect will be somewhere in the page. It takes the username, host, password ect. as arguments. You script might be set up with variables that hold these defined at the top or something it is hard to say without looking at the script, but I'm guessing it doesn't so much matter in this case, find mysql_connect or mysql_pconnect and put the arguments in. Here's links to the PHP man page on the functions so you can get the argument order: http://www.php.net/manual/en/function.mysql-connect.php http://www.php.net/manual/en/function.mysql-pconnect.php -Original Message- From: Stephen Tiano [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 5:22 PM To: [EMAIL PROTECTED] Subject: Maybe OT: Anyone Familiar w/ Dreamweaver MX: PHP Web Development? Well, MySQL is involved to the extent that the Web Development of the title does involve MySQL database-driven applications. So, is anyone familiar with this book. I'm in one of the last chapters and I am hopelessly lost trying to find the proper places to input my username, password, and db name in an exercise involving hand-coding of a file called create_users.php. A little help, please? Anyone? Thank you. Steve Tiano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlimport: Error: Can't get stat of
Probably a permission problem. If it cannot read the file it cannot import it. monster perror 13 Error code 13: Permission denied -Original Message- From: James E Hicks III [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 11:48 AM To: Mysql Subject: mysqlimport: Error: Can't get stat of Why am I getting this error? I have looked on google and it said to use the full path when naming the import file. I have done this and still get the error. What else could I be doing wrong? # mysqlimport -d --fields-optionally-enclosed-by=\' --fields-terminated-by=, - -use r=userid --password=pword DB_2update /fullpathto/thefile.SQL mysqlimport: Error: Can't get stat of '/fullpathto/thefile.SQL' (Errcode: 13), w hen using table: thefile For the filter. sql, query James E Hicks III Noland Company 2700 Warwick Blvd Newport News, VA 23607 757-928-9000 ext 435 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fulltext search
You don't. http://www.mysql.com/doc/en/Fulltext_Search.html There was a discussion on this mailing list last week or the week before on when innoDB support would be implemented. I'm too lazy to look it up, you should search the list archives for it. -Original Message- From: Sidar Lopez Cruz [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 1:01 PM To: MySQL Subject: fulltext search how can i implements a fulltext search engine on InnoDB tables? i need it... :-) Sidar Lopez Cruz - Cero Riesgo, S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BEGINNER QUESTION.
ALTER TABLE table_name DROP col_name http://www.mysql.com/doc/en/ALTER_TABLE.html So... mysql use hitcounter; mysql ALTER TABLE info DROP count; Assuming info is the table name and count is the column you'd like to drop. -Original Message- From: Wileynet [mailto:[EMAIL PROTECTED] Sent: Monday, March 24, 2003 5:27 PM To: [EMAIL PROTECTED] Subject: BEGINNER QUESTION. I have looked everywhere online, books. I simply would like to DELETE a FIELD from the command line. I have a database called hitcounter. Inside hitcounter I have one table with 3 fields. I've tried... DELETE FROM info FIELD count; ALTER info DELETE count; DROP count; Can someone please tell me how to do this, OR do you have to DROP the database and start over -thank you in advance Wiley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqld got signal 11; (CRASH max-3.23.51) What do I need to do to clean up?
I wouldn't run 3.23.51, there have been major security patches since then. I always mess up the byte math, but it looks to me like you have 2 gigs of ram in your box and you are allocating 2.3 gigs to mysql. With 263 connections you would have been using about 1.4 gigs, if you have anything else running on the box, this might be a problem. Whenever I've had a server getting the signal 11 crash adjusting my my.cnf has solved the problem. I would read the manual section on server tuning. I don't think you want mysql to use swap, I don't know, I try to stick with just under the total amount of ram in a box that is only running mySQL and under 40% in a box that is not dedicated (depending on what else is running). Of course there's not really any real logic, math or science to that, it's just what I have found works on our boxes. -Original Message- From: Joe Smith [mailto:[EMAIL PROTECTED] Sent: Friday, March 21, 2003 10:53 AM To: [EMAIL PROTECTED] Subject: mysqld got signal 11; (CRASH max-3.23.51) What do I need to do to clean up? Had my first mysqld crash today after a solid 4 month uptime.The details are below. The DB restarted, and I haven't been able to detect any corruption. Is there anything I should be running to ensure the integrity of the Innodb databases? Running: mysql-max-3.23.51, Innodb databases, Dual Intel 933s 2 Gigs ram 2 gigs swap mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=263 max_connections=500 threads_connected=31 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 2439208 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806bb15 0x82c1328 0x82c28c3 0x82bfb44 0x80cbb40 0x8073a4d 0x80753c8 0x8071324 0x80707f7 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instr uctions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x6a856288 is invalid pointer thd-thread_id=42019698 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 42019698 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash Number of processes running now: 0 /prod/mysql-max-3.23.51-log/bin/resolve_stack_dump -s mysqld.sym -n stack.trace 0x806bb15 handle_segfault__Fi + 425 0x82c1328 pthread_sighandler + 184 0x82c28c3 __pthread_unlock + 147 0x82bfb44 pthread_mutex_unlock + 164 0x80cbb40 mysqld_list_processes__FP3THDPCcb + 1780 0x8073a4d mysql_execute_command__Fv + 7161 0x80753c8 mysql_parse__FP3THDPcUi + 72 0x8071324 do_command__FP3THD + 1316 0x80707f7 handle_one_connection__FPv + 659 Is this a known issue? I'm guessing I should upgrade to mysql-4.12 now... Thanks! Joe - 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: upgrading mysql
On a linux box you do not have to stop everything like on Windows. We always leave our current version running, install the upgrade in a new location with a new data dir (a snapshot of the live one). We run the new install on a different port than the live install so we can test it and what not before switching over. We always leave our installs in a directory that gives the version info like mysql-3.23.55-pc-linux-i686 and then symlink a directory named mysql to the version that should be live. So when we decide it is time to go live with the new install we change the conf to point to the correct data dir and to be on the right port, stop mysqld, change the symlink and then restart with the new version. This allows for easy rollbacks just in case. We have had really poor luck with building from source on our VA Linux box and have used the binaries for the last two or three upgrades. Our builds were really unstable and buggy under a high load, but the binaries have been great. Hope that helps -Original Message- From: Tab Alleman [mailto:[EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:14 PM To: [EMAIL PROTECTED] Subject: RE: upgrading mysql [EMAIL PROTECTED] wrote: Can someone help point me in the right direction? Thanks I just finished a practice upgrade (on a back-up server) myself and will be upgrading the real server during off-peak hours soon. We are running win2k servers so I can't speak directly to any linux issues, but generically speaking, upgrading to 3.23.56 was easy: I downloaded the installation files to my server and unzipped them. Stopped the MySQL service AND mysqladmin (you will have errors if you don't do this!). Then ran the setup.exe, restarted the service, checked mysql admin and saw that the version had been updated, and smiled. I would imagine if you follow the corresponding linux-steps, you should have no problems. Good luck, Tab mysql - 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: Strange problem on the difference of mysql connection between cgi and shell
It is not strange, it is expected behavior. A browser can only handle so many lines of html before getting all wonky. Running a query that takes over 5 minutes to run in a web application is a bad idea, no one is going to want to sit there and wait for it and if there is no output for an extended period of time newer browsers will be helpful and re-request the page, causing lock issues and such. I would suggest trying to speed up the query, you need to make sure you are using indexes. or you might need to use summary tables. You will also want to only display a portion of your result set at a time as 10,000 records is probably going to crash browsers. You will also need to flush output to prevent the browser from re-requesting, you also might want to put something in to prevent the user from hitting reload (such as checking at the beginning to see if the query is already running, most of the time web users do not understand that for every time they hit reload their wait time is going to increase. -Original Message- From: Liu Haifeng [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 7:23 PM To: [EMAIL PROTECTED] Subject: Strange problem on the difference of mysql connection between cgi and shell Hi, I am coding a cgi program to query the records from the mysql tables. When I execute the sql query (which is a join operation on two tables) in the mysql shell, the results are shown correctly. However, if I use cgi script to execute the same query, the web browser just crashed after a while (5 or 6 minutes which is the same as the query execution time ). I am guessing the problem is relevant to the larger result (more than 10,000 records), because if I execute the query against another two tables which have the same structure but contain the less records, both cgi script and shell method can work. So anybody can help me figure out the problem? I have adjusted the configuration of mysqld to be a huge one but still failed. Regards Haifeng Liu - 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: mysqladmin processlist = weird in version 4.0.12
It says in the change log that it added the port to make it easier to see where the connection is coming from. 146.101.143.72:48753 gives you a lot more information than host1 From the release announcement -- * `SHOW PROCESSLIST' will now include the client TCP port after the hostname to make it easier to know from which client the request originated. -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 2:36 PM To: [EMAIL PROTECTED] Subject: mysqladmin processlist = weird in version 4.0.12 Hi all, Anyone know why mysqladmin processlist is not showing the host that is connected, but instead is showing the following in v4.0.12: truncated excerpt +-+-+--+--+ | Id | User| Host | db | Command +-+-+--+--+ | 530 | fcgi| 146.101.143.72:48753 | multimap | Sleep | 536 | fcgi| 146.101.143.72:48139 | multimap | Sleep | 545 | fcgi| 146.101.143.72:45618 | multimap | Sleep | 556 | fcgi| 146.101.143.72:49311 | multimap | Sleep | 570 | fcgi| 146.101.143.72:40745 | multimap | Sleep It used to show: +-+-+---+--+ | Id | User| Host | db | Command +-+-+---+--+ | 530 | fcgi| host1 | multimap | Sleep | 536 | fcgi| host2 | multimap | Sleep | 545 | fcgi| host3 | multimap | Sleep | 556 | fcgi| host2 | multimap | Sleep | 570 | fcgi| host3 | multimap | Sleep Any ideas anyone? Cheers, Andrew mysql,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
RE: Replication error
What does it say when you run SHOW MASTER STATUS on the master? Are you sure the slave is the problem and not the master? -Original Message- From: trashMan [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 7:29 AM To: [EMAIL PROTECTED] Subject: Replication error Please.seven days to try it!! Help me!! :-( -- Hello, I've tried to setup a replication but ...i've several problem! I've follow the manual istruction http://www.mysql.com/doc/en/Replication_HOWTO.html But the slave don't start the replica. SHOW SLAVE STATUS on SLAVE return SLAVE:running SHOW PROCESSLIST on SLAVE return reconnecting after a failed read Any suggestion about this?? Please, help me! I don't know what can i do! The master and the slave are not in the same network: the master is a server located in a webfarm and the slave is my pc. If i try to connect me to mysql MASTER from my pc via mysql --host= --user=userforreplica --password=pwdforreplica i enter but i can not do nothing. Thanks Massimiliano and samuela - 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: command line exectution of query(newbie)
Database name goes after options. It is showing you the help page because it does not understand what you are passing to it. It's kind of saying Here, look at this, this is how I work, follow these directions they might help you out. -Original Message- From: Anil Garg [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: command line exectution of query(newbie) Hi, What am i doing wrong: # ./mysql database name -e delete * from table name where id 5; I get the mysql help page on exectuting the above command. The mysql verison i am using is : 3.23.52. Thanks and regards anil. - 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 to show chinese in phpmyadmin
Hi, I am new in programming. So hope to get some helps from your precious experiences. I am using mysql-3.23 in my FreeBsd4.3 (English version) server. And I am also using phpMyAdmin to view and manage all the databases, tables and fields from a workstation IE browser to access my server, which is very convenient and straight forward. I designed some web pages, which allow me to input data records to my database. I can type simplified Chinese and English records from a workstation into my database, and I also can retrieve those Chinese data records from an workstation's IE browser and those Chinese characters can be shown properly, on the condition of those pages' charset is gb2312. There is one problem that when I use phpMyAdmin to access my databases, and I found that those Chinese records in the table only show ÏÖÓ®¢¿â´æ»õÎïÊÇ·ÉÂíÅç©àºÍÄÏ°©Åç©à Which is unreadable. Can anybody tell me how I can fix this problem? Thanks very much Jennifer - 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: Support in apache for MySQL
What language are you using? As far as I know Apache doesn't connect to databases, you have to use a programming language to do that. I'm guessing you are using PHP and you have possibly upgraded it without compiling in mysql support. -Original Message- From: Henning Olsen [mailto:[EMAIL PROTECTED] Sent: Friday, February 28, 2003 10:12 AM To: [EMAIL PROTECTED] Subject: Support in apache for MySQL Hello I use Apache/1.3.27 Server I use to use a mysql-connection, but now the apache support is gone. Trying to connect to my MySQL-database gives following result: Fatal error: Call to undefined function: mysql_pconnect() in... How do I get the MySQL-support back? Henning (newbie) - 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: getting NULL in auto_increment column
It does not sound like your data is properly normalized. You might want to take a look at the way you have things set up, this sounds like it cause problems later on. -Original Message- From: Danny [mailto:[EMAIL PROTECTED] Sent: Monday, February 24, 2003 1:54 PM To: [EMAIL PROTECTED] Subject: Re: getting NULL in auto_increment column gerald_clark writes: Danny wrote: Can you update an auto_increment with NULL in MySQL? Obviously if you try and put a NULL in directly it will increment to the next integer. Is there some way to actually get a NULL in there instead of the integer? No. Why would you want to? Because I have a column that is normally NULL since I normally don't have information about that column. Occasionally I acquire information about it and assign it to a category. AUTO_INCREMENT is useful because I don't have to think about what the next new category will be. Instead of NULL I could use 0, if I don't have information, but NULL would be the more natural state for lack of information. No biggy though. - 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: List of Tables
SHOW TABLES; Then treat the output as a normal result set. see the manual for more about SHOW syntax http://www.mysql.com/doc/en/SHOW.html -Original Message- From: Jeff Pearson [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 20, 2003 2:31 PM To: [EMAIL PROTECTED] Subject: List of Tables I am writing a vb.net application to do some db management on MySQL. Ive got it pretty far but am at a point where I am stuck. I know with php there is a list tables function. Does anyone know of a way to list the tables of a given database outside of php? Any help would be GREATLY appreciated. Jeff Pearson - 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: Is this query possible...
Yes. You can use INSERT INTO table select from .. If this matters, I will be doing this in 2 different applications. 1 database is MySQL, and the other is MS Access. Not if the two tables are on different database servers. But maybe I'm reading that wrong and the application is just being developed to run on two different instances. -Original Message- From: KH Chiu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 19, 2003 6:52 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Is this query possible... Yes. You can use INSERT INTO table select from .. Regards, All, Is this query possible to do: I am going to select an id from a table SELECT order_id FROM orders WHERE order_start = 1324 I will then use the order_id from this query and insert it as well as some others values I have into another table. So in summary, I want to perform a SELECT and INSERT in the same database query. Can I do it? If this matters, I will be doing this in 2 different applications. 1 database is MySQL, and the other is MS Access. -jonathan - 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: whats the best way to imput text and formated text into a mysql database
You could have PHP escape it for you. Look into the addslashes function. It adds slashes to escape characters. http://www.php.net/manual/en/function.addcslashes.php If your server has magic quotes turned on it will automatically escape characters, but it is not a good idea to rely on that. If you want to thank someone, I would just reply to that person, not to the list. -Original Message- From: ahimsa [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 08, 2003 1:21 PM To: [EMAIL PROTECTED] Subject: whats the best way to imput text and formated text into a mysql database Thank you so much everyone for all of your support! I asked a question re: a mysql post error when the text containd apostrophe and commas. I got feedback re: delimiting and putting a / before each comma and appostrophy. The problem is that this is not feasable for me to do this on so much text, besides its messy etc. does anyone have any suggestions on the best way to imput text into a mysql database that is simple that will accept what ever text, It would be great if it could be formated text. But is this possible. Maybe i should be using a blob field instead of a text field or somethign. Im not sure. Im using mysql with php in my forms. i maybe need to do all of this differently. My goal is to imput pages and pages of text into text fields. Thank you all so much. Also Is it ok to write a thank you to people when they reply or is it standard etiquette to not reply ? Thanks again so much. leeann - 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: Opposite selection...
A Left join using IS NULL will work. You can get the syntax and see an example in the manual http://www.mysql.com/doc/en/JOIN.html From the maunal... If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table: mysql SELECT table1.* FROM table1 -LEFT JOIN table2 ON table1.id=table2.id -WHERE table2.id IS NULL; This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course. See section 5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN. -Original Message- From: Nicholas Stuart [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 9:25 AM To: [EMAIL PROTECTED] Subject: Opposite selection... Ok I'm stumped on what I think should be a somewhat simple query. What I have so far is a list of names that is in a list of projects AND in a the main contact list by doing the following query: SELECT p.name, p.company FROM contacts c, projects p WHERE CONCAT(c.firstName, , c.lastName) = p.name AND c.company = p.company This is good and works correctly, what I need now is the opposite of this. The names that are in the project list but NOT in the contact list. If I had some subqueries this would be a simple NOT IN :) but as I dont (mysql 3.23.55) I'm not sure how to attack this. Thanks for any pointers/advice. -Nick - 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: Using SET in the INSERT statement
Are you referring to the data type SET or the Command SET? The manual page you point to is talking about the data type, but usually when seen in an insert it is the Command. I can't recall seeing the datatype referred to in an INSERT, but maybe my brain isn't fully on yet today. http://www.mysql.com/doc/en/INSERT.html -Original Message- From: boclair [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 8:19 PM To: [EMAIL PROTECTED] Subject: Using SET in the INSERT statement I notice some of my colleagues using SET in INSERT statements. DuBois makes a short reference on p565, DoorStop1. The Manual has some mention in http://www.mysql.com/doc/en/SET.html I sort of get it but I am looking for a fuller explanation. Louise - 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: insert after delete is not atomic
This is probably a dumb question, but are you running all the queries in
eod_stocks? I ran all the queries and they worked fine. I undotted the
select though because my database was not called eod_stocks. Since you did
not include the part where you select the database to run these queries in,
one cannot assume you are creating, deleting and inserting into the same
database you are selecting from, since the select is not defaulting to the
selected DB.
-Original Message-
From: Mudit Wahal [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 04, 2003 8:03 PM
To: [EMAIL PROTECTED]
Subject: insert after delete is not atomic
Description:
I have a delete statement and an insert statment. I'm replacing a line but
I want
to make sure, that its deleted. The insertion is successful as per sql
batch output.
But when I dump the data from the table, there is no record for the date !
Seems like
delete operation happened after the insert.
How-To-Repeat:
SQL Input Script
create table if not exists MSFT (date DATE not null, close DECIMAL(10,2),
high DECIMAL(10,2), low DECIMAL(10,2),
volume MEDIUMINT, yest DECIMAL(10,2), PRIMARY KEY date (date));
delete from MSFT where date='2003-02-04';
insert ignore into MSFT
Values('2003-02-04','47.3200','47.9400','46.8800','406440','47.8000');
===
SQL Output from the script
===
--
create table if not exists MSFT (date DATE not null, close DECIMAL(10,2),
high DECIMAL(10,2), low DECIMAL(10,2),
volume MEDIUMINT, yest DECIMAL(10,2), PRIMARY KEY date (date))
--
Query OK, 0 rows affected (0.00 sec)
--
delete from MSFT where date='2003-02-04'
--
Query OK, 0 rows affected (0.01 sec)
--
insert ignore into MSFT
Values('2003-02-04','47.3200','47.9400','46.8800','406440','47.8000')
--
Query OK, 1 row affected (0.02 sec)
--
SQL Output from query for the date 2003-02-04
===
mysql select * from eod_stocks.MSFT where date = '2003-02-04';
Empty set (0.01 sec)
-
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: TIMESTAMP field is updated unintentionally
Read the section in the manual about timestamps, this is expected behavior, it is how it is supposed to work. http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically... -Original Message- From: Marco Deppe [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 3:18 AM To: [EMAIL PROTECTED] Subject: TIMESTAMP field is updated unintentionally Hi, I was already questioning my sanity, but the problem below is reproduceable: This is how my table looks: mysql describe T_ORDH; --+--+-+++ Field |Type |Null |Key |Default |Extra --+--+-+++ PK_ID |int(10) unsigned | |PRI |NULL|auto_inc ERSTELL_DATUM |timestamp(14) |YES ||NULL| STATUS|smallint(5) unsigned | ||0 | If I do mysql update T_ORDH set STATUS=2 where PK_ID=26272; ERSTELL_DATUM is set to the current date. I know that a timestamp takes the current time, if set it to NULL, but since I'm not touching it, it shouldn't change, should it? A quick workaround is mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM - where PK_ID=26272; The big question: Is it a bug or a feature? (mysql Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686)) - 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 Truncated returns
The problem is probably in either your PHP or HTML, not MySQL. It is common to store mutliple words separated by a whitespace in varchar and text fields. -Original Message- From: Anna Noel Leavitt [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 8:40 AM To: [EMAIL PROTECTED] Subject: Re: MySQL Truncated returns Hello all- I am using MySql, php and forms in HTML to update information. When I select results from column type VARCHAR, if the information stored in the column has a space in it (for example, Tampa Bay), it will only return Tampa (I have the length set at 30 so it is not a length issue). I tried changing the column type to text to no avail. I searched the documentation and couldn't figure it out. Can someone provide some insight for me? Thanks- Anna - 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, PHP and Javascript
PHP is server side, javascript is client side, they do not interact with each other. PHP gets parsed by on the server before the page is rendered, javascript is interpreted by the browser. You can either have PHP generate the javascript or just put it in the page. Rollovers have nothing to do with backend programming. -Original Message- From: Steve Davies [mailto:[EMAIL PROTECTED]] Sent: Friday, January 31, 2003 12:27 AM To: [EMAIL PROTECTED] Subject: MySql, PHP and Javascript Hi All Is it possible to mix javascript and PHP in the same script?? I have a number of web based apps written in PHP/MySql and while they are functionally pretty good they are aesthetically garbage. I'll like to pretty up the interfaces with rollovers etc, but haven't got time to learn JS properly especially if I have to completly re-write the functionality. Any help greatly appreciated Steve - 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: timing
They run in 8 minutes on my test server , about 4 minutes each. Printing to a PDF driver. On the Production Server they take HOURS to run and print. The production server is probably trying to do other stuff at the same time, making your reports slower. A machine with one active connection will run queries a lot quicker than an identical machine with 50 connections. Think about what happens when you try to do 50 things at once :) Is the test server using a localhost connect and the production not? Localhost is a lot faster than TCP/IP when running a large amount of queries. -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 6:08 AM To: [EMAIL PROTECTED] Subject: timing I have 2 Crystal Reports 8.5 (for the record I didn't make these reports) that draw straight from the MYSQL database. They use only 1 table. I am calling them to print from VB 6.0 Theses reports have lots of complex calculations in them, counts , sums and groups. My test server has the same code , same database , same indexes , same amount of memory as the Production server, My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal installed on my test server. The Production server has MYSQL 3.23 and an older version of MYODBC then I have. No Crystal Reports installed. And the server is a faster machine then mine. They run in 8 minutes on my test server , about 4 minutes each. Printing to a PDF driver. On the Production Server they take HOURS to run and print. Any ideas... Mary Stickney TAG-TMI Data Warehouse Programmer 402-474-7612 x 3099 [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
RE: timing
You are positive there is nothing else running while you are trying to do your reports? Have you tried keeping an eye on the processlist to see where it is getting stuck and to make sure there is nothing else going on? Where does the production server get its data from? Are their updates going on while you are running your reports? Is the production server tuned for the type of complex queries you are running? -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 12:02 PM To: Jennifer Goodie; [EMAIL PROTECTED] Subject: RE: timing no it is dedicated to the DataWarehouse Report Processor. runs one report at a time from a ReportQueue. both have a local host. Mary Stickney TAG-TMI Data Warehouse Programmer 402-474-7612 x 3099 [EMAIL PROTECTED] -Original Message- From: Jennifer Goodie [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 1:57 PM To: Mary Stickney; [EMAIL PROTECTED] Subject: RE: timing They run in 8 minutes on my test server , about 4 minutes each. Printing to a PDF driver. On the Production Server they take HOURS to run and print. The production server is probably trying to do other stuff at the same time, making your reports slower. A machine with one active connection will run queries a lot quicker than an identical machine with 50 connections. Think about what happens when you try to do 50 things at once :) Is the test server using a localhost connect and the production not? Localhost is a lot faster than TCP/IP when running a large amount of queries. -Original Message- From: Mary Stickney [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 6:08 AM To: [EMAIL PROTECTED] Subject: timing I have 2 Crystal Reports 8.5 (for the record I didn't make these reports) that draw straight from the MYSQL database. They use only 1 table. I am calling them to print from VB 6.0 Theses reports have lots of complex calculations in them, counts , sums and groups. My test server has the same code , same database , same indexes , same amount of memory as the Production server, My test server has MYSQL 3.23.51 and MYODBC 3.51 , and I have Crystal installed on my test server. The Production server has MYSQL 3.23 and an older version of MYODBC then I have. No Crystal Reports installed. And the server is a faster machine then mine. They run in 8 minutes on my test server , about 4 minutes each. Printing to a PDF driver. On the Production Server they take HOURS to run and print. Any ideas... Mary Stickney TAG-TMI Data Warehouse Programmer 402-474-7612 x 3099 [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
RE: Joins are slow
Try a straight join with the cases table first. You have to play with your queries to get the best results. Creating a temporary, as you mentioned, table might be a very good option in this case, but you need to make sure that your programming takes into account that your page might be hit more than once at a single time so your queries don't collide, i.e. someone is looking for 1999 cases and someone else is looking for 2000 cases so both are in your temp table and you're joining without a where or something, it seems obvious but I've seen it not accounted for. Another thing that might get you is populating your temp table via select into can cause MySQL to use temp tables on disk instead of memory if the data set is very large and your server is not optimised for it, that will take speed way down. -Original Message- From: Steve Quezadas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:19 PM To: [EMAIL PROTECTED] Subject: Joins are slow Maybe I'm dumb for saying this, but sql joins seems expensive to do in terms of performance (yes, I indexed the joined fields). If I do a query search of a 2,600,000 record defendant table WITHOUT a join (SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%;). Performance is generally zippy at .53 seconds (which ends up pulling about 34,000 rows). HOWEVER, once I join this table with the much smaller [Cases] table (about 140,000 rows), performance plummets to 8.79 seconds! Quite a drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%);'. I get about 10,500 rows returned here, but man, what an increase in time. So my main question is: Is this degradation in performance to be expected with a join? What I am thinking about doing is perhaps doing two smaller queries instead of one larger one. My first query can just query the very large Defendants table without a join and create a temporary table of CaseNumbers (takes about .53 seconds). Then I can join that temporary table into the much smaller [Cases] table and then run that part of the query (which takes about 1.23 seconds). So bottom line: Results of big query with one join: 8.79 seconds Results of creating two smaller queries: 1.76 seconds (.53 seconds + 1.23 seconds). So I am thinking about optimizing the query in my PHP program to make two smaller queries instead of one bigger query. Is this a standard thing to do to increase speed? Or am I doing something stupid and inefficient in the join that I am not aware of? - Steve PS Here is some information about my tables and indexes: ([Defendant] Query WITHOUT a join) - .53 seconds. EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%; ++---+---+---+-+--+---+- + | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+---+-+--+---+- + | Defendants | range | Defendant | Defendant | 30 | NULL | 82756 | where used; Using temporary | ++---+---+---+-+--+---+- + ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+-- -+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+-- -+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL | 82756 | where used | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | Defendants.CaseNumber | 1 | where used | +++--+-+-+-- -+---++ Table and index information Cases Table - 140,000 records +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | CaseNumber| char(30) | | PRI | | | | County| char(30) | | | | | | CaseName | char(120) | | | | | | Court | char(30) | | | | | | Filed |
RE: Joins are damn slow. . .
Your joins are probably not using indexes and the tables could be joining in an inefficient order. Try running an explain on your query to see how it is being done. -Original Message- From: Steve Quezadas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 1:31 AM To: [EMAIL PROTECTED] Subject: Joins are damn slow. . . Maybe I'm a bit naive in saying this but. . . SQL joins are damn slow! - 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: Column names
Are you creating new tables or importing from an old application? If you are building something from scratch it is a really really bad idea to use spaces in the names or start putting in special characters, you're just making more work for yourself, and anyone that works on the application after you is going to want to strangle you :) Using an uppercase letter to signify a new word seems to be a pretty consistently used convention, so employeeInformation or departmentEmployeeXref would be common (I like upper cases on the first letter too, but that seems to not be as common). If you are importing an old application into MySQL and need to use '-', you must escape it with `. -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 6:41 AM To: MySQL Subject: Column names Hi all, In MySQL I know that I need to put the column names between `` in some cases if they contain a % or an _ sign in it. Please tell me what sign can I use to link 2 words in a column without needing to include that column name between ``. Can I use a - (dash) sign, or it might be considered in some cases a minus operator? Thank you for any idea. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [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
RE: Will PhpMyAdmin run on Linux-Apache-MySQL box ?
Does the box have PHP installed and configured to use MySQL? I wouldn't go with the php3 extension, as it is outdated, PHP4 has been stable for a while. -Original Message- From: Will Standley [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 16, 2003 3:14 PM To: [EMAIL PROTECTED] Subject: Will PhpMyAdmin run on Linux-Apache-MySQL box ? I installed PhpMyAdmin on a Windows box using EasyPhp... Will PhpMyAdmin run on a Linux/Apache/MySQL server? (I've got one on my desk up running) If so which version should I download? (.php files) - Download phpMyAdmin-2.3.3pl1-php.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php.tar.gz - Download phpMyAdmin-2.3.3pl1-php.zip (.php3 files) - Download phpMyAdmin-2.3.3pl1-php3.tar.bz2 - Download phpMyAdmin-2.3.3pl1-php3.tar.gz - Download phpMyAdmin-2.3.3pl1-php3.zip (Yes, I'm a Linux newbie.) Finally, is it a fairly easy install for a Linux newbie? thanks for any help. Will - 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: mysqldump file fails to recreate database
My feeling is that MySQL shouldn't allow reserved words / characters like this, whether they are in quotes or not. ... Does anyone know the logic behind allowing reserved words when inside quotes like this? It clearly causes problems - especially for people using GUIs - and these people will be the least able to diagnose the problem. Is that a problem with the GUI application, a problem with a user not knowing enough, or a problem with MySQL? I'd say it is not a problem with MySQL, but with either the user or the GUI. A few years ago when I was first starting out using MySQL I had a table with a bad column name, when we upgraded the name no longer worked in queries because MySQL became a little bit stricter in regards to reserved words and I didn't know to back-tick escape them. That was my fault for picking a bad column name, it came from lack of knowledge. I should have never used the column name and had I known more I wouldn't have. It is not the software's job to teach me, it is my job to learn, and from that experience I learned what reserved words are and that it is not a great idea to use them. I'm sure MySQL allows use of reserved words in order to be more compliant with other RDBMS. MS SQL will allow a column to be named 'field' and be indexed without even emitting an angry beep. Let's say I have an application written using MS SQL and I decide I want to convert to MySQL. If I have to change all of my column and key names I am going to be pretty angry and will be on this list complaining about what a shoddy product MySQL is when compared to MS SQL. That would hardly encourage more people to start using this great application. I don't think I should be limited in what I can do because of the lack of knowledge of other people. MySQL should not be written considering the lowest common denominator. Features, reliability, stability, performance and compatibility are far more important factors in designing a top-notch product if you ask me. There are plenty of places for beginners to gain knowledge about the product: this list; the site; web tutorials; and books, to name a few. There's also experience, which is probably one of the best teachers. I don't think it is wise to pander to an audience that doesn't want to think on their own, then you not only limit progress but also alienate the majority of target users. This of course is just my personal (not-very-well-thought-out) opinion and I'm sure a lot of people disagree. -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 3:11 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mysqldump file fails to recreate database Lynn David Newton wrote: It's the first KEY definition it doesn't like. If I comment out KEY field(field), it works, except of course then the field called 'field' is not set as an index. The same exact problem occurs in two places in the mysqldump file. With them commented out the DB reloads. I had one like this yesterday. I'm pretty sure 'field' is a reserved word. The problem arises because some GUIs back-quote (`) all field / index names, which MySQL then can recognize as names. You can continue to use KEY field(field) if you must by using the -Q option with mysqldump, to quote fieldnames. But I wouldn't. Does anyone know the logic behind allowing reserved words when inside quotes like this? It clearly causes problems - especially for people using GUIs - and these people will be the least able to diagnose the problem. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.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
RE: SHOW STATUS LIKE 'Com_%'
can anyone point me to a reference regarding the Com_ status information What exaclty do you mean? http://www.mysql.com/doc/en/SHOW_STATUS.html states that the com_ is just how many times the command has been issued. So if com_select = 34021 there have been 34021 selects issued to the server. Do you need something more in depth or are you just trying to figure out what they mean in general? - 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: Avg_row_length
I am not a guru, but I figured I'd try to answer anyway. It needs an extra bit because it has NULLs. I think it uses the bit to mark if the field is NULL or not, but I've been known to be wrong. http://www.mysql.com/doc/en/Data_size.html - Dear gurus, here's my final stupid question for today. Why is the average row length 11 when it should be 10? mysql DESCRIBE myrowisam; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | col | char(10) | YES | | NULL| | +---+--+--+-+-+---+ mysql SHOW TABLE STATUS LIKE 'myrow%'; +-+++--++ | Name| Type | Row_format | Rows | Avg_row_length | +-+++--++ | myrowisam | MyISAM | Fixed | 68 | 11 | --- TIA, - 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 have varying number of fields?
Sounds a lot like the grape blends in different wines question posted last week. :) You'll need a cross ref table with a one to many relationship and possibly a field that says what the person did. like | SongID | ComposerID | Role | ++-++ |1 | 1 | Poem | |1 | 2 | Music | |1 | 3 | Poem | |1 | 1 | Lyrics | Just a suggestion, You might want more fields or different names. Search on last week's answers, I believe the thread was called Database Design or something like that. I'm sure there's not a lot of posts that mention grapes and wines on the list. -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 3:10 PM To: mysql users Subject: how to have varying number of fields? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- The Subject: may be a bit misleading, so let me start from scratch. I'm creating a sheet music catalog for our church, and one of the things I'll want to track is who wrote it. [Actually, we have music composers and arrangers and poet authors as well as particular biblical sources, but I think that the first three can be classed together.] Of course, Martin Broones does a lot of the music, and Avelyn Kerr a lot, and I wouldn't want to repeat that, so of course I need a 'composers' table (yeah, it should probably be a more rounded name, but so what :-) The problem comes when I have a piece of music whose words or music are by two people, such as Music by Martin Broones. Poem by John and Jane Doe. -- when I already have entries for John and Jane on their own. Do I just bite the bullet and make another record for John and Jane, filling in their names and dates and styles and other 'composers' fields in duplicate, or is there a way to have my 'pieces' table, listing all of the pieces and their attributes, somehow point to *both* of those composers in the foreign 'composer' field? TIA HAND Good grief; even Dan's qsecretary is better than this mysql query thing! :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+JJi7Gb7uCXufRwARAqkaAKCquKk01B5NeAngFUXir6bIMJAPkgCeI0zk YnJGqiKl6+i36SXa9IwQvCU= =bxf7 -END PGP SIGNATURE- - 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: Bug Report: Restrictions on index naming
MySQLCC probably uses the backtick (`) to escape stuff so it issued UNIQUE KEY `IDX_Postcode-Location` (Postcode,Location) and not UNIQUE KEY IDX_Postcode-Location (Postcode,Location) It has been mentioned on the list a few times in the last couple months that if you escape strings containing hyphens with a backtick they work. That doesn't mean it is a good idea to use them. You can try running your dump with the quote-names flag, maybe. I haven't tried it to see what the output is. Run mysqldump's help to see what all the flags are and what they mean. -Original Message- Hi all, I recently had to restore from a backup and discovered that mysql didn't want to re-create a table which had the minus symbol (-) in it, eg DROP TABLE IF EXISTS Postcodes; CREATE TABLE Postcodes ( DanPK mediumint(8) unsigned NOT NULL auto_increment, MyStamp timestamp(14) NOT NULL, Postcode smallint(2) NOT NULL default '0', Location varchar(100) default NULL, State char(3) default NULL, RegionID mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (DanPK), UNIQUE KEY IDX_Postcode-Location (Postcode,Location) ) TYPE=MyISAM; I had added the index with MySQLCC (I think) and the database had been working fine as far as I could tell (minus the crash this morning). The table def is from mysqldump --opt, which I use each night, in combination with the --log-update option to assist in disaster recovery. When I tried to restore from the backup (mysqldump output) it gave me a syntax error around the -Location bit. But it _did_ let me create the index like this before. Thinking about it more, I probably shouldn't have used a minus. I can see why that would be reserved. Any chance of enforcing that in alter table commands (which I would have used to get the index there), or is it considered too expensive to do these kinds of checks? - 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 new forum
Did you already announce this several days ago? Once last Monday and once in November, but isn't this the messageboard-announce-spam list? :) I'd hate to think a list called mysql is for questions about SQL and mysql and not useless announcements about personal sites. /sarcasm - 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 Database Design
Blend will be a cross reference with a one to many relationship This is very simplified but an example of your data could be: Select * from Wine; ++--+ | WineID | WineName | ++--+ | 1 | XYZ | ++--+ Select * from Grape; +-+---+ | GrapeID | GrapeName | +-+---+ | 1 | GrapeA| | 2 | GrapeB| +-+---+ Select * from Blend; +-++-++ | BlendID | WineID | GrapeID | Percentage | +-++-++ | 1 | 1 | 1 | 80 | | 2 | 1 | 2 | 20 | +-++-++ This is obviuously very simplified, just trying to give you a quick response so you can move ahead in your development without being stuck on this. There might be some disagreement on the naming conventions I have illustrated. Use what you like best. -Original Message- From: Colaluca, Brian [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 12:56 PM To: [EMAIL PROTECTED] Subject: MySQL Database Design For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB. Since my grape variety would presumably be a foreign key in the Wine table, how could I specify a certain *percentage* of a foreign key? I've tried hashing this out in numerous ways, including the addition of a Blend table with multiple primary keys, but anyway I slice it, there will still be an abundance of NULLs. For while the majority of wines may only contain one grape, there could be wines that have up to 5 or 6 in varying percentages. - 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: Load local data infile problem
Imagine an ISP giving every customer write privileges for the mysql/bin directory ... ;-/ Why would they have to do that? The file does not need to be in that directory. In order to use LOAD DATA INFILE without LOCAL the file just needs to be somewhere on the server that mysqld is running on and be readable by the mysqld user. I load my files in from my home directory because I don't think the mysql base dir and data dir are a great spot to arbitrarily put files (and I don't have permission to them w/o su-ing). If you are connecting via localhost, have FILE permission on the DB, and can create a readable file somewhere on that server, you would be fine. We do not allow LOCAL on our servers as we are running replication and 3.23.54 won't support it. I do not have write permission to any directories except my home directory. I have never run into any problems with LOAD DATA that were not my own fault, usually it is error 13 because I typed the path wrong or didn't chmod the file. Obviously this does not negate the fact that LOCAL is sometimes needed, but allowing all users to write to mysql/bin is not needed at all for any reason that I can see. Maybe I am missing something? From the docs -- http://www.mysql.com/doc/en/LOAD_DATA.html If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. (LOCAL is available in MySQL Version 3.22.6 or later.) -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 08, 2003 9:40 AM To: Charles Mabbott; 'Prathmesh J. Mahidharia'; [EMAIL PROTECTED] Subject: Re: Load local data infile problem Charles, I posted the same problem a couple of days ago. LOCAL will not work because of a security improvement the MySQL folks applied. LOAD DATA INFILE C:\\mysql\\fred.txt INTO TABLE data_table; Hope this helps, but only a workaround... Without LOCAL, quite alot of things will not work. Imagine an ISP giving every customer write privileges for the mysql/bin directory ... ;-/ Unfortunately, Monty did'nt mention if this is fixed in 4.0.8 or going to be fixed in 4.0.9 or 4.1. Personally, I regard this security improvement rather a bug than a feature. - 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: converting text to hypertext
HTML doesn't just see a URL and automatically make it a hyperlink it is not
helpful like MS products. So what you are seeing is expected behavior. I
would alter the while loop to look like this...
while ($row = mysql_fetch_array($resultID)){ #fetch_array adds little
overhead and gives you an associative array that is easy to work with
print tr\n\ttd$row[Port]/td\n\ttd$row[Application]/td\n\ttda
href='$row[URL]' target='_blank'$row[URL]/a/td\n/tr\n;
}
I do not know your column names and have assumed they are Port,Application,
and URL. I am probably wrong, put your column names in their place. By
selecting * and just looping through the fields returned you are relying on
them being returned in a certain order, while that will work, it is bad
practice. It also made the code confusing and hard to read and didn't
really save you any typing. This is just a quick solution, I did not check
the html or debug the code or look for security flaws, or put logic in place
for poorly formed URLs, you should be able to handle that.
-Original Message-
From: Rick Tucker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 10:58 AM
To: Larry Brown
Cc: [EMAIL PROTECTED]
Subject: RE: converting text to hypertext
Larry,
This is the code I'm using. I'm pretty new to PHP, so there may be a simple
solution within PHP of which I'm unaware. I just thought it could be done
from the MySQL side of things.
$resultID = mysql_query(SELECT * FROM ports, $linkID);
print tabletrthPort #/th;
print thTransport/thth align=centerApplication/thth
align=centerRFC/Vendor's URL/MS KB article/th;
while ($row = mysql_fetch_row($resultID))
{
print tr;
foreach ($row as $field)
{
print td align=center$field/td;
}
print /tr;
}
print /table;
mysql_close ($linkID);
Thanks,
rick
-Original Message-
From: Larry Brown [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 10:13 AM
To: Rick Tucker
Subject: RE: converting text to hypertext
The question seems to me how are you outputting to html?
Larry S. Brown
Dimension Networks, Inc.
(727) 723-8388
-Original Message-
From: Rick Tucker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 11:34 AM
To: [EMAIL PROTECTED]
Subject: converting text to hypertext
I just imported a .csv file and one of the columns of data was websites
addresses. Those addresses aren't being recognized as links when I output
an html table from my queries. I'm scratching me head on how to make the
conversion. I figured there would by a hypertext datatype of some sort, but
I can't find any information regarding this issue. If someone could point me
in the right direction, I would appreciate it.
Thanks,
rick
-
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: Alter table error
ERROR 7: Error on rename of '.\helpdesk\call_information.MYI' to '.\helpdesk\#sql2-b90-81.MYI' (Errcode: 13) Any ideas on why I would get this?? Errorcode 13 is permission denied or file not found. Either .\helpdesk\#sql2-b90-81.MYI exists and your mysqld user does not have permission to overwrite it, or the user does not have write permissions on the data dir and everything in it. Using perror will help you decode the errorcodes. SQL,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
RE: LEFT JOIN function locking up when using large database
Are you using indexes on your tables? This does not sound like a mysql bug, but rather a problem with your table structure or query. I have seen much more complicated joins work on much larger tables without any problem, as I am sure many people on this list have as well. You should run an explain on your query to make sure it is utilizing indexes. -Original Message- From: Rob Taft [mailto:[EMAIL PROTECTED]] Sent: Monday, January 06, 2003 10:53 AM To: [EMAIL PROTECTED] Subject: LEFT JOIN function locking up when using large database I have 2 tables, one with 1,000 entries, the other with 10,000 entries. I'll call these table1 and table2. The query uses both tables: SELECT something to select FROM table1 LEFT JOIN table2 ON (table1.ID = table2.table1_ID) WHERE some condition; table2.table1_ID is just an int that matches an ID from table1. This way I can get several values from 2 tables with one query. This works great when table 1 has 100 entries and table2 has 1,000 entries. But when i increase both by a factor of 10, the query never returns anything. I let it sit for 10 mins before giving up, and my CPU usage is at 100% the whole time. Any suggestions as to what the problem is? I tried this on both 3.23 and 4.0.7 and got the same results. The [EMAIL PROTECTED] would not take my email. Rob Taft [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
Re: PHP and MySQL bug
It would be helpful if you posted that error. You can get it by changing
the die to
$queryr = mysql_query($query) or die(mysql_error());
Without knowing the error, you problem will be harder for everyone to debug.
-Original Message-
From: Nuno Lopes [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 06, 2003 10:09 AM
To: MySQL List; [EMAIL PROTECTED]
Subject: Re: PHP and MySQL bug
The problem is if I close the connection and reopen it the query is done,
but if I remain with the same connection has the previous query, mysql
returns an error.
- Original Message -
From: Larry Brown [EMAIL PROTECTED]
To: MySQL List [EMAIL PROTECTED]
Sent: Sunday, January 05, 2003 4:16 PM
Subject: Re:PHP and MySQL bug
Try replacing the following line...
@MYSQL_QUERY(UPDATE d SET h='$h' WHERE id='$id'); // this query doesn't
work
With...
$query = UPDATE d SET h='$h' WERE id='$id';
$queryr = mysql_query($query) or die(The sql statement does not
execute);
if(mysql_affected_rows() !== 1)
{
die(The sql statement is successfully run however either h did not
change or there is an internal error. Try executing the sql from the
command line to make sure it otherwise works.);
}
and see which is coming back.
Larry S. Brown
Dimension Networks, Inc.
(727) 723-8388
-
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: indexing a blob
http://www.mysql.com/doc/en/CREATE_INDEX.html you key needs a length, not your field. index (hashsum(length)) # for quick lookups -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Monday, December 30, 2002 1:53 PM To: mysql users Subject: indexing a blob -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- Having learned a bit about indexes, I have tried to practice a bit. So far things work for my char(20) fields, but I have a problem with a tinyblob. To wit: create table ccards ( # ID number id smallint not null default 0 auto_increment primary key , type smallint not null ,# references ccardtypes.id name char(40) not null ,# name as on card number tinytext not null , # card number expdate date not null , # expiration date hashsum tinyblob not null , # hash of the card: have we seen this one? index (hashsum) # for quick lookups ) ; Whenever I try this with the index, I get ERROR 1170 at line 49: BLOB column 'hash' used in key specification without a key length I don't know where the key length needs to be specified; I tried hashsum tinyblob(255) not null - 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: Badly placed ()'s and also access denied
Problem is not in the connecting it is in the mysql_select_db as you are denied access to the database, not the server. Your permissions might be off or the php might not be right. It would be helpful if you posted more of your code. -Original Message- From: Gary Hostetler [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 8:28 AM To: [EMAIL PROTECTED] Subject: Badly placed ()'s and also access denied Being the newbie that I am I get this Badly placed ()'swhen I try to do a mysql_connect(localhost,gary,password); I have a php script that gives me a Access denied for user: 'gary@localhost' to database 'CONTACT'Can't Select CONTACT1046: No Database Selected When the runs. I changed the script to include my username and password. Thanks Gary - 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: SQL Get access denied when trying to create a database
With the same user? It sounds like you might want to read the documentation on GRANT and permissions. It is always a good idea to try the documentation before asking questions on the list. http://www.mysql.com/doc/en/User_Account_Management.html -Original Message- From: Gary Hostetler [mailto:[EMAIL PROTECTED]] Sent: Friday, December 20, 2002 8:59 AM To: [EMAIL PROTECTED] Subject: SQL Get access denied when trying to create a database Now I get an access denied even trying to create a mysql database which is weird because I just created one an hour ago the same way. Thanks Gary - 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: Severe performace problem linking tables with mysql
Have you tried optimizing your query? It is more likely that the problem is a poorly optimized query and/or poorly tuned server than it is mySQL. Joins will be slow if you do not take the time to figure out the best way to do what you are trying to accomplish. Forcing the table order can really speed up joins as can making sure the tables are properly indexed and that the indices are being used by the queries. You also might want to think about tuning your server variables to achieve optimum performance. The easiest place to start is running an explain on your query to see what you can change to make it quicker. -Original Message- From: Joseph Dietz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 10:40 AM To: [EMAIL PROTECTED] Subject: Severe performace problem linking tables with mysql PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type) I have discovered a performace issue when joining several tables together. The performance is extremely poor when performing select queries using the WHERE clause and joining the tables with the pk_media_id = fk_media_id etc... I guess this is what people might think about when considering using mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in performace. 4 of my tables are cross reference tables as such: pk=primary key, fk= foreign key MediasMediaAuthorsAuthors pk_media_id fk_media_id, fk_author_id pk_author_id (Many authors for each media) - 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: remote tcp connection failure
It sounds like you need to edit your hosts.allow file to allow from the IP you want to connect from. add either mysqld : ###.###.###.### : allow or mysqld : all : allow I'm not a sysadmin though, so you might want to do a search on the proper way to do this. -Original Message- From: Kristopher Yates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 9:08 AM To: [EMAIL PROTECTED] Subject: remote tcp connection failure Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell AND ALSO when I try to connect directly to the box from a remote ip via TCP. My username/pass has permissions to allow tcp connect from my remote host IP. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting Results
ORDER BY col [ASC|DESC] http://www.mysql.com/doc/en/SELECT.html -Original Message- From: Mike(mickako)Blezien [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 11:45 AM To: MySQL List Subject: Sorting Results Hello all, Is there away, within the sql query, to sort the query results in alphabetical order, IE... going from A... to ..Z. This would be like titles of mailing lists. TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(985)902-8484 MSN: [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
connections hang on authentication
I have been experiencing a problem where mysql appears to hang when authenticating connections. I am running mysql 3.23.52-log on a dedicated PIII server with 768MB of RAM and FreeBSD 4.1. Nothing else runs on this server and 99.9% of the connections come in remotely. What appears to be happening, is the server gets a little busy due to some slow queries and the queries start to back up. When the server gets done catching up with the queue no new threads can connect, they all hang on authentication. After a few minutes the new attempts stop showing up on the processlist. I know that mysqld is not hanging because I was on the server yesterday while it happened and I could still run queries fine, I just couldn't establish any new connections. MySQL never takes up more than 380MB of RAM on the server, the CPU peaks every now and then, but for the most part stays low. It seems to recover fine when the queue is only around 40 queries, but when the queue gets over 100 we have this problem. Any ideas on what is going on? - 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: Select Where !=
http://www.mysql.com/doc/en/JOIN.html mysql SELECT table1.* FROM table1 -LEFT JOIN table2 ON table1.id=table2.id -WHERE table2.id IS NULL; So... Select n.uid, n.name from names_table n LEFT JOIN exclude ON n.uid = exclude.n_uid WHERE exclude.n_uid IS NULL; -Original Message- From: Michelle de Beer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 11, 2002 11:22 AM To: mysql list Subject: Select Where != I have two tables. One with names and one for excluding certain names. Exclude-table contains the uid for the name excluded. If I want to see which names has been excluded, this query does the job: Select n.uid, n.name from names_tables n, exclude WHERE n.uid = exclude.n_uid But if I want to select all names, but leave out the ones that are in the exclude-table, I thought this would do it, but no. Select n.uid, n.name from names_tables n, exclude WHERE n.uid != exclude.n_uid It has something to do with the != thingy... Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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 - 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
