Re: [vchkpw] Mysql table
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). -- Tom Collins - [EMAIL PROTECTED] Vpopmail - virtual domains for qmail: http://vpopmail.sf.net/ QmailAdmin - web interface for Vpopmail: http://qmailadmin.sf.net/
Re: [vchkpw] Mysql table
John Simpson wrote: On 2007-09-19, at 1532, mlist wrote: Please try creating a domain with a long name, up around 80-90 characters long. ... I did as you said and I recieved errors. Here is the command I used and the errors returned. ... vmysql: error creating table 'a_com': Incorrect table name 'a_com' Error. Failed while attempting to add domain to auth backend you missed the other error message. mysql has a limit of 64 bytes for a table name (at least mysql versions 5.1, 5.0, and 5.1 do, i'm assuming earlier versions had the same limit.) also see RFC 1035 section 2.3.4. each portion of a domain name (i.e. the abc in abc.com) can be no longer than 63 bytes, and no full hostname (i.e. www.abc.com) can be longer than 255 bytes. and since mysql has a limit of 64 bytes for a table name, you have a... maybe not a bug, but a design flaw. the name a(63 times).com IS a valid domain name, but a(63 times)_com is NOT a valid table name. perhaps you shouldn't store each domain's data in a separate table? i've never understood the reason for creating separate tables for each domain anyway- but since i don't normally use a SQL back-end for mailbox information, it's not something i really worry about. Well then . . . that explains it. Thanks Rick, Joshua, and John. I'm no full-time DBA wouldn't it take less time to query through a specific table looking for data than it would to query one massive table? Any one else have any thoughts? I'm curious to know which would perform better. Matt
Re: [vchkpw] Mysql table
mlist wrote: John Simpson wrote: and since mysql has a limit of 64 bytes for a table name, you have a... maybe not a bug, but a design flaw. the name a(63 times).com IS a valid domain name, but a(63 times)_com is NOT a valid table name. Definitely a design flaw, even before the domain name length increase. I believe Ken once told me it was a mistake, but he never removed it because people were using it. I think the name length issue is a good reason to depreciate the feature. I'm looking to reduce the number of options to ./configure, and this looks like a good candidate. perhaps you shouldn't store each domain's data in a separate table? i've never understood the reason for creating separate tables for each domain anyway- but since i don't normally use a SQL back-end for mailbox information, it's not something i really worry about. Well then . . . that explains it. Thanks Rick, Joshua, and John. I'm no full-time DBA wouldn't it take less time to query through a specific table looking for data than it would to query one massive table? Any one else have any thoughts? I'm curious to know which would perform better. 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.
Re: [vchkpw] Mysql table
Rick Widmer wrote: mlist wrote: John Simpson wrote: and since mysql has a limit of 64 bytes for a table name, you have a... maybe not a bug, but a design flaw. the name a(63 times).com IS a valid domain name, but a(63 times)_com is NOT a valid table name. Definitely a design flaw, even before the domain name length increase. I believe Ken once told me it was a mistake, but he never removed it because people were using it. I think the name length issue is a good reason to depreciate the feature. I'm looking to reduce the number of options to ./configure, and this looks like a good candidate. perhaps you shouldn't store each domain's data in a separate table? i've never understood the reason for creating separate tables for each domain anyway- but since i don't normally use a SQL back-end for mailbox information, it's not something i really worry about. Well then . . . that explains it. Thanks Rick, Joshua, and John. I'm no full-time DBA wouldn't it take less time to query through a specific table looking for data than it would to query one massive table? Any one else have any thoughts? I'm curious to know which would perform better. 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. I was going to ask about why that option was still available (seeing as how it's widely disliked) but I didn't want to seem ungrateful. If in the future it is deprecated does anyone have an idea on how to convert many tables to one? I'm sure I could piece together a shell script but I know there are a lot better scripters out there than me. Matt
Re: [vchkpw] Mysql table
Rick Widmer wrote: mlist wrote: John Simpson wrote: and since mysql has a limit of 64 bytes for a table name, you have a... maybe not a bug, but a design flaw. the name a(63 times).com IS a valid domain name, but a(63 times)_com is NOT a valid table name. Definitely a design flaw, even before the domain name length increase. I believe Ken once told me it was a mistake, but he never removed it because people were using it. I think the name length issue is a good reason to depreciate the feature. I'm looking to reduce the number of options to ./configure, and this looks like a good candidate. perhaps you shouldn't store each domain's data in a separate table? i've never understood the reason for creating separate tables for each domain anyway- but since i don't normally use a SQL back-end for mailbox information, it's not something i really worry about. Well then . . . that explains it. Thanks Rick, Joshua, and John. I'm no full-time DBA wouldn't it take less time to query through a specific table looking for data than it would to query one massive table? Any one else have any thoughts? I'm curious to know which would perform better. 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. I vote we depreciate the feature if we can provide a script to help people migrate from the multi table method to the single table method. The feature was only intended for sites with one or two domains and large numbers of users. In that case the database would not thrash since there would only be a few tables involved. And the database could save space by not storing the pw_domain field. As a DBA on large databases I could not resist optimizing this redundant data. The space saved on 100,000 users would be 6.4Mbytes for pw_domain of 64 chars or 12.4Mbytes for pw_domain of 128 chars. With the amount of RAM and disk space of modern servers the space saved does not seem significant now. Considering the confusion this feature generates and the relatively small amount of space it saves I say it's worth depreciating the feature. It should be relatively straight forward to create a migration script to move a site with many tables to the single table design. Ken Jones
Re: [vchkpw] Mysql table
Please try creating a domain with a long name, up around 80-90 characters long. Then try to add a user, delete that user then delete that domain. If it all works, you are in good shape and I'll change the note in INSTALL to say you don't have to do anything special if you are using --disable-many-domains. If you have problems, let me know what happens and I'll figure something out. I expect it will probably work. Rick Rick, I did as you said and I recieved errors. Here is the command I used and the errors returned. mail:~ # vadddomain a.com Please enter password for postmaster: enter password again: qmail-newu: fatal: bad format in users/assign vmysql: error creating table 'a_com': Incorrect table name 'a_com' Error. Failed while attempting to add domain to auth backend qmail-newu: fatal: bad format in users/assign Error: no authentication database connection I'm not sure about that last error if it's refering to mysql database connectivity or not - the database is connected and I can establish authenticated smtp/pop3/imap connections. I thought I'd try adding another, smaller domain: mail:~ # vadddomain mytest.com Please enter password for postmaster: enter password again: qmail-newu: fatal: bad format in users/assign Error: (vadduser) Domain does not exist However, the database table is created and the directory is created too. I then tried to delete the domain: mail:~ # vdeldomain mytest.com Error: Domain does not exist Again, however the table is still in the DB. I'm not sure where to proceed. I'm running SLES10 2.6.16-21. I appreciate the help. Matt
Re: [vchkpw] Mysql table
qmail-newu: fatal: bad format in users/assign Before pursuing anything else, check this out - qmail-newu (part of qmail, and simply called by vpopmail) is telling you that your /var/qmail/users/assign file is incorrect/corrupted, and that it can't create the users.cdb file from it. Vpopmail uses the users/assign file to track what domains it owns, so if that file is bad you're in for problems. I don't know what field size restrictions are for that (neither the man page nor qmail-newu.c list any), but while that may not be your root cause, it's certainly a part of it... Josh Joshua Megerman SJGames MIB #5273 - OGRE AI Testing Division You can't win; You can't break even; You can't even quit the game. - Layman's translation of the Laws of Thermodynamics [EMAIL PROTECTED]
Re: [vchkpw] Mysql table
mlist wrote: Please try creating a domain with a long name, up around 80-90 characters long. Then try to add a user, delete that user then delete that domain. If it all works, you are in good shape and I'll change the note in INSTALL to say you don't have to do anything special if you are using --disable-many-domains. If you have problems, let me know what happens and I'll figure something out. I expect it will probably work. Rick Rick, I did as you said and I recieved errors. Here is the command I used and the errors returned. mail:~ # vadddomain a.com Please enter password for postmaster: enter password again: qmail-newu: fatal: bad format in users/assign vmysql: error creating table 'a_com': Incorrect table name 'a_com' Error. Failed while attempting to add domain to auth backend qmail-newu: fatal: bad format in users/assign Error: no authentication database connection I'm not sure about that last error if it's refering to mysql database connectivity or not - the database is connected and I can establish authenticated smtp/pop3/imap connections. I thought I'd try adding another, smaller domain: mail:~ # vadddomain mytest.com Please enter password for postmaster: enter password again: qmail-newu: fatal: bad format in users/assign Error: (vadduser) Domain does not exist However, the database table is created and the directory is created too. I then tried to delete the domain: mail:~ # vdeldomain mytest.com Error: Domain does not exist Again, however the table is still in the DB. I'm not sure where to proceed. I'm running SLES10 2.6.16-21. I appreciate the help. Matt Well I made some slight progress. Turns out that a. . . .com domains was still in my assign file. I deleted it out (along with the dozen or so blank newlines after it) and then was able to add/delete normal size domains. I tried again to add that a . . . .com domain and it gave me the same error and proceeded to break adding/deleting normal size domains. Matt
Re: [vchkpw] Mysql table
On 2007-09-19, at 1532, mlist wrote: Please try creating a domain with a long name, up around 80-90 characters long. ... I did as you said and I recieved errors. Here is the command I used and the errors returned. ... vmysql: error creating table 'a _com': Incorrect table name 'a _com' Error. Failed while attempting to add domain to auth backend you missed the other error message. mysql has a limit of 64 bytes for a table name (at least mysql versions 5.1, 5.0, and 5.1 do, i'm assuming earlier versions had the same limit.) also see RFC 1035 section 2.3.4. each portion of a domain name (i.e. the abc in abc.com) can be no longer than 63 bytes, and no full hostname (i.e. www.abc.com) can be longer than 255 bytes. and since mysql has a limit of 64 bytes for a table name, you have a... maybe not a bug, but a design flaw. the name a(63 times).com IS a valid domain name, but a(63 times)_com is NOT a valid table name. perhaps you shouldn't store each domain's data in a separate table? i've never understood the reason for creating separate tables for each domain anyway- but since i don't normally use a SQL back-end for mailbox information, it's not something i really worry about. | John M. Simpson--- KG4ZOW ---Programmer At Large | | http://www.jms1.net/ [EMAIL PROTECTED] | | http://video.google.com/videoplay?docid=-1656880303867390173 | PGP.sig Description: This is a digitally signed message part
Re: [vchkpw] Mysql table
mlist wrote: Hello, I'm upgrading vpopmail to version 5.4.21. The documentation states that I need to alter some tables. ALTER TABLE `dir_control` CHANGE `pw_domain` `pw_domain` CHAR(96) NOT NULL; ALTER TABLE `ip_alias_map` CHANGE pw_domain pw_domain CHAR(96) NOT NULL; ... I read on a previous post that I actually need to change the 'domain' columns (instead of 'pw_domain') - however, I'm running into one problem. I compiled with --disable-many-domains and my domain tables do not have a 'pw_domain' nor a 'domain' column. What am I missing here? Any help is appreciated. If you were starting from scratch, I would recommend not using --disable-many-domains, as I believe it is more efficient to have a single table with all data, and don't really like the idea of vpopmail adding tables when domains are created or having to delete them when the domain is removed. Its probably too late for that now... Please try creating a domain with a long name, up around 80-90 characters long. Then try to add a user, delete that user then delete that domain. If it all works, you are in good shape and I'll change the note in INSTALL to say you don't have to do anything special if you are using --disable-many-domains. If you have problems, let me know what happens and I'll figure something out. I expect it will probably work. Rick
