Category: MySQL


As you readers probably know, I can’t stand XAMPP and MAMP, being two steaming piles of crap, and have long advocated that you set up VirtualBox & Vagrant, then head over to http://www.puphpet.com, fill in the forms to configure your VM,  generate the config.yaml, and then unzip it and run ‘vagrant up’ to install it. Brilliant so far.

Yesterday I had a total downer of a day, trying to run an old legacy PHP 5.3 app. PuPHPet doesn’t have the EOL PHP 5.3, so at first I settled as a one off for MAMP, but it was slow and horrible.

Then I thought, wait! If I don’t configure Apache or PHP in puphpet, I could get a box up and install 5.3 myself. That’s when I discovered the awesomeness of the puphpet/files folder.

The only thing I used in there was the ssh keys. But there are empty folders waiting for .sh files (shell scripts) to be dropped in.

So for this box, I created exec-once/install-stuff.sh which contained the following:

#!/bin/bash
yum -y install httpd php
yum -y install php-mysql php-devel php-gd php-pecl-memcache php-pspell php-snmp php-xmlrpc php-xml

Then upon running vagrant provision, it not only looked for changes in config.yaml, but it checks for changes in these files too!

I then made set-vhosts.sh, and import-database.sh, which look like these:

#!/bin/bash
echo "
===========================================
Adding vhosts to /etc/httpd/conf/httpd.conf
===========================================
"
echo "
<VirtualHost *:80>

   DocumentRoot /var/www/fife/web
   ServerName fife
   ErrorLog /var/www/fife/log/error.log

   <Directory "/var/www/fife">
      Options -Indexes +FollowSymLinks
      Order allow,deny
      Allow from all
      AllowOverride All
  </Directory>

</VirtualHost>
" >> /etc/httpd/conf/httpd.conf

And …

#!/bin/bash
mysql -u root --password=123 --database=fortdev < /var/www/fife/data/sql_scripts/symf_fortdev.sql

I take it by now you get the idea! So now you can totally destroy your VM, and put any customisations in these shell scripts, so your full setup can be back up in 5 minutes flat with a vagrant up and vagrant provision!!!

You can then also start thinking about using puPHPet for deploying your setup to your production server 🙂 There’s a vagrant plugin called Vagrant Managed Servers, which will take care of that for you. https://github.com/tknerr/vagrant-managed-servers . I haven’t looked at it yet, but of course you can expect a blog post on it here when I figure it all out!!

Advertisements

puPHPet Box MySQL backup

As you all know by now, I love puPHPet. And I like tinkering with things. Which of course leads to the occasional breaking of things. And then I realised, okay, your sites files are being mounted into this virtual machine, what about the databases though?

The great thing about puPHPet and Vagrant is you can build a full server up from scratch in minutes. The one downside is that it cant be smart enough to uninstall things. If you add lines to your config.yaml it will install something, but removing those lines means puPHPet won’t even know it was there, so how would it know to uninstall it? The exceptions of course being the ones which have their own config.yaml entry, with an install: ‘1’ or install: ‘0’

Anyway, I generated me a new config and was about to blitz my old VM when the usual gut instinct checks kicked in, and the DB sprung to mind immediately. So I made a quick backupdbs.sh shell script (courtesy of a StackOverflow post) which makes individual sql files for each of your DB’s. I decided I would keep the script in my mounted sites folder in a bin directory. So hence it lives in /var/www/bin/backupdbs.sh

#!/bin/bash

USER="root"
PASSWORD="YOURPASSWORDHERE"

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    fi
done

To back everything up, vagrant ssh into the machine, then (you’ll probably need to switch user to www-data) do the following:

sudo su www-data
cd /var/www/bin
./backupdbs.sh

Now you’ll find all your DB’s SQL files date stamped in the folder! You can continue to break, tweak, fix, and run your VM to your hearts content 😀

I’m making some Apache redirects from an old domain to a new domain. I need the old domains blog posts to redirect to the new domain. So I need the WordPress permalinks.

In MySQL, you can say the following to get your permalinks.

