Username too long [MySQL] - What should we do?

SysVoid commented 8 years ago

Operating System (OS/VERSION):

CentOS 6.8 Minimal

VestaCP Version:

0.9.8 (x86_64), release 16

Installed Software (what you got with the installer):

Everything aside from php-fpm and postgresql.

Steps to Reproduce:

I logged in as a user I created from admin, went to DB, clicked +, typed the same for username and dbname, with a password.

Related Issues/Forum Threads:

I have not checked for any related threads.

Other Notes:

Username was preprocess. Names too long result in error, and my name was appended to an already long name.

In this, I think we should discuss the best route to go forward with this issue.

anton-reutov commented 8 years ago

Its not a bug, this is a limitation of the mysql server. This limit almost impossible to change

dpeca commented 8 years ago

Yup, almost impossible, and that's why I put this note here - http://mycity.tech/screenshots/mysql-username-length.png Commit: https://github.com/serghey-rodin/vesta/commit/8cabb5414aa8e15ee628db6ee7ade01e9c77fc02 Note is only visible for MySQL, not for MariaDB, because MariaDB don't have that limitation.

I think that's everything we can do about it. Eventually I can write additional javascript that will popup warning message when username is longer than 16 characters?

SysVoid commented 8 years ago

@anton-reutov I guess you're right. I will add the HTML/CSS tag, since perhaps we could go with an official approach similar to what @dpeca added, and a friendlier message when it fails.

dpeca commented 8 years ago

Agree, client-api can check length and return friendlier message. Just keep in mind that you need to make pre-check function to see is it mysql or maridb - you can use the same method that I used on PHP side.

I will see tomorrow to make javascript check&warning.

naumov-socolov commented 8 years ago

I think @dpeca made nice work with that little notice "maximum 16 characters length, including prefix". Very vesta way. I think it would be enough.

Length check should implemented only in one part of the system to keep it clean. And since we have ability to create db with api call, it should have username length check. This means we should avoid to add any additional check in any other part of the system.

Skamasle commented 8 years ago

Some problems come when people create long user name, some thing like "mydomainnametld" and then cam create mysql users.

Will be nice limite vestacp user names to 6 or 8 or 10 to prevent conflicts whit mysql user names

skurudo commented 8 years ago

Propose: check domain name when adding database and auto parse long username in short (delete all vowels for exemple or trim last symbols).

https://bugs.vestacp.com/issues/258

dpeca commented 8 years ago

@anton-reutov Do you want that I try to add check routine in /bin/v-add-database ?

I'm not promicing I have enough time in next 7 days, but I can try.

anton-reutov commented 8 years ago

@dpeca Yes, try to make it

Spheerys commented 8 years ago

I think it's best to avoid long name AND warning user. And to be coherent and to keep a clean system, the checks should be added everywhere we can create databases : API calls and GUI

iamra commented 8 years ago

I have been opting to limit user accounts to 6 characters, but I think even 9 would be good to keep 7 more characters (6 after you consider the _ ) available for the db username.

This is somewhat frustrating, it causes a limiting of the vesta account username verbosity. I wonder if having the account prefixed to the db username is worth this loss.

What would be a pro/con for removing the prefix for the db username only, or even parsing a unique, character limited prefix and just associating it with the vesta account?

gotttlieb commented 8 years ago

Hi, i think that could be grate an update to MariaDB that have a lot of cool stuff Info: https://mariadb.atlassian.net/browse/MDEV-4332

dpeca commented 8 years ago

I think this is a maximum that we can provide about this issue -> https://github.com/serghey-rodin/vesta/commit/654838bfc81ead6e72e9b7de1816405628b18831

Maybe we should close this issue?

@gotttlieb Unfortunately, VestaCP can not choose which version of MariaDB will be in CentOS repository.

gotttlieb commented 8 years ago

@dpeca asuming that you can't know which version will be there i can see 2 options:

Add with the installscript custom mariadb.repo (from the official page) and install using it
Let the system alone and check if the version is 10 or 5.5 then, made or not the alter table as described in the mariadb issue.

Regarding the current issue, your commit at least show the correct error.

Skamasle commented 8 years ago

There is no problem to add mariadb repo, the problem is compatibility, if some body have vestacp whit mysql and whant migrate account from orther vestacp whit mariadb will have some problems.

gotttlieb commented 8 years ago

@Skamasle you are totally true. i didn't think about that, but, anyway, i think that is a really annoying limitation, and not permit the user to set proper names to DB. And i see that if the user account have a large name, is more annoying then.

dpeca commented 8 years ago

Better idea is to check the length of mysql.user.User field...

Server version: 5.5.47-MariaDB MariaDB Server

MariaDB [(none)]> describe mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |

Skamasle commented 7 years ago

Oh yeah, I think in mariadb 10 :/

There are few solutions

Limit user
not limit user -> just modify user prefix when add databases and limit this to 8 this is nice to compatibility but need a lot of code Change to mariadb 10 as default and forget limits -> problem is when fallback to older verions of use mysql and transfer

-> just add like @dpeca adivece when vesta user are added whit something like "try not add long user, limit it to 6 or 8 characters )

And maybe in some years we forget all this when new mysql versions change user limit :dancer:

SysVoid commented 7 years ago

Can't you ALTER mysql.user and make the limit anything?

Skamasle commented 7 years ago

Is not posible alter it :/

SysVoid commented 7 years ago

I guess you're right @Skamasle

madhifallah commented 7 years ago

IMHO a warning should be displayed in the frontend, and prevent the user form choosing a db name larger than 16char for if mysql is the db engine.

iamra commented 7 years ago

I like your suggestions @Skamasle about the following:

not limit user -> just modify user prefix when add databases and limit this to 8 this is nice to compatibility but need a lot of code

There can be an option to install mariadb 10 in the install script instead to avoid this, and waiting on future release to change the userlimit is unreliable. This solution is best imo because the end user does not have think about limiting their username if the account is being automatically created through a billing module. And it keeps the highest compatibility with existing systems by not switching the default MySQL. Ofcourse avoiding collision with shortened usernames would require coding to append with seriel number in those rare occasions.

plutocrat commented 6 years ago

Could you query the value from the available database on setup and add the result to vesta.conf?

outroll / vesta