Bug #5792 MYSQL_TCP_PORT is ignored in mysql command-line client
Submitted: 29 Sep 2004 5:20 Modified: 2 Dec 2005 19:48
Reporter: Masaki Fujimoto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:4.0.21, 5.0.15-BK, 4.1.15-BK OS:Linux (Linux)
Assigned to: Jim Winstead CPU Architecture:Any

[29 Sep 2004 5:20] Masaki Fujimoto
Description:
The manual says that mysql command line clients honor MYSQL_TCP_PORT
environment variable, and in case no option (i.e. --port) is not supplied and no 
port option is not supplied through my.cnf, value specified in MYSQL_TCP_PORT
is recognized the TCP port for connecting servers. And first and foremost, 
MySQL 3.23.x behaves as such.

However, as of MySQL 4.0.x, mysql command line clients always ignore MYSQL_TCP_PORT
envrionment variable as follows:

MySQL 3.23.x:
$ MYSQL_TCP_PORT=13306
$ export MYSQL_TCP_PORT
$ /usr/local/mysql/bin/mysql -h db.example.com
-> connects to db.example.com:13306

MySQL 4.0.x:
$ MYSQL_TCP_PORT=13306
$ export MYSQL_TCP_PORT
$ /usr/local/mysql/bin/mysql -h db.example.com
-> connects to db.example.com:3306

I think MySQL 4.0.x should also honor this environmental variable (MYSQL_HOST,
MYSQL_HISTFILE, MYSQL_UNIX_PORT are OK, and only MYSQL_TCP_PORT has no
effect).

How to repeat:
$ MYSQL_TCP_PORT=13306
$ export MYSQL_TCP_PORT
$ /usr/local/mysql/bin/mysql -h db.example.com

-> mysql client ignores MYSQL_TCP_PORT environment variable and connects to
    port 3306 (default).

Suggested fix:
I think the default value should be 0:

