Re: [exim] mysql lookup on update: $value is affected rows?

Kezdőlap
Üzenet törlése
Válasz az üzenetre
Szerző: Marilyn Davis
Dátum:  
Címzett: exim-users
Tárgy: Re: [exim] mysql lookup on update: $value is affected rows?
On Wed, 27 Oct 2004, Philip Hazel wrote:

> On Tue, 26 Oct 2004, Marilyn Davis wrote:
>
> > It's NOT updating at that point. That's why there's a 0. I still
> > don't know what is happening but I'm sure it's my fault.
>
> You need to find WHY it isn't updating. Is this a verification? Is it


Yes, why?

No, not verifying. I don't think. I call exim:

exim -d+expand -t -f marilyn@??? < /b/local/doorman/tests/messages/gray_message >& /tmp/eout

so my ACL stuff doesn't happen. The message is:

[root@maildance tests]# cat messages/gray_message
To: marilyn@???
From: marilyn@???
Subject: Looking

What's up?
[root@maildance tests]#

A few routers before these are hit, I get:

--------> verify_only router <--------
local_part=marilyn domain=maildance.com
verify_only router skipped: verify_only set

and our routers start:

--------> pre_out router <--------
local_part=marilyn domain=maildance.com

And it goes straight on to deliver the mail.

> happening during incoming SMTP mail? If so, remember that Exim is
> running as "exim", not as "root", and maybe there's a permissions
> problem?
>


No, not smtp, as you see.

We are lax on security because we have no internal users, just my son
and me. We're working to provide some web mail. I don't think it can
be security.

Also, another router makes records for outbound mail (other
experiment) and updates them just fine. But the syntax is a bit
different. I'll do thye pre_ post_ trick again around the router:

pre_out:
   driver = redirect
   debug_print = PRE_OUT:${lookup mysql{\
          select last_use from in_out where \
          in_id = SENDER_ID and out_id = ADDRESS_ID}{$value}{XXX}}
   data =


expanding: PRE_OUT:${lookup mysql{select last_use from in_out where in_id = ${extract{sender_id}{$address_data}} and out_id = ${extract{id}{$address_data}}}{$value}{XXX}}
    result: PRE_OUT:2004-10-27 10:38:25


outbound_update_in_out_knowns:
   driver = redirect
   debug_print = $address_data
   address_data = updated = yes $address_data
   condition = ${if and { { > {ADDRESS_ID}{0}}\
                          { SENDER_IS_OURS}\
                          { > {\
${lookup mysql{insert ignore into in_out (in_id, out_id) \
values (SENDER_ID, ADDRESS_ID) \
on duplicate key update last_use = now()}{$value}{0}} \
                               }{0}} \
                         } {yes}{no}}
  data =


expanding: ${lookup mysql{insert ignore into in_out (in_id, out_id) values (${extract{sender_id}{$address_data}}, ${extract{id}{$address_data}}) on duplicate key update last_use = now()}{$value}{0}}
    result: 2


( 2 means that an existing row was updated rather than that a new one
inserted.)

post_out:
   driver = redirect
   debug_print = POST_OUT:${lookup mysql{\
          select last_use from in_out where \
          in_id = SENDER_ID and out_id = ADDRESS_ID}{$value}{XXX}}
   data =


expanding: POST_OUT:${lookup mysql{select last_use from in_out where in_id = ${extract{sender_id}{$address_data}} and out_id = ${extract{id}{$address_data}}}{$value}{XXX}}
    result: POST_OUT:2004-10-27 10:48:02


----
So that worked.

So I try the same thing with an extra update with the non-working
mysql syntax:

----

expanding: PRE_OUT:${lookup mysql{select last_use from in_out where in_id = ${extract{sender_id}{$address_data}} and out_id = ${extract{id}{$address_data}}}{$value}{XXX}}
    result: PRE_OUT:2004-10-27 10:48:02


other_call:
   driver = redirect
   debug_print = ${lookup mysql{\
update in_out set last_use = now() where in_id = SENDER_ID \
                                    and out_id = ADDRESS_ID}{$value}{xxx}}
   data =


MYSQL query: update in_out set last_use = now() where in_id = 10 and out_id = 60
MYSQL using cached connection for localhost/maildance/root
MYSQL: query was not one that returns data
long result = 1

between:
   driver = redirect
   debug_print = BETWEEN:${lookup mysql{\
          select last_use from in_out where \
          in_id = SENDER_ID and out_id = ADDRESS_ID}{$value}{XXX}}
   data =


expanding: BETWEEN:${lookup mysql{select last_use from in_out where in_id = ${extract{sender_id}{$address_data}} and out_id = ${extract{id}{$address_data}}}{$value}{XXX}}
    result: BETWEEN:2004-10-27 11:01:57
---
So that worked here! Grrrrrrr???
It goes on to the router that does the fancy update:


type=mysql key="insert ignore into in_out (in_id, out_id) values (10, 60) on duplicate key update last_use = now()"
database lookup required for insert ignore into in_out (in_id, out_id) values (10, 60) on duplicate key update last_use = now()
MYSQL query: insert ignore into in_out (in_id, out_id) values (10, 60) on duplicate key update last_use = now()
MYSQL using cached connection for localhost/maildance/root
MYSQL: query was not one that returns data
long result = 2

Looks good too. And on to check the date again:

expanding: POST_OUT:${lookup mysql{select last_use from in_out where in_id = ${extract{sender_id}{$address_data}} and out_id = ${extract{id}{$address_data}}}{$value}{XXX}}
    result: POST_OUT:2004-10-27 11:01:57


I think this probably worked, given the long result = 2. Surely the
clock didn't click.

So I remain clueless and I'm going to stop beating my head on this for
a few days and get some work done -- unless someone has a new avenue
to pursue. But I'll come back to it.

It's crazy, isn't it?

Thank you for helping me think, Philip.

Marilyn