SQLRaptor's questions

Estou tentando usar funções no MySQL 8 e falhando. Eu li a documentação e até onde posso dizer, estou fazendo certo. Como esta é uma tentativa de nível 'hello world', suponho que estou perdendo algo muito óbvio, mas não consigo descobrir. Qualquer ajuda será muito apreciada.

Aqui está o que estou tentando fazer.

CREATE USER 'reader'@'localhost' IDENTIFIED BY 'reader_password';

CREATE ROLE 'read_all';

GRANT SELECT ON MySchema.* TO 'read_all';

GRANT 'read_all' TO 'reader'@'localhost';

O script acima é executado com o usuário root e todas as instruções são bem-sucedidas.

Em seguida, abro uma nova conexão usando as credenciais do usuário 'leitor' e conecto com sucesso. No entanto, qualquer tentativa de SELECT de qualquer tabela em MySchema é negada com a mensagem de erro:

SELECT * FROM MySchema.SomeTable;

SQL Error [1142] [42000]: SELECT command denied to user 'reader'@'localhost' for table 'SomeTable'

Espero que este SELECT seja bem-sucedido, pois a função tem permissões SELECT e o usuário do leitor recebeu a função. o que estou perdendo?

Tenha um ótimo final de semana!

ATUALIZAÇÃO: Após o comentário de Rick James, aqui estão os SHOWs relevantes:

Na conexão raiz (com contexto de esquema ativo 'MySchema'):

SHOW CREATE USER 'reader'@'localhost';
--------------------------------------
CREATE USER 'reader'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*96B291A4F8FC2B6C453E4C54AD2080751B0D4712' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT

SHOW CREATE USER 'read_all';
----------------------------
CREATE USER 'read_all'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT

SHOW GRANTS FOR 'reader'@'localhost';
-------------------------------------
GRANT USAGE ON *.* TO `reader`@`localhost`  
GRANT `read_all`@`%` TO `reader`@`localhost`