SELECT wpp.post_title, wpp.guid,wpp.post_date,
CONCAT(wpo_su.option_value,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    wpo.option_value,'%year%', date_format(wpp.post_date,'%Y'))
    ,'%monthnum%',date_format(wpp.post_date,'%m'))
    ,'%day%',date_format(wpp.post_date,'%d'))
    ,'%postname%',wpp.post_name )
    ,'%category%',wpc.slug )
    ,'%post_id%', wpp.id)
) as permalink
FROM wp_posts wpp
INNER JOIN wp_options wpo ON wpo.option_name='permalink_structure'
INNER JOIN wp_options wpo_su ON wpo_su.option_name='siteurl'
INNER JOIN (
    SELECT wtr.object_id ID, max(wpt.slug) slug
    FROM wp_term_relationships wtr
    INNER JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id=wtr.term_taxonomy_id AND wtt.taxonomy='category'
    INNER JOIN wp_terms wpt on wpt.term_id=wtt.term_id
    GROUP BY  wtr.object_id
) wpc ON wpc.ID=wpp.ID
WHERE wpp.post_type = 'post'
AND wpp.post_status = 'publish'
ORDER BY wpp.post_date DESC

It’s nice to see what the actual queries that run are on your site. Its pretty simple to set up! In your home folder, edit your .bashrc (or equivalent) and slap in the following:

alias tmysql='mysql -u root -e '\''SET GLOBAL general_log = "ON"'\'';sudo tail -f /var/log/mysql/general.log;mysql -u root -e '\''SET GLOBAL general_log = "OFF"'\''; sudo rm /var/log/mysql/general.log; echo '\''\nExiting Tail, Log Deleted.'\'';

Tweaking paths where required, obviously. Now close and open your terminal again, and when you type tmysql, you’ll start tailing MySQL! Sample output below!

          453 Query    SELECT `main_table`.* FROM `enterprise_customersegment_segment` AS `main_table`
          453 Query    SELECT `op_imagecdn_cache`.* FROM `op_imagecdn_cache` WHERE (`op_imagecdn_cache`.`url`='http:blah.com')
          453 Query    SELECT `main_table`.* FROM `madskull_autoblocks` AS `main_table` WHERE (identifier = 'trust-banners')
          453 Query    SELECT `madskull_autoblocks_definitions`.* FROM `madskull_autoblocks_definitions` WHERE (`madskull_autoblocks_definitions`.`id`='39')

Here’s one you might have had. “Lost connection to MySQL server at ‘reading initial communication packet’ “.

If  you get this, then I assume that you are connecting to another machine on the network, a virtual machine, or a remote server.

It’s a pretty simple fix. Edit your /etc/mysql/my.cnf (path may differ depending on your setup), and change the line that says bind-address = 127.0.0.1 to:

bind-address = 0.0.0.0

Save it, then restart mysql.

sudo service mysql restart

Job done!

Geolocate your visitors!

Right now I’m building a mobile app using Intel’s AppFramework for a single page site, and Apigility for the API.

In the app, there is an autosuggest for towns and postcodes, however I thought it would be nice to have a “Locate Me” button, able to pinpoint your visitor (hopefully with a decent degree of accuracy!), so I Googled away and found the following:

https://github.com/estebanav/javascript-mobile-desktop-geolocation

In your site go into your js folder and clone the repo:

cd js
git clone git@github.com:estebanav/javascript-mobile-desktop-geolocation.git

In your page you’ll include the scripts and add your functionality as required:

<script src="js/javascript-mobile-desktop-geolocation/js/geoPosition.js" type="text/javascript" charset="utf-8"></script>
 <script src="js/javascript-mobile-desktop-geolocation/js/geoPositionSimulator.js" type="text/javascript" charset="utf-8"></script>
<script type="text/javascript">
if(geoPosition.init())
 { 
    // Geolocation Initialisation
    geoPosition.getCurrentPosition(success_callback,error_callback,    {enableHighAccuracy:true});
 }
 else
 {
    // You cannot use Geolocation in this device
 }
 geoPositionSimulator.init();
