Directadmin Docs

Troubleshooting

Error connecting to MySQL: Access denied for user: 'da_admin@localhost' (Using password: YES)

When connecting to the MySQL interfaces in DirectAdmin, if this error appears, that would indicated that the "da_admin" user has not been set up correctly.

The very first thing you should check is for the existence of conflicting MySQL configuration files, including:

/root/.my.cnf

and

/etc/mysql/my.cnf

Some systems have a /root/.my.cnf file with a preset password. This will override the value set in DA and cause these errors, especially during backups/restores.

If either of these conflicting MySQL configuration files exist, figure out if they or the /usr/local/directadmin/conf/mysql.conf file contains the correct MySQL da_admin credentials, ensure that the correct credentials are in use in the /usr/local/directadmin/conf/mysql.conf file, and then rename/remove the conflicting MySQL files.

Use the following command to test the credentials (enter the password when prompted):

mysql -uda_admin -p

If the issue is not related to the existence of these files, proceed with the following to resolve this:

Desired root password:

Desired da_admin password:

1. Make sure the root MySQL password works.

If you know it, skip to #2.

The MySQL root password can be found in the /usr/local/directadmin/scripts/setup.txt if the file has not been deleted and/or the password hasn't been changed since initial setup. It's under the header "mysql=". If it cannot be found, then mysqld will have to be restarted with the skip-grant-tables option.

For MySQL 5.7 and higher on CentOS/RHEL and Debian/Ubuntu

Please use:

systemctl stop mysqld
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
systemctl start mysqld

Now reset password for root:

mysql mysql -e "UPDATE mysql.user SET authentication_string=PASSWORD('rootpass') WHERE user='root'; FLUSH PRIVILEGES;"

And restart mysql without skip-grant-tables :

systemctl unset-environment MYSQLD_OPTS
systemctl stop mysqld
systemctl start mysqld
mysql -uroot -p'rootpass' -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootpass';"

For all the other versions of MySQL/MariaDB:

service mysqld stop
mysqld_safe --skip-grant-tables &

Reset password with:

mysql mysql -e "UPDATE mysql.user SET password=PASSWORD('rootpass') WHERE user='root'; FLUSH PRIVILEGES;"

And restart normally:

killall -9 mysqld_safe
killall -9 mysqld
service mysqld start

2. Reset the da_admin MySQL user password.

Type:

mysql -uroot -p

Then press enter. You'll be asked for the password. Once in MySQL, type:

ALTER USER 'da_admin'@'localhost' IDENTIFIED BY 'daadminpass';
GRANT ALL PRIVILEGES ON *.* TO da_admin@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit

That should set the password for da_admin in MySQL.

3. Make sure it's setup correctly for DA to use.

Edit /usr/local/directadmin/conf/mysql.conf and set

user=da_admin
passwd=daadminpass

4. Test it out in DirectAdmin.

Check how it goes from DirectAdmin user panel > MySQL Management.

Error 1615 (HY000): Prepared statement needs to be re-prepared

This happens on busy servers when server has too low value set in the table_definition_cache. It causes queries using prepared statements to start failing.

To fix it the table_definition_cacheopen in new window server variable needs to be increased. Default value for MariaDB servers is 400.

Current value can be checked with:

$ mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e 'SHOW VARIABLES LIKE "table_definition_cache"'
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| table_definition_cache | 400   |
+------------------------+-------+

If prepared statements errors are happening we recommend to double the existig value (from 400 to 800, from 800 ot 1600, etc.).

New value can be changed at runtime with:

$ mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -B -e 'SET GLOBAL table_definition_cache = 800'

To make it permanent it needs to be added to /etc/my.cnf, example:

[mysqld]
table_definition_cache=800

mysql_connect(): Too many connections. Unable to connect to sql server

Mysqld has a limit to the number of connections that it will take at one time.

You can tell mysqld to increase the limit by creating an my.cnf file, adding an option, then restarting mysqld.

The option is:

[mysqld]
max_connections=500

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

If you get an error that you cannot connect to your mysql.sock file (can also be /tmp/mysql.sock on Debian), then do the following:

  1. Check to see if the mysql.sock exists:
ls -la /var/lib/mysql/mysql.sock
  1. Check to see if it's some other MySQL instance that has moved it somewhere else (possibly from a wrong my.cnf setting):
