Inconsistencies in database view for row counts

I'm seeing an inconsistency in HeidiSQL 12.7.0.6850 (latest stable version); the database is MariaDB. I did a server migration and the data on older records became corrupted. So based on some suggestions used INSERT IGNORE to import newer (though not corrupted) entries from database2 to the non-corrupted backup of database1.

In the database view tab with the list of tables the table in question has 3,849 rows in database1 and 3,932 rows in database 2. However when I run the following queries:

SELECT COUNT(id), MAX(id) FROM database1.table1; SELECT COUNT(id), MAX(id) FROM database2.table1;

The results are both 4,017 rows (id) and 4,244 for the max id.

Going to the data tab and reverse-sorting the id column the highest id is 4,244 so at least that looks correct.

So, this looks like a bug though I have also never done an INSERT IGNORE before. Some help please?

Row count on InnoDB tables are estimated, through SHOW TABLE STATUS. So don't rely on what the database tab shows.

https://dev.mysql.com/doc/refman/8.4/en/innodb-restrictions-limitations.html

Ansgar, thank you. I did a little reading and it looks like atomicity is maybe the on-topic concept I'm looking for in general that might extend to this topic.

Is there a database storage engine that values accuracy over performance that I can temporarily convert all of the tables to for auditing purposes that you could please recommend?

I would merge to "good old" MyISAM in that case. That was the default engine for many years, up to some years ago, and had a great performance.

Note that MyISAM has no support for transactions!

Apart from that, it has the advantage of being accurate with the mentioned row count, besides some other less interesting differences.

So MyISAM has better external accuracy in reports but InnoDB has better internal accuracy for data integrity? That doesn't make sense. If you work harder (let's say, on a farm) why would you report your work as subpar?

I updated my database merge logic. I am no longer using INSERT IGNORE and dropped IGNORE. Instead I use the following:

INSERT INTO $database1.$table SELECT * FROM $database2.$table WHERE $database2.$table.$id>(SELECT MAX($id) FROM $database1.$table);

After restarting the MariaDB service the SHOW WARNINGS returns zero warnings porting data from database2 over to database1.

So my primary concern is about data integrity and then performance; would you suggest I stick to InnoDB or do you have any other suggestions?

A bit more research suggests that MariaDB in general is ACID compliant (I really don't know, yet):

https://www.google.com/search?q=mariadb+ACID+support 🡺 https://www.percona.com/blog/choosing-the-right-database-comparing-mariadb-vs-mysql-postgresql-and-mongodb/

I wish the forums allowed editing...so MariaDB offers ACID compliance through Xpand...but it's not an engine? Is it something besides a storage engine? Is it internal and applicable in all scenarios or is it subjective to using certain storage engines?