9

I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session.

How do I make this setting stick after stopping and restarting MySQL?

NOTE: I am on version 5.5.19 of MySQL (with Moodle) and I just upgraded from MyISAM to InnoDB and therefore need this new setting.

I have tried adding these two commands (separately) to my.cnf: binlog-format=ROW and binlog_format=ROW and neither work. I have stopped and restarted MySQL every time I changed the my.cnf.

The error I get without this being set is the nefarious error message:

Debug info: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
Constance
  • 91
  • 1
  • 1
  • 3

2 Answers2

17

I think your problem may stem from where you wrote the directive

Make sure you place the option under the [mysqld] header in /etc/my.cnf

[mysqld]
binlog_format = ROW

If you place it under any other header, the mysqld process will not see it.

Give it a Try !!!

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

3 Values for binlog_format 

[mysqld]
binlog_format = ROW

[mysqld]
binlog_format = STATEMENT

[mysqld]
binlog_format = MIXED
Alternatifs
  • 1