Fixing MySQL Error 1067 on Windows Server 2008 r2

Let me just start by saying that this has been really annoying me for some time now and obviously taking the server offline to fix this is difficult at the best of times.  Fortunately, this week I've had the time to take a look at this and see if I can sort the issue.

From looking at all of the various 'solutions' out there, it doesn't actually seem that any of them are correct.  Don't get me wrong, I'm sure there is one somewhere but I didn't manage to find it past page two of Google so I thought I would give it a go myself.  Just to save you some time, the following doesn't work despite what a lot of people will have you believe (and if they do, then you have been lucky):

• Removing the service and reinstalling;
• Removing and recreating the log files;
• Repairing the install;
• Recreating your MySQL instance;

Now the reason that it happened on my server was due to an upgrade from MySQL 5.1.x to 5.5.x a few months ago.  I thought that I had done the usual routine correctly of taking backups, downing the service, uninstalling, reinstalling and then restoring.  Job done I thought.  This has never been a problem on any other server that I've done this and so I wasn't expecting this.  All it meant was that from time to time, the service wouldn't start after a Windows reboot.  Not really a big deal but any down time at all is a big no no.

So, in essence you will actually need to do most of your preparation that you would normally do for an upgrade.  You might as well download the latest version of MySQL while you do this so firstly go and grab that.  Next, do your normal backup of your databases and also your my.ini from wherever you normally keep it.  Stop your MySQL service and then uninstall your MySQL installation.  Go to your ProgramData folder and remove your MySQL folder, and do the same in your Program Files\MySQL folder (remove your server folder just in case you have your tools or workbench installed in there).  Now go to your Users folder and have a look through there and it will normally be hidden in your Default\AppData folder.  Launch regedit and search for 'mysql' and remove any instance of it that is related to your server install.  Restart your server now.

When your server comes back on, run your nice new installer, giving it the same settings that you had before.  Set a new instance up (it can be the same service name if you like) and then it will go off and set your my.ini, your service and your security settings.  Your service should now be running straight away.  Just to check it though, stop the service and start it again.  You should find that it's running without erroring.  If it does error, not to worry, open a command prompt and browse to your bin folder inside your new installation and run the following:

mysqld -remove MySQL

Where MySQL is the name of your service that you specified.  Assuming that you have typed that in correctly it will say that it has removed the MySQL service.  Now run the instance configuration wizard again (inside your bin folder) and go through it again.

Lastly, restore your databases and users / permissions and you should be done.  Sorry that this is a bit lengthy but this worked for me and every other solution that I found didn't sort my problem.  It seems that this error goes way, way back to MySQL 4.x but nobody has worked out a way to fix this properly.  As I mentioned before, other people have posted their solutions which may have worked for them, but that could mean that this is an inconsistent error which is why it hasn't been fixed as of yet.

SQL Server and Dynamic Memory

Since our move to Hyper-V and the addition of Service Pack 1 for Windows Server 2008r2, the addition of Dynamic memory has helped us vastly manage the resources within our virtual servers much better.  For those who don't know, Dynamic Memory is where the memory utilised in virtual environments can be changed dynamically while the virtual machine is running.  For this to work the OS needs to be aware and it is something that SP1 does nicely for us.

I came across something odd a few weeks ago after deploying a new virtual machine running SQL Server 2008 Standard Edition.  The server had plenty of resources as the OS was able to utilise much more memory as and when it was needed.  The problem was that the SQL Server would fall over without notice.  After reading through the logs, it soon became apparent that the SQL Server would continuously keep running out of memory.  The virtual machine has its settings set to a minimum of 512mb of memory, which will balloon when it is required.  The problem is that the standard edition of SQL Server uses a fixed amount of memory from the OS when it's running and it's not able to ask for more resources.  The only editions that can utilise Hot Memory are the Enterprise and Datacenter editions.

So the moral of the story is that the next time you install your SQL Server and you are running on the Hyper-V platform, it always almost certainly will benefit you to install the Enterprise edition so that you don't need to set your virtual machine to use a much higher fixed memory that the other virtual machines running within that pool can't use.

PHP 5.2 ISAPI to PHP 5.3 FastCGI

If like me it took you a little while to move from ISAPI with IIS to using FastCGI then fear not, you aren't alone.  Being honest, ISAPI with IIS went very well together as it was fast and very easy to setup.  Future versions of PHP no longer support ISAPI as of version 5.3, so you may as well make the move sooner rather than later.

So what do you need to do?  Well, firstly, download the latest build of PHP for your system (download the NTS version, which is the non-thread safe version).  Extract it somewhere on your system i.e. c:\PHP or wherever you mostly feel comfortable.  Next, assuming that you are using either Windows Server 2008 or 2008 r2, go to your Server Manager console, and then click 'Roles' on the right.  Next, click the link on the left named 'Add role services' under the Web Server (IIS) heading. This is assuming that you have IIS already setup on your server, otherwise you will need to add this as a role to your server. Drop down the 'Application Development' box and check the 'CGI' box as shown below.

As you can see, I still have my ISAPI extensions still in there but we can remove those later as we won't be needing them.  Click 'Next' and then let Windows go off and do its own thing.

Ok, your PHP config.  Do what you would normally do here.  In addition to your normal PHP config which can mostly be copied from your old PHP 5.2 config you will need to change the following:

• cgi.fix_pathinfo=1
• fastcgi.impersonate = 1
• cgi.force_redirect = 0
• extension_dir = 'c:\PHP\ext' *or where you have your PHP extension folder*
• open_basedir = *location of your wwwroot folder typically*

Don't forget to change those asterisk comments to real values!  Ok, next back over to IIS, so open your IIS Manager.

