No implicit user creation with GRANT syntax in MySQL 8.0 Ubuntu Focal

Bug #1885825 reported by Ghanshyam Mann on 2020-07-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Compute (nova)
Undecided
Ghanshyam Mann
OpenStack Identity (keystone)
Undecided
Vishakha Agarwal
coverage2sql
Undecided
Ghanshyam Mann
tacker
High
Ghanshyam Mann
zaqar
Undecided
Ghanshyam Mann

Bug Description

Ubuntu Focal (20.04) has mysql 8.0 and with mysql 8.0 there is no implicit user creation with GRANT syntax.

In Ubuntu Bionic (18.04) mysql 5.7 version used to create the user implicitly when using using the GRANT. But starting with mysql 8.0, we need to create the user explicitly before using the GRANT command.

Nova unit and functional tests job using tools/test-setup.sh script start failing when running on Ubuntu Focal

https://zuul.opendev.org/t/openstack/build/8b0f4fcc21854655a638c413b6fe1a91

+ sudo -H mysql -u root -pinsecure_slave -h localhost -e '
    DELETE FROM mysql.user WHERE User='\'''\'';
    FLUSH PRIVILEGES;
    GRANT ALL PRIVILEGES ON *.*
        TO '\''openstack_citest'\''@'\''%'\'' identified by '\''openstack_citest'\'' WITH GRANT OPTION;'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'openstack_citest' WITH GRANT OPTION' at line 2

we need to modify the tools/test-setup.sh to create user first.

Below used to work with mysql 5.7

GRANT ALL PRIVILEGES ON *.* TO '$DB_USER'@'%' identified by '$DB_PW' WITH GRANT OPTION;"

With mysql 8.0 we need to create user first

CREATE USER '$DB_USER'@'%' IDENTIFIED BY '$DB_PW';
GRANT ALL PRIVILEGES ON *.* TO '$DB_USER'@'%' WITH GRANT OPTION;"

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to nova (master)

Fix proposed to branch: master
Review: https://review.opendev.org/738723

Changed in nova:
assignee: nobody → Ghanshyam Mann (ghanshyammann)
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to nova (master)

Reviewed: https://review.opendev.org/738723
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=e0d541073da06a583681774b2ecb61bf5a9bd2d1
Submitter: Zuul
Branch: master

commit e0d541073da06a583681774b2ecb61bf5a9bd2d1
Author: Ghanshyam Mann <email address hidden>
Date: Tue Jun 30 21:21:39 2020 -0500

    Fix user creation with GRANT in MySQL 8.0(Ubuntu Focal)

    In Ubuntu Bionic (18.04) mysql 5.7 version used to create
    the user implicitly when using using the GRANT.

    Ubuntu Focal (20.04) has mysql 8.0 and with mysql 8.0 there
    is no implicit user creation with GRANT. We need to
    create the user first before using GRANT command.

    Nova unit and functional tests job using tools/test-setup.sh
    script start failing when running on Ubuntu Focal

    https://zuul.opendev.org/t/openstack/build/8b0f4fcc21854655a638c413b6fe1a91

    Error log:
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual
    that corresponds to your MySQL server version for the right syntax to use near
    'identified by 'openstack_citest' WITH GRANT OPTION' at line 2

    Story: #2007865
    Task: #40200

    Change-Id: I97b0dcbb88c6ef7c22e3c55970211bed792bbd0d
    Closes-Bug: #1885825

Changed in nova:
status: In Progress → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to coverage2sql (master)

Reviewed: https://review.opendev.org/740363
Committed: https://git.openstack.org/cgit/openstack/coverage2sql/commit/?id=726b149452b60f936a3f7ddb2ba80c3351635886
Submitter: Zuul
Branch: master

commit 726b149452b60f936a3f7ddb2ba80c3351635886
Author: Ghanshyam Mann <email address hidden>
Date: Thu Jul 9 15:25:58 2020 -0500

    [goal] Migrate testing to ubuntu focal

    As per victoria cycle testing runtime and community goal[1]
    we need to migrate upstream CI/CD to Ubuntu Focal(20.04).

    Fixing:
    - bug#1885825
    Fix user creation with GRANT in MySQL 8.0(Ubuntu Focal)
    Ubuntu Focal (20.04) has mysql 8.0 and with mysql 8.0 there
    is no implicit user creation with GRANT. We need to
    create the user first before using GRANT command.

    Story: #2007865
    Task: #40212

    Closes-Bug: #1885825

    [1] https://governance.openstack.org/tc/goals/selected/victoria/migrate-ci-cd-jobs-to-ubuntu-focal.h>

    Change-Id: I7feffa300fea7d86749143aae5fb547beb54406d

