Error: Could Not Find First Log File Name in Binary Log Index File

Issue

The connector fails with the following error:

Could not find first log file name in binary log index file

Environment

Connector: Aurora MySQL

Answer

To prevent this error, you need to force the binary log files to rotate earlier by executing the FLUSH BINARY LOGS; command.

Do the following:

1. Set the parameter event_scheduler to ON. This replaces running the SET GLOBAL event_scheduler = ON command. For more information, see Amazon's documentation.

2. Run the following commands from a user that has flush privileges, for example, root:

   DROP EVENT IF EXISTS myevent;  
   CREATE EVENT myevent  
   ON SCHEDULE EVERY 24 hour DO FLUSH BINARY LOGS;
   

3. Confirm the event was created by running the following command:

   SHOW EVENTS;

4. Once completed, a new binlog should be created every 24 hours. You can confirm this by running the following command:

   SHOW BINARY LOGS;

   There should be one file per day with a size that is generally smaller than the max binlog size, as shown below:

   mysql-bin.000197 7631  
   mysql-bin.000198 7631  
   mysql-bin.000199 7631  
   mysql-bin.000200 7631
   

Cause

This error can be caused by one of the following issues:

• The binary log file has a maximum size of 134 MB.
• The number of changes made to the database is not enough to fill up the binlog file within Aurora's default time interval and the file is not rotated.
• The file is rotated without keeping the old one.
• We lose track of where the files are located.