1 Topic by lmatys (edited by lmatys 2017-07-11 03:01:55)

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Topic: Number of users at dashboard vs database

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.6
- Linux/BSD distribution name and version:  CentOS Linux release 7.3.1611 (Core)
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro? Yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hi.
When logging into iRedAdmin-Pro, I can observe 1985 users at dashboard.
But when I ask the database I get:

MariaDB [vmail]> select * from mailbox;
.some.data.
1998 rows in set (0.00 sec)
MariaDB [vmail]>

MariaDB [vmail]> select address from alias;
.some.data.
3044 rows in set (0.00 sec)
MariaDB [vmail]>

At the dashboard:

"Number of users    1993
Number of aliases    1046"

What is the cause of that situation?
Regards.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Number of users at dashboard vs database

Did you ever create mail users/alias manually (without iRedAdmin-Pro)?

3 Reply by lmatys

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Re: Number of users at dashboard vs database

ZhangHuangbin wrote:

Did you ever create mail users/alias manually (without iRedAdmin-Pro)?

Yes, I did a lot directly into database.
How can we fix it?

Regards.

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Number of users at dashboard vs database

lmatys wrote:

Yes, I did a lot directly into database.

Please compare your SQL command used to create accounts with script shipped in iRedMail: iRedMail-0.9.6/tools/create_mail_user_*

5 Reply by lmatys

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Re: Number of users at dashboard vs database

ZhangHuangbin wrote:
lmatys wrote:

Yes, I did a lot directly into database.

Please compare your SQL command used to create accounts with script shipped in iRedMail: iRedMail-0.9.6/tools/create_mail_user_*

I did use this script.
What can we check?

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Number of users at dashboard vs database

Did you ever create mail accounts with SQL command line manually (not use the script "create_mail_user_*")?

Also, try this:

- Find one user which is not displayed in iRedAdmin-Pro
- Find one user which is correctly displayed in iRedAdmin-Pro
- Compare the SQL records of them in both "vmail.mailbox" and "vmail.alias" tables

7 Reply by lmatys

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Re: Number of users at dashboard vs database

ZhangHuangbin wrote:

Did you ever create mail accounts with SQL command line manually (not use the script "create_mail_user_*")?

Also, try this:

- Find one user which is not displayed in iRedAdmin-Pro
- Find one user which is correctly displayed in iRedAdmin-Pro
- Compare the SQL records of them in both "vmail.mailbox" and "vmail.alias" tables

Hi Zhang.
Generally yes, at the beginning of the migration I did some test with creating and deleting mailboxes and aliases, but I'm close to be sure that very carefully and the cleanup was done.

Currently I have,

MariaDB [vmail]> select name from mailbox;
...
1998 rows in set (0.00 sec)

MariaDB [vmail]>

MariaDB [vmail]> select address from alias;
...
3044 rows in set (0.00 sec)

MariaDB [vmail]>

How to find an account that can be misconfigured?
Best regards.

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Number of users at dashboard vs database

Try these SQL commands (for iRedMail-0.9.6):

select count(*) from mailbox;
select count(*) from alias where islist=1;
select count(*) from alias;

1st command should return same number as 2nd one.
and 1st + 2nd should equal to 3rd one.

9 Reply by lmatys

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Re: Number of users at dashboard vs database

ZhangHuangbin wrote:

Try these SQL commands (for iRedMail-0.9.6):

select count(*) from mailbox;
select count(*) from alias where islist=1;
select count(*) from alias;

1st command should return same number as 2nd one.
and 1st + 2nd should equal to 3rd one.

Please take a look at this,

MariaDB [vmail]> select count(*) from mailbox;
+----------+
| count(*) |
+----------+
|     2010 |
+----------+
1 row in set (0.01 sec)

MariaDB [vmail]> select count(*) from alias where islist=1;
+----------+
| count(*) |
+----------+
|     1046 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]> select count(*) from alias;
+----------+
| count(*) |
+----------+
|     3056 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]>

Something is wrong.

For each account we are going to migrate, we have generated the mailbox.sql from the create_mail_user_SQL.sh  script.
In effect, the create_mail_user_SQL.sh  script generated the lines above for each account like this:

INSERT INTO mailbox (username, password, name,
                     storagebasedirectory,storagenode, maildir,
                     quota, domain, active, local_part, created)
             VALUES ('user1@domain.com', '{SSHA512}qfDiqsQSdgt6wRLuHlUDm2fkv6K81GLZ/2jMgEsXgPX+qkjuRS7Eq0pSvIM0ygQdL/InDnOfAtT8VwtlmNHK5flNR89FTPwP', 'user1',
                     '/var/vmail','vmail1', 'domain.com/u/s/e/user1-2017.05.24.22.14.23/',
                     '0', 'domain.com', '1','user1', NOW());
