On a few sites I run I have vBulletin and / or Photopost installed. Upgrading from MySQL 4 to MySQL 5 is simple because these programs are 100% compatible with both (as they should be). Except for one issue. When installing MySQL 5 it defaults to asking to be installed in “Strict Mode” which is how a database should work. Strict Mode behaves in a way that when a malformed statement is entered into the database, it aborts the operation. This is great for helping to provide a structure and predictability to the data in the database. However, some scripts are not programmed in a way that would allow it to work well with strict mode. In vBulletin, this error will show up:

I would disable strict mode if possible over forcing vBulletin to use it, as some add-ons don’t play well with it.

If you are using the MySQL Administrator, go to startup variables -> advanced -> sql mode and note the options in the field. If there are no options stated, you are NOT in strict mode. If you see any or all of the following, the database is operating in strict mode:

STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

Just remove all 3 statements to disable strict mode. If your server has been operating in strict mode previously and scripts have been designed around it, be sure you can disable it without any adverse effects.

Here is an explanation from the MySQL manual on the above options:

STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.

NO_AUTO_CREATE_USER

Prevents GRANT from automatically creating new users unless a non-empty password is specified.

NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

With NO_ENGINE_SUBSTITUTION disabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.

(No Ratings Yet)

No Comments |  Print This Post

This tip can be done in either Windows or Linux. In Windows using the MySQL Administrator go into the Health Tab and then the Performance Tab. Look at the key_reads and key_read_requests numbers. The ratio of key_reads to key_read_requests should be above 1:100 if you can spare the memory. It should be no lower than 1:10.

How do you increase the ratio for better performance? Increase the key_buffer_size value to an acceptable level.

(No Ratings Yet)

No Comments |  Print This Post

The post below talks about processor affinity and setting IIS to use only one processor to increase stability of certain applications (PHP ISAPI anyone?).

You can set the affinity by opening Task Manager and selecting w3svc.exe or inetinfo.exe on the Processes tab. Right click and choose Set Affinity. Uncheck the processors which should not execute the application.

This is a temporary solution since it is reset once you restart Windows or IIS. Some people often disregard setting processor affinity as a fix since it never seems to work…but this is because they don’t set it permanently. Below is how to set the processor affinity permanently.

Grab the Imagecfg.exe tool from the \support\debug\i386 folder of a Windows NT 4.0, or the Imagecfg.exe tool from the Windows 2000/2003 Server Resource Kit.

Open a CMD prompt and type:

where 0xn is the affinity mask and drive:\Path\program.exe is the program you wish to set. The mask indicates which processor is to run the desired application. On a dual-core system, you use CPUs 0 and 1 (not 1 and 2).

CPU MASK
0 0x1
1 0x2
2 0x4
3 0x8

(average: 5.00 out of 5)

No Comments |  Print This Post

While I was trying to find a content management system for a section of World’s Cutest Animals I decided to try WordPress 2 instead of programming my own (I’m lazy sometimes).

When I installed WordPress 2.1.x it was working fine until midday when requests for the page spiked as usual. It was the dreaded PHP Access Violation. Previous to this, there had never been a PHP related issue with the site in 11 months. I figured I’d try to get it to work instead of uninstalling it right away, so I tried the following:

1. Disabled XCompress HTTP compression, thinking it was an issue with ISAPI ordering like so many IIS related problems.

2. Disabled Isapi Rewrite (thinking it was a compatibility issue between the many rewrite systems and rules on my server in addition to ISAPI ordering)

3. Upgrading PHP to 4.4.7, then downgrading to 4.4.2 (hoping another build had a fix in there, seems every build of PHP breaks or fixes something)

4. Uninstalling WordPress (Tada! Worked! But this is not what I wanted. I’m lazy, I wanted wordpress working)

5. Installed WordPress 2.2 (Still didn’t work)

6. Played with WordPress Permalinks (Nothing)

7. Tried Fast-CGI wrapper from IIS.net for IIS 6.0/7.0 (Worked!)

The Fast-CGI from IIS.net is an effort between Zend and Microsoft to increase the stability of PHP on the upcoming Longhorn Server and IIS 7.0. It’s no secret that PHP is one of the worst offenders of IIS instability. They do have a technology preview out that works with IIS 5.0/6.0. I recommend this if you do have stability issues with PHP ISAPI. It is about 5 times faster than php.exe CGI that comes with PHP 4. The wrapper works for PHP 5 as well. It installs in about 2 minutes thanks to Microsoft’s script and instructions.

Turns out WordPress has some XML GetFeatureList that breaks PHP by forcing a multithreading situation in IIS (w3svc.exe). PHP ISAPI is terrible at any sort of attempt of multithreading as it is not supported.

On a related note, because of PHP ISAPI threading issues, if you have a dual-core server, best to set the processor affinity of w3svc.exe or inetinfo.exe (depending on whether or not you are in isolation mode) to one processor. If you have mulitple websites, assign them to two Application Pools and have each pool running on a different processor. This will assure a much more stable instance of PHP (4 and 5). In each Application Pool, be sure to keep Worker Processes set to 1 (in the properties box). Setting it to a higher number causes multiple threads to be started.

Click here for instructions on permanently setting Processor Affinity in Windows

(No Ratings Yet)

No Comments |  Print This Post

To all those (3 people) who need to use file_get_contents in PHP on IIS 6, you may notice it doesn’t work by default. To see if it works, place the following code into a file called test.php.

You should have the first 20 bytes of the google source code echoed back to you on the page when it is executed. If not, you may need to play with IIS 6.

Does fsockopen work?

IIS 6 by default does not allow sockets to be created on port 80 (file_get_contents works on this port, of course). This is a result of how IIS 6 accepts connections. To get file_get_contents to work, you need to enable IIS 5 Compatibility (Isolation Mode) on IIS 6. You then need to choose Low (IIS Process) under “Application Protection.” Isolation mode settings are found in the IIS Admin Interface –> Right-click on Web Sites folder and look under the Service Tab.

(No Ratings Yet)

1 Comment |  Print This Post