#313 Postfixadmin does not show aliases

v2.91 (3.0 beta1)
closed-fixed
nobody
None
5
2014-08-23
2014-05-10
No

Hi,

My postfixadmin will not show aliases, specifically the ones that do not show up under users.

You select add alias and enter the values below (Which I pulled from a mysql alias database table), which did end up in the database, but they do not show up in the page brought up by list-virtual.php

Alias Database Entry
testalias@mydomain.com,testdestination@mydomain.com,mydomain.com,2014-05-10 04:45:31,2014-05-10 04:45:31,1

Show's the database statistics *Aliases 2 / 100 * Mailboxes 51 / 100

it did work/show aliases fine with v2.3.7

On: OpenSuSE 13.1 using mariadb-5.5.33-2.2.x86_64 and php5-5.5.12-225.1.x86_64 dovecot2.2/ postfix2.9

MariaDB has caused some issues. In the vacation table I modified the structure (Which may be wrong) to have a default value for the cache field as it was preventing the vacation from working.
cache text NOT NULL DEFAULT ''

Discussion

  • Christian Boltz

    Christian Boltz - 2014-05-18

    Works here (on openSUSE Factory ;-)

    Did you notice that list-virtual now has a line

    Show: All :: Mailboxes :: Aliases :: Domain Aliases

    which allows to choose what to show? Maybe you just have to click "All"? ;-)

    The vacation.cache field is a leftover from an older version of vacation.pl and unused now. I'll see if I just drop it (via setup.php) or if I update the code to maintain this field (in case someone uses an old version of vacation.pl).

     
    • Harm Gerding

      Harm Gerding - 2014-05-20

      Hi,

      Have gone back and tried the ALL:Mailboxes::Aliases::Domain Aliases links

      and it still does not work (I was using it with all originally)

       
  • Ben

    Ben - 2014-05-19

    Hi,

    I´ve the same issue. No alias are shown.

    And I cannot login as user and change my password (error message: Mail adress or password is not correct) - only admin login works. Logs don´t work also.

    I work with Debian 7, 5.5.37-MariaDB, PHP Version 5.4.28-1~dotdeb.1.

    If I delete a superadmin, the following apache error is written: Config::read(): attempt to read undefined config option "__LANG.pViewlog_action_delete_admin", returning null, referer: https://xy.de/list-admin.php

     
    Last edit: Ben 2014-05-19
  • John Keach

    John Keach - 2014-05-19

    Hello,

    FreeBSD 10, PHP 5.3.28, MariaDB-5.5.35 - no aliases.
    But in domain overview - Aliases: 9 / 1000

     
    Last edit: John Keach 2014-05-19
  • Christian Boltz

    Christian Boltz - 2014-05-27

    Unfortunately I still can't reproduce the problem.

    Can one of you please upload a complete mysqldump? (or mail it to me - postfixadmin[at]cboltz[dot]de)

    @Ben: I can confirm that logging has a bug - I opened https://sourceforge.net/p/postfixadmin/bugs/317/ for it.

    The /users login works here. Can you please test again (just to be sure you didn't mistype anything) and open a new bug if it still fails? Please also paste your Apache error_log - it should contain a line for the failed login.
    Just to be sure: you tried in /users/login.php, not in /login.php?

     
  • John Keach

    John Keach - 2014-05-27

    I updated the database from version 2.3.7.
    When use the www frontend 2.3.7 - aliases visible.

     
  • Ben

    Ben - 2014-05-28

    @Christian: The login problem is solved. My passwords were stored as hash and your admin software worked with md5crypt. I set each password again and all works fine.

    But is it possible to add a .http auth on the postmaster login /login.php, but not on the user login? I´ve tested this, but it doesn´t work with <files login.php="">, which doesn´t work.

    How has this mysqldump to look like? full export?

     
  • Morten Abildgaard

    I have the same issue with aliases not showing in Postfixadmin 2.91 / trunk.

    Have tried with both a Super admin and a regular admin for the domain. I also tried to set the max number of aliases to 99 to see if it was a bug from a too low number or a bug related to max number being set to 0.

     
  • Christian Boltz

    Christian Boltz - 2014-05-28

    @Ben: for the http auth, please ask in the forum to keep this bugreport readable.

    About the mysqldump: yes, a full export (table structure and data) would be the best. You can of course use test data and replace or remove your passwords, but please make sure it still reproduces the bug.

    Thinking about it - please also include your config.local.php (without database password ;-) - maybe there's a config options that breaks it?

    Another option is to change db_query() in functions.inc.php to error_log() all queries. Then find the query that should produce the alias list, run it in phpMyAdmin or mysql commandline and paste both query and result.

    If you are familiar with PHP a bit, adding a var_dump() here or there could also give some insight - it could show you where things break ;-)

    We can also do some live debugging if you ask on #postfixadmin on freenode - I'm cboltz there and usually online in the evening (CET timezone).

     
  • Harm Gerding

    Harm Gerding - 2014-05-29

    I could do the error log check, can you recommend which file/part to start with to save a little time in doing the research? (I have some PHP programming background)

    I could also do a mysqldump.

     
  • Christian Boltz

    Christian Boltz - 2014-05-29

    You'll catch all queries if you error_log() them in functions.inc.php, function db_query().

    The query to get the alias list is (obviously) something like "SELECT ... FROM alias ..."

    For a more targeted way: list-virtual.php uses AliasHandler->getList() - checking $tAlias with var_dump() might give a quick insight (maybe it's "just" a template problem, even if I doubt that)

    If you want to dig through the code - AliasHandler->getList() basically calls PFAHander->getList() with an additional query sniplet. getList() calls read_from_db() - you'll find $query near the end of it.

     
  • Morten Abildgaard

    Our setup has it's own local MariaDB instance, and it's just me playing around, so I enabled the general log on the DB with

    set global general_log = 'ON';
    set global general_log_file = '/run/mysqld/mysqld.log';

    I then did a

    tail -f /run/mysqld/mysqld.log | sed '/Connect\|Init\|Quit\|Field\|SET/d;s/.*Query.//g'

    In the webbrowser already logged in and on /list-virtual.php?domain=&tab=alias I refreshed the page.

    Added semicolon to the statements and ran them with

    cat postfixadmin.sql | while read s; do printf '%s\n' "$s"; mysql postfix -NBe "$s"; done > postfixadmin-w-results.sql

    The resultsets from the statements are in the attachment. Most of them seem to be the expected (aliases are all there with details), though some of the statements are redundant.
    (Real domainname changed to example.com afterwards)

    That's as far as I have gotten in the debug process.

     
  • Harm Gerding

    Harm Gerding - 2014-05-30

    I have submitted a data dump by email
    with notes and examples.

     
  • VERSATECH SRL

    VERSATECH SRL - 2014-06-05

    We got this bug fixed.

    In file model/AliasHandler.php

    you should modify line 282 from:

        return parent::getList( "__is_mailbox IS NULL AND ( $condition )", $limit, $offset);
    

    to read instead:

        return parent::getList( "__mailbox_username IS NULL AND ( $condition )", $limit, $offset);
    

    It seems that __is_mailbox always returned 1 so the condition was not met.

     
    Last edit: VERSATECH SRL 2014-06-05
  • Ben

    Ben - 2014-06-06

    Yes, this fixed the issue! Great, thanks!

     
  • Harm Gerding

    Harm Gerding - 2014-06-06

    This also fixed it for me.

     
  • Christian Boltz

    Christian Boltz - 2014-06-06

    Thanks a lot for the fix and also to everybody who helped to debug this!

    I just commited the fix to SVN trunk r1678.

    It seems that __is_mailbox always returned 1 so the condition was not met.

    Can you (as in "someone who has seen this bug on his/her system) please check this directly in a mysql client? is_mailbox is used at various places, so it could cause some side effects if it's always 1...

    Can you please paste the result of the following query for a mailbox and an alias? (It's the query used by PostfixAdmin (with the fix already applied.)
    Note: you'll need to replace "example.com" with your domain two times).

    SELECT address,goto,coalesce(is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1' WHEN '0' THEN '0' END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable
    FROM prefix_alias LEFT JOIN ( SELECT 1 as
    is_mailbox, username as mailbox_username FROM prefix_mailbox WHERE username IS NOT NULL ) AS mailbox ON mailbox_username = address WHERE ( mailbox_username IS NULL AND ( domain='example.com' ) ) AND domain IN ('example.com');

    I'd also be interested in the result of the original query (also for a mailbox and an alias):

    SELECT address,goto,coalesce(is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1' WHEN '0' THEN '0' END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable
    FROM prefix_alias LEFT JOIN ( SELECT 1 as
    is_mailbox, username as mailbox_username FROM prefix_mailbox WHERE username IS NOT NULL ) AS mailbox ON mailbox_username = address WHERE ( is_mailbox IS NULL AND ( domain='example.com' ) ) AND domain IN ('example.com');

    If the mysql client tells you there were warnings, please also paste the result of
    show warnings;

    I'm still wondering why I didn't see this bug - hmm, maybe it's because I'm still using MySQL instead of MariaDB or maybe because of something in my my.cnf. Hints welcome ;-)

     
  • Harm Gerding

    Harm Gerding - 2014-06-06

    Tried this quickly and there is a problem with a table missing (prefix_alias)

    ERROR 1146 (42S02): Table 'postfix_example.prefix_alias' doesn't exist
    MariaDB [postfix_example]>
    MariaDB [postfix_example]> show tables;
    +-----------------------------+
    | Tables_in_postfix_example |
    +-----------------------------+
    | admin |
    | alias |
    | alias_domain |
    | config |
    | domain |
    | domain_admins |
    | fetchmail |
    | log |
    | mailbox |
    | quota |
    | quota2 |
    | vacation |
    | vacation_notification |
    +-----------------------------+
    13 rows in set (0.00 sec)

    I changed the name from myserver name to example.

    Harm

     
    Last edit: Harm Gerding 2014-06-06
  • Christian Boltz

    Christian Boltz - 2014-06-06

    sorry, I should have changed my custom table names (which I use to ensure $CONF[database_prefix] works) to the official ones :-/

    OK, second attemp:

    a) updated, working query:

        SELECT address,goto,coalesce(__is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1'    WHEN '0' THEN '0'   END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable 
        FROM alias  LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address  WHERE ( __mailbox_username IS NULL AND ( domain='example.com' ) )  AND  domain IN ('example.com');
    

    b) old, failing query:

        SELECT address,goto,coalesce(__is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1'    WHEN '0' THEN '0'   END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable 
        FROM alias  LEFT JOIN (  SELECT 1 as __is_mailbox, username as __mailbox_username  FROM mailbox WHERE username IS NOT NULL  ) AS __mailbox ON __mailbox_username = address  WHERE ( __is_mailbox IS NULL AND ( domain='example.com' ) )  AND  domain IN ('example.com');
    
     
  • Harm Gerding

    Harm Gerding - 2014-06-07

    Replaced real values with example etc... but here are the results, the second query returned an empty result set.

    SELECT address,goto,coalesce(is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1' WHEN '0' THEN '0' END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable
    FROM alias LEFT JOIN ( SELECT 1 as
    is_mailbox, username as mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS mailbox ON mailbox_username = address WHERE ( mailbox_username IS NULL AND ( domain='example.com' ) ) AND domain IN ('example.com');

    Current database: postfix_example

    +--------------------------------+-----------------------------------+------------+--------+---------+------------+---------------------+------------+---------------------+----------+
    | address | goto | is_mailbox | active | _active | created | _created | modified | _modified | editable |
    +--------------------------------+-----------------------------------+------------+--------+---------+------------+---------------------+------------+---------------------+----------+
    | root@example.com | test.name@example.com | 0 | 1 | JA | 30.03.2014 | 2014-03-30 01:18:07 | 30.03.2014 | 2014-03-30 01:22:16 | 1 |
    | testalias@example.com | test.name@example.com | 0 | 1 | JA | 10.05.2014 | 2014-05-10 04:45:31 | 10.05.2014 | 2014-05-10 04:45:31 | 1 |
    +--------------------------------+-----------------------------------+------------+--------+---------+------------+---------------------+------------+---------------------+----------+
    2 rows in set (0.01 sec)

    SELECT address,goto,coalesce(is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1' WHEN '0' THEN '0' END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable
    FROM alias LEFT JOIN ( SELECT 1 as
    is_mailbox, username as mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS mailbox ON mailbox_username = address WHERE ( is_mailbox IS NULL AND ( domain='example.com' ) ) AND domain IN ('example.com');

    MariaDB [postfix_example]> SELECT address,goto,coalesce(is_mailbox,0) as is_mailbox,CASE active WHEN '1' THEN '1' WHEN '0' THEN '0' END as active,CASE active WHEN '1' THEN 'JA' WHEN '0' THEN 'NEIN' END as _active,DATE_FORMAT(created, '%d.%m.%Y') AS created, created AS _created,DATE_FORMAT(modified, '%d.%m.%Y') AS modified, modified AS _modified,1 as editable
    -> FROM alias LEFT JOIN ( SELECT 1 as
    is_mailbox, username as mailbox_username FROM mailbox WHERE username IS NOT NULL ) AS mailbox ON mailbox_username = address WHERE ( is_mailbox IS NULL AND ( domain='example.com' ) ) AND domain IN ('example.com');
    Empty set (0.00 sec)

     
  • Christian Boltz

    Christian Boltz - 2014-06-07
    • status: open --> closed-fixed
     
  • Christian Boltz

    Christian Boltz - 2014-06-07

    Thanks, the result looks correct - so I can finally close this bug :-)

     

Log in to post a comment.