netstat -lnp | grep mysql
  1. Check to see if it's mysqld is running:
ps ax | grep mysql
  1. Restart mysqld to recreate the sock file:
service mysqld stop
service mysqld start
  1. If needed, use a brute force method (as a last resort) by repeating the following commands:
killall -9 mysqld mysqld_safe

Repeat until no mysqld process is running, it will throw an error.

Then type:

service mysqld start
  1. Check the following log for clues:
/var/lib/mysql/`hostname`.err

On Debian, it's:

/home/mysql/`hostname`.err

Use the following command to confirm the location of your configured error log:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf -e "show variables like 'log_error';"

mysqldump 1449: The user specified as a definer ('da_admin'@'%') does not exist when using LOCK TABLES

If you get a backup error similar to:

Error while backing up database user_dbname
mysqldump error output: mysqldump: Got error: 1449: The user specified as a definer ('da_admin'@'%') does not exist when using LOCK TABLES

then add the following to your directadmin.conf and restart DA:

extra_mysqldump_options=--single-transaction

Note that this is now an internal default for mysqldumpopen in new window

String 'username_dbuser' is too long for user name (should be no longer than 16)

When restoring your MySQL databases, if you run into this error:

Unable to add user username_dbuser. String 'username_dbuser' is too long for user name (should be no longer than 16)
Can't add user username_dbuser to username_dbname: Unable to add user username_dbuser. String 'username_dbuser' is too long for user name (should be no longer than 16)

it means that the username_dbuser values are too long for MySQL.

Although there are settings in the mysql.user and mysql.db tables which control then max length of the username, MySQL clearly states that they cannot be changed: http://dev.mysql.com/doc/refman/5.5/en/user-names.htmlopen in new window

"The limit on MySQL user name length is hard-coded in the MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the MySQL database does not work."

The solution (with regards to DA) is to shorten the DA username, so that the total length of the db user is shorter.

There are 3 ways of doing this:

  1. Change the existing username and restore again from that User's backups folder:
/home/**username**/backups

where this path is needed, as it's a username independent restore area.

  1. Or delete the existing User, rename the backup to a shorter name, and restore again, e.g.,
cd /home/admin/admin_backups
mv user.admin.**username**.tar.gz user.admin.**usern**.tar.gz
  1. Or this last option is more difficult, but will shorten the database name and db usernames, rather than changing the DA username.
  • Using this guide, you can extract the current tar.gz backup file.
  • In the "backup" directory, each MySQL database will have 3 files, e.g.,
**username_dbname**.conf
**username_dbname**.sql
**username_dbname**.dump.log

Rename those 3 files to have a shorter _dbname value, so username_dbname is 16 characters, or less.

  • Also edit the username_dbname.conf and change any username_dbuser= entries which are more than 16 characters.
  • Repack the tar.gz.

If you're seeing the word "collation" in the error, ensure you're restoring onto DA version 1.43.0 or newer, with regards to this change: http://www.directadmin.com/features.php?id=821open in new window

Error: 'Out of resources when opening file (Errcode: 24 - Too many open files)'

If you get the following error while creating a backup:

Error while backing up database USER_NAME
mysqldump error output: mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces
mysqldump: Couldn't execute 'show fields from `NAME_accesslog`': Out of resources when opening file '/tmp/#sql_390d_0.MYD' (Errcode: 24 - Too many open files) (23)

OR:

150707  0:15:49 [ERROR] Error in accept: Too many open files

check this forum thread for solutions:
http://forum.directadmin.com/showthread.php?t=47098open in new window

Some solutions below:

  1. Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh:
ulimit -HSn 1024
ulimit -HSn 32768
ulimit -HSn 1024000

which should force it to try and set it as high as it can go. If one of the value is too high, delete that line, or lower the value.