SHOW GRANTS FOR 'read_all';
----------------------------
GRANT USAGE ON *.* TO `read_all`@`%`         
GRANT SELECT ON `MySchema`.* TO `read_all`@`%

E na conexão do leitor:

SHOW GRANTS;
------------
GRANT USAGE ON *.* TO `reader`@`localhost`  
GRANT `read_all`@`%` TO `reader`@`localhost`

Olá a todos e desde já agradeço a vossa ajuda. Estamos enfrentando desafios com os Grupos de Disponibilidade do SQL Server 2017.

Fundo

A empresa é um software de back-end B2B de varejo. Cerca de 500 bancos de dados de locatário único e 5 bancos de dados compartilhados usados ​​por todos os locatários. A característica da carga de trabalho é lida principalmente e a maioria dos bancos de dados tem atividade muito baixa.

Servidores de produção física hospedados em co-localização foram recentemente atualizados do SQL Server 2014 Enterprise no Windows Server 2012 em uma configuração de SAN/FCI compartilhada, para o SQL Server 2017 Enterprise no Windows Server 2016 em 2 soquetes/32 núcleos/768 GB de RAM e local Unidades SSD usando AlwaysOn AG. O tráfego AG usa portas NIC 10G dedicadas com conexão de cabo cruzado.

O requisito deles é que todos os bancos de dados façam failover juntos, então eles tiveram que colocá-los todos em um único AG. É uma réplica síncrona única e não legível em um servidor idêntico.

Os novos servidores estão em produção desde junho de 2018. A última CU (CU7 na época) e as atualizações do Windows foram instaladas e o sistema estava funcionando bem. Cerca de um mês depois, após atualizar os servidores de CU7 para CU9, eles começaram a perceber os seguintes desafios, listados em ordem de prioridade.

Temos monitorado os servidores usando o SQL Sentry e não observamos gargalos físicos. Todos os principais indicadores parecem bons. A CPU tem uma média de 20%, tempos de E/S normalmente inferiores a 1 ms, RAM não totalmente utilizada e rede <1%.

Desafios

Os sintomas parecem melhorar após o failover, mas voltam em alguns dias, independentemente do servidor principal - os sintomas são idênticos em ambos os servidores.

1. Tempos limite esporádicos do cliente e falhas de conectividade, como

   ...ocorreu um erro ao estabelecer a conexão...

   ou

   O tempo limite de execução expirou

   Às vezes, eles duram até 40 segundos e depois diminuem.

2. O trabalho de backup do log de transações leva 10 vezes mais para ser concluído do que antes. Anteriormente, levava de 2 a 3 minutos para fazer backup dos logs de todos os 500 bancos de dados, agora leva de 15 a 25. Verificamos que o próprio Backup funciona bem com boa taxa de transferência. No entanto, há um pequeno atraso após a conclusão do backup de um log e antes de iniciar o próximo. começa muito baixo, mas dentro de um dia ou dois chega a 2-3 segundos. Multiplicado por 500 bancos de dados, e aí está a diferença.

3. Ocasionalmente, alguns bancos de dados aparentemente aleatórios ficam presos no estado "Não sincronizando" após o failover manual. A única maneira de resolver isso é reiniciar o SQL Server Service na réplica secundária ou remover e reingressar esses bancos de dados no AG.

4. Outro problema introduzido pelo CU10 (e não resolvido no CU11): conexões com o tempo limite secundário no bloqueio em master.sys.databases e até mesmo incapaz de usar o explorador de objetos SSMS para réplica secundária. A causa raiz parece estar bloqueando pelo gravador VSS do Microsoft SQL Server emitindo a seguinte consulta:

   select name, 
          recovery_model_desc, 
          state_desc, 
          CONVERT(integer, is_in_standby), 
          ISNULL(source_database_id,0) 
     from master.sys.databases
   

Observações

Acredito que encontrei a arma fumegante nos logs de erro. Os logs de erros estão cheios de mensagens AG, que são rotuladas como 'somente informativas', mas parece que não são nada normais, e há uma correlação muito forte de sua frequência com os erros do aplicativo.

Os erros são de vários tipos e vêm em sequências:

• DbMgrPartnerCommitPolicy::SetSyncState: GUID

• DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: GUID

• A conexão dos Grupos de Disponibilidade AlwaysOn com o banco de dados secundário foi encerrada para o banco de dados primário 'XYZ' na réplica de disponibilidade 'DB' com ID da réplica: {GUID}. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

• Conexão dos Grupos de Disponibilidade AlwaysOn com o banco de dados secundário estabelecido para o banco de dados primário 'ABC' na réplica de disponibilidade 'DB' com ID da réplica: {GUID}. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

Alguns dias há 10 de milhares desses.

Este artigo discute o mesmo tipo de sequência de erros no SQL 2016 e lá diz que é anormal. Isso também explica o fenômeno de 'não sincronização' após o failover. O problema discutido era de 2016 e foi corrigido no início deste ano em uma UC. no entanto, é a única referência relevante que pude encontrar para os 2 primeiros tipos de mensagens, além de referências a mensagens de propagação inicial automática, o que não deve ser o caso aqui, pois o AG já está estabelecido.

Aqui está um resumo dos erros diários da semana passada, para dias com mais de 10 mil erros por tipo no PRIMARY (o secundário mostra 'perdendo a conexão com o primário...'):

Date        Message Type (First 50 characters)                  Num Errors
10/8/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  61953
10/3/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  56812
10/4/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  27951
10/2/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  24158
10/7/2018   DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint:  14904
10/8/2018   Always On Availability Groups connection with seco  13301
10/3/2018   DbMgrPartnerCommitPolicy::SetSyncState: 783CAF81-4  11057
10/3/2018   Always On Availability Groups connection with seco  10080

Também ocasionalmente vemos mensagens "estranhas", como:

O banco de dados do grupo de disponibilidade "DB" está alterando as funções de "SECONDARY" para "SECONDARY" porque a sessão de espelhamento ou o grupo de disponibilidade falhou devido à sincronização de função. Esta é apenas uma mensagem informativa. Não é necessária nenhuma ação do usuário.

... entre uma série de estados em mudança de "SECUNDÁRIO" para "RESOLVIDO".

Após o failover manual, o sistema pode ficar vários dias sem uma única mensagem desses tipos e, de repente, sem motivo aparente, receberemos milhares de uma vez, o que, por sua vez, faz com que o servidor pare de responder e faça com que o aplicativo tempos limite de conexão. Este é um bug crítico, pois alguns de seus aplicativos não incorporam um mecanismo de repetição e, portanto, podem perder dados. Quando ocorre uma explosão de erros, os tipos de espera a seguir disparam. Isso mostra as esperas logo após o AG parecer ter perdido a conexão com todos os bancos de dados de uma só vez:

Cerca de 30 segundos depois, tudo volta ao normal em termos de esperas, mas as mensagens AG continuam inundando os logs de erros em taxas variadas e durante diferentes horários do dia, horários aparentemente aleatórios, incluindo horários fora de pico. O aumento simultâneo na carga de trabalho durante essas rajadas de erro piora as coisas, é claro. Se apenas alguns bancos de dados forem desconectados, isso normalmente não causa o tempo limite das conexões, pois é resolvido com rapidez suficiente por conta própria.

Tentamos verificar se foi realmente o CU9 que iniciou o problema, mas conseguimos fazer o downgrade de ambos os nós apenas para o CU9. As tentativas de fazer o downgrade de qualquer nó para CU8 resultaram em que o nó ficasse preso no estado 'Resolvendo' mostrando o mesmo erro no log:

Não é possível ler a configuração persistente do grupo de disponibilidade Always On com a ID de recurso correspondente '…. A configuração persistente é gravada por um SQL Server de versão superior que hospeda a réplica de disponibilidade primária. Atualize a instância local do SQL Server para permitir que a réplica de disponibilidade local se torne uma réplica secundária.

Isso significa que teremos que introduzir tempo de inatividade para poder fazer o downgrade de ambos os nós para CU8 ao mesmo tempo. Isso também sugere que houve alguma atualização importante no AG que pode explicar o que estamos enfrentando.

Já tentamos ajustar o max_worker_threads do padrão de 0 (=960 em nossa caixa com base neste artigo ) gradualmente até 2.000 sem impacto observado nos erros.

O que podemos fazer para resolver essas desconexões AG? Alguém aí está passando por problemas semelhantes? Outras pessoas com grande número de bancos de dados em um AG talvez vejam mensagens semelhantes no log de erros SQL começando com CU9 ou CU8?

Agradecemos antecipadamente por qualquer ajuda!

Eu adicionei uma solução sem usar funções de janela e um benchmark com um grande conjunto de dados abaixo da resposta de Martin

Este é um thread de acompanhamento para GROUP BY usando colunas que não estão na lista SELECT - quando isso é prático, elegante ou poderoso?

Na minha solução para esse desafio, utilizo uma consulta que agrupa por uma expressão que não faz parte da lista de seleção. Isso é frequentemente usado com funções de janela, quando o elemento de agrupamento lógico envolve dados de outras linhas.

Talvez isso seja um exagero como exemplo, mas achei que você poderia achar o desafio interessante por si só. Vou esperar postando minha solução, talvez alguns de vocês possam criar melhores.

Desafio

Temos uma tabela de sensores que registra periodicamente os valores de leitura. Não há garantia de que os tempos de amostragem estejam em intervalos monótonos.

Você precisa escrever uma consulta que informará as 'exceções', ou seja, as vezes em que os sensores relataram leitura fora do limite, baixa ou alta. Cada período de tempo que o sensor estava relatando acima ou abaixo dos valores limite é considerado uma 'exceção'. Uma vez que a leitura voltou ao normal, a exceção termina.

Tabelas e dados de amostra

O script está em T-SQL e faz parte dos meus materiais de treinamento.

Aqui está um link para o SQLFiddle .

------------------------------------------
-- Sensor Thresholds - 1 - Setup Example --
------------------------------------------

CREATE TABLE [Sensors]
(
    [Sensor] NVARCHAR(10) NOT NULL,
    [Lower Threshold] DECIMAL(7,2) NOT NULL,
    [Upper Threshold] DECIMAL(7,2) NOT NULL,
    CONSTRAINT [PK Sensors] 
        PRIMARY KEY CLUSTERED ([Sensor]),
    CONSTRAINT [CK Value Range]
        CHECK ([Upper Threshold] > [Lower Threshold])
);
GO

INSERT INTO [Sensors]
( 
    [Sensor] ,
    [Lower Threshold] ,
    [Upper Threshold]
)
VALUES  (N'Sensor A', -50, 50 ),
        (N'Sensor B', 40, 80),
        (N'Sensor C', 0, 100);
GO

CREATE TABLE [Measurements]
(
    [Sensor] NVARCHAR(10) NOT NULL,
    [Measure Time] DATETIME2(0) NOT NULL,
    [Measurement] DECIMAL(7,2) NOT NULL,
    CONSTRAINT [PK Measurements] 
        PRIMARY KEY CLUSTERED ([Sensor], [Measure Time]),
    CONSTRAINT [FK Measurements Sensors] 
        FOREIGN KEY ([Sensor]) 
        REFERENCES [Sensors]([Sensor])
);
GO

INSERT INTO [Measurements]
( 
    [Sensor] ,
    [Measure Time] ,
    [Measurement]
)
VALUES  ( N'Sensor A', N'20160101 08:00', -9), 
        ( N'Sensor A', N'20160101 09:00', 30), 
        ( N'Sensor A', N'20160101 10:30', 59), 
        ( N'Sensor A', N'20160101 23:00', 66),  
        ( N'Sensor A', N'20160102 08:00', 48), 
        ( N'Sensor A', N'20160102 11:30', 08), 
        ( N'Sensor B', N'20160101 08:00', 39), -- Note that this exception range has both over and under....
        ( N'Sensor B', N'20160101 10:30', 88), 
        ( N'Sensor B', N'20160101 13:00', 75), 
        ( N'Sensor B', N'20160102 08:00', 95),  
        ( N'Sensor B', N'20160102 17:00', 75), 
        ( N'Sensor C', N'20160101 09:00', 01), 
        ( N'Sensor C', N'20160101 10:00', -1),  
        ( N'Sensor C', N'20160101 18:00', -2), 
        ( N'Sensor C', N'20160101 22:00', -2), 
        ( N'Sensor C', N'20160101 23:30', -1);
GO

resultado esperado

Sensor      Exception Start Time    Exception End Time  Exception Duration (minutes)    Min Measurement Max Measurement Lower Threshold Upper Threshold Maximal Delta From Thresholds
------      --------------------    ------------------  ----------------------------    --------------- --------------- --------------- --------------- -----------------------------
Sensor A    2016-01-01 10:30:00     2016-01-02 08:00:00 1290                            59.00           66.00           -50.00          50.00           16.00
Sensor B    2016-01-01 08:00:00     2016-01-01 13:00:00 300                             39.00           88.00           40.00           80.00           8.00
Sensor B    2016-01-02 08:00:00     2016-01-02 17:00:00 540                             95.00           95.00           40.00           80.00           15.00
Sensor C    2016-01-01 10:00:00     2016-01-01 23:30:00 810                             -2.00           -1.00           0.00            100.00          -2.00
*/