Blog

Posted on

Using “new” PHP with “old” MySQL passwords

I recently encountered this error trying to connect Achievo to an existing corporate project-tracking database:

Critical: Unknown error: 2000 (mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD(‘your_existing_password’). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file).

To understand what’s going on, you need to understand a couple not-so-recent developments in PHP and MySQL:

  • MySQL version 4.0 and earlier used a 16-byte password hash, but starting with version 4.1+ uses a more secure password algorithm and 41 byte hash (although still supporting the older less secure password hash if needed).
  • The original external PHP module for accessing MySQL was mysql, but the current preferred module is mysqli (MySQL Improved).
  • The original low-level c-library that mysql (and mysqli) used to actually interface to a MySQL server was libmysql (included with MySQL), but starting with PHP 5.3 they are typically compiled with mysqlnd (a seperate project). mysqlnd has many advantages compared to libmysql, but it doesn’t support the old 16-byte MySQL password hash.

The database server I needed to connect to was running MySQL v5.0, and was configured with “old_passwords” set to On, which sets password operations to use the older 16-byte password hash (possibly because the original client/server application had been developed with MySQL 4.0 or earlier).

The simplest solution would have been to turn old_passwords Off and reset my password as the error message said (assuming the password column in the mysql.usr table would hold a 41-byte hash, otherwise its width would have to be increased first). However, the dba was loath to change the server configuration in case it broke existing mission critical enterprise applications, so I needed a different solution.

Since mysql and mysqli are compiled seperately, they can use different underlying c-libraries. In other words, mysql can be compiled to use libmysql – which supports the old 16-byte password hash. That would allow using mysql to connect to the corporate project-tracking database, without any effect on using mysqli to connect to other databases.

On a FreeBSD server, it was as easy as:

# pkg_delete php5-mysql
# cd /usr/ports/databases/php5-mysql
# make config  (uncheck support for mysqlnd)
# make install clean

and then edited the Achievo config.inc.php file to use mysql for the corporate database instead of mysqli.

Posted on

Google Maps Experiment

I’ve been trying to spend a day a week of focused quality time at the keyboard working on Achievo, combined with a long weekly bike ride for thinking. Last Friday, I spent the day with my laptop in a quiet corner of the Mount Royal University library (my first alma mater). Although thunderstorms were in the forecast, I was feeling lucky and set out by bicycle (with a heavy-duty garbage bag for my laptop just in case). I made it to MRU without getting wet, enjoying beautiful but ominous thunderheads overhead, and was safe and sound by the time the rain started. Two thunderstorms later the weather cleared and I was able to ride home (steering clear of some major puddles!).

So here’s to the point of this post, my first embedded Google Map. It was pretty easy to create, but for some reason I can’t get the zoom level right in the image here (the top and bottom are always cropped). Oh well, you can always open the map by clicking the link.


View Calgary SW Bicycle Loop 1 (FCPP, MRU, Weaselhead) in a larger map

Ride on!

Posted on

ToDoList vs TaskCoach

If you’re looking for free task management software, I’ve got a couple suggestions. If you’re managing tasks for a group of people and don’t mind Windows-only software, take a look at ToDoList. If you’re managing tasks for yourself only but need something cross-platform, then take a look at TaskCoach.

Task management is a level down from project management. I’m sure everyone is familiar with Microsoft Project, but there are actually more than a few enterprise-type applications available that would rival SAP in complexity. I needed a tool for my team that was less about calculating critical paths, resource loading, and slack time, and more about keeping track of the teams’ s multiple conflicting priorities so I could step in and lend guidance towards a bigger picture when necessary.

First, I tried FreeMind for several months. I liked the “everything draws from something else” nature of mind-mapping, and the team liked the graphical view of our collective tasks and how they related. On the downside though, it was hard to review tasks in a linear fashion in a meeting, so next I tried the Windows-only ToDoList.

I’ve been using ToDoList for almost two years now. It has a clean user interface, tasks are easily assigned, categorized and sorted, it supports hierarchical tasks (or sub-projects), and has a good selection of built-in filters and attributes.

However, recently I’ve started alternating between Windows and Unix laptops for Achievo development, and needed a cross-platform task manager (I should also say I’m currently the only developer working on this new Achievo functionality). After some research, I settled on TaskCoach for Achievo tasks. The user interface works well, and it’s similar enough to ToDoList that I didn’t need a neural reboot when switching between the two. So far so good for another month or two.

But then one day I found myself asking “Why exactly am I using two different task management tools?”. ToDoList and TaskCoach seemed relatively interchangeable, ToDoList has CSV export while TaskCoach has CSV import, and so I started on migrating my ToDoList tasks to TaskCoach without giving it a further thought. First came a minor irritation that ToDoList wouldn’t export the ‘Assigned-To’ for a task, but then I thought “No problem, I’ll just assign them later manually”, but then after another hour found it didn’t matter – because TaskCoach doesn’t support assigning tasks!

Note to self: Never dive in without giving something a further thought – especially when you’re absolutely sure you don’t need to!

Posted on

Adding Up SaaS Applications

Incorporating SaaS applications into an enterprise’s business processes can offer a number of advantages, including:

  • focusing internal resources on core strategic strengths instead of infrastructure services
  • being able to pick the best fit from a variety of mature low-risk best-of-breed applications
  • lowered internal IT hardware and support costs
  • well-defined costs

But as always, nothing comes for free and the cost of individual applications adds up quickly. For example, assuming a sales and development SME on a growth track with 100 employees, including a 10-person sales team, a couple senior admin/HR roles, consolidated project management across the organization and a product engineering team who working with part numbers and bills of materials, and using the following SaaS applications:

  • Taleo Recruit for talent recruiting – $500/month (Taleo Business Edition Recruit module, 5 users)
  • Saba People Cloud for basic talent management – $500/month (based on competitor Kapta pricing of $5/person/month)
  • Salesforce for customer relationship management – $1250/month (Enterprise version, 10 users)
  • KnowledgeTree for document management (engineering, legal, administration, etc.) – $2000/month (100 users)
  • Basecamp for project management – $99/month (100 projects, 40 GB storage)
  • Aligni for engineering to manage parts and bills of materials – $199/month (< 10,000 parts)

The total is $4,548/month, and doesn’t include an ERP system for managing financials – which could add another $3,330/month (for either a basic system with limited extensibilty, or the base price for an extensible system before add-ons and customization).

Now, I’m not saying this isn’t money well spent, and for many organizations it is. But bear in mind it’s cash off the bottom line and attention (a rare and precious commodity) taken away from something else in order to learn something new. Carefully consider the complete value – and the complete cost – before signing up for another monthly payment on a credit card, because that’s the easy part.