This article is a continuation of my rant about Why Open Source Software Sucks. In the other article I expressed frustration about bugs in MySQL that they consider features. At the time MySQL pointed me to PHP and PHP pointed me back to MYSQL. So I’m continuing my rant and focusing on the technical issues so that I can actually accomplish what I need to accomplish in order to host Dvorak Uncensored properly. Before I beat up on the open source community more I’m going to give people a chance to prove me wrong. Show me what I missed in the manual. Or perhaps what I’m asking for is just too hard for hobbyist programmers to figure out.
MySQL and PHP are doing some circular finger pointing relating to bugs that are probably the fault of MySQL. Both sides say that “This is not a Bug” but what is and isn’t a bug to them isn’t the same as what is and isn’t a bug to me. To me – if it doesn’t work – it’s broken, it’s a bug. To me when a program fails to do something that it obviously should do, it’s a bug.
I first posted the MySQL bug. Ultimately the admins closed the bug and pointed me to the link at the bottom of the bug telling me to report it to php.net. So I went to php.net and posted this bug.
Since these are both open source projects there’s really no one who is accountable to fix bugs. So ultimately I have to dig up tools to figure out a way around it myself. So far I have yet to find a solution that actually works.
Here’s the situation I’m trying to deal with. I have a fairly mighty server. I host about 400 web sites including this one and about 100 of them use some sort of PHP interface that calls MySQL on the back end. Dvorak Uncensored is just one of many interesting sites running on this computer. I do a lot of celebrity hosting for interesting people who have interesting things to say. Most of these sites use software that is installed and configured by other people so going through all the directories to find all of them is impractical. Even if I did, new applications want to install by default to talk to MySQL on “localhost”.
Now I’m trying to expand the capacity, security, and stability of the system. So what I want to do is move the MySQL server to another machine and to set something up on the original server to intercept calls to MySQL configured for localhost and pipe that over to the new server where the database actually lives.
Generally setting this up is easy. You shut down the MySQL server on the main host, copy it to the new server, start MySQL over there and then set up an SHH tunnel through autossh that listens to the TCP port on the main server and pipes it over to the new server for processing. In the past with other applications this is easy, it works, and is reliable. The command to connect these servers is:
autossh -M 3337 -f -N -g -L 3306:localhost:3306 mysql.ctyme.com
But – it doesn’t work. It works if the application is set up to talk to 127.0.0.1 but not if it’s configured to talk to localhost.
The problem is that the folks at MySQL redefined the common definition of “localhost” to mean the socket at /var/lib/mysql/mysql.sock. A socket is something that looks like a file but is really a communication portal into an application. In this case MySQL. So in the MySQL world localhost != 127.0.0.1.
Even though MySQL seems to have settings to specify to use TCP (Protocol=TCP) instead of the socket MySQL overrides all these settings forcing all MySQL calls to the socket. So – how do we make this work?
One solution is to find something that creates the socket and pipes it into the TCP port so that MySQL client applications that insist on going to the socket find a fake socket to talk to. But instead of it being a socket that talks to the local MySQL server it’s a socket the redirects to a remote MySQL server on a different box.
I found a utility called socat. Socat looks like it should work and it actually did work – for a while. Here’s my socat configuration:
socat UNIX-LISTEN:/var/lib/mysql/mysql.sock, fork, user=mysql, group=mysql, mode=777 TCP:mysql.ctyme.com:3306
The problem is that it works great but as soon as I go to sleep it quits working so I wake up to a bunch of emails and phone calls from people who are wondering why their sites are down. I like to be able to sleep at night knowing that I’m not going to wake up, or be woken up, to a disaster. I have this weird expectation that software should work and keep working reliably.
So far Google (which is a deity) has failed to lead me to a solution. I have found a lot of other people with the same problem that I have, but no solutions. I am convinced the problem is solvable if I’m persistent enough.
So – for all you smart people out there who really know Linux the question is – how do you do it? How do you take applications that are configured to talk to MySQL on localhost and redirect them to talk to MySQL on a different server WITHOUT going around and reconfiguring them to point to the other server? Or is this something that the Linux world can’t do because they are a bunch of hobbyist programmers?
I have to admit I am with a lot of other people here in thinking that 1. Trying to say that mysql or php has a ‘bug’ because you can’t point localhost to another server seems like a long shot, regardless of how its put. I know it seems like ‘in the real world’ you should be able to point localhost somewhere else, but it seems that in this real world you cannot. and 2. If you want to run the database on the separate server you will need to reconfigure all the apps.
Can I offer, as another potential work around that hasn’t yet been suggested, just getting a bigger/fast/better server (I hear PS3’s work well in a cluster :-D) so you don’t have to separate the mysql server? My real world experience has been that often you just have to accept your fate and work with it rather than fight against it!
This is what I understand the problem to be. Marc moved his MYSQL server to a new machine to seperate it from his web server machine. That means the new MYSQL server must listen for remote request from the web server. According to MYSQL docs, you need to state in the my.cnf of the new MYSQL server the following:
[client]
protocol=tcp
#bind-address=127.0.0.1
#skip-networking
The protocol line makes MYSQL use TCP instead of sockets. Commenting out the other two lines makes MYSQL listen for remote request.
Another thing is checking to make sure in the hosts file of the new MYSQL server there is a line:
127.0.0.1 localhost
I have also read that in certain linux distros, Red Hat and Slackware, there is a system config file in the /etc directory that might have a line telling MYSQL to listen only from the local not the network.
The only thing I have not check out yet is how to get the web server to direct MYSQL request to the new MYSQL server. I am thinking that would require some network configuration file on the web server.
Ho Hum – I post a problem and still no solutions. All I see are people trying to cover up the problem with insults. The open source solution to dealing with bugs is to attack the person reporting the bug.
#31 – I can point localhost to another server easilly with an SSH tunnel. The problem is that because MySQL is buggy I can’t point MySQL to localhost because MySQL has redefined localhost to mean something other than localhost. If MySQL actually talked to the real localhost which is TCP 127.0.0.1 then redirecting it is trivial.
The problem is the MySQL breaks common definitions and forces a socket connection instead of a TCP connection and they override the user settings so that it goes to the socket even when you tell it to do TCP.
#21 thanks for the heads up on the new version of socat. I might look into it.
#32 I tried that and it doesn’t work. At least it doesn’t work from PHP which perhaps is supposed to read the my.cnf file and obey it. Or perhaps it is the MySQL client libraries that ignores it.
Yes – you would think that it would work:
[client]
protocol=tcp
But if you specify localhost it still goes to the socket. It’s a bug and they are too lame to fix it. Where I come from when you tell an application to use TCP on localhost you expect the application to make a TCP connection to 127.0.0.1 – not a socket connection to /var/lib/mysql/mysql.sock – but they are a bunch of hobbyist programmers and they don’t take software development seriously.
Stop this nonsense. Stop it now! Is this a joke?
We need to accept that lots of things in life aint perfect and realise that this is one of them. And Marc, you need to realise that you made this problem for yourself just as much as any PHP or MySQL dev team did (don’t ask me to explain, you know it’s true that’s why you are so mad at yourself right now).
I think it’s time for everyone, yes you Marc, me, everyone and defiantly John ( 🙂 ) to get over themselves. Let’s all make cakes, drink tea and chat to someone who has no idea what a boolean is.
I mean it now! I don’t want to hear anything more about it!
Marc,
Here’s the fix to your socat hack:
nohup socat UNIX-LISTEN:/var/lib/mysql/mysql.sock, fork, user=mysql, group=mysql, mode=777 TCP:mysql.ctyme.com:3306 &The program works fine until you goto sleep. Are you logging out of your system then? I probably am myself.
It sounds like socat is not detaching itself from your login session when you start it and it’s getting shutdown when you logout.
The nohup command will detach the process from your terminal when it starts. The & symbol tells the shell to run the command line in the background.
Again, you still are just hacking around the real problem. Take jlm’s advice in #6 above and move some of the sites to the other system. You’ll do a lot better by your customers in the long run.
Having been called ignorant and unhelpful by Mr. Perkel in #19 I have taken another look at the description of the situation and see 1 problem in 2 instances: both parties are trying to re-define pi to be 3. MySQL is redefining localhost to mean a socket, while mperkel is redefining localhost to mean “that other machine, over there.” Neither is correct, as #9 pointed out.
#26: sockets are not always faster than the tcp stack to localhost. It may be on linux, but other OSs may not exhibit this behavior. The proper thing for MySQL to have done would be to define a “socket” protocol instead (perhaps they have, I did not look). By re-defining the meaning of localhost under the covers, MySQL has deviated from unix standards and made administrative problems like the one mperkel is facing more difficult.
But mperkel is trying to do the same thing. He is trying to re-define localhost to mean something other than the machine on which the software is running. This is just as bad as what MySQL did from a standards perspective, and standards are what most commercial unix software attempt to implement. This is why what he wants to do would work with most other databases, but it doesn’t mean he should.
mperkel: all the software involved has the ability to cope with a network connection between php and mysql, and the answer to your problem is to use it. Do not try to subvert the meaning of localhost just because you can (#26 can complain about performance overhead of ssh encryption and port redirection now). Instead, use the tools you have already been given. Use your favorite editor and re-configure your front end software to use the name of the database machine.
Even better would be to think ahead: define a pool of database server names and have them all be the same for now, or perhaps a round-robin setup in your internal dns server that just has 1 address for now. Do the same with the other components of your service while your are at it and you will be able to expand more easily in future. There are many blueprints available to help you plan for expansion and you should use them.
MySQL is broken, and they should fix it. But they will not, or can not. mperkel’s configuration is also broken, and I expect he will tell me how wrong I am instead of fixing it.
#37 Byron, I am not too sure that MYSQL is broken in regardes to the localhost. MYSQL can run in different mode, stand alone, networked or clustered. Stand alone mode defaults to using localhost as a socket but can be changed to use TCP. Network mode can use sockets as well as TCP but you must place the server in listen mode by commenting out bind-address = 127.0.0.1 and/or skip-network settings. In cluster mode, you are able to configure several machines to be servers and data nodes. That mode uses DNS from what I read.
What mperkel wants to do is have applications on the webserver use localhost on the webserver then redirect that to the localhost on the new MYSQL server. I am not a network expert but I would think you would need to create an IP alias on the webserver that connects to the IP address of the MYSQL server. Then all the applications must point to the alias.
But mperkel will still say that there is a bug in MYSQL.
I have not heard too many network experts chime in though. May be mperkel needs to post his problem on some blog dedicated to networking. He might get more help.
I manage windows servers for a living. What we see here is the result of somebody lacking the understanding of what localhost is. And what it is used for.
It is perfectly reasonable for MySql to optimize the handling of localhost. Because localhost is ALWAYS the local machine. That is why it is called localhost.
Localhost is NOT the default name of the server. It is ALWAYS the name of the local computer. Localhost is found on all computers using TCP/IP. If it is not there, then the computer is not configured right.
ALL TCP/IP software assumes that nobody have fooled around with localhost. You screw up with localhost, random bits of software can end up not working. That applies to both Windows and Unixes.
greetings,
“but they are a bunch of hobbyist programmers”. Oh? Why not open that fat wallet of yours and buy a MySQL support contract? You DO realize that there is a full-blown commercial version of MySQL with professional support?
Quit bitching that you’re not getting your money’s worth from your software. You paid nothing for it, you’ve made money from it, and now you’re complaining no one’s fixing it for you.
Face facts, your system’s configuration is poor, and you’re trying to cover it up by spackling on more “hobbyist” software with one temporary patch after another. Reconfigure your apps to point at something other than localhost and your problem will vanish.
Us ‘hobbyists’ are under no obligation to “fix” OUR software to accommodate YOUR crazy setup.
Ok so if we understand that localhost is not a machine’s hostname, and it’s intended to always refer to the local machine, it’s obvious what needs to change. The problem is that no matter how many times that is explained Mr. Perkel covers his ears and screams, “LALALALALALA”.
MySQL problem: localhost binds to sockets (and yes sockets and localhost should be separate, but they’re not)
PHP problem: localhost looks for MySQL listening on a socket (and yes sockets and localhost should be separate, but they’re not)
So DUH even if you find a way to forward MySQL, PHP is still going to be looking for a local MySQL.
Sounds like the SOCAN hack might work, but in the end it’s quite apparent that you really should have been assigning host names for each customer from the beginning. Look into implementations like cPanel and you will find that this behavior is the default, and that they do NOT use localhost for MySQL connections. I understand the frustration of an implementation that was supposed to work not working, but this is crazy, not on Windows or on Linux will you find a situation where localhost is not the local machine. I mean jeez, what about that is so hard to understand?
#36 Thanks for the tip. But that’s not it. socat is still in memory – it just quits working. I suppose I could try to rig something to test it every minute and if it’s not working I could kill it and restart. But I think it’s already bizzare that I have to use socat in the first place.
#42
You don’t if you would just set things up properly, but you are still trying to kludge your way through this, but I guess this is how attention whores work.
-Fratm
After reading all these posts, I can’t help but wonder just how much duct tape and bailing wire is being used to hold together MPerkel’s 63 VW Vanagon in order to keep it running.
wow, so many lengthy replies about simple misconception!
i quit reading after #3 so my apologies if someone else had it said already:
mperkel,
=======================================
you can’t have localhost on a non-local machine
=======================================
or
it is not *local* anymore (but remote), don’t you see this fundamental flaw of your diatribe?
and how can you complaint about it so blatantly without thinking, geez, dude…
(i agree localhost should not point to socket, but thats not actually your problem here – trying to set remote host as localhost is)
It appears that most replies don’t understand what an SHH tunnel is. Several people haven’t grasped the concept of the problem presented. No one has presented a working solution.
As to grasping the problem. The issue here is to show a way for applications configured to talk to localhost to be forced to actually talk to 127.0.0.1 where it can be easily redirected with and SSH tunnel WITHOUT having to find a hundred config files and change them all. So several morons suggest that the answer is to change all the config files. My responce – RTFP (Read the fine problem).
The bottom line is that apparently MySQL is to buggy and none of the hobbyist programmers here have a solution or a work around and can’t solve the problem. I suppose it is just too difficult for the open source community. If they aren’t smart enough to comprehend the problem then how can one expect a solution?
Try again for those who can’t grasp the problem. The main server has no pop/imap server but you would never know that because I’m redirecting ports to my email server.
autossh -M 3335 -f -N -g -L 110:localhost:110 -L 143:localhost:143 -L 993:localhost:993 -L 995:localhost:995 mail.ctyme.com
In this example people who had previously configured their email client to ctyme.com are piped to the correct server mail.ctyme.com and everything works. That’s because the dovecot server uses TCP and localhost means localhost.
But MySQL is broken. It doesn’t work under MySQL because in spite of any settings MySQL talks to the Unix socket instead of the TCP port.
If you can’t comprehend the problem then you have no reason to try to fake a solution. You can’t blame my for what MySQL does wrong.
#45 Sure you can have localhost on a non-local machine. You pipe it remotely with an SSH tunnel. I do it all the time. It’s trivial.
Before you accuse me of not thinking I think you need to become familiar with tunneling.
Fine, we’re morons for suggesting you do the right thing and change your configuration so it works, instead of stuffing more temporary fixes into the mix. I think it’s fair to say we comprehend the problem quite well. We’ve offered the proper solutions, and you’ve refused to accept them. As such, you’re stuck in this rut.
Doesn’t matter how you look at the MySQL “problem”, whether it’s right or wrong. It’s the DOCUMENTED BEHAVIOR. Besides, you’ve been told what the correct settings are to get your apps to talk to a TCP socket instead. The fact that those settings don’t match your configuration and you’re unwilling to modify the configurations to accommodate those settings is not MySQL’s fault.
Accept it, “localhost” in mysql clients means a local socket, not 127.0.0.1. It’s a special string in MySQL. If you add a new alias for 127.0.0.1 to the hosts file (say, “mysqlbypass”) and use that as the MySQL connection string, guess what… it uses TCP, even though it’s still talking to “localhost”.
You’re still left with the problem that your apps are using a multitude of different MySQL interface libraries, and all are configured to talk to the special “localhost” string. Change those configurations and you’re fine. Refuse to touch anything and you’re stuck with socat dying regularly.
The main thing is that this is not the MySQL SERVER’s problem, it’s the MySQL CLIENTS. Two completely seperate beasts, you know. If the clients are not parsing the my.cnf for proper configuration data, then either you have to update all those client libraries to versions that WILL parse my.cnf, or you have to modify the apps. Either way, you’re stuck modifying your apps. Anything else you do is a temporary patch and subject to failure.
Let’s describe your setup with an analogy: You’ve got a PC with an ATI video card. You have to do an upgrade to an nVidia card, but you refuse to install the nVidia drivers. You install some software that on-the-fly translates ATI driver calls into nVidia ones, but it dies regularly. Whose fault is this? ATI for not using nVidia access calls? Or yours, for refusing to accept the reality that you’ve got a different card in there and have to update the drivers?
Who’s the moron now? Tracking down the 100 or more configuration files is a matter of some grepping, sed scripts, and possibly a bit of manual file editing. Personally, I could’ve done it in an hour or so. And then it’d be done and you can move on to other things.
As for your email forwarding, sure, that’s one way to do it. I’d have gone a slightly different route: A forwarding-only mail server whose sole purpose is to forward to the other mail server. That way, I wouldn’t have to worry about the tunnel dying for whatever reason. If the network link burps or barfs for any reason, you’re not spamming forwarded data at a now-dead link, the local mail server just queues it up until the network’s back, at which point everything magically resumes forwarding. Much, simpler, no?
I posted this before, but got ignored so let me post it again. I believe that instead of screwing around with ssh and socat that you need to use something like Mysql Proxy.
http://forge.mysql.com/wiki/MySQL_Proxy
What is MySQL Proxy?
MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing
#49 yep – it’s been suggested. Show me how it can do what I’m looking for.
#48 Just because MySQL documents their bug as a feature doesn’t mean it isn’t a bug. It means they are pathetic.
Just wasted another hour on mysql-proxy. Where mysql itself want to do everything listening on a socket mysql-proxy has no feature to listen on sockets. So mysql and mysql-proxy are basically incompatible.
The dos are really poor. Does anyone see how to make mysql-proxy listen on /var/lib/mysql/mysql.sock?
If mysql can’t listen on sockets, isn’t it rather lame?
#50. By your reasoning, then, pretty much everything Microsoft has ever done makes them pathetic, since their Knowledge Base is filled with approximiately 100 gazillion workarounds for documented design stupidities and bugs.
Sorry, but you lose yet again. Open source is no worse or no better than commercial software for weird/dumb/stupid mistakes, but at least with OSS you can find workarounds for them. … assuming you’re willing to listen and implement the suggestions from people.
Jesus Christ Mark. You complain that nobody has solved your problem when you’ve made almost no effort to describe the details. Post server logs at least, geez.
Anyone posting their network problems on a General Blog and expecting a technical answer is fooling himself. Please take your problem to a blog about computer networks. You will get your answer there.
It would help if certain information was known.
Software used and version numbers.
How many actual computers are being used and how are they networked.
A way to view my.cnf and php.ini files
Without this type of information, anyone who tries to help is in the dark.
Okay, Perkel, here ya go. You’re right. MySQL is horrible, buggy software written by arrogant, hobbyist programmers. Nobody else can fix it, either, because we’re all simpletons who can’t understand the nuances of the problem. In fact, this whole OSS thing is so bad that one would truly have to be a total idiot to even use such things in production systems.
Never mind that very few with similar credentials and experience would have even seen this problem, because very few would have even considered such a ignorant, mindless configuration/setup to begin with. You thought you were being slick with the localhost thing, but truthfully, it was stupid and you’re getting burned for it.
Never mind that most of the posters here could actually fix the predicament you’re in, using solutions posted in this very thread, in less time than it’d take you to pack up your desk after getting fired for not being able to fix a major problem that you brought upon yourself.
It’s one way or the other. Either Mysql is buggy and written by arrogant jerks, and some idiot still decided to run it, or an idiot configured it and caused a problem where there should be none. No solutions are “valid” because someone obtaining their livelihood from OSS is to scared to write a bash script using find, grep, and sed, fixing the source of the problem, or using the freely available source code to duct-tape a trivial fix for the idiotic problem.
The perfect summation for all of this is for John to drop in and say something akin to “Heckuva job, Perkel”. Oh wait…
Mr. Perkel (Marc?),
Your code, I presume, is instructing the MySQL client library in PHP to attempt to connect to “localhost,” which it interprets as a socket on the local machine. You do not want to update the code. You want PHP connections to MySQL to continue connecting to a socket on the application machine. You want to forward that socket to a TCP port on the database machine.
Relevant questions are whether PHP is properly communicating with the socket on the application host, whether MySQL is properly accepting connections on the database host, and whether your tunneling software is correctly forwarding data from the socket to the TCP port.
You seem to have made not the slightest effort to address any of these questions.
Have you tested?
The rather well documented fact that mysql uses ‘localhost’ to refer to a socket is a red herring.
From everything you have said on the topic, I have seen no indication that PHP or MYSQL are involved at all, beyond that they are communicating correctly to their respective connections.
In other words, that dog won’t hunt.
Focus on the problem.
The rest of these morons have simply taken your troll-bait and swallowed it whole…whoops, instead of helping you solve your problem. Frankly, you seem to be trying to make yourself out to be a bit of a jerk, which strikes me as odd and out of character. You are obviously an intelligent and capable person, yet you are letting off steam in a public and inciteful rant, in lieu of resolving the causal issue. I hope you find a satisfactory solution to your problem and this nonsense “Open-source sucks” rampage can be appropriately forgotten.
Best of luck, Marc. What are the *facts*?
(PS: Some of the comment posters exhibit tremendous ignorance of socket and TCP/IP communication. Don’t take those tards as representative of the open-source developer/user-base.)
This is Dan again.
Just to be clear, MySQL definitely sucks, and it is retarded and only seems to be popular because it rode the coattails of the Linux explosion. The “speed” myth is a fallacy for any non-trivial query. Let’s talk referential integrity. How the locking system is a hodgepodge of suckery.
MySQL is used widely and broadly because most applications of databases don’t use sensible data structures and ask for merely a place to put stuff. MySQL is able to fill that role, but unfortunately cannot do much else.
Really, I’m surprised any professional would admit to using such crap when sophisticated ACID-compliant database servers like PostgreSQL are available for free as well, and under a less onerous, non-viral license.
Where’s the beef? Upchuck some cash for, *snicker*, Microsoft SQL, or use something that doesn’t suck.
MySQL has a wide reputation for being a hackish piece of crap, and you’re running it anyway. Stop it.