Yesterday, our MySQL instance running on Amazon RDS hung and we were forced to reboot. After rebooting we noticed additional rows in one of our tables that were not there before the crash.
The rows were originally "supposed" to be written to this table via a trigger when updates occurred on another table. This was an "Audit Log" table. There is a datetime column on the "Audit Log" table that indicated when the row was originally "supposed" to be written. These dates stretch up to 3 years ago! About 7,000 rows were added after rebooting! The trigger generates a date value when the row was actually written to the "Audit Log" table and that had yesterday's date!
The RDS MySQL log had this snippet in it during reboot.
InnoDB: Log scan progressed past the checkpoint lsn 181143261075
150922 17:07:44 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 181143275223
150922 17:07:51 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 40603, file name /rdsdbdata/log/binlog/mysql-bin-changelog.268331
150922 17:07:51 InnoDB: Waiting for the background threads to start
150922 17:07:52 InnoDB: 5.5.40 started; log sequence number 181143275223
150922 17:07:52 [Note] Recovering after a crash using /rdsdbdata/log/binlog/mysql-bin-changelog
150922 17:07:52 [Note] Starting crash recovery...
150922 17:07:52 [Note] Crash recovery finished.
Clearly MySQL re-applied these "updates" because of the crash. My questions are the following:
- How in the world would this innoDB bin transaction log have records that should have been written years ago?
- If the trigger failed for these rows the update SQL statement would have failed, correct? Our code is configured to then rollback the transaction.
I'm my no means a DBA, and these might be dump questions but I was just extremely confused to have such old records appear!
MySQL 5.5.40. InnoDB
Aucun commentaire:
Enregistrer un commentaire