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:



Nenhum comentário: