Problem with MySQL username longer than 16 characters

Submitted by tristanleboss on Fri, 04/01/2011 - 17:33

Hello fellow VirtualMin-users,

I just made a fresh install of VirtualMin 1.534 (now updated to 1.535) on a Debian 6.0.1 x64 system. I use this oportunity to report all the issues I come by.

So this time, one of my customer sent me an email telling me he created a domain name and he couldn't access its associated MySQL database. I quickly traced the problem to the fact that its domain name was more than 16 characters long. Indeed, if you create a virtual server for "more-than-sixteen.com", VirtualMin creates user "more-than-sixteen" but, in MySQL, the username is truncated to "more-than-sixtee" because the maximum length for a MySQL username is 16 characters. But, it was the same with my old Debian 5.0 server.

The change in this fresh new setup comes from the fact that you can't connect anymore to MySQL through command line or php5-mysql extension using this more than sixteen characters long username. So now, to log to the database, you have to use "more-than-sixtee" instead of "more-than-sixteen".

Debian 5.0:

root@server:/var/log# mysql --user=more-than-sixteen --password=tristanleboss
Welcome to the MySQL monitor.  Commands end with ; or \g.
root@server:/var/log# mysql --user=more-than-sixtee --password=tristanleboss
Welcome to the MySQL monitor.  Commands end with ; or \g.

Debian 6.0:

root@server:/var/log# mysql --user=more-than-sixteen --password=tristanleboss
ERROR 1045 (28000): Access denied for user 'more-than-sixteen'@'localhost' (using password: YES)
root@server:/var/log# mysql --user=more-than-sixtee --password=tristanleboss
Welcome to the MySQL monitor.  Commands end with ; or \g.

Current setup:

ii  libdbd-mysql-perl                   4.016-1                      Perl5 database interface to the MySQL database
ii  libmysqlclient-dev                  5.1.49-3                     MySQL database development files
ii  libmysqlclient16                    5.1.49-3                     MySQL database client library
ii  mysql-client                        5.1.49-3                     MySQL database client (metapackage depending on the latest version)
ii  mysql-client-5.1                    5.1.49-3                     MySQL database client binaries
ii  mysql-common                        5.1.49-3                     MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                        5.1.49-3                     MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.1                    5.1.49-3                     MySQL database server binaries and system database setup
ii  mysql-server-core-5.1               5.1.49-3                     MySQL database server binaries
ii  php5-mysql                          5.3.3-7+squeeze1             MySQL module for php5

Is there something we can do to prevent this new un-userfriendly behavior ?

Tristan

Status: 
Closed (fixed)

Comments

Submitted by JamieCameron on Fri, 04/01/2011 - 17:49

This has actually always been the case in Virtualmin - the problem is that MySQL only supports usernames of up to 16 characters. There isn't really anything Virtualmin can do about this :-(

Submitted by JamieCameron on Fri, 04/01/2011 - 17:50

Although on the domain creation form, there is a field for entering a username instead of having Virtualmin pick one automatically. You could use this to enter something less than 16 characters, which would avoid the discrepancy.

Submitted by tristanleboss on Fri, 04/01/2011 - 18:31

Seems to be a complex problem :)

In the past, it was possible to live with it because MySQL allowed the login but it seems this behavior changed ...

Indeed, I just understood that under my previous setup, I can login as user 'more-than-sixtee' with virtually anything:

mysql --user=more-than-sixtee --password=tristanleboss
mysql --user=more-than-sixteen --password=tristanleboss
mysql --user=more-than-sixteenabcdefghijklmnopqrstuvwxyz --password=tristanleboss

Because, under Debian 5.0, MySQL 5.0 ignores anything after the first 16 characters.

But, under Debian 6.0, MySQL 5.1 no longer ignores this garbage and you can only login using:

mysql --user=more-than-sixtee --password=tristanleboss

Submitted by tristanleboss on Fri, 04/01/2011 - 18:29

Nothing is impossible :D But, I know that time and money matter ...

So, 2 quick ideas:

1) Maybe, as 'Get.the.MaX' proposed (http://www.virtualmin.com/node/8744), VirtualMin can offer a template for the MySQL username under 'Edit Server Template'. His/her idea of using linux UID as part of the MySQL username (ex: db_user_1001) may be a good idea.

2) Put a new "MySQL username" row under "Administration username" one on both 'Edit Virtual Server' & 'Create Virtual Server' pages.

On 'Create Virtual Server', same behavior than "Adminisatration username":

MySQL username: [ ] Automatic (Adminisatration username truncated to 16 characters) [ ] Custom username

On 'Edit Virtual Server', simply list usernames related to the current virtual server:

MySQL username: more-than-sixtee

What do you think?

Submitted by JamieCameron on Fri, 04/01/2011 - 18:51

I will implement option 2 in the next Webmin release. Option 1 sounds like it would be just as confusing as the current situation :-)

Submitted by tristanleboss on Tue, 04/05/2011 - 22:40

I agree.

Thanks for your good support ;)

Submitted by Issues on Wed, 04/20/2011 - 02:21

Automatically closed -- issue fixed for 2 weeks with no activity.

Submitted by Creapix on Mon, 11/14/2016 - 15:24

We havn't found option in virtualmin for stop cuting MySQL username to 16 for new versions of MySQL, in which can be username up to 32 chars (MariaDB 80 chars). We provides hosting and we want to have DB username automaticly genereted, because we send nice HTML e-mail with setting with virtual server creation.

Submitted by Creapix on Mon, 11/14/2016 - 15:32

It probably has a solution nowadays.