Limit mysql database size

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

 

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?

 

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

 

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

 

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

 

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

 

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.

 

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?

 

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