Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It is really a a headache for me from some hours i am struggling on the problem.I have also asked it on stack-overflow but not got any answer yet so i am asking here.Sorry for Not a good question.

I am Executing a Command on Linux shell.

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,'\\G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');"

I want output as

SHOW CREATE TABLE sakila.category\G
SHOW CREATE TABLE sakila.city\G
SHOW CREATE TABLE sakila.country\G
SHOW CREATE TABLE sakila.customer\G

But I am getting as

SHOW CREATE TABLE sakila.categoryG
SHOW CREATE TABLE sakila.cityG
SHOW CREATE TABLE sakila.countryG
SHOW CREATE TABLE sakila.customerG

Why it is so..?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

This has always been a real pain-in-the-neck for me.

  • The mysql client seems to interpret the \\G as \G
  • The OS seems to take that output and interpret \G as G

Unfortunately, this is the sequence that always happens.

I use the sed command to get around this as follows:

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,' G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');" | sed 's/ G$/\\G/g'

Notice that I replaced \\G with G (blank followed by G)

I have the sed command translate G$ (which means the blank capital G at the end of of line) and convert it to \G. Notice that sed is doing this conversion with OS output and mysql output.

You no longer have to deal with that \G issue. Simply plan for a blank capital G to be transalted by sed to \G.

share|improve this answer
    
if we use -Bre (r stands for raw) instead of s then it works with \\\\G. –  Abdul Manaf Feb 24 '12 at 3:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.