Which SQL mode should I use with MySQL? / What is the recommended SQL mode for MySQL?
My general purpose recommendation is this one:
sql-mode="TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"This "traditional" setting includes most of what you'd want your database to behave like, namely:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USERThis has a nice benefit of returning an error instead of a warning on truncating a varchar column, not let you inserts completely broken date values, and give out division by zero errors. That's pretty traditional as I see it.
The other two settings make sure you don't create InnoDB tables as MyISAM by mistake, and that you include all the columns in the GROUP BY clause, like in other databases.
That's my choice for a reasonable all-around setting - for different applications you might need different ones (see excellent comment by Roland Bouman below).