So, I now have one of our new servers sitting in a datacenter in Dallas. It is to replace one of our dedicated servers we are renting through The Planet. Well, our client’s website is doing extremely well right now and is averaging roughly 2,000 hits per day. This is one of the various eCommerce websites we have built for various customers. So, credit card data does get transmitted (always securely) between the database server residing on the dedicated server. Well, in order to minimize downtime for our customer’s website and to retain no loss of data, we have to do something about database connectivity between the old server and the new server. Luckily, MySQL is resilient enough to allow for remote connections, and as long as the latency between the servers isn’t bad, the website will operate at the same rate as normal.

However, the difference between connecting to a database residing on localhost and a database server residing on some other machine is the type of connectivity. By default, connections are unencrypted, so eCommerce information should NOT be transmitted in this manner. We need to transmit it over SSL-style connections. Again, MySQL is resilient enough to have these features built-in, assuming you compiled your server with SSL support.

I began by taking a copy of our customer’s database and sending it over to the new server:

oldServer> mysqldump -u root databasename -p > databasename.mysql
oldServer> scp databasename.mysql newserver:/home/customer/databasename.mysql
oldServer> ssh newserver
newServer> mysql -u root databasename -p < /home/customer/databasename.mysql

No problems were encountered here. So, the difference between SSL connections using SSH and SSL connections using MySQL is that MySQL requires SSL certificates and keys, much like web servers and mail servers do. However, you don’t have to purchase one of these. You CAN use self-signed certificates, as long as you have access to the Certificate Authority certificate file (shouldn’t be a problem if you are acting as the Certificate Authority). I’m not going to go into signing your own certificates, but there are several great websites that tell you how to do this, such as this one.

So, I did the usual things necessary to create my certificate (since I act as the certificate authority for my company), and copied the certificate data into the following locations:

newServer> cp /root/sslcerts/newServer.key /etc/ssl/private/newServer.key
newServer> cp /root/sslcerts/newServer.crt /etc/ssl/certs/newServer.crt
newServer> cp /root/sslcerts/CA.crt /etc/ssl/certs/CA.pem
newServer> cd /etc/ssl/certs
newServer> /usr/bin/c_rehash

I followed the same pattern for the CA certificate as everything else that was in that directory. All extensions were .pem. Note that openssl generates PEM formatted certificates and keys anyways, so it is perfectly legal and acceptable. c_rehash is used to parse the /etc/ssl/certs directory for new certificate authority certificates and create a symbolic link that has a name that’s a hashed value of the certificate. Read the man page for c_rehash for further information.

Anywho, everything was in their respective location, and I had added the following lines in my /etc/mysql/my.cnf file (under the server section):

ssl-ca = /etc/ssl/certs/CA.pem
ssl-key = /etc/ssl/private/newServer.key
ssl-cert = /etc/ssl/certs/newServer.crt
ssl-cipher = ALL:-AES:-EXP

I started MySQL and no errors were detected. As the MySQL Manual states for SSL connectivity, you issue the command show variables like ‘have%’ to show if you have SSL connectivity available. Here’s what mine showed:

mysql> show variables like 'have%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| have_archive          | NO       | 
| have_bdb              | YES      | 
| have_blackhole_engine | NO       | 
| have_compress         | YES      | 
| have_crypt            | YES      | 
| have_csv              | NO       | 
| have_dynamic_loading  | YES      | 
| have_example_engine   | NO       | 
| have_federated_engine | NO       | 
| have_geometry         | YES      | 
| have_innodb           | DISABLED | 
| have_isam             | NO       | 
| have_merge_engine     | YES      | 
| have_ndbcluster       | NO       | 
| have_openssl          | DISABLED | 
| have_ssl              | DISABLED | 
| have_query_cache      | YES      | 
| have_raid             | NO       | 
| have_rtree_keys       | YES      | 
| have_symlink          | YES      | 
+-----------------------+----------+
20 rows in set (0.00 sec)

Well that’s interesting, SSL is disabled. After a few hours of trying to figure out what this is, including starting SSL with those configuration options on the command line rather than through the my.cnf file, I found out the culprit of this was the ssl-cipher line in the config. Even setting this to just ALL caused the same result. However, when I removed that option and restarted MySQL, I got the following results:

