2
Good afternoon, everyone,
I’m migrating a Delphi/Firebird system to Php/Mysql,
And I’ve come across a situation I can’t understand,
I created a view in mysql to generate a report, the same view I have in Firebird, but when I run the lock view it generates nothing,
I believe that this is not an optimization case, because copying the query that originated the view and running it all works normally,
What would that be? some configuration? mysql bug?
I can’t understand what’s wrong,
Thanks in advance.
An important detail that I need to mention is that today I created the same bank at Postgres and it worked perfectly, including noticeably faster than Firebird,
Below is the Script Creation of one of the Views,
DROP VIEW IF EXISTS SEL_CUSTOS_APROPRIADOS; CREATE VIEW SEL_CUSTOS_APROPRIADOS( LANCAMENTO, EMPRESA, EMPRESA_RAZAO, EMPRESA_CNPJ, ENTIDADE, ENTIDADE_RAZAO, ENTIDADE_CNPJ, ENTIDADE_CPF, EMPREENDIMENTO, EMPREENDIMENTO_DESCR, DATAAQUISICAO, QUADRA, LOTE, DESMEMBRAMENTO, EMISSAO, PARCELA_ID, PARCELA, VENCIMENTO, PARCELA_VALOR, COMPETENCIA, MES, CUSTO, CUSTOAPROPRIADO, INFRAMES, INFRAMES2, DATADISTRATO, CONTACTBEMPREENDCUSTO, CONTACTBRECEITADIFER, CONTACTBRECEITADIFERLP, CONTACTBDESPESADIFER, CONTACTBDESPESADIFERLP, EMPRESACONTABIL, CONTACTBDISTRATODRE, CONTACTBINFRAESTRUTURA) AS SELECT V.LANCAMENTO ,V.EMPRESA ,V.EMPRESA_RAZAO ,V.EMPRESA_CNPJ ,V.ENTIDADE ,V.ENTIDADE_RAZAO ,V.ENTIDADE_CNPJ ,V.ENTIDADE_CPF ,V.EMPREENDIMENTO ,V.EMPREENDIMENTO_DESCR ,V.DATAAQUISICAO ,V.QUADRA ,V.LOTE ,V.DESMEMBRAMENTO ,V.EMISSAO ,V.PARCELA_ID ,V.PARCELA ,V.VENCIMENTO ,V.PARCELA_VALOR ,V.COMPETENCIA ,V.MES ,V.CUSTO VLR_CUSTO ,((V.CUSTO + CUSTOINFRAACUMULADO) * V.PERCRECEBMES) + ((V.CUSTOINFRA)*(V.PERCRECEBACUMUL-V.PERCRECEBMES)) VLR_CUSTO_APROPRIADO ,COALESCE((SELECT SUM(VALOR) FROM SEL_INFRAESTRUTURA_MES WHERE EMPRESA = V.EMPRESA AND EMPREENDIMENTO = V.EMPREENDIMENTO AND MES = V.MES AND MES<v.competenciadistrato )*V.EQUIVALENCIA,0) INFRAMES ,COALESCE((SELECT SUM(VALOR) FROM SEL_INFRAESTRUTURA WHERE EMPRESA = V.EMPRESA AND EMPREENDIMENTO = V.EMPREENDIMENTO AND MES = V.MES AND MES<v.competenciadistrato ),0)* v.equivalencia INFRAMES ,v.datadistrato ,V.CONTACTBEMPREENDCUSTO ,V.CONTACTBRECEITADIFER ,V.CONTACTBRECEITADIFERLP ,V.CONTACTBDESPESADIFER ,V.CONTACTBDESPESADIFERLP ,V.EMPRESACONTABIL ,V.CONTACTBDISTRATODRE ,V.CONTACTBINFRAESTRUTURA FROM SEL_VENDAS_PARCELAS V UNION SELECT V.LANCAMENTO ,V.EMPRESA ,v.razaoempresa ,v.cnpjempresa ,V.ENTIDADE ,v.razaoentidade ,v.cnpjentidade ,v.cpf ,V.EMPREENDIMENTO ,v.descricao ,v.dataaquisicao ,V.QUADRA ,V.LOTE ,V.EMISSAO ,V.EMISSAO ,V.PARCELAS ,V.PARCELAS ,I.DATA ,V.VALORPARCELA ,I.COMPETENCIA ,I.MES ,v.custo VLR_CUSTO ,((I.RATEIO)) VLR_CUSTO_APROPRIADO ,COALESCE((SELECT SUM(VALOR) FROM SEL_INFRAESTRUTURA_MES IL WHERE IL.EMPRESA = V.EMPRESA AND IL.EMPREENDIMENTO = V.EMPREENDIMENTO AND IL.MES = I.MES),0) INFRAMES ,COALESCE((SELECT SUM(VALOR) FROM SEL_INFRAESTRUTURA WHERE EMPRESA = V.EMPRESA AND EMPREENDIMENTO = V.EMPREENDIMENTO AND MES = I.MES aND MES<v.competenciadistrato ),0)* v.equivalencia INFRAMES ,coalesce(v.cancelamento, '2100-12-31') ,V.CONTACTBEMPREENDCUSTO ,V.CONTACTBRECEITADIFER ,V.CONTACTBRECEITADIFERLP ,V.CONTACTBDESPESADIFER ,V.CONTACTBDESPESADIFERLP ,V.EMPRESACONTABIL ,V.CONTACTBDISTRATODRE ,V.CONTACTBINFRAESTRUTURA FROM SEL_INFRAESTRUTURA_LOTES i, sel_VENDAS V WHERE i.EMPRESA=v.EMPRESA AND i.EMPREENDIMENTO=v.EMPREENDIMENTO AND i.QUADRA=v.QUADRA AND i.LOTE=v.LOTE and v.saldo<=0 AND V.CANCELAMENTO IS NULL and i.mes>cast(CONCAT(extract(year from v.ultrecebto),'-',extract(month from v.ultrecebto),'-01') as date) ORDER BY 21 ASC
Could provide codes?
– Paulo Victor
Hi good afternoon friend, can yes, I edited the question or pasted the script of one of the Views la, Thanks.
– Rodrigo Soares
Are your Keys correct? Sounds like an optimization case..
– Paulo Victor
It is difficult to say for sure, however I reviewed some 100x these links, I also checked the indexes of the tables, apparently it is all right, even because as I said, in Firebird and postgres works normally, in mysql no, the detail is that if I copy the select from the view and run a manual query with it "SELECT V.LANCAMENTO,V.EMPRESA... everything works perfectly, only when I run "SELECT * FROM VIEW" does the crash occur... so I guess the selects are correct, and that must be some error from mysql
– Rodrigo Soares
"select" runs without "Where", and view ? "select" runs with "Union"? If the "Where" is applied in each 'leg' of Union a [Indice can be applied in the view I don’t know if mysql optimizer solves , I had a problem like this in Oracle that I solved by "hint" but I can’t find the program now.
– Motta
Mysql is a mystery my friend, as it did not appear solution, I did a gambiarra, I decided to store the data in another table, For popular she created a precedure that makes a loop(select for) giving a post of the data, then I request this project to generate the report to generate the report.
– Rodrigo Soares
Is there an error? It doesn’t cost to ask, but did you increase the query timeout on Workbench (if you’re using it)? Because sometimes Mysql has the time limit setting for running a script. If it is bigger, it does not bring the result and gets an error similar to timeout.
– Douglas Marques