For Operating Systems with systemd (CentOS7+), edit the service file, (for example, MariaDB's service file would be located here: /usr/lib/systemd/system/mariadb.service) and set the number of files limit to a higher value like so:

LimitNOFILE 65535
  1. Check /etc/my.cnf. Under the [mysqld] section, if you have a open_files_limit variable, increase it, e.g.,
open_files_limit=65535

then restart mysqld:

systemctl daemon-reload
service mysqld restart
  1. Confirm in MySQL itself by checking the open_files_limit variable to ensure it's increased, by running the SQL query:
show global variables like 'open%';

InnoDB: Warning: io_setup() failed with EAGAIN.

If mysqld won't start up, and you check your MySQL error log, e.g.,

cd /var/lib/mysql   #"cd /home/mysql" for Debian
tail -n 200 `hostname`.err

and you find this error somewhere in it:

131201 19:22:27 InnoDB: Using Linux native AIO
131201 19:22:27  InnoDB: Warning: io_setup() failed with EAGAIN. Will make 5 attempts before giving up.
InnoDB: Warning: io_setup() attempt 1 failed.
InnoDB: Warning: io_setup() attempt 2 failed.
InnoDB: Warning: io_setup() attempt 3 failed.
InnoDB: Warning: io_setup() attempt 4 failed.
InnoDB: Warning: io_setup() attempt 5 failed.
131201 19:22:30  InnoDB: Error: io_setup() failed with EAGAIN after 5 attempts.
InnoDB: You can disable Linux Native AIO by setting innodb_use_native_aio = 0 in my.cnf
131201 19:22:30 InnoDB: Fatal error: cannot initialize AIO sub-system

then the solution would be to add:

innodb_use_native_aio = 0

to the [mysqld] section of your my.cnf file.

If this is** a fresh install **(no data yet), you might need to re-install MySQL with the my.cnf setting in place.

cd /usr/local/directadmin/scripts
./mysql.sh pass1 da_admin pass2

Where pass1 and pass2 can be set to any passwords you want. pass1 will be the root mysql password, and pass2 will be the da_admin MySQL password.

Another reported solution is to increase your kernel setting:
/proc/sys/fs/aio-max-nr

This is never really a good position to be in, but there are ways of rebuilding these files into a clean slate. Note, that we recommend using the innodb_file_per_table in the [mysqld] section of your my.cnf from install time. Changing it after databases already exist could break things and force you to use this guide. On most operating systems innodb_file_per_table is set to “on” by default.

If you want to change to use innodb_file_per_table, then you'd need to use this guide.

It will essentially dump all databases to sql files, drop all databases, stop mysqld, delete the ib* data files, and restart mysqld and restore the sql files.

  1. Before anything else, ensure you have full .sql backups of all of your data. It's also recommended to have a copy of the MySQL data directory, so you can copy it back if things go south.

CustomBuild can make the plain-text sql files for you. Run the following, but only if your data itself is still valid. Do not run this if you need to restore your backups to get non-corrupted data:

cd /usr/local/directadmin/custombuild
./build set mysql_backup yes
./build mysql_backup

Then confirm that the sql files have been created here:

ls -la /usr/local/directadmin/custombuild/mysql_backups

If you do not have any valid plain .sql file backups, do not proceed. This guide only works if if you have something to restore from.

Note: The innodb_force_recovery optionopen in new window may let you get mysqld running, and possibly even repair it for you in a quick/easy manner.

  1. Next, we'll shut off mysqld to make a data directory backup, which you'll want to do either way (corrupted or not).
perl -pi -e 's/mysqld=ON/mysqld=OFF/' /usr/local/directadmin/data/admin/services.status
service mysqld stop

then copy the directory.

cd /var/lib
cp -Rp mysql mysql.innodb.backup
  1. Try to surgically fix or remove the offending database:
mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf database_name < /usr/local/directadmin/custombuild/mysql_backups/database_name.sql

Make sure to replace database_name with the name of the database in question.

Hopefully this will work for you, and you can stop without a full rebuild. If this fixes the problem, stop here, do not continue.

At this point, assuming you have safe backups of all of your data, you can move to the point of no return.

Start mysqld again, and drop all databases, except these three: mysql, performance_schema, information_schema.

To do this, you can use /phpMyAdmin via Apache to make things easier and quicker.

Use the da_admin user/pass from:

cat /usr/local/directadmin/conf/mysql.conf

Drop all, but those 3 databases.

  1. Now that they're gone, we can clean up the ib* data. Stop mysqld again, delete the ib* files, and start mysqld. If you are changing to use the innodb_file_per_table option, make sure it's in your /etc/my.cnf at this point.
cd /var/lib/mysql
/etc/init.d/mysqld stop
rm -f ib*
/etc/init.d/mysqld start
perl -pi -e 's/mysqld=OFF/mysqld=ON/' /usr/local/directadmin/data/admin/services.status

This will rebuild the ibdata1 and innodb log files, goodbye corruption.

  1. Last step is to restore your sql data files. Because the MySQL database was left, the users should all still be there (its tables all use MyISAM, thus not affected by innodb corruption).

Assuming your sql files are in the path above, then you should be able to run the following script:

cd /usr/local/directadmin/custombuild/mysql_backups
wget http://files1.directadmin.com/services/all/mysql/restore_sql_files.sh
chmod 755 restore_sql_files.sh
./restore_sql_files.sh

This will automatically skip the MySQL and schema databases, and restore everything else.

Error inserting host: Field 'authentication_string' doesn't have a default value

Update January 25, 2018:
An actual fix has been added to check for, and explicitly set the blank authentication_string value when adding an access host:
https://www.directadmin.com/features.php?id=2083open in new window

mysqldump error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLES

Some types of database corruption can cause the data to be lost, but will leave behind enough information to rebuild the empty table structure.

Only use this guide if you've given up trying to recover the table contents, and just want to fix the stable structure.

Let's say that you get the above error for User database , and table throwing the backup error:

mysqldump error output: mysqldump: Got error: 1146: Table 'user_db.table' doesn't exist when using LOCK TABLES
  1. Check to see if there are any other data files, or if it's just the .frm file:
cd "/var/lib/mysql/user_db"
ls -la "table.*"

If it's just the table.frm file, then the rest of the data is likely lost, but you may be able to rebuild the table.

  1. For that, we need to read the .frm file. The mysqlfrm tool is required for that, e.g.,
yum install mysql-utilities

(or rpmopen in new window, connector-pythonopen in new window). Once installed, see if it can be read:

mysqlfrm --diagnostic "table.frm"

which might output the full CREATE TABLE syntax. Save this somewhere safe, until the end of of the last ; character. Ignore any # lines, as they're just comments.

Note, if you see "CHARACTER SET ", you can either delete those 3 words, or change to the correct charset, if you happen to know what it should be set to.

  1. Now, we need to remove the broken table. Login to /phpMyAdmin and run the query:
DROP TABLE user_db.table
  1. Lastly, run the CREATE TABLE query from above, to rebuild the table.

Although** this doesn't rebuild the data**, it will at least get the table back, which can be fine in some cases (if you're lucky, like a temp cache table).