mysql> show variables like 'have%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| have_archive          | NO       | 
| have_bdb              | YES      | 
| have_blackhole_engine | NO       | 
| have_compress         | YES      | 
| have_crypt            | YES      | 
| have_csv              | NO       | 
| have_dynamic_loading  | YES      | 
| have_example_engine   | NO       | 
| have_federated_engine | NO       | 
| have_geometry         | YES      | 
| have_innodb           | DISABLED | 
| have_isam             | NO       | 
| have_merge_engine     | YES      | 
| have_ndbcluster       | NO       | 
| have_openssl          | YES      | 
| have_ssl              | YES      | 
| have_query_cache      | YES      | 
| have_raid             | NO       | 
| have_rtree_keys       | YES      | 
| have_symlink          | YES      | 
+-----------------------+----------+
20 rows in set (0.00 sec)

I have filed a bug with MySQL about this, because it’s a critical flaw, not being able to specify which Ciphers you wish to allow.

So, now that I see SSL connectivity is allowed, I need to create an account and fully restrict it to require SSL connections. This is done in my grant statement. Full instructions on Granting with SSL Requirements are available in the MySQL Reference Manual. So, in MySQL, I issue the following command:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON `databasename`.* TO 'customer_dbuser'@'192.168.1.1' \
    -> IDENTIFIED BY 'goodsecret' \
    -> REQUIRE ISSUER '/C=US/ST=Texas/L=College Station/O=Company Name/OU=Certification Authority Name/CN=My Root Certificate Authority' \
    -> SUBJECT '/C=US/ST=Texas/L=College Station/O=Company Name/OU=Department/CN=oldserver.domain.com/emailAddress=admin@domain.com' \
    -> CIPHER 'DHE-RSA-AES256-SHA';

The ISSUER and SUBJECT lines come from what you filled out when creating the certificate authority CSR and the host CSR, respectively. You can get this value by looking for the issuer and subject lines in the ouput from the following command:

shell> openssl x509 -noout -in oldServer.crt -text

The CIPHER is what openssl uses by default for creating SSL certificates. If you used a different cipher when creating the key, CSR, and Certificates, then specify it accordingly.

So, after having those problems and now having SSL connectivity (half-way) completed, it’s time to move onto the old server. I send over the newly created SSL certificates to the old server via SSH and install them in roughly the same places:

oldServer> cp /root/sslcerts/CA.crt /etc/ssl/certs/CA.pem
oldServer> cp /root/sslcerts/oldServer.crt /etc/ssl/certs/oldServer.crt
oldServer> cp /root/sslcerts/oldServer.key /etc/ssl/private/oldServer.key

I then added the following lines under the client section in the /etc/mysql/my.cnf file:

ssl-ca = /etc/ssl/certs/CA.pem
ssl-key = /etc/ssl/private/oldServer.key
ssl-cert = /etc/ssl/certs/oldServer.crt
# ssl-cipher = ALL:-AES:-EXP

The specification of certificate information on the server as well as certificate information on the client is ESSENTIAL in the success of this. RTFM, if you haven’t already.

So, time to fire up the connection to see if it works:

oldServer> mysql -u customer_dbuser -h newServer -p --ssl-ca=/etc/ssl/certs/CA.pem --ssl-key=/etc/ssl/private/oldServer.key --ssl-cert=/etc/ssl/certs/oldServer.crt
Password: <enter goodsecret>
SSL Connect Failed

If you fail to put anything in the client section of the my.cnf file and DONT specify a ssl-key or ssl-cert on the command line, you will see a generic SSL connection error on the client side and the following error message on the server’s logs (IF you have debugging turned on):

tls peer did not respond with certificate list

This even occurs if you only entered REQUIRE SSL for the client and nothing further. But, this was not my case. I spend yet another few hours trying to figure out what happened with this and finally recompiled the newServer MySQL with the debug option. After trying again and parsing the debug files, I found the following line:

Error:  "error:00000005:lib(0):func(0):DH lib"

This was in the sslaccept function. After Googling this for about an hour, I found nothing relevant to the problem. It’s a generic openssl error, assuming that you are using openssl and not another SSL library set.

To make a long story short, the error message was extremely misleading and it boiled down to a permissions problem. I coulda sworn that all my permissions were correct, but it turns out they weren’t. Here’s how it was setup:

newServer> ls -alF /etc/ssl/private 
drwx------ 2 root root  184 May 19 21:47 ./
drwxr-xr-x 5 root root  152 Apr 19 19:52 ../
-rw-r--r-- 1 root root 3247 May 19 20:32 newServer.key

For those still unfamiliar with Linux, if you look at the permissions for ./, you’ll notice it’s Read, Write, and Execute for the owner ONLY. This means that nobody else could traverse into this directory, EVEN THOUGH the newServer.key was at least readable by everyone.

