A query that references two databases?

[jhsachs]

I inherited a site that uses tables in two different MySQL databases. Up to now that's been a mere nuisance, but I've been asked to write a report script that needs a single query which refers to both databases.

 

The SQL syntax for this query is clear to me, and in fact it works on my development system, where I've combined the two databases into one. The problem is in PHP, where I have to call mysql_query with a resource parameter that can refer to one database or the other. The query needs access to both, so it fails.

 

Is there a way to get around this limitation, short of combining the databases?

[trq]

The link resource that mysql_query uses as an optional second argument represents a database server. If these two databases are on the same server there shouldn't be any issue.

[jhsachs]

I'm sorry thorpe, I don't see how that works. The optional second argument is the resource I mentioned. It can point to either database -- but not both.

 

I checked, and the databases are on the same server, but it's not clear how that helps me.

[trq]

The resource argument has nothing to do with a particular database, just a particular server. As you said, both databases are on the same server.

[jhsachs]

I think I see what you mean now. Unfortunately, I don't think it does me any good!

 

As far as PHP is concerned, I could solve this problem by defining a user name that has privileges on both databases. Then, as you said, a resource obtained for that username on that server would give me access to both.

 

On the my client's hosting service, however, this is impossible. When the host's software creates a database, it creates a user with the same name. Only that user has access to that database, and only that database is accessible to that user. I have no access to a root account that would let me alter this arrangement.

 

For this reason, I'm accustomed to think of "user name" as synonymous with "database name." That's why I didn't understand you at first.

 

I'm afraid my question may be degenerating into a riddle: how can a query refer to two databases on a system that won't let it do so? Unfortunately that's the problem I have to solve.

[Muddy_Funster]

why can't you just query each database in turn - one at a time?

[jhsachs]

I could, but it would be horribly slow. I'd have to filter a very large set of data in a PHP loop instead of letting the query engine do it.

 

I could read a table from one database, insert the data into the other database, run the query on the second database, and drop the copied table. It would work, but it seems hokey.

 

Since I posted the original question I've realized that it is technically feasible to merge the databases. I spoke to my client about that and they agreed to it, so we're going to do it soon, and the problem will go away. This is actually a better solution than a cross-database query, because it makes other things easier, too.

 

I consider this resolved, the answer being, "It is possible except for the stupid restrictions imposed by this particular hosting service"!

[fenway]

If they're on the same server, you can create a user that has access to both.

[jhsachs]

The only way I can create a new user is by creating a new database! I explained this a few posts above.

[fenway]

The only way I can create a new user is by creating a new database! I explained this a few posts above.

Sorry, missed that -- if this is a read-only situation, you can probably used FEDERATED tables to cheat.

[gizmola]

I don't like questions like this, because they suggest that the answer to an unreasonable constraint is to accept the constraint, rather than take the common sense approach and deal with the removal of the unreasonable constraint.

 

You can reference multiple db's in the same query using dbname.table.  The only problem you have is access. 

 

The answer here is to go to your client, and have them get support from the hosting company to GRANT access for userA (if that's the user you'll be using) to userB.  Before you assume you actually need to do that you should see if userB is able to GRANT access to its tables to userA. 

 

Otherwise, have the client get the support they require so you can actually accomplish the goal they are paying you to accomplish in a reasonable and efficient manner.

[jhsachs]

Before you assume you actually need to do that you should see if userB is able to GRANT access to its tables to userA.

 

There is no means of granting access. The "Privileges" tab of phpMyAdmin is absent, either because the host's management removed it or because user accounts are configured so that phpMyAdmin doesn't display it.

 

I don't like questions like this, because they suggest that the answer to an unreasonable constraint is to accept the constraint, rather than take the common sense approach and deal with the removal of the unreasonable constraint.

 

That would be an ideal solution. Unfortunately, it is also an idealistic solution.

 

I have fought battles before with this host. Sometimes I win; more often I don't. I always spend a lot of time on them, which I must bill to my client. If the amount I bill is significant, I'm liable to be asked why I had to do what I did.

 

In this case I can't point to any benefit from doing to right thing beyond the aesthetic one.

 

As long as I'm doing this as a business, I have to try to meet my client's needs in the most cost effective way. Sometimes the most elegant solution to a problem is the most cost effective one. More often there's a tradeoff. I have to pick my battles.

 

As I said previously, the best solution to this problem turned out to be to merge the databases rather than to eliminate the unreasonable restriction on cross-database queries. If I hadn't had to beat my head against this problem, I probably wouldn't have realized that it was feasible to do so. That gives me another type of professional satisfaction: coming up with a solution that benefits my client as well as myself, instead of the obvious "ideal" solution that would have satisified my aesthetic sensibilities at my client's expense.

