mySQL natural number ordering

Had a situation today where I needed to order a list that looked like "DISTRICT 1, DISTRICT 2...DISTRICT 10, DISTRICT 11". Ordering this field with a simple ORDER BY groupField ASC puts "DISTRICT 10" and "DISTRICT 11" before "DISTRICT 2", which is not what I wanted. Also, the field wasn't always going to be setup as single string, a space, and a number, so I couldn't just SUBSTRING out the trailing number. Found the solution in the mySQL forums:

ORDER BY LENGTH(groupField) ASC, groupField ASC

now the ordering is as you'd expect from a user standpoint.

IE doesn't show option text when dynamically creating an optgroup

I dealt with a bit of a headache tonight trying to figure out why Internet Explorer 6 wouldn't display any option text if I created an option within an optgroup dynamically (using Javascript). It turns out that you have to set the innerText property of the option before appending it to the optgroup. Just setting the label or text properties of the option is not enough, you need to set innerText, like so:

select_obj = document.getElementById('my_select_box_id');
optgroup_obj = document.createElement('optgroup');
option_obj = document.createElement('option');

option_obj.label = 'option label';
option_obj.value = 'option value';

if ( typeof(option_obj.innerText) != 'undefined' ) {
option_obj.innerText = 'option label';
}
else {
option_obj.text = 'option label';
}

optgroup_obj.appendChild(option_obj);
select_obj.appendChild( optgroup_obj );

IMP Error connecting to IMAP server. 22 : Invalid argument.

IMP was giving me this error today when trying to login. Turns out that since I had the IMAP server set to use SSL (the default), PHP needs openSSL *compiled statically*, not just the openSSL extension. To fix this in FreeBSD using ports, I just did:

cd /usr/ports/lang/php4
make config
-> check the "Build OpenSSL statically" option
make clean
make
make deinstall
make reinstall

horde sidebar menu hangs on login after turba configuration

I was installing horde today on a new server, and noticed that after doing all of my configs, the sidebar would hang pretty much indefinitely when trying to login. I poked around for a bit and eventually found that turba was the issue. A little googling and a mailing list post led me to the information that the problem is the LDAP source in the turba/config/sources.php file. I don't use LDAP, so I commented out the LDAP source by changing:

