2005 December - damonparker.org

12/12/2005

Force MySQL 4.1 to Use Old Style Passwords

MySQL 4.1 uses a new password hashing schema that translates passwords to a forty character hash instead of the previous version’s sixteen characters. If you upgrade MySQL and don’t rebuild PHP using the updated libraries, PHP applications won’t be able to authenticate properly. A quick (and temporary) fix, is to force MySQL to use the old password hashing schema until PHP can be rebuilt.

Add this to your my.cnf in the [mysqld] section:

# Default to using old password format for compatibility with pre 4.1 clients
old_passwords=1

More information on the MySQL site.

Popularity: 15%

12/9/2005

Using Subselect To Find Missing Rows

I have three tables items, colors, sizes; all related through a common primary key itemID. I noticed items has fewer rows than sizes and colors after a big import. How do I find which rows are missing from each table?

SELECT itemID
FROM sizes
WHERE itemID NOT
IN (
SELECT itemID
FROM items
)

Popularity: 13%

MySQL String Function Concat

MySQL CONCAT concatenates (ie. puts together as one) strings.

For example, we have a column with photo file names all missing the extension (my_photo_name instead of my_photo_name.jpg). To update all rows in the column with the .jpg extension, execute the following query:

UPDATE items
SET mainphoto = CONCAT(mainphoto, ‘.jpg’);

Popularity: 17%