INSERT INTO alias (address, goto, domain, created, active)
           VALUES ('user1@domain.com', 'user1@domain.com','domain.com', NOW(), 1);

After that, we've checked the additional aliases at our database, and did for each one the record:

'insert into alias (address, goto, domain, islist) values ('user10@domain.com', 'user11@domain.com', 'domain.com', 1);

Can you help us, or should we ask for the charge support?
Regards.

10 Reply by lmatys

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Re: Number of users at dashboard vs database

lmatys wrote:
ZhangHuangbin wrote:

Try these SQL commands (for iRedMail-0.9.6):

select count(*) from mailbox;
select count(*) from alias where islist=1;
select count(*) from alias;

1st command should return same number as 2nd one.
and 1st + 2nd should equal to 3rd one.

Please take a look at this,

MariaDB [vmail]> select count(*) from mailbox;
+----------+
| count(*) |
+----------+
|     2010 |
+----------+
1 row in set (0.01 sec)

MariaDB [vmail]> select count(*) from alias where islist=1;
+----------+
| count(*) |
+----------+
|     1046 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]> select count(*) from alias;
+----------+
| count(*) |
+----------+
|     3056 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]>

Something is wrong.

For each account we are going to migrate, we have generated the mailbox.sql from the create_mail_user_SQL.sh  script.
In effect, the create_mail_user_SQL.sh  script generated the lines above for each account like this:

INSERT INTO mailbox (username, password, name,
                     storagebasedirectory,storagenode, maildir,
                     quota, domain, active, local_part, created)
             VALUES ('user1@domain.com', '{SSHA512}qfDiqsQSdgt6wRLuHlUDm2fkv6K81GLZ/2jMgEsXgPX+qkjuRS7Eq0pSvIM0ygQdL/InDnOfAtT8VwtlmNHK5flNR89FTPwP', 'user1',
                     '/var/vmail','vmail1', 'domain.com/u/s/e/user1-2017.05.24.22.14.23/',
                     '0', 'domain.com', '1','user1', NOW());
INSERT INTO alias (address, goto, domain, created, active)
           VALUES ('user1@domain.com', 'user1@domain.com','domain.com', NOW(), 1);

After that, we've checked the additional aliases at our database, and did for each one the record:

'insert into alias (address, goto, domain, islist) values ('user10@domain.com', 'user11@domain.com', 'domain.com', 1);

Can you help us, or should we ask for the charge support?
Regards.

I've checked again and maybe everything is ok, because not all the aliases are lists.

MariaDB [vmail]> select count(*) from mailbox;
+----------+
| count(*) |
+----------+
|     2010 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]> select count(*) from alias where islist=0;
+----------+
| count(*) |
+----------+
|     2010 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]> select count(*) from alias where islist=1;
+----------+
| count(*) |
+----------+
|     1046 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]> select count(*) from alias;
+----------+
| count(*) |
+----------+
|     3056 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]>

Can you comment that?
Regards.

11 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Number of users at dashboard vs database

*) in iRedMail-0.9.6 and earlier / older releases, mail users are stored in "vmail.mailbox", also a copy in "vmail.alias".
*) in iRedMail-0.9.7, mail users are stored in "vmail.mailbox", also a copy in "vmail.forwardings".

12 Reply by lmatys (edited by lmatys 2017-07-22 17:39:54)

  • lmatys
  • Member
  • Offline
  • Registered: 2017-05-25
  • Posts: 17

Re: Number of users at dashboard vs database

ZhangHuangbin wrote:

*) in iRedMail-0.9.6 and earlier / older releases, mail users are stored in "vmail.mailbox", also a copy in "vmail.alias".
*) in iRedMail-0.9.7, mail users are stored in "vmail.mailbox", also a copy in "vmail.forwardings".

Hi Zhang.
So I would like to come back to my main question.

At the irm dashboard I can see:
Number of users    2005

At the database:
MariaDB [vmail]> select count(*) from mailbox;
+----------+
| count(*) |
+----------+
|     2010 |
+----------+
1 row in set (0.00 sec)

MariaDB [vmail]>

Should be the numbers of users from the dashboard equal to number of rows from the vmail.mailbox ?
Regards.