On a few sites I run I have vBulletin and / or Photopost installed. Upgrading from MySQL 4 to MySQL 5 is simple because these programs are 100% compatible with both (as they should be). Except for one issue. When installing MySQL 5 it defaults to asking to be installed in “Strict Mode” which is how a database should work. Strict Mode behaves in a way that when a malformed statement is entered into the database, it aborts the operation. This is great for helping to provide a structure and predictability to the data in the database. However, some scripts are not programmed in a way that would allow it to work well with strict mode. In vBulletin, this error will show up:
I would disable strict mode if possible over forcing vBulletin to use it, as some add-ons don’t play well with it.
If you are using the MySQL Administrator, go to startup variables -> advanced -> sql mode and note the options in the field. If there are no options stated, you are NOT in strict mode. If you see any or all of the following, the database is operating in strict mode:
STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
Just remove all 3 statements to disable strict mode. If your server has been operating in strict mode previously and scripts have been designed around it, be sure you can disable it without any adverse effects.
Here is an explanation from the MySQL manual on the above options:
If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.
Prevents GRANT from automatically creating new users unless a non-empty password is specified.
Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.
With NO_ENGINE_SUBSTITUTION disabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.