CODEWITHSUNDEEP
phpmysqldatabasephpmyadmin
Tommy Lee
Tommy Lee

Reputation: 794

Missing rows when duplicating data from another table using MySQL

MySQL script

INSERT cash_transaction2017 SELECT * FROM cash_transaction WHERE created_at < "2018-01-01 00:00:00"

Above is the result from the script I executed, total rows is 336,090.

However, when I browse the table from phpMyAdmin, I can only see 334,473 rows.

phpmyadmin result

After ordering my rows in cash_transaction2017 table in ascending, I found out that some rows are missing because the last created_date is different with that of cash_transaction.

Why is this happening? The result is the same no matter I execute the script from mysql console, or using php codes.

I also tried to use mysqldump with --skip-tz-utc and it's also missing some rows.

UPDATE

SELECT count(*) FROM `cash_transaction2017`
SELECT count(*) FROM `cash_transaction` WHERE created_at < "2018-01-01 00:00:00"

Apparently executing these 2 queries give me same number of rows, however, the last rows from these 2 queries are different. See the screenshots below:

cash_transaction2017 cash_trasaction

UPDATE 2

Since both tables are transactions table, so if they have the same total amount, it should signifies that they have the same number of rows without any data loss.

So I tried SELECT SUM(amount) on both tables and turns out both the tables have same total amount from SUM(amount)

So the question now is, are there actually any missing rows? Does this problem occur because I'm using innodb?

Upvotes: 1

Views: 98

Answers (1)

sllim.Sam
sllim.Sam

Reputation: 184

You may try to add the line in your config.inc.php from your phpMyAdmin directory:

$cfg['MaxExactCount'] = 1000000*

*(Make sure $cfg['MaxExactCount'] is large enough)

This problem probably occurs only with InnoDB tables. Hope this is useful.

Upvotes: 1

Related Questions