My SQL Database inconsistencies (Cardinality vs. Auto Increment)

Question

I have a problem with my SQL database filled by a C-program using the my SQL connector. The correspondent part of the C-Code which fills the database is displayed below:

sprintf(query_table, "CREATE TABLE IF NOT EXISTS `%08X` (COUNTER INT NOT NULL AUTO_INCREMENT PRIMARY KEY, TIME TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6), DATA VARCHAR(16))", frame.can_id-0x80000000);

    if (mysql_query(conn, query_table) != 0)               
        {                                                                                                  
        fprintf(stderr, "Query Failure; create tables from can log!\n");                                                              
        return EXIT_FAILURE;                                                                             
        }              

sprintf(query, "INSERT INTO `%08X` (TIME, DATA) VALUES (FROM_UNIXTIME(%ld.%06ld), '%02X%02X%02X%02X%02X%02X%02X%02X')",frame.can_id-0x80000000,tv.tv_sec,tv.tv_usec,frame.data[0],frame.data[1],frame.data[2],frame.data[3],frame.data[4],frame.data[5],frame.data[6],frame.data[7]);
//printf("%s",query);

    if (mysql_query(conn, query) != 0)                                                                             
        {                                                                                                  
        fprintf(stderr, "Query Failure; insert into database!\n");                                                              
        return EXIT_FAILURE;                                                                             
        }

The frame.data is a substruct which includes a bytewise 8 byte hex value, created by a peak CAN interface with 250kbit baud rate. As the code represents, with any new CAN telegram ID the DB verify the existence of the correspondent DB table. In case of no existence, the table is going to be created. Due to unknown changes of the CAN software it is not possible to pre-allocate the tables, unfortunately.

After a two-month measurement period, the DB was fulfilled with different telegrams and data sets. In the first place, everything looks fine but with a closer look, some inconsistencies are discernible. The annexed picture 1 displays 3 different cases of Cardinality-Auto-Increment-Issues to explain the current situation. I am not that common with my SQL or C-programming therefore, I would appreciate to get some good advices to fix this problem.

Thank you very much and all the best from Germany.

Answer 1

Cardinality on an index is estimated rather than absolute, so there is no guarantee that cardinality shown in the index stats will be an exact match for the number of rows as returned by SELECT COUNT(*) FROM table_name; It should be close, but there is no guarantee it will be equal.

There is also no guarantee that the next auto_increment value will be equal to SELECT MAX(auto_increment_field)+1. The only guarantee is that it will be greater than or equal to SELECT MAX(auto_increment_field)+1. It is possible to allocate but not use an auto_increment value, e.g. you do something like:

START TRANSACTION;
    INSERT INTO table_name VALUES(...);
ROLLBACK;

This will increment the next auto_increment value but will not result a row with the allocated auto_increment value. There are other reasons why values could be skipped (e.g. auto_increment_increment, auto_increment_offset, innodb_autoinc_lock_mode settings all affect how sparse your autoinc space is likely to be).

If you need 100% accurate COUNT() or MAX(), you will have to use those, and it will be slow on a big table. Index cardinality and AUTO_INCREMENT= value from SHOW CREATE TABLE can only be used as approximations.