MySQL auto Increment id has taken but seems like not written to disk

Question

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.

Databases

db_20110624

db_20110706

db_20110825

and so on....600+

and logdb

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.

Table structure:

'serialid', 'bigint(20)', 'NO', 'PRI', NULL, 'auto_increment'

'requestunkid', 'bigint(20)', 'NO', 'MUL', NULL, ''

'fordate', 'date', 'YES', '', NULL, ''

'databasename', 'varchar(255)', 'YES', '', NULL, ''

Problem

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.

Example:

| 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.

Answer 1

Please provide the schemas involved, plus the SQL statements.

A wild guess is that you are using OFFSET (or LIMIT nnn,mmm). I explain here why OFFSET can skip or duplicate rows.

Answer 2

we finally able to identify the problem. it is because of the following statement in the stored procedures. we are inserting records using the stored procedures. the problem is there are so many things happening between START and COMMIT. and when any insert happens to another table it reserved the autoincrement id. but do not write it to the disk until it commits.And in between next id written to the disk for which process executed early than previous one. so now maximum number discrepancy started.

**START TRANSACTION;

.....

COMMIT;**