Moral of the Story

Double check your permissions.

Well now, here’s an interesting problem I had with Perl on the new Mac Xserve running Intel Xeon 64-bit processors. One of our customer’s had a CGI website (cringes) and was using DBD::MySQL to access the MySQL database. Upon initial observations, permissions had been setup incorrectly and the httpd.conf file was not setup properly for CGI executables.

Past this, I find that the server has dependency problems. This was found due to the following error message in the /var/log/httpd/error_log file. The particular error was:

[Mon May  7 15:43:45 2007] [error] [client xx.xx.xx.xx] Premature end of script headers: /Library/WebServer/CGI-Executables/webevent.cgi
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /Library/WebServer/webevent/lib /System/Library/Perl/5.8.6/darwin-t
hread-multi-2level /System/Library/Perl/5.8.6 /Library/Perl/5.8.6/darwin-thread-multi-2level /Library/Perl/5.8.6 /Library/Perl /Network/Library/Per
l/5.8.6/darwin-thread-multi-2level /Network/Library/Perl/5.8.6 /Network/Library/Perl /System/Library/Perl/Extras/5.8.6/darwin-thread-multi-2level /
System/Library/Perl/Extras/5.8.6 /Library/Perl/5.8.1 .) at (eval 8) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.
 at /Library/WebServer/webevent/lib/db/dbconnect.pm line 58

So, I proceed to install Perl modules. But wait, CPAN’s bitching about an upgrade to CPAN being available. Fine, let’s give it what it wants:

CPAN> install Bundle::CPAN
 
-- CPAN INSTALLS UPDATE --

Cool, now time to move to installing DBI:

CPAN> install DBI
 
-- INSTALL SUCCESSFUL --

Now for the last bit, DBD::mysql:

CPAN> install DBD::MySQL
 
<snip>
t/utf8...............install_driver(mysql) failed: Can't find 'boot_DBD__mysql' symbol in /Library/Perl/DBD-mysql-4.001/blib/arch/auto/DBD/mysql/mysql.bundle
at (eval 3) line 3
Compilation failed in require at (eval 3) line 3.
 
2 tests skipped.
Failed 25/28 test scripts. 413/418 subtests failed.
Files=28, Tests=418,  2 wallclock secs ( 1.54 cusr +  0.35 csys =  1.89 CPU)
Failed 25/28 test programs. 413/418 subtests failed.
make: *** [test_dynamic] Error 255
</snip>

Damn, talk about something unexpected. After reading a few emails, forums, and getting down right frustrated with everything, I find this email note. This isn’t completely correct, in that it turns out you don’t have to install another copy of MySQL in some temporary location for the libraries - you have everything you need, assuming you’ve already installed XCode Tools.

So, what DO you have to do? The following:

shell> cd /path/to/.cpan/build/DBD-mysql-4.001/
shell> perl Makefile.PL --testuser test --testpassword test --testsocket /var/mysql/mysql.sock --cflags="-I/usr/include/mysql" --libs="-L/usr/lib/mysql -lmysqlclient -lz -lm"
shell> make
shell> make test (should work now) 
shell> make install

Understanding of why Perl was so broken

Well, it wasn’t exactly Perl’s fault. It’s MySQL that comes on Apple’s Xserve. Apple, you question with an unquestionable doubt in your mind? Yes, Apple. Check this out:

shell> mysql_config --libs
-arch ppc64  -arch x86_64 -pipe -L/usr/lib/mysql -lmysqlclient -lz -lm
shell> mysql_config --cflags
-I/usr/include/mysql -fno-omit-frame-pointer  -arch ppc64  -arch x86_64 -pipe

Now isn’t that funny! On my new Xeon Xserve, the architecture specifications (which for some odd reason appear in BOTH the cflags AND libs flags which normally appear ONLY in the cflags) are for BOTH ppc64 AND x86_64… AFAIK, this server is Intel 64-bit based, not PowerPC any longer. So, when Perl goes through and autoconfigures its switches, these architecture flags cause the tests to blow up because it’s expecting a completely different set of tools that are expected to work on the PowerPC architecture.

Anywho, it’s working now, and that make me (and my client) happy.

Okay, I feel silly for writing this post, but as many of you know, this blog is not only for other’s enrichment, but for me document what I find for my own purposes and future uses. It also helps me to remember things I find cool, interesting, highly important, etc.

Well, for those of you who use databases (particularly MySQL) and don’t have as vast of a background as you’d like to have (like myself), you find yourself writing ridiculous queries to obtain the previous row of data you inserted into a table.

