resolvendo mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

Problema do dia:

Servidor mysql usado a quase 10 anos, que mesmo tendo sido atualizado a versão, os usuários legados continuam com a hash antigamente utilizada (hoje chamada de old_password)

Para estes casos, basta trocar o tipo de autenticação na session, reescrever a senha e um flush nos privilegios.

SET SESSION old_passwords=0;
UPDATE mysql.user SET Password = PASSWORD('minhasenha') WHERE User = 'meuusuario';
FLUSH PRIVILEGES;

 

Imagem docker lemp/lnmp de fácil uso

Eu uso docker para ambiente de desenvolvimento, logo não preciso de tamanha complexidade com várias imagens dependentes montagem de diretórios cruzados etc.

Para esta finalidade basicamente preciso de um servidor lemp (ou lnmp), que é sigla para “Linux, (e)nginx, mysql e php” e um php myadmin.

Assim sendo criei uma imagem disponível publicamente no docker hub para quem precisa desta finalidade.

Para subir a aplicação com esta imagem, basta criar um docker-composer.yml na raiz de seu projeto com o conteúdo abaixo:

web:
 image: fbraz3/lnmp
 volumes:
 - ./:/app/public/
 ports:
 - "127.0.0.1:80:80"
 - "127.0.0.1:3306:3306"

E depois rodar o comando:

docker-compose up -d

A aplicação estará acessível em http://localhost/ e o PHPMyAdmin em http://localhost/pma/ simples assim.

Já está pré-configurado e com todos os grants necessários uma base de dados com o nome de “app” (sem as aspas) para uso na aplicação.

Como uso essa imagem nos meus projetos venho sempre aprimorando ela, então o changelog vai ficar sempre disponível em https://hub.docker.com/r/fbraz3/lnmp/

 

Mysql ERROR 1452 – Cannot add or update a child row

Estava exportando um dump de um cliente e me reparei com esse erro no mysql:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`databasename`.`#sql-c55_f93b7`, CONSTRAINT `foreign_key_name` FOREIGN KEY (`client_id`) REFERENCES `table_name` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Este erro é muito comum quando o dump é editado manualmente e é inserido/editado algum dado que não está em conformidade com a chave estrangeira.
Para forçar um import, basta desabilitar a checagem de integridade das chaves estrangeiras inserindo a liha abaixo no início do arquivo de import:

SET foreign_key_checks = 0;

Só não se esquecendo que isso resolve a questão do import, mas não dos dados, então é sempre importante revisar os dados após o procedimento pra localizar a raiz do problema.

Ordenando valores no mysql usando casting

Disponho de uma tabela de controle de IP’s dos servidores, de modo que possa saber rapidamente qual IP possui cada servidor, qual ip será usado ao efetuar deploy de uma nova vm, serviços, etc.

Estava tendo um problema ao editar essa tabela, pois ao efetuar uma consulta pelo range (no caso trabvalho com vários /24), os valores vinham desordenados, abaixo a query e resulta para melhor compreensão:

SELECT * FROM `TABELAIP` WHERE `ip` LIKE 'XXX.XX.XXX.%' ORDER BY `ip` ASC

Resultado:

XXX.XX.XXX.0
XXX.XX.XXX.1
XXX.XX.XXX.10
XXX.XX.XXX.100
XXX.XX.XXX.101
XXX.XX.XXX.102
XXX.XX.XXX.103
XXX.XX.XXX.104
XXX.XX.XXX.105
XXX.XX.XXX.106
XXX.XX.XXX.107
XXX.XX.XXX.108
XXX.XX.XXX.109
XXX.XX.XXX.11
XXX.XX.XXX.110
XXX.XX.XXX.111

No caso eu precisava que viessem na ordem certa, ou seja, final .1, final .2, final .3, etc

Para resolver isso, primeiramente “quebrei” a string do IP pelo ponto, buscando o último octeto, só que isso por sí só não bastava, pois mesmo tendo só o IP, o campo continuava a ser do tipo string, e para organização deste tipo no mysql, era necessário decimal, então resolví usando CASTING no mysql, eis a consulta:

SELECT *, CAST(SUBSTRING_INDEX(ip, '.', -1) as DECIMAL) as final FROM `TABELAIP` WHERE `ip` LIKE 'XXX.XX.XXX.%' ORDER BY `final` ASC

E finalmente o resultado desejado:

XXX.XX.XXX.0
XXX.XX.XXX.1
XXX.XX.XXX.2
XXX.XX.XXX.3
XXX.XX.XXX.4
XXX.XX.XXX.5
XXX.XX.XXX.6
XXX.XX.XXX.7
XXX.XX.XXX.8
XXX.XX.XXX.9
XXX.XX.XXX.10
XXX.XX.XXX.11
XXX.XX.XXX.12
XXX.XX.XXX.13
…..

Manipulação de usuários MySQL

Dica básica, mas tem gente que se embanana com isso.

Cenário: Criar uma base de dados chamada mydb, criar um usuário chamado myuser com permissão de acesso total a esta base de dados e acesso somente a localhost.

Primeiramente conectamos ao mysql como root e criamos a base de dados mydb

mysql -u root -pMinhaSenhA
create database mydb;

Agora uma dica legal, da pra criar usuários mysql apenas adicionando permissões. basicamente quando tu manda um grant e o usuário não existe, o mysql cria ele automaticamente… sabendo usar isso é muito bom 😉

Agora criamos então o usuário e adicionamos permissão as interfaces loopback 127.0.0.1, localhost e ::1 (loopback ipv6)

mysql u root -pMinhaSenhA
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' IDENTIFIED BY 'MinhaSenhA';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'127.0.0.1' IDENTIFIED BY 'MinhaSenhA';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'::1' IDENTIFIED BY 'MinhaSenhA';

Agora testamos o acesso, deve ser exibido somente a base mydb e information_schema (no meu caso aparece também uma base do capeta chamada test que o ubuntu criou automaticamente, mas nada qe um “drop database test;” não resolva)

