Problems with MySQL 5.0.x with SQL Statements in Config file
------------------------------------------------------------
Key: FTPSERVER-105
URL: https://issues.apache.org/jira/browse/FTPSERVER-105
Project: FtpServer
Issue Type: Improvement
Components: Core
Environment: Windows XP
Reporter: Christoph Rueger
Hi,
Last week I have checked out the trunk from SVN and made a build using maven.
That worked so far.
I configured the Usermanagement for MySQL and here I have found some problems.
I use mySQL 5.0.24a and my Server runs in sql_mode='' which actually means
Traditional MySQL Mode.
[ INFO] 2007-08-10 17:12:26,079 ------- Apache FTP Server started ------
[ INFO] 2007-08-10 17:24:05,251 Database connection closed.
[ INFO] 2007-08-10 17:24:05,986 Database connection opened.
[ INFO] 2007-08-10 17:24:06,126 SELECT uid, userpassword, homedirectory,
enableflag, writepermission, idletime, uploadrate, downloadrate FROM FTP_USER
WHERE uid = 'admin'
[ INFO] 2007-08-10 17:24:06,204 Creating user : admin
[ INFO] 2007-08-10 17:24:06,220 SELECT uid, userpassword, homedirectory,
enableflag, writepermission, idletime, uploadrate, downloadrate FROM FTP_USER
WHERE uid = 'admin'
[ INFO] 2007-08-10 17:24:06,220 INSERT INTO FTP_USER (uid, userpassword,
homedirectory, enableflag, writepermission, idletime, uploadrate, downloadrate)
VALUES ('admin', 'admin', './res/home', 'true', 'true', 0, 0, 0)
[ERROR] 2007-08-10 17:24:06,345 DbUserManager.save()
java.sql.SQLException: Incorrect integer value: 'true' for column 'enableflag'
at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1343)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1260)
at
org.apache.ftpserver.usermanager.DbUserManager.save(DbUserManager.java:427)
at
org.apache.ftpserver.ConfigurableFtpServerContext.createDefaultUsers(ConfigurableFtpServerContext.java:219)
at
org.apache.ftpserver.ConfigurableFtpServerContext.<init>(ConfigurableFtpServerContext.java:105)
at org.apache.ftpserver.gui.RootPanel.startServer(RootPanel.java:351)
at
org.apache.ftpserver.gui.RootPanel$4.actionPerformed(RootPanel.java:229)
at
javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
at
javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
at
javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
at
javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
at
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:6038)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3260)
at java.awt.Component.processEvent(Component.java:5803)
at java.awt.Container.processEvent(Container.java:2058)
at java.awt.Component.dispatchEventImpl(Component.java:4410)
at java.awt.Container.dispatchEventImpl(Container.java:2116)
at java.awt.Component.dispatchEvent(Component.java:4240)
at
java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
at java.awt.Container.dispatchEventImpl(Container.java:2102)
at java.awt.Window.dispatchEventImpl(Window.java:2429)
at java.awt.Component.dispatchEvent(Component.java:4240)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
at
java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at
java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
The problems began in
org.apache.ftpserver.ConfigurableFtpServerContext#createDefaultUsers()
when the Default Users are created.
In org.apache.ftpserver.usermanager.DbUserManager#save(User user) (line 383)
you do a
map.put( ATTR_ENABLE, String.valueOf(user.getEnabled()) );
The problem is the Stringvalue of "true" or "false" which was converted to 0 or
1 in older mySQL Versions. Appearantly in newer versions, this autoconversion
doesn't work maybe.
I googled around and I have found a MySQL Bugentry which could be related:
http://bugs.mysql.com/bug.php?id=18551
The CREATE_TABLE Statement I used was:
DROP TABLE IF EXISTS `ftpserver`.`ftp_user`;
CREATE TABLE FTP_USER (
uid VARCHAR(64) NOT NULL PRIMARY KEY,
userpassword VARCHAR(64),
homedirectory VARCHAR(128) NOT NULL,
enableflag BOOLEAN DEFAULT TRUE,
writepermission BOOLEAN DEFAULT FALSE,
idletime INT DEFAULT 0,
uploadrate INT DEFAULT 0,
downloadrate INT DEFAULT 0,
maxloginnumber INT DEFAULT 0,
maxloginperip INT DEFAULT 0
);
Now solution which worked for me:
I modified the SQL Statements for Insert and Update in the ftpd.properties to
the following:
config.user-manager.sql-user-insert=INSERT IGNORE INTO FTP_USER (uid,
userpassword, homedirectory, enableflag, writepermission, idletime, uploadrate,
downloadrate) VALUES ('{uid}', '{userpassword}', '{homedirectory}', '1',
'{writepermission}', {idletime}, {uploadrate}, {downloadrate})
config.user-manager.sql-user-update=UPDATE IGNORE FTP_USER SET
userpassword\='{userpassword}',homedirectory\='{homedirectory}',enableflag\='{enableflag}',writepermission\='{writepermission}',idletime\={idletime},uploadrate\={uploadrate},downloadrate\={downloadrate}
WHERE uid\='{uid}'
Note the IGNORE command after INSERT and UPDATE.
That did the trick for me to avoid the SQLException.
Another thing which is also an incomplete config file is the following
stacktrace:
[ INFO] 2007-08-10 18:15:25,048 SELECT uid, userpassword, homedirectory,
enableflag, writepermission, idletime, uploadrate, downloadrate FROM FTP_USER
WHERE uid = 'anonymous'
[ERROR] 2007-08-10 18:15:25,048 DbUserManager.getUserByName()
java.sql.SQLException: Column 'maxloginnumber' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:970)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:2747)
at
org.apache.ftpserver.usermanager.DbUserManager.getUserByName(DbUserManager.java:479)
at
org.apache.ftpserver.gui.UserManagerPanel.actionPerformed(UserManagerPanel.java:528)
at javax.swing.JComboBox.fireActionEvent(JComboBox.java:1242)
at javax.swing.JComboBox.setSelectedItem(JComboBox.java:569)
at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:605)
The reason was that the columns
maxloginnumber
maxloginperip
where missing in the SELECT statement in ftpd.properties
I just changed it to
SELECT * FROM FTP_USER WHERE uid \= '{uid}'
instead of every single column.
After these two things I got it working.
I hope you can include these fixes into the source code. I would also like to
contribute these things, but I couldnt find the ftpd.properties in the
sourcecode which I got from SVN. Maybe I am missing something.
I would appreciate some hints.
Thanks
Christoph
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
