Unknown command '\;'

Hello,
I am running ubuntu 16.04 .
Below script gives: "unknown command '\;'

counter=105
while read COL1 COL2 COL3
do
let counter++
url="http://localhost/test/$COL1.$COL2.$COL3";
mysql -uroot -D mydatabase -pmypasswd -e "SELECT video_id FROM video_series_files WHERE id IN \
(SELECT video_id FROM video_series_files WHERE url like '%$COL1%.mkv')" > video_id
sed -i '/video_id/d' video_id;
sed -i '/--/d' video_id;
while read -r line
do
mysql -uroot -D mydatabase -pmypasswd -e "INSERT INTO video_series_files (id, video_id, file_type, \
protocol, url, languages, quality, accessed ,status ) VALUES ('.$counter.', \
'$line', 'sub', 'custom', '$url', 'a:1:{i:0;s:2:\'$COL2'\;}',  '', '1', '1' )";
done<video_id
done<2

2

Mr.Smith.Goes.to.Washington     en      srt
Mr.Smith.Goes.to.Washington     fr      srt
Mr.Smith.Goes.to.Washington     el      srt

I have checked the database and I confirm that id 103 and following lines are already empty.
Could you please let me know why I receive this error?

Thank you
Boris

done<2

I do not see where 2 is open for read. 2 is normally stderr. This is abnormal, IMO. It can break things behind the scenes.

Could you please let us know where you receive this error?

Just shooting in the dark, I'd guess it's here: \'$COL2'\;} when the semicolon is escaped in lieu of the second single quote. And, as jim mcnamara already pointed out, the redirection should be reconsidered.

Hello,
Thank you for your answers. (To make it easily readable, long parts of the codes are hidden now)
Upon your recommendation, I tested shown below correlations:

while read COL1 COL2 COL3 && while read -r line <&3; do
..
..
..
'$line', 'sub', 'custom', '$url', 'a:1:{i:0;s:2:\"$COL2"\;}',  '', '1', '1' )";
done<video_id 3<2

Also tried below one:

while read COL1 COL2 COL3 && while read -r line <&3; do
..
..
..
'$line', 'sub', 'custom', "$url', 'a:1:{i:0;s:2:\"$COL2"\;}",  '', '1', '1' )";
done<video_id 3<2

And the last one:

while read COL1 COL2 COL3 && while read -r line <&3; do
..
..
..
'$line', 'sub', 'custom', "$url', 'a:1:{i:0;s:2:\"$COL2\";}",  '', '1', '1' )";
done<video_id 3<2

Still investigating. I doubt second loop is not in correct position as input of second loop is created by first loop .
PS: when bold part is written into sql, output in sql database should be shown like:
| a:1:{i:0;s:2:"fr";} |

Kind regards
Boris

That code snippet seems a bit overcomplicated with its double loop and sed operating on intermediate files. Do you know about the (oracle) set head off equivalent in mysql : -N and -s ? Et voila, two sed invocations gone. You then could pipe the first select (id numbers) into the second loop.

What's the result of the first select per every single loop iteration given we have three times the same film title in file 2 ?

One more question: is 2 the name of the input file, and thus NOT a file descriptor?

Hello Rudic,

• I do not know the equivalent of set head off
• As output of first loop looks like below, I do not know how to pipe it to second mysql process.

video_id
3

That's why, first I remove first line, then read it again with second loop.

• 2 is not a file descriptor. It includes file basename , language and file extension in tab separated format.

filename: 2

Mr.Smith.Goes.to.Washington     en      srt
Mr.Smith.Goes.to.Washington     fr      srt
Mr.Smith.Goes.to.Washington     el      srt

*Output of first mysql query:

output is video_id file:

video_id
3

Last status:

while read COL1 COL2 COL3 && while read -r line <&3; do
..
..
..
'$line', 'sub', 'custom', '$url', 'a:1:{i:0;s:2:\"$COL2"\;}',  '', '1', '1' )";
done<2 3<video_id

Kind regards
Boris

How about preparing an sql- file outside mysql using e.g. awk and then executing this with a single mysql invocation? Like create barisprep :

{print  "INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, accessed ,status )" \
        " SELECT '." ++CNT ".', video_id, 'sub', 'custom', 'http://localhost/test/" $1 "." $2 "." $3 "', 'a:1:{i:0;s:2:'" $2 \
        "';}', '', '1', '1' FROM video_series_files WHERE url like '%" $1 "%.mkv';"}

, running it with

awk -vCNT=$counter -fbarisprep 2 > barisprep.sql

resulting in

INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, accessed ,status ) SELECT '.106.', video_id, 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.en.srt', 'a:1:{i:0;s:2:'en';}', '', '1', '1' FROM video_series_files WHERE url like '%Mr.Smith.Goes.to.Washington%.mkv';
INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, accessed ,status ) SELECT '.107.', video_id, 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.fr.srt', 'a:1:{i:0;s:2:'fr';}', '', '1', '1' FROM video_series_files WHERE url like '%Mr.Smith.Goes.to.Washington%.mkv';
INSERT INTO video_series_files (id, video_id, file_type, protocol, url, languages, quality, accessed ,status ) SELECT '.108.', video_id, 'sub', 'custom', 'http://localhost/test/Mr.Smith.Goes.to.Washington.el.srt', 'a:1:{i:0;s:2:'el';}', '', '1', '1' FROM video_series_files WHERE url like '%Mr.Smith.Goes.to.Washington%.mkv';

and then source it like

mysql -uroot -D mydatabase -pmypasswd -sN  < barisprep.sql

EDIT: or even

mysql -uroot -D mydatabase -pmypasswd -sN  < $(awk -vCNT=$counter -fbarisprep 2)

Thank you so much Rudic!
It's almost okay. The only error is at here:
In barisprep code:

'a:1:{i:0;s:2:'" $2

gives:

'a:1:{i:0;s:2:'en';}'

How may I change it to:

'a:1:{i:0;s:2:"en";}'

Thank you
Boris

Hey c'mon, Baris, that should be easily doable by yourself, don't you think? You did similar things in your post #4.

Yes, I did before posting. This is my 2nd try:

Changed the part as:

'a:1:{i:0;s:2:'" \"$2\" 

gives:

awk: 2: unexpected character '\'
awk: 3: unexpected character '''
awk: barisprep: line 3: runaway string constant "} ...

Also tried without escape:

'a:1:{i:0;s:2:'" "$2"

gives single quote:

{i:0;s:2:'$2';}'

Thank you

Did you look at what youdid in post #4? This a:1:{i:0;s:2:\"$COL2\";}" is close ... complete it with two unescaped double quotes.

Thank you Rudic
It's OK now

FWIW - it seems to me you may not know about a shell facility called a here document.

It lets you write code that dynamically enters data for another program like mysql, and behaves as is if it was you directly typing into the program. What you are doing is temporarily replacing stdin with a shell script or an awk script, or a really complex set of statements, etc. The kind of thing data entry staff employees did 50 years ago.

Syntax: we are going to feed the program yesterday's date and last Friday's date, as a very simple example (uses linux/GNU date in the example)

/path/to/program  <<EOF   [ > /path/to/output file, write program stdout  from program to a file -- optional feature]
me 
password
date -d yesterday
date -d "last friday"
EOF                 
# thanks to RudiC for finding the typo. 

the <<EOF tells the shell to start feeding "keystrokes" to /path/to/program EOF is block name could be anything, Charlie or Baris or !
the EOF marks the end of the code block. This has to be in the LEFTMOST column
Most of what you asked for help with can be handled more easily with a here document.