[fenway]

And this is precisely why I insist that all my clients are hosting on my servers.

[Pikachu2000]

That would be an ideal solution. Unfortunately, it is also an idealistic solution.

 

How is expecting your client not to be on what sounds suspiciously like a GoDaddy free, shared hosting server idealistic?

[fenway]

Agreed -- if you can't ensure that your client's servers are up to par, then you shouldn't even bother.

[jhsachs]

Pikachu200 wrote, "How is expecting your client not to be on what sounds suspiciously like a GoDaddy free, shared hosting server idealistic?"

 

I'm not going to name the hosting service. I will say that the service has been below par in several ways, although not in ways that directly impact the their customers' experience; the client recognizes that; and we have discussed changing hosts, but are not going to do so at this point.

 

You're wrong about "expecting." The client was using GoDaddy before I joined them, for reasons I haven't investigated; probably on a recommendation from the person I replaced.

 

So, the question is not whether I "expect" my client to use GoDaddy, but whether I'm going to "expect" them to undertake the trouble, expense, and risk of changing hosts to solve a problem which can be better solved by a simple database reorganization.

 

I don't understand your attitude toward this issue. Or rather, I understand it, because I feel the same desire for technical quality as you do; but I don't understand your disregard for the fact that clients have different priorities, and are the ones paying the bills. No consultant can serve their clients well by delivering what they want instead of what their clients need.

[fenway]

I don't understand your attitude toward this issue. Or rather, I understand it, because I feel the same desire for technical quality as you do; but I don't understand your disregard for the fact that clients have different priorities, and are the ones paying the bills. No consultant can serve their clients well by delivering what they want instead of what their clients need.

Actually, that's the consultant's job -- the client has no idea what they need, nor what they want.

[jhsachs]

fenway wrote, "Actually, that's the consultant's job -- the client has no idea what they need, nor what they want."

 

No. The consultant's job is to explain a situation in terms that the client can understand, even if it is complex and uncertain, then to help the client make informed choices. Anyone who does less is serving their clients poorly and putting their own professional standing at risk.

 

Apart from that, I would never recommend that a client change hosts over an issue like this. There simply is no justification for it. The client understands the nature and extent of the host's problems, and those problems are nowhere near serious enough to threaten the client's welfare. In this situation, whether and when to seek a new host is entirely up to the client, and it should not be otherwise.

[gizmola]

There is no means of granting access. The "Privileges" tab of phpMyAdmin is absent, either because the host's management removed it or because user accounts are configured so that phpMyAdmin doesn't display it.

 

I don't need phpMyAdmin to do a grant and neither do you.  With that said the user may not have the grant permission for their own database.  Regardless, phpMyadmin does not use some magic interface -- it issues a GRANT statement. 

 

As I said previously, the best solution to this problem turned out to be to merge the databases rather than to eliminate the unreasonable restriction on cross-database queries. If I hadn't had to beat my head against this problem, I probably wouldn't have realized that it was feasible to do so. That gives me another type of professional satisfaction: coming up with a solution that benefits my client as well as myself, instead of the obvious "ideal" solution that would have satisified my aesthetic sensibilities at my client's expense.

 

I'm not sure what you mean by merge.  If you mean that you moved all the tables over into one database, that sounds like a good solution.  There are plenty of situations where that would not have worked.  What if the two systems had tables with the same name? 

 

In my experience part of dealing with customers is at times educating them about hosting.  A bargain basement shared host is no bargain, and unless you're working for less than you can make flipping burgers, they are paying you a lot more to work on their problems than they're paying for hosting, and you would be doing them a favor to let them know that they are better off with either a vps or a dedicated host from a reputable hosting company.  I've seen numerous cases of people who have a minimal site that is taking 5x longer than it should to serve a page due to being on an oversubscribed shared host, or seen their site coming up as server not found due to lousy dns servers.  If it's a business worth anything, I can't imagine why they wouldn't want to pay a little bit extra to insure their customers can actually reach their site. 

 

If the people they are paying to work on their site can't do their job efficiently, there is no economy to that.  I don't work on any site where I don't have shell access.  Truthfully, you seem way too please with yourself for having to work around a crappy shared hosting company.  There is no victory in that, and it also seems like you could learn a good deal more about database administration, if you're going to be working on database driven applications.

[fenway]

fenway wrote, "Actually, that's the consultant's job -- the client has no idea what they need, nor what they want."

 

No. The consultant's job is to explain a situation in terms that the client can understand, even if it is complex and uncertain, then to help the client make informed choices. Anyone who does less is serving their clients poorly and putting their own professional standing at risk.

