mysql - .mysql_history not being saved automatically -

i've been unable mysql command-line history working user, though works fine other users on same machine (mysql 5.5 on debian "wheezy" distro.) i'm @ wit's end, , hoping here can me...

whenever start mysql, have no history scroll (it gives visual bell alert when try up-arrow example).

however, i'm using it, history works fine (within single session). i.e., can go earlier commands since started mysql. but, second quit mysql, lose history , have start on again next time.

needless say, extremely frustrating!

to troubleshoot, did 3 things, none of made difference @ all:

(1) explicitly set environment variable (using bash):

% mysql_histfile=~/.mysql_history % echo $mysql_histfile /var/home/userx/.mysql_history 

... , double-checked permissions set correctly (both on file , on directory -- note, created empty file sure wasn't having trouble creating itself):

drwxr-xr-x  53 userx userx 4096 jan 24 15:26 /var/home/userx -rw-------   1 userx userx    0 jan 31 04:14 /var/home/userx/.mysql_history 

i confirm "-rw-------" , file owned user in question (me), identically other users on same machine whom works fine. though, mysql client documentation doesn't need set environment variable unless want change (so i've of course tried without setting variable well).

(2) tried setting/tweaking various logging-related configs in /etc/mysql/my.cnf (from looking @ documentation; however, of settings seem logging, not command-line history).

none of settings in /etc/mysql/*.cnf seems have command-line logging (only server-level logging, e.g. /var/log/mysql...).

to sure, reverted how in standard installation (via debian wheezy apt-get install mysql) of mucking around couldn't have been reason. (note: works fine other users on same exact machine!)

(3) tried examining/tweaking various variables within mysql (based on various things i've seen posted). these hard find information on, , since works other users on same machine, i'm skeptical whether matter. anyway, here's did here:

first, list of currently-set variables, did:

% echo "show variables" | mysql > /tmp/vars 

looking through them, didn't see seemed relevant. here're examples (it's long dump of them here; let me know if there's 1 variable or 1 search can may yield answer, though):

% grep -i hist /tmp/vars performance_schema_events_waits_history_long_size       10000 performance_schema_events_waits_history_size    10 profiling_history_size  15 

... ...

% grep -i log /tmp/vars    (note: irrelevant binlog stuff excerpted) back_log        50 expire_logs_days        10 general_log     off general_log_file        /var/lib/mysql/xxx.log innodb_log_group_home_dir       ./ innodb_mirrored_log_groups      1 log     off log_error log_output      file log_queries_not_using_indexes   off log_slave_updates       off log_slow_queries        off log_warnings    1 slow_query_log  off slow_query_log_file     /var/lib/mysql/rimu3-slow.log 

none of these seems relevant, nor did did based on various web search relating changing variables solve problem -- and, remember: none of other users on same machine has problem. unless variables or other server state haven't found refers user, or there access policy somewhere haven't discovered refers user (for example), not explainable.

the documentation can find anywhere mysql_history file here. doesn't tell how enable mysql_history! (it says how disable it, or change goes, doesn't change me.)

to wrap up, have confirmed that, in end, troubleshooting didn't end leaving set incorrectly: i'm standard environment variables, server configs, variables, etc.

i'm stumped here. immensely appreciated!

steve

i found problem.

there two files used mysql history: ~/.mysql_history , ~/.mysql_history.tmp. discovered second file using ptrace:

open("/var/home/userx/.mysql_history.tmp", o_wronly|o_creat|o_trunc|o_largefile, 0600) = -1 eacces (permission denied) 

knowing there 2 files, issue becomes clearer:

% ls ~/.mysql_history* -rw-------   1 userx userx      0 jan 31 04:14 /var/home/userx/.mysql_history -rw-------   1 root  root  279506 may 11  2014 /var/home/userx/.mysql_history.tmp 

(and yes, problem has gone may 2014, makes make sense now.)

in case, had root access via sudo, fix it:

% sudo chown userx:userx /home/userx/.mysql_history.tmp 

and subsequent use of mysql worked (though history in between still lost forever). :-(

the root problems are: (a) mysql documentation makes no mention of file (and in fact, shouldn't need it), , (b) mysql client doesn't give error message end user letting them know file unmodifiable either @ start-up or @ exit.

--

so, there have it, in nutshell:

(1) mysql documentation fails mention anywhere uses .mysql_history.tmp needs same permissions.

(2) using wrapper rlwrap worked around (see comment above) because apparently doesn't use .tmp version of file.

steve