Re: Quota Support
Yes, you certainly needs to chown the files to the specific user id's for filesystem quota to count the files in the user's quota. This will solve your quota needs, although I see many potential problems: - The mysql daemon needs to have access to the files. An easy solution is to chgrp mysql the files (they should already be) and then change the permissions so the group has access to them (chmod g+rwx). You could also use access control list if your system supports it. - We don't want the users to access the database files directly If a user owns a file, he can change permissions on it, modify it, delete it, etc.. The mysql daemon will not like that and it can cause all sorts of unexpected problems I guess. So the first thing to do is to NOT put the files in the user's home directory with the symlink hack. This has no advantage and the users will certainly mess with them if you do so. A complete solution might be to only chown the files, not their directory. If the user has no access to the directory, he cannot modify its files even though they are owned by him. - Database files are created at various times You cannot only have a script set the permissions correctly when the database is created and then forget about it. As the tables will be created, new files will appear and they need to be chown'ed / chmod'ed. A cron job might come in handy here. - innodb tables are different This should work with myISAM tables, but innodb stores the data in one big file shared between databases if I am not mistaken. So you need to prevent your users from using innoDB. May be other table type will have a similar problem and you might not be able to prevent users from using them. May be someone more experienced with the different table types available can enlighten us here. I never tried such a setup but I will have to soon. Any quirks I missed? Cedric Le December 11, 2002 12:06 am, Steven Adams a écrit : Well hows it spoes to work with disk qoutas if u dont chown it to the user id?? /Steve - Original Message - From: Dean Harding [EMAIL PROTECTED] To: 'Steven Adams' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, December 11, 2002 7:32 AM Subject: RE: Quota Support Why would you need to chown it? Your user's won't need to touch the file at all, so it should be happy being owned by mysql... I don't know, maybe it won't work... I'm not much of a Unix person - I've never touched unix since I left university :) Dean. -Original Message- From: Steven Adams [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 10 December 2002 7:42 pm To: [EMAIL PROTECTED] Subject: Re: Quota Support So do u mean like this user home dir = /home/web/users/xxx then u symlink /home/web/users/xxx/mysql/database1 /usr/local/mysql/lib/database1 chown -R xxx.xxx /home/web/users/xxx/mysql/database1 Wouldent mysql get permissions errors like that? /Steve - Original Message - From: Dean Harding [EMAIL PROTECTED] To: 'Steven Adams' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 09, 2002 7:01 PM Subject: RE: Quota Support You can place the actual database files inside your user's home directory, and sym-link to them from the MySQL's data folder. That way they will contribute to the user's whole quota and you don't end up with one quota for DB and another for everything else. The drawback, of course, is that you have to limit it to one user per database (but each user could have more than one database). Dean Harding. -Original Message- From: Steven Adams [mailto:[EMAIL PROTECTED]] Sent: Monday, 9 December 2002 4:07 pm To: [EMAIL PROTECTED] Subject: Quota Support Hey, I was wondering if its possible to implant a quota system where i can give a user just say access to 2 databases which is allowed to grow to 30Mb bewteen the two of them.. What i would really like is if its possible to make mysql use the disk quota limits.. So i can give the uses say 50MB WebSpace on my server which will iclude his mysql db Thanks /Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check:
Very bad bug on FreeBSD
Hi, I am running mysql 3.23.53 on FreeBSD 4.4. I compiled mysql from source with the following configure command: --prefix=/usr/local --localstatedir=/var/db/mysql Every now and then, the server seems to change its database directory to /var/tmp. When it happens, show databases gives me the content of the /var/tmp directory! I had this problem for more than a year now, and I upgraded every time a new version was available hoping it would fix it. I noticed that the higher to load on the mysql daemon, the more likely it is to happen... So there is probably a race condition or something. The manual says mysql is less reliable on FreeBSD because of the poor multi-threading implementation. I didn't expect I'd have to restard it every day (!!) Any help would be very appreciated. Thank you, Cedric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
my InterBase vs mySQL benchmarks!
Hi, I am considering developing a project on mySQL or InterBase. I tried to find comparisons, benchmarks, etc, but found nothing... Could anyone point me to such documents? If anyone cares, I wrote a small PHP script to benchmark both DB.. it's really simple, but shows mySQL is faster all the times.. http://www.inetflex.com/db-bench.php?tests=100 (This will run the test on my home server, a p3 733 with 256 MB RAM and IDE drive). Both DB server are running on the box of course. Cedric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Large search engine
Hi, You are right about this, I should have thought about it. There is just one problem: A word can occur multiple times in the same documents, so the table which gives the position of a word in a doc. must give all the positions of the word. If we keep your logic, we would have to make a table for each word, and I don't think it's a good idea. Suggestions? By the way, my question was: What is faster between filtering docs and then performing a '%like%' query on relevant documents: Ex: We search for 'red sun', so we first filter the documents to keep only the ones containing red and sun, so we have at chance that some of these contains the expression 'red sun'. Then we perform a select query with where text LIKE '%red sun%' in the relevant documents. OR not using LIKE statements at all and use a word positions table to find the docs where the position of sun = the position of red + 1 Thank you, Cedric Veilleux what you'll need is: 1 table with doc_ids (and perhaps document) 1 table with words 1 table which links words to docs 1 table which gives the position of a word in a doc. create table documents (doc_id integer primary key auto_increment, document text); create table words (word_id integer primary key auto_increment, word varchar(255)); create table occurences (occ_id integer primary key auto_increment, doc_id integer, word_id integer); create table positions (pos_id integer primary key auto_increment, occ_id integer, position integer); this way you can handle "unlimited" words with "unlimited" occurences in "unlimited" documents. any other solution would force you to construct very inefficient tables, or use of blob fields which really horribly would slow down your db when adding data for example, and is generally a very very bad way you shouldn't even think of. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Large search engine
Hi, I am planning a very large search engine. I've spent some time reading the archive and I found some suggestions on how to do this. The word indexing method is a very interesting alternative to slow "...where like '%foo%';" queries. There is from 100k to 500k documents to index, each are about 10 KBytes large. Plain text. The search engine will allow complex boolean queries (AND, OR, NEAR, NOT). I have 2 plans in mind, I'd like to have opinions on what's would be the most efficient. First Way: We populate 2 tables: one containing the documents (text and ID) one containing all the words and the documents ID containing each word The idea is to first filter the documents and then to perform a query in the documents that contains at least one of the words, so we're supposed to get a decent speed. I know this is used by many people and I know it gives good results, even when searching through 100k+ documents. Although, I am wondering if there is not a way to do it without any use of LIKE statements. I really don't know if what I have in mind is a good idea, it may be completely stupid and inefficient, I have very little DB experiences. Anyways, what if in the table containing the words and the matching document ID's, we also specify where in each documents the word is located. ex: WORD| DOCS | LOCATIONS sun | 32;45;1302 | 3 ; 554,1022 ; 76,675,3445 So word sun is the third word of doc 32, the 554th and 1022th word doc 45, etc.. Then the search script will do all the job without sending any other queries. May get quite complicated but it should work, it may also be easier to process sun NEAR star (maybe this is easy to do with LIKE too, I don't know, but I saw nothing in the docs.) Thank you, Cedric Veilleux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php