--- mysql.cc.bak        2004-09-29 14:10:32.000000000 +0900
+++ mysql.cc    2004-09-29 14:10:43.000000000 +0900
@@ -531,7 +531,7 @@
    NO_ARG, 0, 0, 0, 0, 0, 0},
 #endif
   {"port", 'P', "Port number to use for connection.", (gptr*) &opt_mysql_port,
-   (gptr*) &opt_mysql_port, 0, GET_UINT, REQUIRED_ARG, MYSQL_PORT, 0, 0, 0, 0,
+   (gptr*) &opt_mysql_port, 0, GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0,
    0},
   {"prompt", OPT_PROMPT, "Set the mysql prompt to this value.",
    (gptr*) &current_prompt, (gptr*) &current_prompt, 0, GET_STR_ALLOC,
[1 Oct 2004 22:05] Hartmut Holzgraefe
Verified, the supplied patch seems to be valid.
[4 Oct 2004 8:40] Sergey Glukhov
ChangeSet
  1.2038 04/10/04
  Fix for bug #5792:MYSQL_TCP_PORT is ignored in mysql command-line client
[15 Feb 2005 13:16] Alexander Barkov
I found several problems:

- This patch doesn't seem to help in 4.1.
- "mysql --help" displays that the default port number is 0 after this fix.
- mysqladmin, mysqldump, etc, should be fixed too.

Changing back to "In progress"
[4 Oct 2005 13:25] Mark Matthews
Reverify please.
[6 Oct 2005 13:00] Valeriy Kravchuk
I tried to answer the following questions on 5.0.15-rc-BK (ChangeSet@1.2019, 2005-10-06 03:49:14+02:00, kent@mysql.com):

- "mysql --help" displays that the default port number is 0 after this fix.

Yes, it still displays 0

- mysqladmin, mysqldump, etc, should be fixed too.

mysqldump and mysqladmin ignores this environment variable.

mysql client in 5.0.15, though, takes it into account properly:

[openxs@Fedora 5.0]$ libexec/mysqld --port=13306 --socket=/tmp/t.sock &
[1] 13354
[openxs@Fedora 5.0]$ bin/mysql -uroot --socket=/tmp/t.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.15-rc

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on *.* to 'root'@'192.168.0.46';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[openxs@Fedora 5.0]$ unset MYSQL_TCP_PORT
[openxs@Fedora 5.0]$ bin/mysql -uroot --host=192.168.0.46
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.46' (111)
[openxs@Fedora 5.0]$ export MYSQL_TCP_PORT=13306
[openxs@Fedora 5.0]$ bin/mysql --help | grep port
  -P, --port=#        Port number to use for connection.
port                              0
[openxs@Fedora 5.0]$ bin/mysqldump test -uroot --host=192.168.0.46
mysqldump: Got error: 2003: Can't connect to MySQL server on '192.168.0.46' (111) when trying to connect
[openxs@Fedora 5.0]$ bin/mysql -uroot --host=192.168.0.46
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.15-rc

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye

[openxs@Fedora 5.0]$ bin/mysqladmin -uroot --host=192.168.0.46  shutdown
bin/mysqladmin: connect to server at '192.168.0.46' failed
error: 'Lost connection to MySQL server during query'
[openxs@Fedora 5.0]$ bin/mysqladmin -uroot --port=13306 --host=192.168.0.46  shutdown
051006 15:50:08 [Note] libexec/mysqld: Normal shutdown

[openxs@Fedora 5.0]$ 051006 15:50:08 [Note] libexec/mysqld: Shutdown complete

On 4.1.15-BK (ChangeSet@1.2468, 2005-10-04 10:54:58+02:00, georg@lmy002.wdf.sap.corp) this environment variable is ignored completely, even by mysql client:

[openxs@Fedora 4.1]$ libexec/mysqld --port=13306 --socket=/tmp/t.sock &
[1] 20604
051006 15:52:08  InnoDB: Started; log sequence number 0 67786
port=13306  libexec/mysqld: ready for connections.
Version: '4.1.15-debug'  socket: '/tmp/t.sock'  port: 13306  Source distribution

[openxs@Fedora 4.1]$ bin/mysql --help | grep port
  -P, --port=#        Port number to use for connection.
port                              3306
[openxs@Fedora 4.1]$ export MYSQL_TCP_PORT=13306
[openxs@Fedora 4.1]$ bin/mysql --help | grep port
  -P, --port=#        Port number to use for connection.
port                              3306
[openxs@Fedora 4.1]$ bin/mysql -uroot --host=192.168.0.46
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.46' (111)
[openxs@Fedora 4.1]$ bin/mysql -uroot --port=13306 --host=192.168.0.46
ERROR 1045 (28000): Access denied for user 'root'@'Fedora.mshome.net' (using password: NO)
[openxs@Fedora 4.1]$ bin/mysql -uroot --socket=/tmp/t.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.15-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all privileges on *.* to 'root'@'192.168.0.46';
Query OK, 0 rows affected (0.07 sec)

mysql> exit
Bye
[openxs@Fedora 4.1]$ bin/mysql -uroot --host=192.168.0.46
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.46' (111)
[openxs@Fedora 4.1]$ bin/mysql -uroot --port=13306 --host=192.168.0.46
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.15-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> exit
Bye
[openxs@Fedora 4.1]$ echo $MYSQL_TCP_PORT
13306

So, that is the current state of this bug.
[13 Oct 2005 19:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31058
[13 Oct 2005 19:29] Jim Winstead
This patch still resuts in 0 being display from --help. There's not really a way around this without possibly allowing MYSQL_TCP_PORT to override a command-line specification of --port=3306.
(The port gets substituted with MYSQL_TCP_PORT in mysql_server_init(), which is always called
after command-line options are handled, which is when --help is output.)
[29 Nov 2005 19:37] Jim Winstead
Fixed in 5.0.17 and 5.1.4.
[2 Dec 2005 19:48] Paul Dubois
Noted in 5.0.17, 5.1.4 changelogs.