Well if MySQL uses TMPDIR only for the temporary tables, but uses the current data directory for repairing the tables where the .TMD files will become double in size, then it does sound like you are stuck.
If I had to manage it, and wanted automatic recovery, or at least not have to get involved with a client recovering their database, then I would set the hard_limit to be twice or three times the soft_limit. Users would be allowed to go over the soft_limit for only X days as you configure. Using a TMPDIR would help offset the amount of disk space needed for repair, allowing the temp tables to be created on another partition. I cannot think of another possible solution for your scenario. -RG Artem Kuchin wrote: > Russell E. Glaue wrote: >> Hmm.., >> I thought the temp tables were created in the temp directory. >> What version of MySQL are you using? > > 5.1 latest > >> Perhaps you can temporarily increase the user's hard_limit, and not >> increase the soft_limit. When they have finished recovering, you can >> reset the hard_limit. > > I can repair all table manually and i have a script for doing it just by > chown-ing > the db directory to mysql:mysql , repair it, and the back to > mysql:usergroup > so group quota is observed. > >> Or perhaps it is possible to copy the corrupted database to the temp >> directory (with increased hard_limit), perform the recovery there, >> then copy it back. The data directory does not have to remain in the >> same location in order to be recoverable. > > That's not the point. I need to make MYSQL AUTOMATIC REPAIR > work. Because currently, if mysql tries to automatically repair > large broken table it gets stuck because user group quota is > exceeded. > > -- > Artem > > >> This would be the same as the last e-mailed proposed solution, but >> with two extra steps to copy back and forth from the temp directory. >> -RG >> >> >> ----- Original Message ----- >> From: "Artem Kuchin" <[EMAIL PROTECTED]> >> To: "Russell E Glaue" <[EMAIL PROTECTED]>, mysql@lists.mysql.com >> Sent: Sunday, December 9, 2007 9:03:45 AM (GMT-0600) America/Chicago >> Subject: Re: How to auto repair db on hosting with quota >> >> I won't convert thread style, so, i'm top-posting :) >> >> All this would be great if it worked. The main problem is that >> temporary directory option for mysqld iS NOT USED while doing >> table repair. All files while repairing tables are placed >> in the same directoty with the same owner and group as the original >> database files (i mean, for example, .TMD files which can be huge!). >> >> So, i am currenctly stuck with no solultion to the problem. >> >> -- >> Artem >> >> >> >> Russell E Glaue wrote: >>> Using Quota on Unix, you can specify a different quota soft_limit for >>> temporary space. >>> You can set MySQL to use /tmp as the temporary space, then set a >>> soft_limit of 10mb and a hard_limit of 300MB. >>> This quota should be separate from the normal user space quota. >>> >>> With MySQL temp dir set to /tmp, and a soft/hard quota as 10MB/300MB, >>> the recovery should occur successfully. >>> And going above the soft_limit gives the user X amount of days to get >>> back down below the soft_limit. And the user cannot go above the >>> hard_limit. >>> You can set the soft_limit recovery days to like 1 day. They can go >>> over the 10MB soft_limit for 1 day, up to the hard_limit of 300MB, >>> but after one day the temp files will be deleted until the soft_limit >>> is satisfied. >>> >>> This is a great way to control temp space, and not let users abuse >>> it, but still give them a lot of space to perform things like large >>> mysql database recoveries. >>> >>> >>> -RG >>> >>> >>> Artem Kuchin wrote: >>>> I am asking this question as a hosting PROVIDER, not >>>> as a hosting client. >>>> >>>> The sitation is like this (this is a simple example with small >>>> numbers): >>>> >>>> 1) a client has 100mb quota for site and db files (set on unix >>>> group) 2) 80mb is already taken by 75MB database in ONE TABLE and >>>> 5mb of files 3) databases gets broken >>>> 4) mysql tried to auto repair the table >>>> 5) to repair data it must create a temporary file and this files >>>> take almost as much as 75MB >>>> 6) user goes over quota becauase mysql needs another 75MB to reapir >>>> table, and this summs up like 75+75+5 a lot > than 100mb in quota >>>> 7) mysql gets stuck on this db >>>> 8) people continue to acccess site and connections build up and each >>>> connection waits for repair to be finished >>>> 9) eventually mysql runs out of connections and this stop other >>>> clients too. >>>> 10) day is ruined. >>>> >>>> SO the questions are: >>>> >>>> 1) Is the way to specify USER/GROUP and PLACE for temporary files >>>> for table repair? >>>> 2) Maybe there is some OTHER way to set quota for databases? >>>> 3) Is there way to specify max number of connections for a user? >>>> >>>> Current solution for this as i see it is to make a daemon which >>>> must connect to database first with administrative privs and >>>> monitor number of connections and repairs using processlist. If some >>>> user does more than allowed number of connection then just kill them >>>> off. If repair accures - then turn off that host for a while and >>>> turn if back on again >>>> when repair finished. >>>> >>>> I wonder how it is solved on mass hosting servers. >>>> >>>> -- >>>> Regards, >>>> Artem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
