[SOLVED] MySQL on external server - no quota?

[BeNe]

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

[FeG]

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:

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 I've uploaded the corrected patch to ticket 1554.

Regards
FeG

[BeNe]

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

Greez BeNe

[zmediaguru]

(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!

[FeG]

(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' ...

[sci2tech]

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.

[FeG]

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 ( ) patch, which makes (besides the other changes) the two methods counting the same, as I've described above...

Regards
FeG

[sci2tech]

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

[FeG]

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/0...usage.html
- http://dev.mysql.com/doc/refman/5.1/en/s...tatus.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

Regards
FeG

[Zothos]

agreed