Resolver problema "Got error 127 from table handler" no MySQL

Para resolver o problema "Got error 127 from table handler" em uma tabela é rápido: 

mysql> repair table tabela 

Exemplo:

mysql> repair table clientes ;

Este erro é causado principalmente quando alguém copia e cola o conteúdo da pasta MySQL pensando que está realizando um backup do BD. 

MySQL - Comandos básicos

Acessando banco como root: 

# mysql -u root -p 

Criando base de dados guiadoti:

mysql> create DATABASE guiadoti;

Selecionar banco:

mysql> USE guiadoti;

Mostrar tabelas:

mysql> show tables;

Criando tabela:

mysql> create TABLE endereco (
id_endereco smallint NOT NULL,
rua varchar(100) NOT NULL,
bairro varchar(25) NOT NULL,
cidade varchar(25),
primary key(id_endereco)
);

Visualizar colunas da tabela:

mysql> DESC endereco;

Inserindo coluna na tabela:

mysql> ALTER TABLE endereco ADD pais varchar(25);

Remover chave primária da tabela:

mysql> ALTER TABLE endereco DROP primary key; Inserindo chave primária na tabela:

mysql> ALTER TABLE endereco ADD PRIMARY KEY(id_endereco);

Modificar definições de uma coluna:

mysql> ALTER TABLE endereco MODIFY bairro varchar(50);

Excluir coluna da tabela:

mysql> ALTER TABLE endereco DROP cidade;

Renomear tabela:

mysql> ALTER TABLE endereco RENAME localizacao;

Deletar uma tabela:

mysql> DROP TABLE localizacao;

Deletar uma base de dados: 

Excluindo usuário no MySQL

Podemos utilizar o comando DROP USER e o comando DELETE.

Com o comando DROP USER devemos informar o nome do usuário e qual o host de conexão ele pertence:
DROP USER 'marta'@'%';

Também podemos realizar a deleção do usuário com o comando delete:
DELETE FROM mysql.user WHERE user = 'marta';

  
Explicando o comando, estamos deletando (delete from) da tabela user onde a database é mysql (mysql.user) o usuário cujo o nome seja 'marta'.

MySQL - Gerenciamento de memória

Eu tenho trabalhado com o MySQL mas, nas versões para Ubuntu e Debian e versões mais atuais, os nós e nódulos da memória (cache) sempre estão cheios, e o MySQL acaba consumindo muita memória RAM.

Assim, sempre tive problemas em usar mais de uma base de dados. Em 24 horas, estava tendo que reiniciar o servidor umas 3 vezes, no mínimo, para o MySQL voltar a funcionar.

Um exemplo que acontecia sempre: de 10 bases de dados, 6 estavam funcionando normalmente, mas 4 simplesmente paravam de gravar os dados.

A solução:

# crontab -e

Adicione:

# m h   dom mon dow    command
0 * * * * /root/./germem

E crie o script germem:

#!/bin/bash
echo 3 > /proc/sys/vm/drop_caches

Obs.: Para o drop_caches, há opções de 1 a 3. Eu escolhi a 3, pois ela limpa os nós e nódulos da memória. 

O que são tabelas temporárias? Para que servem? Como se criam?

O que é uma Tabela Temporária Local?
É uma tabela semelhante em tudo às tabelas "normais" mas com algumas particularidades: o seu tempo de vida é igual ao da ligação (connection) usada para a sua criação. Ou seja quando a "connection" for fechada a tabela deixa de existir e além disso só pode ser acedida no âmbito dessa "connection", daí ser local à "connection".

Com efeito as Tabelas temporárias são criadas fisicamente numa Base de Dados de sistema designada por tempdb.
Mas em termos de implementação isso não é particularmente relevante, visto que para todos os efeitos eu crio-a e uso-a apenas "dentro" da minha "connection".

