Mysql: Can't find any matching row in the user table

Submitted by sjorsvdp on Wed, 01/13/2016 - 07:13

Among others, virtualmin has been updated. Since then, an error occurs when creating new mysql users:
Error Code: 1133. Can't find any matching row in the user table

Setup:
Virtualmin is running on our web server (web01), Mysql is running on our database server (db01). Virtualmin connects with a non-root user to db01 (based on the hostname).

When a user is created the following queries are executed:
insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('web01.****', '****', '', '', '', '')
flush privileges
set password for 'futurefitplatfor'@'web01.redknot.nl' = password('****')

If I copy these queries to phpmyadmin (on web01), the same error occurs. When I change the virtualmin code to use the create user syntax, everything works as expected:
create user '****'@'web01.****'
set password for 'futurefitplatfor'@'web01.redknot.nl' = password('****')

Regardless if this is an issue at mysql or virtualmin, maybe create user should be used in stead of inserting directly in the user table.

Status: 
Active

Comments

Submitted by andreychek on Wed, 01/13/2016 - 09:49

Howdy -- what distro/version are you using there? And which MySQL version is installed?

Submitted by Marcin on Mon, 01/25/2016 - 03:30

I have the same problem when I try to create new virtual server. Virtualmin is running on web server (web1), Mysql is running on database server (db1). Virtualmin connects with a root user to db1 (based on the IP). Webmin version: 1.782 Operating system: CentOS Linux 6.7 MySQL: 5.1.73

Creating MySQL login .. .. MySQL database failed! : SQL set password for 'xx'@'xxx.vps.webfusion.co.uk' = password('xxx') failed : Can't find any matching row in the user table at ../web-lib-funcs.pl line 1397.

Now I get virutal server without MySQL feature. The same error occurs when I try to do this directly on (db1). Everything worked correctly before an update.

@sjorsvdp: Could you write what changes you done manually in virtualmin code?

Submitted by JamieCameron on Mon, 01/25/2016 - 18:21

For anyone who is seeing this error - can you post the output of the SQL query :

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

where XXX is the problem username.

Submitted by Marcin on Tue, 02/02/2016 - 07:04

From my: (db1)
mysql> select user,host from mysql.user where user = "fitf";
+------+-------------------------------+
| user | host |
+------+-------------------------------+
| fitf | x.x.x.x |
| fitf | y.y.y.y |
| fitf | web-server-2.mydomain.com |
+------+-------------------------------+
3 rows in set (0.01 sec)

where:
x.x.x.x - private IP address of my webserver: (web1)
y.y.y.y - public IP address of my webserver: (web1)
web-server-2.mydomain.com - hostname of my webserver: (web1) - this is problematic record added automatically from my virtualmin on (web1) even when I have set up:

Allowed MySQL client hosts: x.x.x.x y.y.y.y
in: System Settings > Server templates > template > MySQL database form.

Submitted by JamieCameron on Tue, 02/02/2016 - 21:16

Marcin - and what is the exact error you get in Virtualmin when changing a password?

Submitted by Marcin on Fri, 02/05/2016 - 09:45

When I try to change password I get: #1133 - Can't find any matching row in the user table. It only applies to the problematic entry (with hostname). For host with IP address there is no problem. Problem is that Virualmin each time wants to create this entry whereby my virual server is created without MySQL feature.

Submitted by JamieCameron on Fri, 02/05/2016 - 19:52

Odd that Virtualmin is trying to change the MySQL password on a domain without MySQL enabled.

If you click on Edit Virtual Server on the left menu, is the MySQL feature checked in the "Enabled features" section?

Alexandro's picture
Submitted by Alexandro on Mon, 02/15/2016 - 06:28

I'm having the same issue and investigated a little, it seems, after one of the latest updates (in my case it's a cento's box with installed virtualmin 1.782) when i create a new website "WITH" mysql:

1) it doesn't create the database
2) it doesn't flag the website "having" the database, probably due to the "failed" database creation
3) though it creates correctly the database user accounts failing to grant them permissions to the previously "non created" database.

i end up with a website without database on the webserver, and user accounts without database in the database server.

on the other box, ubuntu with same release of virtualmin (1.782) all works correctly.
A.

Submitted by JamieCameron on Tue, 02/16/2016 - 00:16

Alexandro - which mysql versions are on the system with the problem vs. the system that works OK?

Alexandro's picture
Submitted by Alexandro on Fri, 02/19/2016 - 08:43

@JamieCameron, the virtualmin that can't create the db, works with a

