FTP tables out of sync

[AdrianB]

Hi all,

I'm using Sentora for a while.

Few days ago I've notice that the new users created for FTP are not able to login.
Trying to check what is wrong I've notice that the user/pass is incorrect.
But the real problem is that the ProFTP is using sentora_proftpd database with table ftpuser table.
Checking the x_ftpaccounts table I've notice that there the new user is inserted but in the sentora_proftpd table is not.

Truncate the table sentora_proftpd and create a new user still not able to see the user in this table.

What is changed in Sentora? What I can do to gain access to the FTP, regarding to change the proftp config file to use x_ftpaccounts table.

Kind regards,
Adrian

[Me.B]

Are you using it on debian? We don't support debian 8 as we didn't test it.

[AdrianB]

Are you using it on debian? We don't support debian 8 as we didn't test it.

Hi Me.B,

No, I'm using Ubuntu server:

Welcome to Ubuntu 16.04.2 LTS (GNU/Linux 4.4.0-75-generic x86_64)

So is not about Debian.

Kind regards,
Adrian

---

Also I've tried to to switch from PHP5.6 to PHP7 and vice-versa. The accounts are created in x_ftpaccounts but not in ftpuser.

So, I'm out of ideas.

[iraqiboy90]

i'm suddenly having this problem.
Creating new ftp user adds the account on
sentora_core -> x_ftpaccounts
sentora_proftpd -> ftpquotalimits

but nothing in
sentora_proftpd -> ftpuser

[iraqiboy90]

Ok. After extensive search, turning on verbose DB query logging, and testing,  I found the issue.
This starts happening at MariaDB 10.2.4+ or whether you have the STRICT variable enabled.

So, the issue is with the SQL_Mode of

Code:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

changing it to this is enough to fix this issue, you dont need it empty I guess?

Code:

NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

The issue is happening specifically with the way the FTP module has its code written. An old way is used for the INSERT query on the /etc/sentora/panel/modules/ftp_management/code/proftpd.php or proftpd-mysql.php whichever is responsible for adding stuff into the database.

Code:

INSERT INTO ftpuser (id, userid, passwd, homedir, shell, count, accessed, modified) VALUES ('', :username, :password, :homedir, '/sbin/nologin', 0, '', '');");

Column 1, 7 and 8 cannot be empty if STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO is enabled.

STRICT_TRANS_TABLES

Strict mode. Statements with invalid or missing data are aborted and rolled back, except that for non-transactional storage engines and statements affecting multiple rows where the invalid or missing data is not the first row, MariaDB will convert the invalid value to the closest valid value, or, if a value is missing, insert the column default value. Default since MariaDB 10.2.4.

Not sure why It's NOT inserting the column default value, when it's already there in the DB.
Is there an issue in this picture?


To permanently add the solution:

Code:

sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

Add this to your "my.cnf" file and then restart mysql. Double check first how "sql_mode" is written for you. Some people say to use a normal dash, not underscore.

Sources of my solution:
https://mariadb.com/kb/en/sql-mode/
https://stackoverflow.com/questions/2536...l/31046983