1

In continuation to my previous question here (Fully details: connect Raspberry Pi 4 to a remote MySql database located on a server), I have been digging to find out what causing the error and why my Raspberry Pi 4 fails to connect to a remote MySql database through JavaScript but, it makes a perfect connection through Python script.

I executed the following shelljs.exec() command to know the cause (inspired from: how to run node shelljs in sync mode and get stdout and stderr) and I got the following output. I need help to know what this error mean and I need to do fix it. My code and output:

>> var shell = require('shelljs');
>> const { stdout, stderr, code } = shell.exec(command, { silent: true })
>> console.log("Stdout: "+stdout+", Stderr: "+stderr+", Code: "+code)
# Output is 
>> Stdout: , Stderr: /bin/sh: 1: mysql: not found, Code: 127

My understanding from above is, there is no output. But there is an error message called /bin/sh: 1: mysql: not found and error code is 127. I appreciate your help to understand what this error mean and what I need to do? Does it telling that my Raspberry Pi 4 is missing something, so it fails to connect to the remote MySql through JavaScript?

Update:

  1. Based on @Mark Setchell suggestion below: I have installed the MySql on the RPi by referring https://pimylifeup.com/raspberry-pi-mysql/ The code is sudo apt install mariadb-server

enter image description here

  1. @Mark Setchell suggestions: Here is the terminal response

