Bug 1282738

+++ This bug was initially created as a clone of Bug #1262861 +++

Description of problem:
Creating the simple app example produces inaccessible mysql service.

> $ oc new-app --docker-image=openshift/mysql-55-centos7 --code=https://github.com/openshift/ruby-hello-world -l app\=hi --env=MYSQL_USER\=root,MYSQL_PASSWORD\=test,MYSQL_DATABASE\=test
> $ oc rsh mysql-55-centos7-1-f42js
> $ mysql -uroot -ptest -h $HOSTNAME                                   
> ERROR 1130 (HY000): Host '10.1.0.32' is not allowed to connect to this MySQL server

The above has worked in the past.

I can *connect* MySQL using local unix socket but root *login* not accepted.

Version-Release number of selected component (if applicable):
observe it at commit 65b3d6b82 (but not sure when it was introduced)

> # openshift version
> openshift v3.0.1.900-185-g2f7757a

How reproducible:
always

--- Additional comment from Ben Parees on 2015-09-14 11:13:28 EDT ---

Was probably introduced when we introduced password changing, but I think this behavior is ok.  if you want to set the root password, use MYSQL_ROOT_PASSWORD

https://docs.openshift.org/latest/using_images/db_images/mysql.html#environment-variables

Martin it might be good to handle this edge case.

--- Additional comment from Martin Nagy on 2015-09-15 04:57:59 EDT ---

Ben, what would the right behaviour in this case be? I think we should forbid setting MYSQL_USER to root and instead make MYSQL_USER and MYSQL_PASSWORD optional when MYSQL_ROOT_PASSWORD is set.

Setting as MYSQL_DATABASE as optional in that case would theoretically be possible as well, but wouldn't allow replication, since that has to be configured in the file, so I probably wouldn't go there.

--- Additional comment from Aleksandar Kostadinov on 2015-10-22 14:17:20 EDT ---

Any update?

--- Additional comment from XiuJuan Wang on 2015-10-23 07:04:15 EDT ---

Can't connect mysql from app pod even has set MYSQL_ROOT_PASSWORD.

$  oc  env pods database-1-r45x8 --list
# pods database-1-r45x8, container ruby-helloworld-database
MYSQL_USER=user0WQ
MYSQL_PASSWORD=SXlwPa3K
MYSQL_ROOT_PASSWORD=SXlwPa3K
MYSQL_DATABASE=root
[wxj@dhcp-129-163 openshift]$ oc  get pods 
NAME                        READY     STATUS      RESTARTS   AGE
database-1-r45x8            1/1       Running     0          5m
frontend-1-c3arn            1/1       Running     0          1m
frontend-1-pjx6p            1/1       Running     0          1m
ruby-sample-build-1-build   0/1       Error       0          5m
ruby-sample-build-2-build   0/1       Completed   0          3m
[wxj@dhcp-129-163 openshift]$  oc  env pods frontend-1-c3arn  --list
# pods frontend-1-c3arn, container ruby-helloworld
ADMIN_USERNAME=adminMKF
ADMIN_PASSWORD=tDgpEv0d
MYSQL_USER=user0WQ
MYSQL_PASSWORD=SXlwPa3K
MYSQL_ROOT_PASSWORD=SXlwPa3K
MYSQL_DATABASE=root

$oc  logs frontend-1-pjx6p | less
you might consider adding 'puma' into your Gemfile.
Run app...
Connecting to test database (@:)...
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Connecting to test database (@:)...
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

--- Additional comment from Michal Fojtik on 2015-11-10 03:26:34 EST ---

