shakall

Amigos, estou começando no Postgre, venho do MySQL, uma duvida que tenho agora é a respeito do comportamento do postgre a respeito de uma tabela que está sofrendo insert. No meu caso, enquanto o script de insert estava rodando, cerca de 30 minutos, o banco deu lock na tabela para leitura, ou seja não era possível realizar nenhum select que utiliza-se esta tabela. Esse é o comportamento normal do Postgre? Eu vejo que isso está errado, pois não tem o porque de dar lock para leitura na tabela enquanto ela é populada. Obrigado pela ajuda.

Amigos resolvi o problema, notei que a tabela no servidor tinha o tamanho de 3.8GB, já no meu note não passava de 442mb, com a mesma quantidade de registro. Para resolver dei um drop na tabela do servidor e recriei, agora ela tem 442mb igual o notebook e o select está super rapido. Agora gostaria de entender o porque da tabela do servidor ter 3.8GB sendo que na verdade é 442mb. Esta tabela estava com 11milhoes de registros, onde 10 milhões foram deletados via comando delete, mas o tamanho da tabela não foi alterado, provocando este problema.

Olá amigos gostaria de uma ajuda de vocês, estou com um problema de desempenho em uma tabela onde já criei index, o explain está perfeito, mas na hora de rodar tenho o seguintes tempos. /* Affected rows: 0 Registros encontrados: 23.738 Avisos: 0 Duração de 1 query: 2,625 sec. (+ 182,078 sec. network) */ Vejam que o tempo da query é rapido, o problema está a network, o select está sendo rodado em localhost, então não deveria ter tempo de network pois o banco e a execução foram na mesma maquina. Se eu copio a tabela para meu notebook, vai super rápido vejam /* Affected rows: 0 Registros encontrados: 23.738 Avisos: 0 Duração de 1 query: 0,094 sec. (+ 1,469 sec. network) */ alguém tem alguma ideia do que pode ser? Obrigado!

Olá Denis obrigado pela ajuda, meu mysql está instalado no Windows, acessei o my-default.ini, editei e descomentei a linha "#log-bin" deixando ela assim "log_bin=C:\Program Files\MySQL\MySQL Server 5.6\log\sql.log" Reiniciei o mysql, entrei fiz alguns selects mas a pasta está vazia ele não gerou nenhum arquivo, fiz algo errado? Obrigado pela ajuda!

Amigos tem como eu verificar os ultimos comandos executados no banco de dados Mysql? Sumiram algumas funções e procs e eu gostaria de saber quais os ultimos comandos para ver se foi erro humano provavelmente ou se foi algum problema no servidor. Existe algum log de comandos? É possível verificar isso? Obrigado!

Olá pessoal! Estou fazendo uma tabela que ao clicar em uma célula ela muda a cor de fundo com o código abaixo consegui fazer esta parte, agora o problema é que quando eu clicar e arrastar ela tem que mudar a cor de todas as células onde o mouse passar. Alguém pode me ajudar, como fazer para que minha função de mudar a cor da célula seja chamada ao clicar e arrastar o mouse, todas as células por onde ele passar seja trocada as cores? &lt;script> function mudaCor(id) { if (document.getElementById(id).style.background == "none repeat scroll 0% 0% white") { document.getElementById(id).style.background = "green"; } else { document.getElementById(id).style.background = "white"; } } </script> <table border="1" align="center"> <tr> <td id="tdCor1" onmousedown="java script:mudaCor('tdCor1')" width="15" height="15" style="background:white"></td> <td id="tdCor2" onmousedown="java script:mudaCor('tdCor2')" width="15" height="15" style="background:white"></td> <td id="tdCor3" onmousedown="java script:mudaCor('tdCor3')" width="15" height="15" style="background:white"></td> </tr> <tr> <td id="tdCor25" onmousedown="java script:mudaCor('tdCor25')" width="15" height="15" style="background:white"></td> <td id="tdCor26" onmousedown="java script:mudaCor('tdCor26')" width="15" height="15" style="background:white"></td> <td id="tdCor27" onmousedown="java script:mudaCor('tdCor27')" width="15" height="15" style="background:white"></td> </tr> </table>