For example:

mysql> INSERT INTO users (name, email, phone) 
       VALUES
       ("Chris Weldon", "chris@chrisweldon.net", "232-353-4544");
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT id FROM users 
       WHERE name = "Chris Weldon" 
       AND email = "chris@chrisweldon.net" 
       AND phone = "232-353-4544";
+------------------+
| id               |
+------------------+
|            12373 |
+------------------+
1 row IN SET (0.03 sec)

Well, with what I have finally found by purusing the MySQL Reference Manual - you no longer have to write that second assinine query which could potentially take a while to retreive the data, especially if the users table (or whatever table in question is being queried on) is large and could potentially have fields that are not indexed.

Here’s your nice solution:

mysql> INSERT INTO users (name, email, phone) 
       VALUES 
       ("Chris Weldon", "chris@chrisweldon.net", "232-353-4544");
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|            12373 |
+------------------+
1 row IN SET (0.00 sec)

All I can say about this is “Where have you been all my life?”.

If you are trying to use a program to connect to MySQL (in my case, BugTracker, and you encounter the following problem:

Host 'localhost.localdomain' is not allowed to connect to this MySQL server.

This means that the program is trying to use TCP connections rather than sockets, despite using localhost as a configuration option. This is ridiculous, especially in a PHP environment, but we can easily get past it.

In the /etc/hosts file, make sure you have the following line:

/etc/hosts

127.0.0.1    localhost

It CANNOT be:

127.0.0.1    localhost.localdomain    localhost

This fixed my problem.

Day 3 ended the tutorials and began the sessions - the events that the majority of people showed up for. Keystones also precluded the days events, and the first Keynotes were rather intriguing. I heard talks from one of the leads and founders of SixApart, the company responsible for such things as LiveJournal, etc. In addition, Mr. O’Reilly gave a talk on how opensource licenses are out of date. With lack of further explanation of why he believed this, there were many members of the audience (including the other Keynote speakers) who were questioning that statement.

The sessions, left much to be desired. They were approximately 45 minutes each (some actually spanned an hour and a half - though those were rare). As such, 45 minutes hardly gave enough time to thoroughly explain the concepts being presented, but for some of the sessions it was just enough time. For example, the first session I attended was on how this developer used Ruby on Rails to create an MMO in about 45 minutes. The game, is called Unroll - found at llor.nu. It’s a rather simple game, but he explained the concepts behind the creation of his game, rather than explaining some of the methods he used - such as showing code examples, tips and tricks, etc. As such, I left with hardly an understanding of how to use Rails to create a game of my own, but his game is at least open source, so I can download the source code and figure out what I need to then.

The other sessions, which included a combination of PHP, Ruby, and Rails were rather uninteresting for the most part. I took notes in some sessions, but many I’ll have to find the slides in order to benefit from attending them.

However, despite how uninteresting some of these sessions were, I admit that I did take out a lot of ideas that I plan to integrate for my business and CIS. Such things include code caches, such as APC, using IDE’s for development and then running traces on the code to further be able to determine where slow-downs in code occur. I also found a couple of other interesting software items people were using on Macs that I have found quite awesome.

BTW, the Exhibit hall resulted in me getting lots of goodies.

Day 2 of OSCON training was rather sluggish and disappointing. There were several things that I found out that I already knew, but many things I wasn’t expecting from one of the talks, the High Performance PHP. Going into this, I was expecting to see some code examples and talks about certain functions or stylistic coding techniques that would result in improved PHP code. This was definitely not the case, as the majority of the talk was about improving applications that run PHP, or co-exist with PHP. There were also other things to avoid (such as SOAP, but for obvious reasons), but hardly any talks about how to improve your code to improve it’s performance.

The one thing I did learn through this tutorial was how to go through and trace the code, and use things like kcachegrind to make graphs and help you figure out where the slow parts of your code are laying. Percentage breakdowns of the time spent inside certain functions, classes, and objects help to show where your code is “slow”. I plan on using this on my many different projects, just as soon as I figure out how to use the damn program.

The Security tutorial was also nice, but at the same time going over many of the things I learned in my online PHP training that Paul Reinheimer gave last month.

Today: Ruby and Rails
Verdict: Better than awesome!

First thoughts: Instructor name’s David Thomas. First thing on my mind: Wendy’s. lol, sorry, had to get that off my mind.

Anywho, David Thomas and Mike Clark were the instructors for The Ruby Guidebook and The Rails Guidebook. For my first tutorials of OSCON, this definitely caught my attention and I was totally drawn into their presentation. They were excellent speakers, humorous when they needed to be, and more than knowledgeable on the subject.

For those who don’t know what Ruby and/or Rails are, lemme give a quick explanation. Ruby is another programming language out there (actually scripting language), much like perl. There are many aspects to it that really make it a powerful language, especially the fact that everything is an object, which means that everything has certain methods that can be used directly with it.

Rails (or commonly stated as Ruby on Rails) is a framework to build Web Applications that is based on Ruby. From the tutorial, I learned the basics of building a Rails web app, and it is amazingly simple. In fact, the instructor timed himself and it took 47 seconds to get the basic interface for adding, deleting, and modifying items in a shopping cart, complete with MySQL tables, and all the forms necessary and “backend processing”.

Anywho, it’s dinner time. Just wait for my next update.

Well, I am officially writing on the WIFI here at OSCON. After a long, approximate 9 hours of flight, waiting, more flight, waiting, a prezel, and more flying, both myself and Melissa made it here to Portland. I don’t thing we got checked in and situated into our Hotel room until about 1AM Pacific time.

Overall, my flying experience wasn’t as bad as Tom’s was the other day. For those who don’t know (cause I don’t think I posted an entry about his situation), he basically was held over 3 hours in Houson on American Airlines, followed by held over in Chicago for another 3-4 hours before his flight was cancelled and Tom was left stranded in the airport overnight, then didn’t get home till around 4 or 5 PM the following day. In my opinion, that is absolutely horrible service, and airlines should do their upmost best to make sure people get to their desitinations the day they are travelling - not the following day, practically completely through the day.

Anywho, I took American Airlines, but only because I had booked my flight about 3 months before he had this problem. Normally I travel Continenetal. Albeit the problems Tom had, however, both Melissa and I had reasonable flights. The last leg of my journey was on Alaska Airlines, which was the best leg overall because they at least provided a snack (peanuts). American didn’t even offer free peanuts, $4 for a “snack pack”, which would cost less than $1 at the grocery store. No way in hell.

Melissa, took Frontier, and she said they were the nicest airline she had ever been on. In-flight snack: you bet! Chips! A little better than peanuts, to some. But, eh. I’m just glad both of us arrived at the airport and had our luggage just in time to catch the last light-rail that was leaving the airport for the night. Free fare, too.

Double Tree has by far the best beds out of any hotel I’ve ever been to. I didn’t wake up with a sore back at all, and was rather rested, despite not being able to get to sleep till 2AM local time and waking up at 5:45 AM to get ready. Melissa is obviously back behind at the hotel sleeping in, *shakes fist in hotel’s direction*, but hopefully she’ll be able to find something to do to keep herself entertained. I hope that she gets out and sees the town a bit, as this is supposed to be a pretty cool area.

Anywho, time for me to get ready for the convention, and finish answering emails that I haven’t checked in the past couple of days. I’ll keep updated on how awesome the CON is, as I’m in training.

Well, in just over a week I’ll be going to Portland, Oregon to attend my very first OSCON. My boss usually goes, and since I got staff at CIS too late last year, I didn’t have a chance to go to any conferences.

OSCON, for those unfamiliar with it, is the O’Reilly Open Source Convention. From what I can tell, this seems to be the biggest coding convention on this side of the planet - especially the one that seems to emphasize open source development to the extreme. I like the attitude of open source developers, and hope to network a lot at the event. I’m planning on bringing all of my business cards to hand out, and plan to have my little Rolodex pocket holder available to stuff everyone else’s in there. I met a lot of cool and nice people at the last conference I went to (cPanel Traning Conference - Houston, TX), and got their business cards. We’ve stayed in touch since then.

My fiance, Melissa, is coming with me. It was a last-minute decision, but one I hope she’ll thoroughly enjoy. I’ll be in sessions all day most days, and some evenings I’ll be networking with people, but agree that my life has been far to hectic lately, so we plan to take a few evenings to go do some cool things such as visit the Japanese Gardens up there, in addition to the International Rose Test Garden and other things such as the Oregon Zoo, etc. During the day she’ll be meandering probably in the Hotel and surrounding Portland area, enjoying herself. For anyone else interested in a list of other things / places you might find her at, check out the following page.

My camera will be coming with me, so be on the lookout and if you don’t like to be shot, don’t be in range of my viewfinder, else bring that wonderful smile of yours. I’ll be blending in with the crowd, for the most part.