Re: trigger

2009-11-04 Thread Phil
ah, yes I'd missed the 'for each row' when I posted. But for the date math part, look at the column, it's an int() not a date. Puzzled me a little at the time so I tried it.. mysql> select now()-60*60*24*5 from dual; +---+ | now()-60*60*24*5 | +---+ |

RE: trigger

2009-11-04 Thread Gavin Towey
Oops, one more mistake: NOW()-60*60*24*5 isn't the way to do date math. It should be: NOW() - INTERVAL 5 DAY -Original Message- From: Gavin Towey Sent: Wednesday, November 04, 2009 2:33 PM To: 'Phil'; Mysql; 'Stefan Onken' Subject: RE: trigger 1. Triggers must have FOR EACH ROW -- it's

RE: trigger

2009-11-04 Thread Gavin Towey
1. Triggers must have FOR EACH ROW -- it's described in the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html So the correct syntax would be: CREATE TRIGGER greylist_ins AFTER INSERT on greylist FOR EACH ROW delete from greylist where first_seen < NOW()-60*60*24*5; BEGIN/END an

Re: Alphabetical search to and from

2009-11-04 Thread Steve Edberg
At 11:52 PM +0900 11/4/09, Dave M G wrote: MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to

Re: trigger

2009-11-04 Thread Phil
You are missing a BEGIN in the trigger delimiter | CREATE TRIGGER greylist AFTER INSERT on greylist BEGIN delete from greylist where first_seen < NOW()-60*60*24*5; END; | delimiter ; Phil On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken wrote: > Hello, > > I am new to using triggers in mysql. I

Re: trigger

2009-11-04 Thread Michael Dykman
Well you might need to parenthesize the expression, but note that simple integer math done on a DATE or DATETIME, the units are days. you probably want ... where first_seen < (NOW() - 5); michael dykman On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken wrote: > Hello, > > I am new to using trigg

trigger

2009-11-04 Thread Stefan Onken
Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen < NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake?

RE: Alphabetical search to and from

2009-11-04 Thread misiaQ
> Or : > alter table users add first_f_name char(1) not null; > create index first_f_name_idx on users (first_f_name); > update users set first_f_name = left(first_name,1); ... and don't forget the trigger on insert, update to keep that new column always up to date. Regards, m.

Re: CONNECTION_ID() and Threads

2009-11-04 Thread Tompkins Neil
Hi Michael, We are using standard classic ASP code like : *Function OpenConnection() OpenConnection = "DSN=MyDSN_SSL" End Function DSNTemp = OpenConnection() Set Conn = Server.CreateObject("ADODB.Connection") Set RecordsetTest = Server.CreateObject("ADODB.RecordSet") response.wri

Re: Alphabetical search to and from

2009-11-04 Thread Jay Ess
Or : alter table users add first_f_name char(1) not null; create index first_f_name_idx on users (first_f_name); update users set first_f_name = left(first_name,1); And not the query will use index. select username from users where first_f_name between "A" and "B"; -- MySQL General Mailing List

Re: CONNECTION_ID() and Threads

2009-11-04 Thread Michael Dykman
I am certainly no expert in Windows, but that sounds like the work of whatever is managing the connections on your application server. Whatever language you are using, if you opened the connection raw, what you are describing would be impossible. You need to look at how you are acquiring your conn

Re: CONNECTION_ID() and Threads

2009-11-04 Thread Tompkins Neil
Hi Thanks for your quick response. In our test environment running MySQL 5.1.31 over SSL (hosted external of our network) and IIS 6 on Windows 2003 Server, I've found when opening a test connection to the database from my PC and one from my colleagues PC on the same internal network, we both had

Re: Alphabetical search to and from

2009-11-04 Thread Michael Dykman
Good point about that first position. Of course, the ending would need an interpretation according to the charset at hand. select * from country Name between 'F' and 'P\u\u\u\u\u' - md On Wed, Nov 4, 2009 at 10:26 AM, misiaQ wrote: > Hi, > > You're right on one hand. But

RE: Alphabetical search to and from

2009-11-04 Thread misiaQ
Hi, You're right on one hand. But on the another in some languages you have a more characters after the Z letter. Apart from that try this: select 'F' as test union select 'FAA' order by test; and you'll see: test - F FAA Regards, m. -Original Message- From: Michael Dykman [ma

CONNECTION_ID() and Threads

2009-11-04 Thread Tompkins Neil
Hi, With regards the CONNECTION_ID() variable in MySQL, is this based on a per user basis. Or can multiple users use the same connection_id ? Thanks, Neil

Re: Alphabetical search to and from

2009-11-04 Thread Michael Dykman
Haven't tried this myself, but would this not work while permitting the index to engage? select * from country Name between 'FAA' and 'PZZ' On Wed, Nov 4, 2009 at 10:15 AM, misiaQ wrote: > Try this: > select * from country where LEFT(Name, 1) between 'F' and 'P'; > > Regards, > m

RE: Alphabetical search to and from

2009-11-04 Thread misiaQ
Try this: select * from country where LEFT(Name, 1) between 'F' and 'P'; Regards, m. -Original Message- From: Dave M G [mailto:d...@articlass.org] Sent: 04 November 2009 14:52 To: mysql@lists.mysql.com Subject: Alphabetical search to and from MySQL, This should be a fairly simple quest

RE: missing ttydefaults.h

2009-11-04 Thread Martin Gainty
no i havent ..any clues as to location of kernel headers? (apologies for the newbie question) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehen

Re: Alphabetical search to and from

2009-11-04 Thread Jay Ess
Dave M G wrote: MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to do that? Thank you for any

Alphabetical search to and from

2009-11-04 Thread Dave M G
MySQL, This should be a fairly simple question. I have a table with a bunch of people's names. I want to find people who's name begins within a certain range of characters. All names between F and P, for example. What SELECT statement would I use to do that? Thank you for any advice. -- Dave

Re: missing ttydefaults.h

2009-11-04 Thread Michael Dykman
Let me guess, you are doing a source build on a linux box? Have you installed the kernel headers? - michael dykman 2009/11/4 Martin Gainty : > > In file included from readline.c:54: > readline/readline.h:70:29: sys/ttydefaults.h: No such file or directory > > any clue where i can locate? > Mart

missing ttydefaults.h

2009-11-04 Thread Martin Gainty
In file included from readline.c:54: readline/readline.h:70:29: sys/ttydefaults.h: No such file or directory any clue where i can locate? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist v

Re: RE: socket '/tmp/mysql.sock' (2)

2009-11-04 Thread Claudio Nanni
Hi Charles, Try this: mysql -u -p -h127.0.0.1 -P3306 to force tcp instead of socket Let me know! Cheers Claudio On Nov 4, 2009 4:34 AM, "Brown, Charles" wrote: From: Brown, Charles Sent: Tuesday, November 03, 2009 2:54 PM To: Ramsey, Robert