• Blog
  • Get Help Now!
  • Customer Portal

BlueGecko Denmark

  • Why you need us
    • Why act now
    • Why are we unique
  • What we do
    • Products Supported 24/7
      • MySQL
      • Postgres
      • SQL Server
      • Open Source
      • OS & Storage
  • How we do it
    • Consulting
    • System Monitoring
    • Security
  • Support
    • Blog
      • Oracle
      • MySQL
      • News & Events
      • Oracle Applications
      • Amazon Web Services
      • Jeremiah Wilton’s Oradeblog
      • OurSQL Community Podcast
      • Remote DBA
    • Team viewer
    • Whitepapers
  • About Us
    • Philosophy
    • The Blue Gecko Teams
    • Employment Opportunities
    • Partners
    • News
    • Press
  • Contact Us

September 2, 2011 by pgalbraith

About MySQL storage engines and replication

I know most articles are more about DBA issues, however, being a MySQL DBA means that you have several hats that you can where– data architect, application developer, MySQL source developer, and often good old fashioned DBA. I recently had an interesting problem with a particular project I was working on that required the MySQL source code developer hat that I used to wear much more often during the black vodka era of working for MySQL AB.

This was case of a custom storage engine a client has, and whenever I would try to use statement-based replication, I would encounter an error upon running DML statements on the master:

ERROR 1598 (HY000): Binary logging not possible.
Message: Statement-based format required for this statement,
but not allowed by this combination of engines

Despite having worked on several storage engines, written Federated/FederatedX, I couldn’t remember what about a storage engine pertains to replication, nor did it make sense to me that the storage engine would have anything to do with it since binary logging occurs at a higher level than at the storage engine.

I was talking to Monty about it, and he pointed me to look at ha_myisam.cc:

ha_myisam::ha_myisam(handlerton *hton, TABLE_SHARE *table_arg)
  :handler(hton, table_arg), file(0),
  int_table_flags(HA_NULL_IN_KEY | HA_CAN_FULLTEXT | HA_CAN_SQL_HANDLER |
                  HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE |
                  HA_DUPLICATE_POS | HA_CAN_INDEX_BLOBS | HA_AUTO_PART_KEY |
                  HA_FILE_BASED | HA_CAN_GEOMETRY | HA_NO_TRANSACTIONS |
                  HA_CAN_INSERT_DELAYED | HA_CAN_BIT_FIELD | HA_CAN_RTREEKEYS |
                  HA_HAS_RECORDS | HA_STATS_RECORDS_IS_EXACT),
   can_enable_indexes(1)
{}

How one forgets simple but important things over time, that being me! These flags are very important and determine the capabilities of a storage engine. I will post about what each is at a later time. Suffice to say, I had searched Google to no avail, and by posting this article, if you are trying to get replication working for a given storage engine, this will come up! The important flags you see here are HA_BINLOG_ROW_CAPABLE and HA_BINLOG_STMT_CAPABLE. I never used to pay as much attention to these particular flags when developing Federated because I would often use flags from another storage engine and was focused on flags that pertained more to what Federated could do and could not do. However, these flags are required if you want replication to work.

The flags in the client storage engine did have HA_BINLOG_ROW_CAPABLE but not HA_BINLOG_STMT_CAPABLE. Additionally, the flag HA_HAS_OWN_BINLOGGING was present. This is a show-stopper. The only engine that has this is NDB cluster.

No harm done, with the correct flags added, the custom storage engine now replicates!

Happy storage engine hacking!

Related posts:

  1. OurSQL Episode 45: Hackathon Success! (MySQL -> MongoDB replication)
  2. Semi-Synchronous Replication in MySQL 5.5
  3. MySQL 5.5 GA announcement

Filed Under: MySQL

Categories

  • Amazon Web Services
  • Configuration Management
  • downtime
  • Drizzle
  • ebs
  • Education
  • elastic block store
  • elastic compute cloud
  • hosting
  • hot backup
  • ignorance
  • Infrastructure
  • IOUG
  • Jeremiah Wilton's Oradeblog
  • misconception
  • misconceptions
  • Monocle
  • MySQL
  • MySQL Council
  • News & Events
  • Oracle
  • Oracle Applications
  • Oracle Data Guard
  • Oracle Database
  • Oracle Database Appliance
  • Our People
  • OurSQL Community Podcast
  • outage
  • parallel
  • performance
  • podcast
  • proof
  • Remote DBA
  • replication
  • S3
  • Security
  • SQL Server
  • System monitoring
  • Uncategorized

Contact Blue Gecko

  • This field is for validation purposes and should be left unchanged.

Get Our Newsletter

  • This field is for validation purposes and should be left unchanged.

The Fine Print

  • Privacy Policy
  • Terms of Use
  • When is remote DBA the right solution
  • What are remote DBA services
  • Get Help

Blog Categories

  • 24/7 support
  • Blue Gecko 360° Services
  • Products Supported 24/7

Virtual runs

Contact Blue Gecko

Blue Gecko A/S
Slotsgade 21
DK – 4200 Slagelse
Denmark
Phone: (DK) +45 70 60 51 20

Networks

  • Email
  • Twitter

© Blue Gecko Group