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?
Marc, at this point I think you’re just trying to goad the open source community. It’s inflammatory comments like the last sentence of your post which really get people upset. You’re bug reports weren’t written in the nicest way either, especially the MySQL one and frankly when you practically scream through the bug report I don’t blame the developers for not being interested in helping you. You seem to continue to fail to understand that you’re asking to be able to do something that is not supported and fail to realize that exactly the same problems can and do easily happen with commercial software.
There’s always an excuse, a reason, or a personal attack as to why people don’t answer the question as opposed to actually having a solution. The open source community wants to say that the questioner committed some sort of heresy and therefore deserves it. I think the computing community is giving the open source community intellectual welfare holding them to a lower standard like it’s some sort of affirmative action for the technologically disabled.
As someone who dealt with corporate IT projects and vendors for several years I have to say the high regard you hold for commercial software just doesn’t wash with my experience. I would say the same things about lots of commercial software that you say about open source. Not that I’m particularly defending open source, it has its problems for sure but I’m just saying that commercial software doesn’t solve many of them.
Hi,
I’m with you that this is a bug, but they are clearly unwilling to fix it. As a workaround, would adding a second mapping in /etc/hosts to 127.0.0.1 work? something like
127.0.0.1 localhost mysqlbug31577
and then use mysqlbug31577 as the hostname for mysql? That should bypass their string check, unless they are resolving the hostname first and checking for 127.0.0.1 of course.
Just a thought.
I just want to tweak everyones nipples a little bit. But on this particular Issue I am finding WAY better info off of live.com. Perkel, if you switch to live.com you will find that the crashing issue is related to mysql and php being built against differing versions of zlib.
My suggestion is…
./configure
make
make install
both software programs against the proper version of zlib and you should be good to go.
That should be Microsofts new advertising campgain. Better at google for solving Linux problems.
LOL!!!!
It sounds to me like you need this second box because you have way too much on that one server…so I see a few options:
go through all site’s config files and point them to your new mySQL box. No need for the SSH with this, mySQL ssl should be fine. (yes this is a ton of work, but if you keep complaining about it then you will spend just as much time anyway)
or put 1/2 of the sites on one box and 1/2 on the other, moving appropriate databases for these sites, then you wont need any localhost hack.
or just go ahead and convert to apache+php on windows and give it a rest already since its obviously so much better.
–
Yes there is always an “excuse, a reason, or a personal attack” when the person complaining is trying to force a hack on something that doesnt need it.
#5 The program that quits working is socat. Has nothing to do with zlib.
So… let me get this straight… you think commercial software companies would work together to fix a bug that could be palmed off on another company?
Wow… for someone that has worked in the IT industry for years… you do seem VERY naive.
Open source isn’t the savior and solution to all problems in the software industry, it never claimed it was, most of the problems are caused by people. However with open source you do at least have options…
This post, in fact all of these posts are blatent flame bait. Pathetic.
As a hosting provider myself, I’d say the real problem is trying to use defaults for non-standard configurations. Personally, when I assign a hosting account which uses mysql, I assign a DNS name for that account’s mysql server and tell the client to use that hostname for the mysql server. That way, I can move individual clients’ to different (or the same) mysql server by simple DNS updates without bothering them. I totally understand wanting to “make it easy for them” by just letting the default of localhost work, but it limits things too much, isn’t even close to scalable, and leads to this kind of problem Mr. Perkel is having.
I will grant that this is indeed a bug (notably, a bug caused by a developer wanting to “make things easier/better” for non-techie users; I can totally see why they did it even if it was a dumb decision).
Mark needs to grant that in the OSS world, where the s/w is free and (most) programmers don’t get paid, that asking for help is *asking for a favor*. Obviously, a developer doesn’t owe you anything unlike in the commercial software world. While he may very well insist that the developer’s arrogance is just an excuse, the truth is, I know of few people who would grant a favor to someone if that someone was as demanding and pissy as we see in that mysql bug report. Didn’t your daddy tell you that you can attract more bees with honey than vinegar?
This isn’t saying that Mark’s not dead-on correct with his assessment of the attitudes frequently found in the OSS world. There’s a ton of arrogance, and a lot of it is quite unjustified. This forced re-definition of localhost is a good example.
Of course, none of this helps the real problem, which is to get this server back up and running in the manner in which its sysadmin wishes.
First, have you tried the solution given in the PHP bug report? The reply states that the default client libraries *won’t read* the my.cnf file; that you need the MySQLi libraries and set a flag to tell it to read the defaults file.
There’s also the concept of simply going into the source code of the php client library you’re using and putting in one well-placed comment line to remove the check for the string “localhost”. This in itself is one of the great benefits of OSS which typically isn’t available in commercial software. All the package managers around have gone to great pains to make this fairly simple thing to do. While OSS software might not be “just run it” simple, the truth of the matter is that in exchange, you are given the ability to make it do *exactly* what you want it to do. If you had such a problem with a commercial product, you’d be at the mercy of the developer. With OSS, you ultimately can fix it yourself should you desire. And given the google results on this issue, perhaps there are a good number of people who would appreciate what is likely to be a *tiny* patchfile.
Barring that, it looks like you’re going to have to bite the bullet and do a global search on the system for “localhost” and write a little script to change the appropriate ones to either “127.0.0.1” (or use my solution I mention above). A pain in the butt, to be sure, but even if the MySQL developers had any intention of fixing this bug, I’m certain that the tone of the bug report has hardened their heads against it and there’s no way they’ll do it now. Unless, of course, you pay MySQL AB for some commercial support, which does indeed grant the privilege of being demanding.
Meh. Unix /Dec / SCO OpenServer programmer w/Informix for neary 15 years. I even got Linux certification (Red Hat 8 & Fedora Core 3).
Last 5 have been exclusively Microsoft – not planning to ever go back.
There just isn’t any major Corps that have demand for Linux work. Not in my backyard at least.
I run PHP, MySql on Windows Server 2003 ONLY to run WordPress.
Eventually the Open Source Dot Net community will have something just as good as WordPress, the trend is changing, even if slowly.
Its funny…
A question has come up about, “What IS’ a Virus/trojan”??
The only difference is that VIRUS are Severe or cause DAMAGE.
Between all the forms of bots…from Dialers to keyloggers WHAT is the difference. WHICh program SHOULd I use to kill these, and WHY do I need MORE then 1.
To myself they are ALL virus, as I DONT WANT them, I DIDNt ask for them, and THEY DO, cause problems. esp that $400 phone bill.
On the thought of Linux vs MS…
How many groups have gotten together to MAKE/Force programs to be made under Linux and eventually were Bought out, or someone escaped and SOLd off the products?
MANY MANy games USEd to be made Under Linux and ported thru DX to windows… that Stopped AFTEr no one made updates to the DX converter past 8.1….
It is free software… You sound as if you expect it to perform like paid, professional software. If it is not doing what you want, DON’T USE IT!!!
Go out and acquire a proprietary solution such as MS SQL Server, Oracle, or DB2. It won’t get easier, but you will have a higher degree of accountability.
Marc wants to use localhost to refer to a remote host and then complains that MySql uses a non-standard definition of localhost. The pot is calling the kettle black. This comedy of errors is not the fault of OSS in general but of bad design decisions on the part of Marc and the MySql developers.
In my opinion you have two options:
1) Edit the MySql source and recompile. Don’t know how to do this? There are plenty of talented programmers in the U.S. and abroad who you can hire.
2) Bite the bullet and require that all customer code be changed from localhost to the new domain by a specified date. After that cut-off date you will get phone calls, but after the initial complaints you won’t have to deal with this problem again.
Too many lines of code for you to scan yourself? There are plenty of talented PHP programmers in the U.S. and abroad who you can hire to help get this done!
At least MS SQL server plays nice and easily integrates/expands/and maintains much easier in an Active Directory environment without the little bullshit like this bug.
Mark, you’ve got two choices:
1) Reconfigure all those web apps to use proper mysql DSNs which point at the real server. With properly configured DNS mappings, any future migrations will require only a single host/ip mapping change.
2) Everything else.
In other words, bite a medium sized bullet now and get it over with. As you wait and look around for hackish fixes, the little bullet’s growing up into a bazooka shell and beyond. No amount of bug reports or help requests will get around the fact that your setup needs to be updated to adjust to the new reality. You owe it to your clients and to yourself to implement the CORRECT solution, and not flail around looking for hacks to extend the lifespan of a configuration already proven dead.
I am not a network expert so consider this post a wild stab in the dark. Hopefully, someone better informed than me can explain why my suggestion is not the solution.
Suggestion: Is there a way to make a setting in one or all of the following files; networks, resolve.conf and hosts, so that the old server will redirect MySQL and PHP request and send them to the new server?
Call me stupid if you want but I already know that. đ
You mention looking for a solution on google… Search for
find replace linux.
Third hit: http://www.liamdelahunty.com/tips/linux_search_and_replace_multiple_files.php
perl -pi -w -e ‘s/search/replace/g;’ *.php
Is this just me or are other people having problems with the RSS feeds for comments?
I use Firefox and lately the RSS live bookmark feeds show “Live Bookmark feed failed to load” when I click them.
Cheers
I see a lot of people making a lot of noise but I see no solutions. I don’t see any comments where the commenter even understands the problem. Looks like I’m right and the open source world is lame.
#19, Marc,
You have brought up a good point but there might be another message there.
No one has come up with a suitable solution. That could be because either this specific problem has never occurred to others or those who would know don’t frequent DU. There is also the possibility that those who might have the answer don’t understand the problem. This is way above my level.
No, you are not an idiot, stupid, conceited, or whatever for using OSS or even for having a problem. I have a lot of personal admiration for someone that admits they don’t have the answer and will keep working at it until they find the answer.
I wish you the best sir.
Marc — have you communicated with the author of socat, or tested his beta of socat v2.0? Maybe the new version is programmed to function even after you go to sleep đ
This may or may not help.
From MYSQL documentation ::::
The default hostname is localhost. For client programs on Unix, the hostname localhost is special. It causes the client to connect to the MySQL server through a Unix socket file. This occurs even if a –port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use –host or -h to specify a hostname value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the –protocol=tcp option.
:::::
Depending on what version of MYSQL, check to see if
bind-address = 127.0.0.1
or
skip-networking
is uncommented. In Debian it is and Ubuntu it is not by default. Make sure which ever one is there is commented out. Bind-Address forces same server communication only where as commenting out allows remote communications. Same for skip-networking. After making changes restart server.
I got most of the information here
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html
Especially in the comments section at the bottom.
Disregard if you already tried this.
#22 Thanks for attempting a good answer. I’ve used the bind-address option and it does bind to 127.0.0.1 but the client still goes for the socket. I’ve also tried protocol=tcp and it is ignored.
I’m surely not as qualified to argue the technical aspects of this discussion as the rest of you are.All I can say is, WOW!!! John C. is milking this topic for all it’s worth.This is surely driving tons of eyeballs to the site. You’re a genius John!!!! I guess the Apple fanboys have not been taking the bait lately.
I’m sick of this. If you pay the big bucks up front you get the happy happy joy joy support you seem to be looking for. If you pay a pittance or nothing (as in open source) well you get what you pay for. One way or another you gotta pay for it. If it isn’t money then it’s your time and as we all know time is money therefore there’s always a cost. It just depends on where you want to spend your resources (time or money).
I think that the answer has been provided several times, over and over, including you yourself during the initial explanation of the problem. It’s actually quite disingenuous on your part to say that we both don’t understand the problem or have any advice on how to fix it.
As you have been told, for performance reasons, when LocalHost is used as the server name, mySql does not bother going through the TCP/IP stack, since there is no reason for it to do so… after all, it assumes that it is staying within the LocalHost. Why loop through something if you don’t have to?
By specifying 127.0.0.1 instead of LocalHost, you force it to go through the TCP/IP stack, even if the connection will remain local.
By specifying anything besides LocalHost, the system will assume that you are going outside the local server and resolve the server name and use TCP/IP for the connection.
The problem is that the assumption was made during the initial system design that the connections would remain local, and now that you want to break them out of the local box, the LocalHost configuration is coming back to haunt you.
You have been provided the CORRECT solution several time… find a way to fix the applications that were configured to connect to LocalHost so that they either connect to 127.0.0.1 or to a DNS resolvable name. Sadly, that will require fixing the mySql client applications.
What you are looking for (and the whole core of your complaint is) is that you want a way to work-around a misconfiguration issue instead of fixing the real problem. As someone else said above this comment, that is just a patch looking to cause further problems in the future.
This has NOTHING to do with OpenSource or commercial software reliability or support… it is a problem that has obviously shown up due to lack of experience on your part when setting up servers with an eye towards flexibility or growth. My guess is that you will NEVER configure a system to LocalHost again, now that you know what happens when you do that. Lesson learned. Maybe now it’s time to stop looking for workarounds and get started fixing the real problem: the application that calls on MySql.
Enough said on this matter on my part… whine away, but the truth became evident a long time ago.
A flat out “open source world is lame” isn’t going to help you get any good suggestions for a fix. Insulting the world you’re reaching out to for help is a very good way to not get any help at all.
Don’t complain about your bug reports being closed/ignored. They’re not good bug reports. You don’t include any relevant details other than version number. No code snippets, no examples of the connection calls, nothing. Starting out a report with an insult (“INSANE”) really gets people motivated to help.
In short, you’re screaming “it’s broken, fix it!” and expecting people to fall over themselves to help you out. Not going to happen. That’s now how open source works. The stuff may be free-as-in-beer, but it does come with a cost – mutual cooperation.
Since you state a lot of the apps are using PHP, then how about details of how you’re getting PHP to talk to MySQL? Are you using the built-in mysql_* functions? mysqi? The DB or MDB2 libraries? PDO? What are the connection calls?
Let’s say it’s
$dbh = mysql_connect(‘localhost’, $db_user, $db_password); // mysql_* functions
or $dbh = DB::connect(“mysql://$db_user:$db_password@unix+localhost/$db_name”); // pear DB library
Assuming the apps use this consistently througout (adjusting user/pw as appropriate), then is it any wonder they’re going to use the local unix socket? It’s the documented behavior that “localhost” means “use the local socket”. No matter how you fiddle with the clients’ configurations or socat into the mix, they’re still going to talk to the socket, because that’s how the apps are setting up the connection – AS THEY’RE CODED TO. You need to change things to
$dbh = mysql_connect(‘ip.of.new.server:port’, $db_user etc…);
in all those apps.
Dance around the issue all you want, barring any more information from you, enough information to see what’s REALLY going on, all we (the “lame” open source people) can do is offer default answers.
As such, all I can suggest, from what little info you’ve provided in your posts here and in the bug reports is to change all your mysql connection definitions in your apps to point to the new server, or keep spinning your wheels trying to get someone to provide a free fix to your problem.
HINT: It’s not going to happen, since you keep calling people lame and apps insane.
@26 – Great explanation
@27 – “A flat out âopen source world is lameâ isnât going to help you get any good suggestions for a fix. Insulting the world youâre reaching out to for help is a very good way to not get any help at all.”
Um, that’s exactly what he has received, so I believe you are in error.
Don’t you all see it? Marc is an attention whore, and he is just posting this fud to get attention, each of his posts get tons of posts, this strokes his ego, he enjoys this.
You could hand him the solution on a silver platter, and he would still complain and say OSS sucks or is lame.. Because it gets a rise out of everyone.
So stop stroking the whore’s ego. Problem solved.
-Fratm
#20, Mr. Fusion, I wish you the best sir.
Comment by Mr. Fusion â 10/20/2007 @ 8:29 pm
Cha-Ching!!
Wish I could help, but I’m not a Linux server or OSS SQL expert. Were I, I would have spent some time (like a few others here) helping out rather than ranting on.
Keep up the good work, Marc, I’m betting you’ll find the solution before your typing wears out another keyboard.