Consultants have professional opinions -- not every client is going to understand every nuance of technical decision.  Granted, it helps to try and explain it, but not always.  If the client didn't need any help, they wouldn't have hired a consultant.  They hired an expert, so they should treat the opinion as expert as well.

 

Apart from that, I would never recommend that a client change hosts over an issue like this. There simply is no justification for it. The client understands the nature and extent of the host's problems, and those problems are nowhere near serious enough to threaten the client's welfare. In this situation, whether and when to seek a new host is entirely up to the client, and it should not be otherwise.

Clients DO NOT understand anything about hosting -- don't fool yourself.  No technical decision should ever be left to the client -- that's just irresponsible.  Besides, why would you ever limit your recommendations?

[jhsachs]

Gizmola wrote:

 

...by merge...  if you mean that you moved all the tables over into one database, that sounds like a good solution.

 

That is what I meant.

 

In this case, problems like identically named tables did not arise. The difficulty I saw was how to perform a fundamental database reorganization without an interruption in service -- an interruption that could become lengthy if something went wrong.

 

My new insight was that no interruption at all is necessary because one database contains only static tables. I can simply copy them to the other database, then change the site's references to the first database by slipstreaming a three-line change in the code. If there's a problem, I can reverse the change without data loss.

 

A bargain basement shared host is no bargain... and you would be doing them a favor to let them know that they are better off with either a vps or a dedicated host from a reputable hosting company.

 

I understand that, and I would never have recommended this arrangement if I had been around when the decision was made. Now the host is providing service that is far from ideal, but is adequate, and has little or no impact on the client's customers. In particular, there are no performance problems on our production site (although there are on our staging site, and that's a live issue now). The impact on me has been a constant, substantial nuisance, not a serious problem. There's no question that changing hosts would enable me to work more effectively, and that would more than justify the additional cost of a higher grade of service from a better host. In my professional judgment, however, it would not justify the cost and risk of making the switch.

 

That said, I'm interested in your comments about using a VPS or a dedicated host. Up to now I've avoided recommending either one. On the hosting services I've checked (admittedly few, because it has not been a priority), the client is responsible for system administration. That makes sense to me; complete responsibility has to accompany complete control.

 

Now, I'm a developer, not a sysadmin, and that's a task I don't want to be responsible for. Not that I'm opposed to learning new skills, but I can't make my client the guinea pig while I learn skills that are mission-critical for their business. Nor can I justify requiring them to pay another consultant to take over those duties.

 

Can you suggest a way of dealing with this issue? For example, can you recommend hosting services that will offer a VPS or dedicated server but still assume responsibility for system administration?

 

...it also seems like you could learn a good deal more about database administration, if you're going to be working on database driven applications.

 

I'm always interested in extending my skills. Can you suggest areas that I ought to study -- and explain why? I regret not remembering how to refer to two databases in a query, but that's a SQL language issue, not a DB admin issue. I don't see another aspect of this matter in which I've fallen short.

Fenway wrote:

 

Clients DO NOT understand anything about hosting -- don't fool yourself.  No technical decision should ever be left to the client -- that's just irresponsible.  Besides, why would you ever limit your recommendations?

 

I don't think we're going to reach agreement on this issue, so I don't think debating it further would be useful. Before we leave the topic, though, I'd like you to perform a thought experiment. Imagine that instead of you saying that about your clients, your doctor was saying it about his patients -- including you.

 

How does that make you feel? Do you want to find a new doctor whom you can trust? I would.

[fenway]

Actually, I want a doctor who knows what he's doing -- he's allowed to tell me that I'm an idiot.

 

Anyway, what you're looking for is managed hosting.

[gizmola]

I'll try and answer your questions as I understand them, by demystifying a few things.  In really general terms operatiions groups tend to break down roles as: 

 

-network engineers/telecom

-system administration

-security

 

When you have a hosted server, you are paying for the hosting company to provide all of these services in some fashion, whether that is shared hosting, vps or dedicated server.  Shared hosting is not providing you more service for less money -- they are simply maximizing their investment by loading the most customers possible on the available infrastructure.

 

When you talk about administration you seem to be talking purely about the system administration aspect.  I focus on linux system administration.  There are good reasons to learn at least a modicum of sysadmin skills as a developer, because your application is going to be running on the os, and there are routine issues related to that like file system permissions and ownership, or cron that are tools that you really need to understand.  You also are well served to understand some networking basics, and be able to setup lamp.

 

