GUOB Tech Day 2017

15 julho 2015

Evite erros ajustando case sensitive do MySQL

Uma situação muito comum: ambiente de desenvolvimento Windows e ambiente de homologação e produção Linux. Se você se enquadra nela, uma maneira prática de evitar problemas relacionados aos nomes de tabelas é usar a configuração lower_case_table_names=1 em ambas plataformas.

Como o MySQL faz referências às tabelas

No MySQL, databases/schemas são diretórios e tabelas são arquivos no filesystem do SO. É fácil verificar criando uma tabela e listando os arquivos do datadir. Veja este exemplo no Windows:


mysql> CREATE DATABASE meudb;
mysql> USE meudb;
mysql> CREATE TABLE MinhaTabela(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT);


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| meudb              |
| mysql              |
| performance_schema |
+--------------------+


mysql> SELECT @@datadir;
+-------------------------------------------------+
| @@datadir                                       |
+-------------------------------------------------+
| D:\mysql-win\mysql-advanced-5.6.25-winx64\data\ |
+-------------------------------------------------+


C:\>DIR /b/ad D:\mysql-win\mysql-advanced-5.6.25-winx64\data\
meudb
mysql
performance_schema


C:\Users\alastori>DIR /b D:\mysql-win\mysql-advanced-5.6.25-winx64\data\meudb
db.opt
minhatabela.frm
minhatabela.ibd

O MySQL mantém referências entre as tabelas que podem ser manipuladas via SQL e estes arquivos e vai acessá-los sempre que precisar.

O MySQL é ou não é case sensitive ao referenciar tabelas?

O MySQL nas suas configurações padrão pode se comportar de maneira diferente dependendo do Sistema Operacional que está instalado.


No Windows o filesystem não é case-sensitive e não importa se você varia maísculas ou minúsculas ao referenciar uma tabela. Continuando com exemplo anterior:
mysql> USE meudb;
mysql> SELECT * FROM minhatabela;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


mysql> SELECT * FROM MinhaTabela;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


mysql> SELECT * FROM MINHATABELA;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


No Linux os filesystems normalmente são case-sensitive, portanto uma tabela criada com o nome MinhaTabela é diferente de uma tabela criada como minhatabela que também é diferente de MINHATABELA. Na configuração padrão, qualquer referência às tabelas em comandos SQL deve ser idêntica ao que foi definido na criação da tabela e também à forma que o nome do arquivo está no filesystem do SO. Veja um exemplo em outra instância MySQL, agora no Linux:
mysql> SELECT @@version, @@version_compile_os, @@datadir\G
*************************** 1. row ***************************
              @@version: 5.6.25-enterprise-commercial-advanced
   @@version_compile_os: linux-glibc2.5
              @@datadir: /var/lib/mysql/


mysql> CREATE DATABASE meudb;
mysql> USE meudb;
mysql> CREATE TABLE MinhaTabela(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT);
mysql> INSERT INTO MinhaTabela VALUES (),(),();


# ls /var/lib/mysql/meudb
db.opt  MinhaTabela.frm  MinhaTabela.ibd


mysql> USE meudb;
mysql> SELECT * FROM minhatabela;
ERROR 1146 (42S02): Table 'meudb.minhatabela' doesn't exist


mysql> SELECT * FROM MinhaTabela;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


mysql> SELECT * FROM MINHATABELA;
ERROR 1146 (42S02): Table 'meudb.MINHATABELA' doesn't exist


Note que se você fizer um SELECT * FROM MinhaTabela os arquivos no datadir devem estar nomeados exatamente como MinhaTabela e não poderão ser MINHATABELA, nem minhatabela. Isto pode ser uma fonte de erros quando o ambiente de desenvolvimento é Windows e o de homologação ou produção é Linux. Um exemplo é o MySQL não localizar os arquivos das tabelas existentes e retornar erros parecidos com ERROR 1017 (HY000): Can't find file: './meudb/minhatabela.frm' (errno: 2 - No such file or directory).

Como evitar problemas de case sensitive no Linux

Uma solução prática para evitar problemas de portabilidade é tomar duas ações:
  1. garantir que o MySQL sempre crie arquivos no filesystem para tabelas usando caracteres minúsculos, em todas plataformas.
  2. configurar o MySQL para ignorar maíusculas ou minúsculas (case insensitive) nas operações envolvendo tabelas.


A opção lower_case_table_names=1 implementa estas duas ações. Ou seja, sempre converte os nomes de arquivos para minúsculas no momento da criação da tabela e também relaxa o SQL para aceitar tanto minúsculas quanto maiúsculas para nomes de tabelas.


No Windows a configuração padrão já é lower_case_table_names=1.
mysql> SELECT @@version, @@version_compile_os, @@lower_case_table_names\G
*************************** 1. row ***************************
              @@version: 5.6.25-enterprise-commercial-advanced
   @@version_compile_os: Win64
@@lower_case_table_names: 1


No Linux a configuração padrão é  lower_case_table_names=0.
mysql> SELECT @@version, @@version_compile_os, @@lower_case_table_names\G
*************************** 1. row ***************************
              @@version: 5.6.25-enterprise-commercial-advanced
   @@version_compile_os: linux-glibc2.5
@@lower_case_table_names: 0


Se você configurar lower_case_table_names=1 em ambas plataformas, será a opção menos propensa a erros. Você não terá que ajustar todas suas queries, pois a comparação de nomes no SQL será case insensitive, independente da plataforma.


Atenção! Antes de configurar lower_case_table_names=1, se você já possui tabelas que contém maiúsculas, renomeie-as para minúsculas. Se você não renomear as tabelas ANTES de configurar lower_case_table_names=1, o MySQL não localizará os arquivos das tabelas existentes e retornará um ERROR 1017 (HY000): Can't find file: '...' (errno: 2 - No such file or directory).


Uma maneira prática de renomear todas as tabelas é gerar os comandos RENAME com esta query abaixo (substitua seu_database):
USE seu_database;
SELECT CONCAT('RENAME TABLE ', table_name, ' TO ' , LOWER(table_name) , ';')
   FROM information_schema.tables
   WHERE table_schema = 'seu_database';


No nosso exemplo, usando a instância Linux o procedimento seria:
# ls /var/lib/mysql/meudb
db.opt  MinhaTabela.frm  MinhaTabela.ibd


mysql> SELECT CONCAT('RENAME TABLE ', table_name, ' TO ', LOWER(table_name), ';') FROM information_schema.tables WHERE table_schema='meudb';
+---------------------------------------------------------------------+
| CONCAT('RENAME TABLE ', table_name, ' TO ', LOWER(table_name), ';') |
+---------------------------------------------------------------------+
| RENAME TABLE MinhaTabela TO minhatabela;                            |
+---------------------------------------------------------------------+


mysql> USE meudb;
mysql> RENAME TABLE MinhaTabela TO minhatabela;


# ls /var/lib/mysql/meudb
db.opt  minhatabela.frm  minhatabela.ibd


vi /etc/my.cnf
[mysqld]
lower_case_table_names=1


# /etc/init.d/mysql restart


mysql> SELECT @@version_compile_os, @@lower_case_table_names\G
*************************** 1. row ***************************
@@version_compile_os: linux-glibc2.5
@@lower_case_table_names: 1


mysql> USE meudb;
mysql> SELECT * FROM minhatabela;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


mysql> SELECT * FROM MinhaTabela;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


mysql> SELECT * FROM MINHATABELA;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+


Note que agora as queries funcionam no Linux independente do case, além dos nomes dos arquivos no filesystem sempre ficarem em minúsculas. Ou seja, o mesmo comportamento que tínhamos no Windows.


Há outras possibilidades para a configuração lower_case_table_names e para saber mais consulte o manual.


Nota: esta recomendação funciona para MySQL 5.0 até 5.6. No MySQL 5.7 o comportamento pode mudar com o novo dicionário de dados. Se você usa replicação, há alguns cuidados adicionais.


Referência:



26 março 2015

MySQL no Docker

docker mysql.png





Numa definição muito simplista, o Docker é um modo de virtualização mais leve. Na virtualização “tradicional” para ter imagem utilizável você precisa criar a máquina virtual e depois instalar o Sistema Operacional. Com o Docker você pode ter várias imagens sobre o mesmo SO ao mesmo tempo em que mantém o isolamento das aplicações e bibliotecas em cada imagem.


vm-stack.png
docker-stak.png


Ao invés de múltiplas VMs, teremos múltiplos containers. Múltiplos SOs dão suporte à tecnologia de containers, em destaque o Linux. Sendo assim, o kernel do SO provê o suporte aos containers e o Docker é uma camada de abstração para facilitar seu uso, similar ao que o Vagrant faz para VMs. Isto nos dá a vantagem adicional do Docker ser agnóstico ao hardware e à plataforma, ou seja, os containers podem ser executados em qualquer lugar, desde seu laptop até grandes máquinas, sejam eles virtuais ou físicas e de qualquer fornecedor. Se você está interessado em detalhes de como a tecnologia de containers funciona, um bom ponto de partida é a documentação do Oracle Linux, cap. 9.


Na prática, se comparado com VMs, os containers usarão muito menos recursos da máquina para ter várias aplicações ou instâncias de aplicações devidamente isoladas, com suas respectivas bibliotecas. Isto traz um grande benefício não só para desenvolvedores que desejam ter múltiplas versões de aplicações, mas também para Administradores de Sistemas que precisam gerenciar múltiplas instâncias de aplicações e extrair o máximo do hardware.


O MySQL também está disponível para uso com Docker. Portanto, é uma ótima opção usar o Docker para ter múltiplas instâncias de MySQL na mesma máquina, ou seja, uma alternativa ao mysql_multi. Por exemplo, podemos usar esta tecnologia para ajudar o desenvolvedor a ter múltiplas instâncias de MySQL na mesma máquina para testar a replicação com read-write split ou o isolamento de instâncias por cliente (simular multi-tenant).


Se você é novo na utilização do Docker, é recomendado seguir este rápido tutorial: https://www.docker.com/tryit.


Instalação do Docker no Oracle Linux 7



