This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm whether you accept or reject these cookies being set.

A cookie will be stored in your browser regardless of choice to prevent you being asked this question again. You will be able to change your cookie settings at any time using the link in the footer.

FTP tables out of sync
#5
RE: FTP tables out of sync
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.

Quote: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?
[Image: 9423d138645cc3f81cde04b5f7f07011.png]

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
Reply
Thanks given by: TGates


Messages In This Thread
FTP tables out of sync - by AdrianB - 05-16-2017, 04:06 AM
RE: FTP tables out of sync - by Me.B - 05-17-2017, 06:13 PM
RE: FTP tables out of sync - by AdrianB - 05-17-2017, 07:20 PM
RE: FTP tables out of sync - by iraqiboy90 - 03-21-2020, 01:21 AM
RE: FTP tables out of sync - by iraqiboy90 - 03-21-2020, 02:27 AM

Forum Jump:


Users browsing this thread: 1 Guest(s)