Click on your server on the left hand side.  In the central pane will be an icon called 'Handler Mappings'.  Click on that, and then go to 'Add module mapping' in the right hand pane.  Fill in the following details and click 'OK', and then 'Yes' in the popup box when you are finished:

• Request path: *.php
• Module: FastCgiModule
• Executable: *Link this to your PHP folder and then your php-cgi.exe file*
• Name: PHP FastCGI

Next, you will need to do is go back to your server on the left hand side and then click 'FastCGI settings'.  When you get there, click the PHP entry in the middle, and then click 'Edit...' on the right.  Change the InstanceMaxRequests to 10000, and then click on the '...' button under 'EnvironmentVariables'.  Add an entry of PHP_FCGI_MAX_REQUESTS with a value of 10000, and then click 'OK'.  If you don't do these steps, IIS uses its default settings of 200 instanceMaxRequests and 500 PHP_FCGI_MAX_REQUESTS.

Nearly there now.  Make yourself that old PHP test file with the following information: <?php phpinfo(); ?> and see if it runs.  Chances are if you are on a 64 bit system this will have failed unless you tell IIS to use 32-bit applications.  To change this, go to the Application Pools, and then go to your website in the central pane.  Click 'Advanced settings' and then a popup box will appear.  Change the 'Enable 32-bit applications' entry to 'True'.

Assuming that you have set up the rest of your php.ini correctly, it's a case of job done for now!

Moving on, onwards and upwards!

Following on where I left off really from my last post!

I briefly mentioned in my last post that I had an upgrade path in place (after testing of course!) for 'Connect'.  The time was approaching to move on with the technology, whereby Connect had been in place for three years and in that time it had only ever had a couple of PHP / MySQL point release updates.  Not that this was a problem of course, but you can never be left standing still.

Anyway, after doing a bit of research and some testing on my machine at work I ended up drawing up a plan for the migration.  Not only would I be migrating the software from the old server to a new one, I would be taking advantage of certain in-house server developments.  Our new server cluster which runs Microsoft Hyper-V, has now been patched to SP1 by Microsoft to support dynamic memory - which means lots of nice resources for any server sitting within that cluster.  Not only this, I drew up plans to upgrade the core functionality of Moodle to version 1.9.10, as well as move to 64-bit MySQL and also move to the latest PHP 5.2.x release (I don't particularly like running Fast CGI on IIS as it has odd behaviour issues sometimes, which left me with ISAPI).

Testing of all of this was done successfully, very successfully!  With nearly everything running in native 64-bit mode on the server (except PHP), the performance boost was clear to be seen.  Pages instantly loaded quicker, reports were much faster to be dynamically generated and most of all, there would be peace of mind that it should, (famous last words!) be a lot more reliable and ready for the future.

To get started with the migration, one of our library server images of Server 2008 r2 was set up, and immediately patched with the freshly released Service Pack 1 from Microsoft.  MySQL, PHP and IIS were also set up, before copying the code across, setting security permissions and restoring the database from the old server.  The Moodle upgrade and module upgrades then took place, whilst downing the old server to ensure that there would only be one database in use.

And that was it really, how to migrate your Moodle!  Downtime was minimal, and first impressions are that everything seems very fast which I think in part is a little to do with everything.  The contributing factors in my opinion are:

• (The obvious) Much more powerful server, with lots of resources
• Move to 64-bit MySQL
• New PHP release
• Newer Moodle release
• Move to Microsoft Server 2008 r2 SP1

I'll be adding more posts soon, so please follow this blog if it will help you in your day to day duties!

From the start...

Hi everybody, just to introduce myself and my blog firstly!

I'll start by saying that this isn't my first blog, but it is my first one for quite some time - about four years to be precise.  The reason for a re-start of my blog is to work more closely with other developers in the web development field, and hopefully to help anyone in need.

Next, I'll mention what by background is.  I've been a developer in education now for about six years now, and in that time I've worked closely on new ideas, more recently working and developing a highly customised version of Moodle, which has now been branded 'Connect'.

I've worked with various different languages on different operating systems using different databases, but most of my daily work and expertise involves Windows Server 2003r2/08r2, MySQL v5.x and PHP 5.x.  For me, I would say this is now my preferred environment after working with various different combinations of the above, and for many different reasons that I won't go into just now - but I'm sure that they will be apparent in the future as this blog develops.

The current, but soon to be retired server platform that I'm running is an old HP Proliant server, with the following specification:

• Intel Xeon 3065 2.33Ghz processor
• 4Gb DDR2 Memory
• Microsoft Windows Server 2003r2
• PHP 5.2.10 (Upgraded from 5.1.9)
• MySQL 5.1.34 (Upgraded from 5.0.37)
• Microsoft IIS 6
• Moodle 1.9.0+

The server was originally bought to be a 'stop-gap' but has actually performed brilliantly during the time that it has been in service.  Personally, I believe that for 2000 users, the above specification is more than adequate.  What does let the server down especially, is IIS 6 which has increasingly become unreliable which, after doing some research, I believe is down to a corrupt IIS configuration.  Since there have been plans in the pipeline to migrate Connect to a Server 2008r2 environment for quite some time, I have left this alone for the time being.  Other than this, the server is extremely fast and has been very reliable with near enough 100% server uptime (excluding restarts for Windows updates).

Some of the posts that I will be writing shortly will be looking at:

• Move of Connect to a new Windows Server 2008r2
• Upgrading PHP 5.x
• Possible up/downgrade of MySQL
• IIS 6 deep rooted problems
• Upgrading Moodle
• Is Moodle 2.0 worth it?

The issues above are what I believe are current issues affecting my environment, and at some point I hope to have addressed all of the above.

Anyway - thanks for taking the time to read my blog, I hope that you have found this informative.  Please take the time to click on a sponsored link for me (even if you just open it and then close it!), and even follow this blog.