MariaDB: Aria engine is not enabled or did not start.

If MariaDB (mysqld) isn't starting, and you're getting this error:

Aria engine is not enabled or did not start. The Aria engine must be enabled to continue as mysqld was configured with --with-aria-tmp-tables

  1. Make a full backup of your /var/lib/mysql folder.

  2. Try renaming the aria_log_control to something else, e.g.,

cd /var/lib/mysql
mv aria_log_control aria_log_control.moved
  1. Restart mysqld
service mysqld restart

It is safe to delete this file, but only if MariaDB is not running:
https://mariadb.com/kb/en/library/aria-faq/#when-is-it-safe-to-remove-old-log-filesopen in new window

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

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 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.

DA User Login: DB Name: username_

Issue

If you're seeing a total count of -1 on a given database, this would mean that:

There is no usernamedbuser on usernamedb

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.

Login to /phpMyAdmin with the da_admin user/pass found in /usr/local/directadmin/conf/mysql.conf

Click the "SQL" tab

Type in the following query:

GRANT ALL PRIVILEGES ON `username_db`.* TO 'username'@localhost

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.

Running the mysql.sh is not creating data in /var/lib/mysql

If you're trying to re-install MySQL/MariaDB, and are starting from scratch with the /usr/local/directadmin/scripts/mysql.sh, but it's not creating the mysql tables + data, this guide may be able to help. If your solution is not listed here, please create a ticket for DirectAdmin Support, so we can investigate and add it here.

Check your output, and see if any of it matches up with the scenarios below:

  • Within the output:
Data needs to be created in /var/lib/mysql/mysql ...
Neither host 'server.host.com' nor 'localhost' could be looked up with
'/usr/sbin/resolveip'
Please configure the 'hostname' command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the --force option

We've encountered this error before, even though the /etc/hosts did work correctly. Solution to this one is to edit the mysql.sh and add --force to the end of the mysql_install_db line, making it look like this:

