MySQL is having a very strange issue and we are unable to figure out. So I need expert advice to figure out the serious problem. I have tried to provide all possible details. But you can ask for more if you require more information.
We have one MySQL table(ticket) in a different database which got records inserted by triggers from some others databases tables(application databases). Before going further let me introduces database design and table structure.
We have more than 600 database tables with similar table schema with InnoDB engine.
and so on....600+
We have after_insert and after update_trigger written to application database table. so as soon any record inserted/updated from the application side we got a track in logdb.tbl1 table. (where we are managing ticket base queue for further processing )
Information about logdb.tbl1
We are having around 3 to 4 lacks records in this table/day current records in tables 12,273,398 ( 12 millions ) we are purging 1-month-old data from the tables regularly but since a month or 15 days we are not purging it.
'serialid', 'bigint(20)', 'NO', 'PRI', NULL, 'auto_increment' 'requestunkid', 'bigint(20)', 'NO', 'MUL', NULL, '' 'fordate', 'date', 'YES', '', NULL, '' 'databasename', 'varchar(255)', 'YES', '', NULL, ''
We have one process on another server which selects data from logdb.tbl1 process and inserts those rows to another database server.
We are picking data in limit 100 from tickets.channelupdates and insert to another database server. to track the pending queue we are finding max(serialid) from another database server and select next 100 records from ticket.channelupdates. and sometimes it skipped some records to select.
| logdb.tbl1 | serialid 78887794 78887795 78887796 78887797 78887798 78887799 78887800 78887801 78887802 .......
But when we run select, it gives a result with some missing rows.. (eg. it gives 78887794,78887795,78887796,78887800,78887801,78887802) here 78887797 to 78887800 records are missing. but when we run the same query with max number after some time.... it gives an accurate result. which is causing the very serious problem for us. some records got skipped due to this discrepancy.
Note the issue is random. which is happening very often since 15 to 20 days. moreover, we noted there are 1sec to 15 sec gap between the insert(to tbl1 table) and select(from tbl1 table).
Thanks for the help in advance.