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.

Reply via email to