FTP tables out of sync

[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