(In reply to XiuJuan Wang from comment #4)
> Can't connect mysql from app pod even has set MYSQL_ROOT_PASSWORD.
> 
> $  oc  env pods database-1-r45x8 --list
> # pods database-1-r45x8, container ruby-helloworld-database
> MYSQL_USER=user0WQ
> MYSQL_PASSWORD=SXlwPa3K
> MYSQL_ROOT_PASSWORD=SXlwPa3K
> MYSQL_DATABASE=root
> [wxj@dhcp-129-163 openshift]$ oc  get pods 
> NAME                        READY     STATUS      RESTARTS   AGE
> database-1-r45x8            1/1       Running     0          5m
> frontend-1-c3arn            1/1       Running     0          1m
> frontend-1-pjx6p            1/1       Running     0          1m
> ruby-sample-build-1-build   0/1       Error       0          5m
> ruby-sample-build-2-build   0/1       Completed   0          3m
> [wxj@dhcp-129-163 openshift]$  oc  env pods frontend-1-c3arn  --list
> # pods frontend-1-c3arn, container ruby-helloworld
> ADMIN_USERNAME=adminMKF
> ADMIN_PASSWORD=tDgpEv0d
> MYSQL_USER=user0WQ
> MYSQL_PASSWORD=SXlwPa3K
> MYSQL_ROOT_PASSWORD=SXlwPa3K
> MYSQL_DATABASE=root
> 
> $oc  logs frontend-1-pjx6p | less
> you might consider adding 'puma' into your Gemfile.
> Run app...
> Connecting to test database (@:)...
> Can't connect to local MySQL server through socket
> '/var/lib/mysql/mysql.sock' (2)
> Connecting to test database (@:)...
> Can't connect to local MySQL server through socket
> '/var/lib/mysql/mysql.sock' (2)

Can you please open new BZ for this one? That seems not related to the original issue.

--- Additional comment from XiuJuan Wang on 2015-11-10 05:47:52 EST ---

@Michal, sorry I can't reproduce the comment #4 issue with the latest ruby-20-centos7(imageid 080e878d0080) and mysql-55-centos7(imageid 31922f00486e).

If next I could reproduce it, I will report a new bug.

--- Additional comment from Martin Nagy on 2015-11-10 12:41:41 EST ---

PR with fix: https://github.com/openshift/mysql/pull/109

I've made us handle MYSQL_USER=root case. It should also error out if you specify MYSQL_USER=root and MYSQL_PASSWORD and MYSQL_ROOT_PASSWORD.

--- Additional comment from Honza Horak on 2015-11-10 13:16:56 EST ---

Do we need to hurry with fixing this for RHSCL images GA or can we wait with this change till next update? From my PoV the later (waiting till next update) is possible, but I may miss some consequences.

--- Additional comment from Aleksandar Kostadinov on 2015-11-10 13:23:21 EST ---

Is there any simple way to test the quickstart off the PR?

--- Additional comment from Martin Nagy on 2015-11-12 13:33:52 EST ---

PR got merged.

--- Additional comment from Martin Nagy on 2015-11-12 14:14:54 EST ---

Honza, this is low priority, I think

--- Additional comment from XiuJuan Wang on 2015-11-13 06:29:30 EST ---

it's better to prompt not set MYSQL_USER to root when set MYSQL_USER=root.

But now the error is confused for customer:
$oc logs mysql-55-centos7-1-9rvzv 
You must either specify the following environment variables:
  MYSQL_USER (regex: '^[a-zA-Z0-9_]+$')
  MYSQL_PASSWORD (regex: '^[a-zA-Z0-9_~!@#$%^&*()-=<>,.?;:|]+$')
  MYSQL_DATABASE (regex: '^[a-zA-Z0-9_]+$')
Or the following environment variable:
  MYSQL_ROOT_PASSWORD (regex: '^[a-zA-Z0-9_~!@#$%^&*()-=<>,.?;:|]+$')
Or both.
Optional Settings:
  MYSQL_LOWER_CASE_TABLE_NAMES (default: 0)
  MYSQL_MAX_CONNECTIONS (default: 151)
  MYSQL_FT_MIN_WORD_LEN (default: 4)
  MYSQL_FT_MAX_WORD_LEN (default: 20)
  MYSQL_AIO (default: 1)

--- Additional comment from Martin Nagy on 2015-11-13 10:20:59 EST ---

Xiujuan, I don't want to add another exception to the usage.. The general message is still valid. User specifying root is a corner case.

--- Additional comment from Aleksandar Kostadinov on 2015-11-13 10:40:45 EST ---

I think it's reasonable to set root password when MYSQL_USER=root. And then complain if MYSQL_PASSWORD and MYSQL_ROOT_PASSWORD are both set. This is the changed behaviour as far as I understand.

I don't see any reason to disallow user to set MYSQL_USER=root

--- Additional comment from Martin Nagy on 2015-11-13 10:42:03 EST ---

Yes, that is the new behaviour.

--- Additional comment from XiuJuan Wang on 2015-11-16 05:23:02 EST ---

Martin and Alex,
Thanks!
Move bug to verified.

1.Could create mysql pod when only set MYSQL_ROOT_PASSWORD. And could connect mysql.

$oc new-app --docker-image=openshift/mysql-55-centos7 --code=https://github.com/openshift/ruby-hello-world -l app=hi --env=MYSQL_ROOT_PASSWORD=test

bash-4.2$ mysql -h 172.30.111.94 -u root -ptest
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show  databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

2.Could create mysql pod when set  MYSQL_USER(Not root) MYSQL_PASSWORD and MYSQL_DATABASE.Could connect mysql

$ oc new-app --docker-image=openshift/mysql-55-centos7 --code=https://github.com/openshift/ruby-hello-world -l app=hi --env=MYSQL_PASSWORD=test,MYSQL_USER=test,MYSQL_DATABASE=test


3.Will prompt error in mysql pod log when specify MYSQL_USER=root and MYSQL_PASSWORD and MYSQL_ROOT_PASSWORD