Database Troubleshooting

This document covers troubleshooting of the MariaDB database server included with the Access Anywhere appliance.

There are two main deployment options:

• Local database - database running on same virtual machine as Access Anywhere Engine.
• Remote database - Access Anywhere Engine connecting to a database over a network.

A remote database could be:

• An instance of the Access Anywhere appliance configured to only run the bundled MariaDB database.
• An installation of MariaDB or compatible database
• An external database-as-a-service compatible with MariaDB

Before you begin troubleshooting and checking the database server you should have the following information:

• Machine passwords for smeconfiguser and root (will also allow access to a local database)
• Remote database - credentials for database access

Access Anywhere appliance includes a MariaDB 10.4 database server.

Supported databases (as of Access Anywhere 1705.00 through at least 1803.03)

• MariaDB 10.4

These compatible cloud database are supported. Please note versions.

• Amazon RDS for MariaDB 10.4

These cloud database versions are NOT supported:

• MySQL 5.7
• Amazon Aurora
• Amazon RDS for MySQL 5.7
• Google Cloud SQL for MySQL 5.7

To determine the version of the database start mysql and run the command:

select version();

The database uses the configuration file /etc/my.cnf which uses files from /etc/my.cnf.d/.

The data files are located at /var/lib/mysql/.

To check what database an Access Anywhere Engine is using use the ffconfig command:

ffconfig list | grep db

It returns the following settings:

  'dbuser' => 'smestore',
  'dbpassword' => 'besp5fyx',
  'dbname' => 'smestorage',
  'dbhost' => 'localhost',
  

If you are using the default settings (dbhost localhost) you can log into the database server from root

mysql smestorage

Otherwise pass the values of the parameters to mysql. You will be prompted for the password.

mysql -h host -u user smestorage

To check if server logs are enabled:

SHOW VARIABLES LIKE "general_log%";
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| general_log      | OFF      |
| general_log_file | demo.log |
+------------------+----------+

These can be enabled until the next server reboot with:

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file='demo.log';

For an absolute path the log can be found at:

/var/lib/mysql/demo.log

To persist changes across reboot use /etc/my.cnf.d/server.cnf.

To enable log of slow queries you can use SET GLOBAL. Changes made with SET GLOBAL will not persist across server restarts.

SET GLOBAL slow_query_log = 'ON';

Log out of mysql and log back in again to check what the new setting is:

SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

The location of the log can be found with this command. If a relative path is given the location is relative to the directory /var/lib/mysql.

SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+--------------------+
| Variable_name       | Value              |
+---------------------+--------------------+
| slow_query_log_file | appliance-slow.log | 
+---------------------+--------------------+
 

The location can be changed with:

SET GLOBAL slow_query_log_file = 'slow_query.log';

The default long query time is 10 seconds:

SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

This can be changed with:

SET GLOBAL long_query_time = 5;

To persist changes across reboot add these to /etc/my.cnf.d/server.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow_query.log
long_query_time = 5

Check the database is up by running this command on the machine hosting the database server:

systemctl status mariadb

You should see a few lines of output including one that says, “Active: active (running) ”. If you do not then the service is not running. This will prevent the Cloud File Manager from working.

Start the database on the machine hosting the database server:

systemctl start mariadb

For security the database included with Access Anywhere cannot be connected to over a network. That is, it can only be accessed by processes running on the same machine.

This section covers settings that need to be changed for the database to be connected to from another machine. Please consult your IT or network administrator for other connectivity issues.

If the database is remote (Access Anywhere connects to the database over a network) verify network access from the server(s) hosting Access Anywhere Engine(s):

curl hostname:3306

If the port is open success this should return something like:

curl: (56) Recv failure: Connection reset by peer
5.5.56-MariaDB?.......

If the port is not accessible an error message like the following will be seen:

curl: (7) Failed to connect to 10.120.10.194 port 3306: Connection refused

This could be because * Database is not listening on an external port * The server port is not open

If the user does not have permission to connect from a host:

'172.16.79.1' is not allowed to connect to this MariaDB 

Out of the box the database on the appliance is configured to only accept connections from local clients (on the same virtual machine). This can be changed with the bind-address setting. First, assuming the out of the box appliance settings haven’t been changed, edit localhost.cnf as root:

vi /etc/my.cnf.d/localhost.cnf

Comment out bind-address 127.0.0.1 which was telling mysql to only accept local connections. Adding a line with :: allows incoming IPv4 and IPv6 connections. A setting of 0.0.0.0 can be used instead, which is also the default. It will allow IPv4 but not IPv6 connections:

# NAA managed file, do not edit directly!
[mysqld]
#bind-address = 127.0.0.1
bind-address = ::

Then restart the database:

systemctl restart mariadb

Out of the box iptables on the appliance is not configured to accept connections to port 3306.

vi /etc/sysconfig/iptables

Then as root add the following line after line 21:

-A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

Then restart iptables:

systemctl restart iptables

You can log into the local database through the command:

mysql

To create a user for external access:

CREATE USER 'workbench'@'%' IDENTIFIED BY 'password';;
GRANT ALL PRIVILEGES ON *.* TO 'workbench'@'%';
FLUSH PRIVILEGES;