// p : geolocation object
 function success_callback(p)
 {
    // p.latitude : latitude value
    // p.longitude : longitude value
    console.log(p);
 }
function error_callback(p)
 {
    // p.message : error message
    console.log(p);
 }
</script>

Now if you load your page and go into the web inspector, hopefully you should have some coordinates!

The next part of the puzzle is where it gets interesting: How to find the closest match in your table of areas, postcodes, and lat longs!

I would never have figured this out, but there is an equation called the Haversine formula, which uses spherical trigonometry to calculate areas within a certain distance! It looks like this:

R = earths radius (mean radius = 6,371km)
Δlat = lat2 lat1
Δlong = long2 long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1a))
d = R.c
Angles need to be in radians to pass to Trigonometric functions

http://en.wikipedia.org/wiki/Haversine_formula

Here’s my actual query itself:

SELECT *, 
 ( 3959 * acos( cos( radians(55.864237) ) * cos( radians( latitude ) ) 
 * cos( radians( longitude ) - radians(-4.251806) ) + sin( radians(55.864237) ) 
 * sin( radians( latitude ) ) ) ) AS distance 
FROM postcodes HAVING distance < 20 
ORDER BY distance LIMIT 1;

Here I check for any area within 20 miles, but you can make this as short or long as you want.  The 3959 figure at the start of the query is the number used for miles, if you are using kilometres you should change this number to 6371.  I have limited it to 1 row, as I only want the closest match, however you may want to change this in other situations!

So there we have it! NSA-like spying on your visitors! (With their permission of course!)

If you checked my last post, you will have read about how to get all your old ZF1 classes autoloading in your new shiny ZF2 project. However, the DB settings from your application.ini won’t have been set, and so you’ll get a 500 response with  No Db Adapter. Not a problem. We just create the db adapter ourselves and tell Zend_Db that it is the default.

I’ve put this in the ZF2 index.php just before the last line, Zend\Mvc\Application::init($appConfig)->run();

//Set up ZF1 DB
$params = array(
 'host' => '127.0.0.1',
 'username' => 'xxx',
 'password' => 'xxx',
 'dbname' => 'xxx'
);
$adapter = Zend_Db::factory('Pdo_Mysql',$params);
Zend_Db_Table_Abstract::setDefaultAdapter($adapter);

Now your autoloading ZF1 DB classes wont freak out, and should connect no problem!

Yay! Free OS upgrade! But oh! Something’s bound to be affected, my apache or php or something. And you’d be right. So here are my list of issues I had, and how I overcame them.

First up, Mavericks looks really cool, and seems to zip along a lot faster than lion did. Nice one! A few other nice looking things, I’ll get into those later, but are my sites loading? I open Safari, type in one of my vhosts, and then it displays a white web page with a times new roman bold h1 tag claiming ‘It works!’. Yes, Apache is working, but not a lot else! :-s

When I finally realised a while back that all-in-one things like XAMPP are actually no good for serious development, I got my PHP running with the built in Apache, and the same tweaks apply here. Edit /etc/apache2/httpd.conf :

#LoadModule php5_module libexec/apache2/libphp5.so
to
LoadModule php5_module libexec/apache2/libphp5.so

And allow use of .htaccess to override:

AllowOverride All

That should bring you back up. I wasn’t getting any db connection, so I checked to see if my MySQL server was still running. It reported so, so I fired up SequelPro and was able to get access to my data. Hmm.

My next thought that possibly more modules for apache were needing installed or something. I checked a phpinfo();  It was running v5.4.17, with no debug or anything installed, so I guessed it was the Mavericks installed PHP. I thought, well, if I’m updating, we might as well have PHP 5.5 installed, so I went to work on that.

The terminal command ‘port’ experienced difficulties, and I quickly realised that I would need to update Xcode, and Xcode Developer Command Line Tools. Once you have done that, open Xcode up, and agree to the terms and conditions, then close it. If you don’t do this some of the terminal commands will fail.  Then you can run :

sudo port self update
sudo port upgrade outdated

