Create db from mysql api


#1

Since my suggestion back in 2011 didn’t get done and I noticed some changes here with how suggestions are handled I thought I’d throw this one up again.

I checked the mysql api page to be sure there isn’t already a method of creating a db… if there is please slap me… I’ve missed it.

My site relies heavily on being able to create separate / unique dbs for each client. Currently I need to manually create a ton of empty DBs so something is available when a new client signs up.

I would like to be able to call upon the mysql api to do this. Not only would this save ME valuable time from having to create empty DBs but then I wouldn’t actually have to create the empty DBs… and can just create new ones on the fly.

We can already assign users and permissions so I’m good to go there… if we can get in database creation, my client sign-up process becomes truly automatic.

I already have cron jobs running to empty / drop tables from databases that are no longer used so I can go back and re-use them for the sign-up process for new clients.


#2

It seems like maybe there is no appetite from this? I can’t believe I’m the only one that would want this kind of development.

Is there a faster way to create database names other than manually doing it through the Web Panel?


#3

I can. Most people would create a separate table for each client within the same database. That is certainly something that is easily accomplished via php. Perhaps that’s why DH has no appetite for this. (Besides the fact that it could bog down MySQL servers if something got out of hand.) Maybe if they knew more about your end goal, they could come up with something that would work for you.


#4

I’ve gone down that road… one DB with multiple clients does not work for me as those clients are going to have access to the DB data. The Clients need to be restricted to only their own data so they can customize their own website with and populate with data relating to them only.

Is there a way to set up a mysql user to be able to access data that only contains a particular key? I’m pretty sure this has been answered “NO” before… hence my need for multiple DBs.

EDIT: Each Client’s DB contains 81 tables to hold various things relating to their league (we’re a fantasy hockey simulator: http://uhs.hockeysim.ca)… Sometimes these DBs can get quite large (if a league has been around a long time), keeping them as separate DBs keeps it manageable for everyone.


#5

I am looking for the same thing. The two main reasons are:

  1. For security/privacy (so a mistake in the application does not expose information between clients) and

  2. to allow my application to scale (so I can move databases to another server if any particular client has high demand).

Using a column in each table to separate clients is risky, makes thing much more complex, and creates a performance hit because now every single query has to have an extra condition.

I hope that creating databases will be added to the API some time soon. It would make website setup so much easier.