Galera hoje aconteceu comigo um erro grotesco, um a das minhas tabelas simplesmente sumiu, e esta tabela é utlizada em varias outras, ou seja um simples drop table não permitira por causa da FK. Identifiquei o erro ao gerar uma lista de comandos alter table, onde estes comandos adiciona uma nova coluna para guardar a data e hora que os registros são inseridos, esta coluna por default recebe a data e hora no instante da inserção, depois de rodar alguns comandos recebi um erro de que a tabela Filial não existe. ALTER TABLE logc_h.filial ADD COLUMN `DT_CRIACAO` TIMESTAMP NOT NULL DEFAULT NOW(); Achei estranho pois esta tabela existe! Ao verificar constatei que ela realmente tinha desaparecido, mas todas as outras tabelas que a utilizam estavam intactas, com os valores das key apontando para Filial. Ao analisar os logs do banco verifiquei que minutos antes havia ocorrido vários logs de erro, que estão abaixo. Ao tentar recriar a tabela o erro é exibido "/* SQL Error (1005): Can't create table 'logc_h.filial' (errno: -1) */" Gostaria de saber se alguém consegue identificar o problema que ocorreu, e se é possível restaurar a tabela? Obrigado! InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:01 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`situacao_dispositivo` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:02 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`situacao_documento` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:02 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`situacao_viagem` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:02 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`tipo_checkpoint` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:03 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`tipo_dispositivo` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:03 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`tipo_veiculo` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:04 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`tipo_viagem` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:04 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`tipo_visao` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:04 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`transportadora_cooperado` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:05 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`transportadora_motorista` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:06 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`usuario` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:06 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`usuario_cliente` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:06 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`usuario_filial` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:07 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`usuario_perfil` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:22 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`veiculo` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:25 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`viagem` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:26 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`viagem_itinerario` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:28 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`viagem_veiculo` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. 110531 10:33:29 InnoDB: Error; possible reasons: InnoDB: 1) Table rename would cause two FOREIGN KEY constraints InnoDB: to have the same internal name in case-insensitive comparison. InnoDB: 2) table `logc_h`.`#sql2-f90-74a21` exists in the InnoDB internal data InnoDB: dictionary though MySQL is trying to rename table `logc_h`.`visao_movel` to it. InnoDB: Have you deleted the .frm file and not used DROP TABLE? InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html InnoDB: If table `logc_h`.`#sql2-f90-74a21` is a temporary table #sql..., then it can be that InnoDB: there are still queries running on the table, and it will be InnoDB: dropped automatically when the queries end. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: Error B: cannot load foreign constraint logc_h/FK_DISPOSITIVO_FILIAL InnoDB: Error B: cannot load foreign constraint logc_h/FK_DISPOSITIVO_FILIAL InnoDB: Error B: cannot load foreign constraint logc_h/FK_DISPOSITIVO_FILIAL

Amigos estou realizando um sql que me retorna 1600000 registros e preciso exporta-los para um arquivo sql, de preferencia já formatado para insert, gostaria de saber como realizar esta exportação? Obrigado!

Galera estou com um erro muito estranho, quando tento inserir uma data está apresentando erro, segue abaixo os insert. INSERT INTO POSICAO_GPS (SQ_POSICAO_GPS,CD_EMPRESA,DT_POSICAO,SQ_VEICULO,NR_ANTENA_RASTREAMENTO,NR_LAT,NR_LOG,DS_POSICAO,ID_IGNICAO) VALUES(( SELECT UUID()),12,'1969-12-30 21:00:03',5219,5154871,'0','0','','D'); O erro aprensentado é "SQL ERROR(1292):INCORRECT DATATIME VALUE:'1969-12-30 21:00:03' FOR COLUMN 'DT_POSICAO' AT ROW 1" Não sei mais o que fazer para resolver, alguém pode me ajudar? Obrigado!

Putz cara nem sei como agradecer, muito obrigado pela ajuda. Cara testei aqui esta index mas infelizmente não deu certo, olha o exemplo abaixo ele levou 2 minutos para ser executado, depois tem o explain. SELECT DISTINCT DS_POSICAO FROM POSICAO_GPS USE INDEX(EMPRESA_LAT_LOG) WHERE CD_EMPRESA=12 AND (NR_LAT BETWEEN -15.020000 AND -14.980000) AND (NR_LOG BETWEEN -41.020000 AND -40.980000) AND DS_POSICAO IS NOT NULL ORDER BY DT_POSICAO DESC LIMIT 1; /* 0 rows affected, 1 rows found. Duration for 1 query: 122,991 sec. */ Explain "id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra" "1";"SIMPLE";"POSICAO_GPS";"range";"EMPRESA_LAT_LOG";"EMPRESA_LAT_LOG";"15";NULL;"19656";"Using where; Using temporary; Using filesort"

Galera se alguém puder me ajudar na otimização da busca deste select, continuo com o problema, já tentei diversas formas de melhorar mas sem sucesso. Segue abaixo o script com a tabela, dados e o select para facilitar. Muito Obrigado. Script

Segue o script da tabela. PosicaoGPS Obrigado pela ajuda!

