MySQL Management showing incorrect data for number of users

[magicalwonders]

Hi,

I migrated from cPanel to DirectAdmin last year.

There were a couple of minor glitches in the migration, but they were easily resolved at the time. However, I've now noticed that the number of users shown for the various databases is incorrect. I have eight accounts and only one of them is showing the right number. All the rest are showing number of users as either -1 (minus 1) or 0 (zero).

Here's a pic of one of the accounts which shows the issue -

My host has updated all the plugins and extensions for DirectAdmin, but it hasn't fixed the issue and they seem to have run out of ideas. I have asked them if there is a script that can be run to update the number of users but they are not aware of any.

I have tried to raise a support ticket, but when I input my license and client ID I get the following -

If anyone can offer any suggestions on how to fix this that would be very welcome.

Myles

 

[factor]

When you login to the backend in ssh. How many users to you see in mysql? OR if you login as the admin how many do you see in PMA?

About the host. The Host should be able to log a Support ticket. If the server you purchased from them is Full Managed they should be supporting you. If you bought a Non Managed server then they offered you a license when you bought a server. The reason they are allowed to do this is because they should support you.

 

[magicalwonders]

Cheers, Brent,

I'm not great at SSH and as it's a managed server I haven't spent time getting to grips with it. I've logged into the control panel as admin but can't see any reference to PMA. What is that and where is it?

I think I'll push the host to raise a support ticket with DirectAdmin. I'm going to be switching my host in mid-August, but I'd like to get this fixed before moving so as not to start off the new hosting with support tickets!

Myles

 

[Zhenyapan]

we have same error when trying to restore mysql table (with users/passwords) from mariadb 10.2 at 10.4 instance (forgot to check what version was in backups), due to different structure of 10.2 and 10.4 user configs was broken
there are two ways to fix:
1. parse mysql table and recreate all DB users - harder but without downtime (our case because there was 860 databases) dont have commands, it was more than year ago.
2. remove all users from DBs and add new users - must put new passwords to website configs.
3. backup all databases with their configs, reinstall mysql, restore dumps - not sure that it will help in your case.

 

[magicalwonders]

we have same error when trying to restore mysql table (with users/passwords) from mariadb 10.2 at 10.4 instance (forgot to check what version was in backups), due to different structure of 10.2 and 10.4 user configs was broken
there are two ways to fix:
1. parse mysql table and recreate all DB users - harder but without downtime (our case because there was 860 databases) dont have commands, it was more than year ago.
2. remove all users from DBs and add new users - must put new passwords to website configs.
3. backup all databases with their configs, reinstall mysql, restore dumps - not sure that it will help in your case.

Thanks for the info. I've asked my host to raise a ticket with DirectAdmin, so maybe they'll advise on similar fixes. I'm going to let my host take care of it now as I can see this might be a bit of a drain on my time.

 

[Zhenyapan]

there is no support for internal licenses - so your host must solve it himself.

 

[magicalwonders]

there is no support for internal licenses - so your host must solve it himself.

That doesn't sound good. I just received this from my host -

Hello Myles,

Thank you for contacting us.

For a User, the "MySQL Management" page listing the databases has a column called "Number of Users" which represents the number of MySQL accounts set up to use it. When you click the DB, you can see these accounts.

However, DA does not show the fact that the DA system User account (that User's DA login name) is also able to access this database (as it's highly discouraged to use it in scripts, etc.)

So if there is 1 username_dbuser on username_db, there is also a username on username_db. So the true count is 2. When DA counts the Users in mysql, it subtracts 1 from this count to show the total number in the "Number of Users" column.

So, If you're seeing a total count of -1 on a given database, this would mean that there is no DB_user on DB.

I can't make sense of that. Surely it shouldn't be showing zero or minus one if there is one user on the database. If there is no DB_user on DB. then WordPress wouldn't work!

Just to make sure I understand how this should work, if there is one user connected to a database I'm guessing it should display I user inMySQL Management. Yes?

 

[factor]

Add 1 DB user to multiple databases

Hello, I want to add multiple 1 DB user to multiple databases. In cPanel, it's easy and the option is already present. How to get it done in DA?

forum.directadmin.com

Look in the user section in Mysql Management

Click a DB name. How many do you see?

 

[magicalwonders]

Add 1 DB user to multiple databases

Hello, I want to add multiple 1 DB user to multiple databases. In cPanel, it's easy and the option is already present. How to get it done in DA?

forum.directadmin.com

Look in the user section in Mysql Management

Click a DB name. How many do you see?

I'm not sure I'm following the right path. I login as admin and click account manager at top left of the page, but there is no MySQL Management in the drop-down list/

If I change the access level from admin to user (top right) I then get MySQL Management showing -

However, when I click on MySQL Management I get the following screen!

That doesn't seem right?

If I login to one of the accounts I can bring up MySQL Management for that account. The following account for example has seven databases.

Each DB has only one user assigned. Although it's a bit hard to see in the pic the number of users is showing as 0 or -1.

 

[factor]

Its hard to count but it could be this. https://help.directadmin.com/item.php?id=481
A mysql username can only be 16 chacters long. Many of yours seem to long. You account user name is 14 which means you only have 1 left.

unless they did all of this..

https://help.directadmin.com/item.php?id=284

max_username_length=10

Max length a username can be. Max is 30. It is limited to max of 14 with MySQL 5.5/5.6 and MariaDB 5.5 because of the 16 character mysql database name limit and the username naming prefix.
http://www.directadmin.com/features.php?id=189

login to your server at: https://your server/phpMyAdmin and click the "mysql" database on the left. Click the "db" table on the left, below the mysql db. Near the top, click the "structure" tab. Under the "Type" column, see the char() length for both Db and User. The User seems to be the smaller of the two, so if it shows 32, then you can use max_username_length=30

https://www.directadmin.com/features.php?id=2294

 

[magicalwonders]

It's weird as another account with a long name ocdelectricalco is actually showing 1 user.

Whereas a short name account with eight letters is showing 0 and -1 for each database.

I'm having trouble logging into the phpMyAdmin for the server I assume it's not phpMyAdmin for the primary domain the accounts are under? I used the IP address for the server in URL So - https://IP address of server/phpMyAdmin That brings up phpMyAdmin login box, but I don't have any username or password for this.

I tried entering my DirectAdmin login but that didn't work.
I've also tried using root and Admin password, but getting access denied.

 

[magicalwonders]

I found this in the help section -MySQL: Number of Users shows -1, or one less than it should https://help.directadmin.com/item.php?id=2090

It offers the following solution -

The solution is to simply add the username account onto username_db.
To do this, we need to login to /phpMyAdmin.

1. Login to /phpMyAdmin with the da_admin user/pass found in /usr/local/directadmin/conf/mysql.conf
2. Click the "SQL" tab
3. Type in the following query:
   GRANT ALL PRIVILEGES ON `username_db`.* TO 'username'@localhost
4. Still in the SQL tab, run the query:
   FLUSH PRIVILEGES

I'm going to struggle to access mysql.conf so I'll pass this onto my host.

The one thing that bothers me is granting users all privileges. I recently discovered that going from cPanel to DirectAdmin caused all the privileges to go from restricted access to all. That's very bad for security. I assign users Select. Insert, Update and Delete. That's all that's needed for WordPress to work.