问题[mariadb](dba)

我有一个大型复杂的神经科学数据集，我正试图将其组织到一个数据库中，以便与我的实验室进行并发和一致的访问。这是我第一次尝试使用 SQLite 进行小得多的实验，而且我已经咬得太多了。

包含神经记录的主表有大约 100 亿行。在用数据填充此表之前，我建立了一个复合主键(id, experiment_id)，其中id每一行都是唯一的。我这样做是为了可以按experiment_id. 这在当时看来是个好主意。然而，一旦表被填充，在 phpMyAdmin 中添加额外的索引失败（错误弹出窗口指示新密钥已损坏）。

面对这个障碍，我找到了https://mariadb.com/kb/en/partition-maintenance/，它的第一个要点是“#1：在你知道它如何以及为什么会有帮助之前不要使用分区。 ”，然后是“表大小很少是性能问题”。因此，希望“重做”，我创建了一个具有相同列（减去该id列）但没有分区方案和索引的新表并执行了：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
INSERT INTO tfconv_2 (experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag)
SELECT experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag
FROM tfconv;
COMMIT;

这运行了好几天（这并不奇怪，因为首先需要好几天才能将数据放入原始表中）。在 MariaDB 努力工作的同时，我可以在 phpMyAdmin 摘要页面中看到新表的行数在上升。监视我服务器上的资源，该进程似乎正在以一种常规的、系统的方式将数据复制到新表中。

然后，有一次我去检查它时，新表上的行数减少了。SHOW PROCESSLIST透露：

   Id   User         Host           db Command    Time                   State
10237   root    localhost   singlecell  Killed  217191  Reset for next command

有了这个背景，我有三个问题：

1. 为什么这个查询会失败？（错误日志文件在相关时间窗口内保持沉默。）
2. 如果我再次尝试构建无分区表，您是否建议我做一些不同的事情？
3. 有没有办法在原始分区表上设置索引，从而避免损坏？

我想将 MariaDB 添加到我的 docker compose 文件中，如下所示：

version: '3'
services:
    mariadb:
        image: 'mariadb:10.3'
        ports:
            - "3306:3306"
        volumes:
            - "${PWD}/data/mariadb:/var/lib/mysql:rw"
        environment:
            MYSQL_ROOT_PASSWORD: 'rootpass'
            MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
            MYSQL_USER: 'dev'

现在我可以使用-uroot -p并输入rootpass密码进行连接，但是我无法使用-udev并且没有密码进行连接。它说：

ERROR 1045 (28000): Access denied for user 'dev'@'172.31.0.1' (using password: NO)

我也尝试添加MYSQL_PASSWORD: ~orMYSQL_PASSWORD: ''到环境变量，但这也不起作用。

当我添加一个实际密码时它可以工作MYSQL_PASSWORD: 'dev'，但没有密码我就无法工作。有谁知道环境变量的神奇组合？

我想要实现这一点的原因是因为我的团队目前以不同的方式设置了这个，每个人都有一个.my.cnf包含user=dev和password=. 所以如果每个人都能保持这个本地配置不变就好了。

select drc_users.name,
vehicles.total_vehicles,
COUNT(distinct drivers.id) as total_drivers
from drc_users
LEFT join brc_users on drc_users.id = brc_users.district_id
left join crc_users on crc_users.block_id = brc_users.id
left join schools on schools.cluster_id = crc_users.id
left join (select count(id) total_vehicles from vehicles) on vehicles.school_id = schools.id
left join drivers on drivers.school_id = schools.id
where drc_users.district_id = 2401

我正在使用 mariadb 10.5.12

需要有关此查询问题的帮助。

如果我有邮政编码，EH1 2GB我正在寻找一个只会提取 EH 的功能

理想情况下，这适用于可能仅包含 1 个前导字母的邮政编码以及G2 1AB前导字母后的 2 个数字，例如 EH54 1BD 和 G25 2RD

我试过使用

我曾尝试拉取整个邮政编码，然后使用 javascript ( const extractPostalArea = (postalCode) => postalCode.match(/^[A-Z]+/)?.[0] ?? null;) 进一步分隔邮政编码，但这导致的问题多于解决的问题。

有没有办法提取邮政编码，结果类似于下表...

任何帮助将非常感激

我正在使用 MariaDB

我只想将 Mariadb 数据库中所有 15 个表的数据保留 90 天。当前数据库大小为 60 GB。有哪些可用于执行相同操作的选项。

我遇到了另一个用户在此处描述的相同数据库问题，我设法通过设置启动了 mysqld innodb_force_recovery = 6，这使我可以使用mysqldump -u<username> -p -f --all-databases > all.sql. 但是，此备份导致了一组错误消息：

mysqldump: Error 1034: Index for table <table1> is corrupt; try to repair it when dumping table <table1> at row: 0
mysqldump: Error 1034: Index for table <table2> is corrupt; try to repair it when dumping table <table2> at row: 6
mysqldump: Error 1034: Index for table <table3> is corrupt; try to repair it when dumping table <table3> at row: 0
mysqldump: Error 1034: Index for table <table4> is corrupt; try to repair it when dumping table <table4> at row: 0
mysqldump: Error 1034: Index for table <table5> is corrupt; try to repair it when dumping table <table5> at row: 0
mysqldump: Error 1034: Index for table <table6> is corrupt; try to repair it when dumping table <table6> at row: 0
mysqldump: Error 1034: Index for table <table7> is corrupt; try to repair it when dumping table <table7> at row: 0

