Re: SQL Mode per user?

[Roland Bouman]

Actually, you can. It's a bit of a dirty trick I guess, but it sure does seem to work.

I did it like this. I created two users, ansi and traditional. Then I put this line in My.ini in the [mysqld] (server) section:

init_connect='select @sql_mode:=substring_index(current_user,\'@\',1); set sql_mode:=@sql_mode;'

the init_connect is a (list of) queries that is executed whenever a user connects. It is not executed for root, and i think also not for other users with the super privileges. (This is to prevent root from being locked out when this contains an invalid query)

Now, the query here strips of the local part of the username (the part before the @, so that 'ansi@%' yields just plain 'ansi') and stores it in a user variable (@sql_mode). Then, the value of the user variable is used to set the sql_mode.

At first, i tried to do

select @@sql_mode:= etc.

but alas this does not work: you need to do it in two steps.

I think it's possible to put all this in a file, and reference that from the ini file, but I dont know how. If you find out, please let me know.