ERROR 1064 (42000) at line 1 – Passing MySQL Create User Arguments via SSH

I was recently building a script to help move things from one server to another and in the process stumbled into a very annoying problem when passing MySQL arguments to a remote server via SSH.

In practice, this should be extremely simple. If you do this on the server, you could execute a command like this:

mysql --user="root" --password="[pass]" --execute="create user 'testing'@'localhost' identified by '123';"
						

You would now have a new user in your database called testing and its password would be 123.

I would logically assume I could pass this to the same server remotely from another server. I would assume you could do something like this:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="create user 'testing'@'localhost' identified by '123';"'
						

Especially because commands like this will work no problem:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="show databases;"'
						

Instead, you will probably get something like this:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for 
the right syntax to use near '123' at line 1
						

Turns out, if you need to escape the double-quotes when using the execute, but it seems only when you're passing other arguments across.

Mind you, I actually tried escaping the characters as well, but I was doing them all, not just the double quotes. I had done:

Example 1:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="CREATE USER 'foo2'@'localhost' IDENTIFIED BY 'mariadb';"'
						

Example 2:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="CREATE USER 'foo2'@'localhost' IDENTIFIED BY 'mariadb';"'
						

I'm not 100% on this one, but it's the only thing that makes sense.

So to get it working, it actually has to be like this:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" 
--execute=\"create user 'testing'@'localhost' identified by '123';\"'
						

You will notice I added the \ at the opening double-quotes and the end. And make note that it doesn't work with single quotes, must be the double-quotes escaped. I am guessing it has to do with how the other parameters are being passed.

Now if you run something like this:

ssh root@[ip address] 'mysql --user="root" --password="[pass]" --execute="SELECT * FROM mysql.user;"'
						

You should see testing is now created.

Posted in   Troubleshooting   MySQL     by noc_team