In Progress CPANEL-35808 - namespace limit on MySQL 8 tables & usernames

[splaquet]

how about removing the namespace limit on MySQL 8 tables & usernames? i installed MySQL8 (because why would I not?!) and now have the server up and ready to port over some of my personal sites, for testing and performance benchmarking.

BUT (note the BIG BUT?), due to DB/username character limitations (set by cPanel i'd assume?), there are a few hiccups with the export/import process.

i also had to run this in mysql:

SET GLOBAL log_bin_trust_function_creators = 1;

and then this on my .sql import file:

sed -i 's/NO_AUTO_CREATE_USER//' mydump.sql

 

[cPRex]

Hey there! MySQL usernames are limited to 32 characters, which would include the "username_" portion that cPanel usually fills in automatically. This isn't a cPanel restriction, but is part of the MySQL 8.0 software as noted here:

MySQL :: MySQL 8.0 Reference Manual :: 6.2.1 Account User Names and Passwords

dev.mysql.com

The actual database content limits can be found here:

MySQL :: MySQL 8.0 Reference Manual :: 9.2.1 Identifier Length Limits

dev.mysql.com

which in general are mostly 64 characters, and that is also what the cPanel >> MySQL database limits you to.

Let me know if that helps!

 

[splaquet]

well, i'd have to run things again to see what happened exactly... but, it appeared that when transferring cPanel accounts across servers, from MariaDB10.3 > MySQL8.

after i looked closer into it, i noticed that the longer username had been imported into the master MySQL user table. I didn't notice that until I terminated the account and tried another time. So, even though the username was present in the MySQL user table, it was not displaying in the actual cPanel account. ...which, not surprising, was what was preventing me from creating another user in the cPanel account. and since i didn't actually notice the existing name in the user table until after all of this, i had recreated a shortened name for the database and table.

i only have a few accounts on the testing server at the moment. i'll be migrating more over when CloudLinux releases a few more php8 extensions. i have a feeling that this will pop up again, and i'll pay closer attention to it.

this worked: (old school, standard 8 char username format)
username_mdb

this had issues: (new school, not limited to 8 char username format)
longerusernames_mdb

that's the actual character length of the specific DB name that i tried to transfer. you'll notice that the longer username prefix is far from 32 characters.

 

[cPRex]

If you had a sample database this might be a good thing to open a ticket on so we can do some testing with it, as I'm not seeing anything obviously broken when doing a 10.3 >> 8.0 transfer on my end.

 

[splaquet]

i had forgot to mention a few pieces:
- old server (source) was CentOS/CloudLinux 7, new server (destination) was 8
- source server did not have Governor installed, destination server did have Governor installed

also, i've seen UTF8 table errors/issues pop up. this was quite the massive DB (6gb), and i have a feeling that i still had a few UTF8 tables in there... because i'm seeing notifications in phpmyadmin that reference them. i'm not really sure of the easiest method to deal with migrating tables from UTF8 to UTFMB4, when the strings are too long to be converted over natively. (sorry for being a noob. hopefully that makes sense)

i'm a bit busy at the moment. when (and if) i come across this again, i'll update this thread with more details.

 

[splaquet]

today, i was trying to debug why one of my transferred sites wasn't popping up. i deleted the user_tonylee, deleted the user & db in user's cPanel account, added them both back, and the DB worked again. this is similar to the other situation i was referring to. i apologize for not having more on this at the moment... but wanted to share my observations.

here's a screenshot from mysql.user table:



here's a screenshot of the users databases:

 

[cPRex]

That is interesting for sure. If you can get us access to a system (or both) where this issue happens we can do some more in-depth testing.

 

[splaquet]

certainly. what's the best method of handling that? via traditional support ticket?

...i'm gathering that others are not having similar experiences?

 

[cPRex]

Yup - a standard ticket through WHM or through the link in my signature.

I haven't seen similar reports of this behavior just yet.

 

[splaquet]

I've submitted a ticket: #94191856

##

so, i was trying to transfer over a few sites to then review the mysql.user table... looking for DB users that were added during transfer. for the first time, i noticed the message in the screenshot below (transferring from server with MariaDB 10.3 > MySQL 8). so, if the 8 character limitation is not present in MySQL8, is this a remnant notice of that limitation?

background:
1) the first account was setup years ago, as the same domain.net. when my client acquired the .com, we created a second install for him. no such notice or notification presented itself when we created the new/second account.
2) we created a second account for the client, to handle his bulk email on a dedicated/seperate IP address. again, no issues when creating them and no conflicts with using them.



