ID #1060

I have a very long username for the MySQL database at my ISP. SQLyog won't let me use it.


Right! Because a MySQL user name is up to 16 characters long. The MySQL docs clearly state:

MySQL usernames can be up to 16 characters long. Operating system usernames might have a different maximum length. For example, Unix usernames typically are limited to eight characters.

However it is a bad practice with some ISP's that they generate longer usernames than that. It is typically 'cheaper' hosting providers that offer a single MySQL database as part of a 'personal' or 'small business' subscription plan. They auto-generate the username from the user's domain name and it could be somewhat like mydb_myveryowndomain or similar. It is also true that some of our competitors offer support for that. However it is very bad practice! To make it work you will have to:

1) ALTER the TABLE mysql.users and change the mysql.users.users column from a char(16) to some longer value
2) You can no longer GRANT user rights, but must INSERT/UPDATE the mysql system tables directly
3) You must 'patch' (or rather 'hack') the MySQL API/client code

We have had intense discussions with this the MySQL AB on this issue. From the official correspondence we quote:

"This is simply a lucky fluke of sorts (if it works). MySQL simply does not support longer usernames .... Altering the system tables, aside from using our own mysql_fix_privilege_tables script to keep up with our changes, is simply unsupportable. There are server and client changes needed to properly handle any sort of modifications here, even though in some cases a quirk (as above) may seem to function .... This is, basically, dangerous behavior. We will attempt to curb it as well as we can.... Luckily, our manual states clearly that in both cases, MySQL will not provide support if any problems arise ... That is , it may work and it may not work, but MySQL will not ponder as to why it works or why it does not work ... We simply do not provide support for such cases."

We won't play that game as others do! You should convince your ISP that changing the format of the user table is bad and dangerous practice!

And further: MySQL has 'stopped the game'. Again we quote from the above correspondence:

"To make things even more precise, I will now send a server patch to our development management. This patch adds a code that will truncate user column at 16 chars and other columns to their nominal sizes. This will ensure that future 4.1 and 5.0 versions will not work with longer names, whatever changes some application could envisage."

So with the most recent builds in the MySQL 4.1.x series and with MySQL 5.0 it would not work anyway. There is now code in the server binary itself that truncates any user name to 16 characters.


Tags: -

Related entries:

You can comment this FAQ