AlexStocks commented on code in PR #1087:
URL: https://github.com/apache/dubbo-go-pixiu/pull/1087#discussion_r4534970991


##########
admin/dao/impl/guest_mysql_test.go:
##########
@@ -0,0 +1,174 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package impl
+
+import (
+       "database/sql"
+       "fmt"
+       "os"
+       "strings"
+       "sync"
+       "testing"
+       "time"
+)
+
+import (
+       "github.com/go-sql-driver/mysql"
+)
+
+func TestRegisterConcurrentUsernameIsUnique(t *testing.T) {
+       db := newGuestMySQLTestDB(t)
+       createGuestTestTables(t, db)
+
+       dao := &GuestDao{db: db}
+       if err := dao.EnsureUsernameUniqueIndex(); err != nil {
+               t.Fatalf("EnsureUsernameUniqueIndex() error = %v", err)
+       }
+
+       const attempts = 8
+       start := make(chan struct{})
+       results := make(chan error, attempts)
+       var workers sync.WaitGroup
+       workers.Add(attempts)
+       for i := 0; i < attempts; i++ {
+               go func() {
+                       defer workers.Done()
+                       <-start
+                       results <- dao.Register("same-user", "hashed-password")
+               }()
+       }
+       close(start)
+       workers.Wait()
+       close(results)
+
+       successes, duplicates := 0, 0
+       for err := range results {
+               switch {
+               case err == nil:
+                       successes++
+               case err.Error() == "user already exists":
+                       duplicates++
+               default:
+                       t.Errorf("Register() error = %v", err)
+               }
+       }
+       if successes != 1 || duplicates != attempts-1 {
+               t.Fatalf("registration results: successes = %d, duplicates = 
%d; want 1 and %d", successes, duplicates, attempts-1)
+       }
+
+       var userCount, roleCount int
+       if err := db.QueryRow("SELECT COUNT(*) FROM pixiu_user WHERE username = 
?", "same-user").Scan(&userCount); err != nil {
+               t.Fatal(err)
+       }
+       if err := db.QueryRow("SELECT COUNT(*) FROM 
pixiu_user_role").Scan(&roleCount); err != nil {
+               t.Fatal(err)
+       }
+       if userCount != 1 || roleCount != 1 {
+               t.Fatalf("persisted rows: users = %d, roles = %d; want 1 each", 
userCount, roleCount)
+       }
+}
+
+func TestEnsureUsernameUniqueIndexLeavesExistingDuplicatesUntouched(t 
*testing.T) {
+       db := newGuestMySQLTestDB(t)
+       createGuestTestTables(t, db)
+       if _, err := db.Exec("INSERT INTO pixiu_user (username, password) 
VALUES (?, ?), (?, ?)", "duplicate", "one", "duplicate", "two"); err != nil {
+               t.Fatal(err)
+       }
+
+       err := (&GuestDao{db: db}).EnsureUsernameUniqueIndex()
+       if err == nil || !strings.Contains(err.Error(), `duplicate username 
"duplicate" exists`) {
+               t.Fatalf("EnsureUsernameUniqueIndex() error = %v, want 
duplicate username error", err)
+       }
+
+       var count int
+       if err := db.QueryRow("SELECT COUNT(*) FROM pixiu_user WHERE username = 
?", "duplicate").Scan(&count); err != nil {
+               t.Fatal(err)
+       }
+       if count != 2 {
+               t.Fatalf("duplicate rows after failed migration = %d, want 2 
untouched rows", count)
+       }
+}
+
+func newGuestMySQLTestDB(t *testing.T) *sql.DB {
+       t.Helper()
+       dsn := os.Getenv("PIXIU_TEST_MYSQL_DSN")
+       if dsn == "" {
+               t.Skip("set PIXIU_TEST_MYSQL_DSN to a MySQL account allowed to 
create and drop databases")

Review Comment:
   [P2] 新增的 MySQL 关键路径在本地与 CI 中恒被跳过,`pass` 不构成执行证据
   
   **触发条件**:任何改动 `EnsureUsernameUniqueIndex` / `Register` / 
`EnsureDefaultAdmin` 的后续提交。
   
   **实际影响**:`admin/dao/impl` 包在 CI 与本地都会报 `ok`,但这些用例全部 `SKIP` —— 判据是 
`PIXIU_TEST_MYSQL_DSN` 为空即 `t.Skip`。我核对了仓库现有 5 个 workflow:没有任何一个声明 MySQL 
service,也没有任何位置设置该变量,因此下列逻辑至今没有任何一次真实执行:
   
   - `hasUniqueUsernameIndex` 的 `information_schema.statistics` + `GROUP BY ... 
HAVING COUNT(*) = 1` 判据
   - `ALTER TABLE pixiu_user ADD UNIQUE INDEX uniq_pixiu_user_username` 
在已有表上的迁移行为(含并发实例先加成功的分支)
   - `Register` 的 1062 → `user already exists` 映射(本 PR 声称覆盖并发注册)
   - `EnsureDefaultAdmin` 的 `INSERT ... SELECT ? FROM DUAL WHERE NOT EXISTS 
(...)`
   
   这些 SQL 直接决定升级后实例能否启动(`admin/core/server.go` 对两个函数都是 
`global.LOG.Fatal`),读码通过不等于可执行。
   
   **证据**(本地 head `9fd6cd08`,go1.25.0 darwin/amd64):
   ```
   $ go test -count=1 -v -run 
'TestRegisterConcurrentUsernameIsUnique|TestEnsureUsernameUniqueIndex|TestEnsureDefaultAdmin'
 ./admin/dao/impl/
   --- SKIP: TestRegisterConcurrentUsernameIsUnique (0.00s)
   --- SKIP: TestEnsureUsernameUniqueIndexLeavesExistingDuplicatesUntouched 
(0.00s)
   PASS
   ok  github.com/apache/dubbo-go-pixiu/admin/dao/impl 0.030s
   ```
   `go build ./admin/...`、`go test ./admin/... ./pkg/config/schema/` 均 exit 0,非 
SQL 路径(`admin/controller/account`、`admin/logic/account`、`admin/initialize`)覆盖正常。
   
   **修复方向**:在 CI 增加 MySQL(service container 或复用 `integration-test` job 起一个 
`mysql:8` 并 `health-cmd=mysqladmin ping`),设置 `PIXIU_TEST_MYSQL_DSN`,让这三个用例在 PR 
与 `develop` 上真实执行;若担心 secret 暴露,使用一次性容器内账号即可。



##########
admin/dao/impl/guest.go:
##########
@@ -57,33 +60,105 @@ func (d *GuestDao) Login(username, password string) (bool, 
int) {
        return true, id
 }
 
-func (d *GuestDao) Register(username, password string) error {
+func (d *GuestDao) EnsureUsernameUniqueIndex() error {
+       indexed, err := hasUniqueUsernameIndex(d.db)
+       if err != nil {
+               return err
+       }
+       if indexed {
+               return nil
+       }
 
-       if username == "" {
-               return errors.New("void username")
+       if username, err := findDuplicateUsername(d.db); err == nil {
+               return fmt.Errorf("cannot add unique username index: duplicate 
username %q exists; resolve duplicate accounts and restart", username)
+       } else if !errors.Is(err, SQL.ErrNoRows) {
+               return err
        }
 
-       db := d.db
-       var id int
-       err := db.QueryRow("SELECT id FROM pixiu_user WHERE username = ?", 
username).Scan(&id)
+       _, err = d.db.Exec("ALTER TABLE pixiu_user ADD UNIQUE INDEX 
uniq_pixiu_user_username (username)")
        if err == nil {
-               return errors.New("User already exists, please login")
+               return nil
        }
-       //now := time.Now().Format("2006-01-02 15:04:05")
-       stmt, err := db.Prepare("INSERT INTO pixiu_user (username,password) 
VALUES (?,?)")
+
+       // Another Pixiu instance may have added the index while this instance 
was starting.
+       if indexed, checkErr := hasUniqueUsernameIndex(d.db); checkErr == nil 
&& indexed {
+               return nil
+       }
+       if username, duplicateErr := findDuplicateUsername(d.db); duplicateErr 
== nil {
+               return fmt.Errorf("cannot add unique username index: duplicate 
username %q exists; resolve duplicate accounts and restart", username)
+       }
+       return fmt.Errorf("failed to add unique username index: %w", err)
+}
+
+func hasUniqueUsernameIndex(db *SQL.DB) (bool, error) {
+       var indexed bool
+       err := db.QueryRow(`
+               SELECT EXISTS (
+                       SELECT index_name
+                       FROM information_schema.statistics
+                       WHERE table_schema = DATABASE()
+                         AND table_name = 'pixiu_user'
+                         AND non_unique = 0
+                       GROUP BY index_name
+                       HAVING COUNT(*) = 1 AND MAX(column_name = 'username') = 
1
+               )
+       `).Scan(&indexed)
+       return indexed, err
+}
+
+func findDuplicateUsername(db *SQL.DB) (string, error) {
+       var username string
+       err := db.QueryRow(`
+               SELECT username
+               FROM pixiu_user
+               GROUP BY username
+               HAVING COUNT(*) > 1
+               LIMIT 1
+       `).Scan(&username)
+       return username, err
+}
+
+func (d *GuestDao) EnsureDefaultAdmin(username, password string) error {
+       _, err := d.db.Exec(`
+               INSERT INTO pixiu_user (username, password, role)
+               SELECT ?, ?, 1 FROM DUAL
+               WHERE NOT EXISTS (SELECT 1 FROM pixiu_user WHERE username = ?)
+       `, username, password, username)
+       return err
+}

Review Comment:
   补充证据,帮助定位修复范围(不改变上面结论):
   
   1. **管理员门禁不受影响**:`admin/dao/impl/user.go` 的 `CheckUserIsAdmin` 查询的是 
`pixiu_user.role = 1`,与本 PR 在 `EnsureDefaultAdmin` 里写入的 `role` 列一致。所以默认管理员能通过 
`/user/checkIsAdmin` 与 `POST /register` 的管理员校验,受影响的是角色展示接口。
   2. **只补 `pixiu_user_role` 不足以修好 `/user/getUserRole`**:`GetUserRole` 的第二条查询是 
`SELECT role_name, description FROM pixiu_role WHERE id = (SELECT role_id FROM 
pixiu_user_role WHERE user_id = 1)` —— `user_id = 1` 是硬编码。即使给默认管理员补了关联行,通过 
`/register` 新建的第二个管理员仍会读到 `id=1` 的角色(或在该行不存在时报错)。该硬编码在 Base 中已存在,不作为本 PR 的 
Finding,但如果本 PR 要把"首次启动可用"补齐,建议一并改为按 `username`/当前用户 id 
关联,否则第一次登录看到的角色信息仍可能来自其他账号。



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to