CHECK:
select * from dig_lotes where sit_spa!='mov' and lote_tipo='p' order by lote_data,lote_numero
"1001" "2013-04-12" "706" => repeated twice
"1001" "2013-04-17" "746" => repeated twice too
"lote_unidade" "lote_data" "lote_numero" "lote_tipo" "total_ocorrencias" "total_documentos" "total_baixas" "total_bancos" "moeda_total" "valor_total" "valor_baixas" "valor_bancos" "digitador_tipo" "digitador_id" "sit_spa" "data_spa_d" "data_spa_s" "data_spa_p" "data_spa_a" "data_spa_mov" "bordero" "cessionario_tipo" "cessionario_id" "importado" "numero_remessa_boleto" "arquivo_importacao_id" "observacao" "lote_data_origem" "lote_numero_origem" "cobrador_padrao_tipo" "cobrador_padrao_id"
"1001" "2013-04-12" "706" "p" "0" "1" "0" "0" "R$" "14681,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-12 22:23:03" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-12" "706" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-17" "746" "p" "0" "3" "0" "0" "R$" "14480,00000" "0,00000" "0,00000" "f" "44953" "s" "2013-04-17 20:56:27" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-17" "746" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "604" "p" "0" "1" "0" "0" "R$" "6912,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-18 11:32:34" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "692" "p" "0" "1" "0" "0" "R$" "180,00000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-18 18:14:59" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "705" "p" "0" "1" "0" "0" "R$" "41,40000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-18 19:03:15" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "706" "p" "0" "1" "0" "0" "R$" "2362,50000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-18 19:09:03" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "706" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "707" "p" "0" "1" "0" "0" "R$" "552,90000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-18 19:24:52" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "707" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "708" "p" "0" "1" "0" "0" "R$" "13680,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-18 19:59:48" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "710" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-18" "710" "p" "0" "1" "0" "0" "R$" "11759,10000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-18 20:57:08" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "369" "p" "0" "1" "0" "0" "R$" "50277,90000" "0,00000" "0,00000" "f" "44330" "s" "2013-04-19 06:40:06" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "603" "p" "0" "1" "0" "0" "R$" "18136,74000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 10:55:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "658" "p" "0" "1" "0" "0" "R$" "540,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 12:15:24" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "661" "p" "0" "4" "0" "0" "R$" "11558,40000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 12:36:33" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "689" "p" "0" "1" "0" "0" "R$" "1322,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 14:46:04" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "703" "p" "0" "1" "0" "0" "R$" "39,85000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-19 16:00:38" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "706" "p" "0" "1" "0" "0" "R$" "380,00000" "0,00000" "0,00000" "f" "44953" "s" "2013-04-19 16:47:49" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "706" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "725" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "725" "p" "0" "1" "0" "0" "R$" "2864,25000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 18:07:38" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "728" "p" "0" "1" "0" "0" "R$" "99,75000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-19 18:19:03" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "736" "p" "0" "1" "0" "0" "R$" "50184,10000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 19:23:18" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "736" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "745" "p" "0" "1" "0" "0" "R$" "13534,25000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 20:25:05" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "746" "p" "0" "3" "0" "0" "R$" "91320,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-19 21:20:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-19" "746" "p" "0" "0" "0" "0" "R$" "0,00000" "0,00000" "0,00000" "f" "0" "s" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-21" "1" "p" "0" "1" "0" "0" "R$" "173486,38000" "0,00000" "0,00000" "f" "44330" "s" "2013-04-21 23:50:48" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "308" "p" "0" "3" "0" "0" "R$" "11500,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 10:53:53" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "310" "p" "0" "1" "0" "0" "R$" "42021,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 11:01:43" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "311" "p" "0" "1" "0" "0" "R$" "63581,90000" "0,00000" "0,00000" "f" "6755" "s" "2013-04-22 11:15:19" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "316" "p" "0" "1" "0" "0" "R$" "225000,00000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 11:37:26" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "319" "p" "0" "1" "0" "0" "R$" "85658,33000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 11:55:08" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "320" "p" "0" "1" "0" "0" "R$" "157957,07000" "0,00000" "0,00000" "f" "90006" "p" "2013-04-22 11:58:14" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "447" "p" "0" "1" "0" "0" "R$" "40,85000" "0,00000" "0,00000" "f" "6709" "s" "2013-04-22 12:58:10" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "494" "p" "0" "1" "0" "0" "R$" "522,20000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-22 14:39:46" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "514" "p" "0" "1" "0" "0" "R$" "240,00000" "0,00000" "0,00000" "f" "44953" "s" "2013-04-22 15:42:49" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "519" "p" "0" "1" "0" "0" "R$" "360,00000" "0,00000" "0,00000" "f" "44953" "s" "2013-04-22 15:47:26" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "520" "p" "0" "1" "0" "0" "R$" "60,00000" "0,00000" "0,00000" "f" "44953" "s" "2013-04-22 15:50:56" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "522" "p" "0" "1" "0" "0" "R$" "207,00000" "0,00000" "0,00000" "f" "43878" "s" "2013-04-22 16:32:51" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "535" "p" "0" "1" "0" "0" "R$" "31074,08000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 17:48:08" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "540" "p" "0" "1" "0" "0" "R$" "50,00000" "0,00000" "0,00000" "f" "6783" "s" "2013-04-22 19:38:29" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPf" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "541" "p" "0" "1" "0" "0" "R$" "540,00000" "0,00000" "0,00000" "f" "6755" "s" "2013-04-22 19:38:56" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "542" "p" "0" "3" "0" "0" "R$" "73231,20000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 19:45:39" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
"1001" "2013-04-22" "543" "p" "0" "1" "0" "0" "R$" "169743,14000" "0,00000" "0,00000" "f" "90006" "s" "2013-04-22 19:49:23" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0000-00-00 00:00:00" "0" "f" "0" "SPfat" "0" "0" "" "0000-00-00" "0" "f" "0"
repair table dig_lotes ;
"Table" "Op" "Msg_type" "Msg_text"
"19_cashflow.dig_lotes" "repair" "status" "OK"
it's really duplicated!
CREATE TABLE t2 LIKE dig_lotes;
INSERT INTO t2 SELECT * FROM dig_lotes;
/* SQL Error (1062): Entrada '1001-2013-04-18-710' duplicada para a chave 'PRIMARY' */
SHOW WARNINGS;
"Level" "Code" "Message"
"Error" "1062" "Entrada '1001-2013-04-18-710' duplicada para a chave 'PRIMARY'"
any idea how to solve my problem?
delete from dig_lotes where (lote_unidade,lote_data,lote_numero) IN (SELECT lote_unidade,lote_data,lote_numero FROM t2);
returned: /* SQL Error (1032): Não pode encontrar registro em 'dig_lotes' */
i will truncate table and insert ignore from t2 to solve my problem
i think the problems was at insert ignore, since this select:
select lote_unidade,lote_tipo,lote_data,lote_numero,importado,'s' from dig_contas
where (lote_unidade,lote_tipo,lote_data,lote_numero) not in (select lote_unidade,lote_tipo,lote_data,lote_numerofrom dig_lotes);
could return more than 1 row with same 'primary key fields' (lote_unidade,lote_data,lote_numero)
maybe it's a bug in aria engine?
the table is 'new' i done a ALTER TABLE dig_lotes ENGINE=ARIA, about two days ago, and it changed without problem, in other words, no duplicated primary key in old MyISAM table
Hi Roberto,
Please provide SHOW CREATE TABLE dig_contas and full dumps for both tables if possible.
Thanks.
well i have only a backup of data before converting dig_lotes to mariadb (must be converted with ALTER TABLE dig_lotes ENGINE=ARIA) - it's was a partitioned myisam table (maybe alter table accepted a error from old table... maybe it's the insert ignore with problem...)
the files can by download here:
http://spd2.friganso.com.br/mariadb/all.tgz
it's a http server (apache) with directory list blocked, all.tgz have all files from both tables, inside this mariadb/ folder (http) have theses files:
all.tgz dig_contas.MAI dig_lotes#P#p0.MAD dig_lotes#P#p1.MAD dig_lotes#P#p2.MAD dig_lotes#P#p3.MAD dig_lotes.frm
dig_contas.MAD dig_contas.frm dig_lotes#P#p0.MAI dig_lotes#P#p1.MAI dig_lotes#P#p2.MAI dig_lotes#P#p3.MAI dig_lotes.par
date time from backup is:
[beto@friganso mariadb]# ls -la /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_contas.*
rw-r---- 1 beto root 131072 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_contas.MAD
rw-r---- 1 beto root 73728 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_contas.MAI
rw-r---- 1 beto root 10733 Abr 21 14:35 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_contas.frm
[beto@friganso mariadb]# ls -la /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes*
rw-r---- 1 beto root 18325504 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p0.MAD
rw-r---- 1 beto root 12230656 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p0.MAI
rw-r---- 1 beto root 6807552 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p1.MAD
rw-r---- 1 beto root 3776512 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p1.MAI
rw-r---- 1 beto root 7168000 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p2.MAD
rw-r---- 1 beto root 4079616 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p2.MAI
rw-r---- 1 beto root 6782976 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p3.MAD
rw-r---- 1 beto root 3678208 Abr 21 14:36 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes#P#p3.MAI
rw-r---- 1 beto root 10078 Abr 21 14:35 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes.frm
rw-r---- 1 beto root 32 Abr 21 14:35 /home/mysql/backup/diario/atu-mysqlantigo/low_priority/19_cashflow/dig_lotes.par
thanks!
when i did alter table of dig_lotes i had no errors
i didn't checked (CHECK TABLE, REPAIR TABLE) the old myisam table
when ALTER TABLE, if the table have an error it can't be done since a error message apears like 'table XXX marked as xxxx, please repair...' or something like it, right?
just to check where the error comes... i don't remember a error from this table before aria engine, but, maybe the alter table can have a bug? or don't check if the key is unique or not, considering that table is ok (no errors)? maybe the error come from insert? well i just have a notice of duplicated primary key after insert... sorry if it can't help :/
just to add more information without download file:
CREATE TABLE `dig_lotes` (
`lote_unidade` int(11) NOT NULL DEFAULT '0',
`lote_data` date NOT NULL DEFAULT '0000-00-00',
`lote_numero` int(11) NOT NULL DEFAULT '0',
`lote_tipo` char(1) NOT NULL DEFAULT 'p',
`total_ocorrencias` int(10) unsigned NOT NULL DEFAULT '0',
`total_documentos` int(10) unsigned NOT NULL DEFAULT '0',
`total_baixas` int(10) unsigned NOT NULL DEFAULT '0',
`total_bancos` int(10) unsigned NOT NULL DEFAULT '0',
`moeda_total` char(3) NOT NULL DEFAULT 'R$',
`valor_total` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`valor_baixas` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`valor_bancos` decimal(17,5) unsigned NOT NULL DEFAULT '0.00000',
`digitador_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`digitador_id` int(11) NOT NULL DEFAULT '0',
`sit_spa` char(3) NOT NULL DEFAULT 'd',
`data_spa_d` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_s` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_p` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_a` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_mov` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`bordero` bigint(20) unsigned NOT NULL DEFAULT '0',
`cessionario_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`cessionario_id` bigint(20) NOT NULL DEFAULT '0',
`importado` varchar(25) NOT NULL,
`numero_remessa_boleto` bigint(20) unsigned NOT NULL DEFAULT '0',
`arquivo_importacao_id` int(10) unsigned NOT NULL DEFAULT '0',
`observacao` text NOT NULL,
`lote_data_origem` date NOT NULL DEFAULT '0000-00-00',
`lote_numero_origem` int(11) NOT NULL DEFAULT '0',
`cobrador_padrao_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`cobrador_padrao_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`lote_unidade`,`lote_data`,`lote_numero`),
KEY `lote_origem` (`lote_unidade`,`lote_data_origem`,`lote_numero_origem`),
KEY `consulta_lote` (`lote_tipo`,`sit_spa`(1),`lote_data`,`lote_numero`)
) ENGINE=Aria DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY ()
PARTITIONS 4 */
CREATE TABLE `dig_contas` (
`cc_hash_key` varchar(75) NOT NULL DEFAULT '',
`evento_id` int(10) unsigned NOT NULL DEFAULT '0',
`conta_aplicacao_origem` int(11) NOT NULL DEFAULT '0',
`conta_origem_origem` int(11) NOT NULL DEFAULT '0',
`conta_aplicacao_destino` int(11) NOT NULL DEFAULT '0',
`conta_origem_destino` int(11) NOT NULL DEFAULT '0',
`conta_origem` bigint(20) unsigned NOT NULL DEFAULT '0',
`carteira_origem` bigint(20) unsigned NOT NULL DEFAULT '0',
`conta_destino` bigint(20) unsigned NOT NULL DEFAULT '0',
`carteira_destino` bigint(20) unsigned NOT NULL DEFAULT '0',
`moeda` char(3) NOT NULL DEFAULT 'R$',
`valor` decimal(17,5) NOT NULL DEFAULT '0.00000',
`data_transferencia_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_transferencia_origem` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_transferencia_destino` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_vencimento_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_vencimento_origem` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_vencimento_destino` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_compensacao_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_compensacao_origem` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_compensacao_destino` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_compensacao_prevista` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lote_unidade` int(11) NOT NULL DEFAULT '0',
`lote_tipo` char(1) NOT NULL DEFAULT 'r',
`lote_data` date NOT NULL DEFAULT '0000-00-00',
`lote_numero` int(10) NOT NULL DEFAULT '0',
`digitador_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`digitador_id` bigint(20) NOT NULL DEFAULT '0',
`sit_spa` enum('d','s','p','a','mov') NOT NULL DEFAULT 'd',
`data_spa_d` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_s` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_p` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_a` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`data_spa_mov` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`origem_tipo` enum('cob','doc','bc') NOT NULL DEFAULT 'doc',
`hash_origem` varchar(75) NOT NULL DEFAULT '',
`importado` varchar(25) NOT NULL,
`pess_mov_tipo` enum('f','j') NOT NULL DEFAULT 'f',
`pess_mov_id` int(10) unsigned NOT NULL DEFAULT '0',
`retirada_banco` enum('Y','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`cc_hash_key`),
KEY `lote` (`lote_unidade`,`lote_data`,`lote_numero`)
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
maybe the problem is in PARTITION engine, instead myisam or aria...
after send last post with create table of 'dig_lotes' i checked that it have partitions...
sorry about dont showing this information before, i was thinking that after alter table engine=aria the partitions was removed
So you did still have partitions on dig_lotes by the time the problem occurred? Are you sure about that, your CREATE TABLE statement in the description doesn't contain partitioning, how did you acquire it if the table still has it?
SMALL HISTORY
did still have partitions on dig_lotes by the time the problem occurred? YES
how did you acquire it if the table still has it? the issue i got from HEIDISQL , the last one i got from SHOW CREATE TABLE (sorry about wrong info)
LONG HISTORY
at 2013-04-21 (sunday) i was changing old mysql 5.1.26 to mariadb 10.0.1
i done one backup and changed some tables to aria engine (the big ones), i replace because guys turned computer off without stopping process and halt command (yes maybe the myisam table is broken), the database get up and i done repair in all tables before changing to mariadb
at 2013-04-22 (yesterday) a little before reporting this insue some users told me that some data was not being displayed (dig_lotes table information) and i recreate partial information from dig_contas tables (the INSERT IGNORE query)
after INSERT some rows got back normally, but others rows was duplicated
i got the create code from heisql and it didn't showed me the partition part of table the create table of this insue is wrong, the right one have the /50100 PARTITION BY KEY () PARTITIONS 4/
this one (with PARTITION) i got from SHOW CREATE TABLE dig_lotes, sorry about wrong info
Okay, do you have the error log file covering the "long history"? If the MyISAM table was broken at some point, we'll probably see some indication of it in the error log.
sure there's two logs, one before i start test of mariadb (i think i did some runs with mariadb but the server was turned off using this log)
http://spd2.friganso.com.br/mariadb/low_priority.error.log-before-mariadb
(Abr 21 14:53)
and this one is the today log:
http://spd2.friganso.com.br/mariadb/low_priority.error.log-today
(Abr 21 15:43)
i removed log while i was was updating old mysql version, because i run mysqld_multi and it was running without ledir parameter in my.cnf
if you want my.cnf configuration it's here:
http://spd2.friganso.com.br/mariadb/my.cnf-today
hummm another error in my post ... the old 'mysql' was "5.2.4-MariaDB-log" heidisql was lying to me
In your "LONG STORY", did you ever run mysql_upgrade after switching from 5.2.4 to 10.0.1? Neither in your description, nor in server logs do I see any sign of it.
yes, upgraded after changing mysqld_multi and my.cnf to run with right ledir
i think it's not in log since i rm it after mysql_multi start working without errors
i didn't saw a error in upgrade
i done repair and check after upgrade (i don't know if it do the right job but i didn't found the error without the users telling me about primary key duplication, i think it's a problem in partition engine, but i'm not sure)
Hi,
Back to the backup, you said "i have only a backup of data before converting dig_lotes to mariadb (must be converted with ALTER TABLE dig_lotes ENGINE=ARIA) - it's was a partitioned myisam table" – but as you quoted the contents of the archive yourself, and it correlates with what I see in the archive, the tables there are Aria (MAI and MAD files). So, which archive is that, from what time?
humm now i see :/
i will check backup again
:/ i didnt' found a backup before aria engine, just one old one but it have less information (2011)
maybe we could remove the part of 'myisam to aria engine' error, and leave the partition repair/check didn't remove duplicated rows?
The problem is, I'm not getting duplicates on the table from your archive – are you? Could you please try to use these files and check if you still see any duplicates?
hum, could aria_log.000001 have some information? i just
cp /old_backup/19_cashflow/dig_lotes* /mysql/data/test/
and run the query again, no duplicates?! very strange
i will try to copy the aria_log too and some files inside /old_backup/ to my test directory
check the directory files could they change something of dig_lotes table?
#sql_1186_0.MAD
#sql_119a_0.MAD
#sql_11ad_0.MAD
aria_log_control
#sql_1186_0.MAI
#sql_119a_0.MAI
#sql_11ad_0.MAI
aria_log.00000001
mysql_upgrade_info
hi, i cannot reproduce it anymore :/
please close the issue
if i can reproduce it again i will report again
tnkx
Closing as Roberto suggested