Monthly Archives: October 2011

I just picked up an old Dell Precision 690 workstation, which I intend to develop into a file server, a Windows IIS server, and an Ubuntu LAMP server. This monster was built in 2006, but it still has some neat specs and tons of capacity (7 PCI slots, 4 hard drive bays, etc…), should I want to expand further.

Dell Precision 690

Dell Precision 690 Workstation

The main specs

CPU: Dual Core Intel Xeon 5060 3.2GHz, 4M Cache, 1066 MHz FSB
RAM: 2GB DDR2 PC2-5300, CL=5, Fully Buffered, ECC, DDR2-667
HD: SAS Fujitsu MAX3073RC 73GB, 15000 RPM, 16MB Cache
Video: Nvidia Quadro NVS 285 PCI-Express, 128MB

This is not a normal tower

Right away, the size of this thing suggests it isn’t a normal tower. It’s about up to my knee and weights 70 lbs. It feels like it’s made with heavier gauge steel than the typical chassis, but that may be me projecting.

I immediately shopped around for more RAM, obviously. 2GB seems a little thin, even by 2006 standards, when considering the way everything else is high-end. The mainboard has 8 slots and supports up to 32GB, but I figure 6GB is a safe place to start.

The workstation has three enormous fans, like, big-as-your-hand big. Running it with the chassis open causes some sort of thermal protection system to kick in and it spins the fans up to the point that they were blowing stuff on the floor half-way across the room.

The CPU has a big, passive heat sink with six copper pipes and sits between two of those fans. I’m tempted to buy a second CPU, but I’ll hold off.

I’m still on the fence about the SCSI drive. It should be super fast, but I’m a little spoiled by the SSD in my machine at work, so it’s hard to get excited about a mechanical drive, even one running at 15k RPM.

The Nvidia Quadro card is also fanless, and has a bizarre DMS-59 connector. An adapter converts the DMS-59 connector into two DVI outputs.

For a recent project, I needed to create a form that would perform a look up of people names in a MySQL database, but I wanted to use a single input field. To make it easy on the users of the form, I wanted the input field to accept names in either “Firstname Lastname” or “Lastname, Firstname” format, and I wanted it to autocomplete matches as the users typed, including when they typed both names separated by a space or a comma followed by a space.

The Ajax lookup was quick work with jQuery UI’s Autocomplete widget. The harder part was figuring out the most simple table structure and an appropriate SQL query.

A flawed beginning

My people table contains a “first_name” column and a “last_name” column, nothing uncommon there. To get the project out the door, I wrote a PHP function that ran two ALTER TABLE queries on the people table to create two additional columns for pre-formatted strings (column “firstlast”, to be formatted as “Firstname Lastname”, and column “lastfirst”, to be formatted as “Lastname, Firstname”), added indexes on these columns, and then walked through each record in the table, populating these new fields. I then wrote a very straight forward SQL query to perform a lookup on both fields. The PHP and query looked something like this:

// The jQuery UI Autocomplete widget passes the user input as a value for the parameter "name"
$name= $_GET['name'];

// This SQL query uses argument swapping
$query = sprintf("SELECT * FROM people WHERE (`firstlast` LIKE '%1\$s' OR `lastfirst` LIKE '%1\$s') ORDER BY `lastfirst` ASC",
mysql_real_escape_string($name. "%", $link));

This was effective, accurate, and pretty fast, but the addition of columns bothered me and I didn’t like that I needed to run a process to generate those pre-formatted fields each time a record was added to the table (or if a change was made to an existing record). One possible alternative was to watch the input and match either lastname or firstname until the user entered a comma or a space, then explode the string on the comma or space and search more precisely. Once a comma or a space was encountered, I felt pretty sure that I would be able to accurately determine which part of the input was the first name and which was the last name. But this had that same inefficient, clunky bad-code-smell as the extra columns. (Explode is one of those functions that I try to avoid using.) Writing lots of extra PHP didn’t seem necessary or right.

I’m much more comfortable with PHP than with MySQL queries, but I realize that one can do some amazing things within the SQL query, and that it’s probably faster to use SQL to perform some functions. So, I decided that I’d try to work up a query that solved my problem, rather than write more lines of PHP.

CONCAT_WS to the rescue

I Googled around for a bit and settled on using CONCAT_WS to concatenate the first names and last names into a single string be matched, but found it a bit confusing to work with. I kept trying to use it to create an alias, “lastfirst”, and then use the alias in the WHERE clause, which doesn’t work, or I was getting the literal column names back instead of the values. Eventually, I hit upon the correct usage.

The PHP and query now looks like this:

// The jQuery UI Autocomplete widget passes the user input as a value for the parameter "name"
$name= $_GET['name'];