mysql -u myuser -pMinhaSenhA
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 70
Server version: 5.5.20-0~ppa1~oneiric (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| test               |
+--------------------+
3 rows in set (0.00 sec)

Agora sim, tudo criado e funcionando! 😉

Grant mysql

Pra efetuar um grant de select restringindo range de IP e senha

GRANT SELECT ON BANCO.`TABELA` TO 'USUARIO'@'10.1.1.%' identified by 'SENHA';

Se necessitar outros grants, pode ser no mesmo comando separado por virgula.

Ex: Grant de SELECT, INSERT e UPDATE

GRANT SELECT,INSERT,UPDATE ON BANCO.`TABELA` TO 'USUARIO'@'10.1.1.%' identified by 'SENHA';

Benchark Oracle vs MySQL

Estou estudando a tecnologia oracle, as soluções são realmente interessantes principalmente com “oracle rac”.

Algumas coisas impressionam, como por exemplo,  você está fazendo uma query usando um dos nós do cluster, se o servidor reboota, ou da algum problema, ele roteia automaticamente para o próximo nó e continua a query do ponto que parou, fizemos testes e de fato isso e real, a query para por alguns poucos segundos e depoi segue a execução normalmente.

Fiz um for simples em php para inserção de 10000 registros (sim tem maneiras melhor de inserir, mas era um teste simples) numa tabela chamada “teste” possuindo dois fields:
-> id int key  –> sem auto-increment
-> reg varchar(50)

Trecho do código que usei para o for (em oracle)

for($i=1;$i<=10000;$i++)
{
        $sql = "INSERT INTO country(id, pais) VALUES('".$i."', 'registro-".$i."')";
        $oracle->query($sql);
}

Trecho do código em php

for($i=1;$i<=10000;$i++)
{
        $sql = "INSERT INTO country(id, pais) VALUES('".$i."', 'registro-".$i."')";
        mysql_query($sql, $link);
}

utilizei a função time() do php para medir o timestamp inicial e final, a diferença deles é o tempo da operação em segundos.

Resultados:

–> inicio e fim são timestamp, tempo total em segundos
Oracle:
INICIO: 1316194690
FIM: 1316194729
Tempo Total: 39
Espaço ocupado: 500kb

MySQL INNODB:
INICIO: 1316194769
FIM: 1316194772
Tempo Total: 3
Espaço ocupado: 416Kb

MySQL MyISAM:
INICIO: 1316194854
FIM: 1316194857
Tempo Total: 3
Espaço ocupado: 336.99Kb

Conclusão:
Foi um teste muito simples para afirmar qual é o melhor, mas podemos ver que para operações simples, o mysql é quase 10 vezes mais rápido e ocupa menos espaço em disco.
Porém é fato sabido que o mysql perde performance na medida em que a quantidade de dados vai crescendo, já no oracle, quando isto acontece ele se mantem estável (com perda mínima, ou sem de performance).

Trabalhando com triggers (insert, update delete)

Eu utilizava um rotina em php para sincronizar duas tabelas, ela rodava via cron uma vez por hora, porém isso me gerava um certo incômodo, pois as mesmas mensuravam espaço em disco em disco de alguns servidores e as vezes precisava dos dados na hora.

Até que uma hora resolví deixar a preguiça de lado e fazer de uma forma decente, via trigger.

Deixo aqui compartilhado as trigger que criei, aonde podem ser utilizadas como exemplo para after insert, after update e after delete:

After Insert:

DELIMITER $$
CREATE DEFINER=CURRENT_USER TRIGGER sincroniza_backup AFTER INSERT ON tabela_origem
  FOR EACH ROW BEGIN
    INSERT INTO tabela_destino SET
	id 				= NEW.id_servidor,
	nome			= NEW.nome_servidor,
	particao		= NEW.particao,
	tamanho			= NEW.tamanho,
	ocupado			= NEW.ocupado,
	livre			= NEW.livre,
	percentualUso	= NEW.percentual_uso;
  END;
$$
DELIMITER ;

Aqui o parametro NEW, recebe os dados da row afetada pelo insert executado

Para after update:

DELIMITER $$
CREATE DEFINER=CURRENT_USER TRIGGER sincroniza_backup_update AFTER UPDATE ON tabela_origem
  FOR EACH ROW BEGIN
    UPDATE tabela_destino sb SET
	sb.nome			= NEW.nome_servidor,
	sb.ocupado		= NEW.ocupado,
	sb.livre			= NEW.livre,
	sb.percentualUso	= NEW.percentual_uso,
	sb.testeEmail	= NEW.teste_email
	WHERE sb.nome=NEW.nome_servidor AND sb.particao=NEW.particao;
  END;
$$
DELIMITER ;

Mais uma vez o parametro NEW recebe os dados da tabela de origem (as rows afetadas pelo update)

Esse é um pouco diferente, o after delete

DELIMITER $$
CREATE DEFINER=CURRENT_USER TRIGGER sincroniza_backup_delete AFTER DELETE ON servidor_backup
  FOR EACH ROW BEGIN
    DELETE FROM ServidorBackup WHERE
	nome=OLD.nome_servidor AND particao=OLD.particao;
  END;
$$
DELIMITER ;

Nesse caso o valor que as rows deletas continham vão pro OLD

Agora o bonus stage:
Para deletar uma trigger

DROP TRIGGER sincroniza_backup_update;

———————————————-
Posted Listening: “Schools Out – Alice Cooper”

Deletar registros com mais de 30 dias

Essa é básica, mas vejo muita gente se quebrando e fazendo códigos mirbolantes via script pra isso.

Para deletar qualquer registro com mais de 30 dias somente usando SQL (não esqueça de substituir o campo `date` pelo nome do campo que irá utilizar e `minha_tabela` pelo nome da tabela.

DELETE FROM `minha_tabela` WHERE `date` < DATE_SUB(NOW(), INTERVAL 30 DAY);

———————————————-
Posted Listening: “The Evil That Men Do – Iron Maiden”