Assim perante a pergunta que neste momento pode estar a bailar-lhe nos lábios: "Então posso criar várias tabelas temporárias locais com o mesmo nome numa BD?", a resposta é: SIM!

Isto porque internamente o SQL Server adiciona um sufixo a cada uma das tabelas de modo a saber qual diz respeito a que sessão.

Ok, agora que já sabemos o que é uma tabela temporária local, para que serve?
Estas tabelas são habitualmente usadas como tabelas auxiliares em determinados processos que envolvam as chamadas operações de "corte e costura".

Vou deixar aqui apenas alguns exemplos:

1. Conciliar dados provenientes de várias tabelas e aplicando-lhes  cálculos e/ou agregações, admitindo que esse resultado é suposto ser usado várias vezes no âmbito da mesma "connection". (Ganha-se performance)

2. Para permitir fazer filtros à cabeça i.e. imagine que possui duas tabelas com milhões de registos e precisa de as juntar, no entanto após a junção que produz ainda muitos registos existe uma cláusula WHERE (filtro) que vem reduzir substancialmente a quantidade de registos devolvidos. O problema é que o filtro pode ser aplicado após a junção e esta consome imenso tempo!
As tabelas temporárias podem ser usadas de modo a que seja aplicado primeiro o filtro a cada tabela original e passado o respectivo resultado para estas e depois juntam-se as tabelas temporárias que porque já têm muito menos    registos produzem o resultado final mais rapidamente. (Ganha-se performance)
 
3. Podem ser usadas em Stored Procedures para que no final seja devolvido um só "resulset" com toda a informação que foi sendo criada por exemplo por um ou mais processos iterativos dentro do SP ao longo da sua execução.

4. etc.

Nos meus exemplos utilizo várias vezes estas tabelas porque me permitem fazer demonstrações sem "invadir" as Bases de Dados com lixo, uma vez que a tabela e todo o conteúdo desaparece ao fechar a ligação.

O que é uma tabela temporária Global?Conceptualmente é parecida com a local mas com uma diferença substâncial: o seu âmbito.
Ou seja a Global é criada também ao nível de uma "connection" mas está acessível a todas as "connections" que entretanto sejam abertas e lhe pretendam aceder.

Quando é que esta desaparece? Quando for fechada a última "connection" que está a utilizá-la.
No fundo a ideia é permitir partilha da mesma informação (já devidamente tratada) em várias connections.

Nota: Aqui não podem ser criadas tabelas com o mesmo nome.

Como se cria uma Tabela Temporária Local?A sintaxe em T-SQL é igual à de uma tabela normal com uma simples diferença: O nome da tabela começa com um #
Ex: CREATE TABLE #Tabela (c1 int)

Como se cria uma Tabela Temporária Global?A sintaxe em T-SQL é igual à de uma tabela normal com uma simples diferença: O nome da tabela começa com dois ##
Ex: CREATE TABLE ##Tabela (c1 int)

Nota: Estas tabelas (quer as locais quer as globais) suportam todas as operações normais SELECT, INSERT, UPDATE, DELETE, etc e inclusivamente suportam transacções explícitas e índices.

Listando a localização física dos arquivos dos bancos de dados

Muitas vezes necessitamos identificar a localização física dos arquivos que compõem os bancos de dados, e ficar clicando com o botão direito do mouse em cada banco não é muito prático (imagine uma instância com 20 bancos). Todo esse esforço pode ser minimizado através da execução de uma simples query:

SELECT d.name, m.physical_name FROM sys.master_files m
inner join sys.databases d
on d.database_id = m.database_id

Curiosidade: Protocolo VIA no SQL Server 2012

Quem aqui já não viu lá no Configuration Manager do SQL Server na aba Network Configuration o protocolo VIA? Protoloco esse pouco visto no mercado sendo utilizado. Eu particularmente nunca vi ou conheci alguém que já utilizou esse protocolo em produção, pois ele é muito especifico para um determinado tipo de hardware.