/usr/bin/mysql_install_db --force

where the --force option simply skips those DNS checks.

DirectAdmin already makes sure that localhost exists in the /etc/hosts, so we're not concerned about these checks.

Note: DirectAdmin 1.56.0 and up will use --force by default, so you'd only need to make this edit for older versions.

MariaDb 10.4: Deleting Databases is slow / causes timeout

As of version 10.4, MariaDB has replaced the mysql.user table with mysql.globa_priv table, and mysql.user becomes a view:
https://mariadb.com/kb/en/library/mysqluser-table/open in new window

For the most part, this really shouldn't matter, as DA would be using the mysql_use_new_user_methods=1 (for ALTER USER, etc, instead of direct mysql.user control).

We had a report of slow response time for deleting database, where DA does some fancy queries to figure out if a User is on mysql databases or not, so it would only delete the User if it's only on the one being deleted.

The short of it is that DA was looking at a few rows that were not specifically indexes, thus the lookup would have been done in a linear fashion (I believe), hence very slow response time, as this server had 45,000 database accounts (yes this is a lot, but there is no limit).

To optimize things, one can added 3 new index files on the related tables:

  1. mysql.global_priv

    • user
    • host

  2. mysql.db

    • db

Where, I believe the biggest gain was mysql.db:db.

To add a simple index to a database, login to /phpMyAdmin with your da_admin account (/usr/local/directadmin/conf/mysql.conf), and go to the "mysql" database. Click the table you'd like (e.g., global_priv), and go to the "Structure" tab.

About 1/2 way down there is a line that says:

Create an index on [1] columns [GO]

Click that GO button with 1 in the form.

For "Index Choice" change "UNIQUE" to be "INDEX", select the column of your choice (e.g., 'user'), and click "Go".

An Index is a lookup table, so MariaDB can find a specific value in that row more quickly.

If there is no index for a row that is heavily used, performance can be affected.

Note that these tables already do have "PRIMARY" indexes combining sometimes 3 different columns, but if DA is only referencing just 1 column, then you should see performance gains by giving that one column it's own index.

The before and after results were fairly clear it worked:

  • Before:
    MariaDB [mysql]> select count(*) from user;
    +----------+
    | count(*) |
    +----------+
    |    45196 |
    +----------+
    1 row in set (4 min 48.356 sec)
  • After:
    MariaDB [mysql]> select count(*) from user;
    +----------+
    | count(*) |
    +----------+
    |    45201 |
    +----------+
    1 row in set (0.001 sec)

Perhaps this is something the MariaDB team could look at in the future, but for now this seems to work.

Similar report on MariaDB 10.3 which does not use a view, solution was to index:

  1. mysql.user

    • user
    • host

  2. mysql.db

    • db

Related: Rewrite clearing of access hosts for system account during DB removal.

The server requested authentication method unknown to the client [caching_sha2_password]

MySQL 8.0 supports a new authentication plugin called caching_sha2_password.

For the most part, this may cause confusion in several areas, including the one-click phpMyAdmin login / SSO.

If you login to phpMyAdmin as da_admin and check your mysql.user table, if you have any Users created by DA, including the SSO logins that start with "da_sso_" which have the "plugin" column set to caching_sha2_password, then the work-around is** to force the default back to mysql_native_password ** .

Edit the my.cnf file and in the [mysqld] section, add code

default-authentication-plugin=mysql_native_password

and restart mysqld.

We'll continue to monitor the prevalence of this issue, and if it's widespread, then we'll include:

IDENTIFIED WITH mysql_native_password BY 'password'

in the CREATE USER section, when applicable.

MySQL queries cause high-load

First thing you should do when suspecting MySQL is the reason of high load is to check MySQL queries, you may do it by executing the following line from the terminal:

mysql --defaults-extra-file=/usr/local/directadmin/conf/my.cnf  -e "show full processlist;" -sss

In case DirectAdmin triggers a high-load notice to the Admin message system, SHOW FULL PROCESSLIST; output will be included in the message. The output is only generated if:

  • MySQL/MariaDB is running.
  • MySQL/MariaDB is running localy (as the queries on a remote mysqld server are not likely relevant in the output). You may verify it via the /usr/local/directadmin/conf/mysql.conf file. It should have host=localhost or no host at all since the default setting is 'localhost'.
Last Updated: