MariaDB on Windows: SELECT query with ROW_NUMBER() kills the MariaDB 10.6.4 service

[DFS]

$ net start MariaDB
The MariaDB service is starting.......
The MariaDB service was started successfully.


$ mariadb -u DFS -p
Enter password: *******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.4-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.


MariaDB [(none)]> use usenet;
Database changed
MariaDB [usenet]> SELECT ROW_NUMBER() OVER
-> (ORDER BY COUNT(POSTID) DESC, POSTERNAME) ROWNUM,
-> P3.POSTERNAME, COUNT(DISTINCT(P.POSTERID)) AS NYMS,
-> COUNT(POSTID) AS POSTS, COUNT(DISTINCT(UTCDATE)) AS DAYS_POSTED,
-> COUNT(POSTID) / COUNT(DISTINCT(UTCDATE)) AS POSTSPERDAY,
-> MAX(UTCDATE) AS LASTPOST
-> FROM POST P, POSTER P2, POSTERNAME P3
-> WHERE P.POSTERID = P2.POSTERID
-> AND P3.POSTERNAMEID = P2.POSTERNAMEID
-> AND P.GROUPID = 1
-> AND P3.POSTERNAMEID NOT IN (141,381,388)
-> AND P.UTCDATE >= '2003-07-01'
-> GROUP BY P3.POSTERNAME;
(after 30 seconds)
ERROR 2013 (HY000): Lost connection to server during query

MariaDB [usenet]> show tables;
ERROR 2006 (HY000): Server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to server on 'localhost' (10061)
ERROR: Can't connect to the server

unknown [usenet]>exit
Bye

$ net stop MariaDB
The MariaDB service is not started.

More help is available by typing NET HELPMSG 3521.




Strange!

I tried it with and without the PARTITION BY clause and it crashed the
service either way.

Took out ROW_NUMBER() and it ran fine.

FWIW, the query runs just fine as is in SQLite.

Edit: I found similar bug reports in jira.mariadb.org, but they're all
closed. So I created a new bug report.

[J.O. Aho]

On 21/08/2021 02:32, DFS wrote:

MySQL and MariaDB have always had some issues with the pirate OS.
I would recommend you to use the WSL feature if you can't use a Unix or
Unix like OS.

WSL allows you to run the Linux binaries, far more stable and I would
recommend that for other enterprise tools as redis, kafka, apache,
nginx, and so on.

[DFS]

Post by J.O. Aho
MySQL and MariaDB have always had some issues with the pirate OS.

ALL the 'ROW_NUMBER() crashed my server' reports I found on
jira.mariadb.org occurred on the hobbyist OS.

Post by J.O. Aho
I would recommend you to use the WSL feature if you can't use a Unix or
Unix like OS.
WSL allows you to run the Linux binaries, far more stable

I doubt it.

Post by J.O. Aho
and I would
recommend that for other enterprise tools as redis, kafka, apache,
nginx, and so on.

It's WAMP (Windows Apache MariaDB Python) for me, if I go that route.

[J.O. Aho]

Post by DFS

Post by J.O. Aho
MySQL and MariaDB have always had some issues with the pirate OS.

ALL the 'ROW_NUMBER() crashed my server' reports I found on
jira.mariadb.org occurred on the hobbyist OS.

Didn't find that many MacOS users reporting something, but that may have
to do with few of them run on that OS.

Post by DFS

Post by J.O. Aho
I would recommend you to use the WSL feature if you can't use a Unix
or Unix like OS.
WSL allows you to run the Linux binaries, far more stable

I doubt it.

You won't know until you tried.

[Luuk]

Post by DFS
$ net start MariaDB
The MariaDB service is starting.......
The MariaDB service was started successfully.
$ mariadb -u DFS -p
Enter password: *******
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.4-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
MariaDB [(none)]> use usenet;
Database changed
MariaDB [usenet]> SELECT ROW_NUMBER() OVER
    -> (ORDER BY COUNT(POSTID) DESC, POSTERNAME) ROWNUM,
    -> P3.POSTERNAME, COUNT(DISTINCT(P.POSTERID)) AS NYMS,
    -> COUNT(POSTID) AS POSTS, COUNT(DISTINCT(UTCDATE)) AS DAYS_POSTED,
    -> COUNT(POSTID) / COUNT(DISTINCT(UTCDATE)) AS POSTSPERDAY,
    -> MAX(UTCDATE) AS LASTPOST
    -> FROM POST P, POSTER P2, POSTERNAME P3
    -> WHERE P.POSTERID = P2.POSTERID
    -> AND P3.POSTERNAMEID = P2.POSTERNAMEID
    -> AND P.GROUPID = 1
    -> AND P3.POSTERNAMEID NOT IN (141,381,388)
    -> AND P.UTCDATE >= '2003-07-01'
    -> GROUP BY P3.POSTERNAME;
(after 30 seconds)
ERROR 2013 (HY000): Lost connection to server during query
MariaDB [usenet]> show tables;
ERROR 2006 (HY000): Server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to server on 'localhost' (10061)
ERROR: Can't connect to the server
unknown [usenet]>exit
Bye

Did you check the log file ?

(see: `show variables like 'log_error';`)