Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 2 > Feature Requests

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 6th January 2006, 14:31
ddelbia ddelbia is offline
Member
 
Join Date: Dec 2005
Posts: 56
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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?
Reply With Quote
Sponsored Links
  #2  
Old 6th January 2006, 14:38
till till is online now
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,748
Thanks: 821
Thanked 5,328 Times in 4,181 Posts
Default

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.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 21st April 2006, 05:09
oliver.blaha oliver.blaha is offline
Member
 
Join Date: Apr 2006
Posts: 37
Thanks: 0
Thanked 1 Time in 1 Post
Lightbulb

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?
Reply With Quote
  #4  
Old 21st April 2006, 10:05
till till is online now
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,748
Thanks: 821
Thanked 5,328 Times in 4,181 Posts
Default

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.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #5  
Old 21st April 2006, 13:05
oliver.blaha oliver.blaha is offline
Member
 
Join Date: Apr 2006
Posts: 37
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6  
Old 21st April 2006, 17:52
oliver.blaha oliver.blaha is offline
Member
 
Join Date: Apr 2006
Posts: 37
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Client Website MySQL Database Names Lister Installation/Configuration 4 12th January 2008 17:21
Mail System doesnt work! crichton Installation/Configuration 3 17th September 2007 23:49
File size limit exceeded ? igorj General 4 16th March 2006 11:41
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04
Problem installing ISPConfig, then with MySQL... ctroyp Installation/Configuration 7 26th September 2005 16:37


All times are GMT +2. The time now is 11:21.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.