MySQL history show a lot of \040

Question

When I tried to see the history of mysql - I see this

cat ~/.mysql_history

I got this :

└──  cat ~/.mysql_history
_HiStOrY_V2_
exit
GRANT\040ALL\040PRIVILEGES\040ON\040*\040.\040*\040TO\040'jada'@'localhost';
FLUSH\040PRIVILEGES;
select\040User,Host\040from\040mysql.user;
exit
select\040User,Host\040from\040mysql.user;
GRANT\040ALL\040PRIVILEGES\040ON\040*\040.\040*\040TO\040'jada'@'localhost';
FLUSH\040PRIVILEGES;
select\040User,Host\040from\040mysql.user;
exit
drop\040database\app;\040
show\040databases;\040
create\040database\app;\040
exit
show\040databases;\040
use\app;\040
select\040*\040from\040users;\040
exit
use\app;\040
select\040*\040from\040users;\040
exit
select\040User,Host\040from\040mysql.user;
GRANT\040ALL\040PRIVILEGES\040ON\040*\040.\040*\040TO\040'jada'@'localhost';
FLUSH\040PRIVILEGES;
select\040User,Host\040from\040mysql.user;
exit
select\040User,Host\040from\040mysql.user;
exit
select\040*\040from\040users;
show\040databases;\040
use\app\040;\040
select\040*\040from\040users;
DELETE\040FROM\040table_name\040WHERE\040condition;
DELETE\040FROM\040users\040WHERE\040id\040=\0402;
select\040*\040from\040users;
history
show\040history;
exit

I saw a bunch of \040.

How do I see a clean history like a regular history in bash ?

I've tried on 5 VMs. Same result. All the time.

Berm

\040 is the space character. – Horodko

I don't see anything like that. What does type cat show? – Horodko

What version of mysql are you running? – Horodko

@Horodko I am on Server version: 8.0.20-0ubuntu0.19.10.1 – Berm

type cat return cat is hashed (/usr/bin/cat) – Berm

This is a client issue, not server. But maybe they changed the format of the history file (I'm still using 5.x). – Horodko

I do not think they changed the format... (so it must be a client issue), try hash -r (after reading what it will do) – Gigahertz

I tried on the Terminal and ssh shell , 2 apps show the same. – Berm

client is more than how you connect to your server.... (hash has not much to do with the client, only with how it behaves) – Gigahertz

Please share more details. How is this even related to programming? To me, this looks pretty off-topic here – Hardaway

The file is a text file, and actually has those 4 characters for space. It probably has 4 characters for many other characters, especially 8-bit codes. – Accompany

FYI: \040 is the octal escape sequence for "space". (This does not explain how to undo the seemingly unnecessary escaping. – Accompany

Answer 1

I remember is old bug on mysql client https://bugs.mysql.com/bug.php?id=68925 try to update it

Krispin

This answer has been awarded bounties worth 25 reputation by Community!? This is more a link than an answer. Anyway this don't give an answer to How do I see a clean history like a regular history in bash ? ;-) – Wordy

Answer 2

sed 's/\\040/ /g' < .mysql_history

This only fixes spaces; I don't know the extent of the problem.

\0xx is an octal escape sequence. 40 is the octal for space.

Accompany

Answer 3

This is a common job for perl:

perl -pe 's/\\([0-7]{1,3})/chr oct $1/eg' <.mysql_history

This will resolve octal representations (not only spaces).

Demo/test:

perl -pe 's/\\([0-7]{1,3})/chr oct $1/eg' <<<"SELECT\040\042String\042;"
SELECT "String";

But this could by done under shell

Line by line, mostly if required for one line, you could

echo -e "GRANT\040ALL\040PRIVILEGES\040ON\040*\040.\040*\040TO\040'jada'@'localhost';"
GRANT ALL PRIVILEGES ON * . * TO 'jada'@'localhost';

or better, with bash:

printf %b\\n "GRANT\040ALL\040PRIVILEGES\040ON\040*\040.\040*\040TO\040'jada'@'localhost';"
GRANT ALL PRIVILEGES ON * . * TO 'jada'@'localhost';

Of couse, you could loop over whole file:

while read -r line ;do
    printf '%b\n' "$line"
done <.mysql_history

For doing this exhaustively by using sed

You have to run sed twice:

sed "$(sed 's/\\\([0-9]\{3\}\)/\\o\1/g;s/^/1a\\/')" <<<''

For stdin or

sed "$(sed 's/\\\([0-9]\{3\}\)/\\o\1/g;s/^/1a\\/' .mysql_history)" <<<''

Wordy

Answer 4

You can simply replace the \040 with space character using sed command and define an alias for the same, so that you can simply call alias instead of repeating the full command: Alias mysqlh for mysql history

$ alias mysqlh="cat ~/.mysql_history | sed 's/\\\040/ /g'"
$ mysqlh
_HiStOrY_V2_
exit
GRANT ALL PRIVILEGES ON * . * TO 'jada'@'localhost';
FLUSH PRIVILEGES;
select User,Host from mysql.user;
exit
select User,Host from mysql.user;
GRANT ALL PRIVILEGES ON * . * TO 'jada'@'localhost';
FLUSH PRIVILEGES;
select User,Host from mysql.user;
exit
drop database\app;
show databases;
create database\app;
exit
show databases;
use\app;
select * from users;
exit
use\app;
select * from users;
exit
select User,Host from mysql.user;
GRANT ALL PRIVILEGES ON * . * TO 'jada'@'localhost';
FLUSH PRIVILEGES;
select User,Host from mysql.user;
exit
select User,Host from mysql.user;
exit
select * from users;
show databases;
use\app ;
select * from users;
DELETE FROM table_name WHERE condition;
DELETE FROM users WHERE id = 2;
select * from users;
history
show history;
exit

Prober

Avoid useless use of cat! write: alias mysqlh="sed 's/\\040/ /g' < .mysql_history" from Rick James's answer – Wordy