Mysql: Cannot Create Database from Shell?


#1

I’ve purchased a mysql PS to compliment my current PS. One of my services involves the need to create a unique mysql DB for new clients.

Currently I’ve been doing it through the DH web panel, however, that’s wholly dependent on whether I can get net access… and sometimes I may be offline for as long as 8-10 hours.

I do not expect the client to wait that long for their service to begin.

My attempts at creating a DB via shell have resulted in “Access Denied”… which is puzzling to me since I’ve paid for a Private mysql server… and am not using a shared one.

I am looking to have a cron job triggered to look for validated new clients and create their mysql database.

Can anyone assist me?


#2

Our permissions never allow you to create new databases directly — all MySQL databases must be created through the panel so that our management tools can recognize and manage them properly.


#3

Hi Andrew,

Would the same be said if this functionality was available through the API?

James


#4

We don’t have an API call to create databases yet — having one would solve the problem, but we aren’t currently aware of any great use cases for one. I’d appreciate it if you could email me details of what you’re trying to do here (using the “email” button under this post).


#5

I don’t see any “email” button… clicking on your profile and attempting to email you tells me that I don’t have permission to this feature.

More of less what I have happening is a client will register for a service… after an email validation, a message is sent to me to go in and set up their db. Now I basically, create the db, assign existing users to the db with certain permissions and set up a subdomain. Once this is done an automated email is triggered alerting the client their service is ready.

I know I am able to pretty much automate everything except for the database creation… the whole process is dependent on my own access to the internet (which at times may not be possible).


#6

Bother. Check if the email button works now?

From what you’re describing, it sounds like you’d probably be better served by creating tables within a single database to store data for different clients, or storing all the data in a single table with a column to distinguish the clients. Creating lots of small databases is inefficient for the database server, and probably ends up being much more difficult for you to manage.


#7

So… I’m posting back here because I did send a reply by email but didn’t get a response. I think it would be better to carry on the conversation here so others may benefit from the thread.

The solution you’ve offered doesn’t really work for what I’m doing. Having separate, smaller databases is more secure and is manageable.

I actually had thought about your solution before posting here originally, however, it won’t fit our needs as one of the premium services we are offering is access to the user’s website AND mysql db. To my knowledge you cannot limit mysql data access by a common key field. If I’m in error, please let me know! Having separate, smaller databases works nicely for us as we generate a unique user name that grants the client access to only their DB.

Having the Create Database option as an API command will completely remove the manual work required on my end… that in of itself is causing the bottleneck in client responses.

I see my API suggestion is available for voting. I sincerely hope that this will get approved and worked on as soon as humanly possible. I moved over from GatorHost because of a lack of control / accessibility and so far DreamHost has by far exceeded what I was looking for. Being able to create mysql DBs “on-the-fly” would pretty much be the icing on the cake.


#8

I went and added my vote. :slight_smile: The link if others want to vote is: https://panel.dreamhost.com/index.cgi?tree=home.sugg&category=_all&search=Create%20Database%20from

I’m working on a project were this will indeed be a need if we end up hosting it here.


#9

Has there been any progress on this issue? I see it is still up for voting but I haven’t heard about anything being added to the mysql API.

This is very much affecting how I offering my services. I have looked at having users create their leagues using one database, however, there is no way isolate specific leagues for the purposes of both accessing league-specific data and saving / restoring league-specific data.

Honestly, this is the only thing I need to get my site / service completely automated. I wish I could see what kind of progress is made or whether this has been tossed in the “will never happen” pile. At least then I’ll know if I need to start making plans to migrate to another service.


#10

Has there been any news on this? I just found out today this was not possible. Like other posters on this thread I have automated everything else in my customer setup process except for the database creation. I can understand the need to manage the databases and have them created through the panel, but certainly this could be added to the api.


#11

[quote=“Andrew_F, post:4, topic:56271”]
We don’t have an API call to create databases yet — having one would solve the problem, but we aren’t currently aware of any great use cases for one. I’d appreciate it if you could email me details of what you’re trying to do here …[/quote]

The use case I’d propose is automatic end-to-end migration from one host to another.

I appreciate that this is something you might want to positively discourage.

Nevertheless, here’s the reasoning.

In my opinion, having two hosting accounts with different companies at around 10 dollars per month can be a better bet than having one hosting account with a supposedly higher-reliability company at around 20 dollars per month.

But that’s only the case if it’s easy to migrate between the two hosting accounts.

It would be good (I suggest) to be able to click a button on Friday evening which automatically migrates everything from one host to the other, so that (allowing the weekend for DNS propagation) everything is up and running on the other host on Monday morning.

Then the next Friday evening, one can repeat the exercise in the reverse direction.

To this end, the Dreamhost API is currently lacking functions to Create_Domain, Create_SubDomain, and Create_Database.


#12

So… from what I can see this hasn’t been added to the API. I’m getting concerned that this will never happen… I’ve shown as well as two others that there is a desire to have this functionality included in the current mysql API… or at the very least create a new API to only handle mysql Database creation.

I’ve learned that I have ALS, albeit slow-progression, so I’ve been spending quite a bit of time automating EVERYTHING. From stats reports to dynamically updating my crontab… the only thing I really need now is a method of creating a mysql database. My work-around for this problem is to simply create a bunch of empty databases that get used one at a time when I get a new customer. I am also recycling stagnant / idle customer databases… my system is in a good place… except for when I absolutely need new database creation.

The only thing I can do is write instructions for my wife on how to create new databases and run the mysql user privileges script when I’m dead and gone. Not good.

Let me re-iterate from my previous posts… Putting everything into a single database does not work for me. Customers will load / save databases, show data from a public website template and also PAY to access that database readonly so they can do their own custom website.

For the sake of my OCD, sanity and Captain Picard’s lost rug, please make a Create Database API.


#13

I doubt you will ever see this. It’s also really only a limitation at the shared hosting level. Dreamhost doesn’t want to make it “easy” for you to resell shared hosting. They’re fine with a developer hosting some value added sites for customers, but even there I think they would really prefer that the developer sell their customer a separate dreamhost account (for which the developer can engineer their own kickbacks using the rewards program).

The other API you will never see is creation of mailboxes or aliases… same reasons, the don’t want you giving those away or re-selling them.


#14

The lack of a mailbox creation API has less to do with reselling and more to do with abuse. Spammers ruin everything. :frowning:

An alias management API isn’t out of the question. It just isn’t something we’ve implemented at this point.


#15

With the remove of panel links pointing to the API panel pages, and the same removal panel search, is there any fear of the API disappearing completely? At this time, you either must have the panel link bookmarked, or find one of the few that exist via the wiki, other than that there is no path to those panel pages. Specifically the page I mean is https://panel.dreamhost.com/?tree=home.api which you now have to KNOW how to find to find it.


#16

Any kind of SaaS application could use an API call to generate a database. Each client that uses the application needs their own tables and their own data.

  • If there is a bug in the application, client data is not exposed (which may cause security and/or privacy issues).

  • If one client is hogging resources, their database can be easily moved to another server.

  • The application stays simple, no need to add extra conditions to hundreds of queries to try and separate data for each client

  • No need to have a performance hit by adding the extra condition(s), or having exponentially more data added to a single table.

I’m sure I can think of more reasons, but the ability to add a database via API would be very helpful. The same goes for the ability to drop databases (to save space, and in the case clients want their data wiped).

The idea is that clients sign up for a trial period on my application on a shared hosting account. The API would allow this to be self-serve. If they decide to continue and pay for the service, I need to create a VPS account to provide them a reliable service with an acceptable SLA (service level agreement). This would create more business for Dreamhost.