pi@raspberrypi:~ $ /usr/bin/mysql -u fieldArduinoYUN -h aa.bb.cc.dd -p abcdf -e "use field_data; SELECT product FROM product_list WHERE product_id = 123;"
Enter password:    
ERROR 1044 (42000): Access denied for user 'fieldArduinoYUN'@'%' to database 'abcdf'
pi@raspberrypi:~ $
Mainland
  • 4,110
  • 3
  • 25
  • 56
  • 1
    It means you are running a `bash` script which you don't appear to have shown us and it doesn't have its PATH set correctly so it can't find the `mysql` program you tried to run. – Mark Setchell Jan 07 '23 at 01:29
  • Hi @MarkSetchell I appreciate for helping me. As I mentioned in my above, here is the complete script at another question: https://stackoverflow.com/questions/74994968/connect-raspberry-pi-4-to-a-remote-mysql-database-located-on-a-server. Can you please explain in detail what is the PATH here that I did not set correctly? Thanks – Mainland Jan 08 '23 at 04:11
  • You did `shell.exec(command)` but we can't see `command`. – Mark Setchell Jan 08 '23 at 07:29
  • @MarkSetchell The command is just above this `shell.exec(command)`. That is, `var command = 'mysql -u '+this.user+' -h '+this.host+' -p'+this.password+' -e "'+sql+'"';` This translates to the command `"command = 'mysql -u this.user -h this.host -p this.password -e sql """` – Mainland Jan 08 '23 at 16:04
  • Ok, in your Terminal, type `which mysql` and it will tell you where your `mysql` command is installed. Use that full path in your code in place of `mysql` – Mark Setchell Jan 08 '23 at 16:06
  • @MarkSetchell I just updated my question with a screenshot. I just got an empty response both at the root and home directory. – Mainland Jan 08 '23 at 16:14
  • Have you installed `mysql` client? If so, how? – Mark Setchell Jan 08 '23 at 16:19
  • @MarkSetchell I am just new to this. What I understand from your question is, do I have to install a MySql client on the `Raspberry Pi 4`? Then only I can connect this `RPi 4` with a `remote MySql` database located on a different server? I am checking my notes (I am noting down all the modules that I installed) and I don't think I installed `MySql client`. But question is, how come same `RPi 4` makes successful connection with `remote MySql` when queried through `python script`. – Mainland Jan 08 '23 at 16:21
  • @MarkSetchell After doing some search here, some answers pointed towards which shell is being used. Don't know If I worded it correctly here. They suggested this `ls -l \`which sh\`` and I got the following response: `lrwxrwxrwx 1 root root 4 Jan 6 16:25 /usr/bin/sh -> dash`. – Mainland Jan 08 '23 at 16:33
  • Just because Python has a library that can talk to MySQL on another server that doesn't mean there is a program called `mysql` that you can run from a Terminal or call from a `bash` script. So you will need to install the client too. – Mark Setchell Jan 08 '23 at 17:01
  • If you want to run `mysql ...` in a `bash/dash` shell from Javascript, you must first be able to run that same command from your Terminal. – Mark Setchell Jan 08 '23 at 17:16
  • @MarkSetchell I have update my question with screenshot. Now I have installed `mysql` and I ran `which mysql` and got the response `/usr/bin/mysql`. Now I ran the `javascript` again, but getting same error. Also, I did not quite understand your comment on running `mysql ..` in a `bash/dash`. Can you please elaborate? Thanks – Mainland Jan 08 '23 at 19:36
  • You need to be able to run a simple command starting `/usr/bin/mysql -u ... -p ... -h ... -e SOMETHINGSIMPLE` in your Terminal. When that works, replace `mysql` in your `command` with `/usr/bin/mysql` – Mark Setchell Jan 08 '23 at 19:40
  • Write the password **directly** after `-p` without a space between. – Mark Setchell Jan 08 '23 at 20:48
  • @MarkSetchell I really appreciate your suggestion. This time I didn't get a `null` error but something new. As you suggested, I ran the command in the terminal. and got the provided terminal response to the above question. Please have a look. – Mainland Jan 08 '23 at 20:54
  • I want to share here some information here that might help why I am getting `no Access` error. This `RPi 4` is sitting in my office and there is another `Arduino YUN` in our office building elsewhere but connected to the same network. This `Arduino YUN` is also querying the same `MySql` with same credentials as in the above I have used for `RPi`. So, two devices (`Arduino YUN` and `Raspberry PI 4`) querying the remote MySql with same credentials (same user name and password) is allowed? or Do I have to create a new user and password for this `RPi`? Thanks – Mainland Jan 08 '23 at 20:55
  • @MarkSetchell Hip Hip Hurray! Worked. Fantastic. Yes, I just removed the space between `-p` and password. This worked like fantastic. You are genious. I don't know how to thank you. I have been trying to get this work for past one week. You are truely genious but I have to command your patience. Really hatsoff. Thank you very much. – Mainland Jan 08 '23 at 20:57
  • 1
    Cool Good luck with your project! – Mark Setchell Jan 08 '23 at 20:59
  • @MarkSetchell Can you please write the answer and I will accept it. Because, this is important question and not many people know and there are no resources. All my google search for past week only yielded results on how to make/access/install MySql on Raspberry pi but not on how to connect a Raspberry Pi with a remote MySql. – Mainland Jan 08 '23 at 21:02
  • @MarkSetchell Also, I am curious, deleting space between `-p` and password is it a standard or only for the Raspberry pi? I am puzzled how this small difference is making the big difference. – Mainland Jan 08 '23 at 21:03
  • Bedtime here I'll do it tomorrow. – Mark Setchell Jan 08 '23 at 21:04
  • No issues. I really appreciate for sparing and spending time, during those late hours at your end, to help me solve the problem. I look forward to your answer. Thanks again. – Mainland Jan 08 '23 at 21:12

1 Answers1

1

Summarising the exchanges from the comments, there were the following issues and solutions.

The following error means that the shell (bash/dash/ash etc) was unable to find the mysql command:

/bin/sh: 1: mysql: not found

This normally results from the PATH variable not being set to tell the shell where mysql is located in the filesystem.

It transpired that, in this case, the reason was that the mysql client was not installed. OP had thought that installing a Python module would provide a command-line MySQL client too.

The solution was to install the MYSQL client tools.

The second issue was that the password for the mysql client tool wasn't being provided correctly which resulted in:

ERROR 1044 (42000): Access denied

This can be solved by providing the password immediately following the -p option and without a space like this:

/usr/bin/mysql -u USER -h aa.bb.cc.dd -pPASSWORD -e "SOMECOMMAND"
Mark Setchell
  • 191,897
  • 31
  • 273
  • 432
  • 1
    This is a gem of a solution for the week-long effort I had put into solving this issue. I appreciate your patience and excellent solution. I cannot thank you enough. – Mainland Jan 10 '23 at 17:26