CODEWITHSUNDEEP
mysql
user972946
user972946

Reputation:

Paste to mysql CLI does not work - it loses some characters

I paste a >2KB SQL file to mysql CLI and it randomly loses characters and then reports syntax error in my SQL.

For example:

(.....)
UPDATE ct_transform_target_summary
SET
ytd_margin_target = jul_margin_target + aug_margin_target + sep_margin_target + oct_margin_target + nov_margin_target + dec_margin_target +
                    jan_margin_target + feb_margin_target + mar_margin_target + apr_margin_target + may_margin_target + jun_margin_target,
ytd_adjustments = jul_margin_adj + aug_margin_adj + sep_margin_adj + oct_margin_adj + nov_margin_adj + dec_margin_adj +
                  jan_margin_adj + feb_margin_adj + mar_margin_adj + apt_margin_adj + may_margin_adj + jun_margin_adj,
ytd_margin = jul_margin + aug_margin + sep_margin + oct_margin + nov_margin + dec_margin +
             jan_margin + feb_margin + mar_margin + apr_margin + may_margin + jun_margin;
(....)

Becomes:

(....)
->   
->   
->   SET
->     ytd_margin_target = jul_margin_target + aug_margin_target + sep_margin_target + oct_margin_target + nov_margin_target + dec_margin_target +
->                    
->     ytd_adjustments = jul_margin_adj + aug_margin_adj + sep_margin_adj + oct_margin_adj + nov_margin_adj + dec_margin_adj +
->                       jan_margin_adj + feb_ma
->     ytd_margin = jul_margin + aug_margin + sep_margin + oct_margin + nov_margin + dec_margin +
->                  jan_margin + feb_margin + mar_margin + apr_margin + may_

This apparently never happens to short SQL code, but only happens to long code. Could this be caused by my terminal (Fedora 17 Gnome terminal) or could it be an issue of mysql CLI?

Never had I experienced such problem in terminal before. It only happens in mysql cli.

Upvotes: 3

Views: 2004

Answers (4)

djjolicoeur
djjolicoeur

Reputation: 484

I think Sammitch is on the right track here, but I believe that solution only works if you are logged into the machine where mysql is living. The following should work from anywhere. 

$ vi query.sql
$ mysql -h db.host.tld -u user -p mydatabase < query.sql

Pasting into the CLI always makes me nervous!

Upvotes: 1

lanzz
lanzz

Reputation: 43168

Pasting into the MySQL CLI client is tricky, because it uses Readline for interactive line editing. Readline interprets certain input as control sequences and does not pass them verbatim to the MySQL client. The character most likely to be causing problems in your case is tabulation, used often for indentation purposes in SQL, but used in Readline for tab completion. Inadvertent tab completion in the middle of your query might have very unexpected results and it is often hard to pinpoint the exact location where it has introduced interference.

Upvotes: 4

Sammitch
Sammitch

Reputation: 32232

Write the SQL to a file, ie: query.sql, then use the source command in the mySQL CLI.

$ vi query.sql
$ mysql -h db.host.tld -u user -p
mysql> use mydatabase
mysql> source query.sql

Upvotes: 5

Michael
Michael

Reputation: 10474

Does this happen when you paste to a file in your terminal on your mysql server? Most likely you are having a buffer issue when pasting across the network. But I would check pasting to a file on the same server and if that works, then it is the mysql cli that is causing some issue, but I bet it's just the network latency causing problems.

Upvotes: 0

Related Questions