I came across a hanging point when it was trying to build a thing called ‘boost’. Apparently other people on the net were complaining about the same thing but no one had an answer. After failing a few times I decided I would concentrate on just upgrading packages that I needed, so I typed this to see what was already installed: (results listed to give you an idea of what i have running)

$ port -qv installed
  apache2 @2.2.22_2+preforkmpm platform='darwin 11' archs='x86_64'
  apache2 @2.2.25_0+preforkmpm (active) platform='darwin 13' archs='x86_64'
  apr @1.4.6_1 platform='darwin 11' archs='x86_64'
  apr @1.4.8_0 (active) platform='darwin 13' archs='x86_64'
  apr-util @1.4.1_0 platform='darwin 11' archs='x86_64'
  apr-util @1.5.2_1 (active) platform='darwin 13' archs='x86_64'
  autoconf @2.69_0 platform='darwin 11' archs='noarch'
  autoconf @2.69_2 (active) platform='darwin 13' archs='noarch'
  automake @1.12_0 platform='darwin 11' archs='noarch'
  automake @1.14_0 (active) platform='darwin 13' archs='noarch'
  boost @1.50.0_0+no_single+no_static (active) platform='darwin 11' archs='x86_64'
  bzip2 @1.0.6_0 (active) platform='darwin 13' archs='x86_64'
  corkscrew @2.0_0 (active) platform='darwin 11' archs='x86_64'
  cppunit @1.12.1_0 (active) platform='darwin 11' archs='x86_64'
  curl @7.27.0_1+ssl (active) platform='darwin 11' archs='x86_64'
  curl-ca-bundle @7.27.0_0 (active) platform='darwin 11' archs='noarch'
  db46 @4.6.21_6 platform='darwin 11' archs='x86_64'
  db46 @4.6.21_8 (active) platform='darwin 13' archs='x86_64'
  db_select @0.1_2 (active) platform='darwin 13' archs='noarch'
  dirac @1.0.2_1 (active) platform='darwin 11' archs='x86_64'
  docbook-xml @5.0_0 (active) platform='darwin 11' archs='noarch'
  docbook-xml-4.1.2 @4.1.2_1 (active) platform='darwin 11' archs='noarch'
  docbook-xml-4.2 @4.2_0 (active) platform='darwin 11' archs='noarch'
  docbook-xml-4.3 @4.3_0 (active) platform='darwin 11' archs='noarch'
  docbook-xml-4.4 @4.4_0 (active) platform='darwin 11' archs='noarch'
  docbook-xml-4.5 @4.5_0 (active) platform='darwin 11' archs='noarch'
  docbook-xml-5.0 @5.0_0 (active) platform='darwin 11' archs='noarch'
  docbook-xsl @1.76.1_1 (active) platform='darwin 11' archs='noarch'
  dyld-headers @239.3_0 (active) platform='darwin 13' archs='noarch'
  expat @2.1.0_0 (active) platform='darwin 13' archs='x86_64'
  ffmpeg @0.7.13_2+gpl2+mmx (active) platform='darwin 11' archs='x86_64'
  fftw-3 @3.3.2_0 (active) platform='darwin 11' archs='x86_64'
  fontconfig @2.9.0_1 (active) platform='darwin 11' archs='x86_64'
  freetype @2.4.10_0 (active) platform='darwin 11' archs='x86_64'
  gdbm @1.10_1 platform='darwin 11' archs='x86_64'
  gdbm @1.10_2 (active) platform='darwin 13' archs='x86_64'
  gettext @0.18.1.1_2 platform='darwin 11' archs='x86_64'
  gettext @0.18.3.1_1 (active) platform='darwin 13' archs='x86_64'
  ghostscript @9.05_3 (active) platform='darwin 11' archs='x86_64'
  glib2 @2.32.4_0 (active) platform='darwin 11' archs='x86_64'
  gperf @3.0.4_2 (active) platform='darwin 13' archs='x86_64'
  gsed @4.2.1_2 (active) platform='darwin 11' archs='x86_64'
  help2man @1.40.5_3 platform='darwin 11' archs='x86_64'
  help2man @1.40.10_0 (active) platform='darwin 11' archs='x86_64'
  htop @0.8.2.1_0 (active) platform='darwin 11' archs='x86_64'
  icu @4.8.1_0 platform='darwin 11' archs='x86_64'
  icu @51.2_1 (active) platform='darwin 13' archs='x86_64'
  ImageMagick @6.7.9-0_1+q16 (active) platform='darwin 11' archs='x86_64'
  ircii @20111115_0 (active) platform='darwin 11' archs='x86_64'
  jbig2dec @0.11_1 (active) platform='darwin 11' archs='x86_64'
  jbigkit @2.0_2 (active) platform='darwin 11' archs='x86_64'
  jpeg @8d_0 (active) platform='darwin 11' archs='x86_64'
  lame @3.99.5_0 (active) platform='darwin 11' archs='x86_64'
  lcms2 @2.3_0 (active) platform='darwin 11' archs='x86_64'
  libedit @20120601-3.0_0 platform='darwin 11' archs='x86_64'
  libedit @20121213-3.0_0 (active) platform='darwin 13' archs='x86_64'
  libffi @3.0.11_0 (active) platform='darwin 11' archs='x86_64'
  libiconv @1.14_0 (active) platform='darwin 13' archs='x86_64'
  libidn @1.25_0 (active) platform='darwin 11' archs='x86_64'
  libmcrypt @2.5.8_1 (active) platform='darwin 11' archs='x86_64'
  libogg @1.3.0_1 (active) platform='darwin 11' archs='x86_64'
  libpaper @1.1.24_0 (active) platform='darwin 11' archs='x86_64'
  libpcap @1.2.1_0 (active) platform='darwin 11' archs='x86_64'
  libpng @1.5.12_0 (active) platform='darwin 11' archs='x86_64'
  libsdl @1.2.15_0+x11 (active) platform='darwin 11' archs='x86_64'
  libtheora @1.1.1_1 (active) platform='darwin 11' archs='x86_64'
  libtool @2.4.2_0 platform='darwin 11' archs='x86_64'
  libtool @2.4.2_2 platform='darwin 11' archs='x86_64'
  libtool @2.4.2_3 (active) platform='darwin 13' archs='x86_64'
  libunwind-headers @35.1_1 (active) platform='darwin 13' archs='noarch'
  libvorbis @1.3.3_0 (active) platform='darwin 11' archs='x86_64'
  libvpx @1.1.0_0 (active) platform='darwin 11' archs='x86_64'
  libxml2 @2.8.0_0 (active) platform='darwin 11' archs='x86_64'
  libxslt @1.1.26_0 (active) platform='darwin 11' archs='x86_64'
  lynx @2.8.7rel.1_1+ssl (active) platform='darwin 11' archs='x86_64'
  lzo2 @2.05_1 (active) platform='darwin 11' archs='x86_64'
  m4 @1.4.16_0 (active) platform='darwin 13' archs='x86_64'
  mhash @0.9.9.9_0 (active) platform='darwin 11' archs='x86_64'
  ncurses @5.9_1 platform='darwin 11' archs='x86_64'
  ncurses @5.9_2 (active) platform='darwin 13' archs='x86_64'
  nspr @4.8.9_0 (active) platform='darwin 11' archs='x86_64'
  openjpeg @1.5.0_3 (active) platform='darwin 11' archs='x86_64'
  openssl @1.0.1b_0 platform='darwin 11' archs='x86_64'
  openssl @1.0.1c_0 platform='darwin 11' archs='x86_64'
  openssl @1.0.1e_1 (active) platform='darwin 13' archs='x86_64'
  orc @0.4.16_0 (active) platform='darwin 11' archs='x86_64'
  p5.12-locale-gettext @1.50.0_6 platform='darwin 11' archs='x86_64'
  p5.12-locale-gettext @1.50.0_7 (active) platform='darwin 11' archs='x86_64'
  pcre @8.31_0 platform='darwin 11' archs='x86_64'
  pcre @8.33_0 (active) platform='darwin 13' archs='x86_64'
  perl5 @5.12.3_1+perl5_12 platform='darwin 11' archs='noarch'
  perl5 @5.12.4_0+perl5_12 (active) platform='darwin 13' archs='noarch'
  perl5.12 @5.12.4_0 platform='darwin 11' archs='x86_64'
  perl5.12 @5.12.4_1 platform='darwin 11' archs='x86_64'
  perl5.12 @5.12.4_2 (active) platform='darwin 13' archs='x86_64'
  php53 @5.3.16_1+libedit (active) platform='darwin 11' archs='x86_64'
  php53-curl @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-ftp @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-iconv @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-mbstring @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-mcrypt @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-mongo @1.2.12_0 (active) platform='darwin 11' archs='x86_64'
  php53-mysql @5.3.16_1+mysqlnd (active) platform='darwin 11' archs='x86_64'
  php53-openssl @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-soap @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-sqlite @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-xdebug @2.2.1_0 (active) platform='darwin 11' archs='x86_64'
  php53-xsl @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php53-zip @5.3.16_1 (active) platform='darwin 11' archs='x86_64'
  php54 @5.4.6_1+libedit (active) platform='darwin 11' archs='x86_64'
  php54-apache2handler @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-curl @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-ftp @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-gd @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-iconv @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-mbstring @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-mcrypt @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-mongo @1.2.12_0 (active) platform='darwin 11' archs='x86_64'
  php54-mysql @5.4.6_1+mysqlnd (active) platform='darwin 11' archs='x86_64'
  php54-openssl @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-soap @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-sqlite @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-xdebug @2.2.1_0 (active) platform='darwin 11' archs='x86_64'
  php54-xsl @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php54-zip @5.4.6_1 (active) platform='darwin 11' archs='x86_64'
  php_select @1.0_0 (active) platform='darwin 11' archs='noarch'
  pkgconfig @0.27_0 (active) platform='darwin 11' archs='x86_64'
  python27 @2.7.3_0 platform='darwin 11' archs='x86_64'
  python27 @2.7.6_0 (active) platform='darwin 13' archs='x86_64'
  python_select @0.3_1 platform='darwin 11' archs='noarch'
  python_select @0.3_3 (active) platform='darwin 13' archs='noarch'
  re2c @0.13.5_0 (active) platform='darwin 11' archs='x86_64'
  readline @6.2.000_0 (active) platform='darwin 11' archs='x86_64'
  schroedinger @1.0.11_0 (active) platform='darwin 11' archs='x86_64'
  scons @2.2.0_0 (active) platform='darwin 11' archs='noarch'
  snappy @1.0.5_0 (active) platform='darwin 11' archs='x86_64'
  speex @1.2rc1_0 (active) platform='darwin 11' archs='x86_64'
  spidermonkey @1.7.0_5 (active) platform='darwin 11' archs='x86_64'
  sqlite3 @3.7.14_1 platform='darwin 11' archs='x86_64'
  sqlite3 @3.8.0.2_0 (active) platform='darwin 13' archs='x86_64'
  tiff @3.9.5_0 (active) platform='darwin 11' archs='x86_64'
  urw-fonts @1.0.7pre44_0 (active) platform='darwin 11' archs='noarch'
  webp @0.2.0_1 (active) platform='darwin 11' archs='x86_64'
  x264 @20111210_0 (active) platform='darwin 11' archs='x86_64'
  xmlcatmgr @2.2_1 (active) platform='darwin 11' archs='x86_64'
  xorg-kbproto @1.0.6_0 (active) platform='darwin 11' archs='noarch'
  xorg-libice @1.0.8_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libpthread-stubs @0.3_0 (active) platform='darwin 11' archs='noarch'
  xorg-libsm @1.2.1_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libX11 @1.5.0_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libXau @1.0.7_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libxcb @1.8.1_2+python27 (active) platform='darwin 11' archs='x86_64'
  xorg-libXdmcp @1.1.1_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libXext @1.3.1_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libXrandr @1.3.2_0 (active) platform='darwin 11' archs='x86_64'
  xorg-libXt @1.1.3_0 (active) platform='darwin 11' archs='x86_64'
  xorg-randrproto @1.4.0_0 (active) platform='darwin 11' archs='noarch'
  xorg-renderproto @0.11.1_0 (active) platform='darwin 11' archs='noarch'
  xorg-xcb-proto @1.7.1_0+python27 (active) platform='darwin 11' archs='noarch'
  xorg-xextproto @7.2.1_0 (active) platform='darwin 11' archs='noarch'
  xorg-xproto @7.0.23_0 (active) platform='darwin 11' archs='noarch'
  xrender @0.9.7_0 (active) platform='darwin 11' archs='x86_64'
  XviD @1.3.2_3 (active) platform='darwin 11' archs='x86_64'
  xz @5.0.3_0 platform='darwin 11' archs='x86_64'
  xz @5.0.4_0 platform='darwin 11' archs='x86_64'
  xz @5.0.5_0 (active) platform='darwin 13' archs='x86_64'
  zlib @1.2.6_0 platform='darwin 11' archs='x86_64'
  zlib @1.2.7_0 platform='darwin 11' archs='x86_64'
  zlib @1.2.8_0 (active) platform='darwin 13' archs='x86_64'

