Posted on: March 05, 2021 07:44 PM
Posted by: Renato
Views: 4179
Instalei uma versão mais recente do MySQL. Tenho querys que funcionam perfeitamente com a versão anterior e agora tenho muitos erros. ”
Causas mais frequentes desse problema e como resolvê-lo.
Estamos falando sobre este erro:
Você já viu isso?
SQL_MODE
Como primeira coisa, vou apresentar o conceito de SQL_MODE .
O MySQL pode funcionar usando diferentes modos SQL que afetam a sintaxe das consultas e verificações de validação. Com base no valor configurado da variável sql_mode, significa que uma consulta pode ser válida e ser executada regularmente ou pode receber um erro de validação e não pode ser executada.
As versões mais antigas do MySQL acostumaram os usuários a escrever consultas que não eram semanticamente corretas porque foi projetado para funcionar no “modo indulgente”. Os usuários podem escrever qualquer tipo de consulta sintaticamente válida, independentemente da conformidade com o padrão SQL ou das regras semânticas. Este era um mau hábito que foi corrigido introduzindo o sql_mode para instruir o MySQL a trabalhar de uma forma mais restritiva para validação de consulta.
Alguns usuários não estão cientes desse recurso porque o valor padrão não era tão restritivo. A partir de 5.7, o valor padrão é mais restritivo e esta é a razão pela qual alguns usuários têm problemas com falhas inesperadas de consulta após a migração para 5.7 ou 8.0.
A variável sql_mode pode ser definida no arquivo de configuração ( /etc/my.cnf ) ou pode ser alterada em tempo de execução. O escopo da variável pode ser GLOBAL e SESSION, então ele pode mudar pelo propósito do modo para qualquer conexão única.
A variável sql_mode pode ter mais valores, separados por vírgula, para controlar diferentes comportamentos. Por exemplo, você pode instruir o MySQL sobre como lidar com datas com zeros como '0000-00-00', para garantir que a data seja considerada válida ou não. No “modo indulgente” (ou se a variável sql_mode estiver vazia) você pode INSERIR tal valor sem problemas.
Mas este não é o comportamento correto conforme declarado pelo modo TRADICIONAL . Como os bons programadores sabem, você deve validar as datas em seu código-fonte para evitar dados incorretos ou resultados incorretos.
Veja a seguir como você pode instruir dinamicamente o MySQL a se comportar no modo tradicional para lançar um erro:
Existem muitos outros modos que você pode usar. Cobrir todos os modos não é o objetivo do artigo, portanto, consulte a documentação oficial para obter mais detalhes e exemplos:
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
O problema ONLY_FULL_GROUP_BY
Vamos nos concentrar na causa mais frequente de erros ao migrar para 5.7 ou 8.0. Como dissemos, 5.7 tem um modo SQL padrão que é mais restritivo do que 5.6 e, como tal, é para 8.0. Isso é verdade quando você atualiza o MySQL copiando o arquivo my.cnf antigo que não tem uma configuração específica para a variável sql_mode . Portanto, esteja atento.
Vamos criar uma tabela de exemplo para armazenar os cliques nas páginas do nosso site. Gostaríamos de registrar o nome da página e a id do usuário registrado.
Agora queremos fazer uma consulta para calcular as páginas mais visitadas.
A consulta funciona, mas não está realmente correta. É facilmente compreensível que page_url seja a coluna da função de agrupamento, o valor no qual estamos mais interessados e queremos ser únicos para contagem. Além disso, a coluna de visitas é boa, pois é o contador. Mas e quanto ao user_id ? O que esta coluna representa? Nós agrupamos no page_url para que o valor retornado para user_id seja apenas um dos valores no grupo. Na verdade, não foi apenas o usuário número 1 que visitou o index.html, mas até mesmo os usuários 2 e 3 visitaram a página. Como posso considerar esse valor? É o primeiro visitante? É o último?
Não sabemos a resposta certa! Devemos considerar o valor da coluna user_id como um item aleatório do grupo.
De qualquer forma, a resposta certa é que a consulta não é semanticamente correta, pois não tem sentido retornar um valor de uma coluna que não faz parte da função de agrupamento. Então, espera-se que a consulta seja inválida no sql tradicional.
Vamos testar.
Agora temos um erro, como esperado.
O modo SQL ONLY_FULL_GROUP_BY faz parte do modo TRADICIONAL e está habilitado por padrão a partir de 5.7.
Muitos clientes tiveram esse tipo de problema após a migração para uma versão recente do MySQL.
Agora sabemos qual é a causa do problema, mas nossos aplicativos ainda não estão funcionando. Que soluções possíveis temos para permitir que os aplicativos voltem a funcionar?
Solução 1 - reescrever a consulta
Como não é correto selecionar uma coluna que não faz parte do agrupamento, podemos reescrever a consulta sem essas colunas. Muito simples.
Se você tiver muitas consultas afetadas pelo problema, terá que potencialmente fazer muito trabalho para recuperá-las e reescrevê-las. Ou talvez as consultas possam fazer parte de um aplicativo legado que você não consegue ou não quer tocar.
Mas essa solução é aquela que força você a escrever consultas corretas e permite que a configuração do seu banco de dados seja restritiva em termos de validação de SQL.
Solução 2 - volte ao modo indulgente
Você pode alterar a configuração do MySQL e voltar ao modo “indulgente”.
Ou você só pode eliminar o ONLY_FULL_GROUP_BY do padrão. O modo SQL padrão no MySQL 5.7 inclui estes modos: ONLY_FULL_GROUP_BY, STRINCT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER
Solução 3 - uso de funções de agregação
Se seu aplicativo absolutamente precisar recuperar o campo user_id por algum motivo válido, ou se for muito complicado alterar seu código-fonte, você pode contar com uma função de agregação para evitar a alteração da configuração do modo sql.
Por exemplo, podemos usar as funções de agregação MAX () , MIN () ou mesmo GROUP_CONCAT () .
O MySQL fornece até uma função específica para resolver o problema: ANY_VALUE ().
Fonte:
- https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/
Donate to Site
Renato
Developer