SQLRaptor's questions

我正在尝试在 MySQL 8 中使用角色，但失败了。我已经阅读了文档，据我所知，我做得对。由于这是一个“hello world”级别的尝试，我认为我遗漏了一些非常明显的东西，但我无法弄清楚。任何帮助将不胜感激。

这就是我想要做的。

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';

上面的脚本是用root用户执行的，所有的语句都成功了。

然后，我使用“读者”用户凭据打开一个新连接，并成功连接。但是，任何从 MySchema 中的任何表中进行 SELECT 的尝试都会被拒绝并显示错误消息：

SELECT * FROM MySchema.SomeTable;

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

我希望这个 SELECT 能够成功，因为该角色具有 SELECT 权限，并且读者用户已被授予该角色。我错过了什么？

周末愉快！

更新：根据 Rick James 的评论，以下是相关的节目：

在根连接上（使用活动模式上下文“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`@`%

在阅读器连接上：

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

大家好，提前感谢您的帮助。我们在 SQL Server 2017 可用性组方面遇到了挑战。

背景

公司是一家零售B2B后端软件。大约 500 个单租户数据库，以及 5 个所有租户使用的共享数据库。工作负载特点主要是读取，大多数数据库的活动度很低。

托管在同一地点的物理生产服务器最近从共享 SAN/FCI 配置的 Windows Server 2012 上的 SQL Server 2014 Enterprise 升级到 2 插槽/32 核/768 GB RAM 和本地的 Windows Server 2016 上的 SQL Server 2017 Enterprise使用 AlwaysOn AG 的 SSD 驱动器。AG 流量使用带有交叉电缆连接的专用 10G NIC 端口。

他们要求所有数据库一起进行故障转移，因此他们必须将它们全部放在一个 AG 中。它是同一服务器上的单个不可读同步副本。

新服务器自 2018 年 6 月开始投入生产。安装了最新的 CU（当时为 CU7）和 Windows 更新，系统运行良好。大约一个月后，在将服务器从 CU7 更新到 CU9 后，他们开始注意到以下挑战，按优先级排列。

我们一直在使用 SQL Sentry 监控服务器，并没有观察到物理瓶颈。所有关键指标似乎都不错。CPU 平均为 20%，IO 时间通常小于 1ms，RAM 未充分利用，网络 <1%。

挑战

故障转移后症状似乎有所好转，但几天后又回来了，无论哪个服务器是主要服务器 - 两台服务器上的症状都是相同的。

1. 零星的客户端超时和连接故障，例如

   ...建立连接时发生错误...

   或者

   执行超时过期

   有时这些会持续长达 40 秒，然后消退。

2. 事务日志备份作业的完成时间比以前长 10 倍。以前备份所有500个数据库的日志需要2-3分钟，现在需要15-25分钟。我们已经验证了 Backup 本身运行良好且吞吐量良好。但是，在完成一个日志的备份之后和开始下一个日志之前会有一个小的延迟。它开始时非常低，但在一两天内达到 2-3 秒。乘以 500 个数据库，还是有区别的。

3. 有时，一些看似随机的数据库在手动故障转移后会卡在“未同步”状态。解决此问题的唯一方法是重新启动辅助副本上的 SQL Server 服务，或者删除这些数据库并将其重新加入 AG。

4. CU10 引入的另一个问题（在 CU11 中未解决）：在 master.sys.databases 上阻塞时连接到辅助超时，甚至无法将 SSMS 对象资源管理器用于辅助副本。根本原因似乎被 Microsoft SQL Server VSS 编写器阻止，发出以下查询：

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

观察

我相信我在错误日志中找到了确凿的证据。错误日志充满了 AG 消息，被标记为“仅供参考”，但看起来它们根本不正常，并且它们的频率与应用程序错误有很强的相关性。

错误有多种类型，并按顺序出现：

• DbMgrPartnerCommitPolicy::SetSyncState: GUID

• DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: GUID

• AlwaysOn 可用性组与辅助数据库的连接在可用性副本“DB”上的主数据库“XYZ”终止，副本 ID：{GUID}。这只是一条信息性消息。无需用户操作。

