[SOLVED] MySQL on external server - no quota?

[SOLVED] MySQL on external server - no quota? - Printable Version

+- ispCP - Board - Support (http://www.isp-control.net/forum)
+-- Forum: ispCP Omega Support Area (/forum-30.html)
+--- Forum: System Setup & Installation (/forum-32.html)
+--- Thread: [SOLVED] MySQL on external server - no quota? (/thread-4632.html)

Pages: 1 2

[SOLVED] MySQL on external server - no quota? - zmediaguru - 10-07-2008 05:42 AM

Hello,

I'm running MySQL on a dedicated SQL server with the /etc/ispcp/ispcp.conf configured for the remote MySQL host. The ISPCP control panel works properly, I am able to create and administer databases no problem on the external server.

The problem I've discovered is that the ISPCP quota script is looking for the actual MySQL data files on the local machine and counting disk usage using 'du' on these data files.

Has anyone worked on a modification to use a MySQL query replace the 'du' function for counting database size?

RE: MySQL on external server - no quota? - BeNe - 10-07-2008 04:39 PM

Yes, you are right!
This is a Problem/Bug however.
I don´t know a running Server with ispCP this time which count the DB.
Looks like we have to find a workaround.

Greez BeNe

RE: MySQL on external server - no quota? - FeG - 10-07-2008 05:51 PM

Hi,

(10-07-2008 05:42 AM)zmediaguru Wrote: Has anyone worked on a modification to use a MySQL query replace the 'du' function for counting database size?

This is already implemented in the ispCP engine if I'm right. You have to change in /var/www/ispcp/engine/quota/ispcp-dsk-quota the line 370:

Code:

$rs = mysqldb_quota_by_dir_engine();

Code:

$rs = mysqldb_quota_by_mysql_engine();

By doing so you switch from retrieving the space used by mysql-dbs from the 'du'-procedure to a method depending only on mysql queries.

I haven't tested this since I don't use another MySQL server, but IMHO it should work.

Regards
FeG

RE: MySQL on external server - no quota? - Zothos - 10-08-2008 12:39 AM

could you please create a ticket with a bug report attached and a patch for the problem? Smile

RE: MySQL on external server - no quota? - FeG - 10-08-2008 12:55 AM

Hi,

(10-08-2008 12:39 AM)Zothos Wrote: could you please create a ticket with a bug report attached and a patch for the problem?

I've created the following ticket (directly in Milestone 1.0.0) and added a (non-standard Wink

) patch, as described above already.

Regards
FeG

RE: MySQL on external server - no quota? - zmediaguru - 10-08-2008 06:47 AM

(10-07-2008 05:51 PM)FeG Wrote: This is already implemented in the ispCP engine if I'm right. You have to change in /var/www/ispcp/engine/quota/ispcp-dsk-quota the line 370:

Code:

$rs = mysqldb_quota_by_dir_engine();

to

Code:

$rs = mysqldb_quota_by_mysql_engine();

By doing so you switch from retrieving the space used by mysql-dbs from the 'du'-procedure to a method depending only on mysql queries.

Perfect! This is exactly what I was looking for. One quick note - the table status Data_length and Index_length values used by this functions appear to be returning data size in bits, and need to be converted to MB in /var/www/ispcp/engine/quota/ispcp-dsk-quota the line 200:

Code:

$size += $data_length + $index_length;

Code:

$size += ($data_length / 1073741824) + ($index_length / 1073741824);

# 1073741824 = 1024 * 1024 * 1024 - convert bits to MB

Otherwise disk quota counts are WAY too high.

Note: I attempted to post a reply to this effect on the Ticket, but was rejected as 'possible spam'.

RE: MySQL on external server - no quota? - victor531 - 10-08-2008 11:24 AM

Hi, I've tried to check this patch in my Rc4 + debian 4.0 but with full space used, still can with a phpbb forum works.

my changes
$rs = mysqldb_quota_by_mysql_engine();
$size += ($data_length / 1073741824) + ($index_length / 1073741824);

any ideas ?

RE: MySQL on external server - no quota? - FeG - 10-08-2008 07:06 PM

Hi,

(10-08-2008 06:47 AM)zmediaguru Wrote: One quick note - the table status Data_length and Index_length values used by this functions appear to be returning data size in bits, and need to be converted to MB

This is not correct. Yes, they are in bytes, but the disk usage is calculated in bytes, too and the column 'domain_disk_usage' in the table 'domain' stores it's value as bytes.

So no conversion is needed here.

victor531 Wrote:I've tried to check this patch in my Rc4 + debian 4.0 but with full space used, still can with a phpbb forum works.

Sorry, I don't understand your question.. Rolleyes

what do you mean with "still can with a phpbb forum works" ?

Regards
FeG

RE: MySQL on external server - no quota? - zmediaguru - 10-09-2008 02:22 AM

(10-08-2008 07:06 PM)FeG Wrote: Hi,

(10-08-2008 06:47 AM)zmediaguru Wrote: One quick note - the table status Data_length and Index_length values used by this functions appear to be returning data size in bits, and need to be converted to MB
This is not correct. Yes, they are in bytes, but the disk usage is calculated in bytes, too and the column 'domain_disk_usage' in the table 'domain' stores it's value as bytes.

So no conversion is needed here.

If no conversion is needed, then, how come then with no conversion added to the script, the Quota count is returning over a Petabyte of storage space used (see attachment ) where actual disk space used is roughly 6.7GB as reported by 'du'??

Here is the quota calculation with my conversion in place

RE: MySQL on external server - no quota? - victor531 - 10-09-2008 12:00 PM

Hi,
excuse me, the thing is that I have a domain with disk space of 300 MB in our server, now its full and with filezilla is not possible to upload anything ( shows: disk usage complete 300 Mb of 300 MB).
But this domain has a forum (PHPBB+MySQL DB) and this forum still accepts posts and works without problem,with this, the disk usage grows up over the 300 Mb. ( at least 350 MB today)

Any ideas ?

thanks in advance

victor