As you can see I have quite a few! But I was incredibly lucky, I typed in :

sudo port upgrade curl

And it installed, also fixing the boost problem, allowing me to continue with ‘sudo port upgrade outdated’ without the error happening!

Be prepared to wait a while for all of this compiling to go on! Once you are bang up to date we can install PHP 5.5. First lets get rid of all the old stuff compiled for your old OS (actually this works when if you put a -u flag on the sudo port -u upgrade outdated):

sudo port uninstall inactive

Now for 5.5! Feel free to add more PHP modules, full list available at the macports website.

sudo port install php55 php55-curl php55-ftp php55-iconv php55-mbstring php55-mcrypt php55-mongo php55-mysql php55-openssl php55-soap php55-sqlite php55-xdebug php55xsl php55-zip php55-apache2handler

cd /opt/local/apache2/modules
sudo /opt/local/apache2/bin/apxs -a -e -n php5 mod_php55.so

Now edit your httpd.conf to load 5.5:

LoadModule php5_module /opt/local/apache2/modules/mod_php55.so

sudo apachectl restart

Job done. My thoughts on all this? First you had all in one nonsense like MAMP and XAMPP. No good. Then we have the option of using a package manager such as MacPorts or Homebrew (macports in my case), but in all honesty I really think manually compiling and configuring everything is a much better idea, as per my other posts regarding the Ubuntu server in my work.

