Unable to use phpmyadmin to search "users" table.

[jols]

Not quite sure what to make of this. We've got a customer on one an older server who is unable to use the cPanel installed, phpmyadmin utility to search their "users" table. Keeps returning:

" MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0075 sec )"

But this same customer also has another account on a newer cPanel server where there is no such issue, i.e. the searches work just fine.

Any advice?

 

[jols]

Update - Looks like I've got the exact same problem as this person:

SourceForge.net: phpMyAdmin: MySQL returned an empty result set

I'm totally perplexed by this.

 

[Gleem]

Having the same problem. Were you able to find any solution?

 

[jols]

No, regretfully no. I've searched all over too.

At first I thought it was a permissions issue, but there is an issue with trying this from both using phpMyAdmin within cPanel (by the individual hosted account member) and WHM (by the server admin), so I really don't think this is permissions related, but rather possibly some kind of compatibility issue between phpMyAdmin and a recent update of the cPanel system.

 

[janipewter]

I have this same problem. What version of PHP do you use?

Mine only started after I downgraded from PHP 5.3 to PHP 5.2 due to compatibility issues with my site on my new server. I have read around the internet and a lot of sites seem to suggest it is related to a variable (max_input_vars) which was implemented in PHP 5.3.

I have not managed to solve it yet, and it's rather annoying. It also affects the users table in my database. I have over 150,000 users and I cannot search the database. If I query it, it works fine. But the search in phpmyadmin does not.

 

[jols]

On the particular server where this problem exists, we are still at 5.2.x. Another server with 5.3.x does not have this issue.

 

[hgrg]

is your mysql server intact? check /var/lib/mysql/[yourhostname].err file..

 

[cfrench]

I'm getting the same exact issue. It only happens on some tables, not all. When I search using the SQL command it works fine. Definitely a bug and an annoying one at that.

 

[Gleem]

Ok, I have only tested this on one table, but the problem appears to be on tables with ENUM fields. After I changed ENUM to SET on one of the tables having problems I was able to get results returned from my search.

 

[cfrench]

Ok, I have only tested this on one table, but the problem appears to be on tables with ENUM fields. After I changed ENUM to SET on one of the tables having problems I was able to get results returned from my search.

It happens for me on INT but only on some tables. It's very strange.

 

[hgrg]

are the engines all the same?

 

[cfrench]

are the engines all the same?

Yes. All are INNODB.

 

[slawn]

Same problem here for the last 3 weeks or so - since the last update. Problem was occurring on all of my servers (5 of them) with different PHP, MySQL ver.

I've searched on the web and the problem definitely exists because I've found it on few forums - but no solution given so far and cPanel staff doesn't seem to pick this up or do anything about it.

Just spent some time investigating and won't be getting into to many details - the problem is connected to tables that have ENUM fields and it's connected to the javascript that is used to build AJAX request. Basically if you have an ENUM field and you won't select it, it is being passed as NULL so simple query which should look like that:

SELECT * FROM test WHERE userid = 1

looks like that (firebug will help you see the response from AJAX request)

SELECT * FROM test WHERE userid = 1 AND enum_field = 'null'

Now how to fix that. Edit this file:

/usr/local/cpanel/base/3rdparty/phpMyAdmin/js/tbl_select.js

and replace this:

if(a["fields["+d+"]"]=="")

with this:

if(a["fields["+d+"]"]=="" || a["fields["+d+"]"]==undefined)

Hope that helps (very annoying bug)!!!

 

[janipewter]

Thanks so much slawn - that fixed the problem!

 

[WhiteDog]

Has this issue been reported to / fixed by phpMyAdmin itself ?
Looks like something they'd want to fix (and not us or cPanel )

 

[slawn]

Has this issue been reported to / fixed by phpMyAdmin itself ?
Looks like something they'd want to fix (and not us or cPanel )

The problem is that phpmyadmin on cPanel is not the latest one. There are no issues with the new one (I use it locally) - the latest ver is 3.5.5 and the one on cPanel is 3.5.3.

 

[jols]

Thanks slawn.

I can also confirm that the edit/fix you recommend has resolved the issue here as well. Thanks once again.

 

[Gleem]

Thanks for this, slawn!

 

[tekoa]

Thanks, slawn. I needed this.