SQL Question
Hy I want to sum quantites but there is some data that value is negative but users didn't write the - sign before. I can decide which datas are negative. I would like something like that select sum(moved_quantities) from db if moving like 'Move-' then sum seem the move is negative else move value is positive Is it possible? Joe - Yahoo! Cars NEW - sell your car and browse thousands of new and used cars online search now -
howto show query process path? (indexes related)
Hi, for a database project i need to know how indexes impact on query usage. the explain command gives me only infos if index is used or not...and some other infos. i need to know the file access plan. i mean: access the index - find the value - access the file page something like a strace command. Is that possibile? i'm on 5.0.18 Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: SQL Question
Oops, I meant to send this to the list. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Mester József [EMAIL PROTECTED] Sent: Friday, January 06, 2006 8:44 AM Subject: Re: SQL Question - Original Message - From: Mester József [EMAIL PROTECTED] To: Mysql list mysql@lists.mysql.com Sent: Friday, January 06, 2006 3:42 AM Subject: SQL Question Hy I want to sum quantites but there is some data that value is negative but users didn't write the - sign before. I can decide which datas are negative. I would like something like that select sum(moved_quantities) from db if moving like 'Move-' then sum seem the move is negative else move value is positive Is it possible? If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How come this update does not work??
Carlos Vasquez wrote: How come this doesn't work? Wp_photos.photo = IMG_1234.JPG Pixelpost_pixelpost.headline = /this/path/to/directory/IMG_1234.JPG So I need to just match the latter-bit of the file. update pixelpost_pixelpost,wp_posts,wp_photos set pixelpost_pixelpost.headline=wp_posts.post_title,pixelpost_pixelpost.datetime=wp_posts.post_date_gmt,pixelpost_pixelpost.body=wp_photos.caption where wp_photos.photo like '%pixelpost_pixelpost.headline%' and wp_photos.post_ID=wp_posts.ID; I get zero rows returned, 0 updated, etc. Any ideas would be appriciated? thanks in advanced I'd say the where clause isn't being matched. Try a select with the same where clause and see what you get back -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine?
On Wednesday, January 4, 2006 1313, Chander Ganesan [EMAIL PROTECTED] wrote: John Hoover wrote: I need some advice re my choice of a storage engine for transaction-safe processing and including tables that are not transaction-safe within transactions. The problem: We need to insert related records into several different tables and be sure that all the insertions succeeded. It seems that transactions are the recommended way of achieving this and I was thinking of using InnoDB tables. However, I'm not sure if that is the best engine to use - can anyone give me reasons for selecting a specific transaction-safe engine? You can use BDB or InnoDB - InnoDB provides row level locking, BDB provides page level locking. I'd say InnoDB is the way to go (usually). I was planning to use InnoDB, but wanted to find out if anybody had good reasons to use BDB instead. Guess I'll stick to my original plan. Also, some of my insertions will involve the mySQL tables (creation of a new user and granting privileges, for example). According to the manuals, those tables use the myISAM engine and can not be changed to any other engine. What is the best way to handle insertion errors on myISAM tables? I had planned to test for an error after each operation and, if one occurred, manually undo whatever previous operations had already succeeded. That's a lot of work if the operation involves multiple tables and I'd like to know if there is a better alternative. Finally, if I do handle errors manually, what should I do if there is an error in the error handler? For example, suppose I've inserted one record and then an error prevents insertion of the related record so that I have to delete the previously inserted record. Is it possible for the delete to fail? If so, I'll have a partial transaction that can't be completed and can't be undone - what should I do to clean up? What are your insertion operations? Typically, you would use GRANT statements to add users the these tables - and those statements (if they fail) won't do any GRANTing. I wouldn't grant access using insert statements - you'll be flushing your privilege tables regularly. The most common operation will probably be adding new users. I expect that changing privileges for existing users will be done very infrequently. Sounds like GRANT will handle both requirements nicely. Unless you are using the Host table, I'd recommend you do the following: 1. Prior to modifying a user, use the 'show grants' statement to find out what access the user has - store that. 2. Perform all your GRANT operations. 3. If a single operation fails, remove the user and execute the stored access (from step 1) for the user to restore his/her access. - if the user didn't already exist, just remove all their access. For the most common case, there will be no previous user so, if anything fails, I can just delete the user and I'll be back to the starting point. However, for the case where I'm modifying an existing user, I'll use your suggestion for getting and storing the original privileges. For users that won't connect to the database directly, you probably don't want to create individual accounts - as if the user connects directly they can perform operations outside the bounds of your application (where you may implement business logic). You're the second person to mention users who won't connect directly to the database and I'm not sure that I understand what you mean. I THINK you mean connect via the command line tools and bypass my application. I suppose that I could have hidden accounts (and passwords) that my application uses behind the scenes. In this scenario, my users would login with their name and password, but the connection to the DB would use some secret account name and password. After the connection was made, the application would query some table (private to my application) to find out the actual privileges for that person. The application would then enforce those privileges. Nobody could bypass the application because they wouldn't know the hidden usernames or passwords. My concern with this scheme is that the secret usernames and passwords would either be set in a preferences file, in which case they wouldn't really be secret, or they'd be coded in the application, in which case I couldn't change them (we're supposed to change our passwords at least once a year - more often for some systems). Assuming you stick with grant statements, it shouldn't be too difficult to maintain integrity when you want to do your pseudo-transactions. Use InnoDB everywhere else. I think that's what I'll do. Thanks for the suggestions. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
mysql 5.0 upgrade from 4.1.14/innodb/signal 11
Hi All, A little background here... I have recently moved over to V 5.0.18-standard from 4.1.14. Pardon my ignorance, but after 2 very late nights, I am about getting to my wits end :) This move wasn't entirely planned. A coworker started a alter table... command via mysql control center to change a table from myISAM to INNODB. 7 hours later, I killed off the process and tried to avoid a 'roll back' by deleting the #sqlibd file. Several hours later... I was able to finally bring up the server using innodb_force_recovery = 3, exported everything using mysql_dump, then brought 5.0.18 online and imported it in. I had one table with 7 million records (in a mysql_dump file) that took about 15 hours to import. I think the reason had to do with the record size and my buffer settings. Anyway ... I have my my.cnf tweaked based on the my-innodb-heavy-4G.cnf file. things seem to be working better :) This is a 2 processor machine (P4 3.0 GHZ), 4 GB Ram. My innodb specific settings are : innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_log_arch_dir = /usr/local/mysql/data innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 256M innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 150 innodb_thread_concurrency = 8 innodb_file_per_table On a show table status for this table in question, I get Data_free=0, which concerns me - but I think may explain the 15 hour import (if mysql had to continously allocate more disk space for this table). Is there a way to preallocate space for a innodb table (using innodb_file_per_table)? -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 2178, signal count 2175 Mutex spin waits 3141, rounds 9508, OS waits 281 RW-shared spins 3407, OS waits 1700; RW-excl spins 308, OS waits 184 Are these numbers good or bad? Record lock, heap no 170 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd482e; asc C H.;; 1: len 30; hex 38343639663163323764336531316461623263646439326565316230; asc 8469f1c27d3e11dab2cdd9ff2ee1b0;...(truncated); 2: len 4; hex 02786cb7; asc xl ;; Record lock, heap no 171 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4838; asc C H8;; 1: len 30; hex 393138666333633037643365313164613837663265653063623736623262; asc 918fc3c07d3e11da87f2ee0cb76b2b;...(truncated); 2: len 4; hex 0278aa6c; asc x l;; Record lock, heap no 172 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4852; asc C HR;; 1: len 30; hex 333439383934393137656431313164616162326430303033626165363063; asc 349894917ed111daab2d0003bae60c;...(truncated); 2: len 4; hex 0278def2; asc x ;; Record lock, heap no 177 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4826; asc C H;; 1: len 30; hex 666130613938653737643139313164616161626166326232666362663665; asc fa0a98e77d1911daaabaf2b2fcbf6e;...(truncated); 2: len 4; hex 027860bf; asc x` ;; Record lock, heap no 178 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd481b; asc C H ;; 1: len 30; hex 383030653537353437643365313164616239643065316530366133383835; asc 800e57547d3e11dab9d0e1e06a3885;...(truncated); 2: len 4; hex 027858dc; asc xX ;; Record lock, heap no 179 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4848; asc C HH;; 1: len 30; hex 306534366136616537643161313164616162353166326232666362663665; asc 0e46a6ae7d1a11daab51f2b2fcbf6e;...(truncated); 2: len 4; hex 02782af8; asc x* ;; Record lock, heap no 180 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 43bd4851; asc C HQ;; 1: len 30; hex 366337623533343937643366313164616231303264666539626137616233; asc 6c7b53497d3f11dab102dfe9ba7ab3;...(truncated); 2: len 4; hex 027845de; asc xE ;; Also - is something difference with grants? I get a signal 11 and mysql restarts when I try to give a grant... statement 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=536870912 read_buffer_size=2093056 max_used_connections=2 max_connections=200 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2571486 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x9b08ac0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went
5.1 Delopment source
Hi - I am trying to download the 5.1 development sources as per the documentation; when I try this: export PATH=/home/beau/src/bitkeeper/bk_client-1.1:$PATH sfioball -r+ bk://mysql.bkbits.net/mysql-5.1 mysql-5.1 I get this: ERROR-cannot cd to mysql-5.1 (illegal, nonexistant, or not package root) I think the free bk_client is setup as per the documentation; maybe I just don't understand the mysql source tree structure. Does anyone see what I'm doing wrong? -- Aloha = Beau; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0 upgrade from 4.1.14/innodb/signal 11 -- PT2
Hi All, forgot something in my other post: machine is running suse 9.3, 2.6.11.4-20a-smp kernel. Ok, I think I know the answer here... but just to make sure :) 4.1.14 ran with about 10 mysqld process. skip-innodb was initially turned on in the my.cnf before the attempted migration to innodb. With innodb enabled on 5.0.18, I am only seeing a single process in a normal ps. threads right? ps -elfm shows a little more: 4 - root 26602 1 0 - - - 668 - 09:40 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/dat 4 S root - - 0 79 0 - - wait 09:40 - 00:00:00 - 4 - root 26822 26386 0 - - - 491 - 09:58 pts/1 00:00:00 tail -f /usr/local/mysql/data/db.err 4 S root - - 0 76 0 - - - 09:58 - 00:00:00 - 0 - glaw 26833 26326 0 - - - 1046 - 09:59 pts/3 00:00:00 -bash 0 S glaw - - 0 75 0 - - wait 09:59 - 00:00:00 - 0 - glaw 26854 26326 0 - - - 1045 - 09:59 pts/4 00:00:00 -bash 0 S glaw - - 0 75 0 - - wait 09:59 - 00:00:00 - 4 - root 26873 26854 0 - - - 1004 - 09:59 pts/4 00:00:00 su - 4 S root - - 0 79 0 - - wait 09:59 - 00:00:00 - 0 - root 26877 26873 0 - - - 755 - 09:59 pts/4 00:00:00 -bash 0 S root - - 0 75 0 - - - 09:59 - 00:00:00 - 4 - mysql27009 26602 9 - - - 512778 - 10:05 pts/1 00:05:15 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pi 4 S mysql- - 0 76 0 - - - 10:05 - 00:00:02 - 1 S mysql- - 0 76 0 - - 322560 10:05 - 00:00:00 - 1 D mysql- - 0 75 0 - - sync_p 10:05 - 00:00:00 - 1 S mysql- - 0 76 0 - - 322560 10:05 - 00:00:03 - 1 S mysql- - 0 75 0 - - 322560 10:05 - 00:00:30 - 1 S mysql- - 0 76 0 - - - 10:05 - 00:00:03 - 1 S mysql- - 0 76 0 - - - 10:05 - 00:00:03 - 1 S mysql- - 1 76 0 - - 322559 10:05 - 00:00:49 - 1 S mysql- - 0 76 0 - - - 10:05 - 00:00:00 - 1 S mysql- - 1 77 0 - - 363528 10:05 - 00:00:59 - 1 S mysql- - 0 78 0 - - 393791 10:12 - 00:00:11 - 1 D mysql- - 3 77 0 - - sync_p 10:14 - 00:01:46 - 1 S mysql- - 0 75 0 - - 1460 10:17 - 00:00:16 - 1 S mysql- - 1 76 0 - - 322560 10:33 - 00:00:28 - George Law VoIP Network Developer 864-678-3161 [EMAIL PROTECTED] MSN: [EMAIL PROTECTED]
Re: 5.1 Delopment source
On Fri, Jan 06, 2006 at 06:02:05AM -1000, Beau E. Cox wrote: Hi - I am trying to download the 5.1 development sources as per the documentation; when I try this: export PATH=/home/beau/src/bitkeeper/bk_client-1.1:$PATH sfioball -r+ bk://mysql.bkbits.net/mysql-5.1 mysql-5.1 The name of the repository was changed to mysql-5.1-new, and it looks like the documentation may not have been updated. try: sfioball -r+ bk://mysql.bkbits.net/mysql-5.1-new mysql-5.1 Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1 Delopment source
Beau E. Cox wrote: Hi - I am trying to download the 5.1 development sources as per the documentation; when I try this: export PATH=/home/beau/src/bitkeeper/bk_client-1.1:$PATH sfioball -r+ bk://mysql.bkbits.net/mysql-5.1 mysql-5.1 I get this: ERROR-cannot cd to mysql-5.1 (illegal, nonexistant, or not package root) I think the free bk_client is setup as per the documentation; maybe I just don't understand the mysql source tree structure. There is no free bitkeeper client. Free Bitkeeper was shutdown months ago. Does anyone see what I'm doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication A-B-C - changes on B are not replicated to C
Hello. My conclusion so far: The data is in the relay-log on C but it's not put into the database for some reason. I simply have no clue what reason it could be. In fact i'm a little bit confused right now so any help is very welcome. In you original message you told you were working with MySQL 4.0. It is an old release. Check if the problem exists on the latest release. Frank Fischer [EMAIL PROTECTED] wrote: Hi all I followed Gleb's advice (see below), removed all filters, restarted the db servers - same behaviour. Still replications work if there are changes made on A. Then these changes are replicated A-B-C. When making changes on B, i can see them in the bin-log on B and the relay-log of C but they are not written to the db on C. Since there are no filters on C (anymore) and the replication with changes on A works, i just don't understand where the problem could be. There also are no error messages in the err log. Everything seems else to work fine. Is there any way to get a more detailed logging of what is happening during the replication (kind of debug information)? To make sure that the replicated SQL queries are ok i put them out of the relay bin of C and issued them to the db on C using MySQLQueryBrowser. That worked fine. Another funny thing is, when i issue LOAD DATA FROM MASTER on C it loads all data from master B inclusive all changes that have been made on B which were not replicated before. My conclusion so far: The data is in the relay-log on C but it's not put into the database for some reason. I simply have no clue what reason it could be. In fact i'm a little bit confused right now so any help is very welcome. Greetings Frank -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question
Hello. Do you want something similare to this: SELECT SUM(IF(moving like 'Move-',-moved_quantities,moved_quantites)) FROM DB; Have a look here: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Mester József wrote: Hy I want to sum quantites but there is some data that value is negative but users didn't write the - sign before. I can decide which datas are negative. I would like something like that select sum(moved_quantities) from db if moving like 'Move-' then sum seem the move is negative else move value is positive Is it possible? Joe - Yahoo! Cars NEW - sell your car and browse thousands of new and used cars online search now - -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with datetime value
Hello. This is a bug: http://bugs.mysql.com/bug.php?id=16249 Leo wrote: Hi All, I got a problem with a query that involved datetime field. the table structure goes something like this : CREATE TABLE `price_log` ( `Item` char(20) NOT NULL default '', `Started` datetime NOT NULL default '-00-00 00:00:00', `Price` decimal(16,3) NOT NULL default '0.000', PRIMARY KEY (`Item`,`Started`) ) ENGINE=MyISAM; insert into price_log values (A1,2005-11-01 08:00:00,1000), (A1,2005-11-15 00:00:00,2000), (A1,2005-12-12 08:00:00,3000), (A2,2005-12-01 08:00:00,1000); when i execute this query : select * from price_log where item like A% and started=2005-12-01 24:00:00; it will return this result set : ItemStarted Price -- --- A1 2005-11-01 08:00:00 1000.000 A1 2005-11-15 00:00:00 2000.000 A2 2005-12-01 08:00:00 1000.000 but, when i limited to certain item, like this : select * from price_log where item=A1 and started=2005-12-01 24:00:00; it return an empty set. What is going wrong? I know it should return 2 rows, as the previous sql result set got two rows of item 'A1'. is it because the value of 2005-12-01 24:00:00 which ofcourse is not a valid date time value. but anyhow, the first query was succeded ?? im using mysql 4.1.11, 4.1.15, 4.0.1 .. and the result were all the same. Thanks for any comment. - Leo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT query
Hello.
Usually working with IP addresses in a numeric form is faster. Use
INET_NTOA() and INET_ATON() functions to store IP addresses as unsigned
ints. To work with subnetworks instead of like 'xxx.xxx.%' use
ip_address_in_numeric_form between inet_aton('xxx.xxx.0.0') and
inet_aton('xxx.xxx.255.255') or similar condition (check if between
covers the borders of the subnet). %php% are usually slow. Force your
application which inserts data to the database explicitly determine
the type of the content (perhaps, you should add a field which will
indicate the content. I agree, that this is a superfluity, however
the speed of the query is more important).
Jay Paulson (CE CEN) [EMAIL PROTECTED] wrote:
Below is a query I'm trying to create and the sql for the table I'm pulling the
information out of. The query is definitely not the best query out there
especially
since I'm still pretty new with sql. I know there has to be a better way of
getting the
information I want. A little background. I'm parsing an Apache access_log
file and
throwing it all into a DB so I can run some reports on it. What I'm wanting
to get are
certain file types that were downloaded (in this case all .html, .php, .pdf,
.doc, and
.flv files) in a certain date range but grouped by certain ip addresses. Some
groups of
ips might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%).
What needs
to be returned is the count of all the file types that have been downloaded
but grouped
by certain ips that I'm looking for.
I hope I didn't confuse anything because I think I confused myself! ;)
Thanks for any help!
CREATE TABLE `apache_statslog` (
`STATS_ID` int(11) NOT NULL auto_increment,
`ip` varchar(25) default NULL,
`accesstime` datetime default NULL,
`thepage` varchar(250) default NULL,
`thetype` varchar(25) default NULL,
`thecode` char(3) default NULL,
`thebytes` int(11) default NULL,
`theref` varchar(250) default NULL,
`browser` varchar(250) default NULL,
PRIMARY KEY (`STATS_ID`),
KEY `ip` (`ip`),
KEY `accesstime` (`accesstime`),
KEY `thepage` (`thepage`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ;
SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits,
count(fl.ip) as
fl_page_hits, count(so.ip) as so_page_hits
FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl,
apache_statslog as so
WHERE (swrm.accesstime = '2006-01-01 00:00:00' AND swrm.accesstime =
'2006-01-04
23:59:59')
AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip
LIKE 'xxx.xxx.%')
#this is a group that needs to return a count
AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR
swrm.thepage LIKE
'%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE '%.flv%')
ORDER BY swrm.accesstime ASC
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers on 5.0.17 -- definer not fully qualified
Gleb Paharenko wrote: Hello. I've subsequently upgraded the instance to 5.0.18, Have you updated master to 5.0.18 as well? - unfortunately, the set up demands that the master stays at 4.0. I can't change that. The triggers were working in 5.0.16. It's the new DEFINER that was added with 5.0.17 that seems to be causing the problem. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine?
John Hoover [EMAIL PROTECTED] wrote on 01/06/2006 10:21:40 AM: snipped For users that won't connect to the database directly, you probably don't want to create individual accounts - as if the user connects directly they can perform operations outside the bounds of your application (where you may implement business logic). You're the second person to mention users who won't connect directly to the database and I'm not sure that I understand what you mean. I THINK you mean connect via the command line tools and bypass my application. I suppose that I could have hidden accounts (and passwords) that my application uses behind the scenes. In this scenario, my users would login with their name and password, but the connection to the DB would use some secret account name and password. After the connection was made, the application would query some table (private to my application) to find out the actual privileges for that person. The application would then enforce those privileges. Nobody could bypass the application because they wouldn't know the hidden usernames or passwords. My concern with this scheme is that the secret usernames and passwords would either be set in a preferences file, in which case they wouldn't really be secret, or they'd be coded in the application, in which case I couldn't change them (we're supposed to change our passwords at least once a year - more often for some systems). snipped I agree with Chander and still recommend application-level database accounts, not one or more direct MySQL logins per user. That's how most databases storing data with complex business rules maintain their data integrity. The SQL data definition language is usually not complex enough to enforce certain common business relationships so we rely on the application the users interact with to do it for us. That means that most users DO NOT have their own private accounts with MySQL. The permissions are enforced programmatically by the application (usually with the help of one or more private tables, not actually part of the business data you are trying to protect). Certain users either are or feel that they are 'privileged' and need direct access to the underlying data. In those cases, I will create personal accounts for them in the database but I will only grant them read-only access. That way if they wanted to connect using their favorite data tool (mysql client, charting, reporting, whatever) they still could but they won't be able to break anything. They can 'look' but they cannot 'touch'. Most applications require periodic updates anyway (new rules, new screens, requested changes, etc). It should be possible to enforce consistent upgrades by changing the application passwords during each update cycle. That way, you can make sure that v1.1.13 no longer connects while v1.1.15 or better still can. Depending on the complexity of your business rules and the ever-changing nature of business, having version specific application accounts may be the easiest option to ensure that the rules-du-jour are being properly followed. There are various ways to obfuscate the passwords you use in various versions of each application. Do not leave your passwords in plain-text in any application leaving your direct supervision (as in a desktop rollout). Also, if the application and the database are using a networked connection on an open network, you should probably encrypt the link (SSL is built into the MySQL protocols). Even with that said I know that No program is un-crackable. All you need to achieve is reasonable security for the application and the sensitivity of your data. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 5.1 Delopment source
On Friday 06 January 2006 06:15 am, Jim Winstead wrote: On Fri, Jan 06, 2006 at 06:02:05AM -1000, Beau E. Cox wrote: Hi - I am trying to download the 5.1 development sources as per the documentation; when I try this: export PATH=/home/beau/src/bitkeeper/bk_client-1.1:$PATH sfioball -r+ bk://mysql.bkbits.net/mysql-5.1 mysql-5.1 The name of the repository was changed to mysql-5.1-new, and it looks like the documentation may not have been updated. try: sfioball -r+ bk://mysql.bkbits.net/mysql-5.1-new mysql-5.1 Jim Winstead MySQL Inc. Perfect! Thanks Jim. -- Aloha = Beau; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1 Delopment source
On Friday 06 January 2006 06:16 am, gerald_clark wrote: Beau E. Cox wrote: Hi - I am trying to download the 5.1 development sources as per the documentation; when I try this: export PATH=/home/beau/src/bitkeeper/bk_client-1.1:$PATH sfioball -r+ bk://mysql.bkbits.net/mysql-5.1 mysql-5.1 I get this: ERROR-cannot cd to mysql-5.1 (illegal, nonexistant, or not package root) I think the free bk_client is setup as per the documentation; maybe I just don't understand the mysql source tree structure. There is no free bitkeeper client. Free Bitkeeper was shutdown months ago. Maybe I'm not using the correct name; this link: Download the BitKeeper free client from http://www.bitmover.com/bk-client.shar. as per the documentation is still active, and in the bitmover.com web site it is available as 'unsupported'. And it works. Does anyone see what I'm doing wrong? -- Aloha = Beau; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers on 5.0.17 -- definer not fully qualified
Hello. I've subsequently upgraded the instance to 5.0.18, Have you updated master to 5.0.18 as well? Ian Sales (DBA) wrote: After upgrading to 5.0.17, the triggers on one of my instances now break replication with a definer is not fully qualified error. I set the DEFINER in the CREATE TRIGGER statement to CURRENT_USER (i.e., [EMAIL PROTECTED]), but the error still occurs. If I try setting the DEFINER to any other user, then I get user does not have access errors, irrespective of whether that user does or does not have the necessary privileges. I've subsequently upgraded the instance to 5.0.18, but the error still occurs. I've also tried experimenting with setting the DEFINER as a specific user and then assigning INSERT, UPDATE, DELETE, SELECT and SUPER privileges to that user. To no avail. Anyone have any ideas how to resolve this? Thanks - ian -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a SELECT query
This helps a ton! Thanks! I didn' tknow about the INET_NTOA() or the
INET_ATON() functions. That is much quicker to query on them than on a char
set of ips. However, I did notice on the mysql web site that these functions
are only available in 5.x but the way the page is set up I'm a little confused
if this is true or not. I'm running 4.1.x and it would be nice to have those
functions.
The page below is where I found information about the function.
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html
Thanks!
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Fri 1/6/2006 5:28 AM
To: mysql@lists.mysql.com
Subject: Re: Help with a SELECT query
Hello.
Usually working with IP addresses in a numeric form is faster. Use
INET_NTOA() and INET_ATON() functions to store IP addresses as unsigned
ints. To work with subnetworks instead of like 'xxx.xxx.%' use
ip_address_in_numeric_form between inet_aton('xxx.xxx.0.0') and
inet_aton('xxx.xxx.255.255') or similar condition (check if between
covers the borders of the subnet). %php% are usually slow. Force your
application which inserts data to the database explicitly determine
the type of the content (perhaps, you should add a field which will
indicate the content. I agree, that this is a superfluity, however
the speed of the query is more important).
Jay Paulson (CE CEN) [EMAIL PROTECTED] wrote:
Below is a query I'm trying to create and the sql for the table I'm pulling the
information out of. The query is definitely not the best query out there
especially
since I'm still pretty new with sql. I know there has to be a better way of
getting the
information I want. A little background. I'm parsing an Apache access_log
file and
throwing it all into a DB so I can run some reports on it. What I'm wanting
to get are
certain file types that were downloaded (in this case all .html, .php, .pdf,
.doc, and
.flv files) in a certain date range but grouped by certain ip addresses. Some
groups of
ips might have 2 or 3 subnets it needs to get (ie xxx.xxx.% and yyy.yyy.%).
What needs
to be returned is the count of all the file types that have been downloaded
but grouped
by certain ips that I'm looking for.
I hope I didn't confuse anything because I think I confused myself! ;)
Thanks for any help!
CREATE TABLE `apache_statslog` (
`STATS_ID` int(11) NOT NULL auto_increment,
`ip` varchar(25) default NULL,
`accesstime` datetime default NULL,
`thepage` varchar(250) default NULL,
`thetype` varchar(25) default NULL,
`thecode` char(3) default NULL,
`thebytes` int(11) default NULL,
`theref` varchar(250) default NULL,
`browser` varchar(250) default NULL,
PRIMARY KEY (`STATS_ID`),
KEY `ip` (`ip`),
KEY `accesstime` (`accesstime`),
KEY `thepage` (`thepage`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59145 ;
SELECT count(swrm.ip) as swrm_page_hits, count(mw.ip) as mw_page_hits,
count(fl.ip) as
fl_page_hits, count(so.ip) as so_page_hits
FROM apache_statslog as swrm, apache_statslog as mw, apache_statslog as fl,
apache_statslog as so
WHERE (swrm.accesstime = '2006-01-01 00:00:00' AND swrm.accesstime =
'2006-01-04
23:59:59')
AND (swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip LIKE 'xxx.xxx.%' OR swrm.ip
LIKE 'xxx.xxx.%')
#this is a group that needs to return a count
AND (mw.ip LIKE 'xxx.xxx.%' OR mw.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (fl.ip LIKE 'xxx.xxx.%' OR fl.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (so.ip LIKE 'xxx.xxx.%' OR so.ip LIKE 'xxx.xxx.%') #this is a
group that needs to
return a count
AND (swrm.thepage LIKE '%.html%' OR swrm.thepage LIKE '%.php%' OR
swrm.thepage LIKE
'%.doc%' OR swrm.thepage LIKE '%.pdf%' OR swrm.thepage LIKE '%.flv%')
ORDER BY swrm.accesstime ASC
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question
Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT query
Jay Paulson (CE CEN) wrote: This helps a ton! Thanks! I didn' tknow about the INET_NTOA() or the INET_ATON() functions. That is much quicker to query on them than on a char set of ips. However, I did notice on the mysql web site that these functions are only available in 5.x but the way the page is set up I'm a little confused if this is true or not. I'm running 4.1.x and it would be nice to have those functions. The page below is where I found information about the function. http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html Thanks! Change the 5.0 to 4.1 in the URL to see the relevant page: http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html You will discoverr that INET_NTOA() and INET_ATON() were added to mysql way back in version 3.23.15. Unfortunately, the 5.0 version of the manual has no mention of when things were added to mysql in previous versions. You have to look at the 4.1 version of the manual for that. I suppose the reasoning is that the 5.0 manual is for only the one version of mysql, while the 4.1 manual is for versions 3.23, 4.0, and 4.1 combined, but I find it a real pain, and it seems to confuse people into thinking old things first arrived in 5.0. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine?
On Friday, January 6, 2006 1129, [EMAIL PROTECTED] wrote: I agree with Chander and still recommend application-level database accounts, not one or more direct MySQL logins per user. That's how most databases storing data with complex business rules maintain their data integrity. The SQL data definition language is usually not complex enough to enforce certain common business relationships so we rely on the application the users interact with to do it for us. That means that most users DO NOT have their own private accounts with MySQL. The permissions are enforced programmatically by the application (usually with the help of one or more private tables, not actually part of the business data you are trying to protect). I had planned to enforce business rules in my application, but give each person their own account so that MySQL could enforce a second layer of security, but I had not been thinking of people trying to subvert the system by bypassing the application. I think I'll reconsider my design. Also, now that version 5 is out, I'll have to think about moving at least some of the rules into triggers so they can't be bypassed. Most applications require periodic updates anyway (new rules, new screens, requested changes, etc). It should be possible to enforce consistent upgrades by changing the application passwords during each update cycle. That way, you can make sure that v1.1.13 no longer connects while v1.1.15 or better still can. Depending on the complexity of your business rules and the ever-changing nature of business, having version specific application accounts may be the easiest option to ensure that the rules-du-jour are being properly followed. Good point. There are various ways to obfuscate the passwords you use in various versions of each application. Do not leave your passwords in plain-text in any application leaving your direct supervision (as in a desktop rollout). Also, if the application and the database are using a networked connection on an open network, you should probably encrypt the link (SSL is built into the MySQL protocols). Even with that said I know that No program is un-crackable. All you need to achieve is reasonable security for the application and the sensitivity of your data. I'm going to have to use SSL (or something similar) because plaintext passwords are not allowed on our network, but I haven't gotten that far in my planning. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thanks for the input. -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT query
Hello.
These functions are available in 4.1.16 as well:
mysql select inet_aton('192.168.0.1');
+--+
| inet_aton('192.168.0.1') |
+--+
| 3232235521 |
+--+
1 row in set (0.06 sec)
mysql select version();
+--+
| version()|
+--+
| 4.1.16-debug-log |
+--+
This helps a ton! Thanks! I didn' tknow about the INET_NTOA() or the
INET_ATON() functions. That is much quicker to query on them than on a
char set of ips. However, I did notice on the mysql web site that
these functions are only available in 5.x but the way the page is set
up I'm a little confused if this is true or not. I'm running 4.1.x and
it would be nice to have those functions.
Jay Paulson (CE CEN) wrote:
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question
- Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT query
Michael, thanks for your feedback! Jay Paulson (CE CEN) wrote: This helps a ton! Thanks! I didn' tknow about the INET_NTOA() or the INET_ATON() functions. That is much quicker to query on them than on a char set of ips. However, I did notice on the mysql web site that these functions are only available in 5.x but the way the page is set up I'm a little confused if this is true or not. I'm running 4.1.x and it would be nice to have those functions. The page below is where I found information about the function. http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html Thanks! Change the 5.0 to 4.1 in the URL to see the relevant page: http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html You will discoverr that INET_NTOA() and INET_ATON() were added to mysql way back in version 3.23.15. Unfortunately, the 5.0 version of the manual has no mention of when things were added to mysql in previous versions. You have to look at the 4.1 version of the manual for that. I suppose the reasoning is that the 5.0 manual is for only the one version of mysql, while the 4.1 manual is for versions 3.23, 4.0, and 4.1 combined, but I find it a real pain, and it seems to confuse people into thinking old things first arrived in 5.0. I understand part of your complaint: Several people have criticised us for having removed version-specific information from the (now split) manuals. On the other hand, we haven't removed any information about when features were added, behaviour was changed, and so on, for the major version covered in the manual you're currently looking at. For example, if some function was introduced in 3.23, you'll find the exact (minor) version when it was introduced in the 3.23/4.0/4.1 manual, but you won't find that version information in the 5.0 or the 5.1 manual.[1] I'd like to have split the 3.23/4.0/4.1 combined manual, too; however, we didn't have enough resources to do that, so that might be a cause of confusion for people using those MySQL versions. Sorry. [1] The reasoning behind this: Is it relevant for a 5.0 user to know that INET_ATON() was introduced in 3.23.15? We don't think it is, and many users had complained that the manual was full of clutter like that. Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead Skype: stefanhinz SIP: 4429 Desk: +49308270294-0 Fax: -1 TZ: Berlin Mobile: +491777841069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a SELECT query
My quick opinion about this comment below. Unfortunately I'm still using MySQL 3.23.x in production (an on going battle to get them to upgrade is still in progress). However, our development server is using MySQL 4.1.x (yet another on going battle to get them to install 3.23.x). Therefore, having the information with the version number of when a function was introduced would be GREAT! I'm sure I'm not the only one with this confusion. :) If there ever becomes a time where you guys have enough resources to add that into the manual that would GREAT!!! Thanks! -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED] Sent: Fri 1/6/2006 12:40 PM To: Michael Stassen Cc: Jay Paulson (CE CEN); Gleb Paharenko; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Help with a SELECT query [1] The reasoning behind this: Is it relevant for a 5.0 user to know that INET_ATON() was introduced in 3.23.15? We don't think it is, and many users had complained that the manual was full of clutter like that. Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead Skype: stefanhinz SIP: 4429 Desk: +49308270294-0 Fax: -1 TZ: Berlin Mobile: +491777841069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a SELECT query
Jay, My quick opinion about this comment below. Unfortunately I'm still using MySQL 3.23.x in production (an on going battle to get them to upgrade is still in progress). However, our development server is using MySQL 4.1.x (yet another on going battle to get them to install 3.23.x). Therefore, having the information with the version number of when a function was introduced would be GREAT! I'm sure I'm not the only one with this confusion. :) If there ever becomes a time where you guys have enough resources to add that into the manual that would GREAT!!! We certainly want to do anything to make the manual more helpful! However, I think I don't understand your request: If you're using both 3.23 and 4.1, you only have to use *one* manual because 3.23, 4.0, and 4.1 are all covered in the same manual. What am I getting wrong? If you'd be using, say, 4.1 and 5.0, and you'd complain that you'd have to look into two manuals to get the whole story for your particular setup, I'd understand that. /Stefan -Original Message- From: Stefan Hinz [mailto:[EMAIL PROTECTED] Sent: Fri 1/6/2006 12:40 PM To: Michael Stassen Cc: Jay Paulson (CE CEN); Gleb Paharenko; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Help with a SELECT query [1] The reasoning behind this: Is it relevant for a 5.0 user to know that INET_ATON() was introduced in 3.23.15? We don't think it is, and many users had complained that the manual was full of clutter like that. Regards, Stefan Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead Skype: stefanhinz SIP: 4429 Desk: +49308270294-0 Fax: -1 TZ: Berlin Mobile: +491777841069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help counting player with lowest score for each week.
How do I simply find out who the bubble is now in my db now that we track more than 1 $0 person. I need just the person
with $0 in the amount column with the lowest Place for each date.
I can iterate through it all if needed in PHP, but I am always looking to learn
more SQL tricks.
I am close, as this gives me 1 person, and it says what that amount is, but then it gives me the person with the highest
PLACE, instead of what is shown in min(place). (
SELECT playerid, date, place, min(place)
FROM results WHERE amount=0 AND year(date)=2006
GROUP BY date ORDER BY DATE, place DESC
Full Query
SELECT firstname, lastname, A.playerid, count(A.playerid) AS Bubbles,
A.place
FROM
(SELECT playerid, date, place, min(place)
FROM results WHERE amount=0 AND year(date)=2006
GROUP BY date ORDER BY DATE, place DESC
) AS A,
players WHERE a.playerid = players.playerid GROUP BY A.playerid ORDER BY
Bubbles DESC;
Anyone?
Skip.
--
-- Table structure for table `results`
--
CREATE TABLE results (
ID int(11) NOT NULL auto_increment,
playerId int(11) default '0',
date datetime default NULL,
locationId int(11) default NULL,
place int(11) default '0',
amount float default '0',
buyin int(5) default NULL,
rebuy enum('Y','N') default NULL,
gametype varchar(255) default 'Texas Hold Em',
numbuyins int(4) default NULL,
points double default '0',
PRIMARY KEY (ID),
KEY locationId (locationId),
KEY playerId (playerId)
) TYPE=MyISAM;
--
-- Table structure for table `players`
--
CREATE TABLE players (
playerId int(11) NOT NULL auto_increment,
firstName varchar(50) default NULL,
nickName varchar(50) default NULL,
lastName varchar(50) default NULL,
PRIMARY KEY (playerId)
) TYPE=MyISAM;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: SQL Question - Bayesian Filter detected spam
Why not just use the ABS Function update ev98nv_tm set mome=ABS(b) where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; 12.4.2. Mathematical Functions All mathematical functions return NULL in the event of an error. ABS(X) Returns the absolute value of X. mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam - Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- 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]
FW: Re: SQL Question
Why not just use the ABS Function update ev98nv_tm set mome=ABS(b) where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; 12.4.2. Mathematical Functions All mathematical functions return NULL in the event of an error. ABS(X) Returns the absolute value of X. mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam - Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- 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]
Bringing mysql server back up?
I was doing some testing with our development mysql server (4.1.x) and one of the databases died and wouldn't let me do anything not even read the tables in the database. So I thought I would bring it down and start it back up. I proceeded to use ./mysqladmin shutdown command which did what I wanted it to do and shutdown the server. Now I can't seem to figure out how to bring it back up. What is the command line I need to use? I've tried ./mysqld -start and I get the following error message. $:/usr/local/mysql/bin $ ./mysqld -start 060106 14:58:53 [Warning] Can't create test file /usr/local/mysql-standard-4.1.12-apple-darwin7.9.0-powerpc/data/wfmudev1.lower-test ./mysqld: Can't change dir to '/usr/local/mysql-standard-4.1.12-apple-darwin7.9.0-powerpc/data/' (Errcode: 13) 060106 14:58:53 [ERROR] Aborting 060106 14:58:53 [Note] ./mysqld: Shutdown complete Thanks!
MySQL or PHP problem?
Hi everyone, I've recently tried installing webcalander http://www.k5n.us/webcalendar.php on my FBSD box. I'm using version 5.1.1 of PHP, version 5.0.17 of MySQL, and version 1.0.2of Webcalander. I've posted for help on the webcal list and the few respondents seemed to think it might be a mysql issue. The error message certainly makes things appear that way anyhow. *Warning*: mysql_pconnect() [function.mysql-pconnecthttp://www.freebsd-geek.com/webcal/function.mysql-pconnect]: Access denied for user 'me'@'localhost' (using password: YES) in * /usr/www/docs/freebsd-geek.com/webcal/includes/php-dbi.php* on line *95* WebCalendar Error Error connecting to database: Access denied for user 'me'@'localhost' (using password: YES) I've checked line 95 in the file the error refers to. It doesn't appear to be an editable variable or anything like that, it's just code. I'm not nearly as familiar with mysql as I would like to be, so I have no idea how to proceed from here. Can some kind soul point/handhold me in the right direction? Thanks. :-) -- Tim DeBoer http://www.freebsd-geek.com Just once, I'd like it if someone called me Sir. Without adding You're creating a scene.
Sorry for my n00bie question - mysql 5.0.18 with load data infile
Hi All, Just wanted to apologize for my earlier rambling emails. I am been working on recoving from a innodb corruption for 2 days now and was really in panic mode. Everything seems to be running well with 5.0.18, althought my server load it up there. I am running 5.0.18 on a Suse 9.3 box, P4 3 GHZ, 4 GB RAM 16:38:57 up 86 days, 7:20, 4 users, load average: 4.44, 4.41, 4.51 USER TTYLOGIN@ IDLE JCPU PCPU WHAT root tty1 01Nov05 66days 0.23s 0.23s -bash this has been pretty much a sustained all day long. I have a perl script that takes some text cdrs, reformats them into .csv files, and then slams then into mysql using 'load data local'. Every 10 minutes, I process the raw cdrs, separate the completes and incompletes, generate 3 .csv files, and do 3 'load data local' commands. completed cdrs go into a innodb table, incompletes and raw cdrs go into a myISAM table. The tables are fixed length - no varchars. everything is forced to the full char length in my perl, so my 'load data' generates no warnings on truncated fields. The reason I did it this way was the incomp cdrs and raw cdrs are all read only. The completed cdrs have fields that get updated at a later time. There seemed to be a lot of processes out there waiting on table locks when this was a myisam table. The performance of the 'load data infile' command seems like it is taking way too long. (dates are just from `date` in perl): 2006-01-06 16:35:21 : begin import into comp_cdr 2006-01-06 16:35:42 : end import into comp_cdr (1361) (0)(0)(0) innodb table: 65 inserts per second. 2006-01-06 16:35:42 : begin import into incomp_cdr 2006-01-06 16:39:10 : import into incomp_cdr2 (15084) (0)(0)(0) Processed 16445 CDRs in 429.60 seconds - 38.28 cdr/sec 1361 complete calls, 15084 incomplete calls the myisam table takes longer... 75 inserts a second. My understanding is that 'load data' should be considerably faster than doing one big loop in perl and doing inserts. I haven't went back to perl for this, but that might be my next step. anyone have any suggestions. I am not posting any table descriptions here, but will do if needed. -- George Law VoIP Network Developer 864-678-3161 [EMAIL PROTECTED] MSN: [EMAIL PROTECTED]
mysql make breaks
When building mysql on a x86 solaris 9 server and running make I get the following error: make[2]: Entering directory `/kit1/software/mysql-5.0.18/strings' gcc -c -o strings-x86.o strings-x86.s Assembler: strings-x86.s, line 1 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 2 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 3 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 4 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 5 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 6 : Syntax error strings-x86.s, line 7 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 8 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 9 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 10 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 11 : Syntax error strings-x86.s, line 12 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 13 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 14 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 16 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 19 : Inappropriate assembler version: have 02.01 expect 1.00 or greater strings-x86.s, line 23 : Syntax error strings-x86.s, line 24 : Syntax error strings-x86.s, line 31 : Syntax error strings-x86.s, line 32 : Syntax error strings-x86.s, line 33 : Syntax error strings-x86.s, line 34 : Syntax error strings-x86.s, line 37 : Illegal mnemonic strings-x86.s, line 37 : Syntax error strings-x86.s, line 50 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 51 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 56 : Syntax error strings-x86.s, line 58 : Syntax error strings-x86.s, line 59 : Syntax error strings-x86.s, line 60 : Syntax error strings-x86.s, line 63 : Syntax error strings-x86.s, line 66 : Illegal mnemonic strings-x86.s, line 66 : Syntax error strings-x86.s, line 78 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 79 : Warning: Error in the # lineno from preprocessor strings-x86.s, line 85 : Syntax error strings-x86.s, line 86 : Syntax error strings-x86.s, line 87 : Syntax error strings-x86.s, line 88 : Illegal mnemonic strings-x86.s, line 88 : Syntax error strings-x86.s, line 90 : Syntax error strings-x86.s, line 91 : Illegal mnemonic strings-x86.s, line 91 : Syntax error strings-x86.s, line 92 : Syntax error strings-x86.s, line 93 : Syntax error strings-x86.s, line 95 : Syntax error strings-x86.s, line 96 : Syntax error strings-x86.s, line 97 : Syntax error Too many errors - Goodbye make[2]: *** [strings-x86.o] Error 1 make[2]: Leaving directory `/kit1/software/mysql-5.0.18/strings' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/kit1/software/mysql-5.0.18' make: *** [all] Error 2 After this I ran scripts/mysqlbug SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Super-User Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.0.18 (Source distribution) C compiler:gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7) C++ compiler: gcc (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7) Environment: machine, os, target, libraries (multiple lines) System: SunOS kit 5.9 Generic_112234-10 i86pc i386 i86pc Architecture: i86pc Some paths: /usr/bin/perl /opt/make/bin/make /opt/gcc/bin/gcc GCC: Reading specs from /opt/gcc/bin/../lib/gcc-lib/i386-pc-solaris2.9/3.3.2/specs Configured with: ./configure --prefix=/opt/gcc-3.3.2 : (reconfigured) ./configure --prefix=/opt/gcc-3.3.2 Thread model: posix gcc version 3.3.2 Compilation info:
RPM help
This is really a question of RPMs (though it arises because I'm eager to move from MySQL 4.1 to MySQL 5). If I knew the correct place to post the question, I would probably know where to find the answer. I do suspect that it's a FAQ, but I haven't found the right search terms. I'm using SuSE 9.3 (not the Enterprise Server), and I would like to upgrade from MySQL 4.1 to the latest stable version. There do not appear to be SuSE rpms for 5.0. I'm happy to build and install MySQL from source, but I would like to integrate the fact of that installation into the RPM database. I'm sure that there are tools or instructions on how to do that, but I haven't found any yet. Thanks for any help with this. Cheers, -j -- Jeffrey Goldberghttp://www.goldmark.org/jeff/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Replication
I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Replication
Jason Williard wrote: I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? It can be if you enable circular replication (A--B--A) . However, keep in mind that there is no way to prevent simultaneous updates (i.e., locking a table on A for update won't apply a lock to B..) I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Are there update operations occuring on servers 2 and 3? What's the bulk of your operations - selects or updates? There are a couple of ways to go with this. You can use clustering - in which case you can apply changes to the cluster and everything should stay in sync, or (the more straightfoward way) you can simply have all write operations performed on the master - use the slave servers for lookup only. If you use the second option (this assumes that the bulk of your operations are 'select' statements) you might be able to squeeze out some more performance by having the replica servers use a MyISAM table type (Assuming you use InnoDB on the master). If needed you can design a circular replication scheme - and have one of the slaves switch to a master role when the master is unavailable. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 http://www.otg-nc.com Phone: 877-258-8987/919-463-0999 Fax: 919-386-0158 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL View Optimization Help
Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott.
Re: RPM help
On Jan 6, 2006, at 4:38 PM, Jeffrey Goldberg wrote: I'm using SuSE 9.3 (not the Enterprise Server), and I would like to upgrade from MySQL 4.1 to the latest stable version. There do not appear to be SuSE rpms for 5.0. Someone has kindly pointed out to me off-list that there are generic linux RPMs (which I'd overlooked). -j -- Jeffrey Goldberghttp://www.goldmark.org/jeff/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT help.
Thank you very much to all who responded. I ended up using Shawn's solution, the others seem good as well. Thanks again. Have a great weekend. Richard [EMAIL PROTECTED] wrote: Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` = now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx 4 and recent_tx = 0; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 01/05/2006 10:43:15 AM: - Original Message - From: Richard Reina [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 05, 2006 10:29 AM Subject: SELECT help. Can someone help me write a query to tell me the customer numbers (C_NO) of those who've had more than 4 transactions but none in the last 6 months? | transactions_table | | ID|C_NO|DATE | AMOUT| |2901| 387|2003-10-09|23.00| Obviously my table has many more entries. Thank you for any help. What version of MySQL are you using? I would give a different answer if you had a version that supported subqueries than if you were using an older version that didn't support them. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] A people that values its privileges above its principles soon loses both. -Dwight D. Eisenhower.
Condition within a trigger
Hello All, I've been racking my brain trying to figure out something. I'm relatively new to the arena of SQL and have been doing a bunch of reading and experimenting. I have found a need for a trigger that will fire after an insert into a table. The trigger needs to act only if the value of a field contains something. CREATE TRIGGER only_this AFTER INSERT ON table_a FOR EACH ROW INSERT INTO table_b (email_addr,value) values (NEW.email_addr,0); This trigger works fine. However, I only want it to fire when NEW.email_addr contains a specific domain name. This is the part that is causing me confusion. -- This message is made of 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combine Into One Query ????
Hi, Im wondering if there is a way to combine the following queries into ONE query, or at the very least fewer queries... (DATE) is today's date. SELECT DISTINCT ip_address FROM logfile WHERE site_id = x AND date_time LIKE (DATE) GROUP BY ip_address SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %WIN% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %WIN% AND a_browser NOT LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %NETSCAPE% AND a_browser LIKE %WIN% AND a_browser LIKE %WINDOWS% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %NETSCAPE% AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %FIREFOX% AND a_browser LIKE %WIN% AND a_browser LIKE %WINDOWS% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %FIREFOX% AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %MSIE% AND a_browser LIKE %WIN% AND a_browser LIKE %WINDOWS% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %MSIE% AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %SAFARI% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %SAFARI% AND a_browser NOT LIKE %FIREFOX% AND a_browser NOT LIKE %NETSCAPE% AND a_browser NOT LIKE %MSIE% AND a_browser NOT LIKE %MAC% AND a_browser NOT LIKE %WIN% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %SAFARI% AND a_browser NOT LIKE %FIREFOX% AND a_browser NOT LIKE %NETSCAPE% AND a_browser NOT LIKE %MSIE% AND a_browser LIKE %MAC% AND a_browser LIKE %MOZ% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %SAFARI% AND a_browser NOT LIKE %FIREFOX% AND a_browser NOT LIKE %NETSCAPE% AND a_browser NOT LIKE %MSIE% AND a_browser LIKE %WIN% AND a_browser LIKE %MOZ% -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combine Into One Query ????
Hi, Im wondering if there is a way to combine the following queries into ONE query, or at the very least fewer queries... (DATE) is today's date. SELECT DISTINCT ip_address FROM logfile WHERE site_id = x AND date_time LIKE (DATE) GROUP BY ip_address SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %WIN% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %WIN% AND a_browser NOT LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %NETSCAPE% AND a_browser LIKE %WIN% AND a_browser LIKE %WINDOWS% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %NETSCAPE% AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %FIREFOX% AND a_browser LIKE %WIN% AND a_browser LIKE %WINDOWS% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %FIREFOX% AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %MSIE% AND a_browser LIKE %WIN% AND a_browser LIKE %WINDOWS% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %MSIE% AND a_browser LIKE %MAC% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser LIKE %SAFARI% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %SAFARI% AND a_browser NOT LIKE %FIREFOX% AND a_browser NOT LIKE %NETSCAPE% AND a_browser NOT LIKE %MSIE% AND a_browser NOT LIKE %MAC% AND a_browser NOT LIKE %WIN% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %SAFARI% AND a_browser NOT LIKE %FIREFOX% AND a_browser NOT LIKE %NETSCAPE% AND a_browser NOT LIKE %MSIE% AND a_browser LIKE %MAC% AND a_browser LIKE %MOZ% SELECT a_browser FROM logfile WHERE site_id = x AND date_time LIKE (DATE) AND a_browser NOT LIKE %SAFARI% AND a_browser NOT LIKE %FIREFOX% AND a_browser NOT LIKE %NETSCAPE% AND a_browser NOT LIKE %MSIE% AND a_browser LIKE %WIN% AND a_browser LIKE %MOZ% -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SETting values to TABLE field at TRIGGER runtime
Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting # of days until expiration
On Jan 5, 2006, at 9:38 PM, Michael Stassen wrote: DATEDIFF was added in 4.1.1. What version of mysql do you have? Thanks Michael - that was indeed the problem. Some ancient-ass version that's been on my development server for who knows how many years, in accordance with the If it ain't broke don't fix it rule. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Condition within a trigger
Sorry for responding to my own message. I figured this out. DELIMITER // CREATE TRIGGER only_this AFTER INSERT ON table_a FOR EACH ROW BEGIN IF NEW.email_addr LIKE '[EMAIL PROTECTED]' THEN INSERT INTO table_b (email_addr,value) VALUES (NEW.email_addr,0); END IF; END On Saturday, January 7, 2006 at 2:39:07 AM, [EMAIL PROTECTED] confabulated: Hello All, I've been racking my brain trying to figure out something. I'm relatively new to the arena of SQL and have been doing a bunch of reading and experimenting. I have found a need for a trigger that will fire after an insert into a table. The trigger needs to act only if the value of a field contains something. CREATE TRIGGER only_this AFTER INSERT ON table_a FOR EACH ROW INSERT INTO table_b (email_addr,value) values (NEW.email_addr,0); This trigger works fine. However, I only want it to fire when NEW.email_addr contains a specific domain name. This is the part that is causing me confusion. -- This message is made of 100% recycled electrons. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL View Optimization Help
Scott Klarenbach [EMAIL PROTECTED] wrote on 01/06/2006 08:13:10 PM: Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott. Treat views as you would any other query. All of the optimizations that normally apply to SELECT query performance should also apply to view performance. Views differ from tables in that they cannot be indexed. That is probably why you are getting performance hits by building views on views. Any query against a view (such as a second-tier derivative view) will end up performing the equivalent of a full table scan on any view it uses. There is no hard and fast rule about building views based on other views or based on tables. What works best for you should be which solution you stick with. If you have millions of rows in a base table and a view can reduce that to about ten thousand rows of summary information, I would be very tempted to stick with the view as the basis of a future query. You still have to generate that view each time you want to use it but its data may be sitting there in the query cache so it has the potential to be very fast. If I were you I would review the entire optimization chapter: http://dev.mysql.com/doc/refman/5.0/en/optimization.html It's loaded with useful information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
missing mysqld.sock
I've installed mysql 4 and noticed that both mysql 4.0 and 4.1 was on the system. In my attempt to remove the older version I may have done away with my mysqld.sock. When I try to access mysql i'm getting the following message: debOS:~# /etc/init.d/mysql start Starting MySQL database server: mysqld...failed. Please take a look at the syslog. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Any ideas? Thanks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=us-ascii META content=MSHTML 6.00.2900.2802 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVI've installed mysql 4 and noticed that both mysql 4.0 and 4.1 was on the system.nbsp; In my attempt to remove the older version I may have done away with my mysqld.sock.nbsp; When I try to access mysql i'm getting the following message:/DIV DIVdebOS:~# /etc/init.d/mysql startBRStarting MySQL database server: mysqld...failed.BRnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp; Please take a look at the syslog.BR/usr/bin/mysqladmin: connect to server at 'localhost' failedBRerror: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'BRCheck that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!BR/DIV DIVnbsp;/DIV DIVAny ideas?/DIV DIVnbsp;/DIV DIVThanks/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing mysqld.sock
Hi Jon, This reference will give you all the info you need. http://dev.mysql.com/doc/refman/4.1/en/can-not-connect-to-server.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Jon Miller [mailto:[EMAIL PROTECTED] Sent: Saturday, 7 January 2006 5:59 PM To: mysql@lists.mysql.com Subject: missing mysqld.sock I've installed mysql 4 and noticed that both mysql 4.0 and 4.1 was on the system. In my attempt to remove the older version I may have done away with my mysqld.sock. When I try to access mysql i'm getting the following message: debOS:~# /etc/init.d/mysql start Starting MySQL database server: mysqld...failed. Please take a look at the syslog. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Any ideas? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
