Problem With MySQL "Strict" Mode

Just added this to the MySQL modes in my.ini

ONLY_FULL_GROUP_BY

Every query with an aggretate function or a GROUP BY seems to be falling foul of it. An example of a query that falls foul of it:

SELECT
					  f.name
					, f.description
					, COALESCE(lp.last_post,0) AS last_post_thread
					, COALESCE(lp.last_poster,'No Posts Found') AS last_poster
					, COALESCE(lp.last_post_time,'No Posts Found') AS last_post_time
					, COALESCE(lp.post_count,0) AS post_count
					, COALESCE(t_count.thread_count,0) AS thread_count
					, COALESCE(t_count.t_title,'Forum Empty') AS t_title
					, COALESCE(p_count.post_count,0) AS post_count
				FROM
					ue_forum AS f
				LEFT OUTER JOIN
					ue_forum_thread AS t
						ON f.id = t.forum_id
				LEFT OUTER JOIN
					ue_forum_post AS p
						ON f.id = p.thread_id
				LEFT OUTER JOIN
					(
						SELECT
							  MAX(post.id) AS last_post
							, COUNT(post.id) AS post_count
							, post.posted AS last_post_time
							, u.name AS last_poster
						FROM
							ue_forum_thread AS thread
						LEFT OUTER JOIN
							ue_forum_post AS post
								ON thread.id = post.thread_id
						LEFT OUTER JOIN
							ue_user AS u
								ON u.id = post.poster
					) AS lp
						ON lp.last_post = t.id
 				LEFT OUTER JOIN
					(
						SELECT
							  COALESCE(COUNT(t.id),0) AS thread_count
							, t.forum_id AS f_id
							, t.title AS t_title
						FROM
							ue_forum AS f
						LEFT OUTER JOIN
							ue_forum_thread AS t
								ON f.id = t.forum_id
					) AS t_count
						ON f.id = t_count.f_id
 				LEFT OUTER JOIN
					(
						SELECT
							  COALESCE(COUNT(p.id),0) AS post_count
						FROM
							ue_forum AS f
						LEFT OUTER JOIN
							ue_forum_thread AS t
								ON f.id = t.forum_id
						LEFT OUTER JOIN
							ue_forum_post AS p
								ON t.id = p.thread_id
					) AS p_count
						ON f.id = t_count.f_id
				GROUP BY
					f.id

That’s giving me a MySQL error:

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ‘universal_empires.t.forum_id’; this is incompatible with sql_mode=only_full_group_by

Where should I be starting with fixing this? I’ve got to assume that whatever MySQL server the live site eventually goes on has the MySQL set for maximum strictness

Your query is large and most probably truncated at the beginning so I will not go into its specifics but to me this looks like the same problem experienced by those migrating from mysql to stricter databases like postgres. The SQL standard requires that when you group then you must use an aggregate function for each column you select but don’t use in the GROUP BY clause.

Let me show a most simple example:

SELECT id, name, code FROM parts
    GROUP BY name;

// Postgres says:
// ERROR:  column "parts.id" must appear in the GROUP BY clause
// or be used in an aggregate function

This is to prevent unpredictable randomness. When you group by name then there may be multiple items with the same name and then which id and code should be selected? This is undefined, therefore forbidden in strict sql.

To make it work you need to do something like this:

SELECT MAX(id), name, MIN(code) FROM parts
    GROUP BY name;

The only exception from the rule is when you order by the PK:

SELECT id, name, code FROM parts
    GROUP BY id;

because id is unique for each row and there’s no room for unpredictability. This exception is allowed in Postgres, however I don’t know if it’s part of the standard or whether it’s allowed in MySQL strict mode.

STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER

That’s my current SQL Mode string, is there anything that I can add to make it even stricter?

Does it make any difference if a field is a FK?

nope

nope

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.