[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

RE: MySQL on external server - no quota? - BeNe - 10-09-2008 04:25 PM

The one is the disk quota that works with the limit 300MB included the MySQL DB and the upload of a file doesn´t work any more. But how to disable a "write function" in the DB for the user when the limit is full ? This mean if someone install a script that upload a files in the DB he can upload and upload and upload...

mhhh *damn*

Greez BeNe

RE: MySQL on external server - no quota? - FeG - 10-09-2008 06:46 PM

Hi,

Thanks, zmediaguru, for your response.. there is an index error in the ispcp-dsk-quota script .. so we were both right Wink

.. no conversion is needed, but the current script gives the wrong output.

The script uses the indices 5 and 7, but it has to be 6 and 8, so the patch for this file looks like this:

Code:

Index: ispcp-dsk-quota

===================================================================

--- ispcp-dsk-quota     (Revision 1365)

+++ ispcp-dsk-quota     (Arbeitskopie)

@@ -207,9 +207,9 @@

                        foreach(@$ref)

                        {

-                           my $data_length   = @$_[5];

+                           my $data_length   = @$_[6];

-                       my $index_length  = @$_[7];

+                       my $index_length  = @$_[8];

                                $size += $data_length + $index_length;

                        }

@@ -379,7 +379,7 @@

 }

-$rs = mysqldb_quota_by_dir_engine();

+$rs = mysqldb_quota_by_mysql_engine();

 if ($rs != 0) {

Thanks again for insisting on that Smile

I've uploaded the corrected patch to ticket 1554.

Regards
FeG

RE: MySQL on external server - no quota? - BeNe - 10-09-2008 06:51 PM

Thanks FeG for your quick Patch!
Thats exactly the way how Community can work Smile

Greez BeNe

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

(10-09-2008 06:46 PM)FeG Wrote: Hi,

Thanks, zmediaguru, for your response.. there is an index error in the ispcp-dsk-quota script .. so we were both right .. no conversion is needed, but the current script gives the wrong output.

The script uses the indices 5 and 7, but it has to be 6 and 8, so the patch for this file looks like this...

FeG - thanks for the patch! I've implemented that and it does indeed solve the problem. The funny thing is I had looked over those column numbers, and counted incorrectly as well. Great work!

RE: MySQL on external server - no quota? - FeG - 10-10-2008 04:43 PM

(10-10-2008 09:02 AM)zmediaguru Wrote: The funny thing is I had looked over those column numbers, and counted incorrectly as well. Great work!

Me too

I've thought 'Hmm.. I've counted that the data_length column is the 7th ... but hey - they'll know what they're doing' ... Wink

RE: MySQL on external server - no quota? - sci2tech - 10-11-2008 02:20 AM

The idea is good but the the result is not accurate. This method does not add size of .frm (than contain structure of table or output from a Create view commands) file witch usually is ~8k for each table in first case.

RE: MySQL on external server - no quota? - FeG - 10-11-2008 06:10 AM

Hi,

(10-11-2008 02:20 AM)sci2tech Wrote: The idea is good but the the result is not accurate. This method does not add size of .frm (than contain structure of table or output from a Create view commands) file witch usually is ~8k for each table in first case.

In fact, the method mysqldb_quota_by_dir_engine doesn't count the .frm files either. The differences are only caused by the db.opt file and the directory itself.
If you run this command:

Code:

du -csb --exclude=*.frm --exclude=db.opt /var/lib/mysql/DATABASE/* | tail -1

the calculated space will be the same as the output of the method mysqldb_quota_by_mysql_engine, which can be simulated on a shell like this:

Code:

echo "SHOW TABLE STATUS;" | mysql DATABASE | awk '{ sum += $7 + $9 } END { print sum }'

So I'd suppose to instead modify mysqldb_quota_by_dir_engine so that it ignores the size of db.opt and the directory by using the du - command shown above. In my opinion this is the right solution since the user doesn't really uses the size of db.opt and the directory itself with it's mysql db - so it would be correct to not count it.

I've just apploaded a third ( Wink

) patch, which makes (besides the other changes) the two methods counting the same, as I've described above...

Regards
FeG

RE: MySQL on external server - no quota? - sci2tech - 10-12-2008 02:05 AM

Your patch is added in r1367. Thank you and +1 to your reputation Smile

RE: [SOLVED] MySQL on external server - no quota? - FeG - 10-17-2008 12:56 AM

Thank you, sci2tech, for applying my patch.

I've found out, that there is a discrepance between what SHOW TABLE STATUS measures and what du measures when there are InnoDB tables.

See:
- http://arcterex.net/blog/archives/2008/08/20/finding_mysql_table_disk_usage.html
- http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

This is because MySQL uses shared tablespace to store InnoDB tables which means that disk usage != data size for these tables. On my server, there are databases where the difference are factor 50; the data size is much more than the disk usage shown by du.

I suppose to go on by using the mysqldb_quota_by_mysql_engine function as it is done in the current trunk, since phpMyAdmin also shows the data size given by SHOW TABLES STATUS; so I think this will be okay.

I hope the dev team agrees with my opinion Smile

Regards
FeG

RE: [SOLVED] MySQL on external server - no quota? - Zothos - 10-17-2008 07:44 PM

agreed Smile