[root@vm-plt-mysql01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

While the one working correctly is

root@vm-plt-weblx04:~# mysql -V
mysql  Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3

Submitted by JamieCameron on Sat, 02/20/2016 - 00:36

Is that MySQL 5.1.73 version the standard package supplied with CentOS 6?

Alexandro's picture
Submitted by Alexandro on Wed, 02/24/2016 - 07:34

yes in order to not have any update issue, i prefer using repo packaged softwares.

The issue started right after virtualmin package update, when new themed interface was released. Before that, when it was simple html based, all worked properly, after i agreed to use the new cool looking interface, i have been notified of virtualmin update, and it broken mysql database creation (only on the centos box, on the ubuntu box it kept working like a charm)

Submitted by zignut on Thu, 02/25/2016 - 07:05 Pro Licensee

I entered a similar issue at https://www.virtualmin.com/node/39794 . I initially believed it due to sql_mode = NO_AUTO_CREATE_USER, but that proved incorrect. FLUSH PRIVILEGES also does not rectify it. The only "real" way was to modify /usr/libexec/webmin/virtual-server/feature-mysql.pl to do CREATE USER x@y identified by 'staticpassword', flush privileges instead of the insert into user...

Submitted by sjorsvdp on Thu, 03/31/2016 - 06:47

I'm sorry for the long delay in my reply. I didn't receive any notifications (how do I subscribe) and the issue got out of my scope. Until now, I think webmin/virtualmin was updated.

"what distro/version are you using there? And which MySQL version is installed?"
Ubuntu 14.04 LTS
mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3

"Could you write what changes you done manually in virtualmin code?"
/usr/share/webmin/virtual-server/feature-mysql.pl (line 2580)
sub get_user_creation_sql
{
my ($host, $user, $encpass) = @_;
return ("create user '$user'\@'$host'", "set password for '$user'\@'$host' = $encpass"); // <--- added this line
if ($mysql::mysql_version >= 5) {

/usr/share/webmin/virtual-server/feature-mysql.pl (line 2598)
sub execute_password_change_sql
{
my ($user, $encpass) = @_;
my $d = &mysql::execute_sql($mysql::master_db,
"select host from user where user = ?", $user);
foreach my $host (&unique(map { $_->[0] } @{$d->{'data'}})) {
# my $sql = "set password for '$user'\@'$host' = $encpass"; // <--- disabled this line
my $sql = "UPDATE mysql.user SET Password=$encpass WHERE User='$user' AND Host='$host';"; // <--- added this line

Submitted by JamieCameron on Thu, 03/31/2016 - 22:41

Was this the MySQL package that came with Ubuntu 14, or a custom install from another source?

Submitted by sjorsvdp on Fri, 05/27/2016 - 05:34

"Was this the MySQL package that came with Ubuntu 14, or a custom install from another source?" The one that came with Ubuntu

Submitted by JamieCameron on Fri, 05/27/2016 - 15:59

Do you have the skip-name-resolve option enabled in MySQL? Another user who reported this problem found that it was triggered by that option.

Submitted by sjorsvdp on Wed, 06/01/2016 - 05:16

Yes, that option is enabled

Submitted by JamieCameron on Wed, 06/01/2016 - 23:05

I recommend turning it off - it causes mysql to behave in a way that is hard for Virtualmin to manage.

Submitted by sjorsvdp on Mon, 08/29/2016 - 13:26

I have disabled the option skip-name-resolve (restarted mysql) and changed the templates by editing the files in /etc/webmin/virtual-server/templates/ (removed the web01.*. entry) and restarted webmin.

When I create a new virtual server, an entry for user@hostname is still made. Why? What can I do to fix this?

Submitted by JamieCameron on Mon, 08/29/2016 - 21:35

sjorsvdp - are you still getting the error about not being able to find any matching row ?

Submitted by sjorsvdp on Tue, 08/30/2016 - 02:55

Yes I do

Which is odd, because I think I've removed all settings to create a user based on the hostname

Submitted by JamieCameron on Wed, 08/31/2016 - 00:09

Any chance we could login to your system to see what's going wrong here?

Submitted by sjorsvdp on Wed, 08/31/2016 - 02:42

I'm sorry, but that's not an option. Can I provide some log files or config files?

Submitted by JamieCameron on Wed, 08/31/2016 - 22:58

Can you post the exact error message you're getting, and the output from the SQL command select user,host from mysql.user where user = 'xxx' (where xxx is the problem user)

Submitted by sjorsvdp on Thu, 09/01/2016 - 06:40

For some reason I don't get any errors now. The user is created though (also with hostname) user host
test3..nl 10.0.50.35 test3..nl 127.0.1.1 test3..nl web01..nl

Submitted by JamieCameron on Fri, 09/02/2016 - 00:31

Ok, that's what I'd expect