Missing rows when duplicating data from another table using MySQL

Question

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.

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:

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?

erm, should that not be insert into cash_transaction2017....? What does select count(*) from cash_transaction show in terms of records - the same as you expect or less as per question? — Professor Abronsius
– Professor Abronsius, Commented Mar 16, 2018 at 7:07
@RamRaider the script is filter the data from cash_transaction and duplicate into table cash_transaction2017 — 1myb
– 1myb, Commented Mar 16, 2018 at 7:08

Julien Rousé · Accepted Answer · 2018-03-16 08:11:49Z

1

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.

edited Mar 16, 2018 at 8:11

Julien Rousé

1,1111 gold badge16 silver badges30 bronze badges

answered Mar 16, 2018 at 7:52

sllim.Sam

1841 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tommy Lee Over a year ago

yes u are right, the rows aren't actually "missing", it's just that it cant be displayed when browsing in phpmyadmin

Collectives™ on Stack Overflow

Missing rows when duplicating data from another table using MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related