• AlwaysOn 可用性组与辅助数据库的连接为可用性副本“DB”上的主数据库“ABC”建立，副本 ID：{GUID}。这只是一条信息性消息。无需用户操作。

有些日子有几十万个。

这篇文章讨论了 SQL 2016 上相同类型的错误序列，并在那里说它是异常的。这也解释了故障转移后的“非同步”现象。讨论的问题是 2016 年的，今年早些时候在 CU 中修复。但是，对于前两种类型的消息，它是唯一相关的参考，除了对自动初始播种消息的参考，因为 AG 已经建立，所以这里不应该是这种情况。

这是上周每日错误的摘要，对于在 PRIMARY 上每种类型有 > 10K 错误的日子（辅助显示“与主服务器失去连接......”）：

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

我们也偶尔会看到“奇怪”的消息，例如：

可用性组数据库“DB”正在将角色从“SECONDARY”更改为“SECONDARY”，因为镜像会话或可用性组由于角色同步而发生故障转移。这只是一条信息性消息。无需用户操作。

...在从“二级”到“正在解决”的许多变化状态中。

手动故障转移后，系统可能会连续几天没有收到这些类型的消息，突然间，无缘无故地，我们会一次收到数千个，这反过来又会导致服务器变得无响应，并导致应用程序连接超时。这是一个严重的错误，因为他们的一些应用程序不包含重试机制，因此可能会丢失数据。当发生这样的错误突发时，以下等待类型会飙升。这显示了 AG 似乎立即失去与所有数据库的连接后的等待：

大约 30 秒后，就等待而言，一切都恢复正常，但 AG 消息在一天中的不同时间以不同的速度不断涌入错误日志，包括非高峰时段在内的看似随机的时间。在这些错误爆发期间同时增加工作量当然会使事情变得更糟。如果只有几个数据库断开连接，它通常不会导致连接超时，因为它自己解决得足够快。

我们尝试验证确实是 CU9 引发了问题，但我们只能将两个节点降级到 CU9。尝试将任一节点降级到 CU8，导致该节点陷入“正在解决”状态，在日志中显示相同的错误：

无法读取具有相应资源 ID '... 的 Always On 可用性组的持久配置。持久化配置由承载主要可用性副本的更高版本 SQL Server 编写。升级本地 SQL Server 实例以允许本地可用性副本成为辅助副本。

这意味着我们必须引入停机时间才能同时将两个节点降级到 CU8。这也表明 AG 有一些重大更新，可以解释我们正在经历的事情。

我们已经尝试将 max_worker_threads 从它的默认值 0（根据本文在我们的盒子上 = 960 ）逐渐调整到 2,000，而没有观察到对错误的影响。

我们可以做些什么来解决这些 AG 断开连接？有没有人遇到类似的问题？AG 中拥有大量数据库的其他人能否在以 CU9 或 CU8 开头的 SQL 错误日志中看到类似的消息？

提前感谢您的帮助！

我添加了一个不使用窗口函数的解决方案和一个低于 Martin's Answer 的大数据集的基准

这是使用不在 SELECT 列表中的列进行 GROUP BY 的后续线程- 这什么时候实用、优雅或强大？

在我应对这一挑战的解决方案中，我使用了一个查询，该查询按不属于选择列表的表达式进行分组。当逻辑分组元素涉及来自其他行的数据时，这经常与窗口函数一起使用。

举个例子，也许这是一个矫枉过正的例子，但我认为你可能会发现这个挑战本身很有趣。我会等待发布我的解决方案，也许你们中的一些人可以提出更好的解决方案。

挑战

我们有一个定期记录读数的传感器表。不能保证采样时间处于单调间隔。

您需要编写一个查询来报告“异常”，即传感器报告超出阈值读数的次数，无论是低值还是高值。传感器报告高于或低于阈值的每个时间段都被视为“异常”。一旦读数恢复正常，异常结束。

示例表和数据

该脚本在 T-SQL 中，是我培训材料的一部分。

这是 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

预期结果

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
*/