Dokeos and MySQL strict SQL mode

We've had reports about Dokeos creating problems when used with MySQL in strict mode. There's a large topic development on the forum about that. The idea is that MySQL can be set to be more strict about the SQL syntax used in queries. Apparently Dokeos doesn't provide a very strict implementation, so SQL errors appear all over when using this mode. As there is no perfect way to counter this setup (the system admin should be the one to do the choice whether or not Dokeos should be installed if it doesn't respect the MySQL's strict SQL mode), here are a pair of methods that you can apply to change this setting if you feel this is appropriate:
  1. use the following command inside your MySQL client or inside the MySQL server's configuration: SET @@global.sql_mode= '';
  2. use @mysql_query("set session sql_mode='';"); every time you find a mysql_connect() function in Dokeos, by putting the line right after this mysql_connect. If mysql_connect returns a result into a variable, you have to use that variable as a second argument to the mysql_query() function call.
It has been suggested that we should add a check for MySQL mode into the installation procedure. However, this check cannot be put in the normal global check page, as at this point we don't have the MySQL settings just yet. It could be added into the check operation made on the MySQL server specifically though...

Comments

I have a question about Mysql: does Dokeos work with InnoDB tables? Innodb creates 1/3 of the files that MyIsam creates and could be a partial solution for too many files in one folder that cripples (my) Dokeos on 1 DB.

Sadly, we have had a very bad experience with InnoDB tables in terms of table corruption. Actually, since we quit using InnoDB, we never had a table corruption problem, but before that we used to have one every two weeks, in average.
So I wouldn't recommend using them (you would also have to change a considerable amount of SQL queries in your Dokeos code to be able to use them).

This being said, Dokeos 2.0, currently under development and scheduled for sometime in the second half of 2009, will only use one database and a fixed number of tables. This should solve the problems we are getting now with huge amounts of files.

As we always said, the single-database mode in Dokeos 1.8 is *not* recommended. We only provide it for little-size campuses that use free hosting solutions (although the multiple-databases mode is not actually good either, it is already far better).