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↳Linux34 conteúdosKali Linux em um Servidor VPS: como, quando e por que usar?DevSecOps · dez 2024Construindo um Windows Service ou Linux Daemon com Worker Service & .NET Core – Parte 2Dev (Back & Front) · jul 2020Criando uma WebApi utilizando .NET, Linux e VSCodeDev (Back & Front) · ago 2019Ver tudo em DevSecOps →. 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↳MySQL8 conteúdosMySQL + Adminer + Docker Compose: montando rapidamente um ambiente para usoData · abr 2019Banco de dados MYSQL no PHPStormDev (Back & Front) · jul 2019Criando um ambiente de desenvolvimento PHP mínimo com Docker – Parte 3Dev (Back & Front) · out 2019Ver tudo em Data → 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 esses 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 o 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 como 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. Isso 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 maiúsculas ou minúsculas (case insensitive) nas operações envolvendo tabelas.

A opção lower_case_table_names=1 implementa essas 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, independentemente 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 <i>seu_database</i>):

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 independentemente 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.