Pois bem, a curiosidade é que no SQL Server 2012 esse protocolo foi retirado da lista dos protocolos disponíveis.

Habilitando sql authentication e o usuário “SA”

Um problema comum quando se utiliza o SQL Server é quando não é possível entrar com usuários SQL Server, exemplo o super usuário “sa”, pois durante a instalação foi configurado para Windows Authentication.

Como mencionado em um post no Fórum do MSDN: “Criei a instância, criei senha para meu login, mais quando vou entrar pela autenticação do SQL SERVER, ele dá erro, já pela da autenticação do Windows ele dá certo…”

Visto que não é possível ficar reinstalando o SQL Server, somente para trocar a autenticação de Windows Authentication para “Mixed” Authentication, segue neste post a solução para este problema.

Primeiramente é necessário entrar no SQL Server utilizando o SQL Server Management Studio. Conforme a figura abaixo, estou conectando em uma instância do SQL Server 2008 Express utilizando a autenticação Windows Authentication, mas este exemplo funciona perfeitamente no SQL Server 2005 e nas edições superiores.

Após conectar, clicando com o botão direito do mouse sobre a instância do SQL Server na janela Object Explorer (Atalho F8 ou Menu “View\Object Explorer”), item Properties.

Nesta nova janela, na aba (“página”) Security, altere “Server authentication” para “SQL Server and Windows Authentication mode”.

Feito isso, uma janelinha informará que para esta alteração ter efeito, será necessário que o serviço do SQL Server deve ser reiniciado. Mas isso pode ser feito depois, quando for terminada a configuração dos usuários do SQL Server, então deixe para depois.

Continuando na janela Object Explorer, expandindo a instância, “folder” Security\Logins, pode ser criado novos usuários do SQL Server ou seguindo o objetivo inicial deste tópico, habilitar o usuário “sa”. Conforme a figura abaixo, botão direito sobre o login “sa”, menu Properties.

Na nova janela, altere o password do usuário, também é possível alterar o “idioma” e o banco de dados padrão deste usuário nesta janela, entre outras funcionalidades.

Na aba (“página”) Status, clique na opção “Grant” em “Permission to connect to database engine” e “Enable” em “Login”.

Outra maneira pratica de habilitar o usuário “sa” é por meio de script, exemplo:

ALTER LOGIN sa ENABLE;

GO

ALTER LOGIN sa WITH PASSWORD = 'P@ssw0rdM0del0';

GO 

Então, agora resta reiniciar o SQL Server. Para não ter que entrar nos Serviços do Windows ou SQL Server Configuration Manager ou qualquer variante como arquivos .bat ou SQL Server Surface Area.

Uma dica é clicar com o botão direito na instância do SQL Server na janela Object Explorer e “Restart”.

Clique em Yes nesta próxima janelinha, para dizer que você TEM CERTEZA QUE QUER REINICIAR O SERVIÇO…

Agora é só conectar com o usuário “sa” para testar.

SQL Server 2012 – FileTable

Criação da base de dados com FileGroup e FileStream habilitado

CREATE DATABASE solucoesdba

ON

PRIMARY ( NAME = Arch1,

    FILENAME = 'd:\SQL_Server\2012\Data\solucoesdba.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,

    FILENAME = 'd:\SQL_Server\2012\Data\filestream')

LOG ON  ( NAME = Archlog1,

    FILENAME = 'd:\SQL_Server\2012\Log\solucoesdba.ldf')

GO

Criação da pasta dentro do FileStream para a utilização da FileTable

ALTER DATABASE BlogFileTable SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, 
DIRECTORY_NAME = 'solucoesdba') WITH NO_WAIT
GO

Aqui,voce já deve poder visualizar se o acesso FileStream esta habilitado em sua base de dados, vide select:

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc

FROM sys.database_filestream_options;

Todos os arquivos que iremos visualizar estará contido em uma tabela, obviamente, portanto, devemos criar esta tabela, que possui uma sintaxe curiosa, e um pouco diferente de um CREATE TABLE regular…

