17 Dec 2005

Lately i’ve been developing with MySQL3, and it’s almost like hitting your head against a brick wall with its limitations and specific documentation is difficult to find.

Some of the limitations:

  • Subqueries. ’select id from t where grp in (select grp from g where u > 100)’
  • Add DISTINCT to Aggregates
  • JOINs with ambiguous columns causes a MySQL error.
  • Foreign Key Definition Ignored on MyISAM.
    • In MySQL Server 3.23.44 and up, the InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section, “FOREIGN KEY Constraints”.

      For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well.

  • Function CASE
  • Add a handler interface for table information so one can use it as a system table. This would be a bit slow if one requested information about all tables, but very flexible. One should also implement ’show info from table’ for basic table information.
  • Reordering columns. E.g. ALTER TABLE FormObjects MODIFY COLUMN FormFieldRuleID INT(11) AFTER FOLID;
  • JOINS on UPDATE’s.

Other limitations

  • Don’t add automatic DEFAULT values to columns. Give an error when using an INSERT that doesn’t contain a column that doesn’t have an DEFAULT.
  • Multiple SQL in a query commands

fairly recently started a new position, and have come from developing on all the latest versions to developing with MySQL 3, and PHP 4, which is quite a step back from MySQL 5, (PHP 5.0.4) or even MySQL 4. In general I take many aspects of MySQLs features as being rather trivial and it makes my job that much more difficult to discover why there is syntax errors, which is valid in later versions of MySQL.

As to why MySQL 3 is still being used is due to support an application.

Comment Form

About this blog

I have been a developer for roughly 10 years and have worked with an extensive range of technologies. Whilst working for relatively small companies, I have worked with all aspects of the development life cycle, which has given me a broad and in-depth experience.