MODx & MySQL

Hi, all - n00b here, so please be gentle... I haven’t found an answer to this either on the forum or in the wiki, but hopefully I’ve just missed it and someone can help me out.

I’m trying to set up MODx on my home PC just to get it working and get a feel for what I can use it for. Here are the details for my PC:

OS: Windows 2000 SP4 + various hotfixes
Browser: use Opera, Firefox, IE pretty interchangeably
Web server: Abyss X1 v2.4
(neither Apache nor IIS are installed on this PC)
PHP version: 4.4.7
MySQL version: 4.1.12a (ia32) (no large tables)
MODx version: 0.9.6 (tried 0.9.5 also)

Abyss has been configured properly for years - other web applications (PHPnuke, XOOPS, MediaWiki, phpBB, Mantis) work fine. MySQL likewise has been working fine for a long time. Other PHP scripts can access their MySQL databases without any problem.

However, when trying to install MODx, it goes through the first few steps OK, but when trying to test the database connection, it always fails. I have created a DB user named ’modx’ and granted it ALL global privileges. I have tried pre-creating the DB ’modxdb’ and giving user ’modx’ ALL privileges on this database. Nothing seems to work. I have no idea what might be preventing MODx from connecting to the database.

Can anyone out there suggest anything else I might try? Has anyone else managed to get MODx working successfully with Abyss Web Server and other similar configuration to mine?

have no idea about Abyss Web Server but if you are a windows user why not trying Wamp server?
im sure youll be happy with wamp! its free and easy to use n configure, download it here: http://www.wampserver.com/en/
during wamp installation, it installs php, mysql, apache for you !

Thanks for the reply.

I’d consider it, except that I already *have* PHP and MySQL installed and working fine, and I have no interest in installing a huge application like Apache on this relatively low-powered PC.

Abyss Web Server is a sweet little program, with very modest system requirements, and has worked fine for everything else I’ve ever tried to set up except for MODx.  It is a tiny (<400KB) download, is free, and is very easy to install and configure - perfect for home testing. The home site is http://www.aprelium.com - and I’d be interested to know if more knowledgeable MODx users are able to get MODX working in a similar configuration.

Bottom line is that I really doubt that Abyss is the problem here. I suspect some sort of configuration issue between MODx and MySQL, but have no idea where else to look for it.

Quote from: beerslayer at May 26, 2007, 09:20 PM

Bottom line is that I really doubt that Abyss is the problem here. I suspect some sort of configuration issue between MODx and MySQL, but have no idea where else to look for it.

i just found a bugtrack partially related to your issue here with low priority to be done, you can test modx0.9.5 on Abyss server i guess, then you might want to upgrade it to 0.9.6?

Unfortunately, I’m not getting that far. I never make it to the Manager because the installation won’t complete.

I’ve gone in and tweaked the MODx (ver. 0.9.6) code in a few places to get it to give me more information about why it’s failing. It looks like the problem is in install\action.connection.php, lines 15-17, which I’ve changed to read as follows:



(Note to developers: please consider adding this output to your code. "Connection to host: failed!" doesn’t help much...)

At any rate, with my hacked code in place, testing the DB connection gives the following result:

Connection to host: failed! - Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client

I was not aware that my version of MySQL is below the minimum required for the "MySQL client" (which is MODx, of course), and I’m not enough of a guru to figure out what authentication protocol the MODx script is using.

If a simple call to mysql_connect() is failing, I cannot imagine how all my other PHP-based web applications (phpBB, PHPnuke) are working. This is truly baffling.

Got it!

The problem had to do with MySQL versions 4.1 and up.  Apparently, in version 4.1, MySQL changed the password format, using a longer hash to encrypt the database users’ passwords before storing them in the user database.

To solve the problem, all I had to do was to force my MySQL user named "modx" to store its password using the old format, by connecting to MySQL as root via the commandline:


... and entering the following code:


... and then flushing the tables:


Following this operation, the database connection test passes successfully and installation proceeds normally!

This also serves to confirm that MODx and Abyss Web Server work fine together. This issue had nothing to do with Abyss.

There’s more information on this MySQL-related issue at:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html

Now, for the $64,000 question: can MODx be updated so that this isn’t an issue?  I doubt all MODx users will be using MySQL versions < 4.1...

MODx doesn’t have it’s own MySQL client. It uses the client that is compiled in PHP (php_mysql.dll and libmysql.dll) to connect to the MySQL database with mysql_connect function.

Note: In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.

So the ways to go around this is:

1. do what you did
2. edit mysql.ini (or my.cnf or the like) and put set_variable = old_passwords=1 under [MYSQLD]
3. downgrade MySQL to <4.1.1
4. install mysqli php extension (included in php5)
5. install php5

This is not a bug or problem in MODx, it’s a problem with your environment setup.

Thanks for the response.

Quote from: doze at May 27, 2007, 04:53 PM

MODx doesn’t have it’s own MySQL client.

Right, but that’s not what I said.  I said that it *is* its own MySQL client, and from what I know, I stand by that statement. 

Quote from: doze at May 27, 2007, 04:53 PM

So the ways to go around this is:

1. do what you did
2. edit mysql.ini (or my.cnf or the like) and put set_variable = old_passwords=1 under [MYSQLD]
3. downgrade MySQL to <4.1.1
4. install mysqli php extension (included in php5)
5. install php5

This is not a bug or problem in MODx, it’s a problem with your environment setup.

With all due respect, I disagree.  My environment setup works fine with everything else.  I feel that I should not have to give up the added security of a longer password hash, solely due to MODx’s not including support for modern versions of MySQL (4.1.1 and up).

Your option #2 would only force MySQL into an obsolete mode and thus force ALL applications to use old-style password hashing, which would prevent newer applications which do support the added security from taking advantage of it.

Option #3 is out of the question.

As for option #4, my understanding of mysqli is that it requires a completely separate set of function calls: i.e. instead of calling mysql_connect(), you’d have to call mysqli_connect().  I could be mistaken about this, but if it’s true, this would involve a complete rewrite of MODx, which doesn’t make much sense to me.

I’m not sure that option #5 would resolve the problem... I simply don’t know enough about PHP5. It’s possible that you’d still have to force the use of old-style password hashing even with PHP5.

If I get any free time (yeah, right), maybe I’ll look into what it would take to change MODx to support the longer password hash.  I suspect the changes aren’t all that extreme, but of course I won’t know for sure without further investigation.

I really think you might have hit on the perfect storm of configuration issues. All my sites are on typical LAMP stacks with Apache and PHP 5.x and either MySQL 4.1.x or 5.0.x ... and have been since MODx version 0.9.1.

Sorry you think this has something to do with MODx, but it doesn’t; MODx does not use MySQL password hashing functions, which is what this is in regards to. I suspect you created the MySQL user account in a way that made it incompatible with the client connection you are using. The only solution is to run your client with the old-passwords=1 (to prevent your out-of-date client from not being able to connect when a long password hash has been used). This is simply caused because you set a password in 4.1 for your db user that is not compatible with a pre-4.1 client which is apparently compiled into your web server.