Audit MySQL? no just Crash it :)

Auditing maybe the main part of a corporate structure that wants to know everything. Searching for MySQL auditing plugins we come up to 3 known plugins: 1. MySQL Enterprise Audit (Official one from Oracle) – commercial 2. MariaDB audit plugin for MySQL (version 1.1.6) – free 3. McAfee MySQL audit plugin – free
With a great happiness tried to test this plugins. But wait i don’t want to crash my MySQL just want to install and use these plugins. Let’s begin with MariaDB audit plugin for MySQL — at this time the latest version is 1.1.6 With a great effort in SkySQL site they provide us with a tutorial of how to activate this plugin. Check it: SkySQL Tutorial about Plugin Followed all instructions from tutorial and got a crash of MySQL 5.6.17. So there was an error messages with 2 statement:

    INSTALL PLUGIN server_audit SONAME 'server_audit.so';
    ERROR 2013 (HY000): Lost connection to MySQL server during query 

And

    SET GLOBAL server_audit_logging=ON; 
    ERROR 2013 (HY000): Lost connection to MySQL server during query 

From Web APP side the error message is something more interesting:

    SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet',

This is a same error message that we saw after Errcode: 28 – No space left on device.
Reported but still waiting as open 🙂 — 72437.

So The State of my server is very critical and actually i lost my lovely MySQL 😦 Killed all mysql processes. Restarted MySQL looked at error log and here you are:

Version: '5.6.17-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 140430 11:21:52 server_audit: MariaDB Audit Plugin version 1.1.6 STARTED. 06:21:52 UTC - mysqld got signal 11 ; . . . Thread pointer: 0x18428270 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f63a0d78e18 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6475]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663084]
/lib64/libpthread.so.0[0x385780f500] /lib64/libc.so.6[0x3a2173259f]
/usr/sbin/mysqld[0xabdd60] /usr/sbin/mysqld[0xabe0a0]
/usr/lib64/mysql/plugin/server_audit.so(logger_open+0x10b)[0x7f639bdf806b]
/usr/lib64/mysql/plugin/server_audit.so(+0x4202)[0x7f639bdf8202]
/usr/lib64/mysql/plugin/server_audit.so(+0x5ccf)[0x7f639bdf9ccf]
/usr/sbin/mysqld(_Z23initialize_audit_pluginP13st_plugin_int+0x4d)[0x68f6ad]
/usr/sbin/mysqld[0x6ea481]
/usr/sbin/mysqld(_Z20mysql_install_pluginP3THDPK19st_mysql_lex_stringS3_+0x43f)[0x6eee7f] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0xf04)[0x6deb14]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x32f)[0x6e2b3f]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x140b)[0x6e403b] 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0xcf)[0x6b0f3f]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6b1067]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xabd8da]
/lib64/libpthread.so.0[0x3857807851] /lib64/libc.so.6(clone+0x6d)[0x3a216e811d] . . . 
    2014-04-30 11:21:54 19033 [Note] InnoDB: Starting crash recovery.
    2014-04-30 11:21:54 19033 [Note] InnoDB: Reading tablespace information from the .ibd files... 
    2014-04-30 11:21:55 19033 [Note] InnoDB: Restoring possible half-written data pages 2014-04-30 11:21:55 19033 [Note] InnoDB: from the doublewrite buffer... InnoDB: Doing recovery: scanned up to log sequence number 69056068973 InnoDB: Transaction 246648984 was in the XA prepared state. InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 0 row operations to undo InnoDB: Trx id counter is 246649344 
    2014-04-30 11:21:55 19033 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed [/code] Result is was: MariaDB Audit Plugin Crashed my MySQL 5.6.17... Will i use it? Last step was: 140430 14:40:53 server_audit: STOPPED

with all necessary steps that stated here: McAfee Audit Plugin So no more words and just pasting error log portion:

2014-04-30 19:00:48 14571 [Note] Audit Plugin: starting up. Version: 1.0.4 , Revision: 459 (64bit). AUDIT plugin interface version: 769 (0x301). 
2014-04-30 19:00:48 14571 [Note] Audit Plugin: hot patch for: open_tables (0x69bd00) complete. Audit func: 0x7fb366fcad30, Trampoline address: 0x7fb366fcc060 size: 15.
2014-04-30 19:00:48 14571 [Note] Audit Plugin: Done initializing sql command names. status_vars_index: [141], com_status_vars: [0x1215400]. 
2014-04-30 19:00:48 14571 [Note] Audit Plugin: Init completed successfully. 2014-04-30 19:00:48 14571 [Note] Recovering after a crash using /var/lib/mysql/data/mysql-bin 
2014-04-30 19:00:48 14571 [Note] Starting crash recovery... 
2014-04-30 19:00:48 14571 [Note] Crash recovery finished. 
2014-04-30 19:00:48 14571 [Note] Server hostname (bind-address): '*'; port: 3306 2014-04-30 19:00:48 14571 [Note] IPv6 is available. 
2014-04-30 19:00:48 14571 [Note] - '::' resolves to '::'; 
2014-04-30 19:00:48 14571 [Note] Server socket created on IP: '::'. 14:00:48 UTC - mysqld got signal 11 ; Thread pointer: 0x26e2b70 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7fff90422a78 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6475]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663084]
/lib64/libpthread.so.0[0x35b6e0f710]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x0)[0x69bd00] 
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x44)[0x69cb74] /usr/sbin/mysqld(_Z10acl_reloadP3THD+0x28f)[0x67c9cf]
/usr/sbin/mysqld(_Z8acl_initb+0x120)[0x67e9d0]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0xa26)[0x59fc96]
/lib64/libc.so.6(__libc_start_main+0xfd)[0x35b6a1ed1d] /usr/sbin/mysqld[0x591949] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 0 Status: NOT_KILLED

Conclusion: 2 of 3 Audit Plugins crashed MySQL 5.6.17. And now Triggers will make us happy or Purchase a commercial product

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s