MySQL query breaks in linux shell script, but query runs in mysql command line

Question

I am referencing the answer by Gunaseelan to this article. UPDATE if exists else INSERT in SQL

I was having a problem writing a query to update if exists else insert in MySQL, but Gunaseelan's solution worked great on the mysql command line. However when I try to use it in a bash script it breaks. I can't seem to see what's wrong here and could really use another pair of eyes to help me figure it out.

mysql> describe wordfreqs;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| frequency    | int(11)      | NO   |     | NULL    |                |
| n_gram       | varchar(100) | NO   | UNI | NULL    |                |
| logic_number | int(11)      | YES  |     | NULL    |                |
| s            | tinyint(4)   | YES  |     | NULL    |                |
| substitution | varchar(100) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Data:

1,PACER ,,,
1,LIQUID NAILS,,,
1,F P C,,,
1,ACE ,,,
3,SIMPSON,,,
1,SUREBONDER,,,
1,DO IT BEST,,,
1,LIQUID NAILS,,,
1,JACKSON,,,
1,DURO,,,
15,JB,1,S,JB WELD
13,DEVIL,1,S,RED DEVIL


mysql> INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution) VALUES (1,'BUCKET',7,1,'BOUQUET') ON DUPLICATE KEY UPDATE frequency = frequency+10;

Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM wordfreqs;                                                                                                                                 +----+-----------+--------+--------------+------+--------------+
| id | frequency | n_gram | logic_number | s    | substitution |
+----+-----------+--------+--------------+------+--------------+
|  1 |        22 | BUCKET |            7 |    1 | BOUQUET      |
+----+-----------+--------+--------------+------+--------------+
1 row in set (0.00 sec)

However in bash script I get different results...

mysql --login-path=local SKU_project -N -e "INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution) VALUES ($freq,'$Ngram',$logicNumber,'$S','$substitution') ON DUPLICATE KEY UPDATE frequency = frequency+$freq;"

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''','') ON DUPLICATE KEY UPDATE frequency = frequency+393' at line 1

Any help would be greatly appreciated. :-)

Thanks @ marekful. Here is the output from a couple of the echo statements:

INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution)
               VALUES (1,'PACER ',,'','') ON DUPLICATE KEY UPDATE frequency = frequency+1;
INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution)
               VALUES (15,'JB',S,'S','JB WELD') ON DUPLICATE KEY UPDATE frequency = frequency+15;
INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution) VALUES (13,'DEVIL',S,'S','RED DEVIL') ON DUPLICATE KEY UPDATE frequency = frequency+13;

Look at (and post) the final command line after variables are interpolated. What data types are your fields? Depending on that, you should have single quotes for strings / dates, etc. in VALUES, e.g. `VALUES($freq, '$Ngram' ... )` if $Ngram is string... Actually, your own example of the query executed in MySQL shell shows that the 2nd and last arguments should be quoted in VALUES(). — marekful, Feb 14 '19 at 15:15

score 0 · Answer 1 · answered Feb 14 '19 at 15:19

0

It's because some fields are string types as shown in your own example VALUES (1,'BUCKET',7,1,'BOUQUET'), the values for n_gram and substitution should be also quoted in the string you pass to MySQL on the command line:

mysql --login-path=local SKU_project -N -e "INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution) VALUES ($freq, '$Ngram', $logicNumber, $S, '$substitution') ON DUPLICATE KEY UPDATE frequency = frequency+$freq;"

answered Feb 14 '19 at 15:19

marekful

14,986
6
37
59

thanks. changed that, but still breaks... the error is slightly different now... ''','') instead of ',') – Mike Feb 14 '19 at 15:30
In the Bash script, print out (echo) the command line before executing it and add that into your question. – marekful Feb 14 '19 at 15:32
There are still a few problems in the VALUES clauses. Here `VALUES (1,'PACER ',,'','')`, you have an omitted value (,,). That should be `,'',`. Here `VALUES (15,'JB',S,'S','JB WELD')`, S is not quoted, it should be `'JB','S','S'`. – marekful Mar 19 '19 at 12:45

score 0 · Answer 2 · answered Feb 16 '19 at 07:52

The error is visible in the queries you generate:

INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution)
       VALUES (1,'PACER ',,'','') ON DUPLICATE KEY UPDATE frequency = frequency+1;
INSERT INTO wordfreqs (frequency, n_gram, logic_number, s, substitution)
       VALUES (15,'JB',S,'S','JB WELD') ON DUPLICATE KEY UPDATE frequency = frequency+15;

The field logic_number stores a number (INT(11)) but you put a string in the query in the VALUES list. Also, in the first query the value is missing.

Check the code that sets $logicNumber; it seems it uses the wrong value.

If you are sure that $logicNumber contains the correct value, you can use $((logicNumber)) to produce a number from it (even when its value is the empty string).

If the value of $logicNumber is not a number then $((logicNumber)) is 0. But if $logicNumber is a string that starts with a number then the evaluation of $((logicNumber)) produces an error. Take that into account when you compute the value of $logicNumber.

Alternatively you can put the value of $logicNumber into apostrophes into the query and MySQL will take care of the conversion to number.

MySQL query breaks in linux shell script, but query runs in mysql command line

2 Answers2