i'm not sure if it was coincidence or not, but i had reinstalled/updated CloudLinux's DB Governor yesterday. i was having a few issues that references ea-php44-php-mysql, ea-php51-php-mysql & ea-php52-php-mysql, so i thought a refreshed --install-beta would help. it appeared to have removed the php44 notice, but the 51 & 52 were still present (as seen below). ...and since those packages don't actually exist, and i really don't have the time to create symlinks to the alt versions (if that'd even fix the issue), i'm really not sure how to actually resolve it.

ERROR   : mysql.so is not found. Please install ea-php52-php-mysql8.0 package
ERROR   : mysql.so is not found. Please install ea-php52-php-mysql8.0 package
ERROR   : mysql.so is not found. Please install ea-php52-php-mysqlNone package
ERROR   : mysql.so is not found. Please install ea-php52-php-mysql5.5 package
ERROR   : mysql.so is not found. Please install ea-php52-php-mysql5.1 package
ERROR   : mysql.so is not found. Please install ea-php52-php-mysql5.0 package
ERROR   : alt-ea-php52 reconfiguration is failed
ERROR   : mysql.so is not found. Please install ea-php51-php-mysql8.0 package
ERROR   : mysql.so is not found. Please install ea-php51-php-mysql8.0 package
ERROR   : mysql.so is not found. Please install ea-php51-php-mysqlNone package
ERROR   : mysql.so is not found. Please install ea-php51-php-mysql5.5 package
ERROR   : mysql.so is not found. Please install ea-php51-php-mysql5.1 package
ERROR   : mysql.so is not found. Please install ea-php51-php-mysql5.0 package
ERROR   : alt-ea-php51 reconfiguration is failed

 

[cPRex]

Thanks so much - I've added some of my own details and I'm following along so I'll be sure to keep this thread updated.

 

[splaquet]

...it looks as though it was a bug. the support agent thoroughly tested out, and ended up filing a report with the developers.

 

[cPRex]

I'm interested to see the results of that case. I'm following along so I'll be sure to post an update if/when I get one.

 

[splaquet]

...sad, but true... it's either a coverup on something that was overlooked, or an intentional security measure that was left in place. i don't feel comfortable calling this one resolved. but, for now at least, it apparently is. here's the official/final response:
###

So, to create a 16 character username is possible on recent cPanel versions, the latest cPanel release, MariaDB10.3 and MySQL8… but cPanel has issues with handling transfers of accounts when usernames are greater than 8 characters?

Correct, while the username limitation was increased from 8 characters due to the available increase in a MySQL database name size, the Transfer Tool still enforces this limitation to maintain compatibility with older cPanel servers that may be attempting to utilize the Transfer Tool. One such example for this was to facilitate a transfer from a MariaDB server to a MySQL server using a version before MySQL 8. However, as MySQL 8 is now becoming more widespread, it may be beneficial to remove this limit in the Transfer Tool as both MariaDB and MySQL no longer have this limitation.

My colleague who initially helped you on this ticket, filed case CPANEL-35808 to our developers regarding this issue specifically, and while this case is not publically available it will be identified by that case ID if any action is taken here in our changelog: https://docs.cpanel.net/changelogs/

FYI (this just came to mind), the solution that I ended up using to transfer the conflicting accounts was to use /scripts/package account to bundle up the remote account, sftp the file from server to server, and /scripts/restore to recreate it locally. If memory serves, with the exception of 1, that worked for most of the manual transfers.

I am happy that this workaround provided a solution for you. The /scripts/pkgacct and /scripts/restorepkg tools, while accomplishing the same end-goal of packaging and restoring accounts, does not employ the same restricted restore as the Transfer Tool by scanning certain requirements. Obvious issues with the packaged account will still be logged, however, these tools do not employ the same scanning mechanisms that the Transfer Tool uses to ensure compatibility between different cPanel versions and MySQL versions.