From Newsgroup: comp.databases.mysql

$ 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.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql


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.
--

//Aho
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 8/21/2021 7:57 AM, J.O. Aho wrote:

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

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.

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.

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.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql


On 21/08/2021 17.48, DFS wrote:

On 8/21/2021 7:57 AM, J.O. Aho wrote:

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

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.

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.
--

//Aho
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 21-8-2021 02:32, DFS wrote:

$ 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.-a 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
-a-a-a -> (ORDER BY COUNT(POSTID) DESC, POSTERNAME) ROWNUM,
-a-a-a -> P3.POSTERNAME, COUNT(DISTINCT(P.POSTERID)) AS NYMS,
-a-a-a -> COUNT(POSTID) AS POSTS, COUNT(DISTINCT(UTCDATE)) AS DAYS_POSTED,
-a-a-a -> COUNT(POSTID) / COUNT(DISTINCT(UTCDATE)) AS POSTSPERDAY,
-a-a-a -> MAX(UTCDATE) AS LASTPOST
-a-a-a -> FROM POST P, POSTER P2, POSTERNAME P3
-a-a-a -> WHERE P.POSTERID = P2.POSTERID
-a-a-a -> AND P3.POSTERNAMEID = P2.POSTERNAMEID
-a-a-a -> AND P.GROUPID = 1
-a-a-a -> AND P3.POSTERNAMEID NOT IN (141,381,388)
-a-a-a -> AND P.UTCDATE >= '2003-07-01'
-a-a-a -> 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';`)


--- Synchronet 3.21d-Linux NewsLink 1.2