SSH console login working but SFTP does not, why?

Question

I have a table named history with fields:

id, accessionNumber, date_borrowed, date_returned, fine

I intend to update the table's row in the following manner when the query is run:

1- insert today's date into return_date

2- subtract the borrow_date from return_date(=today's date), multiply by a constant that I am going to send as a parameter and insert the result into fine

I have been looking around for hours now, but as I'm a beginner I couldn't get much out of it.

This is my query that doesn't work, what am I doing wrong?

UPDATE history
SET date_returned = CURDATE() ,
fine=DATEDIFF(CURDATE(),STR_TO_DATE(history.date_borrowed, '%m/%d/%Y')) * @some_constant
WHERE id = @id and accessionNumber=@accessionNumber

I am using MySQL Workbench 6 and C# if that's is going to help.

The error message I am getting is :

Incorrect datetime value: '2013-12-11' for function str_to_date

Thanks in advance for any help.

Solution 1

To access your sftp from other hosts, please make sure following is installed and configured properly.

• Installed OpenSSH servers
• Configured sshd_config
  • PubkeyAuthentication yes
  • Subsystem sftp internal-sftp

• Added your public key to ~/.ssh/authorized_keys

• Start the ssh server with port 22/TCP open # /etc/init.d/sshd start

• # iptables -I INPUT -j ACCEPT -p tcp --dport 22

Finally, test $ sftp <login>@<hostname>

Solution 2

In my case, the user had zsh at the top of his .bashrc file, so that he could drop into zsh shell instead of bash.

bash was his default shell. Removing this solved the issue. I then chsh user -s /bin/zsh for the user to retain zsh as his default shell.

Solution 3

Do you have any text going to the console (e.g. echo statements) in any of your .profile files such as .bashrc? This can mess with sftp connectivity. See my answer to a similar question on serverfault

Solution 4

Your anti-virus software can also cause this. We faced it recently. ssh via PuTTY was working fine, but WinSCP was not able to connect. Started working once an exception was configured in the Anti-Virus.

View more solutions

Comments

[Gordon Linoff]

What error are you getting?

[hashbrown]

what is the error you are getting?

[Chris Dunaway]

What are the column names? In the beginning of your post you mention fields with names date_returned, and date_borrowed but then later in your post you refer to columns named borrow_date and return_date ! Which is it?

[ehsan0x]

sorry for the typo, the field name is date_borrowed, now corrected.

[Admin]

The response from Filezilla is: "Disconnected: No supported authentication methods available (server sent: publickey) " But I am using the same key that works for my SSH console login (PuTTy on windows btw)

[Admin]

SFTP is NOT FTP over SSH. You don't have to 'allow FTP' anywhere. The fact that Filezilla says "No supported authentication methods available" indicates it's connected to the server fine, and the problem is nothing to do with your firewall. How did you set up Filezilla to use your public key?

[Admin]

Hi and welcome to Unix & Linux. It gets confusing b/c SFTP shares a component of its name with the protocol FTP but beyond that they have nothing to do with each other. SSH is a single protocol that runs on port 22 and it provides the abilities to securely connect in the form of a interactive shell or to transfer files over the same connection. The SSH server on the remote side needs to allow for SFTP connections, so I'd look into the SSH server's logs for insights as to why it's failing in addition to making sure that Filezilla has an appropriate key to connect to the server securely.

[Admin]

I'd also take a look and verifying SFTP is setup properly from the server itself, yes you can use the command line sftp tool on the server to connect, which is helpful in verifying things before bringing Filezilla into the mix. digitalocean.com/community/tutorials/….

[ehsan0x]

the field IS of type date, and thanks a lot it is working now.

[OMG-1]

Another good idea is to check for interactive session using [[ $- != i ]] && return.

[crackpotHouseplant]

Subsystem sftp internal-sftp did it for me. It was set to /usr/libexec/openssh/sftp-server by default, which didn't even exist

[Paul]

Same problem with other shells (such as fish).

[Paul]

Be careful with this indeed. If you auto launch other console such as fish this will result in similar issues.

[Corgalore]

Adding Subsystem sftp internal-sftp to sshd_config also worked for me with no other changes on Centos.

[Will]

For the sake of simplicity: in my case, a digitalocean droplet with password login, I only needed to enable OpenSSH: "sudo ufw allow OpenSSH" and then "sudo ufw enable", but I suppose that the second shell-command is redundant. By doing so connecting via sftp with FileZille succeeded. In details this is explained here: digitalocean.com/community/tutorials/…

[user5359531]

"Configured sshd_config" ; you are referring to the file /etc/ssh/sshd_config?

[user5359531]

does this include the standard linux ssh "splash screen" text when you log in? $ ssh [email protected] ; Welcome to Ubuntu 20.04.2 LTS (GNU/Linux 5.8.0-48-generic x86_64)