Proftpd+Mysql Quota Problem

Discussion in 'HOWTO-Related Questions' started by DeeJay, Dec 27, 2006.

  1. DeeJay

    DeeJay New Member

    Hello.
    First thanks for the great guides, I have read and used alot of them and especially the Postfix+Courier guide.

    Now, my problem right now is that I used the guide http://www.howtoforge.com/proftpd_mysql_virtual_hosting to setup a FTP, and trust me it works great besides one thing, it does not increase how many bytes a user has transfered or anything, every value in ftpquotatallies is 0 (besides user which is my username and quota_type which says user) and I have tried alot of different things without success. My proftpd.conf is pasted bellow (Removed my passwords)

    Code:
    
    # /etc/proftpd.conf -- This is a basic ProFTPD configuration file.
    # To really apply changes reload proftpd after modifications.
    #
    
    ServerName                      "Nequam FTP"
    ServerType                      standalone
    DeferWelcome                    off
    
    MultilineRFC2228                on
    DefaultServer                   on
    ShowSymlinks                    on
    
    TimeoutNoTransfer               600
    TimeoutStalled                  600
    TimeoutIdle                     1200
    
    DisplayLogin                    welcome.msg
    DisplayFirstChdir               .message
    ListOptions                     "-l"
    
    DenyFilter                      \*.*/
    
    # Port 21 is the standard FTP port.
    Port                            21
    
    MaxInstances                    30
    
    User                            nobody
    Group                           nogroup
    
    Umask                           022  022
    AllowOverwrite                  on
    
    DefaultRoot ~
    
    
    # The passwords in MySQL are encrypted using CRYPT
    SQLAuthTypes            Plaintext Crypt
    SQLAuthenticate         users* groups*
    
    
    # used to connect to the database
    # [email protected] database_user user_password
    SQLConnectInfo  [email protected] proftpd XXXXX
    SQLUserInfo     ftpuser userid passwd uid gid homedir shell
    
    # Here we tell ProFTPd the names of the database columns in the "grouptable"
    # we want it to interact with. Again the names match with those in the db
    SQLGroupInfo    ftpgroup groupname gid members
    
    # set min UID and GID - otherwise these are 999 each
    SQLMinID        500
    
    # create a user's home directory on demand if it doesn't exist
    SQLHomedirOnDemand on
    
    # Update count every time user logs in
    SQLLog PASS updatecount
    SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser
    
    # Update modified everytime user uploads or deletes a file
    SQLLog  STOR,DELE modified
    SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
    
    # User quotas
    # ===========
    QuotaEngine on
    QuotaDirectoryTally on
    QuotaDisplayUnits Mb
    QuotaShowQuotas on
    
    SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_ava$
    SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE n$
    SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = fi$
    SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies
    SQLNamedQuery gettally  SELECT "ROUND((bytes_in_used/1048576),2) FROM ftpquotatallies WHERE name='%u'"
    SQLNamedQuery getlimit  SELECT "ROUND((bytes_in_avail/1048576),2) FROM ftpquotalimits WHERE name='%u'"
    SQLNamedQuery getfree   SELECT "ROUND(((ftpquotalimits.bytes_in_avail-ftpquotatallies.bytes_in_used)/1048576),2) FROM ftpquotalimits,ftpquotatallies WHERE ftpquotalimits.name = '%$
    
    SQLShowInfo   LIST    "226" "Used %{gettally}MB from %{getlimit}MB. You have %{getfree}MB available space."
    
    QuotaLimitTable sql:/get-quota-limit
    QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally
    
    RootLogin on
    RequireValidShell off
    
    Example of what i mean, by it's not increasing:
    Code:
    Transferred 1 file totaling 5,49 MB in 1,86 second (10,99 MB/s)
    [R] 226 Used 0.00MB from 10.00MB. You have 10.00MB available space.
    
    And 'netstat -tap' (if needed) gives me:
    Code:
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 localhost.localdo:10024 *:*                     LISTEN     10827/amavisd (mast
    tcp        0      0 localhost.localdo:10025 *:*                     LISTEN     2368/master
    tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     2179/mysqld
    tcp        0      0 *:sunrpc                *:*                     LISTEN     1581/portmap
    tcp        0      0 *:882                   *:*                     LISTEN     2396/rpc.statd
    tcp        0      0 *:ftp                   *:*                     LISTEN     11315/proftpd: (acc
    tcp        0      0 *:3000                  *:*                     LISTEN     11440/eggdrop
    tcp        0      0 *:smtp                  *:*                     LISTEN     2368/master
    tcp        0      0 10.0.0.6:34672          London.UK.EU.under:ircd ESTABLISHED11440/eggdrop
    tcp        0      0 10.0.0.6:ftp            10.0.0.40:1559          ESTABLISHED11338/proftpd: test
    tcp        0      0 localhost.localdo:34693 localhost.localdo:mysql TIME_WAIT  -
    tcp        0      0 localhost.localdo:34692 localhost.localdo:mysql TIME_WAIT  -
    tcp        0      0 localhost.localdo:34701 localhost.localdo:mysql ESTABLISHED11406/cleanup
    tcp        0      0 localhost.localdo:34702 localhost.localdo:mysql ESTABLISHED11406/cleanup
    tcp        0      0 localhost.localdo:mysql localhost.localdo:34701 ESTABLISHED2179/mysqld
    tcp        0      0 localhost.localdo:mysql localhost.localdo:34702 ESTABLISHED2179/mysqld
    tcp        0      0 localhost.localdo:10025 localhost.localdo:34700 TIME_WAIT  -
    tcp6       0      0 *:imaps                 *:*                     LISTEN     2093/couriertcpd
    tcp6       0      0 *:pop3s                 *:*                     LISTEN     2114/couriertcpd
    tcp6       0      0 *:pop3                  *:*                     LISTEN     2101/couriertcpd
    tcp6       0      0 *:imap2                 *:*                     LISTEN     2076/couriertcpd
    tcp6       0      0 *:www                   *:*                     LISTEN     6755/apache2
    tcp6       0      0 *:auth                  *:*                     LISTEN     2229/oidentd
    tcp6       0      0 *:ssh                   *:*                     LISTEN     2392/sshd
    tcp6       0      0 *:smtp                  *:*                     LISTEN     2368/master
    tcp6       0    740 ::ffff:10.0.0.6:ssh     ::ffff:10.0.0.40:3308   ESTABLISHED2518/0
    
    Also, no mysql-errors or anything.
     
    Last edited: Dec 27, 2006
  2. DeeJay

    DeeJay New Member

    Anyone? Please.
     
  3. falko

    falko Super Moderator ISPConfig Developer

    Does your user exist in both the ftpquotalimits and the ftpuser tables?

    Do you have this in your /etc/proftpd.conf?

    Code:
    DefaultRoot ~
    
    
    # The passwords in MySQL are encrypted using CRYPT
    SQLAuthTypes            Plaintext Crypt
    SQLAuthenticate         users* groups*
    
    
    # used to connect to the database
    # [email protected] database_user user_password
    SQLConnectInfo  [email protected] proftpd password
    
    
    # Here we tell ProFTPd the names of the database columns in the "usertable"
    # we want it to interact with. Match the names with those in the db
    SQLUserInfo     ftpuser userid passwd uid gid homedir shell
    
    # Here we tell ProFTPd the names of the database columns in the "grouptable"
    # we want it to interact with. Again the names match with those in the db
    SQLGroupInfo    ftpgroup groupname gid members
    
    # set min UID and GID - otherwise these are 999 each
    SQLMinID        500
    
    # create a user's home directory on demand if it doesn't exist
    SQLHomedirOnDemand on
    
    # Update count every time user logs in
    SQLLog PASS updatecount
    SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser
    
    # Update modified everytime user uploads or deletes a file
    SQLLog  STOR,DELE modified
    SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser
    
    # User quotas
    # ===========
    QuotaEngine on
    QuotaDirectoryTally on
    QuotaDisplayUnits Mb
    QuotaShowQuotas on
    
    SQLNamedQuery get-quota-limit SELECT "name, quota_type, per_session, limit_type, bytes_in_avail, bytes_out_avail, bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
    
    SQLNamedQuery get-quota-tally SELECT "name, quota_type, bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = '%{0}' AND quota_type = '%{1}'"
    
    SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = files_in_used + %{3}, files_out_used = files_out_used + %{4}, files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND quota_type = '%{7}'" ftpquotatallies
    
    SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, %{4}, %{5}, %{6}, %{7}" ftpquotatallies
    
    QuotaLimitTable sql:/get-quota-limit
    QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert-quota-tally
    
    RootLogin off
    RequireValidShell off
     
  4. DeeJay

    DeeJay New Member

    Thanks for the answer, even tho I've been hold up by some work - The problem was that 'per_session' in the quotalimits was setted to TRUE, when i setted it to FALSE the quota limits was active. So it works smoothly, thanks.
     

Share This Page