Sick of having to recompile everything after an OS upgrade? Think about setting up a Ubuntu Server Virtual Machine using VirtualBox! And then your server can run on any machine with any configuration, and the pain will be a lot less in the future!

Feel free to comment on anything you may have come across during the process! Everyone’s set up is different!

Remotely connecting MySQL

I’ve looked this up LOADS of times as its something you’ll want to do no doubt, much as you love PHPMyAdmin (ha!)

GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Now you can use SequelPro, MySQLWorkbench, Navicat, or whatever thing you like to use

 

Have you ever seen this problem?

I had a MySQL Date in my DB (2012-08-31). This is the 31st of August.

$expirydate = new Zend_Date($mysql_date);
 echo $expirydate;

Returned 31st August as expected.

But if the date is the 4th August (2012-08-04), the same code as above will echo out the 8th April!
Zend_Date automatically tries to guess the format, and mistakenly guessed Y/d/m !
I came across this problem when I tried to compare dates:

 $expirydate = new Zend_Date($mysql_date);
 $today = new Zend_Date();
 if($today->isLater($expirydate))
 {
     //today is beyond expiry date
 }

The Zend Docs weren’t very clear, and i thought maybe it should have been the other way around, $expirydate->isLater($today)
But it’s not.

All that is required is to set the format in the constructor. For MySQL Dates, the format is ISO_8601:

$expirydate = new Zend_Date($mysql_date,Zend_Date::ISO_8601);

Problem solved. The moral of the story? Make sure you declare the format of the dates!!