CODEWITHSUNDEEP
mysqldatabaseinnodbmyisam
jeremy_rutman
jeremy_rutman

Reputation: 5788

why did this mysql table copy generate duplicate rows

I did 

insert  t1  select *  from  t2;

to copy a table, after creating t1 by 

CREATE TABLE t1 (
  `c` text COLLATE utf8_unicode_ci,
  `d` text COLLATE utf8_unicode_ci,
  `count` int(11) DEFAULT NULL,
  `d1` double DEFAULT NULL,
  `d2` int(11) DEFAULT NULL,
  `c1` double DEFAULT NULL,
  `c2` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

A count of original and duplicate tables shows 69M vs. 80M rows , and indeed I see duplicate rows in the copied table - does someone know why this can happen? The original table was innodb and copy is myisam, perhaps this is the source of the dupication?

Upvotes: 0

Views: 39

Answers (1)

ChristophS
ChristophS

Reputation: 623

I summarize:

Ensure table t1 is empty before copy

truncate t1;

then copy the way you have done before and count again.

Upvotes: 1

Related Questions