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 offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 36,368
Thanks: 833
Thanked 5,477 Times in 4,313 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 offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 36,368
Thanks: 833
Thanked 5,477 Times in 4,313 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
  #7  
Old 21st April 2006, 19:10
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 36,368
Thanks: 833
Thanked 5,477 Times in 4,313 Posts
Default

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

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
Reply With Quote
  #9  
Old 21st April 2006, 19:25
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lneburg, Germany
Posts: 36,368
Thanks: 833
Thanked 5,477 Times in 4,313 Posts
Default

Quote:
Originally Posted by oliver.blaha
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
Its sufficient to update this database dump. The upgrade uses this dump to setup a new database and imports the old data afterwards.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #10  
Old 23rd April 2006, 16:12
oliver.blaha oliver.blaha is offline
Member
 
Join Date: Apr 2006
Posts: 37
Thanks: 0
Thanked 1 Time in 1 Post
 
Default

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 by oliver.blaha; 23rd April 2006 at 16:18.
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 06:30.


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