Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

使用 MySQL 作为数据库时根据名称查询为大小写不敏感 #4372

Closed
guqing opened this issue Aug 3, 2023 · 3 comments · Fixed by #6897
Closed

使用 MySQL 作为数据库时根据名称查询为大小写不敏感 #4372

guqing opened this issue Aug 3, 2023 · 3 comments · Fixed by #6897
Labels
area/core Issues or PRs related to the Halo Core kind/bug Categorizes issue or PR as related to a bug.
Milestone

Comments

@guqing
Copy link
Member

guqing commented Aug 3, 2023

What is version of Halo has the issue?

2.8.0

What database are you using?

MySQL 8.x

What is your deployment method?

Docker

Your site address.

No response

What happened?

当我在 Halo 中使用 MySQL 作为数据库时发现登陆时我的用户名为 guqing 但我使用 GUQING 能登陆只是权限不正确。

经过测试发现

select * from extensions where name = '/registry/users/Ghost'

能查询出 name='/registry/users/Ghost' 的数据,排除了数据库驱动的影响,因为 halo 使用的是 r2dbc 的驱动而我查询使用的是官方驱动。

MySQL默认情况下是大小写不敏感的,这意味着在执行查询时,MySQL会忽略查询条件中的大小写差异。这种行为是由 MySQL 的默认字符集和校对规则所决定的。

在MySQL中,字符集和校对规则是用来确定如何比较和排序字符串的规则。如果使用的字符集和校对规则是不区分大小写的,那么在执行查询时,MySQL会将查询条件中的所有字符串都转换为相同的大小写形式,然后再进行比较。

而我希望查询中区分大小写,可以使用 BINARY 运算符来强制 MySQL 对字符串进行区分大小写的比较。例如,您可以使用以下查询来查询name字段值为 /registry/users/Ghost 的数据,而不会匹配到 name 字段值为 /registry/users/ghost 的数据:

SELECT * FROM table_name WHERE BINARY name = '/registry/users/Ghost';

这将强制 MySQL 对 name 字段进行区分大小写的比较,只返回 name 字段值为 /registry/users/Ghost 的数据。

Relevant log output

/kind bug
/area core
/milestone 2.8.x

Additional information

No response

@f2c-ci-robot f2c-ci-robot bot added the kind/bug Categorizes issue or PR as related to a bug. label Aug 3, 2023
@f2c-ci-robot f2c-ci-robot bot added this to the 2.8.x milestone Aug 3, 2023
@f2c-ci-robot f2c-ci-robot bot added the area/core Issues or PRs related to the Halo Core label Aug 3, 2023
@guqing guqing modified the milestones: 2.8.x, 2.9.x Aug 3, 2023
@ruibaby ruibaby modified the milestones: 2.9.x, Backlog Aug 25, 2023
@dlx79
Copy link

dlx79 commented Sep 5, 2023

我的 MySQL 数据库 3306 端口已经被占用了 本项目修改端口还不能部署成功,有什么解决办法么

@guqing
Copy link
Member Author

guqing commented Sep 5, 2023

我的 MySQL 数据库 3306 端口已经被占用了 本项目修改端口还不能部署成功,有什么解决办法么

尝试使用下面的 docker-compose.yaml ,此配置基于官方文档提供的配置并去掉了 halodb 中的 ports: - "3306:3306" 配置,参考文档 https://docs.halo.run/getting-started/install/docker-compose

version: "3"

services:
  halo:
    image: halohub/halo:2.9
    container_name: halo
    restart: on-failure:3
    depends_on:
      halodb:
        condition: service_healthy
    networks:
      halo_network:
    volumes:
      - ./:/root/.halo2
    ports:
      - "8090:8090"
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:8090/actuator/health/readiness"]
      interval: 30s
      timeout: 5s
      retries: 5
      start_period: 30s
    command:
      - --spring.r2dbc.url=r2dbc:pool:mysql://halodb:3306/halo
      - --spring.r2dbc.username=root
      # MySQL 的密码,请保证与下方 MYSQL_ROOT_PASSWORD 的变量值一致。
      - --spring.r2dbc.password=o#DwN&JSa56
      - --spring.sql.init.platform=mysql
      # 外部访问地址,请根据实际需要修改
      - --halo.external-url=http://localhost:8090/

  halodb:
    image: mysql:8.0.31
    container_name: halodb
    restart: on-failure:3
    networks:
      halo_network:
    command:
      - --default-authentication-plugin=mysql_native_password
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_general_ci
      - --explicit_defaults_for_timestamp=true
    volumes:
      - ./mysql:/var/lib/mysql
      - ./mysqlBackup:/data/mysqlBackup
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 3s
      retries: 5
      start_period: 30s
    environment:
      # 请修改此密码,并对应修改上方 Halo 服务的 SPRING_R2DBC_PASSWORD 变量值
      - MYSQL_ROOT_PASSWORD=o#DwN&JSa56
      - MYSQL_DATABASE=halo

networks:
  halo_network:

@jiewenhuang
Copy link

jiewenhuang commented Nov 28, 2023

当h2数据库的数据中含有区分大小写的数据例如:

  • /registry/settings/theme-Joe-setting
  • /registry/settings/theme-joe-setting

则在利用后台备份后,在MySQL的halo中会恢复也会失败。

f2c-ci-robot bot pushed a commit that referenced this issue Oct 18, 2024
…6897)

#### What type of PR is this?
/kind improvement
/area core
/milestone 2.20.x

#### What this PR does / why we need it:
将 MySQL 的表创建脚本 name 列字符集改为 utf8mb4_bin 以使其对大小写敏感

Fixes #4372

**how to test it?**
使用 docker 运行 MySQL
```shell
# mariadb 同样将镜像改为 mariadb 后执行相同步骤
docker run --name mysql-test -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=halo -p 3306:3306 --rm -d mysql:latest
```
然后执行表创建脚本并手动执行以下两条 SQL 能成功插入
```sql
insert into extensions(name,data,version) values('a', 'a', 0)
insert into extensions(name,data,version) values('A', 'A', 0)
```

#### Does this PR introduce a user-facing change?
```release-note
修改 MySQL 表创建脚本 name 列的字符集使其大小写敏感以解决可能会遇到切换数据库时因为数据冲突而无法导入备份的问题(这只对此版本及之后的新用户有效)
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/core Issues or PRs related to the Halo Core kind/bug Categorizes issue or PR as related to a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants