Re: [vchkpw] Re: Mysql table
Tom Collins wrote: I think we'll get better domain alias support if you pull column `domain` out of table `Domains` and add it to the table `domain_alias`. I agree. This makes it easy to make sure domain names are unique. I'm not sure we need to have a master and alias -- the names can all be equal with this setup. I think we still need it. The 'real' domain is the one that has a file system behind it, and we need to know which one it is. I suggest a pointer field real_domain containing the domain_name_id of the real domain. [Isn't there something in mailing lists that ties the list to the real domain, that makes renaming domains hard?] We can save a little bit of space by storing the path to the domain directory in the domain table, and only the path from there down in the users table. The dir field in the users table should be renamed to user_dir. I think domain_alias should be renamed to domain_names. Is there a better name for domains now? You might even want to have the limits fields go into a separate table, with one entry in that table declared default. That way, domains with default entries can all point to that single row in the limits table. How about we remove the NOT NULL constraint from each of the limit values, and if they are null take the value from the 'domain' _system.default. This way if you leave any value NULL, the default is filled in from the database in a single query: SELECT b.domain as domain, c.domain as real_domain, if( a.ip_address IS NULL, e.ip_address, a.ip_address ) as ip_address, if( a.max... IS NULL, e.max..., a.max... ) as max..., ... FROM domain_names a LEFT JOIN domains b USING( domain_id ) LEFT JOIN domain_names c ON c.domain_id = b.real_domain LEFT JOIN domain_names d ON d.domain = '_system.default' LEFT JOIN domains e ON e.domain_id = d.domain_id WHERE a.domain = '$TargetDomain'; Rick Macdougall wrote: I'd add a key on timestamp. [in the relay table] OK. I also added a sequence field to the valias table. Here is a pseudo-diff showing the changes: CREATE TABLE Domains( domain_idbigint not null auto_increment, real_domain bigint, - domain char(195), // TC + domain_dir char(100), // RW ip_address char(18)NOT NULL DEFAULT '0.0.0.0', maxpopaccounts INT(10) NOT NULL DEFAULT -1 maxaliases INT(10) NOT NULL DEFAULT -1, maxforwards INT(10) NOT NULL DEFAULT -1, maxautorespondersINT(10) NOT NULL DEFAULT -1, maxmailinglists INT(10) NOT NULL DEFAULT -1, diskquotaINT(12) NOT NULL DEFAULT 0, maxmsgcount INT(12) NOT NULL DEFAULT 0, defaultquota INT(12) NOT NULL DEFAULT 0, defaultmaxmsgcount INT(12) NOT NULL DEFAULT 0, disable_pop TINYINT(1) NOT NULL DEFAULT 0, disable_imap TINYINT(1) NOT NULL DEFAULT 0, disable_dialup TINYINT(1) NOT NULL DEFAULT 0, disable_passwordchanging TINYINT(1) NOT NULL DEFAULT 0, disable_webmail TINYINT(1) NOT NULL DEFAULT 0, disable_relayTINYINT(1) NOT NULL DEFAULT 0, disable_smtp TINYINT(1) NOT NULL DEFAULT 0, disable_spamassassin TINYINT(1) NOT NULL DEFAULT 0, delete_spam TINYINT(1) NOT NULL DEFAULT 0, perm_account TINYINT(2) NOT NULL DEFAULT 0, perm_alias TINYINT(2) NOT NULL DEFAULT 0, perm_forward TINYINT(2) NOT NULL DEFAULT 0, perm_autoresponder TINYINT(2) NOT NULL DEFAULT 0, perm_maillistTINYINT(4) NOT NULL DEFAULT 0, perm_quota TINYINT(2) NOT NULL DEFAULT 0, perm_defaultquotaTINYINT(2) NOT NULL DEFAULT 0, cur_usersint, level_curint, level_maxint, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int, level_index1 int, level_index2 int, the_dir char(160), primary key( domain_id ), key( ip_address ), ); - CREATE TABLE domain_alias( // RW + CREATE TABLE domain_names( // RW -domain_alias_id bigint not null auto_increment, // RW +domain_name_id bigint not null auto_increment, // RW domain_idbigint not null, - aliaschar(195), // TC + domain char(195), // TC - primary key( domain_alias_id ), // RW + primary key( domain_name_id ),// RW foreign key( domain_id ) REFERENCES domains( domain_id ),
Re: [vchkpw] Re: Mysql table
Rick Widmer wrote: I wasn't quite ready for this, but it has been on my list for a long time... since it has come up, let me throw up this database structure for comment. I have reduced the number of tables (in a full installation) and made it relational.- CREATE TABLE relay( ip_addr char(18) not null, timestampdatetime, primary key( ip_addr ) ), No coffee yet so this may well be out in left field, but on large systems when a clear open smtp is run I don't think you'll want to be searching through 20K records in the table to see who to expire. I'd add a key on timestamp. Rick
[vchkpw] Re: Mysql table
Tom Collins wrote: On Sep 20, 2007, at 12:56 PM, Rick Widmer wrote: If I remember right, speed was the reason for separate tables, but testing showed it was not faster. I think the single table works better because all your mail users are accessing the same table, and its indexes so they stay loaded all the time. If you use separate tables it is always thrashing the cache as different files need to be accessed. If we were really looking for speed, we could move to a ng (next generation) table format that was more relational. Provide tools to migrate from the old to the new for those people who only access the data through vpopmail's APIs. Continue to support the old method for people who have home-grown apps that access the data. The domains should be in a table of their own, and the users table should index the domains table. Having an index in the users table on an int (and the resulting size savings) would be measurable. The biggest change would be updating the selects and inserts in the code. Not a huge change -- just a join between the tables. Something to consider, and perhaps discuss further (on this list or vpopmail-devel). I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R.
[vchkpw] Re: Mysql table
Rick Macdougall wrote: Robin Bowes wrote: Tom Collins wrote: I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R. How large ? 5k, 15k, 100k ? What sort of size of database are folk using in the real world? How many users? What's the most common no. of users? R.
Re: [vchkpw] Re: Mysql table
Robin Bowes wrote: Tom Collins wrote: I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R. How large ? 5k, 15k, 100k ? Rick
Re: [vchkpw] Re: Mysql table
Robin Bowes wrote: Rick Macdougall wrote: Robin Bowes wrote: Tom Collins wrote: I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R. How large ? 5k, 15k, 100k ? What sort of size of database are folk using in the real world? How many users? What's the most common no. of users? R. I'm guessing 200 - 2000 but that's based on the servers I manage for clients (around 20 or so). The biggest installation I manage is around 18k users and I could export that with munged domain names / passwords if you wanted something that big. Rick
[vchkpw] Re: Mysql table
Rick Macdougall wrote: Robin Bowes wrote: What sort of size of database are folk using in the real world? How many users? What's the most common no. of users? R. I'm guessing 200 - 2000 but that's based on the servers I manage for clients (around 20 or so). The biggest installation I manage is around 18k users and I could export that with munged domain names / passwords if you wanted something that big. It's hardly worth bothering with for ~2000 users. The difference in memory/disk use would be minimal. An 18k user table would be interesting. R.
Re: [vchkpw] Re: Mysql table
I wasn't quite ready for this, but it has been on my list for a long time... since it has come up, let me throw up this database structure for comment. I have reduced the number of tables (in a full installation) and made it relational.- CREATE TABLE Domains( domain_idbigint not null auto_increment, domain char(195), ip_address char(18)NOT NULL DEFAULT '0.0.0.0', maxpopaccounts INT(10) NOT NULL DEFAULT -1, maxaliases INT(10) NOT NULL DEFAULT -1, maxforwards INT(10) NOT NULL DEFAULT -1, maxautorespondersINT(10) NOT NULL DEFAULT -1, maxmailinglists INT(10) NOT NULL DEFAULT -1, diskquotaINT(12) NOT NULL DEFAULT 0, maxmsgcount INT(12) NOT NULL DEFAULT 0, defaultquota INT(12) NOT NULL DEFAULT 0, defaultmaxmsgcount INT(12) NOT NULL DEFAULT 0, disable_pop TINYINT(1) NOT NULL DEFAULT 0, disable_imap TINYINT(1) NOT NULL DEFAULT 0, disable_dialup TINYINT(1) NOT NULL DEFAULT 0, disable_passwordchanging TINYINT(1) NOT NULL DEFAULT 0, disable_webmail TINYINT(1) NOT NULL DEFAULT 0, disable_relayTINYINT(1) NOT NULL DEFAULT 0, disable_smtp TINYINT(1) NOT NULL DEFAULT 0, disable_spamassassin TINYINT(1) NOT NULL DEFAULT 0, delete_spam TINYINT(1) NOT NULL DEFAULT 0, perm_account TINYINT(2) NOT NULL DEFAULT 0, perm_alias TINYINT(2) NOT NULL DEFAULT 0, perm_forward TINYINT(2) NOT NULL DEFAULT 0, perm_autoresponder TINYINT(2) NOT NULL DEFAULT 0, perm_maillistTINYINT(4) NOT NULL DEFAULT 0, perm_quota TINYINT(2) NOT NULL DEFAULT 0, perm_defaultquotaTINYINT(2) NOT NULL DEFAULT 0, cur_usersint, level_curint, level_maxint, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int, level_index1 int, level_index2 int, the_dir char(160), primary key( domain_id ), unique key( domain ) key( ip_address ), ); CREATE TABLE domain_alias( domain_alias_id bigint not null auto_increment, domain_idbigint not null, aliaschar(195), primary key( domain_alias_id ), foreign key( domain_id ) REFERENCES domains( domain_id ), unique key( alias ) ); CREATE TABLE relay( ip_addr char(18) not null, timestampdatetime, primary key( ip_addr ) ), CREATE TABLE users( user_id bigint not null auto_increment, domain_idbigint not null, name passwd char(40), uid int, flagsint, comment char(48), dir char(160), shellchar(20), passwd char(16), remote_ipchar(18), rate_limit int, active char(1), created datetime, timestampbigint default 0 NOT NULL, primary key( valias_id ), foreign key( domain_id ) REFERENCES domains( domain_id ), unique key ( name, domain_id ) ); CREATE TABLE valias( valias_idbigint not null auto_increment, user_id bigint not null, name char(?), aliaschar(195), primary key( valias_id ), foreign key( user_id ) REFERENCES users( user_id ), unique key( name ) ); I use bigint keys because the last time I looked (admittedly it was a long time ago) if you had 2 bigints at the beginning of a table, and the value in the second one was small, those two fields were bigger than the data overwritten when a record is deleted from the table. This increases the amount of data you can recover until the record gets overwritten. (I have had to recover deleted records the by searching through the database files before...) If this happens, MANY_DOMAINS, CLEAR_PASS and IP_ALIAS_DOMAINS will all go away. If we need to enable/disable clear pass it will be an option in the file where we set the database open info. Also, I want to change query construction from creating defines to building them into strings. I would also like to remove all data base structure changes from the code, and provide a sql script to create the database. The vopomail database user should be able to operate with no more than INSERT, DELETE, UPDATE, and SELECT rights to the database.
Re: [vchkpw] Re: Mysql table
On Sep 21, 2007, at 4:32 PM, Rick Widmer wrote: Comments? I think we'll get better domain alias support if you pull column `domain` out of table `Domains` and add it to the table `domain_alias`. Domain name to domain on the system is a many to one relationship, so the name should be in a separate table. I'm not sure we need to have a master and alias -- the names can all be equal with this setup. I mention it because it reduces lookups to a single query (or at least a simpler query). Instead of needing to check for the domain name in one of two tables, you just check one. Apologies if this SQL has any MySQL-flavored syntax... SELECT `user`.`password`, `user`.`flags` FROM `domain_name`, `domain`, `users` WHERE `domain_name`.`domain_id` = `domain`.`domain_id` AND `user`.`domain_id` = `domain`.`domain_id` AND `domain_name`.`name` = '%s' AND `user`.`name` = '%s' You might even want to have the limits fields go into a separate table, with one entry in that table declared default. That way, domains with default entries can all point to that single row in the limits table. -- Tom Collins - [EMAIL PROTECTED] Vpopmail - virtual domains for qmail: http://vpopmail.sf.net/ QmailAdmin - web interface for Vpopmail: http://qmailadmin.sf.net/
[vchkpw] Re: MySQL logging
On Tuesday, August 24, 2004 at 08:42 (which was Tuesday, August 24, 2004 at 14:42 where I am) Richard A. Secor wrote: Have you thought of writing a script in perl and run it in the crontab? I have (although not specifically in perl), but that would require directly interfacing with MySQL and thus exposing the user/password combination in yet another place. This is why I was looking for a more 'formal' way to solve this. -- Greetings, Maurice PS: please trim quotes to context.
Re: [vchkpw] Re: MySQL logging
On Aug 24, 2004, at 6:19 AM, Maurice Snellen wrote: Have you thought of writing a script in perl and run it in the crontab? I have (although not specifically in perl), but that would require directly interfacing with MySQL and thus exposing the user/password combination in yet another place. If the script is owned by vpopmail (or root), and run in the vpopmail (or root) crontab, then it would have permissions to read the ~vpopmail/etc/vpopmail.mysql file to get the password information. Even if you embedded it in the script, you could make the file readable only by root. -- Tom Collins - [EMAIL PROTECTED] QmailAdmin: http://qmailadmin.sf.net/ Vpopmail: http://vpopmail.sf.net/ Info on the Sniffter hand-held Network Tester: http://sniffter.com/
[vchkpw] Re: mysql gone away. Please help a noob.
Hi jon kutassy writes: I took the windoze approach and restarted mysql, and its all working fine!! Which has the same effect as the suggestion in an earlier reply: flush privileges, but results in a longer outage of mysql. An alternative to doing flush privileges from the mysql client is to do a mysqladmin flush-privileges (or mysqladmin reload which means the same thing but is less typing). And you didn't use the Windows approach. The Windows approach is to reboot the computer any time you make a change. In fact, the Windows approach is to force a reboot upon you any time you make even a trivial change to the configuration. I suspect the day is not far off when Windows will report You have changed a critical configuration flag by toggling the caps-lock key. Windows must reboot now. Followed in a future release by Windows has detected that the position of the mouse has changed and must reboot. (oh, my mistake - that last feature has been in Windows all along, it just fails to work most of the time and when it does work you think it's a random crash). BTW, does anyone know if Microsoft-owned Hotmail is still using qmail despite Bill's repeated attempts to force them to switch to Exchange? :) -- Paul Allen Softflare Support
[vchkpw] Re: mysql migration
Replicate the MySQL servers. Worked for me when I was changing servers. Worked like a charm. -John Remo Mattei writes: Hi guys I have an old mail server with vpopmail 5.3.12 and I am building a new one more powerful which I want to migrate all users to. I have dump the database mysqldump and imported in the new one however it did not import the users :-) my plan was to create the domain and then copy all the domain info from the old to new. I have done it with cdb and work great I even have it done from cdb to mysql and worked fine but mysql to mysql having a fit :-) I have even copy the data dir from my old mysql to my new one but still do not see the users. Any suggestions are appreciated. Thanks, Remo Mattei Network Security Engineer cell 801-209-8554 email [EMAIL PROTECTED] --Powered by Linux. My other OS is your Windows box.
Re: MySQL APOP
Support for APOP has been depreciated in 5.0 Ken Jones On Sun, 2001-11-25 at 05:08, Joe wrote: Hi. I use vpopmail 5.0. I think this is cool software. My configuration options are like follows, --enable-roaming-users=y --enable-relay-clear-minutes=30 --enable-tcpserver-file=/etc/tcp.smtp --enable-hardquota=5000 --enable-ucspi-dir=../ucspi-tcp-0.88 --enable-apop=y --enable-apop-file=/etc/apop-secrets --enable-mysql=y --enable-sqlincdir=/usr/local/include/mysql --enable-sqllibdir=/usr/local/lib/mysql --enable-sqllibs=libmysqlclient.a but, APOP cannot be enable. Though I made virtualdomains $ vadddomain my.domain -a , hashed passwords was housed in pw_passwd column of MySQL's vpopmail table. If it is right action, where is non-hashed password for APOP ? Or else is MySQL not compatible with APOP ?
Re: mysql crypt seed
prehaps i should be more specific. im using a php4 script to change the password. basically i do a: $oldone = crypt($oldpasswd,$emailinfo[0]); and then compare that crypt to the crypt in the vpopmail database, vpopmail table, pw_passwd where pw_name='$emailinfo[0]' and pw_domain='$emailinfo[1]'. emailinfo is an array of the name and domain of the email address. here is what php4.net says (abreviated) about crypt: crypt() will return an encrypted string using the standard Unix DES encryption method. Arguments are a string to be encrypted and an optional two-character salt string to base the encryption on. i found where vpopmail crypts the password: salt[0] = randltr(); salt[1] = randltr(); salt[2] = 0; tmpstr = crypt(newpasswd,salt); i guess i answered my own question. but anyone have any other ideas how to do this? its feasible to use clear passwords in the database, or to define the salt as something as opposed to something random and recompile and use that to crypt the password... any other ideas? what about security concerns? charlie - Original Message - From: Ken Jones [EMAIL PROTECTED] To: Charlie Chrisman [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 17, 2001 11:28 PM Subject: Re: mysql crypt seed On Wed, 2001-10-17 at 20:52, Charlie Chrisman wrote: what is the seed of the crypt of the password when using mysql authentication? basically i want to write a quick, small script that will allow users to change their password. thats all... it doesn't use the mysql crypt/password functions. It uses the standard (as of now) unix crypt() function. Read through the vpasswd() function. You can find the code which creates the password. It's standard stuff. You could always exec the vpasswd command. Or link in a program that calls the vpasswd() api. Or copy the code from the library and paste it into your program. Ken Jones
Re: Mysql Support
--- duncan [EMAIL PROTECTED] wrote: I was looking at his toaster install and it looks very handy. I was I agree. Very handy indeed. It also explains how to use tcpserver by virtue of showing some real examples. On their own, DJB's docs are too obtuse for me 8(. wondering if the patch was BSD specific at all, or if it worked on any box. It should work on any *nix (linux in my case) becuase its just the code right, and the qmail code works? anyone have any experience with this patch and/or his toaster instructions. His instructions use the FreeBSD ports collection (a bit like Linux RPMS) and some packages (a bit like Linux RPM) to install or prepare some of the bits and pieces: From http://matt.simerson.net/computing/qmail.toaster.shtml # pkg_add -r ispell # pkg_add -r gdbm # pkg_add -r autorespond # pkg_add -r setquota # cd /usr/ports/mail/qmail; make enable-qmail clean # cd /usr/ports/mail/qmailanalog; make install clean and also to create various users and groups. # pw groupadd -n vchkpw -g 90 # pw useradd -n vpopmail -u 90 -g 90 -c Vpopmail-Master -d /usr/home/vpopmail -s /sbin/nologin # pw groupadd -n www -g 91 # pw useradd -n www -u 91 -g 91 -c https -d /usr/local/www -s /sbin/nologin Also, fetch is standard with FreeBSD, but not standard *NIX. Apart from that, patching and building seems normal. It was simple to convert the toaster into a shell script, using sed or echo to hack or create config files instead of pico/vi/emacs/whatever. I have not used it completely successfully, but that is because I was trying to do the same install but with no MySQL support, intending to use only LDAP. So my bad. However, courier-imap is a PITA and will not co-operate. I find the docs impenetrable at the moment so I'm sitting back for a couple of days while I decide whether I should bend over and use MySQL or try again ;). Adam. thx Adam Nealis wrote: --- Che Stevens [EMAIL PROTECTED] wrote: I was reading that i needed to patch tcpserver with this Matt Simerson patch in order for it to read from the DB instead of compiling an new cdb each time. I need to know how to make this patch work. ANyone have any experiance with it. Thanks. If you mean how do I install the patch?, pop over to here: http://matt.simerson.net/computing/qmail.toaster.shtml and look for Install UNIX Client-Server Program Interface for TCP. Adam. Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie
Re: mysql error when adding a domain using vpopmail 5.0rc5
did you edit vmysql.h before you ran a make? On Wednesday, October 3, 2001, at 02:28 PM, duncan wrote: hello- im trying to use vpopmail and i got it installed ok (or so it seems) and built with these options: Current settings --- vpopmail directory = /home/vpopmail uid = 519 gid = 520 ip alias = OFF --enable-ip-alias-domains=n (default) address extentions = ON --enable-qmail-ext=y roaming users = ON --enable-roaming-users=y tcpserver file = /home/vpopmail/etc/tcp.smtp open_smtp file = /home/vpopmail/etc/open-smtp user quota = OFF --enable-hardquota=n default auth module = mysql --enable-mysql=y mysql replication = OFF --enable-mysql-replication=n default table optimization = many domains --enable-many-domains=y default system passwords = OFF --enable-passwd=n default file locking = ON --enable-file-locking=y default file sync = ON --enable-file-sync=y default auth logging = ON --enable-auth-logging=y mysql logging = OFF --enable-mysql-logging=n default mysql clear passwd = ON --enable-clear-passwd=y valias processing = ON --enable-valias=y pop syslog = show only failure attempts --enable-logging=e default default domain = sodatrain.com --enable-default-domain=test.com auth inc = -I/usr/local/include/mysql auth lib = -L/usr/local/lib/mysql -lmysqlclient -lz ive got mysql Ver 11.15 Distrib 3.23.39 I was trying to add a domain... and it gave these errors: vmysql: sql error: MySQL server has gone away ps -ax | grep mysql shows mysql still running. here is the sequence of the process: [root@chaos bin]# ./vadddomain test.com Please enter password for postmaster: enter password again: could not connect to mysql update server vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away vmysql: sql error: MySQL server has gone away Error: Unable to chdir to vpopmail/users directory thanks for your help! -- - Sean P. Scanlon perl -e 'print pack(h*, 3707370426c6575646f647e2e65647), \n' -
Re: MySQL question
On Mon, 2001-10-01 at 15:54, Brandon Ramirez wrote: Just for future reference, how hard would it be to convert the data in the cdb files into a MySQL table? Right now, I have no reason to stop using cdb, it works great and I don't want to mess with anything. But in the future when my company can afford to get more server boxes online, I'd like to have web, database, and DNS/email servers all separate to balance the load. I would like to be able to make a script that integrates into our account manager software running on the web server that adds or removes users from the database. That way nobody has to connect to the mail server to update vpasswd files. So in the future, would it be complicated to take all of the cdb files and put the data into a database? Is there a way to convert them all at once, or will I have to copy the data myself? Just parse out the fields in the vpasswd file and insert them with SQL statements into the vpopmail table. Ken Jones
Re: mysql setup
Where is the 4.10.31 version located? -- IDG New MediaEinar Bordewich Development Manager Phone: +47 2336 1420 E-Mail: eibo(at)newmedia.no Lat: 59.91144 N Lon: 10.76097 E - Original Message - From: Ken Jones [EMAIL PROTECTED] To: J.M.Roth [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, August 04, 2001 8:13 AM Subject: Re: mysql setup Hi JM The 4.10.31 version has the completed vconversion program. It was temporarily turned off when the mysql api changed somewhere around 4.10.14. We finally got caught up on the todo list to update vconvert. --enable-valias=y tells vdelivermail to look up aliases using the valias api function calls. For mysql sites the aliases are stored in a mysql table. Mysql people like this because they can write sql applications to update aliases. For the cdb and all other modules the valias api uses the standard .qmail files. --enable-auth-logging=y Some sites want to lookup the time a user last authenticated on the system. This option turns that feature on. Uses? sites can age email accounts, vdeloldusers is an example of ageing/deleting un used email accounts. --enable-mysql-replication=y This is complicated. The short answer it makes the vpopmail api (and all applications using the api) work with mysql's replication feature. We used it to place a read only live copy of mysql on each email server in a cluster. On 03 Aug 2001 22:12:39 +0200, J.M.Roth wrote: hi, I tried installing 4.10.30 with mysql authorization which seems to have worked alright but the vconvert util is dead It doesn't output a thing. Neither does it convert anything... Also, what are the following configure options good for in detail? --enable-valias --enable-auth-logging --enable-mysql-replication BTW configure --help still displays this: --enable-sqwebmail-pass=y|n Turn off support for sqwebmail passwords (y) or off (n) OFF or OFF Thanks, the new version has better information. --enable-sqwebmail-pass=y|n Turn on support for sqwebmail passwords (y) or off (n) default is no. For sqwebmail-3.0 and above use default no Thanks for any help. J.M.Roth
Re: mysql setup
I believe that Ken in a earlier post said that vconvert is disabled for now. Mark On 03 Aug 2001 22:12:39 +0200, J.M.Roth wrote: hi, I tried installing 4.10.30 with mysql authorization which seems to have worked alright but the vconvert util is dead It doesn't output a thing. Neither does it convert anything... Also, what are the following configure options good for in detail? --enable-valias --enable-auth-logging --enable-mysql-replication BTW configure --help still displays this: --enable-sqwebmail-pass=y|n Turn off support for sqwebmail passwords (y) or off (n) OFF or OFF Thanks for any help. J.M.Roth
Re: mysql setup
Hi JM The 4.10.31 version has the completed vconversion program. It was temporarily turned off when the mysql api changed somewhere around 4.10.14. We finally got caught up on the todo list to update vconvert. --enable-valias=y tells vdelivermail to look up aliases using the valias api function calls. For mysql sites the aliases are stored in a mysql table. Mysql people like this because they can write sql applications to update aliases. For the cdb and all other modules the valias api uses the standard .qmail files. --enable-auth-logging=y Some sites want to lookup the time a user last authenticated on the system. This option turns that feature on. Uses? sites can age email accounts, vdeloldusers is an example of ageing/deleting un used email accounts. --enable-mysql-replication=y This is complicated. The short answer it makes the vpopmail api (and all applications using the api) work with mysql's replication feature. We used it to place a read only live copy of mysql on each email server in a cluster. On 03 Aug 2001 22:12:39 +0200, J.M.Roth wrote: hi, I tried installing 4.10.30 with mysql authorization which seems to have worked alright but the vconvert util is dead It doesn't output a thing. Neither does it convert anything... Also, what are the following configure options good for in detail? --enable-valias --enable-auth-logging --enable-mysql-replication BTW configure --help still displays this: --enable-sqwebmail-pass=y|n Turn off support for sqwebmail passwords (y) or off (n) OFF or OFF Thanks, the new version has better information. --enable-sqwebmail-pass=y|n Turn on support for sqwebmail passwords (y) or off (n) default is no. For sqwebmail-3.0 and above use default no Thanks for any help. J.M.Roth
Re: MySQL passwords..
Steve Fulton wrote: I switched on the MySQL clear-text password option, and while I can now see there is a clear text password in the table, there is also an encrypted. So, my question is simple: Which does vpopmail actually read? Is it possible to tell it to read one, but not the other? for authentication requests vpopmail uses the encrypted password field. When the password is changed (or new user added) the clear text password is stored in the clear text field then encrypted and stored in the encrypted password field. The clear text password field is display only. Ken Jones inter7
Re: MySQL -vs- CDB [Conversion]
on 1/10/01 2:06 PM, Mike Rogers at [EMAIL PROTECTED] spake: I am currently running a MySQL vpopmail, as I have been for about a year and a half now. Lately, I have experienced above normal load on my MySQL server, and have considered using CDB instead, as that way my mail system does not fall to it's knees when someone does a large sort on my MySQL server. Will I notice any real disadvantage in converting to CDB from MySQL? Is it worth it? High-reliability is important, so I don't want to have to worry about flaws in the MySQL daemon. I realize I would have to recompile vpopmail/sqwebmail/qmailadmin, but it may be worth it. Also, is there a conversion utility that will convert from MySQL (small tables) to CDB? Aside from some of the new features available with the MySQL module, the real difference between cdb and MySQL is the speed of updates. If you have more than about 7-10k accounts, cdb updates get really slow. And it gets worse with more accounts. This isn't a problem with MySQL. On the other hand, cdb lookups are slightly faster. So, if you don't have too many accounts on each domain, you may want to stick with cdb format. vconvert can convert either direction: cdb-mysql or mysql-cdb Cheers, Bill Shupp
Re: MySQL -vs- CDB [Conversion]
on 1/10/01 2:41 PM, Mike Rogers at [EMAIL PROTECTED] spake: That even sounds good enough. Generally, my domains contain between 2 and 30 pop accounts each. A few exceptions, but in general, CDB would have faster lookups, would be independent of MySQL, and easily moveable it seems. I think I will make that change... It sounds promising. I am currently running the MySQL module, and I want to move to CDB to speed it up. Also it would avoid the waiting for connections. Sounds like a good plan to me- is the upgrade easy? Just run vconvert and recompile vpopmail? (then qmailadmin/sqwebmail) Pretty much. I recommend a test run on a development box first, if you can. If not, just have good backups, including the mysql data. Cheers, Bill
Re: MySQL -vs- CDB [Conversion]
Will I notice any real disadvantage in converting to CDB from MySQL? Is it worth it? High-reliability is important, so I don't want to have to worry about flaws in the MySQL daemon. This is slightly OT but it was raised a question for me: If you are using mysql replication would (or is it already?) it be possible to get vpopmail to query the update (master) server if the local read-only server is not available? As well as adding redundancy, could it also be used to speed up provisioning delays i.e if user is not found is local database , could the master be queried as well? I'm just in early testing of vpopmail for use in an ISP enviroment (and results are promising, the software is v good) and haven't _really_ looked at the mysql replication features yet , so forgive me if this question in already in the docs or mailing lists. Donal
Re: mysql permitions
what about user permitions? - Original Message - From: Ken Jones [EMAIL PROTECTED] To: alexus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, June 18, 2001 6:26 PM Subject: Re: mysql permitions alexus wrote: i dont want to use root for vpopmail so i would like to find out which privilages does new vpopmail user has to have in order to perform all task secufully just like root All for permissions for it's database, if the database already exists. Ken Jones
Re: mysql permitions
user whatever name u want needs to have all permissions for vpopmail database ..as already said by Ken.. regards dushyanth what about user permitions? - Original Message - From: Ken Jones [EMAIL PROTECTED] To: alexus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, June 18, 2001 6:26 PM Subject: Re: mysql permitions alexus wrote: i dont want to use root for vpopmail so i would like to find out which privilages does new vpopmail user has to have in order to perform all task secufully just like root All for permissions for it's database, if the database already exists. Ken Jones -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com
Re: mysql permitions
such as drop privilages? grant privilages? references privilegas? index privilages and alter privilages? ... i think not... i understand that it needs select,insert,update,delete and create... but that's nto exactly what i was asking ... my question is what's user permtion needs to be not what's user permition for database must be there are two setting in mysql for each user for user itself and for which db can this user access you talking about second setting and i'm talking about first one... - Original Message - From: Dushyanth Harinath [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 19, 2001 9:14 AM Subject: Re: mysql permitions user whatever name u want needs to have all permissions for vpopmail database ..as already said by Ken.. regards dushyanth what about user permitions? - Original Message - From: Ken Jones [EMAIL PROTECTED] To: alexus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, June 18, 2001 6:26 PM Subject: Re: mysql permitions alexus wrote: i dont want to use root for vpopmail so i would like to find out which privilages does new vpopmail user has to have in order to perform all task secufully just like root All for permissions for it's database, if the database already exists. Ken Jones -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com
Re: mysql permitions
ok..now i got u... BTW i dint give any permissions for the user in the user table..just created it and assigned and a password and gave permissions in the db table for that user..and it works for me..so i think u dont need any user permissions. regards dushyanth such as drop privilages? grant privilages? references privilegas? index privilages and alter privilages? ... i think not... i understand that it needs select,insert,update,delete and create... but that's nto exactly what i was asking ... my question is what's user permtion needs to be not what's user permition for database must be there are two setting in mysql for each user for user itself and for which db can this user access you talking about second setting and i'm talking about first one... - Original Message - From: Dushyanth Harinath [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 19, 2001 9:14 AM Subject: Re: mysql permitions user whatever name u want needs to have all permissions for vpopmail database ..as already said by Ken.. regards dushyanth what about user permitions? - Original Message - From: Ken Jones [EMAIL PROTECTED] To: alexus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, June 18, 2001 6:26 PM Subject: Re: mysql permitions alexus wrote: i dont want to use root for vpopmail so i would like to find out which privilages does new vpopmail user has to have in order to perform all task secufully just like root All for permissions for it's database, if the database already exists. Ken Jones -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com
Re: mysql permitions
ok, thanks - Original Message - From: Dushyanth Harinath [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, June 19, 2001 9:43 AM Subject: Re: mysql permitions ok..now i got u... BTW i dint give any permissions for the user in the user table..just created it and assigned and a password and gave permissions in the db table for that user..and it works for me..so i think u dont need any user permissions. regards dushyanth such as drop privilages? grant privilages? references privilegas? index privilages and alter privilages? ... i think not... i understand that it needs select,insert,update,delete and create... but that's nto exactly what i was asking ... my question is what's user permtion needs to be not what's user permition for database must be there are two setting in mysql for each user for user itself and for which db can this user access you talking about second setting and i'm talking about first one... - Original Message - From: Dushyanth Harinath [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 19, 2001 9:14 AM Subject: Re: mysql permitions user whatever name u want needs to have all permissions for vpopmail database ..as already said by Ken.. regards dushyanth what about user permitions? - Original Message - From: Ken Jones [EMAIL PROTECTED] To: alexus [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, June 18, 2001 6:26 PM Subject: Re: mysql permitions alexus wrote: i dont want to use root for vpopmail so i would like to find out which privilages does new vpopmail user has to have in order to perform all task secufully just like root All for permissions for it's database, if the database already exists. Ken Jones -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com
Re: mysql permitions
alexus wrote: i dont want to use root for vpopmail so i would like to find out which privilages does new vpopmail user has to have in order to perform all task secufully just like root All for permissions for it's database, if the database already exists. Ken Jones
Re: mysql blues
Hi, http://www.mail-archive.com/vchkpw@inter7.com/msg04765.html check that link out.. i did the same kinda mistake and that's the solution.. regards dushyanth having trouble getting mysql to work (i am quite a newbie to mysql and how it works). i can log into the server using vpopmail user and the password i chose. when logged into mysql as vpopmail user i can create and destroy databases (only ones vpopmail created). here are my compile options and settings after configure. below that is the output when i try add a test domain. everything works great using cdb auth module but i would like to learn to use mysql. any hints would be appreciated. thanx. ./configure --enable-mysql=y --enable-auth-logging=y --enable-logging=p --en able-roaming-users=y --enable-hardquota=31457280 --enable-sqwebmail-pass=y - -enable-tcprules-prog=/usr/local/bin/tcprules --enable-tcpserver-file=/var/v popmail/etc/tcp.smtp vpopmail 4.10.18 Current settings --- vpopmail directory = /var/vpopmail uid = 60 gid = 60 ip alias = OFF --enable-ip-alias-domains=n (default) address extentions = OFF --enable-qmail-ext=n (default) roaming users = ON --enable-roaming-users=y tcpserver file = /var/vpopmail/etc/tcp.smtp open_smtp file = /var/vpopmail/etc/open-smtp user quota = 31457280 -enable-hardquota=31457280 auth module = mysql --enable-mysql=y mysql replication = OFF --enable-mysql-replication=n default table optimization = many domains --enable-large-site=n default system passwords = OFF --enable-passwd=n file locking = ON --enable-file-locking=y default auth logging = ON --enable-auth-logging=y mysql clear passwd = OFF --enable-clear-passwd=n default valias processing = OFF --enable-valias=n pop syslog = show failure attempts with clear text password --enable-logging=p default domain = --enable-default-domain= auth inc = -I/usr/include/mysql auth lib = -L/usr/lib/mysql -lmysqlclient -lz [root@que vpopmail-4.10.18]# vadddomain testing.com Please enter password for postmaster: enter password again: could not connect to mysql update server could not create dir control table create table dir_control ( domain varchar(255) not null, cur_users int, level_cur int, level_max int, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int , level_index1 int, level_index2 int, the_dir varchar(255), primary key (domain) ) could not replace dir control table 2 replace into dir_control ( domain, cur_users, level_cur, level_max, level_start0, level_start1, level_start2, level_end0, level_end1, level_end2, level_mod0, level_mod1, level_mod2, level_index0, level_index1, level_index2, the_dir ) values ( 'dom_60', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '') vsql_getpw: failed select could not create dir control table create table dir_control ( domain varchar(255) not null, cur_users int, level_cur int, level_max int, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int , level_index1 int, level_index2 int, the_dir varchar(255), primary key (domain) ) could not create dir control table create table dir_control ( domain varchar(255) not null, cur_users int, level_cur int, level_max int, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int , level_index1 int, level_index2 int, the_dir varchar(255), primary key (domain) ) could not replace dir control table 2 replace into dir_control ( domain, cur_users, level_cur, level_max, level_start0, level_start1, level_start2, level_end0, level_end1, level_end2, level_mod0, level_mod1, level_mod2, level_index0, level_index1, level_index2, the_dir ) values ( 'testing.com', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '') vsql_getpw: failed select MySQL server has gone away mysql adduser failed Error: Unable to chdir to vpopmail/users directory -- As we enjoy great advantages from the inventions of others, we should be glad of an opportunity to serve others by any invention of ours; and this we should do freely and generously. -- Benjamin Franklin Dushyanth Harinath Archean Infotech Limited Ph No:091-040-3228666,6570704,3228674 http://www.archeanit.com
Re: mysql high availability
This is probably worth talking about now, since we are working on the mysql module for the next several weeks. Using Matt's read/update patch as a starting point the mysql functions are broken up into read-only queries and update queries with separate definitions in vmysql.h for two mysql login connections. One for updates and one for read only. I don't have any clean ideas yet on how to handle the three cases most people are running into: 1) one mysql server for everything 2) replicated mysql system, with one read only server (slave) and one update server (master) 3) a primary server and a backup server Anyone have any ideas? Ken Jones Mark Lane writes: Is the data static? If not it is probably not worth the effort as you would have to send every write to every mysql server. If it is static then sure just tell the program calling mysql to open another mysql server if the first one fails for example something like this. if (!(connection = mysql_connect (host, user, password))) then connection=mysql_connection (host2, user, password); Of Course this will require rewriting the mysql login section of vchkpw. Mark - Original Message - From: Jeffrey Iskandar Ahmad [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 29, 2001 7:26 PM Subject: mysql high availability Hi guys, Is there a feature to enable failover if primary mysql does not respond and go to secondary mysql server. Is there anyway to enable multiple point of failure in any kind of setup?. Thanks Regards, Jeffrey Iskandar Ahmad System Engineer Technology Division Time dotNet bhd
Re: mysql high availability
Hi Dave, Dave 'Duke of URL' Weiner writes: This is probably worth talking about now, since we are working on the mysql module for the next several weeks. Using Matt's read/update patch as a starting point the mysql functions are broken up into read-only queries and update queries with separate definitions in vmysql.h for two mysql login connections. One for updates and one for read only. I don't have any clean ideas yet on how to handle the three cases most people are running into: 1) one mysql server for everything 2) replicated mysql system, with one read only server (slave) and one update server (master) 3) a primary server and a backup server Anyone have any ideas? Why yes, Ken, I do :) First off, a very good start. As you and I have discussed before, I was atempting a primary server with two slave servers replicating off the master. Using Ben's patch, everything seemed to work quite well, using the hostname of the master as the Write server, and localhost as the read-only server. In testing, when the master write server was shut down, users could not authenticate via pop, as the login routines were attempting to write the ip address and other info for the pop-before-smtp files. My only suggestion would be not to store the pop-before-smtp data in the MySQL database. That way, you end up with, IMO, a fault tolerant system for MySQL authentication. Yes, I was looking at that. The first thing that I ran into is trying to minimize database transactions during a pop access. The authentication part is easy, just read the user info from mysql. There are two optional transactions that do updates during an authentication 1) the roaming users --enable-roaming-users=y 2) authentication logging --enable-auth-log=y It seems pretty wasteful to have the authentication mechanism open two database connections, one for the read only auth and one for the two possible updates. But I really can not see a way around it. For the roaming users stuff to work, the IP addresses have to be centralized. Otherwise in a clustered setup once they pop, they may or may not be able to use smtp relaying. We have tried using a file based system with a NFS mount, but it runs into synchronization problems when multiple machines are updating the file at the same time, and locking doesn't help because NFS locks will fail. One thing we've been looking at is having fall back servers for both the read and update mysql connections. Meaning: if a connection to one fails, then it attempts a connection to the fallback machine. The other thing is the authentication needs to be compiled into the binaries. One other option is a patch from Ondrej that gets the mysql auth data from environment variables. Ken Jones
Re: mysql high availability
Ken, A good way of getting around the NFS locking problem is to insert another program inbetween tcpserver and qmail-smtpd that checks a database for a relay flag (Which can be set when you call open_smtp_relay()).and that program sets the RELAYCIENT. Sean - Original Message - From: Ken Jones [EMAIL PROTECTED] To: Dave 'Duke of URL' Weiner [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 7:28 AM Subject: Re: mysql high availability Hi Dave, Dave 'Duke of URL' Weiner writes: This is probably worth talking about now, since we are working on the mysql module for the next several weeks. Using Matt's read/update patch as a starting point the mysql functions are broken up into read-only queries and update queries with separate definitions in vmysql.h for two mysql login connections. One for updates and one for read only. I don't have any clean ideas yet on how to handle the three cases most people are running into: 1) one mysql server for everything 2) replicated mysql system, with one read only server (slave) and one update server (master) 3) a primary server and a backup server Anyone have any ideas? Why yes, Ken, I do :) First off, a very good start. As you and I have discussed before, I was atempting a primary server with two slave servers replicating off the master. Using Ben's patch, everything seemed to work quite well, using the hostname of the master as the Write server, and localhost as the read-only server. In testing, when the master write server was shut down, users could not authenticate via pop, as the login routines were attempting to write the ip address and other info for the pop-before-smtp files. My only suggestion would be not to store the pop-before-smtp data in the MySQL database. That way, you end up with, IMO, a fault tolerant system for MySQL authentication. Yes, I was looking at that. The first thing that I ran into is trying to minimize database transactions during a pop access. The authentication part is easy, just read the user info from mysql. There are two optional transactions that do updates during an authentication 1) the roaming users --enable-roaming-users=y 2) authentication logging --enable-auth-log=y It seems pretty wasteful to have the authentication mechanism open two database connections, one for the read only auth and one for the two possible updates. But I really can not see a way around it. For the roaming users stuff to work, the IP addresses have to be centralized. Otherwise in a clustered setup once they pop, they may or may not be able to use smtp relaying. We have tried using a file based system with a NFS mount, but it runs into synchronization problems when multiple machines are updating the file at the same time, and locking doesn't help because NFS locks will fail. One thing we've been looking at is having fall back servers for both the read and update mysql connections. Meaning: if a connection to one fails, then it attempts a connection to the fallback machine. The other thing is the authentication needs to be compiled into the binaries. One other option is a patch from Ondrej that gets the mysql auth data from environment variables. Ken Jones
RE: mysql high availability
I've been down this road and that's a terrible way to do it. Why? Because for every SMTP connection you have to initiate a connection to the database. That's expensive. What you really want to do is have the tcpserver program make a persistent connection to the database and query it for every SMTP session. I couldn't think of a better way to do it so I hacked up tcpserver and added MySQL support to it. It reads a control file (/var/qmail/control/sql) in the following format: server sql.domain.com port3306 databasevpopmail table relay userusername passsecret time1800 When/if the persistent connection drops it automatically retries. If it fails then it allows the SMTP session anyway but does not set RELAYCLIENT. It retries every time seconds. The patch is available on my web site: http://matt.simerson.net/computing/mail.shtml and is found under the link entitled tcpserver MySQL patch Matt -Original Message- From: Sean C Truman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 6:50 AM To: Ken Jones; Dave 'Duke of URL' Weiner Cc: [EMAIL PROTECTED] Subject: Re: mysql high availability Ken, A good way of getting around the NFS locking problem is to insert another program inbetween tcpserver and qmail-smtpd that checks a database for a relay flag (Which can be set when you call open_smtp_relay()).and that program sets the RELAYCIENT. Sean - Original Message - From: Ken Jones [EMAIL PROTECTED] To: Dave 'Duke of URL' Weiner [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 7:28 AM Subject: Re: mysql high availability Hi Dave, Dave 'Duke of URL' Weiner writes: This is probably worth talking about now, since we are working on the mysql module for the next several weeks. Using Matt's read/update patch as a starting point the mysql functions are broken up into read-only queries and update queries with separate definitions in vmysql.h for two mysql login connections. One for updates and one for read only. I don't have any clean ideas yet on how to handle the three cases most people are running into: 1) one mysql server for everything 2) replicated mysql system, with one read only server (slave) and one update server (master) 3) a primary server and a backup server Anyone have any ideas? Why yes, Ken, I do :) First off, a very good start. As you and I have discussed before, I was atempting a primary server with two slave servers replicating off the master. Using Ben's patch, everything seemed to work quite well, using the hostname of the master as the Write server, and localhost as the read-only server. In testing, when the master write server was shut down, users could not authenticate via pop, as the login routines were attempting to write the ip address and other info for the pop-before-smtp files. My only suggestion would be not to store the pop-before-smtp data in the MySQL database. That way, you end up with, IMO, a fault tolerant system for MySQL authentication. Yes, I was looking at that. The first thing that I ran into is trying to minimize database transactions during a pop access. The authentication part is easy, just read the user info from mysql. There are two optional transactions that do updates during an authentication 1) the roaming users --enable-roaming-users=y 2) authentication logging --enable-auth-log=y It seems pretty wasteful to have the authentication mechanism open two database connections, one for the read only auth and one for the two possible updates. But I really can not see a way around it. For the roaming users stuff to work, the IP addresses have to be centralized. Otherwise in a clustered setup once they pop, they may or may not be able to use smtp relaying. We have tried using a file based system with a NFS mount, but it runs into synchronization problems when multiple machines are updating the file at the same time, and locking doesn't help because NFS locks will fail. One thing we've been looking at is having fall back servers for both the read and update mysql connections. Meaning: if a connection to one fails, then it attempts a connection to the fallback machine. The other thing is the authentication needs to be compiled into the binaries. One other option is a patch from Ondrej that gets the mysql auth data from environment variables. Ken Jones
RE: mysql high availability
Oh trust me, I know that. :-) My point was that it's much less expensive to have tcpserver open up a persistent connnection to the MySQL server and keep it open rather than launching another (albeit small) program that connects to and queries the database. I'm only using MySQL for my SMTP relay (custom hacks) and I'm doing an average of 19 queries per second to the database over the last 60 days. Queries are only generated by SMTP sessions, updates are triggered by successful POP or IMAP authentications. Doing it my way, every incoming SMTP request only does a query. With your method you have to open a TCP connection, connect to the database, authenticate, query, and finally drop the connection for every SMTP session. I have seven of these mail toasters, the largest one handling an average of 1.7 million SMTP connections per day spread across 4 machines. That's the one I care about most and will refer to throughout the rest of this email. MySQL has no problems keeping up. It's running on a Quad Xeon 550 with 2GB of RAM and it only uses 5-10% of one CPU. When we have HA available for MySQL I'll move all the authentication from .cdb files into MySQL and the load on my MySQL server will rocket upward. It's to everyone's advantage to make every piece of the system as lightweight as possible. When that happens I'll probably also hack most of the functionality of vchkpw into tcpserver as well. Then all the POP3 connections (which represent the majority of mail retrieval) will trigger SQL updates via a persistent connection as well. At that time I'll probably try to twist Ken's arm into including the modified tcpserver as a compile time option to vpopmail. Why is persistent connections so important? Your system has a finite (normally tuneable) number of TCP connections that it can have open at a given time. Why waste a 100 of those connections to MySQL when you can have one persisent connnection? Why waste the CPU overhead necessary to open up and tear down an extra 100 TCP connections per second? Need more reasons? Read the very first tip given on MySQL's site about optimizing performance. It reads: Use persistent connections to the database to avoid the connection overhead. If you can't use persistent connections and you are doing a lot of new connections to the database, you may want to change the value of the thread_cache_size variable. See section 13.2.3 Tuning Server Parameters. It's also much easier to troubleshoot problems when you can look at your SQL servers processlist and only have 8 connections in there (two for each server (tcpserver SMTP tcpserver POP3)). It's cleaner, faster, and uses less resources. Another thought on this: What happens when you are using multiple SQL servers (for HA) and the primary is down? With having a small program that gets called by tcpserver, it's going to try to connect to the first SQL server and will have to timeout before it tries the second. That's painful, and every POP and SMTP session will then get a timeout length pause before their connection succeeds. When tcpserver opens the connection, if the persistent connection to the primary server drops, it'll try to reopen it. If it failes to reopen, I could add some logic to get it to connect to a secondary server. Then only the connections that arrive during the time tcpserver's connection is retrying have a pause, once it's failed over to the backup MySQL server(s) all connection resume as normal. This is also the way the RADIUS protocol handles failover. The client sends all requests and logging to the first configured server until that server stops answering. It then fails over to the next server and stays there until that server dies (at which time it'll try the first again). If we want connections to return to the first MySQL server we can have tcpserver periodically attempt to reconnect to the primary SQL server. Matt -Original Message- From: Sean C Truman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 30, 2001 12:52 PM To: Matt Simerson; Ken Jones; Dave 'Duke of URL' Weiner Cc: [EMAIL PROTECTED] Subject: Re: mysql high availability Matt, I can provide you with load specifications. MySQL can handle the work load. I have one customer site running 5 Node Cluster, This does over 500,000 messages a day. Attached is a extended-status from the mysql database server, a show of load average on the machine and also shows the table structure and record count. Machine Specs. Pent III 700Mhz 512MB Ram. With this cluster we are checking the database for relay and or deny flag every single connection. Point being that this setup is not even stressing the database and MySQL is a champ!. Sean - Original Message - From: Matt Simerson [EMAIL PROTECTED] To: 'Sean C Truman' [EMAIL PROTECTED]; Ken Jones [EMAIL PROTECTED]; Dave 'Duke of URL' Weiner [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 1:33 PM Subject: RE
Re: mysql replication
Can you add some postgres support? On Tue, 29 May 2001, Ken Jones wrote: Date: Tue, 29 May 2001 17:44:32 GMT From: Ken Jones [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: mysql replication Hey Folks, Would the author of the read/update mysql patch send me an email? I want to put a credit in the AUTHORS and ChangeLog. I have the mysql module setup to do all read queries on one connection and all updates on another connection. This should fit in well with mysql replication. I was reading over the mysql documentation today and they suggest two connections. One for the master that does any updating and a second for the slave that does only reading. I will post the new version later today. Ken
Re: mysql replication
The vpopmail-4.10.8 release has a postgres module. I need to add it to the configure script. Ken Dan Phoenix writes: Can you add some postgres support? On Tue, 29 May 2001, Ken Jones wrote: Date: Tue, 29 May 2001 17:44:32 GMT From: Ken Jones [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: mysql replication Hey Folks, Would the author of the read/update mysql patch send me an email? I want to put a credit in the AUTHORS and ChangeLog. I have the mysql module setup to do all read queries on one connection and all updates on another connection. This should fit in well with mysql replication. I was reading over the mysql documentation today and they suggest two connections. One for the master that does any updating and a second for the slave that does only reading. I will post the new version later today. Ken
Re: mysql high availability
Is the data static? If not it is probably not worth the effort as you would have to send every write to every mysql server. If it is static then sure just tell the program calling mysql to open another mysql server if the first one fails for example something like this. if (!(connection = mysql_connect (host, user, password))) then connection=mysql_connection (host2, user, password); Of Course this will require rewriting the mysql login section of vchkpw. Mark - Original Message - From: Jeffrey Iskandar Ahmad [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 29, 2001 7:26 PM Subject: mysql high availability Hi guys, Is there a feature to enable failover if primary mysql does not respond and go to secondary mysql server. Is there anyway to enable multiple point of failure in any kind of setup?. Thanks Regards, Jeffrey Iskandar Ahmad System Engineer Technology Division Time dotNet bhd
Re: Mysql or cdb
On Mon, May 21, 2001 at 12:12:02PM -0700, Roy Kartadinata wrote: Quick questions for all those that runs web hosting business, is it better running vpopmail with mysql or cdb support ? If using mysql, should I use large site setting or not ? I know with large site setting turn off, everything would in one table. Is that slow the pop process down ? MySQL, definitely. Much more manageable and eliminates file locking difficulties as you scale. And which scheme you select depends on your systems: Lots of small domains with few accounts (small site), or just a couple domains with thousands of users each (large site)? -B -- Ben Beuchler There is no spoon. [EMAIL PROTECTED]-- The Matrix
Re: Mysql or cdb
ahh...thnx, I have a lot of domains with about average of 20 POP accounts for each domain. So I should use small site setting then right ? Roy K Unix is simple, but it takes a genius to understand the simplicity. - Original Message - From: Ben Beuchler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, May 21, 2001 12:12 PM Subject: Re: Mysql or cdb On Mon, May 21, 2001 at 12:12:02PM -0700, Roy Kartadinata wrote: Quick questions for all those that runs web hosting business, is it better running vpopmail with mysql or cdb support ? If using mysql, should I use large site setting or not ? I know with large site setting turn off, everything would in one table. Is that slow the pop process down ? MySQL, definitely. Much more manageable and eliminates file locking difficulties as you scale. And which scheme you select depends on your systems: Lots of small domains with few accounts (small site), or just a couple domains with thousands of users each (large site)? -B -- Ben Beuchler There is no spoon. [EMAIL PROTECTED]-- The Matrix
Stuck - Re: Mysql and Vpopmail
Hi I am running qmail and mysql 3.23.37. Trying to compile vpopmail-4.9.10 on a RedHat 7.0 Server using mysql for authenticaion. Compiling seems to be fine (in other words there are no apparent errors using my options). Here is my problem: [root@jupitor vpopmail-4.9.10]# telnet localhost 110 Trying 204.225.119.9... Connected to localhost.visinet.ca. Escape character is '^]'. +OK 23220.989199620@jupitor user blue +OK pass blue /disk02/vpopmail/bin/vchkpw: error while loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory -ERR authorization failed Connection closed by foreign host. Here are my configure options: ./configure \ --enable-large-site=n \ --enable-apop=y \ --enable-passwd=y \ --enable-roaming-users=y \ --enable-mysql=y \ --enable-sqlincdir=/usr/local/mysql/include/mysql \ --enable-sqllibdir=/usr/local/mysql/lib/mysql I did edit the vmysql.h file with the appropriate settings. Any help would be appreciated. Tony
RE: Stuck - Re: Mysql and Vpopmail
It can't find the mysql libraries. Find libmysqlclient.so.10 (find / -name libmysqlclient.so.10 -print) and make sure the path to that file is in /etc/ld.so.conf then run ldconfig. Hi I am running qmail and mysql 3.23.37. Trying to compile vpopmail-4.9.10 on a RedHat 7.0 Server using mysql for authenticaion. Compiling seems to be fine (in other words there are no apparent errors using my options). Here is my problem: [root@jupitor vpopmail-4.9.10]# telnet localhost 110 Trying 204.225.119.9... Connected to localhost.visinet.ca. Escape character is '^]'. +OK 23220.989199620@jupitor user blue +OK pass blue /disk02/vpopmail/bin/vchkpw: error while loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory -ERR authorization failed Connection closed by foreign host. Here are my configure options: ./configure \ --enable-large-site=n \ --enable-apop=y \ --enable-passwd=y \ --enable-roaming-users=y \ --enable-mysql=y \ --enable-sqlincdir=/usr/local/mysql/include/mysql \ --enable-sqllibdir=/usr/local/mysql/lib/mysql I did edit the vmysql.h file with the appropriate settings. Any help would be appreciated. Tony
Re: mysql+vpopmail+checkingmail
I got vpopmail and mysql to work togeather, it rights to the database and all. But its not accepting my password, i try my login with the @domain.com at the end, and nothing works, i know I have it right, I run linux, newest vpopmail and mysql. Anyone have similar probs. I also connect fine to the pop3 server Have you configured your qmail-pop3 server to use vchkpw for authentication. Read INSTALL file of vpopmail. Hope this helps. - Sumith
Re: mysql+vpopmail+checkingmail
[EMAIL PROTECTED] wrote: /usr/local/bin/tcpserver 0 pop-3 /qmail/bin/qmail-popup 75megs.com \ /qmail/vpopmail/bin/vchkpw /qmail/bin/qmail-pop3d Maildir I got that line in my startup info Also I ran into two other problems, 1. When I send mail it dosent check passwords. Isent that bad? 2. How do i make it so that i can sendmail anywhere just not to whats in my rcpthosts file? Thank you Sounds like you are battling with the smtp relay control problem. Follow the directions in the vpopmail INSTALL file with --enable-roaming-users=y option. Then make sure you check your email with pop before sending email via smtp. Also do not edit your rcpthosts file or delete it. Let the vpopmail vaddomain/vdeldomain programs update it for you. -- Ken Jones "Live Free, Live GPL" http://www.inter7.com/
Re: mysql+vpopmail+checkingmail
In a message dated 3/17/2001 4:51:26 PM Eastern Standard Time, [EMAIL PROTECTED] writes: Also do not edit your rcpthosts file or delete it. Let the vpopmail vaddomain/vdeldomain programs update it for you. Well, i need to send email to an address on aol.com, and editing it was the only way it would send, when I ran qmail last year I didnt have that problem
Re: MySQL - why not?
Maybe the problem lies here --enable-mysgl=y \ mysql is spelled with a q not a g Steve At 23:03 16/03/2001, you wrote: I am trying to get vpopmail to use mysql, but no matter what I do, it keeps writing to the .cdb files. MySQL seems to be up and running ok. I can access it. Below is my configure. 'vconvert -c -s' runs with no errors, but no database gets created. What have I missed now? === Cut === #! /bin/sh # Generated automatically by configure. # Run this file to recreate the current configuration. # This directory was configured as follows, # on host kb3.ksbase.com: # ./configure \ --enable-roaming-users=y \ --enable-default-domain=ksbase.com \ --enable-tcprules-prog=/bin/tcprules \ --enable-tcpserver-file=/home/vpopmail/etc/tcp.smtp \ --enable-logging=y \ --enable-log-name=vpopmail \ --enable-ucspi-dir=/usr/local/src/ucspi-tcp-0.88 \ --enable-mysgl=y \ --enable-hardquota=9000 === Cut === KS KARICO Business Services Toronto, ON Canada http://www.ksbase.com ... Back up my hard disk? I can't find the reverse switch!
Re: mysql + vpopmail problem
[EMAIL PROTECTED] wrote: ok, sorry, I use RH 7.0, the newest stable version of mysql, I use vpopmail 4.9.9 I also tried with 4.9.8 I get this error /qmail/vpopmail/bin/vadddomain: error while loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory /configure --prefix=/qmail/vpopmail --enable-mysql=y --enable-sqlincdir=/usr/local/mysql/include/mysql --enable-sqllibdir=/usr/local/mysql/lib/mysql --enable-qmaildir=/qmail thats what I do to configure, and those are the correct dirs to lib and include You need to update your /etc/ld.so.conf file to point to the directory where you mysql shared object libraries reside. Ken Jones
Re: mysql + vpopmail problem
im a little lost from that, all i know is that in that long funny looking directory /usr/local/mysql/lib/mysql is the file libmysqlclient.so.10 and when I run vadddomain, I get that error saying it cant find it.
Re: mysql + vpopmail problem
you have to be more elaborate in future for people to help you we don;t evenknow what OS your on anyways freebsd ldconfig -m /usr/local/mysql/lib/mysql should work linux edit /etc/ld.so.conf i beleive add that line ldconfig provide details! On Tue, 13 Mar 2001 [EMAIL PROTECTED] wrote: Date: Tue, 13 Mar 2001 20:07:47 EST From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: mysql + vpopmail problem im a little lost from that, all i know is that in that long funny looking directory /usr/local/mysql/lib/mysql is the file libmysqlclient.so.10 and when I run vadddomain, I get that error saying it cant find it.
RE: mysql + vpopmail problem
Also as a note on FreeBSD. You should copy the ldconfig_paths out of your /etc/defaults/rc.conf and paste it into your /etc/rc.conf then add the line to the end of string. Mine looks like this: ldconfig_paths="/usr/lib/compat /usr/X11R6/lib /usr/local/lib /usr/local/lib/mysql" This will make sure the path is loaded on boot should your server crash or lose power. BEWARE!! Make sure you copy the full line from out of the /etc/defaults/rc.conf. The /etc/rc.conf overrides any of the variables in the /etc/default/rc.conf. If you do not copy the full line you could get stuck in single user mode using 'ed' to fix the mistake. Big Pain :( Joe -Original Message- From: Dan Phoenix [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 13, 2001 6:08 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mysql + vpopmail problem you have to be more elaborate in future for people to help you we don;t evenknow what OS your on anyways freebsd ldconfig -m /usr/local/mysql/lib/mysql should work linux edit /etc/ld.so.conf i beleive add that line ldconfig provide details! On Tue, 13 Mar 2001 [EMAIL PROTECTED] wrote: Date: Tue, 13 Mar 2001 20:07:47 EST From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: mysql + vpopmail problem im a little lost from that, all i know is that in that long funny looking directory /usr/local/mysql/lib/mysql is the file libmysqlclient.so.10 and when I run vadddomain, I get that error saying it cant find it.
Re: mysql + vpopmail problem
Hello, set the LD_LIBRARY_PATH=/path/to/libmysqlclient.so.10 best regards dushyanth When i do ./configure --prefix=/qmail/vpopmail --enable-sqwebmail-pass=y --enable-mysql=y --enable-sqlincdir=/usr/local/mysql/include/mysql --enable-sqllibdir=/usr/local/mysql/lib/mysql/lib --enable-qmaildir=/qmail The two locations to sqlincdir and sqllibdir are not read i guess and it goes to the default location /usr/local/lib/mysql or something along those lines. So i made symbolic links to those two default locations to the correct one, it then would make, but when i try to run anything like vadddomain i get ./vadddomain: error while loading shared libraries: libmysqlclient.so.10: cannot open shared object file: No such file or directory Any ideas? -- Dushyanth Harinath Programmer Archean Infotech Limited http://www.archeanit.com
Re: mysql + vpopmail problem
i know which is crazy.alot of time people are putting them in /usr/local/mysql/lib On Sat, 10 Mar 2001 [EMAIL PROTECTED] wrote: Date: Sat, 10 Mar 2001 21:34:33 EST From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: mysql + vpopmail problem In a message dated 3/10/2001 9:22:00 PM Eastern Standard Time, [EMAIL PROTECTED] writes: [EMAIL PROTECTED] Well, it works now without those options because i made symbolic links, it looks in /usr/local/include/mysql and /usr/local/lib/mysql and i did those two configure options before, and it just ignored them and would look in /usr/local/include/mysql, and lib/mysql, i made made symbolic links to those, then it will make, but I still get that error when I try to run vadddomain. I really cant find any more documentation on this.
Re: mysql+vpopmail question
Hi Tamer, use vpasswd to change the users password. also make sure that you set the skip-networking option in /etc/mysql/my.cnf. This way you make sure that mysql will not listen for external login attempts. kind regards Juergen Hoffmann You wrote: Daniel Hardaker writes: Is it possible to get vpopmail/vchkpw (with mysql support) to store passwords in clear text? The reason why I am asking this is would like a "password retrieval feature" using a hint question (like in hotmail), using a php script I wrote (cracking the hashes would be silly). :P Daniel Wrote: Hmm...im sure it is, but its extremely unsafe especially using mySQL... Why would it be exteremly unsafe if I am the only local user on that box. No one else has local access to the server and I limit access to the mysql server only from localhost. Besides, all the user's and passwords in the mysql database are virtual (Thanks to vpopmail.) surely it would be much better to have the hint question and then get your script to reset the password and allow the user to change it to whatever thet like? Just my opinion.. IMG SRC="/images/emoticon14.gif" Now, Your suggesstion is very good, except for the fact that "changing it" is not so easy because as far as I know the mysql encryption funtions are not compatible with unix crypt functions so I guess I cannot do it from php if the password has to be checked against a hash. also discussed earlier on the list: http://www.mail-archive.com/vchkpw@inter7.com/msg01882.html Am I correct? Let me know! Tamer -- later Buddy Get your own free E-Mail Address at http://www.web-4-all.de
Re: mysql+vpopmail question
Daniel Hardaker writes: Is it possible to get vpopmail/vchkpw (with mysql support) to store passwords in clear text? The reason why I am asking this is would like a "password retrieval feature" using a hint question (like in hotmail), using a php script I wrote (cracking the hashes would be silly). :P Daniel Wrote: Hmm...im sure it is, but its extremely unsafe especially using mySQL... Why would it be exteremly unsafe if I am the only local user on that box. No one else has local access to the server and I limit access to the mysql server only from localhost. Besides, all the user's and passwords in the mysql database are virtual (Thanks to vpopmail.) surely it would be much better to have the hint question and then get your script to reset the password and allow the user to change it to whatever thet like? Just my opinion.. :) Now, Your suggesstion is very good, except for the fact that "changing it" is not so easy because as far as I know the mysql encryption funtions are not compatible with unix crypt functions so I guess I cannot do it from php if the password has to be checked against a hash. also discussed earlier on the list: http://www.mail-archive.com/vchkpw@inter7.com/msg01882.html Am I correct? Let me know! Tamer
RE: mysql or not.
Well, I found the breaking point of vpopmail's open-smtp feature to be about 1500 domains (in an NFS environment). The actual breaking point isn't really related to the amount of domains/users but rather how many and how freqently your clients open POP/IMAP sessions. I was forced to move away from .cdb to MySQL for the open-smtp (which required a bit of hacking) portions of vpopmail about a month ago. I have yet to convert all my vpasswd files into a MySQL database. I like the fact that all the files are strewn about the file system, easily editable, easily viewable, and easily accessible from every mail server in a large NFS cluster. Having said that, I've found a few problems that stuffing authentication into a MySQL database would fix. Vpopmail's quota mechanism can be tempermental and in practice, I've had mixed results with it. I'm not going to say that this isn't due to some form of operator malfunction (my screwing up) but I know I'm not the only one that's had issues. Because of that, I use system quotas (I only need to enforce them for domains and each domain his it's own UID/GID) and I've had, on a couple occasions, the domains vpasswd file get nuked. I'm guessing it was because the domains mail was over the system quotas and it opened the file (destroying contents) and then couldn't write to it because of quota restrictions. This is just my theory on how it happened. Having the data stuffed into MySQL would prevent this. :-) I've also seen a few races in MySQL recently where my MySQL server just varies off into lala land. It still responds to queries but much more slowly and of course, the system load starts heading skyward. For some reason, I'm not able to kill the MySQL processes (using mysqladmin) that are causing the problems so I end up having to kill the MySQL process and restart it. I've just upgraded to the latest MySQL beta and we'll see if that doesn't help. I also want to play with the replication features. If I can get that working well I'll go ahead and put a MySQL instance on every server in my mail farm and use replication to keep all the data synced. That will be cool. :-) Matt -Original Message- From: Peter Brezny [mailto:[EMAIL PROTECTED]] Sent: Monday, January 22, 2001 1:02 PM To: [EMAIL PROTECTED] Subject: mysql or not. Why use mysql with vpopmail? What are the primary advantages/disadvantages. Is it something really needed for sites 2000 users? TIA Peter Brezny SysAdmin Services Inc.
Re: mysql or not.
"Matt Simerson" [EMAIL PROTECTED] writes: Well, I found the breaking point of vpopmail's open-smtp feature to be about 1500 domains (in an NFS environment). The actual breaking point isn't really related to the amount of domains/users but rather how many and how freqently your clients open POP/IMAP sessions. I was forced to move away from .cdb to MySQL for the open-smtp (which required a bit of hacking) portions of vpopmail about a month ago. Have you (or anyone else) tried using vpopmail's integration with LDAP? Putting users/passwords into an LDAP directory seems a better match -- SQL's optimized for read/write while LDAP is best at read-mostly.
RE: mysql or not.
I thought about it, read about it, and toyed with the LDAP idea and I think it's got a lot of potential for working well for authentication but for the POP-AUTH type stuff, I don't think it would work well enough on a large scale system handling hundreds or thousands of updates per minute across a dozen machines (as my POP servers are doing). Also, the LDAP support (per the documentation) for some of my other software is pretty beta and I don't know LDAP well enough to just jump in head first and hope I can solve any problems that crop up on a production server. With .cdb files or MySQL it's pretty easy for me to hack together tweaks that I need to make all the parts of my mail server play nicely together. Matt -Original Message- From: Chris Shenton [mailto:[EMAIL PROTECTED]] Sent: Monday, January 22, 2001 12:22 PM To: Matt Simerson Cc: '[EMAIL PROTECTED]' Subject: Re: mysql or not. "Matt Simerson" [EMAIL PROTECTED] writes: Well, I found the breaking point of vpopmail's open-smtp feature to be about 1500 domains (in an NFS environment). The actual breaking point isn't really related to the amount of domains/users but rather how many and how freqently your clients open POP/IMAP sessions. I was forced to move away from .cdb to MySQL for the open-smtp (which required a bit of hacking) portions of vpopmail about a month ago. Have you (or anyone else) tried using vpopmail's integration with LDAP? Putting users/passwords into an LDAP directory seems a better match -- SQL's optimized for read/write while LDAP is best at read-mostly.
RE: mySQL problem
The problem is that the mysql client libs are loaded at run time
Heres what you could do:
1. if you are on a solaris box: set LD_LIBRARY_PATH to include
/usr/local/mysql/lib
2. Linux: add /usr/local/mysql/lib to /etc/ld.so.conf run ldconfig or
reboot
3. FreeBSD edit the "ldconfig_paths" /etc/rc.conf (look in
/etc/defaults/rc.conf )
to include /usr/local/mysql/lib
YMMV
Hope this helps,
--
-
Sean P. Scanlon
perl -e 'print pack("h38", "3707370426c6575646f647e2e65647"), "\n"'
-
-- -Original Message-
-- From: Daniel Hardaker [mailto:[EMAIL PROTECTED]]
-- Sent: Saturday, December 16, 2000 8:05 PM
-- To: [EMAIL PROTECTED]
-- Subject: mySQL problem
--
--
-- Hi, ive compiled vpopmail with mySQL support, set libs to
-- /usr/local/mysql/libs/mysql and inc to
-- /usr/local/mysql/include/mysql and
-- everything compiled fine
-- However, now i get the following error message when I try to
-- add a domain or
-- convert users:
-- [admin@jupiter bin]# ./vadddomain test2.com
-- ./vadddomain: error in loading shared libraries:
-- libmysqlclient.so.6: cannot
-- open shared object file: No such file or directory
--
-- Anyone shed some light on what this is all about?
-- libmysqlclient.so.6 is in
-- the lib dir specified above.
--
-- Thanks!
-- ---
-- PGP Key at: www.iv2.co.uk/danielhardaker.asc
--
--
RE: MySQL + vpopmail
Why would you want to send your passwords in clear text? Why would you want them stored that way? -- Tony Publiski World Wide Net, Inc. +1 (734) 513-7707 x 2012 -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Friday, November 03, 2000 3:47 PM To: [EMAIL PROTECTED] Subject: MySQL + vpopmail Does vpopmail have clear text password support ?
Re: mysql and cdb authentication
Greg Kopp wrote: I want to migrate my domains from cdb to mysql authentication. I compiled vpopmail 4.8.7 with: ./configure \ --enable-roaming-users=y \ --enable-tcpserver-file=/etc/tcp.smtp \ --enable-relay-clear-minutes=30 \ --enable-logging=y \ --enable-hardquota=1500 \ --enable-mysql=y \ --enable-sqlincdir=/usr/include/mysql \ --enable-sqllibdir=/usr/lib/mysql I changed vmysql.h to use "domains" as the default database, and I left "vpopmail" as the default table name. Once installed, I can't check my mail on any cdb domain. Where did I go wrong? You'll need to convert your vpasswd files into mysql. Use the following command: ./vconvert -c -s That will convert every virtual domain. I recommend moving one domain and testing it. ./vconvert -c -s domainname Ken
RE: mysql and cdb authentication
I did that and it stil fails, now it's failing on both. If I recompile vpopmail without mysql, it works again -Original Message- From: kbo [mailto:kbo]On Behalf Of Ken Jones Sent: Tuesday, September 05, 2000 3:19 PM To: Greg Kopp Cc: [EMAIL PROTECTED] Subject: Re: mysql and cdb authentication Greg Kopp wrote: I want to migrate my domains from cdb to mysql authentication. I compiled vpopmail 4.8.7 with: ./configure \ --enable-roaming-users=y \ --enable-tcpserver-file=/etc/tcp.smtp \ --enable-relay-clear-minutes=30 \ --enable-logging=y \ --enable-hardquota=1500 \ --enable-mysql=y \ --enable-sqlincdir=/usr/include/mysql \ --enable-sqllibdir=/usr/lib/mysql I changed vmysql.h to use "domains" as the default database, and I left "vpopmail" as the default table name. Once installed, I can't check my mail on any cdb domain. Where did I go wrong? You'll need to convert your vpasswd files into mysql. Use the following command: ./vconvert -c -s That will convert every virtual domain. I recommend moving one domain and testing it. ./vconvert -c -s domainname Ken
