MySQL’s Step To Standards
MySQL AB have recently published the production ready release of MySQL 5.0 that takes a great step towards todays SQL standards by providing advanced features such as stored procedures, triggers, views, and cursors.
Extra goodies have also been introduced into the release such as pluggable store engine types and SQL modes.
This brings MySQL up to the SQL 2003 standards and with the introduction of SQL modes, introduces a default STRICT_TRANS_TABLES setting that further validates the syntax used in queries.
As this is a default setting in new MySQL 5.0 installations, it fails on certain queries existing in the osCommerce 2.2 Milestone 2 release due to the queries not being formed appropriately. This is evident with some queries performing left joins, and when empty values (”") are being inserted into datetime columns.
This can be thought of the same as how PHP provides its error reporting levels. The stricter the setting value is at, the more thorough logic and queries are parsed and validated.
With such error reportings available, it is exciting to see where osCommerce fails as even though the 2.2 Milestone 2 release has been available for years, the thousands and thousands of eyes that have inspected the source code have not picked up on these issues and when fixed, brings osCommerce yet another step towards being standards compliant.
This is good news for 2.2 Milestone 3 as not only will it be compatible to PHP’s E_ALL reporting level, but also MySQL 5.0’s STRICT_TRANS_TABLES SQL mode to polish its stance on standards and security.
An update to the 2.2 Milestone 2 release was also announced today that addresses security issues, bug reports, PHP 5 compatiblity, and MySQL 5 compatibility. The PHP 5 and MySQL 5 compatiblilty updates are minor and should take care of postings in the forums when 2.2 Milestone 2 is installed under such environments.
It is interesting to see what the adoption rate of MySQL 5.0 will be like, especially now that processing logic can be moved away from the web server to the database server, and how this reflects the web hosting packages/servers out there.