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

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

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

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?

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.

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.

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

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.

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.

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

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)

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

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

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

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

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.

Yes, that option is enabled

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

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?

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

Yes I do

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

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

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

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)

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

Ok, that's what I'd expect