Why does the MySQL built-in function 'current_user()' return a hostname containing a '%' symbol?

Question

Does MySQL have built in function to get host name?

Similar to

select user(); //this returns  user@userip

Edit:

select current_user(); //returns user@10.0.3.%

Last symbol is % -- why?

score 7 · Answer 1 · answered Oct 21 '11 at 09:38

7

SELECT @@hostname;

--mysql 4.1 didn't have this one.

answered Oct 21 '11 at 09:38

Joao Costa

2,563
1
21
15

score 3 · Answer 2 · answered Jan 08 '13 at 21:22

If you want the hostname of the database server, you can use SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'hostname';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | munda |
+---------------+-------+
1 row in set (0.00 sec)

It's not a built-in function so it can't be used in a SELECT statement.

Nishant · Answer 3 · 2011-01-06T14:44:02.330

wouldn't his work?

~~select substring_index(user(),'@', -1) as hostname;~~

The above is wrong, it returns the user's IP not host's. I was fooled by testing on local. Sorry about that.

I guess this returns host name, but this wouldn't be useful unless you are ready to grep, pipe and cut Just a FYI:

C:\>mysqladmin -u username -pmypassword -h dev.naishelabs.com version

mysqladmin  Ver 8.41 Distrib 5.0.22, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.77
Protocol version        10
Connection              dev.naishelabs.com via TCP/IP
TCP port                3306
Uptime:                 73 days 5 hours 7 min 45 sec

this will return ip of user, not host ip :( – Edmhs Jan 06 '11 at 14:11 — Edmhs, Jan 06 '11 at 14:11

score 3 · Accepted Answer · answered Jan 06 '11 at 14:36

3

select current_user(); returns user@10.0.3.% last simbol is % why ??

the % is the record in mysql.user that match your current login

which can be derived from

select concat(user, '@', host) from mysql.user;

the % is determined by host value.

answered Jan 06 '11 at 14:36

ajreal

46,720
11
89
119

score 2 · Answer 5 · answered Jan 06 '11 at 14:05

2

Are you looking for CURRENT_USER function.

Returns the user name and host name combination for the MySQL account that the server used to authenticate the current client. This account determines your access privileges.

The value of CURRENT_USER() can differ from the value of USER().

answered Jan 06 '11 at 14:05

Naveed

41,517
32
98
131

1

select current_user(); returns user@10.0.3.% last simbol is % why ?? – Edmhs Jan 06 '11 at 14:11
Where your database is hosted. On your local machine or somewhere else on the network. And what type of network you are using. Is it virtual environment? – Naveed Jan 06 '11 at 14:18

score 2 · Answer 6 · answered Jan 06 '11 at 14:11

2

You can use user() and current_user() functions. If you want only hostname do something like select substr(current_user(),LOCATE('@', current_user())+1) AS localhost;
You can find details here

answered Jan 06 '11 at 14:11

cristian

8,676
3
38
44

this returns me 10.43.0.% with % in the end why? – Edmhs Jan 06 '11 at 14:13
% at the end means that you can connect from every ip in the range 10.43.0.1 .. 10.43.0.255 (i'm not sure, this is what i believe to be) – cristian Jan 06 '11 at 14:23
The "10.43.0.%" is part of your current user, it's what you have granted access to, and is stored in the mysql tables. (that is, user@10.43.0.% is the user as mysql knows it from its user tables, the IP part is not taken directly from the connection you have to the server) – nos Jan 06 '11 at 14:51
select @@hostname; is the solution – panofish Mar 24 '16 at 17:15

dotancohen · Answer 7 · 2013-12-03T14:34:13.230

The @@hostname variable contains the system hostname:

$ cat /etc/hostname
bruno

$ hostname
bruno

$ mysql
mysql> SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| bruno      |
+------------+

Note that this can be combined and used in other queries:

mysql> SELECT name, @@hostname FROM people;
+-------+-------------+
| name  | @@hostname  |
+-------+-------------+
| Dotan | bruno       |
+-------+-------------+

mysql> SELECT CONCAT('I am on server ', @@hostname);
+---------------------------------------+
| CONCAT('I am on server ', @@hostname) |
+---------------------------------------+
| I am on server bruno                  |
+---------------------------------------+

Note that this answer answers the originally asked question `Does Mysql have build in function to get host name?` and that the question and title have since been edited to a completely different question. — dotancohen, Mar 24 '16 at 17:33

Why does the MySQL built-in function 'current_user()' return a hostname containing a '%' symbol?

7 Answers7