Can't find any matching row in the user table

Submitted by zignut on Wed, 02/24/2016 - 22:51 Pro Licensee

set password for 'user'@'host' = password('xxxxxx') failed : Can't find any matching row in the user table at /usr/libexec/webmin/web-lib-funcs.pl line 1397

I get the above error when attempting to add mysql to a virtual server. It's new, and I believe it's related to running mariadb 10.1. Based on an issue here https://github.com/letsencrypt/boulder/issues/1322 it makes sense, however setting the sql_mode does not solve it. Testing, if I use CREATE USER instead of inserting direct to mysql.user, set password succeeds as desired. CREATE USER has the added benefit of replicating correctly through the galera cluster as well. Is there a need to use insert/deletes to the mysql db without using the ddls? Server version: 10.1.11-MariaDB-log MariaDB Server

Status: 
Active

Comments

Submitted by andreychek on Thu, 02/25/2016 - 09:11

Howdy -- Yeah I do suspect you're seeing a compatibility issue with that particular software.

We unfortunately don't get much testing in on MySQL/MariaDB versions that aren't included in our supported distros, so it is possible to run into some issues with those. But we'll certainly work on getting that fixed up!

One thing Jamie has been collecting for folks seeing issues like the above, could you paste in the output of this MySQL query:

select user,host from mysql.user where user = "XXX"

Submitted by zignut on Thu, 02/25/2016 - 11:38 Pro Licensee

I'll add a test domain and get that info shortly.

Submitted by pcmerc on Fri, 03/04/2016 - 21:54

Having the same issues across all my virtualmin GPL installs.
Using mysql on a remote system.
This was all working fine prior to the last updates.
I have manually created the user & database.
How do I enable the "Edit Databases" option for a domain manually as backups won't dump the database.
I've been picking through the configs & enabled everything I could find related to a domain as well as creating the domain acl in /etc/webmin/mysql & the profile.
What am I missing?
The domain has the mysql feature checked/enabled as well but the Edit Databases is not visible for it.
This is NOT a compatibility issue. I have different versions of mysql 5.5.x as well as lower versions & I'm seeing the same bug across them all.

Submitted by JamieCameron on Sat, 03/05/2016 - 19:23

Can anyone who is seeing this run the test MySQL command select user,host from mysql.user where user = "XXX" (replace XXX with the actual user) and post the output here?

Submitted by zignut on Wed, 05/11/2016 - 08:11 Pro Licensee

Circling back around for this issue..

MariaDB [(none)]> select user,host from mysql.user where user = "emergencyservice";
+------------------+--------------------------------+
| user             | host                           |
+------------------+--------------------------------+
| emergencyservice | 10.10.20.%                     |
| emergencyservice | oarsman.cloud.mediamanaged.net |
+------------------+--------------------------------+
2 rows in set (0.01 sec)

Submitted by zignut on Wed, 05/11/2016 - 08:48 Pro Licensee

Accompanying error from the logs.

2016-05-11  9:34:52 140453474048768 [ERROR] Slave SQL: Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'SET PASSWORD FOR 'emergencyservice'@'oarsman.cloud.mediamanaged.net'='*xxxxxxxxxxxxxxxxxxxxxxxxxx'', Internal MariaDB error code: 1133

Submitted by zignut on Wed, 05/11/2016 - 08:53 Pro Licensee

phpmyadmin tells me, while the user is there, that it has no password: name, host, password, global privileges, grant emergencyservice,10.10.20.%,Yes,USAGE,No
emergencyservice,oarsman.cloud.mediamanaged.net,No,USAGE,No

Submitted by zignut on Wed, 05/11/2016 - 09:48 Pro Licensee

Jamie, FWIW If I modify the create db function to use CREATE USER syntax, instead of the direct INSERT, everything seems to work correctly. In /usr/libexec/webmin/virtual-server/feature-mysql.pl

2569c2569,2570
<       return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "set password for '$user'\@'$host' = $encpass");
---
> #     return ("insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('$host', '$user', '', '', '', '')", "flush privileges", "set password for '$user'\@'$host' = $encpass");
>       return ("create user '$user'\@'$host' identified by password $encpass","flush privileges");

EDIT: creation works, but not sure the password gets set correctly, and a password change still gives the same error.

Submitted by JamieCameron on Wed, 05/11/2016 - 23:55

That's a better way of creating a user actually - I'll do this in the next release.