Vamos utilizar o Oracle Linux 7 como SO para testar o MySQL no Docker. Você encontra um tutorial de como criar uma VM com Oracle Linux aqui: http://www.alastori.com.br/2014/11/tutorial-ambiente-de-testes-com-mysql.html. Neste tutorial, você pode ignorar a seção de instalação do MySQL Enterprise.


Para os testes, desabilite temporariamente o SELinux ou deixe no modo Permissive:
# setenforce Permissive


Para instalar o Docker, primeiro habilite add ons do Oracle Linux 7. Edite as configurações do repositório public-yum para enabled=1 na seção [ol7_addons]:
# vi /etc/yum.repos.d/public-yum-ol7.repo


[ol7_addons]
name=Oracle Linux $releasever Add ons ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL7/addons/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1


Agora prossiga com a instalação do Docker via YUM:
# yum install docker


Para inicializar simplesmente faça:
# systemctl start docker.service


Adicione a inicialização no boot:
# systemctl enable docker.service


Pronto! Se você deseja utilizar o Oracle Linux 6, o procedimento é muito parecido, veja https://docs.docker.com/installation/oracle. Se quiser utilizar outro SO, veja a respectiva documentação no site do Docker https://docs.docker.com/installation.

MySQL no Docker



Pensando no Docker como uma “VM light”, o próximo passo é intuitivo: instalar o MySQL. Isso é perfeitamente possível, porém a Oracle já disponibiliza imagens otimizadas com o MySQL pré-instalado. Estas imagens estão disponíveis em no Docker Hub Registry https://registry.hub.docker.com/u/mysql/mysql-server.


Para criar uma nova instância com a versão mais recente estável do MySQL basta executar:
# docker run --name container-teste-mysql -e MYSQL_ROOT_PASSWORD=senha-mysql -d mysql/mysql-server:latest


Se tudo ocorreu bem, você verá a seguinte mensagem:
Status: Downloaded newer image for mysql/mysql-server:latest
...


Veja os containers que estão em execução com:
# docker ps
CONTAINER ID  IMAGE                      ... NAMES                      ...
08cd9827bf73  mysql/mysql-server:latest  ... container-teste-mysql      ...


Agora você pode acessar o shell do container e testar o MySQL:
# docker exec -it container-teste-mysql bash
# mysql -uroot -p
(senha definida no passo anterior como senha-mysql)


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23 MySQL Community Server (GPL)


Copyright (c) 2000, 2015, 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> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.23    |
+-----------+
1 row in set (0.00 sec)
mysql> quit
# exit


Você pode parar ou pausar o container com os comandos stop, start, pause, unpause. Por exemplo:
# docker stop container-teste-mysql
# docker ps -a
# docker start container-teste-mysql
# docker ps -a


Simples! Não é mesmo?


Acesso ao MySQL fora do container



Esta imagem está pré-configurada para que o MySQL escute a porta padrão (3306). A maneira mais usual de acessar externamente um container é linkar com outro container que contém a aplicação.


Usaremos o mysql client (linha de comando) a partir de outro container para conectar ao container-teste-mysql que já iniciamos anteriormente. Para este teste simples, vamos reutilizar a mesma imagem MySQL, pois ela já possui o mysql client instalado. Para iniciar um novo container com o link para o container original faça:


# docker run -it --link container-teste-mysql:mysql1 --name novo-container-teste-mysql -e MYSQL_ROOT_PASSWORD=senha-mysql -d mysql/mysql-server:latest


# docker ps
CONTAINER ID  IMAGE                      ... NAMES                      ...
2b65839aecba  mysql/mysql-server:latest  ... novo-container-teste-mysql ...
08cd9827bf73  mysql/mysql-server:latest  ... container-teste-mysql      ...


No parâmetro --link especificamos o nome do container que queremos linkar e também um alias. Dentre outras coisas, o alias é usado para atualizar o arquivo /etc/hosts da nova “máquina” com o IP da “máquina” linkada. Usaremos este nome como rota de acesso.


Entre na linha de comando do novo-container-teste-mysql e tente conectar-se a container-teste-mysql:
# docker exec -it novo-container-teste-mysql bash
# mysql -uroot -p --host=mysql1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.23 MySQL Community Server (GPL)


Copyright (c) 2000, 2015, 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>

Antes de usar em produção



Como toda tecnologia nova, tome alguns cuidados antes de usar em produção, principalmente com as questões de segurança. Veja a documentação do Docker que possui algumas informações específicas sobre segurança.


No caso específico de MySQL, não deixe de ler a seção “Where to Store Data” do MySQL no repositório Registry Hub.


Conclusão



O Docker é um projeto muito interessante que facilita o uso da tecnologia de containers. Tanto desenvolvedores quanto Administradores de Sistema podem beneficiar-se do uso desta tecnologia. Como o MySQL escala tipicamente usando uma abordagem horizontal, conhecer esta tecnologia pode facilitar muito o desenvolvimento e deploy de múltiplas aplicações. Este artigo é apenas uma introdução e há muito mais o que explorar! Deixe nos comentários sugestões para novos artigos.


Referências: