Mysql query syntax error on Windows, works on Linux

Question

I have problem with query in mysql. I'm writing web application in Laravel and needed to write raw query for my data. The query is

WITH sorted_messages AS (
        SELECT 
            IF(user_from = 3, user_to, user_from) AS other_user, 
            content, 
            created_at,
            ROW_NUMBER() OVER (PARTITION BY other_user ORDER BY created_at DESC) AS rn
        FROM messages AS m
        WHERE user_from = 3 OR user_to = 3
    )
    SELECT 
        name, 
        p.image_url as image_url, 
        users.id, 
        content, 
        sm.created_at
    FROM 
        users LEFT JOIN sorted_messages AS sm ON users.id = other_user
        LEFT JOIN profiles AS p ON users.id = p.user_id
    WHERE (rn = 1 OR rn IS NULL) AND users.id != 3
    ORDER BY created_at DESC
    LIMIT 10

I made sure mysql ONLY_FULL_GROUP_BY mode is disabled. I changed my config/database.php to have this under 'mysql':

'modes' => [
                "STRICT_TRANS_TABLES",
                "NO_ZERO_IN_DATE",
                "NO_ZERO_DATE",
                "ERROR_FOR_DIVISION_BY_ZERO",
                "NO_AUTO_CREATE_USER",
                "NO_ENGINE_SUBSTITUTION",
            ],

When I run this on Linux it works, but on Windows it doesn't. Response is

Illuminate\Database\QueryException: SQLSTATE[42000]: 
Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right
syntax to use near &#039;sorted_messages AS (

I tried manually disabling ONLY_FULL_GROUP_BY mode in mysql settings, but it didn't help.

Do your Window and Linux machines have the same version of Mysql? Mysql only support `WITH` command from version 8.0: https://stackoverflow.com/questions/56961981/mysql-query-syntax-error-on-windows-works-on-linux — catcon, Jul 10 '19 at 00:41
That seems to be the problem. Version shipping with WAMP is 5.7.x. Do you have any suggestion on how to write it to be compatible with both versions? — Mike, Jul 10 '19 at 00:47
The link I provided also suggest some solution, please refer to that post to find your answer. — catcon, Jul 10 '19 at 00:49
Oops, my bad: https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — catcon, Jul 10 '19 at 01:00
I think I solved it using temporary tables. Thank you for pointing out that version of ```mysql``` was the problem — Mike, Jul 10 '19 at 01:01

Mysql query syntax error on Windows, works on Linux

0 Answers0