“尝试修复它”是否意味着mysqldump实际上确实尝试修复它（并成功），或者它是否意味着我应该尝试修复它作为下一个动作，如果是这样，我需要什么命令现在发行？我需要重新运行吗mysqldump？

由于只有很少的 MariaDB 经验，我需要检查大规模 MariaDB 安装上的字符编码设置。我想通过 Ansible 执行此操作，并且相信发出某种返回编码设置的 SQL 命令（对应于 .default-character-set中的设置my.cnf。理想情况下来自 bash。

对于postgres，即我做psql -tAc "SHOW server_encoding;"*并将结果注册到一个变量中。

所以，实际上我在寻找两件事：

1. 列出 MariaDB 的字符编码默认设置的 SQL 查询
2. 一种直接从命令行向 MariaDB 发送此类查询的方法

在一边......我猜可能不同的数据库有不同的编码，对吧？对于我当前的任务，检测安装的默认设置就足够了。

作品：

SET lc_time_names='en_US';

失败并显示“变量‘lc_time_names’无法设置为‘de_DE’的值”：

SET lc_time_names='de_DE';

这对我来说似乎很简单。我想，我必须安装该语言环境，但没有搜索“mariadb install locale”或“mariadb add locale”返回任何有用的信息。所以我认为可用的语言环境可能与操作系统有关。我使用的是 macOS，所以locale -a列出了很多语言环境，其中包括“de_DE”。

我使用该区域设置运行完全相同的数据库没有问题，但必须更新 macOS 并通过brew install [email protected]. 这更新的不仅仅是 php。之后，错误出现了。

创建（更改）外键时遇到问题。

我的数据库中有两个表（通过飞路迁移创建）：

连接器（迁移）

create table if not exists connector
(
    id char(36) not null,
    # other fields omitted
    primary key (id)
) DEFAULT CHARACTER SET 'utf8mb4'
  COLLATE 'utf8mb4_unicode_520_ci';

connector_preset（迁移）

CREATE TABLE IF NOT EXISTS connector_preset
(
    id CHAR(36) NOT NULL,
    # other fields omitted

    PRIMARY KEY (id)
);

我想在connector和connector_preset之间创建一个链接，所以我创建了另一个这样的迁移：

ALTER IGNORE TABLE `connector`
    # `connector_preset`.`id`
    ADD COLUMN IF NOT EXISTS `preset_id` CHAR(36),
    ADD CONSTRAINT `fk_connector_preset_id` FOREIGN KEY (`preset_id`) REFERENCES `connector_preset` (`id`);

但它失败并出现以下错误：

SQL State  : HY000
Error Code : 1005
Message    : (conn=4) Can't create table `test`.`connector` (errno: 150 "Foreign key constraint is incorrectly formed")
Location   : db/migration/...
Line       : 34
Statement  : ALTER IGNORE TABLE `connector`
    # `connector_preset`.`id`
    ADD COLUMN IF NOT EXISTS `preset_id` CHAR(36),
    ADD CONSTRAINT `fk_connector_preset_id` FOREIGN KEY (`preset_id`) REFERENCES `connector_preset` (`id`),

这些列似乎属于同一类型。此外，由于某种原因，它在本地 k8s 集群（10.3.29-MariaDB）中工作，但在集成测试（testcontainers，MariaDB 10.6.11）中失败。在使用 10.3.29 的 GH 操作中也失败，这很奇怪，因为它在本地工作。

如果我将 testcontainers 中的 mariadb 版本设置为 10.3.29 - 它仍然失败。

尝试更改connector_preset表以使用相同的字符集和排序规则：

ALTER IGNORE TABLE `connector_preset` DEFAULT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci';

还是不行

我有一个用户可以访问多个数据库。

在MariaDB 10.6.11中，我无法使用以下命令列出所有可访问的数据库：

SHOW DATABASES;
/* SQL Error (1227): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation */

另一方面，我可以使用这个：

SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA` ORDER BY `SCHEMA_NAME`;

在装有MariaDB 10.5.18的不同服务器上“SHOW DATABASES”工作正常，即使我认为两台服务器上的用户具有相同的权限：

MariaDB 10.6 用户（不允许显示数据库）：

SHOW GRANTS FOR 'dev-all'@'localhost';
GRANT USAGE ON *.* TO `dev-all`@`localhost` IDENTIFIED BY PASSWORD '...'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `dev-___`.* TO `dev-all`@`localhost` WITH GRANT OPTION

MariaDB 10.5 用户（允许显示数据库）：

SHOW GRANTS FOR 'dev-all'@'localhost';
GRANT USAGE ON *.* TO `dev-all`@`localhost` IDENTIFIED BY PASSWORD '...'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `dev-___`.* TO `dev-all`@`localhost` WITH GRANT OPTION

是不是MariaDB版本不同导致的？还是我错过了其他东西？

不允许用户列出他们所有的数据库有什么意义？我该如何解决 MariaDB 10.6 上的这个问题？