In MS SQL Server, I can set workstationId in the connection string and, in queries and procedures, retrieve that value via the host_name() function.

I've tried setting workstationId in mysql, but there is no host_name() function. The only thing I can find in MYSQL is:

select variable_value from information_schema.global_variables where variable_name = 'hostname'

which indeed returns the hostname, but not my intended override.

Any way to do what I want?

n/m.

Sorry, don't understand "n/m"

1 members found this post helpful.

There is a mysql hostname system variable also
http://dev.mysql.com/doc/refman/5.7/...variables.html

Yup, that gives me my hostname, but I can't set it. It's read-only.

Is there a way to query for my connection string?

also, I can set a variable after connecting from a java program:

set @myvar = 'something';

Then I can query it:

select @myvar;

And I get the set value. If I connect again later, but don't set the variable, the value is null. That tells me it persists for the duration of the connection. But, will that be set for my session only (hopefully)? I don't want someone else who happens to connect at the same to to get the wrong value, or set it themselves to something different.

In other words, is setting a variable like this global or local to the session?

These variables are memory-only objects. Use tables for shared/permanent data.

I don't get it, why whould you want to change the server hostname once connected, also what do you mean with connection string, user name, password, hostname, database?

Guess he wants to record the user's (client's) metadata for later analysis. (Eg client computer name/IP, client process's username/userid (cf identd))

A connection string is used by any program, be it visual basic, java, etc., wanting to connect to a database instance, whether SQL Server, MYSQL, etc. The string, for MYSQL, is of the form:

jdbc:mysql://localhost/members?user=username&password=pw

Where "members" is the db_name. There are many, many additional attributes for a connection string: https://www.connectionstrings.com/mysql/

For SQL server, one of those attributes is workstationID. Normally, this does contain the hostname.

My application is a web server with a MYSQL backend. In this case, the workstation is always the same: the hostname of the web server. I want to override that with the ID of the logged in user. I'm using this information, as NevemTeve guessed, to log who made what changes to database tables from their browser. In SQL Server, I can retrieve this override setting in a query, stored procedure or trigger using the SQL Server function host_name().

None of that works in MYSQL. There is no host_name() function and the @@hostname variable is read only.

I have, however, figured out a solution for MYSQL. As I wrote in my post of 06-17-16, 05:48 PM, I can set my own variable simply by:

set @webuser = 'someUserId';

This can then be retrieve via `select @webuser` in any query, stored procedure or, most importantly for my case, triggers. If not set, it returns null. The variable persists during the connection and is gone upon disconnection. I am assuming this variable is local to the connected session and not global to all concurrent connections, but I've no easy way of testing that.

If any of you know for sure about the scope of such variables, please let me know. For the time being, this mechanism works.

You are correct. Here's a link:

http://dev.mysql.com/doc/refman/5.7/...variables.html

Something like workbench maybe?
https://dev.mysql.com/doc/workbench/...nnections.html

I would also look at performance schema feature
http://dev.mysql.com/doc/refman/5.7/...ce-schema.html

keefaz, workbench looks like a great tool and I'll keep it in mind. In my case, the web user logs in via a web page and the ID and password entered are compared with values retrieved from the database. Tools like workbench would have no knowledge of internal java session values. I use this userId in an trigger attached to tables so I can log the userId, time and column changed into a changelog table.