Troubleshooting

Guy Lichtman edited this page Jan 26, 2015 · 12 revisions

If you are experiencing an installation issue it is recommended to try out the latest dev-snapshot build to see if the issue is already resolved in the trunk. Dev snapshot builds are available at: https://bintray.com/mcafee/mysql-audit-plugin/dev-snapshot (follow the latest version link and then the "Files" link).

If the AUDIT plugin fails to install, error messages will be logged in the MySQL error log. Log file location can be queried in MySQL by running the following command:

show global variables like 'log_error';

Check the error log for AUDIT plugin messages to better understand what is happening during the install process.

Checksum Validation

AUDIT plugin uses compiled offsets for accessing built-in MySQL data structures that are not exposed through a consistent API. The use of offsets is used for supporting multiple MySQL versions with a single binary distribution. The plugin will use the proper offsets according to the MySQL server version. To verify that the plugin is running on a MySQL version which we know and tested the offsets for, we've added a checksum verification on the mysqld binary. Thus, if a MySQL server distribution, which we haven't extracted the offsets and checksum for, is being used, the plugin will fail installation as the checksum of mysqld will not match a known checksum. Usually, the offsets between different MySQL distributions are the same for a specific version. Thus, it is possible to run the AUDIT plugin with the offsets of a specific MySQL server version without checksum verification. If checksum verification fails the log will contain an error of the form:

Audit Plugin: Couldn't find proper THD offsets for: MYSQL_VERSION

In this case, you can try to disable the checksum verification. To disable checksum verification: add to the MySQL option file (my.cnf) at the [mysqld] section the option:

audit_validate_checksum=OFF

Then try installing the AUDIT plugin either via plugin-load configuration option (restart) or by issuing the INSTALL PLUGIN statement.

Note: we recommend adding to my.cnf also the plugin-load configuration option:

plugin-load=AUDIT=libaudit_plugin.so

Otherwise when mysqld is restarted it won't recognize the audit_validate_checksum option and fail starting up.

When checksum validation is turned off, AUDIT plugin will still search for valid offsets according to the MySQL version and perform basic validation on the offsets.

Note: Starting with version 1.0.3 a new configuration parameter was introduced: offsets_by_version. It controls if to load and validate offsets according to the MySQL version (default: on) and audit_validate_checksum controls if to search for offsets according to predefined checksums. Meaning that by default, AUDIT plugin will also try to find valid offsets according to the MySQL version, if checksum validation does not pass.

If you still see in the error log the message:

Audit Plugin: Couldn't find proper THD offsets for: MYSQL_VERSION

This means that either offsets for the exact MySQL version being used are not included in the build or that the offsets included didn't pass basic validation as a different distribution is being used. At this stage we recommend extracting the offsets (see next section).

Offset Extraction

AUDIT plugin supports setting the offsets via configuration. So if the offsets are not included with the build it is possible to extract the offsets and configure AUDIT plugin to use these offsets.

If the MySQL distribution includes debug symbols (most builds from mysql.com include debug symbols) it is possible to extract the offsets using a simple script.

Download the offset-extract.sh script from: https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh

Note: The offest-extract.sh script changed as the plugin added additional offsets. If you are using a build earlier than 1.0.8-515, you will need to use the script from the 1.0.7 tag: https://raw.githubusercontent.com/mcafee/mysql-audit/v1.0.7/offset-extract/offset-extract.sh . Further more, if you are using a build earlier than 1.0.4-451, you will need to use the script from the 1.0.3 tag: https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh

Note: GDB is required to use this script.

Change the permission of the file to executable:

chmod +x offset-extract.sh

Then run the following:

./offset-extract.sh <path to mysqld> [optional mysqld symbols]

From the output you will need to use the number offsets later on.

Sample output:

 ~# ./offset-extract.sh /usr/sbin/mysqld
 //offsets for: /usr/sbin/mysqld (5.1.41-community)
 {"5.1.41-community","6ccf4357688d8e46bfcb4443966970b0", **6200, 6264, 3672, 3944, 88, 2048**},

Then add to the mysqld configuration file (usually /etc/my.cnf) the following under the [mysqld] section:

audit_offsets=<offsets extracted above>

The offset section for example should look like this:

audit_offsets=6200, 6264, 3672, 3944, 88, 2048

Then try installing the AUDIT plugin either via plugin-load configuration option (restart) or by issuing the INSTALL PLUGIN statement.

Note: Some distributions (such as Percona Server) contain the mysqld debug symbols as a separate binary, which requires installing an additional package (rpm/deb). For example for Percona Server on RedHat you will need to install: Percona-Server-55-debuginfo-5.5.30-rel30.1.465.rhel6.x86_64.rpm. Once installed pass to the offset-extract.sh script also the path to the mysqld symbols file. Symbols file must be the same version as the target mysqld. For example:

./offset-extract.sh /usr/sbin/mysqld /usr/lib/debug/usr/sbin/mysqld.debug

Notice that the mysqld debug symbols may be named also: /usr/lib/debug/usr/sbin/mysqld (no .debug). Make sure not to confuse this with mysqld-debug, which is a separate executable.

Offsets on Debian distributions: On Debian (and Ubuntu) distributions debug symbols are not shipped with the mysql server distribution. There is need to compile from source in order to extract the offsets. Example steps for compiling from source with debug symbols:

  • Download source code by running: apt-get source mysql-server
  • Extract source: dpkg-source -x mysql-<version>.dsc
  • Change into extract dir: mysql-<version>
  • Modify debian/rules file to add "-g" flag to CFLAGS and CXXFLAGS passed to cmake. Example diff of modifications:
#diff debian/rules debian/rules.org
93c93
<               CFLAGS=$${MYSQL_BUILD_CFLAGS:-"-g -O2 -DBIG_JOINS=1 ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
---
>               CFLAGS=$${MYSQL_BUILD_CFLAGS:-"-O2 -DBIG_JOINS=1 ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
95c95
<               CXXFLAGS=$${MYSQL_BUILD_CXXFLAGS:-"-g -O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \
---
>               CXXFLAGS=$${MYSQL_BUILD_CXXFLAGS:-"-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti ${FORCE_FPIC_CFLAGS} -fno-strict-aliasing"} \

  • Build mysql with "nostrip" option: DEB_BUILD_OPTIONS="nostrip" dpkg-buildpackage -b -uc
  • Resulting mysqld file available at: builddir/sql/mysqld now contains debug symbols and you can extract the offsets from this binary.