Changed in keystone:
assignee: nobody → Vishakha Agarwal (vishakha.agarwal)
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to keystone (master)

Fix proposed to branch: master
Review: https://review.opendev.org/746451

Changed in keystone:
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to keystone (master)

Reviewed: https://review.opendev.org/746451
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=b54839f382b046b7ec8b9633fbea763d31d9949c
Submitter: Zuul
Branch: master

commit b54839f382b046b7ec8b9633fbea763d31d9949c
Author: Vishakha Agarwal <email address hidden>
Date: Mon Aug 17 13:37:48 2020 +0530

    Fix user creation with GRANT in MySQL 8.0(Ubuntu Focal)

    In Ubuntu Bionic (18.04) mysql 5.7 version used to create
    the user implicitly when using using the GRANT.

    Ubuntu Focal (20.04) has mysql 8.0 and with mysql 8.0 there
    is no implicit user creation with GRANT. We need to
    create the user first before using GRANT command.

    This patch updates tools/test-setup.sh so that keystone supports
    ubuntu focal.

    Story: #2007865
    Task: #40190

    Change-Id: I86d10729cfc7c02f12df611b56f6e263969dfe4b
    Closes-Bug: #1885825

Changed in keystone:
status: In Progress → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to tacker (master)

Reviewed: https://review.opendev.org/744608
Committed: https://git.openstack.org/cgit/openstack/tacker/commit/?id=7b9a074bdaff7ffe5ab28cb2944f0aa0f4972924
Submitter: Zuul
Branch: master

commit 7b9a074bdaff7ffe5ab28cb2944f0aa0f4972924
Author: Ghanshyam Mann <email address hidden>
Date: Mon Aug 3 21:32:41 2020 -0500

    [goal] Migrate testing to ubuntu focal

    As per victoria cycle testing runtime and community goal[1]
    we need to migrate upstream CI/CD to Ubuntu Focal(20.04).

    Fixing:
    - bug#1885825
    Fix user creation with GRANT in MySQL 8.0(Ubuntu Focal)
    Ubuntu Focal (20.04) has mysql 8.0 and with mysql 8.0 there
    is no implicit user creation with GRANT. We need to
    create the user first before using GRANT command.

    - bug#1886298
    Bump the lower constraints for required deps which added python3.8 support
    in their later version.

    - Move functional jobs to Focal nodeset

    Closes-Bug: #1886298

    Story: #2007865
    Task: #40222

    [1] https://governance.openstack.org/tc/goals/selected/victoria/migrate-ci-cd-jobs-to-ubuntu-focal>

    Change-Id: I636c3a3b6cd429b1f8df4eaff0cb845ae0f57362

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to zaqar (master)

Reviewed: https://review.opendev.org/744637
Committed: https://git.openstack.org/cgit/openstack/zaqar/commit/?id=e35f9ad8d3f2f070aae9e0b23f0736e974734762
Submitter: Zuul
Branch: master

commit e35f9ad8d3f2f070aae9e0b23f0736e974734762
Author: Ghanshyam Mann <email address hidden>
Date: Mon Aug 3 22:20:27 2020 -0500

    [goal] Migrate testing to ubuntu focal

    As per victoria cycle testing runtime and community goal[1]
    we need to migrate upstream CI/CD to Ubuntu Focal(20.04).

    Fixing:
    - bug#1885825
    Fix user creation with GRANT in MySQL 8.0(Ubuntu Focal)
    Ubuntu Focal (20.04) has mysql 8.0 and with mysql 8.0 there
    is no implicit user creation with GRANT. We need to
    create the user first before using GRANT command.

    - bug#1886298
    Bump the lower constraints for required deps which added python3.8 support
    in their later version.

    Co-Author: wanghao <email address hidden>

    Story: #2007865
    Task: #40229

    Closes-Bug: #1885825

    [1] https://governance.openstack.org/tc/goals/selected/victoria/migrate-ci-cd-jobs-to-ubuntu-focal>

    Change-Id: Ic15dfe480e4d34c3ba102b65153fab1af787bd67

Yasufumi Ogawa (yasufum) on 2020-09-23
Changed in tacker:
importance: Undecided → High
Changed in coverage2sql:
status: New → Fix Released
Changed in zaqar:
status: New → Fix Released
Changed in tacker:
status: New → Fix Released
Changed in coverage2sql:
assignee: nobody → Ghanshyam Mann (ghanshyammann)
Changed in tacker:
assignee: nobody → Ghanshyam Mann (ghanshyammann)
Changed in zaqar:
assignee: nobody → Ghanshyam Mann (ghanshyammann)
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers