Mysql – What could cause a record to appear in the MySQL binary log but not the original table

logsMySQLmysql-5.5trigger

I have a web application (CiviCRM) that has the option to enable binary logging, which I've done. We use this software to record donations, including automatic recurring donations. Our payment processor sends a POST request to a particular URL, which SHOULD create a new donation record in the db. Instead, it's creating a record in the log table with log_action of Insert, but there's no corresponding record in the original table – and no Delete record in the log.

What circumstances might cause this? Where should I begin troubleshooting? There's no clustering/relaying – there are a handful of triggers that act on this record, which I'm eyeing suspiciously, but would appreciate input.

MySQL version is 5.5.44-0+deb7u1.

Best Answer

It sounds like you lack the SUPER privilege (global permission).

When you connect to MySQL, run SHOW GRANTS;

If you see more than one line, you do not have SUPER.

What does having SUPER give you ?

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 20.7, “Binary Logging of Stored Programs”.

This also means you may only have database level grants for all users.

How can you tell ?

Login as root@localhost and run this

SELECT
    A.column_name GlobalPrivilege,
    IFNULL(B.column_name,'Not at DB Level') DBPrivilege
FROM
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user'
AND column_name LIKE '%priv') A
LEFT JOIN
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='db'
AND column_name LIKE '%priv') B
USING (column_name);

You should see something like this

+------------------------+-----------------------+
| GlobalPrivilege        | DBPrivilege           |
+------------------------+-----------------------+
| Select_priv            | Select_priv           |
| Insert_priv            | Insert_priv           |
| Update_priv            | Update_priv           |
| Delete_priv            | Delete_priv           |
| Create_priv            | Create_priv           |
| Drop_priv              | Drop_priv             |
| Reload_priv            | Not at DB Level       |
| Shutdown_priv          | Not at DB Level       |
| Process_priv           | Not at DB Level       |
| File_priv              | Not at DB Level       |
| Grant_priv             | Grant_priv            |
| References_priv        | References_priv       |
| Index_priv             | Index_priv            |
| Alter_priv             | Alter_priv            |
| Show_db_priv           | Not at DB Level       |
| Super_priv             | Not at DB Level       |
| Create_tmp_table_priv  | Create_tmp_table_priv |
| Lock_tables_priv       | Lock_tables_priv      |
| Execute_priv           | Execute_priv          |
| Repl_slave_priv        | Not at DB Level       |
| Repl_client_priv       | Not at DB Level       |
| Create_view_priv       | Create_view_priv      |
| Show_view_priv         | Show_view_priv        |
| Create_routine_priv    | Create_routine_priv   |
| Alter_routine_priv     | Alter_routine_priv    |
| Create_user_priv       | Not at DB Level       |
| Event_priv             | Event_priv            |
| Trigger_priv           | Trigger_priv          |
| Create_tablespace_priv | Not at DB Level       |
+------------------------+-----------------------+

As shown, SUPER does not exist for a DB Level user.

You can run this

SELECT user,host FROM mysql.db;

to see your DB Level users.

Please also look into your DB level user having the TRIGGER privilege, which can allow a database level user to create, drop, or execute triggers.

Your real problem comes from Drupal's required settings for CiviCRM

You can look into doing on of the following:

  • make all users global users and give them SUPER (not secure)
  • give away the TRIGGER privilege to all DB users
    • UPDATE mysql.db SET trigger_priv = 'Y';
    • FLUSH PRIVILEGES;

GIVE IT A TRY !!!

Related Question