CODEWITHSUNDEEP
mysqlterminal
John
John

Reputation: 1751

Terminal INSERT into MySQL Not Working

I im creating mysql script that creates database, tables and insert data into created table...but i need to do it using ubuntu terminal..i successfully created database and tables but my INSERT sql statement does not insert nothing into table...

echo "CREATE DATABASE "mydatabase"" | mysql -uroot -pPASSWORD; \
echo "DROP TABLE IF EXISTS "servers";" | mysql -uroot -pPASSWORD mydatabase; \   
echo "CREATE TABLE "servers" ( \
    "id" int(2) NOT NULL AUTO_INCREMENT, \
    "server" varchar(64) NOT NULL, \
    "ip" int(10) unsigned NOT NULL, \
    "panel" int(4) NOT NULL, \
    PRIMARY KEY ("id") \
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" | mysql -uroot -pPASSWORD mydatabase; \
    echo "LOCK TABLES "servers" WRITE;" | mysql -uroot -pPASSWORD mydatabase; \

This code above works and creates database and tables and next command does not insert into table from terminal and i could not find any errors...so why is not inserting into table?

echo "INSERT INTO servers (id, server, ip, panel) VALUES (1, \''+rows[0].server+'\', \'INET_ATON('+rows[0].ip+')\', '+rows[0].panel+');" | mysql -uroot -pPASSWORD mydatabase; \
echo "UNLOCK TABLES;" | mysql -uroot -pPASSWORD mydatabase;

Upvotes: 1

Views: 1917

Answers (1)

Julien Palard
Julien Palard

Reputation: 11526

Your should try your echo "INSERT…" without the | mysql to see what it outputs.

Typically the one you wrote in your question:

echo "INSERT INTO servers (id, server, ip, panel) VALUES (1, \''+rows[0].server+'\', \'INET_ATON('+rows[0].ip+')\', '+rows[0].panel+');" | mysql -uroot -pPASSWORD mydatabase; \

without its | mysql outputs:

INSERT INTO servers (id, server, ip, panel) VALUES (1, \''+rows[0].server+'\', \'INET_ATON('+rows[0].ip+')\', '+rows[0].panel+');

You can now try to evaluate this in your brain or copy and paste it to a MySQL to see what happend. I am personally now able to interpret it in my brain, it yields "Syntax error near '\''+rows[0].server+'\''".

I don't know where your rows come from, but rows[0].ip in bash is not a legal syntax, are you mixing languages? It won't work.

Side note, you should use here documents instead of lots of backslash, it will be more readable:

cat <<EOF | mysql …
   CREATE TABLE "servers" (
    "id" int(2) NOT NULL AUTO_INCREMENT,
    "server" varchar(64) NOT NULL,
    "ip" int(10) unsigned NOT NULL,
    "panel" int(4) NOT NULL,
    PRIMARY KEY ("id")
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
EOF

Upvotes: 1

Related Questions