// This SQL query uses argument swapping
$query = sprintf("SELECT *, CONCAT_WS(  ', ',  `last_name`,  `first_name` ) as lastfirst FROM people WHERE (CONCAT_WS(  ', ',  `last_name`,  `first_name` ) LIKE '%1\$s' OR CONCAT_WS(  ' ',  `first_name`,  `last_name` ) LIKE '%1\$s') ORDER BY lastfirst ASC",
mysql_real_escape_string($name. "%", $link));

The first instance of CONCAT_WS isn’t needed for the lookup. The first instance allows me to order the results alphabetically and provides me an array key of “lastfirst” with a value of the person’s name already formatted as “Lastname, Firstname”, so I don’t have to do it later with PHP. The lookup comes from the two instances of CONCAT_WS in the WHERE clause. I haven’t done any performance measuring here, but the results of the lookup get back to the user plenty fast enough, if not just as quickly as the method using dedicated columns.

The result of the query is output back to the page as JSON-formatted data for use in the jQuery Autocomplete.

The end result works exactly as I had hoped. A user of the form is able to type a person’s name in whatever way is comfortable to them, as “Bob Smith” or “Smith, Bob”, and the matches are found either way. The only thing it doesn’t do is output the matches back to the autocompleter in the same format that the user is using. But I can live with that for now.

While setting up a new MySQL account at a GoDaddy hosted web site, I kept getting an error when logging in to phpMyAdmin.

Error
#1045 – Access denied for user

For things like database usernames/passwords and other things that I’ll never have to remember or type, I like to use a long string of random characters. One excellent source of such strings is GRC’s Ultra High Security Password Generator. I typically use a subset of the 63 random alpha-numeric characters (a-z, A-Z, 0-9) in the bottom box. This gives me a good mix of uppercase, lowercase, and numbers, which satisfies the requirements of most password systems that require even minimum complexity.

So, I picked a string of characters for the database name and a different string for the password (making sure the password contained at least 1 uppercase character and 1 number), pasted them into the config.php file I was going to use on the project and then pasted them into the database setup form and created my database. No problem.

I gave it 10 or 15 minutes to get all set up and then launched phpMyAdmin. I copied and pasted the username and password from my config file into the log in fields and wham, I got the #1045.

After much second guessing and more copying and pasting, all with no luck, I tried resetting the password back in the Hosting Control Center. I waited a few more minutes for good measure and tried again. Still, #1045 – Access denied for user.

Then it was time to Google, which turned up a thread full of people with the same experience at http://community.godaddy.com/groups/web-hosting/forum/topic/mysql-login-error-1045-access-denied-for-user/?sid&sp=1&topic_page=1&num=15.

Back in the Control Center, I noticed that the mixed case characters I’d used for the database/username had been converted to lowercase. So I tried using the lowercase version at phpMyAdmin and still no luck.

I submitted a support ticket, as recommended in the thread, and then called Customer Support for good measure.

The guy confirmed that the database was in good shape and that the last password reset took effect, then had me reset it again. And of course, when I tried to log into phpMyAdmin a moment later with the lowercase username, it went right in.

The fix (or a plausible explanation, at least)

The lesson learned here, is that even though the new MySQL database setup form will accept mixed case characters as the database name/username, it will silently convert them to lowercase on you. The phpMyAdmin login, then, is case sensitive, so you may want to copy and paste from the Control Center into phpMyAdmin to be sure you’re feeding it the correct username.

I’ve been using the FileZilla FTP client for many years and in that time have had only a few occasions where the application didn’t perform with the default settings.

One of those instances was yesterday, when I was trying to connect to my firm’s FTP site from an external network connection. From inside the office, using the internal IP address, FileZilla connected normally and displayed the contents of the root directory after I authenticated.

From outside the office, connecting via the hostname ftp.domain.com, FileZilla would connect normally and authenticate successfully, but it would not display the contents of the root directory. Instead, the server would send a “425 Can’t open data connection” message. FileZilla would then report “Error: Failed to retrieve directory listing”.

Here’s the complete conversation between the client and the server (names and IP addresses changed to protect the firm’s identity):

Status: Resolving address of ftp.domain.com
Status: Connecting to 38.98.xxx.xxx:21...
Status: Connection established, waiting for welcome message...
Response: 220-Microsoft FTP Service
Response: 220 Company Name
Command: USER ftp_username
Response: 331 Password required for ftp_username.
Command: PASS **********
Response: 230-Welcome to the Company Name FTP service.  Unauthorized use is strictly prohibited.
Response: 230 User ftp_username logged in.
Status:	Connected
Status:	Retrieving directory listing...
Command: PWD
Response: 257 "/" is current directory.
Command: TYPE I
Response: 200 Type set to I.
Command: PASV
Response: 227 Entering Passive Mode (192,168,0,114,13,156).
Status: Server sent passive reply with unroutable address. Using server address instead.
Command: LIST
Response: 425 Can't open data connection.
Error: Failed to retrieve directory listing
Response: 421 Timeout (120 seconds): closing control connection.
Error: Could not read from socket: ECONNRESET - Connection reset by peer
Error: Disconnected from server

