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

splaquet

Well-Known Member
Sep 24, 2008
53
3
58
W. Hartford, CT
cPanel Access Level
Root Administrator
Twitter
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:
Code:
SET GLOBAL log_bin_trust_function_creators = 1;
and then this on my .sql import file:
Code:
sed -i 's/NO_AUTO_CREATE_USER//' mydump.sql
 
Last edited by a moderator:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
5,332
689
313
cPanel Access Level
Root Administrator
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:


The actual database content limits can be found here:


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-Known Member
Sep 24, 2008
53
3
58
W. Hartford, CT
cPanel Access Level
Root Administrator
Twitter
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.
 

splaquet

Well-Known Member
Sep 24, 2008
53
3
58
W. Hartford, CT
cPanel Access Level
Root Administrator
Twitter
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.
 
  • Like
Reactions: cPRex

splaquet

Well-Known Member
Sep 24, 2008
53
3
58
W. Hartford, CT
cPanel Access Level
Root Administrator
Twitter
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:
Screen Shot 2021-01-27 at 12.37.36 PM.png


here's a screenshot of the users databases:
Screen Shot 2021-01-27 at 1.06.25 PM.png
 

splaquet

Well-Known Member
Sep 24, 2008
53
3
58
W. Hartford, CT
cPanel Access Level
Root Administrator
Twitter
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.

Screen Shot 2021-02-01 at 11.41.32 AM.png

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.

Code:
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
 

splaquet

Well-Known Member
Sep 24, 2008
53
3
58
W. Hartford, CT
cPanel Access Level
Root Administrator
Twitter
...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.