Olá Denis, obrigado pela ajuda. Fiz o que você disse, mudei para between, mas tive que tirar as "" pois com elas nada era retornado, retirando ele encontra os registros. Adicionei o index que você comentou mas no explain ele não faz uso, ele utilza outro index da empresa, segue abaixo os resultados, vou gerar um script com a tabela e os dados, assim fica mais facil para você me ajudar. explain SELECT DISTINCT DS_POSICAO FROM POSICAO_GPS WHERE CD_EMPRESA=17 AND DS_POSICAO IS NOT NULL AND (NR_LAT BETWEEN -12.92493 AND -12.91493) AND (NR_LOG BETWEEN -49.43828 AND -49.40828) ORDER BY DT_POSICAO DESC LIMIT 1 "id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra" "1";"SIMPLE";"POSICAO_GPS";"ref";"Ref_91,localidade";"Ref_91";"5";"const";"58724";"Using where; Using temporary; Using filesort" INDEX `Ref_91` (`CD_EMPRESA`),

Galera venho novamente pedir ajuda de vocês, com a tabela abaixo possuo algo em torno de 1milhão de registros, e agora preciso gerar um sql que me retorne uma posição dentro de uma margem de valores, criei o seguinte sql. SELECT DISTINCT DS_POSICAO FROM POSICAO_GPS WHERE CD_EMPRESA=17 AND DS_POSICAO IS NOT NULL AND (NR_LAT>=-12.92493 AND NR_LAT<=-12.91493) AND (NR_LOG>=-49.43828 AND NR_LOG<=-49.40828) ORDER BY DT_POSICAO DESC LIMIT 1 Quando executo este sql, caso tenha alguma posição nesta margem de valores ela é retornada rapidamento, e no explain ele utiliza os index e passa em apenas 1 linha, isso ta funcionando muito bem. O problema é se eu passar uma margem que não tenha nenhum registro, simplesmente ele demora 300segundos em media para não retornar nada, e ao testar no explain ele diz que passou em 11 linhas. "id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra" "1";"SIMPLE";"POSICAO_GPS";"index";"Ref_91,Index 10,Index 9,Index 11";"IDX-DTPOSICAO-SQVEICULO-CDEMPRESA";"14";NULL;"11";"Using where; Using temporary" Criei varios index para ver se melhora o desempenho mas o problema continua se não existir uma posição na margem passada ele demora 300segundos para responder, se tem em menos de 0.200 segundos é respondido. não sei mais o que fazer para resolver isso. Obrigado! CREATE TABLE `posicao_gps` ( `SQ_POSICAO_GPS` VARCHAR(36) NOT NULL, `SQ_VEICULO` INT(11) NULL DEFAULT NULL, `CD_EMPRESA` INT(6) NULL DEFAULT NULL, `SQ_DISPOSITIVO_VEICULO` BIGINT(19) NULL DEFAULT NULL, `DT_POSICAO` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `NR_ANTENA_RASTREAMENTO` BIGINT(20) NULL DEFAULT NULL, `NR_LAT` VARCHAR(50) NOT NULL, `NR_LOG` VARCHAR(50) NOT NULL, `DS_POSICAO` VARCHAR(400) NULL DEFAULT NULL, `ID_IGNICAO` VARCHAR(1) NULL DEFAULT NULL, `VL_VELOCIDADE` FLOAT NULL DEFAULT NULL, `STATUS` VARCHAR(1) NOT NULL DEFAULT 'V', PRIMARY KEY (`SQ_POSICAO_GPS`), INDEX `Ref_90` (`SQ_VEICULO`), INDEX `Ref_91` (`CD_EMPRESA`), INDEX `IDX-DTPOSICAO-SQVEICULO-CDEMPRESA` (`DT_POSICAO`, `SQ_VEICULO`, `CD_EMPRESA`), INDEX `SQ_DISPOSITIVO_VEICULO` (`SQ_DISPOSITIVO_VEICULO`), INDEX `index 7` (`SQ_VEICULO`, `DT_POSICAO`), INDEX `status` (`STATUS`), INDEX `Index 10` (`CD_EMPRESA`, `NR_LAT`, `NR_LOG`), INDEX `Index 9` (`NR_LAT`), INDEX `Index 11` (`NR_LOG`), CONSTRAINT `FK_posicao_gps_dispositivo_veiculo` FOREIGN KEY (`SQ_DISPOSITIVO_VEICULO`) REFERENCES `dispositivo_veiculo` (`SQ_DISPOSITIVO_VEICULO`) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT `Ref_90` FOREIGN KEY (`SQ_VEICULO`) REFERENCES `veiculo` (`SQ_VEICULO`) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT `Ref_91` FOREIGN KEY (`CD_EMPRESA`) REFERENCES `empresa` (`CD_EMPRESA`) ON UPDATE NO ACTION ON DELETE NO ACTION ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT

Que coisa heim, valeu pela ajuda!!!