Failed to CREATE/RENAME when create blocked by ownership and renaming #30
Description
If you create a DatabaseUser object with an existing username, that'll fail and sit (like it should). However, if you update DatabaseUser's username to something that doesn't conflict, you get an error because it tries to CREATE and looks up the wrong stuff:
renaming username from manage-ui (conflict) to manage-ui-secret:
2022-07-25T23:26:28.535Z INFO controller.databaseuser Starting workers {"reconciler group": "mysql.apps.cuppett.dev", "reconciler kind": "DatabaseUser", "worker count": 1}
2022-07-25T23:26:29.015Z INFO controllers.DatabaseUser Taking ownership of the authentication secret. {"Name": "manage-secret-pw", "Namespace": "mariadb-test"}
2022/07/25 23:28:20 /workspace/orm/model.go:84 record not found
[19.084ms] [rows:0] SELECT * FROM mysql.user WHERE user = 'manage-ui-secret' ORDER BY user.Host LIMIT 1
2022/07/25 23:28:20 /workspace/orm/model.go:84 record not found
[0.006ms] [rows:0]
2022-07-25T23:28:20.156Z INFO controllers.DatabaseUser User does not exist or failed retrieving {"Host": "hq.cuppett.com", "Name": "manage-ui"}
2022/07/25 23:28:20 /workspace/controllers/databaseuser_controller.go:532 Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY '8tbCr0C3pO2tJnO@n#IY1eQp'' at line 1
[4.727ms] [rows:0] CREATE USER 'manage-ui' IDENTIFIED WITH 'mysql_native_password' BY '8tbCr0C3pO2tJnO@n#IY1eQp'
2022-07-25T23:28:20.161Z ERROR controllers.DatabaseUser Failed to execute query. {"Host": "hq.cuppett.com", "Name": "manage-ui", "Query": "CREATE USER 'manage-ui' IDENTIFIED WITH 'mysql_native_password' BY '8tbCr0C3pO2tJnO@n#IY1eQp'", "error": "Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY '8tbCr0C3pO2tJnO@n#IY1eQp'' at line 1"}
github.com/cuppett/mysql-dba-operator/controllers.(*DatabaseUserReconciler).userCreate
/workspace/controllers/databaseuser_controller.go:270
github.com/cuppett/mysql-dba-operator/controllers.(*DatabaseUserReconciler).Reconcile
/workspace/controllers/databaseuser_controller.go:185
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Reconcile
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:114
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:311
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:266
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func2.2
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:227
2022/07/25 23:28:20 /workspace/orm/model.go:84 record not found
[19.173ms] [rows:0] SELECT * FROM mysql.user WHERE user = 'manage-ui-secret' ORDER BY user.Host LIMIT 1
2022/07/25 23:28:20 /workspace/orm/model.go:84 record not found
[0.005ms] [rows:0]
2022-07-25T23:28:20.191Z INFO controllers.DatabaseUser User does not exist or failed retrieving {"Host": "hq.cuppett.com", "Name": "manage-ui"}
2022/07/25 23:28:20 /workspace/controllers/databaseuser_controller.go:532 Error 1396: Operation CREATE USER failed for 'manage-ui'@'%'
[5.723ms] [rows:0] CREATE USER 'manage-ui'
2022-07-25T23:28:20.197Z ERROR controllers.DatabaseUser Failed to execute query. {"Host": "hq.cuppett.com", "Name": "manage-ui", "Query": "CREATE USER 'manage-ui'", "error": "Error 1396: Operation CREATE USER failed for 'manage-ui'@'%'"}
github.com/cuppett/mysql-dba-operator/controllers.(*DatabaseUserReconciler).userCreate
/workspace/controllers/databaseuser_controller.go:270
github.com/cuppett/mysql-dba-operator/controllers.(*DatabaseUserReconciler).Reconcile
/workspace/controllers/databaseuser_controller.go:185
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Reconcile
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:114
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).reconcileHandler
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:311
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).processNextWorkItem
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:266
sigs.k8s.io/controller-runtime/pkg/internal/controller.(*Controller).Start.func2.2
/go/pkg/mod/sigs.k8s.io/[email protected]/pkg/internal/controller/controller.go:227