response 221 of 334:

Dec 5 03:54 UTC 2010

Thank you, Dan!

response 223 of 334:

Dec 6 02:19 UTC 2010

that's neat!

response 225 of 334:

Dec 9 04:33 UTC 2010

Okay, so several of us were talking about getting users access to
MySQL and PostgreSQL, both of which are installed here on Grex.  I had
mentioned that the Board discussed such access months back and the
comment I heard from staff members was that any such usage needed to
be part of the quota system, potenially necessitating a complex setup
to make sure the size of db files got counted under the right quotas.
One way to implement this sort of setup is to run multiple db servers,
each pointing to a db in a single user's home directory.  It sounds
complicated and potentially resource intensive.

Dan asked who said that, and at this point I don't recall, only that a
couple staff appeared to agree that was the way to go. I never heard
anything more about it after that.

Personally, I think it would probably work just fine if we had one
central db and allowed users to have accounts on that and to create
tables there under their user ids.  That would certainly make the setup
much easier.

As to the potential for abuse, I don't see it as any different from any
other application that uses disk space.  It's something to manage.  And
if we made it by request and for validated users, we'd in theory know
who was causing issues, if any occurred.  And probably we'd have less
abuse type of issues in a requested service, as well.  There are no
guarantees, but I'm fairly tired of seeing the potential threat of abuse
holding the rest of the users back from getting the apps they'd like.
So, why don't we try this and see how it goes?

I think it would be a wonderful situation to be in where we outgrow
our current computer due to more people using the system in more ways,
rather than watch the current system die of old age.

response 227 of 334:

Dec 9 18:18 UTC 2010

CGI can be given without too many complications. For MySQL, how about 
we create a separate partition of 1GB and stick everything on that 
(it's automatically quota limited to whatever the mysql-user has access 
to which should be 1GB and mysql-homedir). We could create a bunch of 
test mysql-accounts/databases for people to experiment with.. that way 
we could: 
1. allot something really quick if a newuser wants it.
2. we could start advertising it as a service.

so if a newuser arrives, he can start work straight away on a 
pre-created account. By the time his script his ready, we'll prolly 
have come to some decision on how to handle MySQL properly (in terms of 
automated scripts to create db with a "proper" name, and some kind of 
disk quota measuring mechanism and validation/whitelist)

response 229 of 334:

Dec 9 18:27 UTC 2010

more importantly, what Sindi pointed out, we need a duplicate rootFS 
with suitable network-config! That's an absolute priority.. if the 
server goes down, someone in 'provide' just resets the box and we boot 
up via the 2nd FS so staff can login and fix FS1. Why didn't we go with 
something like this earlier on.. any idea?? 

basically it's a normal bsd install on hda1 and dd if=/dev/hda1 
of=/dev/hda2, and then suitable grub(or whatever bootloader entries) 
entries for both hda1 and hda2..

response 231 of 334:

Dec 9 21:15 UTC 2010

Or perhaps write a stored procedure do it instead.

response 233 of 334:

Dec 10 03:11 UTC 2010

Seems this is a popular problem to solve:
http://www.cyberciti.biz/tips/linux-unix-setting-up-mysql-database-quotas.h
tml

response 235 of 334:

Dec 10 04:51 UTC 2010

#!/usr/local/bin/bash
#
# Assumes each user has 1 database, named the same as their username.

MYSQLUSER="root"                            # MySQL username
MYSQLPASS="changetherootpasswordyo"         # MySQL password
MYSQLHOST="localhost"                       # MySQL hostname or IP
EXCLUDEDB="test mysql information_schema"   # Databases to be excluded

DBQUOTA=1                                   # Database quota in MB

MYSQL="$(which mysql)"
DBS="$($MYSQL -u $MYSQLUSER -h $MYSQLHOST -p$MYSQLPASS -Bse 'show 
databases')"

