750506 - Need to change mysql1.metrics binlog format back to ROW to support JIRA

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Daniel Einspanjer [:dre] [:deinspanjer]]

We ran into a problem with Hive and the new default binlog format of MySQL 5.1.(whatever we upgraded to), but based on this KB article, we can't easily tweak the config of Jira to fix it. :/

http://confluence.atlassian.com/display/JIRA/JIRA+Cannot+Create+Issues+when+Using+MySQL+with+Binary+Logging

Sheeri,  please take a look at this and let me know if you think it will be okay for us to change this setting on mysql1 and restart tomorrow morning.

Comment 1

[Sheeri Cabral [:sheeri]]

The problem wasn't that the default binlog format changed. The only thing that changed was that MySQL started giving warnings for problems that were problems before, but MySQL wasn't giving warnings for. 

The real problem is that JIRA uses a non-standard isolation level. Can you configure JIRA to use REPEATABLE_READ? Why does JIRA require a system that will do dirty reads?

See http://sheeri.com/content/isolation-%2526amp%3B-concurrency for more information about the different isolation levels.

Comment 2

[Sheeri Cabral [:sheeri]]

(the default isolation level in MySQL has always been "repeatable read", and the default binlog format has always been "statement")

Comment 3

[Daniel Einspanjer [:dre] [:deinspanjer]]

I spent a bunch more time looking at this, it sounds from the bugs linked in the Jira KB that there was some setting that prior to the MySQL version we are now on was set to MIXED instead of pure STATEMENT?

Anyway, there doesn't appear to be any suitable changes we can make to JIRA to get around this limitation.

If we can reconfigure our existing MySQL instance to use ROW level binlogging, then we should do that.  If that is detrimental to either the replication or performance, then we should see about spinning up a second instance of MySQL to host the JIRA data by itself so we can configure that as needed.

What are your thoughts Sheeri?

Comment 4

[Sheeri Cabral [:sheeri]]

mysql> set global binlog_format=MIXED;
Query OK, 0 rows affected (0.00 sec)

I just set that on mysql1 and mysql2, and put the right variable setting in /etc/my.cnf. Let me double-check after 30 mins that puppet won't overwrite that, and then we can probably close this.

Existing connections haven't changed, so you have to re-connect.

Comment 5

[Sheeri Cabral [:sheeri]]

puppet overwrote that. :( Will figure out later, note that the currently running config is OK:

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

Comment 6

[Sheeri Cabral [:sheeri]]

This has been updated in puppet, so it won't overwrite it (since the end of May we put the variable into puppet, because AMO also uses binlog_format=MIXED.