MySQL: Number of Users shows -1, or one less than it should


Enter Your Query:
Use '%' for wildcards and quotes for "exact phrases"


Top Level » MySQL / MariaDB

MySQL: Number of Users shows -1, or one less than it shouldLast Modified: Dec 18, 2018, 1:31 pm
For a User, the "MySQL Management" page listing the databases has a column called "Number of Users" which represents the number of mysql accounts setup 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 username on username_db.  So the true count is 2.
When DA counts the Users in mysql, it sutracts 1 from this count to show the total number in the "Number of Users" column.

DA User Login:
DB Name:username_


Issue

If you're seeing a total count of -1 on a given database, this would mean that:
  1. There is no username_dbuser on username_db
  2. There is no username on username_db

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

Note: if you have multiple access hosts on this database, you would need to either repeat the above with that value instead of localhost,  or just delete and re-add those access hosts through DirectAdmin.

© 2018 JBMC Software, Suite 173  3-11 Bellerose Drive, St Albert, AB  T8N 1P7  Canada.  Mon-Fri 9AM-5PM MST