CODEWITHSUNDEEP
pythonmysqldjangobinlog
Derek
Derek

Reputation: 21

Using python Django and Mysql. Data won't inserted into MySQL due to Binlog

When I use django to design a website and delopy, I find I have some trouble if I use MySQL that the Binlog is activate( format:STATEMENT).

There are my settings for this deployment:

The first step, I need to migrate my models to databese, so I input like this:

python manage.py migrate

And traceback like this:

    django.db.utils.OperationalError: (1665, '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.')

After searching in Internet, I know the one way to fix it is to change the Binlog format, which works perfectly for me.

But I still want to know how to fix it if I don't want to change my Binlog configuration. I think Django can support this format for Binlog.

Upvotes: 2

Views: 1712

Answers (3)

Mark Wolfman
Mark Wolfman

Reputation: 163

I ran into a similar problem and fixed it by changing the isolation level to "repeatable read". You really only need it to apply the migrations then you can probably(?) remove it. In settings.py I changed my DATABASES setting to:

DATABASES = {
    'default': {                                                                                         
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db_name',                                                                                 
        'USER': 'db_user',
        'PASSWORD': 'db_password',
        'HOST': 'db_host',
        'PORT': 'db_port',
        'CONN_MAX_AGE': db_conn_max_age,
        'OPTIONS': {                                                                           
            'isolation_level': "repeatable read",                                                        
        },                                                                                     
    }                                                                                            
}

then ran python manage migrate, then remove the OPTIONS entry. Pay attention to these risks.

Upvotes: 2

Derek
Derek

Reputation: 21

After my searching yesterday, I have get a summery solutions for this problem.

  • First solution: Change the Binlog format.
    As Hariprakash said, we can change the Binlog format and fix this problem. In most of cases, it is the best way to fix it.
    You can change the setting in two ways:
    1. Hariprakash ways, but it only influences the settings in session. When you shut down the session, the seetings will be lost.
    2. You can revise Mysql settings file my.cnf
      In my.cnf, you need to add log_bin=mysql-bin and set the bin_format=ROW or binformat=MIXED which based on your choice. And then, you need to restart Mysql service. That will be effective forever.
  • Second solution: Change the default storage engine for Mysql
    Before you revise default storage engine, you need to check how many kinds of your supporting engine.
    show engines;
    And you can change the default storage engine.
    • In settings file my.cnf, you should insert default-storage-engine=INNODB after [mysqld], and remember you must insert server-id=1, this number is used for recognize your host. Do not make other host have same number.
    • Also you can use the Mysql version for 5.5.4 or older, which uses MyISAM as default storage engine according to Django document.

Although there are two ways to fix the problem, but I think there are not the best solution. If someone have an better answer which do not revise my.cnf, please tell me.

Upvotes: 0

Hariprakash Sambath
Hariprakash Sambath

Reputation: 179

Can you try to set Binlog format as ROW

for setting the format please use this,

mysql> SET GLOBAL binlog_format = 'ROW';

After that use below query for confirming

mysql> show variables like 'binlog_format';

Upvotes: 1

Related Questions