The interesting thing, I thought, was that when the server agreed to use passive mode, it did so with a port on the internal IP address, which is unroutable from outside the network.

The fix is to use active mode

OK, if you’re reading this, you probably just want to know how to make it work. FileZilla uses passive mode by default, but due to the network configuration of certain servers, active mode is required to establish a data connection. A bit of background reading with some explanation is farther down.

In FileZilla, click on Edit | Settings.

Under Connection, click on FTP and choose Active as the Transfer Mode.

Under Connection, under FTP, click on Active mode and choose “Ask your operating system for the external IP address” (the default setting).

Under Connection, under FTP, click on Passive mode and choose “Fall back to active mode” (this is an optional setting).

What is the difference between active and passive mode?

According to the FileZilla wiki page on network configuration:

In passive mode, which is recommended (see below), the client sends the PASV command to the server, and the server responds with an address. The client then issues a command to transfer a file or to get a directory listing, and establishes a secondary connection to the address returned by the server.

In active mode, the client opens a socket on the local machine and tells its address to the server using the PORT command. Once the client issues a command to transfer a file or listing, the server will connect to the address provided by the client.

The difference, then, is which side gets to determine the address used during the connection. In passive mode, the server provides the address, while in active mode, the client provides the address.

Why do I need to use active mode?

You probably shouldn’t need to use active mode, and in fact, it requires more configuration by the user of the FTP client to use active mode.

In passive mode, the router and firewall on the server side need to be configured to accept and forward incoming connections. On the client side, however, only outgoing connections need to be allowed (which will already be the case most of the time).

Analogously, in active mode, the router and firewall on the client side need to be configured to accept and forward incoming connections. Only outgoing connections have to be allowed on the server side.

http://wiki.filezilla-project.org/Network_Configuration#Technical_background

So, it boils down to who’s going to be responsible for the NAT and firewall configuration. Using passive mode places the responsibility on the server side of the connection, while using active mode places it on the client side. Typically, the FTP server administrator should be better equipped to handle this responsibility than the average FTP client user.

Passive mode

In passive mode, the client has no control over what port the server chooses for the data connection. Therefore, in order to use passive mode, you’ll have to allow outgoing connections to all ports in your firewall.

Active mode

In active mode, the client opens a socket and waits for the server to establish the transfer connection.

http://wiki.filezilla-project.org/Network_Configuration#Setting_up_FileZilla_Client

I’m behind a NAT router and I’ve never had any problems with passive mode. On the other hand, I seem to be able to connect to all my sites without any problem with the client in active mode, too, and I haven’t had to open any ports in Windows Firewall or forward any ports on my router. So maybe active mode doesn’t require as much configuration as the wiki page leads me to believe. Or maybe I’m just getting lucky and I’ll eventually run into problems if I continue to run in active mode.

Why does the server respond with the local IP address?

The FileZilla people offer a a partial explanation for why I’m seeing the internal IP address when I connect using the hostname. Back in Settings, under Connection | FTP | Passive mode, is some support text that reads: Some misconfigured remote servers which are behind a router, may reply with their local IP address.

The wiki page is pretty good reading, and has some interesting stuff on NAT, but I think that I’ll offer this plain-language, local IP address explanation when troubleshooting FTP connections.

If there is one thing that bothers me about Dreamweaver’s default settings (other than its annoying habit of rewriting valid code as camelCase), it is that double-clicking .php and .asp files launches Dreamweaver in Design view. Who on earth thinks that people generally want to open these kinds of files in anything other than code view?

Well, thankfully, this ridiculous behavior can be changed with a quick registry tweak. Just add an extension to the “Open As Text” value to cause Dreamweaver to always open that file type in code view. The .reg file below assumes you’re running Dreamweaver CS5.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Adobe\Dreamweaver CS5\Helper Applications Preferences]
"Open As Text"=".js .asa .css .cs .config .inc .txt .as .asc .asr .vb .htaccess .htpasswd .php .asp .html"

It would be nice if Dreamweaver were smart enough to realize that, if your preferred layout is Coder, any file should be opened in Code view.

The link below explains how to do the same thing via Edit | Preferences.

Source: Open files in Code view by default