Limit mysql database size

Discussion in 'Feature Requests' started by ddelbia, Jan 6, 2006.

  1. ddelbia

    ddelbia Member

    It would be nice to have a quota for mysql databases... I know that some ISP (cpanel?) do it.
    Is it possible?
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    I think there is no such function in mySQL. I guess they implemented it with a cronjob that disables the mySQL database if the size exceeds xx MB.
     
  3. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    I found this script on the net:

    Code:
    #!/usr/bin/php -q
    <?PHP
    
    /*
     * MySQL quota script
     * written by Sebastian Marsching
     *
     */
    
    /*
        This program is free software; you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation; either version 2 of the License, or
        (at your option) any later version.
        
        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.
        
        You should have received a copy of the GNU General Public License
        along with this program; if not, write to the Free Software
        Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
    */
    
    
    /*
     * Create table for quota data with the following statement:
     *
     * CREATE TABLE `Quota` (`Db` CHAR(64) NOT NULL, 
     * `Limit` BIGINT NOT NULL,
     * `Exceeded` ENUM('Y','N') DEFAULT 'N' NOT NULL,
     * PRIMARY KEY (`Db`), UNIQUE (`Db`));
     *
     * The field 'db' stores the information for which database
     * you want to limit the size.
     * The field 'limit' is the size limit in bytes.
     * The field 'exceeded' is only used internally and must be
     * initialized with 'N'.
     */
     
    /*
     * Settings
     */
     
    $mysql_host  = 'localhost';
    $mysql_user  = 'root'; // Do NOT change, root-access is required
    $mysql_pass  = '';
    $mysql_db    = 'quotadb'; // Not the DB to check, but the db with the quota table
    $mysql_table = 'quota';
    
    /*
     * Do NOT change anything below
     */
     
    $debug = 0;
    
    // Connect to MySQL Server
    
    if (!mysql_connect($mysql_host, $mysql_user, $mysql_pass))
    {
     echo "Connection to MySQL-server failed!";
     exit;
    }
    
    // Select database
    
    if (!mysql_select_db($mysql_db))
    {
     echo "Selection of database $mysql_db failed!";
     exit;
    }
    
    // Check quota for each entry in quota table
    
    $sql = "SELECT * FROM $mysql_table;";
    $result = mysql_query($sql);
    
    while ($row = mysql_fetch_array($result))
    {
     $quota_db = $row['db'];
     $quota_limit = $row['limit'];
     $quota_exceeded = ($row['exceeded']=='Y') ? 1 : 0;
     
     if ($debug)
      echo "Checking quota for '$quota_db'...\n";
     
     $qsql = "SHOW TABLE STATUS FROM $quota_db;";
     $qresult = mysql_query($qsql);
     
     if ($debug)
      echo "SQL-query is \"$qsql\"\n";
     
     $quota_size = 0;
     
     while ($qrow = mysql_fetch_array($qresult))
     {
      if ($debug)
      { echo "Result of query:\n"; var_dump($qrow); }
      $quota_size += $qrow['Data_length'] + $qrow['Index_length'];
     }
     
     if ($debug)
      echo "Size is $quota_size bytes, limit is $quota_limit bytes\n";
     
     if ($debug && $quota_exceeded)
      echo "Quota is marked as exceeded.\n";
     if ($debug && !$quota_exceeded)
      echo "Quota is not marked as exceeded.\n";
     
     if (($quota_size > $quota_limit) && !$quota_exceeded)
     {
      if ($debug)
       echo "Locking database...\n";
      // Save in quota table  
      $usql = "UPDATE $mysql_table SET exceeded='Y' WHERE db='$quota_db';";
      mysql_query($usql);
      if ($debug)
       echo "Querying: $usql\n";
      // Dismiss CREATE and INSERT privilege for database
      mysql_select_db('mysql');
      $usql = "UPDATE db SET Insert_priv='N', Create_priv='N' WHERE Db='$quota_db';";
      mysql_query($usql);
      if ($debug)
       echo "Querying: $usql\n";
      mysql_select_db($mysql_db);
     }
     
     if (($quota_size <= $quota_limit) && $quota_exceeded) 
     {
      if ($debug)
       echo "Unlocking database...\n";
      // Save in quota table
      $usql = "UPDATE $mysql_table SET exceeded='N' WHERE db='$quota_db';";
      mysql_query($usql);
      if ($debug)
       echo "Querying: $usql\n";
      // Grant CREATE and INSERT privilege for database
      mysql_select_db('mysql');
      $usql = "UPDATE db SET Insert_priv='Y', Create_priv='Y' WHERE Db='$quota_db';";
      mysql_query($usql);
      if ($debug)
       echo "Querying: $usql\n";
      mysql_select_db($mysql_db);
     }
    }
    
    ?>
    
    Maybe that helps?

    I suppose that can't be too hard to implement for ISPConfig. I'll look over it this afternoon, hopefully I already can post a working solution tomorrow.

    I'm planning to change the script to enable dynamic database sizes per customer, that means counting the total amount of data of a user's databases and setting the "quota exceeded" flag for all these databases if the sum of file usage and database usage exceeds the site's total quota limit.

    I currently don't know which group the database files have set, but maybe I'll simplify the whole thing and change the group of the database's files to the site's group in a cron script. This shouldn't prevent writing to the database physically, but will make quota calculation much easier - and this way the database size won't be ignored when uploading files.

    Please comment my plannings, maybe you have additional ideas?
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    This looks practicle.

    I wont use the extra databases used in the script as we have already databases where we can store the values.

    You can add the quota exceeded field to the table isp_isp_datenbank. I recommend to add the field that contains the overall quota of the databases to the isp_isp_web table, so you can set it like the other web quotas.
     
  5. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    Mhm... makes sense. But: If I do it your way, then the database quota is independant from web quota, isn't it? That alone doesn't hurt, but will I this way have the possibility to limit the total database space my resellers can give to their customers? Or would I have to make bigger changes?

    Btw, if you're planning to integrate the script into ISPConfig, then I'd love to use its databases. Otherwise I'd prefer to use a seperate db, because I don't want to fall into trouble when I'm upgrading ;)
     
  6. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    In the meanwhile I have investigated it myself. There won't be many changes, but some will have to be done. But this only makes sense if you're really interested in including a provided patch to ISPConfig ;)
     
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    Sure we will integrate a patch in ISPConfig :) We can integrate this patch only if it uses the ISPConfig tables as i described above. A dependency to an external database wont work well as pople will fail to setup it correctly.

    I think it is not nescessary to add new tables at all as ISPConfig already has a table for the databases where you can add the nescessary field easily.
     
  8. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    Okay, then I'll use ispconfig database tables. And I agree with you that there won't be the need of an additional table.

    I'll also patch the code to enable changing the quota value directly in ispconfig for webs and resellers. That means the quota will be calculated per web, not per database. I think this should be sufficient.

    One question is left: I have to add the new fields to the sql table. Therefore my first idea was to patch the sql script responsible for setting up the database when installing. But: I don't know what I have to do to make it compatible when someone upgrades... Please give me a hint ;)
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    Its sufficient to update this database dump. The upgrade uses this dump to setup a new database and imports the old data afterwards.
     
  10. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    As promised I wrote the patch, tested it, and it seems to work.
    You can enter the max. amount of sql space available for resellers, who can make parts of their space available for webs under their account.
    Statistics are shown both in the web's statistic tab and in the reseller's overview page.
    German and english translations are included, other languages' strings are currently shown in english.

    As the patch is to big to attach it here, I sent it to dev [at] ispconfig [dot] org.

    I hope it's useful and will be soon integrated as it's been a lot of work ;)
    I tried to change as few as possible, and tried to keep the programming style of the changed files.

    Btw, the patch was built against a fresh and clean unpacked version of ISPConfig 2.2.1
     
    Last edited: Apr 23, 2006
  11. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    I forgot to mention:
    The database update script contained in the patch is a complete rewrite, as the script presented above wasn't the best base to extend.
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    Thanks for the patch :)

    I just tried to apply it to an freshly unpacked ISPConfig 2.2.1 and got some errors:

    Maybe the patch was corrupted in the email. Can you make a tar.gz or .zip file from the patch and send it to the dev@... email address again? Have you tried to apply the patch, does it apply correctly on your server?
     
  13. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    At my server it correctly applies... but I'll try it again in a few minutes, maybe there really got something corrupted. Could you send me some of the .rej-files that were produced?
     
  14. oliver.blaha

    oliver.blaha ISPConfig Developer ISPConfig Developer

    Tried again and it applies without any problems.
    I directly took version 2.2.1 from the server:

    Code:
    wget http://mesh.dl.sourceforge.net/sourceforge/ispconfig/ISPConfig-2.2.1.tar.gz
    tar xzvf ISPConfig-2.2.1.tar.gz
    cd install_ispconfig
    patch -p1 < ../ISPConfig-2.2.1-MySQL-Quota.diff
    
     
    Jemt likes this.
  15. till

    till Super Moderator Staff Member ISPConfig Developer

    The patch is now in SVN, thanks to Oliver.
     
    Jemt likes this.

Share This Page