Limit mysql database size

[ddelbia]

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

[till]

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.

[oliver.blaha]

I found this script on the net:


#!/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?

[till]

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.

[oliver.blaha]

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 ;)

[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 ;)

[till]

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.

[oliver.blaha]

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 ;)

[till]

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.

[oliver.blaha]

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

[oliver.blaha]

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.

[till]

Btw, the patch was built against a fresh and clean unpacked version of ISPConfig 2.2.1

Thanks for the patch :)

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

(Stripping trailing CRs from patch.)
patching file config.inc.php.tmp
(Stripping trailing CRs from patch.)
patching file db_ispconfig.sql
(Stripping trailing CRs from patch.)
patching file install.php
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/classes/ispconfig_isp_reseller.lib.php
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/classes/ispconfig_isp_web.lib.php
Hunk #1 FAILED at 85.
Hunk #2 FAILED at 129.
Hunk #3 FAILED at 179.
Hunk #4 FAILED at 282.
Hunk #5 FAILED at 652.
Hunk #6 FAILED at 746.
6 out of 6 hunks FAILED -- saving rejects to file ispconfig/lib/classes/ispconfig_isp_web.lib.php.rej
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/help/de_1013.hlp
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/help/de_1022.hlp
Hunk #1 FAILED at 28.
1 out of 1 hunk FAILED -- saving rejects to file ispconfig/lib/help/de_1022.hlp.rej
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/help/en_1013.hlp
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/help/en_1022.hlp
Hunk #1 FAILED at 28.
1 out of 1 hunk FAILED -- saving rejects to file ispconfig/lib/help/en_1022.hlp.rej
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/plugins/check_webspace.plugin.php
Hunk #1 FAILED at 83.
Hunk #2 FAILED at 112.
Hunk #3 FAILED at 195.
3 out of 3 hunks FAILED -- saving rejects to file ispconfig/lib/plugins/check_webspace.plugin.php.rej
(Stripping trailing CRs from patch.)
patching file ispconfig/lib/plugins/reseller_stats.plugin.php
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/frame_start.php
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/de.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/en.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/es.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/fr.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/it.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/nl.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/pl.lng
(Stripping trailing CRs from patch.)
patching file ispconfig/web/isp_manager/lib/lang/se.lng
(Stripping trailing CRs from patch.)
patching file scripts/shell/mysql_quota.php
(Stripping trailing CRs from patch.)
patching file uninstall


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?

[oliver.blaha]

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?

[oliver.blaha]

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


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

[till]

The patch is now in SVN, thanks to Oliver.