SQL Syntax Error from bash script

Question

I have a script that essentially creates Devices, graphs, and trees as well as attempts to create a user for Cacti graphing software.

I receive the below SQL error when attempting to run the shell script. However copying and pasting the exact same input statement into MySQL directly it accepts the syntax.

ERROR 1064 (42000) at line 3: 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 ''TestName','cf199661b212c55286cf81d9602ce63','0', 'TestFullName','on','on','' at line 1

The below script is how this is implemented

php add_device.php --description=$1 --ip=$2 --community=community --template=5 --ping_method=icmp
mysql -u root cacti -ppassword -e "select id from host where description='$1'" | grep -v id > tempID
php add_tree.php --type=node --node-type=host --tree-id=3 --parent-node=34 --host-group-style=2 --host-id=`cat tempID`
php add_graphs.php --host-id=`cat tempID` --graph-type=ds --graph-template-id=2 --snmp-query-id=1 --snmp-query-type-id=14 --snmp-field=ifOperStatus --snmp-value=Up
mysql -u root cacti -ppassword -e "select MAX(id) FROM user_auth" | grep -v id > tempUserID
Number=`cat tempUserID`
User_IDD= $(($Number + 1))
Host_ID=`cat tempID`


mysql -uroot -ppassword cacti << EOF



INSERT INTO user_auth (id,username,password,realm,full_name,must_change_password,show_tree,show_list,show_preview,graph_settings,login_opts,policy_graphs,policy_trees,policy_hosts,policy_graph_templates,enabled) V
ALUES($User_IDD,'$3','cf199661b212c55286cf81d9602ce630',0,'$4','on','on','on','on','on',1,2,2,2,2,'on');
INSERT INTO user_auth_perms (user_id,item_id,type) VALUES ($User_IDD,$Host_ID,3);
INSERT INTO user_auth_realm (realm_id,user_id) VALUES (7,$User_IDD);

EOF

rm TempUserID
rm TempID
~

The exact SQL insert statements pasted directly into the command line are as follows as an example that functions

INSERT INTO user_auth 
        (id, username, password, realm, full_name, must_change_password, show_tree, show_list, show_preview, graph_settings, login_opts, policy_graphs, policy_trees, policy_hosts, policy_graph_templates, enabled) 
VALUES  
(       38, 
        'Test', 
        'cf199661b212c55286cf81d9602ce63',
        '0',
        'TestUser',
        'on',
        'on',
        'on',
        'on',
        'on',
        1,
        2,
        2,
        2,
        2,
        'on'
);

INSERT INTO user_auth_perms 
        (user_id, item_id, type) 
VALUES  (38, 285, 3);

INSERT INTO user_auth_realm 
        (realm_id, user_id) 
VALUES  (7, 38);

I've been bashing my head against this for a few days now and I can't quite find what's wrong in my syntax, does anyone see anything outright wrong?

your posted error and code doesn't match. Show the actual code which throws the error. — Rahul, Jul 11 '14 at 19:32

score 1 · Answer 1 · answered Jul 11 '14 at 20:02

It sounds like what's happening is that you're "running" the script in your head, finding that it works fine, and then getting stuck when the computer doesn't agree.

When debugging, you shouldn't run the script for the computer: don't copy-paste the SQL and then replace the variables with what you think they ought to be.

Instead, let the computer run the script: have it output the SQL and then observe if that matches what you expect.

You can do this by replacing mysql -uroot -ppassword cacti with cat, which will make the script spit out the result instead of executing it. You'll see it's something like this:

INSERT INTO user_auth (id,username,password,realm,full_name, [snip]) 
VALUES(,'baz','cf199661b212c55286cf81d9602ce630',0,'cow','on','on',[snip]);

And now the problem is more obvious: VALUES(,'baz' is missing its first value.

To solve that problem, you can pay careful attention to unexpected error messages. You'll see that one you're getting is something like:

42: command not found

You should not ignore these kinds of messages. When you copy-paste output to stackoverflow, you should include all output (if it's too long, you should reduce the size of your script and input data, rather than redacting information you don't think is relevant).

You'll find that the error occurs on this line:

User_IDD= $(($Number + 1))

And why is it saying "command not found" instead of assigning to the variable? Because of the space after the =. Assignments in bash can not have spaces around the assignment operator.

Remove the space and try again.

I'm not saying that's sufficient to solve all the problems with your script, but it's a good start.

PS: ShellCheck would have automatically pointed out the bad space.

SQL Syntax Error from bash script

1 Answers1