SQL Query problem in phpMyAdmin and SQLBuddy

Question

I use phpMyAdmin on my web server, which has VirtualMin installed and PHP 7.3 (I've also tried previous versions).

The problem I have, is phpMyAdmin would give an error when running larger (not massive) queries. For example, a table with 3 rows, running select * from tbl1 GROUP BY SUBSTRING(extension,1,5); would give this error:

Error in processing request
Error code: 403
Error text: error (rejected)
It seems that the connection to server has been lost. Please check your network connectivity and server status.

So I had a read online, and people said to use SQLBuddy instead, so I have tried this, and exactly the same query gives this error

There was an error receiving data from the server.

Just a sidenote: this query is most likely incorrect for SQL Standard standpoint(unless column: extension is PK/UQ). Related: [Group by clause in mySQL](https://stackoverflow.com/a/33629201/5070879) — Lukasz Szozda, Dec 27 '20 at 18:08
Please provide `SHOW CREATE TABLE tbl1` and the 3 rows of the table. — Rick James, Dec 28 '20 at 06:22
And, what version of MySQL? Technically speaking, the `GROUP BY` is ill-formed. Read about "only full group by". — Rick James, Dec 28 '20 at 06:24
You can try with [MySQL Workbench](https://dev.mysql.com/downloads/workbench/) where we won't get issues like timeout, size, etc. — Ejilarasan J, Dec 29 '20 at 04:20
when you write `SUBSTRING` query be careful that there are no value length less than the index you provided `5`, try this `SELECT * FROM tbl1 where LENGTH(extension) < 5`, If there are result you should update your SUBSTRING length. — Luay AL-Assadi, Jan 03 '21 at 08:06
@EjilarasanJ Workbench works okay, as does the Virtualmin interface but I like using phpMyAdmin — charlie, Jan 03 '21 at 14:49
@charlie All the options are available + additional options are in Workbench also. Instead of UI make use of the features. — Ejilarasan J, Jan 04 '21 at 10:39

sartajahmed · Answer 1 · 2020-12-31T11:04:48.540

1

You can try below

Change address in your browser from "localhost" to "127.0.0.1".
Increase post_max_size / memory_limit in php.ini file.

edited Dec 31 '20 at 11:04

answered Dec 30 '20 at 09:40

sartajahmed

79
9

i increased `post_max_size` to 200M but still nothing – charlie Dec 31 '20 at 10:02
i'm not using localhost, it's hosted on a cloud server – charlie Dec 31 '20 at 10:03
also, it works fine via Virtualmin which the server runs – charlie Dec 31 '20 at 10:03
Have you increase memory_limit in php.ini – sartajahmed Dec 31 '20 at 11:04
Maximum memory allocation was increased to 200M too for testing it – charlie Dec 31 '20 at 16:17

score 0 · Answer 2 · answered Jan 03 '21 at 15:13

0

the problem is most likely that you try a select * with a group by:

can you try to change it to this:

'select SUBSTRING(extension,1,5) as test from tbl1 GROUP BY SUBSTRING(extension,1,5);'

answered Jan 03 '21 at 15:13

Sam

21
3

score 0 · Answer 3 · answered Jan 03 '21 at 15:56

The context really is probably your query of "*", all fields. "Group by" is more used for aggregation purposes: min(), max(), avg(), etc. You have no context to what you are trying to get an aggregation for, but grouping by the substring of an extension. If you could supply more details to what your data content is, and why you think GROUP BY is it vs DISTINCT, might provide better answers.

Now, that said, and not knowing any more of the data, I would start by doing a simple count(*) which returns 1 for any record qualified. May be just as simple as that.

select 
      SUBSTRING(extension,1,5),
      count(*) NumRecordsWithThisExtension
   from 
      tbl1 
   GROUP BY 
      SUBSTRING(extension,1,5);

SQL Query problem in phpMyAdmin and SQLBuddy

3 Answers3