Finally got MySQL installed in my home directory on the web server okay

Today I had received a reply from WebFaction about how to install the mysqli PHP extension. They said I would need to rebuild PHP rather than just making the mysqli.so extension and pointing to it in php.ini.

So far as I can tell, when PHP is configured with an extension included, it kind of includes it internally, and doesn't produce an extension.so file. So I think you can either configure PHP with the extensions included, or configure it without extensions, then build each extension separately and include them via the php.ini file. I'm not sure on this though, maybe I'll ask on the Web Squeeze.

Anyway, I renamed the current PHP installation on the Web Server, and built the new version of PHP, then restarted the server, and amazingly it actually worked (as far as I could tell anyway). In phpinfo() the mysqli was listed as being for mysql version 5.4beta, when previously it was for mysql 5.04.



Another thing I don't understand is how I can start, stop, and restart the server (I'm on shared hosting, so obviously I can't stop, start and restart the whole server). So far as I can tell, WebFaction redirect traffic for my site to a local copy of Apache they've installed in my ~/webapps/phpstack folder.

Looking at my phpinfo() page entry for mysqli, I noticed that the MYSQLI_SOCKET value was /tmp/mysql.sock and the value for mysqli.default_port was 3306, both incorrect for the mysql I'd installed in my home directory.

After lunch I went on Animal Crossing for a bit, and then tried installing mysql and php again. This time when installing mysql, I made sure to configure it with --with-unix-socket-path=$HOME/webapps/mysql/mysql.sock --with-tcp-port=myport. I also changed it so that it would install in ~/webapps/mysql rather than just ~/mysql where I had installed it before.

Since I had specified the tcp port and unix socket when installing mysql, I wasn't sure if I needed to create a my.cnf file as described in the WebFaction tutorial How to install MySQL into your home directory, but I thought I might as well create the my.cnf file as described, just in case.



After that I had to delete my php installation I'd just done, and install it again so that the mysqli php extension would be compiled against mysql with the correct port and socket settings.

Configuring, making and make installing these things takes quite a while.

After installing php again, I restarted the server, and phpinfo() now showed MYSQLI_SOCKET pointing to my local mysql installation, but mysqli.default_port was still 3306. Googling for "mysqli.default_port", I found this page that has lots of info on mysqli configuration options. Basically, mysqli.default_port is an option that you can set in php.ini.

So I checked the phpinfo() page to check where the php.ini file was located. However, when trying to browse there in Filezilla, I couldn't see the folder that I'd installed PHP to. I tried browsing back a level, then back to PHP's parent folder, but still couldn't see the PHP installation directory. I scrolled up and down the list in case it was at the end of the filelist or some other random place in the directory listing, but nope, it wasn't there.

I tried disconnecting and reconnecting to the server a couple of times, but still couldn't see the PHP installation directory. In my shell session on the server, I checked the PHP installation directory was there, and it was, Filezilla just couldn't see it for some cheesun.

I closed Filezilla, opened it again and reconnected to the server, and now it could see the PHP installation directory. I guess I could have done this in the shell session, but I much prefer using a GUI, especially for editing text files.



So I created a php.ini file locally (no php.ini file existed after installing PHP), added mysqli.default_port=myport to it, then saved it and uploaded it to the correct place on the server. I restarted the server, and now mysqli.default_port was set to the correct port. Yay!

Now all I had to do was update phpMyAdmin's config.inc.php file so that $cfg['Servers'][$i]['socket'] was pointing to the correct place.

I also wrote down the whole procedure so it should make it easier if/when I need to do this again. I'm hoping to upgrade to PHP-5.3 when it's released as it has new mysql native drivers, which are apparently quite a bit faster/more efficient than the current mysql extensions.



Heh, I just found that Mickelmaus pretends to be asleep is linked to as a suggested video from The Worst Star Trek Music Video Ever. As I hoped it seems that watching a video and then searching or watching for another video links the two together. So watching the Worst Star Trek Music Video Ever and then Mickelmaus pretends to be asleep (or vice versa) enough times creates a strong enough link for one to be featured on the other's similar videos list. I'm not sure if doing it lots of times in a row has any affect, or if you should do it once every day to create the link. Obviously if you can get other people to do the same thing, it will be more effective and should work quicker.

Getting back to installing mysql and php, I checked the server error log and found there was a load of errors like

php-cgi: unable to close module modulename can't close resident module @ module.c/UnregisterModule

This error occured about 15 times with different module names in a row, and then this block of errors was repeated a few times throughout the error log. Googling for these errors didn't come up with anything.

Another error in the error log was

[error] server reached MaxClients setting, consider raising the MaxClients setting

Googling for this did come up with some info, even a post on the WebFaction forums, which was helpful [error] server reached MaxClients setting. So I changed the ServerLimit in the httpd.conf file as suggested, restarted the server, and this seemed to fix it, as well as the other errors I was getting (though I haven't done anything to test it other than log in to phpMyAdmin).

In the evening I started watched Indiana Jones & The Crystal Skull with Moccle and L, then watched Springwatch with L and McRad. After that I did some more website work. I found that when I uploaded my photosite database to the web server (via phpMyAdmin's import function), I got an error about the max length of a key being 1000 bytes.

Doing some googling I found the problem - in UTF-8 each character uses/has reserved for use 3 bytes, so the max key length is 333 characters. In my case I had a UNIQUE key on 2 varchar fields, each with a length of 255 chars (I didn't actually check, but presume that to be the case). So my key was 510 chars max length, when the maximum length I could have was 333 chars. So I had to modify the key so it took 25 chars from one column and 255 from the other, which keeps it under 333 chars, and that fixed it. Surprisingly I only had this problem on one column.

The other surprising thing was that I only had this problem when uploading the SQL to the web server. My local environment (MySQL 5.1 and an older version of phpMyAdmin than is on the server) didn't complain about the key length being too long.

After updating the database on the server and also updating one of the files on the server to take account of the different filepath on the server compared to my local testing environment, I tried my web page. Unfortunately it didn't work too well, as it couldn't find any of the PHP includes files.

I had forgotten to edit the php.ini file on the server so that php includes would look in the right place for the files to include. After updating that I tried the page again, and it worked, and could read and write to the database. But the jQuery didn't seem to be working.

The problem was with the AJAX calls, they were still calling to my local testing site's URL. So I edited my javascript file to use a variable for the site URL, uploaded it to the server, and the value of the site URL variable in the file on the server. I decided to try it from the www. URL, but I got a site not found, so I obviously need to set my site up properly. I'll probably do that tomorrow.

The weather today was overcast, with the occasional break in the clouds letting a bit of sunshine through for about a minute in the morning, and rain in the afternoon.

Food
Breakfast: Fruited tea cake; cup o' tea.
Lunch: Mature cheddar cheese with sweet & crunchy salad sandwich made with white granary bakery bread; clementine; honey on crust of white granary bakery bread; caramel Rocky; cup o' tea.
Dinner: Spaghetti; Lloyd Grossman chilli & tomato sauce; bacon; mixed veg. Pudding was a large slice of Danube cake stuff. Coffee; Oreo; Cadbury's Dairy milk.
Supper: Cup o' tea; fly biscuit.