Hi, I have a Dovecot test installation with MySQL userbase and authentication where I am trying to setup last_login plugin with SQL dictionary
I read http://wiki2.dovecot.org/Plugins/LastLogin and I addes this to my configuration
plugin { last_login_dict = sql:/etc/dovecot/dovecot-sql.conf.ext } mail_plugins = $mail_plugins stats protocol imap { mail_plugins = $mail_plugins imap_stats last_login }
When I log in with IMAP I get this error:
Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-sql.conf.ext) failed: Unknown dict module: sql
/etc/dovecot/dovecot-sql.conf.ext is the file taht contains the SQL configuration.
If I put mysql instead of sql the result is the same:
Error: last_login_dict: dict_init(mysql:/etc/dovecot/dovecot-sql.conf.ext) failed: Unknown dict module: mysql
The output of doveconf -n follows.
Do you have any idea on how to use MySQL for last_login?
Ciao, luigi
2.2.14: /etc/dovecot/dovecot.conf
OS: Linux 2.6.32-431.29.2.el6.x86_64 x86_64 CentOS release 6.5 (Final)
auth_cache_negative_ttl = 0 auth_cache_size = 100 k auth_cache_ttl = 8 hours auth_mechanisms = plain login digest-md5 cram-md5 auth_verbose = yes base_dir = /var/run/dovecot/ listen = 127.0.0.1, 10.19.67.253 login_greeting = Ready. login_trusted_networks = 10.xx.xx.0/24, 2001:xxxx:xxxx:xxxx::/64 mail_plugins = " stats" mailbox_list_index = yes maildir_stat_dirs = yes passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { last_login_dict = mysql:/etc/dovecot/dovecot-sql.conf.ext sieve = ~/.dovecot.sieve sieve_dir = ~/sieve stats_refresh = 10s stats_track_cmds = yes } protocols = imap service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0660 user = postfix } } service imap-login { inet_listener imap { port = 143 } inet_listener imaps { port = 993 ssl = yes } } service managesieve-login { inet_listener sieve { port = 4190 } } service pop3-login { inet_listener pop3 { port = 110 } inet_listener pop3s { port = 995 ssl = yes } } service stats { fifo_listener stats-mail { mode = 0666 } } ssl_cert = </etc/xxxxx/xxxxxx.luigirosa.com.crt ssl_dh_parameters_length = 2048 ssl_key = </etc/xxxxx/xxxxx.luigirosa.com.key ssl_parameters_regenerate = 202 hours ssl_protocols = !SSLv2 !SSLv3 syslog_facility = local5 userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } verbose_proctitle = yes protocol lmtp { mail_plugins = sieve } protocol imap { imap_client_workarounds = tb-extra-mailbox-sep mail_plugins = " stats imap_stats last_login" } protocol pop3 { pop3_client_workarounds = outlook-no-nuls pop3_uidl_format = %08Xu%08Xv }
-- / +--[Luigi Rosa]-- \
Microsoft launches site to help those with H1N1. No help for those with Windows 7 --fark.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Luigi Rosa wrote on 18/10/2014 11:29:
Hi, I have a Dovecot test installation with MySQL userbase and authentication where I am trying to setup last_login plugin with SQL dictionary
I figured out I have to use dovecot-dict-sql.conf.ext instead of dovecot-sql.conf.ext
I configured dovecot-dict-sql.conf.ext this way:
driver= mysql connect = {the connect string that works for user lookup} map { pattern = last-login table = mailbox username_field = username }
And I defined the plugin this way:
plugin { last_login_dict = sql:/etc/dovecot/dovecot-dict-sql.conf.ext last_login_key = last-login/%u # default }
But I still get the same error:
Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-dict-sql.conf.ext) failed: Unknown dict module: sql
Ciao, luigi
/ +--[Luigi Rosa]-- \
Every program has (at least) two purposes: the one for which it was written and another for which it wasn't. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1
iQIcBAEBAgAGBQJUQmwfAAoJEO5WT/qgw4yKgV0P/iGjH7Tcy/tfCf9fVjECO187 fa07fBXwG+BR9CqDejmNeT0qGC8Vx3YebWl3Sc61N8z6CMXzwQUo4gAsLnw3bW/3 VTioxcZKULbE8bNE8goN/yrNur1N4yMcrQTSb+Lno4D5adXfWdXC2j8nwRrAnjXf om4XvWP+S/PX7oblqymNCHWtJhmOwPkCPKqKqpXxdZ4SXQHCb+96NI4Mkbp1WVEr 1npH8+p1ut8s1wjwGo56tE+EplJKNp4oMhQch4nHQp6uWnAH3IF6P8T23iLnog7p wuG0e2Mo1vDoMWaf7J+6lIUB45OizgA1qAHho5jpASCGQE5D/XqolRzTTvJJ2Ykh Jgll8Gc+/JqMeHX5XlsUsVdPGEFIvwTGyKjZB90qA5UPOJf6hgRQbcVam3iUDBRW v+KRrtPuTeExDj9nfxL1QZAwoFW6QY+mUNvS+jgb1vGmQlCK12lUgBzVPi1d72sN boHmpmGfLnl97cg+FjrAtU8N0aBeoOkIqECmLSmnQwdwz+PkoyrgKvAVt4IgI0aX WVNBSy1cir+/I2c9EY+FTYyRczSaRglTMBwLMM1O910NMRvsZmqniTCvfBnhv4Xv 95GhLhisAp/TBUSD8Jnk+K76Un/xfNSAex1t6NeyVdEKSOiqVlSMYo8o5wHBzaXV A6ztvItzmTnfOux5C03q =6gvz -----END PGP SIGNATURE-----
On 10/18/2014 09:33 AM, Luigi Rosa wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Luigi Rosa wrote on 18/10/2014 11:29:
Hi, I have a Dovecot test installation with MySQL userbase and authentication where I am trying to setup last_login plugin with SQL dictionary I figured out I have to use dovecot-dict-sql.conf.ext instead of dovecot-sql.conf.ext
I configured dovecot-dict-sql.conf.ext this way:
driver= mysql connect = {the connect string that works for user lookup} map { pattern = last-login table = mailbox username_field = username }
And I defined the plugin this way:
plugin { last_login_dict = sql:/etc/dovecot/dovecot-dict-sql.conf.ext last_login_key = last-login/%u # default }
But I still get the same error:
Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-dict-sql.conf.ext) failed: Unknown dict module: sql
It's been said here before, and I'll echo it again -- better documentation for dict would be helpful, a step-by-step guide to its principles of operation, rather than just the anecdotal examples we have, out of which we're supposed to extrapolate.
On 10/18/2014 10:05 AM, Gedalya wrote:
On 10/18/2014 09:33 AM, Luigi Rosa wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Luigi Rosa wrote on 18/10/2014 11:29:
Hi, I have a Dovecot test installation with MySQL userbase and authentication where I am trying to setup last_login plugin with SQL dictionary I figured out I have to use dovecot-dict-sql.conf.ext instead of dovecot-sql.conf.ext
I configured dovecot-dict-sql.conf.ext this way:
driver= mysql connect = {the connect string that works for user lookup} map { pattern = last-login table = mailbox username_field = username }
And I defined the plugin this way:
plugin { last_login_dict = sql:/etc/dovecot/dovecot-dict-sql.conf.ext last_login_key = last-login/%u # default }
But I still get the same error:
Error: last_login_dict: dict_init(sql:/etc/dovecot/dovecot-dict-sql.conf.ext) failed: Unknown dict module: sql
It's been said here before, and I'll echo it again -- better documentation for dict would be helpful, a step-by-step guide to its principles of operation, rather than just the anecdotal examples we have, out of which we're supposed to extrapolate.
"The above SQL example uses dictionary proxy process (see below), because SQL libraries aren't linked to all Dovecot binaries. The file and Redis examples use direct access. "
http://wiki2.dovecot.org/Quota/Dict
I guess you have to use the proxy.
So maybe something like
plugin { last_login_dict = proxy::lastlogin }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext }
I'll try it later myself, thanks for taking it thus far :-) I've been meaning to try this out but was rather perplexed.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Gedalya wrote on 18/10/2014 16:10:
I guess you have to use the proxy.
So maybe something like
plugin { last_login_dict = proxy::lastlogin }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext }
I'll try it later myself, thanks for taking it thus far :-) I've been meaning to try this out but was rather perplexed.
I made some progress thank to your suggestions.
dovecot-dict-sql.conf.ext content is:
map { pattern = /shared/last-login/$user table = mailbox username_field = username value_field = lastlogin fields { username = $user } }
Note that you must not specify "driver" parameter
Plugin is defined this way:
plugin { last_login_dict = proxy::lastlogin last_login_key = last-login/%u # default }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext mode = 0660 user = 501 }
But I get two errors.
First is the permission of /var/run/dovecot/dict that is 700 root.root and I get a permission error. Nothing changes if I put user = root. I have to manually chmod /var/run/dovecot/dict every time I restart Dovecot otherwise I get
Error: net_connect_unix(/var/run/dovecot//dict) failed: Permission denied (euid=501(mailvirtuale) egid=501(mailvirtuale) missing +r perm: /var/run/dovecot//dict, dir owned by 0:0 mode=0755)
If I chmod 777 /var/run/dovecot/dict the error disappears but I get this:
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB> dovecot: dict: Error: sql dict set: Invalid/unmapped key: shared/last-login/username
Ciao, luigi
/ +--[Luigi Rosa]-- \
Don't tell me what you dreamed last night for I've been reading Freud. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1
iQIcBAEBAgAGBQJUQnsNAAoJEO5WT/qgw4yKPgsP/i3PvHmaHZ3FTyDkXczenVm+ 12dA5MHQ7p26Xai7C3hJeLn/mteZUqc7aEM839nB6vFo+nPBJYjPgVB7k24eSq4T ZV/F3RZV+I4f1oTvzxH8gKvFnXEEbGSXsM7/U+anSdbNVMEPfpZDxgf/DKJfCk61 NBJN+n9bqQ4ZhaN6UD9AGh5w3Oyh4GK40D7Xv6NVca+psMjuT17TKgZ1JaMg7Ir0 QV4f3wmCWfMFGIpxUvUca7UTENYWHkdfLqsWjDU8+tLM7meuJ3PKMasuOKTNMglY 8PyszRGPCBfPvoQluC0K7uXqlEMLpXEmdz354chilXYX4T7zUh+9IHV4MSg1SSQX B7HMrTOU5TY7HfqzHOtLrb4CVtj6thzl9Ixh7Qa13LDbjzqSI5Pe78nVcmfVqp8I Wqovm+XWqm6nUoNFh850iw57t15D6Pg+vZT/qJBT8o++lcA+ssriNsFHw8/nFqSV qe7Oxifhrn7j4KRdBlK8gKiydhxFT//bIdlS2bOwPi+nnkcnT7xT46L1WZVfhQez hYTxAK9ETKonT4O2IKSSn92fxqEobZu99Kom5sRm4uLsvQxdOwjKPJn0Bx00XCW1 vCp1/gmFYkLPAtB/hQ0U32WpL1x/GMKRd6BZXsHQMDgVkf4dVQqB23oUiugl/whE U5s4Dr/5WbmNITXOd0/W =LFZi -----END PGP SIGNATURE-----
On 10/18/2014 10:37 AM, Luigi Rosa wrote:
But I get two errors.
First is the permission of /var/run/dovecot/dict that is 700 root.root and I get a permission error. Nothing changes if I put user = root. I have to manually chmod /var/run/dovecot/dict every time I restart Dovecot otherwise I get
Error: net_connect_unix(/var/run/dovecot//dict) failed: Permission denied (euid=501(mailvirtuale) egid=501(mailvirtuale) missing +r perm: /var/run/dovecot//dict, dir owned by 0:0 mode=0755) In conf.d/10-master.conf (this is from my config):
service dict { unix_listener dict { mode = 0660 user = vmail #group = } }
If I chmod 777 /var/run/dovecot/dict the error disappears but I get this:
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB> dovecot: dict: Error: sql dict set: Invalid/unmapped key: shared/last-login/username
Ok, what were you thinking when you defined this pattern = /shared/last-login/$user ? I personally don't understand this part of the dovecot config at all..
Let's look into this..
Ciao, luigi
On 10/18/2014 10:48 AM, Gedalya wrote:
If I chmod 777 /var/run/dovecot/dict the error disappears but I get this:
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB> dovecot: dict: Error: sql dict set: Invalid/unmapped key: shared/last-login/username
Ok, what were you thinking when you defined this pattern = /shared/last-login/$user ? I personally don't understand this part of the dovecot config at all..
Let's look into this.. OK I suspect that"
pattern = shared/{dictionary name}/$user
So:
pattern = shared/lastlogin/$user
As per:
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext mode = 0660 user = 501 }
On 10/18/2014 11:02 AM, Gedalya wrote:
On 10/18/2014 10:48 AM, Gedalya wrote:
If I chmod 777 /var/run/dovecot/dict the error disappears but I get this:
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB> dovecot: dict: Error: sql dict set: Invalid/unmapped key: shared/last-login/username
Ok, what were you thinking when you defined this pattern = /shared/last-login/$user ? I personally don't understand this part of the dovecot config at all..
Let's look into this.. OK I suspect that"
pattern = shared/{dictionary name}/$user
So:
pattern = shared/lastlogin/$user
As per:
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext mode = 0660 user = 501 } Or it's plugin name so try last_login ??
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Gedalya wrote on 18/10/2014 16:48:
service dict { unix_listener dict { mode = 0660 user = vmail #group = } }
That solved the permission issue, thanks!
Ciao, luigi
/ +--[Luigi Rosa]-- \
Only one human captain has ever survived battle with a Minbari fleet. He is behind me. You are in front of me. If you value your lives, be somewhere else. --Delenn "Severed Dreams" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1
iQIcBAEBAgAGBQJUQovyAAoJEO5WT/qgw4yKpzYP/i6WHK/4QkNPAgKX2o5fP+j8 qQKzmm8XXANd8qWDkMxlb0xyAqrNJsDAm1CKq5xI+r3QN+f8HcDqNzsDN7m0cqUf jyqJ+KDpbDnXbMU8DZHkK8vuKl+b5QpL5pSmrxYfAEfoJkrjIzGJ14EopbZ+dtXg 6xTY6RuQOF8XMNV1fy2k2TlCjD6rIE0YyanBB5BJvhKhAsZ3egwbhAoqvPPJi9CR 8s0zErdeiT+3MJKsGgmaTfrIa9yg3ADFLqgAQfS3OryRjVD7EThDSsh/uyLMSUWX 50Nv4+ItnszbCCVRfu8Al6IcGvGhnxgvMpibFJUomdrYkwIv9gnHe+vL8+HCAuTl 0ZMaWEYCDtf+4PLY3nznAe7/1QqF4C59vSGkYxp9jtFEvSZ2DIBny0fuzRFBS4Jl b5kdtsCCtknk25lXQzhPg/FE9q5Aj4Zo183/s8unoFKKxHq/Rwr2df1dpQw9ITDA FFcAMvkyRvTxGeldKpADTcm5bMOLZlKPmmf+UKViZFysXKgcm3SLVR+RpFkajQ4v l1BlpZ2wW2vjhYcfj9JZmd/+uEturA8vG6vbha2w4GP4zTwrKdZjEHiTvi4/nkMY LxogXNhBMCTCwiYkPZINb05Eagg++zcmaCbZM31tb2m3dHrnvtmuCZKGfKA4DTMZ NP/UCGkRQd96zRCTY8Ii =J7GG -----END PGP SIGNATURE-----
On 10/18/2014 10:37 AM, Luigi Rosa wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Gedalya wrote on 18/10/2014 16:10:
I guess you have to use the proxy.
So maybe something like
plugin { last_login_dict = proxy::lastlogin }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext }
I'll try it later myself, thanks for taking it thus far :-) I've been meaning to try this out but was rather perplexed. I made some progress thank to your suggestions.
dovecot-dict-sql.conf.ext content is:
map { pattern = /shared/last-login/$user table = mailbox username_field = username value_field = lastlogin fields { username = $user } }
Note that you must not specify "driver" parameter
Plugin is defined this way:
plugin { last_login_dict = proxy::lastlogin last_login_key = last-login/%u # default }
dict { lastlogin = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext mode = 0660 user = 501 }
But I get two errors.
First is the permission of /var/run/dovecot/dict that is 700 root.root and I get a permission error. Nothing changes if I put user = root. I have to manually chmod /var/run/dovecot/dict every time I restart Dovecot otherwise I get
Error: net_connect_unix(/var/run/dovecot//dict) failed: Permission denied (euid=501(mailvirtuale) egid=501(mailvirtuale) missing +r perm: /var/run/dovecot//dict, dir owned by 0:0 mode=0755)
If I chmod 777 /var/run/dovecot/dict the error disappears but I get this:
dovecot: imap-login: Login: user=<username>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=497913, secured, session=<6R0mYbMFFwB/AAAB> dovecot: dict: Error: sql dict set: Invalid/unmapped key: shared/last-login/username
OK I got it.
The code looks at shared/$last_login_key, so shared/last-login/username, but your map says /shared/last-login/$user, note the leading slash! That's probably all it is.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Gedalya wrote on 18/10/2014 17:36:
The code looks at shared/$last_login_key, so shared/last-login/username, but your map says /shared/last-login/$user, note the leading slash! That's probably all it is.
BINGO!
Now that we killed the mosters of this level, let's move to the next level.....
Welcome to the SQL query level!
I was expecting an UPDATE...WHERE but I got this:
INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON DUPLICATE KEY UPDATE lastlogin='1413647370'
This is not very useful if I want a lastlogin field of the mailbox table
updated on every login.
Ciao, luigi
/ +--[Luigi Rosa]-- \
For every human problem, there is a neat, simple solution; and it is always wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1
iQIcBAEBAgAGBQJUQozoAAoJEO5WT/qgw4yKXd0P/iGtE9bm3U3gUNqoOP8cI0Yi 2xU66Nk64jogcPyRXberWiaeXbytaJj15DAVwbQT08Iv53flOMbkNwn2LyfX2Yqi DThdCaH60ADTkBsU2NUepglv3mGisuZ/T8euKo6OdDBowZ1t0u8EW1J41DP/4K6M SWLDPcTkLGgcfJMCEvwP2JeWx6j0RCR2/e/mSe0Fvq9qUNYTbqHiDTLSh6bO/OPp 0vxj0LVb/xWIUlNS0pEbhFlSXaU5yNORyIacg0KMWEPB0d0qwGP6kusxi2l+huF/ VfbebBpj9qns8XJppxKTEpTKW7H3BoiM3vegLl7JNOwSKXa9xiNMhILkxcpgkRBi idu8w+p7KXWrOWqDQ3giFhFrtMuhYNGryVvetD168426+AVkVM8/Zb7dETajgzH3 75XLlc4fMhyW6aRV5vGDllY6MzyDrEFsO3vtCE7NKZq7WmImnlwKsbm3NbEYFWsn fEmOM9Hjc/DkRTTiejA31DztczrFd/f/kXXFSuPs25Dco6WVkojofpT4c8xhyS83 OTYBJY1gBfxSHn9zj3auTKYdxhOpzzopRn0uUT1M9iGYfy71ZJuL97dw1gOfkfOL /RbBQwut9RJJWRYlYhIHKc9cvnB21pO76OLHkPHmslNUBX67q4fS3gUuhMbAroq2 37gmW6grIMIvY6s4XgMJ =vXUT -----END PGP SIGNATURE-----
Luigi Rosa <lists@luigirosa.com> wrote:
I was expecting an UPDATE...WHERE but I got this:
INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON DUPLICATE KEY UPDATE lastlogin='1413647370'
This is not very useful if I want a
lastloginfield of themailboxtable updated on every login.
This SQL query looks correct to me. It INSERTs a new row for a username if one does not already exist but UPDATEs it if there is already such a row.
This is the normal and advised way of doing this specific task in an atomic way by using the "ON DUPLICATE" syntax of SQL.
Grüße, Sven.
-- Sigmentation fault. Core dumped.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Sven Hartge wrote on 18/10/2014 18:07:
This SQL query looks correct to me. It INSERTs a new row for a username if one does not already exist but UPDATEs it if there is already such a row.
My fault: I didn't know this SQL syntax.
In fact the field has been correctly updated.
Thank you Sven!
Ciao, luigi
/ +--[Luigi Rosa]-- \
Every government is a parliament of whores. The trouble is, in a democracy, the whores are us. --P. J. O'Rourke -----BEGIN PGP SIGNATURE----- Version: GnuPG v1
iQIcBAEBAgAGBQJUQpJBAAoJEO5WT/qgw4yKryAP/2237FGyMYw3tEq2HdN2JYDE QCxBRyUAVQSnIvJZwPhJxlFd2O3nkIrDEirpXUh4W5AhDSrTt517tiykCbQZzWz8 XN6t52PbAR4MdcdJu69c7fIzm1LfhCquPAJz+MLOOvBPXwzP2fOEL8tWWpKHAQeI uTktIzHGYEaHgX3V+LQ6TTCtofhi7oved5AaFiLYr42pYJSjKTlLvYa3Ycx3n8iz kF1QuHpIaOqO55CIzrc7LYgNAUhC5SKpxLlGcjYgHvE/q6Cet2CIIE5DWF/UCn7I PHcpLeEQS2YavfCWtKhAyb5qobP4zQFKG5lb09+BLIugZX/P4w1KYVBaNxfaDYRe X5hnat0GcMS7NQIvVLXlqVp/GLoG/AIynu+bfSZtpL00MznI5j6N+BvJZ4ygTTvL huIinr/6WaQvF7Gn+J6f741LqNDbRjajDgKlET3wVF25QfhVP/KNnNoSuZZ7LHnF b7ToIBtXBebGmrPX9GQuorYXTNTgZa6XrPACk/+y4YWp8lWxkWHoW3SUwqm79JS8 bKf3+xj6YPaFREVBbuzDkueaLrncFsWUuthxIO3JX5taOicplZf9GrDE6fRyF5Ew pdtGmxoFhhxV1SNPAZB6G5YciJrrMdaKYcHHxxQnKbTBVEHw4A98AcyGZHKCOwsP Wuq4sDEISdOxrk0Gp39T =hgL9 -----END PGP SIGNATURE-----
Luigi Rosa <lists@luigirosa.com> wrote:
Sven Hartge wrote on 18/10/2014 18:07:
This SQL query looks correct to me. It INSERTs a new row for a username if one does not already exist but UPDATEs it if there is already such a row.
My fault: I didn't know this SQL syntax.
It is incredibly usefull. Otherwise you would have to do a SELECT first to decide if you want to INSERT or UPDATE as a second query and this creates a race condition if another process is able to do its INSERT after you decided there is no existing key because at the time if your first SELECT there was in fact none.
Grüße, Sven.
-- Sigmentation fault. Core dumped.
Zitat von Luigi Rosa <lists@luigirosa.com>:
Gedalya wrote on 18/10/2014 17:36:
The code looks at shared/$last_login_key, so shared/last-login/username, but your map says /shared/last-login/$user, note the leading slash! That's probably all it is.
BINGO!
Now that we killed the mosters of this level, let's move to the next
level.....Welcome to the SQL query level!
I was expecting an UPDATE...WHERE but I got this:
INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON DUPLICATE KEY UPDATE lastlogin='1413647370'
This is not very useful if I want a
lastloginfield of themailboxtable updated on every login.Ciao, luigi
/ +--[Luigi Rosa]-- \
For every human problem, there is a neat, simple solution; and it is always wrong.
anybody knows how this can be achieved with postgresql, because 'ON
DUPLICATE KEY UPDATE' syntax is mysql specific
On 10/19/2014 04:10 AM, Michael Cramer wrote:
Zitat von Luigi Rosa <lists@luigirosa.com>:
Gedalya wrote on 18/10/2014 17:36:
The code looks at shared/$last_login_key, so shared/last-login/username, but your map says /shared/last-login/$user, note the leading slash! That's probably all it is.
BINGO!
Now that we killed the mosters of this level, let's move to the next level.....
Welcome to the SQL query level!
I was expecting an UPDATE...WHERE but I got this:
INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON DUPLICATE KEY UPDATE lastlogin='1413647370'
This is not very useful if I want a
lastloginfield of themailboxtable updated on every login.Ciao, luigi
/ +--[Luigi Rosa]-- \
For every human problem, there is a neat, simple solution; and it is always wrong.
anybody knows how this can be achieved with postgresql, because 'ON DUPLICATE KEY UPDATE' syntax is mysql specific The user is not causing the 'ON DUPLICATE KEY UPDATE', that's the dovecot mysql driver. Dict is an abstraction layer. It will just work if you do pretty much the same config but with the postgres driver.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Sun, 19 Oct 2014, Michael Cramer wrote:
Gedalya wrote on 18/10/2014 17:36:
The code looks at shared/$last_login_key, so shared/last-login/username, but your map says /shared/last-login/$user, note the leading slash! That's probably all it is.
BINGO!
Now that we killed the mosters of this level, let's move to the next level.....
I was expecting an UPDATE...WHERE but I got this:
INSERT INTO mailbox (lastlogin,username) VALUES ('1413647370','username') ON DUPLICATE KEY UPDATE lastlogin='1413647370'
anybody knows how this can be achieved with postgresql, because 'ON DUPLICATE KEY UPDATE' syntax is mysql specific
http://wiki2.dovecot.org/Quota/Dict see the section about "If you're using PostgreSQL, you'll need a trigger"
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux)
iQEVAwUBVESzD3z1H7kL/d9rAQLX8wf/WoO/J8GSf5ht2ZH7tV731jxvjd29epJr LzyqJ9DdwAt/4doR7/RK+3TKYfEzvkP7wkzOr7j6IkWhM9R2NXdXRLpYmKYG0GOb oPueNHQ3ibAcGgYHNL14UJve/H+laxJXOWYhlxM8CgR2IT+EBeJIGiHmVWjUm3DL f1Wxwzf4Ozdl0nRwZGr0nToVklaDabB9wdNNdBno+42UcbdENZdloWbAEs/2HjfA 0ckbJ4BXf+SR/TgeRmMPXMIN7/n4iIJnikTQuuE0MhV5DgdTfY0Xa1p+sE0Q1qSO pxC7FxYaaI+Yxk9q/NT8HJRwMbx3T0GQ0zyh8Bo0DH/v1QSvbgtJrg== =nZET -----END PGP SIGNATURE-----
participants (5)
-
Gedalya
-
Luigi Rosa
-
Michael Cramer
-
Steffen Kaiser
-
Sven Hartge