SQLSTATE[42000]: Syntax error or access violation: 1055

In one of my Laravel projects, I recently encountered an issue related to SQL syntax error or access violation “SQLSTATE[42000]: Syntax error or access violation: 1055 ‘laravel_app.tests.title’ isn’t in GROUP BY“. This issue appeared in the Laravel application which is based on online tests that can be attempted by users. On an admin page, we were displaying the list of tests added by an admin in a table form. For each test, we were showing fields

• non-aggregted fields such as title, number of questions to attempt
• aggregated field like number of questions exist in table for each test

 Naturally, the aggregated fields are part of the GROUP BY clause for the test_id column, which is the main reference for each test.

The issue surfaced within a controller action where I was using a LEFT JOIN to pull in data from the questions table for each test. Here’s the code that caused the error:

class TestController extends Controller
{
   function getListOfTests(Request $request) {
       $testsData = Test::leftJoin('questions', 'tests.id', '=', 'questions.test_id')
           ->select('tests.id', 'tests.title', 'tests.description', 'tests.ready_for_publish', 'tests.no_of_ques_to_attempt', 'tests.updated_at', DB::raw('COUNT(questions.id) as num_questions'))
           ->groupBy('tests.id')
           ->orderBy('updated_at')
           ->get();

       return view('manage_tests', ['tests' => $testsData]);
   }
}

In this query, we were selecting several columns, including:

• id, title, description, ready_for_publish
• no_of_ques_to_attempt, updated_at
• An aggregated value of num_questions, using COUNT(questions.id)

However, despite the inclusion of the COUNT() function, MySQL flagged an issue with the query execution, highlighting that the column tests.title was not included in the GROUP BY clause.

This error message is indicative of MySQL’s strict SQL mode, specifically the ONLY_FULL_GROUP_BY mode. When this mode is enabled, MySQL enforces strict rules on GROUP BY queries. It ensures that every column in the SELECT clause that is not aggregated must be included in the GROUP BY clause. The error occurs because in our query, only the tests.id column was included in the GROUP BY clause, but the other selected columns, like tests.title, were not.

Essentially, the ONLY_FULL_GROUP_BY mode requires that all non-aggregated columns from the SELECT statement be included in the GROUP BY clause. In the case of our query, this rule was causing the failure, as tests.title and other columns were neither aggregated nor grouped.

Solution

To address this, I first checked the SQL mode settings in my MySQL configuration file (my.ini in XAMPP). Surprisingly, the ONLY_FULL_GROUP_BY mode was not present there.

If ONLY_FULL_GROUP_BY  mode exists in your system, you can refer the link to resolve the ONLY_FULL_GROUP_BY issue in mysql.

Further research led me to understand that Laravel has its own SQL mode settings. To modify these settings, I navigated to the config/database.php file and located the MySQL configuration section. Here, I added the necessary SQL mode settings:

    'mysql' => [
        'driver' => 'mysql',
        // Other configurations...
        'modes' => [
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION'
        ],
    ],

By adding these modes, I was able to resolve the issue, and the joins in my queries began functioning correctly.

This experience highlights the importance of understanding database configurations and how they can affect Laravel projects. By appropriately configuring SQL modes, we can ensure smooth operation and avoid common SQL errors.