CREATE TABLE Documentos AS FileTable
    WITH ( 
          FileTable_Directory = 'solucoesdba',
          FileTable_Collate_Filename = database_default
         );
GO

Agora, basta você copiar e colar os arquivos no caminho especificado, que no meu caso, é: \\servidor\mssqlserver\solucoesdba\solucoesdba

Caso voce não esteja conseguindo abrir a pasta, execute:

SELECT FileTableRootPath ('Documentos', 2)
Ou seja, trocando “Documentos” pelo nome da sua tabela FileTable

O select nesta tabela pode e deve ser feito normalmente:

SELECT * FROM Documentos
Aonde sera possível encontrar N informações sobre o arquivo.


Outra curiosidade é a possibilidade de se criar diretórios, ou seja, alem de automático  sua arquivos também pode ficar organizados!

Gráfico para crescimento da base

É muito importante acompanharmos o crescimento dos arquivos de dados para podermos trabalhar preventivamente a questão de espaço em disco, além de melhorar o planejamento quando vamos configurar o tamanho dos arquivos. Hoje vamos gerar um gráfico como este: 

Primeiro vamos criar uma tabela para armazenar os dados, com uma coluna para data e outra para o tamanho:

-- coloque o nome de sua base no lugar de AventureWorks2008R2

USE AdventureWorks2008R2
CREATE TABLE datagrowth
(dia varchar(12) not null,
tamanho int not null)

Agora precisamos coletar os dados e inserí-los na tabela que criamos. Podemos obtê-los da sys.master_files da seguinte forma:

INSERT INTO datagrowth (dia, tamanho)

(SELECT CONVERT(VARCHAR(20),GETDATE(),6) AS dia, SUM(Size * 8 / 1024) AS tamanho
FROM sys.master_files 
WHERE name LIKE 'AdventureWorks2008R2_Data%')

SELECT * FROM datagrowth

Colocamos este código em um job para rodar diariamente e teremos esta informação para vários dias. Depois de alguns dias, faça novamente um SELECT na tabela filegrowth, copie e cole tudo no Excel. Selecione tudo e clique em Inserir:

Agora basta clicar em gráfico de linha e escolher o modelo que você quer. Neste exemplo vamos pegar o mais simples:

Com o gráfico na mão, agora é só ajustar tamanho, cores e editar o que você quiser:

Agora após pequenos ajustes:

Pronto, gráfico na mão. Agora é só justificar a compra de novos discos para antes do final do ano.

Verifica status dos Jobs

O Script abaixo fornece uma maneira simples de você verificar em qual estado encontra-se seu Job, sendo assim possível visualizar os valores: Falhou, Completou com sucesso, Cancelado e Nunca utilizado.

select job.name as nome,  
description as descricao, 
cast(isnull(NULLIF(SUBSTRING(cast(last_run_date as varchar(50)),1,4),'0') +'-'+ 
SUBSTRING(cast(last_run_date as varchar(50)),5,2) + '-'+ 
SUBSTRING(cast(last_run_date as varchar(50)),7,2),'2999-12-31') as datetime) as UltimaExecucao, 
Status =  
case when cast(last_run_outcome as varchar(10)) = 0 then 'Falhou'  
     when cast(last_run_outcome as varchar(10)) = 1 then 'Completou com sucesso'  
     when cast(last_run_outcome as varchar(10))= 3 then 'Cancelado' 
     when CAST(last_run_outcome as varchar(15)) = 5 then 'Nunca utilizado'  
else cast('Desconhecido' as varchar(15)) 
end 
from msdb.dbo.sysjobs as job 
left outer join msdb.dbo.sysjobservers as jobServer 
on job.job_id = jobServer.job_id 
where job.name not in ('syspolicy_purge_history','Output File Cleanup', 
'sp_delete_backuphistory','sp_purge_jobhistory') 
order by nome