Posts filed under 'MySQL'
At long last, Adobe has released a hotfix for a number of serious issues affecting the PHP MySQL server model in Dreamweaver 8.0.2. Unfortunately, you can’t just go to the Adobe site and download it. You need to contact Adobe support and ask for it. Officially, the reason is because Adobe wants to know how many people have been affected by the problems that it solves, and it wants to check that the solution “works for them”.
Anybody using my book Foundation PHP for Dreamweaver 8 should get the hotfix pronto, as it solves two problems affecting Chapters 9 and 12:
- Without the hotfix, Dreamweaver 8.0.2 strips CONCAT() out of SQL queries.
- Changes in the way PHP 5.1.4 and above handles XML resulted in the XSL Transformatiion server behavior reporting incorrectly that the source was not a valid XML document.
The hotfix also solves a couple of other important problems, so if you have Dreamweaver 8.0.2 or have been putting off updating to 8.0.2, I strongly urge you to get in touch with Adobe support and get the hotfix. You can find full details in this technote on the Adobe site.
August 28th, 2006
When the Dreamweaver 8.0.2 updater came out, I originally reported that it mishandles the MySQL DATE_FORMAT() function in the Test SQL Statement panel. After further testing, and some help from Alexandru Costin of InterAKT, I’ve discovered the problem was with the dates in a test table that I was using. Oops!
The dates had been entered incorrectly into the test table, so the actual values were 0000-00-00 00:00:00. No wonder that DATE_FORMAT() wasn’t able to convert them. However, the nasty problem with Dreamweaver 8.0.2 corrupting SQL queries that use CONCAT() has been confirmed by several people. Let’s hope it gets fixed soon.
May 14th, 2006
I love phpMyAdmin as a quick, easy-to-learn front-end for MySQL, but it’s just broken a cardinal rule of updates by making two changes to the installation procedure in the space of three months. In principle, the changes are well-intentioned. Instead of diving into an 800-odd line configuration file, users now have the choice of creating a simple text file themselves or using a web interface to build the configuration file automatically. The idea is that whenever you upgrade, you simply move your personal configuration file to the root folder, and everything works smoothly. It doesn’t…
The new system was introduced in version 2.7.0, but if you move the configuration file, config.inc.php, from 2.7.0 to 2.8.0, it doesn’t work! What’s particularly confusing is that MySQL reports that access was refused because the root password wasn’t used. After a bit of head scratching, I tried the new web interface to create config.inc.php for me. To my mind, the web interface is badly thought out. It’s counter-intuitive and time-consuming to use. Still, using the web interface did at least create a configuration file that worked - and that was the point of the exercise.
Looking inside to see the difference, it turns out that phpMyAdmin now uses an index of 1 instead of 0 to identify the primary server. I’ve no idea if this is a permanent change or just a mistake, but it certainly defeats the purpose of a configuration file intended to make upgrades easier if it breaks on a simple point upgrade.
I’ve updated my updates for Foundation PHP for Dreamweaver 8 and Foundation PHP 5 for Flash. The really annoying thing is that three pages were reset in the second printing of the Dreamweaver book to take account of the changes in phpMyAdmin 2.7.0. These latest changes now make the updated version already out of date. I don’t mind changes like this when they’re a clear improvement, but changing the installation procedure in 2.7.0 and breaking it in the very next release, just seems like sloppy management at phpMyAdmin
March 13th, 2006
Simon Collison does such a good sales patter for ExpressionEngine in Blog Design Solutions that I decided to try it out. Things looked even brighter than his description when I disovered that not only has the price for the non-commercial licence has gone down from $149 to $99, but there’s now also a “Core” version, which is a free, unlimited trial. The free version lacks a lot of features, but it’s a good way to give ExpressionEngine a test drive without needing to worry about how many days you’ve got left to make up your mind whether to buy the paid-for version.
My enthusiasm took a sharp turn for the worse when the installation process failed at the last fence. I kept on getting the following message:
Error: Unable to perform the SQL queries needed to install this program. Please make sure your MySQL account has the proper GRANT privileges: CREATE, DROP, ALTER, INSERT, and DELETE.
I knew, however, that my account had all those privileges - and more.Eventually, I dug into the code of the installation file, search for the error message, and did a little debugging to find out what lay behind this unexplained failure. I got the page to display the problem SQL query, and then ran it directly in MySQL Monitor. All became clear: the primary key of each table is set to auto_increment, and the ExpressionEngine installation script attempts to insert an empty string into the primary key field. In older versions of MySQL, this works just fine, as the empty string is replaced by the next available number. In MySQL 5.0 running in SQL Strict Mode, though, this represents an out-of-range number, so the whole process comes to a grinding halt.
The solution was simple: switch off SQL Strict Mode in MySQL. Although it didn’t take me all that long to work this out, I suspect it’s going to catch out a lot of MySQL beginners. SQL Strict Mode is now turned on by default in the Windows Essentials version of MySQL 5.0, and ExpressionEngine isn’t the only mainstream script to use an empty string for the primary key in INSERT commands. It’s also used by phpMyAdmin.
So, for the time being, Windows Essentials users of MySQL should deselect the SQL Strict Mode option in the MySQL Configuration Wizard. A better solution, though, would be for everyone to start writing SQL commands that adhere to Strict Mode.
February 26th, 2006