for DB in $DBS
do
   if [ "$EXCLUDEDB" != "" ];
   then
      for i in $EXCLUDEDB
      do
         [ "$DB" == "$i" ] && ( continue 2; )
      done
   fi

   DBSIZE=$($MYSQL -u $MYSQLUSER -h $MYSQLHOST -p$MYSQLPASS -Bse 'SELECT 
sum(data_length+index_length)/1024/1024 FROM information_schema.TABLES 
WHERE table_schema=\"$DB\" GROUP BY table_schema')

   if [ $DBSIZE > $DBQUOTA ];
   then
      $MYSQL -u $MYSQLUSER -h $MYSQLHOST -p$MYSQLPASS -Be 'REVOKE 
INSERT, CREATE ON $DB FROM \'$DB\'@'localhost'
   fi
done

response 237 of 334:

Dec 10 04:58 UTC 2010

Does that process look like it could be automated safely along with
account setup in MySQL?  I assume each user needs to have their own
account set up, so any tables they create would remain separate from
other users' tables.  Since each user gets a link back to the main MySQL
db, the user should be able to add tables to their own acccount without
a problem and the quota system will control how much data they can save.
Is that your understanding?

Anyway, this is what was discussed in a prior Board meeting in terms of
keeping a quota for the DB files in addition to the regular data under a
user's home directory.

I assume postgreSQL could be set up in a similar fashion.

response 239 of 334:

Dec 10 12:13 UTC 2010

resp:237 I don't think it would be such a great idea to symlink from 
the sqlarea into the userHomeDir.. what if the userdeletes his dbDir 
and creates another symlink? There's a whole can of worms we are 
opening up.. i think.. the moment you have a system process writing to 
a user area. I'm not saying it's a bad idea.. it's a nice solution 
because it looks cleaner..

response 243 of 334:

Dec 10 15:36 UTC 2010

resp:242 I'm inclined to agree, though I don't think there's
necessarily anything wrong with symlinking the files to some directory
under the users's home.  I was thinking about it, and mysql itself
runs as an unprivileged user; if the user deleted his or her database,
they would only mess themselves up.  If they tried to point the
symlink elsewhere, they'd be subject to the constraints of that
other database with respect to access.  I suppose they could point
it at a random user file, but 1. few of those are writeable by the
mysql user, and 2. that wouldn't really do anything, anyway; mysql
is smart enough that it could detect that it's not one of its own
files.

But I agree with Nate.  I think we can just create databases for
users who want them, and if space becomes a problem, we address it
then.  I don't think we need to create a new database for everyone
as soon as they create an account; we could let them request it and
do it on a one-off basis, or write a tool so that a user could
request one for him or her self and get it automatically created.

I think MySQL and CGI access are both pretty good ideas, and this
discussion about quotas brings up a concern of mine: there's this
knee-jerk reaction to change on Grex, to installing new software
and services, and it's starting to get really old.  Grex is not
presently a high-demand service, and I don't know that it ever will
be again.  We need to stop worrying about things that aren't problems,
and start concentrating on things that are: users using all of our
disk space on mysql databases is not a problem at the moment.  Let's
not overthink it.  Let's also not be afraid to change software
configurations around, or install new stuff.

That said, I also don't think we should go overboard putting new
services and things into place if there's no demonstrated demand
for them.  I also think we really need to fix up what we have before
we start worrying about hordes of new services.  There hasn't been
a lot of demand on Grex for MySQL and PHP; those things are cheaply
and easily available elsewhere, and Grex just isn't that interesting
for them.  Even on M-Net, which does provide access to CGI programs
and MySQL, I can probably count on my fingers the number of requests
that have come in for one or the other or both in the past few
years.  I think we *should* do them, but they're not top priority,
and other things can and should take precedence over them: things
like web newuser, and getting the content of the main grex website
updated and cleaned up.

Also, let's not light our hair on fire trying providing everything
under the sun.  Most users who want access to a database probably
want to use MySQL: I think we can get away with giving out MySQL
database access, and not worry about PostgreSQL (even though, in
my opinion, the latter is a much better piece of software).  I think
that's also useful in another context, in that we can use PostgreSQL
for our own internal production uses and give user access to MySQL
and SQLite.  Even MySQL seems like kind of a stretch to me: any
validated user on Grex can get access to SQLite if all they want
to do is play around with an SQL database.  Full-blown MySQL access
isn't necessary for, well, anything really.  At least, anything
that can be done on Grex.

This may sound somewhat contradictory, in that on one hand I'm
saying we shouldn't just say "no" and on the other, I'm sounding
like I'm saying "no," but that's not really it.  What I'm saying
is, let's not waste our most precious resources in terms of people's
time on things that may not have a substantial return on investment,
and let's prioritize and get the house in order before we move on
to grander projects.

Let's build the foundation that we're going to layer the rest on,
before we start putting the finishing touches on the molding.