View Single Post
Old 12th September 2009, 16:10
pilot9 pilot9 is offline
Junior Member
Join Date: Sep 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default MySql prefixes

Originally Posted by digitalage View Post
Thank you pilot9 for your response. It's now too late for me, I already reinstalled linux on that server.

However, I'd like to know more details about this issue and how to avoid it in the future. I don't understand your case. Are you nice to explain a bit more detailed? If you can, please clear up the prefixes staff, as I don't understand why and when is it write to use them. I assume you installed drupal, and after that the hell began. Am I write?
Yes, I had installed Drupal, which gives you a nice option to use table prefixes for multisite installations in the same database. However, most recipes don't take them into consideration, so they are easy to miss. You are left with authentication errors, which are difficult to track sometimes. And as far as I know, the only more general solution to this sort of problem is keep careful track of everything! For relatively inexperienced sysadmin like myself, it is a mess and a climb up a steep learning curve... One general solution I would love to find (and have not found) is a comprehensive debugging recipe/howto, a series of steps to tackle certain kinds of very common errors (like authentication errors) where logs do not typically point to the problem directly...) When I have a little more time, I will sketch one out and maybe SPTM (smarter people than me) can flesh it out.

In my case, I had to go through my postfix (also dovecot, PAM, SASL) configuration files and add the MySQL database table prefixes accordingly. I had been working from a combination of Falko Timme's Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Debian Lenny) recipe along with Alex Saavedra's Drupal + Postfix Integration Under Ubuntu 8.04 (Hardy) recipe and Christoph Haas's excellent tutorial Howto: ISP-style Email Server with Debian-Etch and Postfix 2.3 (also a Lenny updated version.
Basically, wherever a call was made to MySql, I needed to add table prefixes to the tables... so for example:

I added table prefixe [--PREFIX--] to the lines below. So if my prefix was "MySite" then [--PREFIX--]sometable becomes MySitesometable .

1. In /etc/postfix/sasl/smtpd.conf

sql_select: SELECT pass FROM [--PREFIX--]users WHERE mail='%u' AND status=1

Note: "users" is of course the table in the database with the passwords and so on. The database itself and the columns in the tables themselves do NOT need to be prefixed!

2. In /etc/dovecot/dovecot-sql.conf

password_query = SELECT mail AS user, pass AS password FROM [--PREFIX--]users WHERE mail='%u';

Note: "user" and "password" above are column names to retrieve your user's signin names and pws to access their email. "users" again is the table where that info is stored (and so needs to be prefixed).

3. and in /etc/pam.d/smtp

auth required user=USER passwd=PW host= db=MYDB table=[--PREFIX--]users usercolumn=mail passwdcolumn=pass crypt=0

account sufficient user=USER passwd=PW host= db=MYDB table=[--PREFIX--]users usercolumn=mail passwdcolumn=pass crypt=0

Note that MYDB, USER and PW are the relevant Database, the system user that accesses the database (eg "Mail" or whatever) and its password.

4. and in the Postfix configuration files in /etc/postfix/...

4a. /etc/postfix/

query=SELECT domain_name AS domain FROM [--PREFIX--]mailfix_domains WHERE domain_name='%s'

4b. /etc/postfix/

query=SELECT CONCAT(md.domain_name, '/', LEFT(u.mail, LOCATE('@', u.mail) - 1),'/') AS maildir FROM [--PREFIX--]mailfix_domains md JOIN ([--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid) ON md.domain_id=mu.domain_id WHERE u.status=1 AND u.mail='%s'

4c. /etc/postfix/

query=SELECT mu.forward FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.mail='%s' AND LENGTH(mu.forward)>0

4d. /etc/postfix/

query=SELECT mu.incoming_bcc FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.status=1 AND u.mail='%s' AND LENGTH(mu.incoming_bcc)>0

4e. /etc/postfix/

query=SELECT mu.outgoing_bcc FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.status=1 AND u.mail='%s' AND LENGTH(mu.outgoing_bcc)>0

4f. /etc/postfix/

query=SELECT mu.quota FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.mail='%s'

The thing about MySQL is that it is very unforgiving. One little space or an added " ` " somewhere and you are going to get something like authentication errors. I spent a lot of time combing all the MySQL calls and then going ever so carefully over the whole process of how the different parts of the server call one another before I remembered these prefixes. The silver lining was taking a few steps up the learning curve...

Reply With Quote