There really is very little excuse not to explore sysadmin given the availability of cheap hosting, and cheap or even free virtualization.  For example, anyone with a relatively recent workstation or laptop can get a copy of virtualbox, download an iso for centos or ubuntu or debian and have their own playground where they can install and configure things and learn in a completely safe environment. Here's an article series I wrote that walks you through the process and here's another one for Ubuntu.  If you look through those, you should see that there is nothing magical about setting up LAMP.  These are things you should try and do yourself, and I will guarantee you that it will be a great learning experience and increase your confidence as a developer because you will learn how to explore a system and figure out how things are configured, which is a skill that comes in handy especially where problems arise.  So basically you can easily have your own private development network running on your own computer where you can practice, explore, and develop your code, using the same server operating system your client is running on.

 

Let's demystify for a moment what a shared host is providing.  They run the same os's, and the same software that you can, only they typically are running it with software that has been configured so that you can do certain sysadmin tasks using the gui.  Some people see this is either a convenience or a necessity, but the reality is that very few businesses ever use the things in cpanel for anything other than adding a new company email address.  For more complicated tasks like adding a subdomain with users, databases and associated users, the customer is not doing that -- someone they are paying is doing it through cpanel. 

 

I don't use cpanel unless it's already part of the equation, and the reason is simple: I don't need it, and it uses resources.  It requires disk space, and the apache processes and databases it utilizes have to come off the top of what is available.  The first constraint you typically hit in a lamp app is the availability of memory.  Everything runs better when there is lots of memory available.  I don't see it as a benefit to take a chunk of that available memory and have to give it to cpanel purely so that once very 6 months someone needs to login for 5 minutes and do things that I can easily do with command line utilities in less time.

 

However, many shared and dedicated hosts offer the ability to have all the same software they use.  These are often billed as reseller accounts.  If you want to pay for that they will provide it for you.  There are also plenty of solid alternatives like Webmin that do most if not all the things that cpanel does:  http://www.squidoo.com/10-free-cpanel-alternatives

 

There have been times I've setup a vps server for someone, and added webmin and usermin to give them the ability to do a few routine tasks they require. 

 

Once you understand linux itself and how a distro package manager works, and how applications tend to be laid out and how they work, you're in a good position to start poking around cpanel and seeing how it works and what conventions it uses.  None of it is magic.

 

Once you get start on this path it's a natural progression to learn about apache, and mysql administration.  There are plenty of books and tutorials out there, and the main thing to realize about mysql is that like most all rdbms's you interface to it with sql commands.  Once you go into the mysql client and create a new database, create a new user and grant the user  permissions to the database, I'm sure you'll realize that the value of cpanel to do that for you is pretty minimal. 

 

To conclude, there are many different hosting companies, with different divisions and different product lines.  You can find all sorts of different packages of services out there.  It really all comes down to what the customer wants, and how much they are willing to pay. 

 

The bottom line is: shared hosting is a cut rate entry level service.  Some companies do that better than others, but it's primarily designed to get people who want to host a few simple web pages.  This works for the hosting companies because most of these websites get no traffic, and consume no resources.  They get 16 page views a week.  What support is the customer actually getting?  Is there site backed up completely and accurately for free?  If you install an application do they provide support on that application?  Who exactly do you get when you call customer support?  What actually happens if your site gets wildly successful, or they perceive you to become a resource hog on the server?  The perception that shared hosting is intrinsically better supported hosting, is just plain wrong.  When people spend more on a single expensed lunch for employees than they pay for their web presence for a year, that should indicate that the customer has not been properly educated, or simply doesn't care about their web presence. 

 

If it's public facing, they may care when some kid who setup a phpbb for his friends, gets exploited, and turned into a spam bot, and his host (which shares the same IP address as your customer) ends up on an RBL list, and all your email now gets spam blocked and your customers site is getting filtered by Norton 360. 

 

[fenway]

Either you're a web developer or a sysadmin -- doing both is dangerous -- you're unlikely to be an expert in both (@gizmola - present company excepted), so something's going to suffer.  And your server being compromised is much worse.

[jhsachs]

gizmola, thank you for the pointers. The concept of a "reseller account" is a valuable one, and I will look into it.

 

I will also see about installing Linux on one of my machines. I did that a couple of years ago, but at the time I was interested in using Linux instead of Windows as a development platform, and the difficulties I had finding a workable backup solution persuaded me that it was far more trouble than it was worth. For a learning platform the requirements are, of course, much less demanding.

 

I want to correct an assumption you appear to have made, which may have colored your responses. Because I do not have Linux sysadmin skills, that does not mean I am unfamiliar with Apache, MySQL, or PHP. I have installed all of them under Windows enough times, and configured them enough, that the process holds no terrors for me. There is, of course, a great difference between knowing how to configure xAMP and knowing how to configure and maintain an effective xAMP server, just as there is a difference between knowing how to install Linux and knowing how to maintain a properly configured and backed-up Linux development system.