echothrust/echofish

Doc enhancement: binlog_format=row

jinnatar opened this issue · 3 comments

jinnatar commented

Installation docs should probably cover that the MariaDB default of binlog_format=statement won't allow the archive prune to run. I changed mine to binlog_format=row and that seems to have worked.

For posterity and google searches, the error received is something like this:
MariaDB [ETS_echofish]> CALL eproc_rotate_archive();
ERROR 1665 (HY000): 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.

gadamo commented

I am unable to reproduce your issue on Ubuntu 16.04, with default mariadb-server package. We would like more information in order to nail this one too:

  • Have you made any special customisation on your database setup?
  • Can you please provide the [mysqld] section of your my.cnf?

Sidenote: until we resolve this, and only as a temporary fix, perhaps MIXED would be better than ROW.

jinnatar commented

Previously I didn't have binlog_format defined, that's the only line I added now to make the rotation work.

The only customization is in what's below:

[mysqld]
user            = srv
pid-file        = /srv/run/mysqld/mysqld.pid
socket          = /srv/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /srv/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking

event_scheduler=on

bind-address            = 0.0.0.0
max_connections         = 100
connect_timeout         = 5
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M
myisam_recover          = BACKUP
key_buffer_size         = 128M
table_open_cache        = 400
myisam_sort_buffer_size = 512M
concurrent_insert       = 2
read_buffer_size        = 2M
read_rnd_buffer_size    = 1M
query_cache_limit               = 128K
query_cache_size                = 64M
log_warnings            = 2
slow_query_log_file     = /srv/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity      = query_plan

log_bin                 = /srv/log/mysql/mariadb-bin
log_bin_index           = /srv/log/mysql/mariadb-bin.index
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_format           = row


default_storage_engine  = InnoDB
innodb_buffer_pool_size = 256M
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
gadamo commented

@Artanicus We have configured a VM matching your setup, using the my.cnf values you provided and binlog_format=statement. We removed all files from /var/lib/mysql/ and ran mysql_install_db to make sure that both mysql and echofish databases are created from scratch.

Nevertheless, we cannot hit the error you do, so it seems we need some more detail to hit your case:

  • A count or approximate estimate of your daily logs volume in archive
    • e.g. select count(*) from archive where received_ts > now() - interval 1 day;
  • The size of your binary log and if it has been rotated
    • Output of du -sh /var/lib/mysql/ib*
  • Output of the following mysql queries:
    • show processlist;
    • show engine innodb status \G;
    • SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

This error has been reported again, this time we will hopefully nail it for good.