if ( Util::extensionExists('ldap')) {

to

if ( false && Util::extensionExists('ldap')) {

in config/turba/sources.php and it now works perfectly.

InnoDB table creation fails after an improperly dropped table

I was playing around with InnoDB on my development server, somehow screwed things up pretty bad, and had to delete some .frm files from the db directory manually. However, I found that even though some of my tables didn't exist in show tables, I couldn't recreate them. the mySQL client was spitting out fairly ambiguous InnoDB errors (can't rename table, etc), and it was confusing me quite a bit. I even found that I could create the tables as myISAM, but once I tried to change engine to InnoDB, it wouldn't let me. I even tried dropping the entire database and recreating it, but the InnoDB data dictionary still had information about those tables in that database! I found some more insight by using the mySQL command "show engine innoDB status;", which told me the last foreign key errors. Turns out the foreign key constraints were still applied to my table even though the table didn't actually exist. I tried dropping the keys by name, but that didn't work. It didn't issue any errors, but it also didn't do anything. Ultimately I had to recreate all the keys as they had originally appeared (matching the name and type exactly), then drop the table properly.

Windows Explorer freezes when video files are in the folder you're viewing

I recently started having a problem where any time there was an avi file in the folder I was viewing, explorer would freeze up and have to be shutdown from task manager. Over at annoyances.org, I found a handy fix that took care of the problem right away. Here it is:

NOTE: before doing this, you should make a backup of your registry by going to start->run, typing regedt32 , then going to File > Export and saving the registry file somewhere safe.

1. Click Start
2. Click Run
3. Type: regedt32
4. Browse to the registry key (the things that look like folders on the left side of the screen) HKEY_CLASSES_ROOT\SystemFileAssociations\.avi\shellex\PropertyHandler.
5. Delete the Key on the right hand side called "default" by right-clicking and hitting "Delete".

That should be it. If that doesn't work, the following command might help, but it will disable all AVI thumbnail preview capability:

go to Start > Run, type: REGSVR32 /U SHMEDIA.DLL and press OK. To undo this change, simply go to Start > Run, type: REGSVR32 SHMEDIA.DLL and press OK.

ACT 2005 Install fails on SQLHotfix818.exe on Small Business Server 2003

This problem showed up while I was migrating a client onto a new SBS 2003 server. The ACT install would just about complete, then complain about an invalid exception in SQLHotfix818.exe at the very end. Subsequently trying to start ACT would cause an "Object reference not set to instance of object" error. I tried a lot of things, but here's what ended up working:

1. Go to Add/Remove Programs and remove any references to MS SQL Desktop Engine (including SharePoint and SBSMonitoring, you can reinstall these if you need them. If you actively use either, make sure you have a backup!).

2. Rename C:\Program Files\Microsoft SQL Server to something else, so the ACT install can't find it.

3. Make sure the SYSTEM user has full access to the C:\ drive (per ACT KB article #16478)

4. Make sure all services that start with MSSQL$ are stopped (Start -> Administrative Tools -> Services)

5. If ACT is installed, uninstall it.

6. Now install ACT, and you should be good to go.

Horde/IMP won't create sent mail folder. This is what the server said: Invalid mailbox name

I was setting up the new version of IMP today, and found that I couldn't create folders from webmail. I knew it wasn't an IMAP permissions issue, because I could create & subscribe to folders with no problems from my IMAP client. It turns out that with Courier-IMAP, the IMAP server I prefer, you need to set the following options in your server confing (probably /usr/local/www/horde/imp/config/servers.php):

under $servers['imap'] (or whatever server you're using - 'imap' is the default - change the namespace and folder lines to read:

'folders' => 'INBOX.',
'namespace' => '',

Note the trailing dot after INBOX. Then you have to LOG OUT AND LOG BACK IN before the changes will take effect. The docs suggest that these options should be the other way around for Courier-IMAP, but it doesn't seem to work that way.

Changing file associations in windows as a non administrator user

It seems that non-admin users on Windows 2000 and Windows XP can't edit their own file associations. This becomes a problem when you have a nonstandard association that needs to be in place for everyone in the office/organization. I grappled with this problem a bit, but eventually found that I could write a simple VBscript to edit the current user's registry settings to add in my own file assocations for a given extension. The code is below, simply save this script with a vbs extension (example: fileAssn.vbs) and add it as a login script for any users who need the association:

Option Explicit

Dim objShell
set objShell = WScript.CreateObject ("WScript.Shell")

'----------------------------------------------------------------
' Don't edit above here
'
' Below is an example of associating PDF files with AcroRd32.exe
' You can change this to whatever you need, and just
' copy and paste the line over again to add more associations.
'-----------------------------------------------------------------

addFileAssociation ".pdf", "AcroRd32.exe"


'-------------------------
' Don't edit below here
'-------------------------
Sub addFileAssociation( fileExt, whichApp )

If ( Left(fileExt, 1) <> "." ) Then
fileExt = "." & fileExt
End If

objShell.RegWrite "HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\FileExts\" & fileExt & "\Application", whichApp

End Sub

passing username to spamc through maildrop with postfix

I'm setting up a purely virtual postfix/mysql mail server, and I wanted to implement spamassassin to filter spam. Initially I had intended to use procmail, but postfix doesn't currently support procmail for virtual users, so I had to go with maildrop. All I wanted was to enable the line:

xfilter "/usr/local/bin/spamc -u USERNAME"

and have the username changed to the recipient for the mail being processed. After stumbling through some changes in master.cf and trying different maildrop variables, I found that the easiest way to get the username to the script was simply to pass ${recipient} as a parameter to maildrop in master.cf, like this:

maildrop unix - n n - - pipe
flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient} ${recipient}

(yes, ${recipient} should be there twice.) This way, I can always pull the recipient email address directly into the maildroprc file by referencing $1. So my xfilter line became:

xfilter "/usr/local/bin/spamc -u $1"

then I just fleshed out my maildroprc script (i'd post it here but it's pretty specific to my setup, and other howtos on maildrop/postfix are available) to handle what happens if a mail is tagged as spam.

Windows XP automatically tries to search when you click a folder

A friend of mine came to me with this issue, and after a bit of searching, I came across http://windowsxp.mvps.org/searchwindow.htm . Running "regsvr32 /i shell32.dll", as suggested by that site, fixed his problem, so I figured I'd repost it here, since it took me a while to find:

The following is from http://windowsxp.mvps.org/searchwindow.htm:

If you double-click a drive or folder, Search Companion may start and the drive or folder may not open. 'Search' will be the top most in the right-click context menu for a folder or drive. This usually happens if you've edited the File Folder or Drive via the Folder Options File Types dialog, in order to add a context-menu item such as "Command Prompt here", "Print Directory" feature for folders or drives.

References:
http://support.microsoft.com/?kbid=321379
http://support.microsoft.com/?kbid=321186

Quick fix

Click Start, Run and type this command:

regsvr32 /i shell32.dll
(Tip from David Candy)

To fix the problem manually, open Registry Editor and navigate to:

HKEY_CLASSES_ROOT\Directory\shell

-and-

HKEY_CLASSES_ROOT\Drive\shell

• In the right-pane, locate and click the (Default) value under the keys mentioned above

• Click Modify on the Edit menu.

• Type the word none in the Value data box, and then click OK.

• Exit Registry Editor.

Windows (WSH) Shell Login scripts won't run after allowing specified programs

In an attempt to lock down some workstations that were having spyware trouble, I made use of the Windows 2000 "Run only allowed windows applications" option in the Group Policy Object. [Note: this feature is expanded in Windows Server 2003 as "Software Restrictions Policies" in the GPO editor, but the idea is the same]. Since the network users only use a handful of applications ( MS Word, Internet Explorer, etc ), I was able to specify these as allowed programs, disallowing everything else. However, even if I specified my login scripts in the "allowed programs" list as my_script.vbs, they still weren't running. On a hunch, I added wscript.exe, the interpreter for VBS scripts, to the allowed program list, and everything ran fine after that. I also allowed sndvol32.exe (which will allow users to access the sound mixer to change their volume), calc.exe, notepad.exe, mspaint.exe, and a few other various windows "accessories" that everyone should have access to.

Bottom line:
Can't get VBS scripts to run due to having specified an allowed list of windows applications? Try adding wscript.exe to the list.

Users restricted from changing their volume settings for the same reason? Try adding sndvol32.exe.

Proftpd can't find libmysqlclient.so.14 inside chroot jail

I was trying to make the switch to purely virtual ftp users in Proftpd, but noticed that the jailed Proftpd binary (created with my Proftpd Chroot script) was having trouble finding the mySQL libraries. No problem, I thought, it tells me what file it can't find, so I'll just copy them into their respective homes underneath the chroot jail. The first two went fine, libm.so and libz.so, but even after copying libmysqlclient.so.14 from /usr/local/lib/mysql/ to /usr/chroot/proftpd/usr/local/lib/mysql, which is underneath my chroot jail, Proftpd still couldn't find it on startup. After tinkering with the file location and doing some google searching, I found a thread related to shared objects that suggested using strace on a binary file to determine where exactly it's looking for a particular shared object. Aha! Proftpd running under the jail, for reasons I haven't yet figured out, wants libmysqlclient.so.14 to be in /usr/lib. No mySQL sub directory or anything, just in the standard /usr/lib directory. After moving the file there, proftpd started up like a charm.

mysqldump out of memory error 5

Tonight I was trying to mysqldump some large tables, but kept getting "mysqldump out of memory error 5". I knew I had dumped tables larger than the ones I was dumping, so I wasn't sure what was going on. After poking around with mySQL settings in my.cnf to no avail, I decided to try optimize table. That seemed to fix the problem. The reason it came to mind is because I had purged about 100,000 rows of the problematic table earlier in the day and thought maybe it needed cleanup

bottom line:
mysqldump giving "out of memory" error, try:
optimze table myTable

Windows MSI installer won't install

I was trying to install Veritas Backupexec 9.1 tonight, and it kept failing with "backupexec failed to install msde". The Veritas site said to try to install manually, and I found that I couldn't execute the MSI file at all. It simply did nothing when I clicked the file, but that was a problem I had seen before. Security settings on MSI files have to be very leniant for some reason, so I simply granted "everyone" full access to the install files. I'm not sure which user actually needs access in order for the install to run properly (maybe SYSTEM?), but since security isn't going to be important on files I'm deleting, I just granted EVERYONE full access to the entire folder. You should not, however, make a habit of doing this on files you don't want your entire organization to have access to.

Bottom Line:
clicking an MSI file seems to do absolutely nothing? Try granting full access permissions to the windows group "EVERYONE" and see if things change. If security is important on the MSI file, take away the full access as soon as you are finished installing.

PHP Sessions stop working after mod_php4 upgrade to php 4.3.10

Last night I upgraded PHP to php 4.3.10 from 4.3.9. Everything seemed to have gone well, but today people complained about some scripts not working. I did some digging and found that session_start() would stop the script cold with no error messages. I was rather confused for a while, then decided to just recompile the session extension ( /usr/ports/www/php4-session/make && make install ) and things seemed to work after that. Now I'm going to recompile all extensions in case something else was broken in the process. Hopefully I won't be breaking anything myself.

Found Windows 2000 Sound Drivers for E-Machine Etower 533id2

Came across these drivers after some searching, thought someone else might find the link useful. This machine (Etower 533id2) uses the Crystal CS4281 Chipset.

http://www.opendrivers.com/freedownload/217909/Crystal-CS4281-Sound-Driver-Windows.html

Selecting an age from a birthday date field in mySQL

I added this to the mySQL manual a while back, but it seems to have been filtered out in more recent versions of the docs. Although I prefer to do my arithmetic in PHP, this query will determine an age from a date field birthDate in mySQL.

SELECT YEAR(NOW()) - YEAR(birthDate) - IF ( MONTH(NOW()) < MONTH(birthDate), 1, 0 ) - IF ( MONTH(NOW()) = MONTH(birthDate) AND DAYOFMONTH(NOW()) < DAYOFMONTH(birthDate), 1, 0) AS age

FreeBSD thinks vpopmail isn't installed even when it is

I grappled with this problem a little bit today, where FreeBSD kept insisting on installing vpopmail from other ports even though it was already installed. The port install would fail, however, because FreeBSD won't install over another package without making "reinstall". I couldn't figure out why it thought vpopmail was not installed until I froze the screen when it was checking, and I noticed that it looks for /usr/local/vpopmail/lib/libvpopmail.a. Since I install vpopmail to /home/vpopmail, that file was in /home/vpopmail/lib/ !
I just did:

mkdir -p /usr/local/vpopmail/lib
ln -s /home/vpopmail/lib/libvpopmail.a /usr/local/vpopmail/lib/libvpopmail.a

and the problem went away.

Installing vpopmail to a directory other than the default

I was setting up a FreeBSD machine with Matt's Mail Toaster today, and I couldn't seem to get vpopmail to install anywhere except for /usr/local/vpopmail, even though I specified in toaster-watcher.conf that the vpopmail directory should be /home/vpopmail. After some digging, I found that the vpopmail install checks the home directory of the vpopmail user in order to find out where it should install to. So, I did

chsh vpopmail

and changed the home directory to /home/vpopmail. To get it to work seamlessly, though, I also had to set PREFIX=/home before doing the vpopmail install, so the line was:

env PREFIX=/home toaster_setup.pl -s vpopmail

or, if you're using the ports on your own:
env PREFIX=/home make install