We typically recommend tools from the hypervisor or third-parties to backup the appliance and database. In some cases it’s helpful to use native tools.

The Access Anywhere database is named smestorage and stores data in of InnoDB and MyISAM tables.

For more information see https://mariadb.com/kb/en/library/backup-and-restore-overview/.

For smaller systems create a logical backup of the smestorage database using the command mysqldump run as root. It creates a set of SQL statements that can be executed to replicate the original database schema and data:

mysqldump smestorage > smestorage.sql

Restoring or loading a database run mysql in script mode as root:

mysql smestorage < smestorage.sql

For larger databases a physical backup is recommended. The backup file will be smaller and the database can be recovered more quickly than from SQL statements.

Note: There are some innodb tables that may have problems participating in backup.

mysqlhotcopy performs a physical backup duplicating the on disk files. It can only be run on the same machine as the database and creates multiple files in the target folder:

mysqlhotcopy smestorage ~/backup

From the mysql program:

show full processlist;

If there are processes waiting on locks these will be shown along with any active processes that we can presume are holding the locks. Review the Time field to confirm the longest running query and run the following command to kill it:

kill <thread_id>;

### Number of Connections

Execute mysqladmin from the command line as root:

$ mysqladmin extended-status | grep Threads
| Threads_connected                        | 3           |
| Threads_created                          | 3176        |
| Threads_running                          | 1           |

The database data is located at /var/lib/mysql. To check space on this volume (and others):

df -h

This volume may run out of space for a number of reasons. To find the top 50 files above 10M:

find /var/lib/mysql -xdev -type f -size +10M -exec du -sh {} ';' | sort -rh | head -n50

Either this space can be released or the partition needs to be extended.

The following tables are most likely to become full due to regular usage or runaway processes:

• or_logs - Audit logs. Can be cleared:
  • Web File Manager (GUI) - Organization > Audit Logs > Clear Audit Logs (internally 500 at a time)
  • MySQL (be sure to pause mariadb via systemctl)- Preserve Old Logs
    • RENAME TABLE or_logs TO or_logs_date2021; CREATE TABLE or_logs LIKE or_logs_date2021;
  • MySQL - Remove Old Logs
    • TRUNCATE or_logs;
• us_logs - Login audit log. Can be cleared:
  • MySQL (be sure to pause mariadb via systemctl))- Preserve Old Logs
    • RENAME TABLE us_logs TO us_logs_date2021; CREATE TABLE us_logs LIKE us_logs_date2021;
  • MySQL - Remove Old Logs
    • TRUNCATE us_logs;
• fi_files - File metadata. Critical
• io_temp/io_tempremovefile - Used in provider sync.

Though not enabled by default a customer may have turned on binary logs while setting up slave replications. The files will look something like:

1.1G    /var/lib/mysql/master-tx-log-bin.000011
1.1G    /var/lib/mysql/master-tx-log-bin.000010

To purge these logs see

https://mariadb.com/kb/en/library/sql-commands-purge-logs/
https://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html

Another way to deal with a full database is to allocate more space. The Access Anywhere database, by default, uses the local filesystem for storage. Allocating more space to the database involves increasing the space available on the filesystem. This is typically done by extending one of the disk partitions of the virtual machine.

Use the following command to determine the disk used for the mysql volume

 $  blkid | grep sme-mysql
 /dev/sdb1: LABEL="sme-mysql" UUID="af240e06-f1d2-4dd1-af74-daf6e7803413" TYPE=“ext4"  

In this case the Filesystem is

/dev/sdb1

First stop the database that is using the partition (run as root):

systemctl stop mariadb

And unmount the partition:

umount /var/lib/mysql

Next we will use the parted command to expand the filesystem to all available space on the disk

parted -s /dev/sdb resizepart 1 100%

Next unmount:

umount /var/lib/mysql

Then run a check:

e2fsck -f /dev/sdb1

Extend the partition

resize2fs /dev/sdb1

Run a check on the larger partition:

e2fsck -f /dev/sdb1

Remount

mount -a

Check new size

df -h

Restart database:

systemctl start mariadb

From the command line:

mysqlcheck smestorage

From mysql shell run repair table on each corrupted table.

REPAIR TABLE  tbl_name [, tbl_name]

You can also run for all MyISAM tables:

cd /var/lib/mysql/smestorage
myisamchk --force --fast --update-state --key_buffer_size=48G --read_buffer_size=128K --write_buffer_size=128K --sort_buffer_size=48G *.MYI 

See also: https://mariadb.com/kb/en/repair-table/

Access Anywhere HA/DR environments set up prior to v2006 did not leverage GTID for database replication. Starting with v2006 and greater, GTID is the recommended method for data migration.

As the root user run the following on every database node:

 echo "gtid_strict_mode=1" >> /etc/my.cnf.d/localhost.cnf
 systemctl restart mariadb

Please use this only if you currently have replication running and both nodes are currently in sync.

On both nodes (secondary first than primary), in the 'mysql' console run the following:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST=<ip of the other db node>, MASTER_USER="repl_user", MASTER_USE_GTID=slave_pos;
START SLAVE;

Once you have made the configuration change above and restarted MariaDB, you can proceed with reseeding the databases and re-enabling replication.