Category Archives: MySQL 5.5

MySQL Enterprise Audit: "[ERROR] Plugin 'audit_log' init function returned error"

Problem

The MySQL Enterprise Audit plugin stops logging to the audit log file /var/log/mysql/audit.log when the MySQL service starts. The following errors may be seen in the MySQL error log file /var/log/mysql/error.log:

 130228  2:39:50 [ERROR] Plugin 'audit_log' init function returned error.
 130228  2:39:50 [ERROR] Plugin 'audit_log' registration as a AUDIT failed.

Solution

When the audit log plugin opens its log file, it checks whether the XML declaration and opening root element tag need to be written and writes them if so. When the audit log plugin terminates, it writes a closing tag to the file.
If the log file exists at open time, the plugin checks whether the file ends with an tag and truncates it if so before writing any elements. If the log file already exists but does not end with or the tag cannot be truncated, the plugin considers the file malformed and fails to initialize. This can occur if the server crashes or is killed with the audit log plugin running. No logging occurs until the problem is rectified.
To deal with this problem, you must either remove or rename the malformed log file:

$ sudo mv /var/log/mysql/audit.log /var/log/mysql/audit.log.`date +%Y%m%d`

Login to the MySQL service as a user with the SUPER privilege. Delete and re-install the audit plugin.

>SELECT * FROM mysql.plugin;
+-----------+--------------+
| name      | dl           |
+-----------+--------------+
| audit_log | audit_log.so |
+-----------+--------------+
1 row in set (0.00 sec)
>DELETE FROM mysql.plugin WHERE name='audit_log';
Query OK, 1 row affected (0.01 sec)
>SELECT * FROM mysql.plugin;
Empty set (0.00 sec)
>INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Query OK, 0 rows affected (0.03 sec)
>SHOW PLUGINS;
+--------------------------+----------+--------------------+--------------+-------------+
| Name                     | Status   | Type               | Library      | License     |
+--------------------------+----------+--------------------+--------------+-------------+
| binlog                   | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| mysql_native_password    | ACTIVE   | AUTHENTICATION     | NULL         | PROPRIETARY |
| mysql_old_password       | ACTIVE   | AUTHENTICATION     | NULL         | PROPRIETARY |
| MEMORY                   | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| MRG_MYISAM               | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| CSV                      | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| MyISAM                   | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| BLACKHOLE                | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| FEDERATED                | DISABLED | STORAGE ENGINE     | NULL         | PROPRIETARY |
| PERFORMANCE_SCHEMA       | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| InnoDB                   | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| INNODB_TRX               | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_LOCKS             | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_LOCK_WAITS        | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_CMP               | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_CMP_RESET         | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_CMPMEM            | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_CMPMEM_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_BUFFER_PAGE       | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_BUFFER_PAGE_LRU   | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| INNODB_BUFFER_POOL_STATS | ACTIVE   | INFORMATION SCHEMA | NULL         | PROPRIETARY |
| ARCHIVE                  | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| partition                | ACTIVE   | STORAGE ENGINE     | NULL         | PROPRIETARY |
| audit_log                | ACTIVE   | AUDIT              | audit_log.so | PROPRIETARY |
+--------------------------+----------+--------------------+--------------+-------------+
24 rows in set (0.00 sec)
>SELECT * FROM mysql.plugin;
+-----------+--------------+
| name      | dl           |
+-----------+--------------+
| audit_log | audit_log.so |
+-----------+--------------+
1 row in set (0.00 sec)

Verify that the audit log file /var/log/mysql/audit.log was recreated and that new MySQL connections are being logged to it.
If the INSTALL PLUGIN statement executed above fails with the output:

>INSTALL PLUGIN audit_log SONAME 'audit_log.so';
ERROR 1123 (HY000): Can't initialize function 'audit_log'; Plugin initialization function failed.

it may be because you did not move the malformed audit log file out of the way as stated above. Try:

$ sudo mv /var/log/mysql/audit.log /tmp

Finally, install the plug-in again:

>INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Query OK, 0 rows affected (0.03 sec)

Hopefully, it will complete successfully this time.

My System Configuration

  • MySQL Enterprise Server 5.5.35

References