[Sites/DB Module] How to add table column

Discussion in 'Developers' Forum' started by MaddinXx, Jul 29, 2013.

  1. MaddinXx

    MaddinXx Member HowtoForge Supporter

    Hi there

    I'm currently trying to integrate MongoDB management into ISPConfig (so you can choose MongoDB in DB dropdown).

    I've already changed the needed interfaces files (add to dropdown, show server type in list) etc. but...

    Now we/I need an additional column in the ISPConfig DB which stores the DB type (mysql, mongo, or others if support will come somewhen).

    How should this be done? I think this will be a great feature for ISPConfig in general so I don't want it to use it's own table/plugin style - but try to integrate it as good as MySQL.

    And are there other things I might have missed? Of course perfect integration will require configuration options in server settings etc. but this might be something for someone with better skills as soon as I've finalized the base (MongoDB commands etc.).

  2. till

    till Super Moderator Staff Member ISPConfig Developer

    A good name for this column will be database_type in my opinion. There is a shrt readme file in install/sql folder that describes how changes in the database scheme have to be added.
  3. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    There is already a "type" column in the web_database table, that currently contains 'mysql' for all databases.
  4. MaddinXx

    MaddinXx Member HowtoForge Supporter

    Quite stupid...I checked it...but may have overseen it... but makes things easier :) thanks!
  5. MaddinXx

    MaddinXx Member HowtoForge Supporter

    Hi there

    So I made quite some progress:

    - MongoDB can be chosen from Dropdown when creating a new DB
    - The DB type is shown in DB list
    - You can enable or disable monitoring MongoDB service in server config
    - MongoDB log is available from monitor
    - Backups are created
    - Backups appear in list
    - Backups can be restored

    Now I'm going to implement the plugin logic (CRUD DBs/users) but may need someone to review/help with some things:

    - Even when monitoring MongoDB is set to "yes"... it doesn't do it
    - MongoDB doesn't appear in the list when clicking Monitor -> Services
    - The web_backup table ENUM (web, mysql) needs another one: mongodb
    - Bring changes to install/update scripts
    - some more things I've forgotten...?

    Nothing is seriously tested yet (as I first need the plugin logic) but I would appreciate if I could get some help from someone with deeper knowledge for the points listed above.

    Since it may still take a while to become stable enough and I don't want to destroy SVN version (*laugh*) I'm working on private Gitlab instance for now.

    Is someone willing to assist me here? Please let me know so I can create Gitlab user and we can get in touch.

    PS: I guess the easiest thing when finished will be to create a .patch or .diff file?

  6. till

    till Super Moderator Staff Member ISPConfig Developer

    If you have any specific questions, please post them here and I will try to answer them.

    Use debug loglevel to debug the actions of your server plugin.

    Have you added it in the file that displays the services in the monitor interface module?

    see readme file in install/sql folder.

    Yes, please submit it as patch file.
  7. MaddinXx

    MaddinXx Member HowtoForge Supporter

    Hi till

    So I already fixed some of the problems I had, while the following keep remaining:

    1. Users get deleted
    When you have a DB with a user + read-only user and switch them (or remove user and make the read-only user the user) both are deleted -> no more users exist in DB.

    What could be the problem for that? Please find the code I use here: http://drops.frontender.ch/4cQM/3CbJc6p7

    2. Switching MongoDB <-> MySQL
    What should we do here?

    3. Renaming DB doesn't work
    There seems to be a bug in MongoDB which prevents renaming a DB (unauthorized error). You can find several reports about that and a nasty workaround (really nasty). Should be implement it or wait until it's fixed and do ... ?

    4. Monitor -> Services
    I just don't find it... but I leave it for now. This might be small work for you afterwards :)

    5. User password
    The query which fetches the user from MySQL DB returns it's encrypted password. MongoDB uses some kind of MD5 (through this doesn't help) -> how can I get the plain password for the user?

    6. Hard-coded credentials
    MongoDB root user credentials are currently hard-coded in cron_daily, the plugin itself and the backup plugin. We may need to ask them during install process and create a "credentials" file like the one for MySQL. I'd appreciate it if I could leave this as well.

    That's it for the moment :) Thanks!
  8. Croydon

    Croydon ISPConfig Developer ISPConfig Developer

    Hi MaddinXx,

    @2. this should not be possible at all

    @3. this doesn't work with mysql either as the RENAME DATABASE command is buggy in mysql

    @5. the plain password is not (and must not be) stored anywhere so you won't be able to reuse it
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    1) I recommend that you add some log lines with LOGLEVEL_DEBUG so you can see in debug mode in detail which code gets executed and also output relevant variables in the debug lines.

    Regarding your code style, in iSPConfig we use all lowercase in variable names, so better use e.g.:


    instead of:


    as we want to keep a clean and consistent coding style in the project.
  10. MaddinXx

    MaddinXx Member HowtoForge Supporter

    My thoughts about the points:

    2. Agree - this needs to be permitted in interface.
    3. OK - I'll leave it non-functional then and as soon as it's fully supported this can be "re"-added
    5. There are two possibilities that come to mind:

    1) add another field in user table and store the MongoDB password there (it uses md5($username.":mongo:".$password)
    2) is the current encryption algo public? E.g. if we could tell the clients: "Hey, use sha1(ISPConfigXY:md5($username.":mongo:".$password)) this would also be an option (not pretty, but hey...which CP supports Mongo hehe).

    The problem is that MongoDB has really limited user support (much better in 2.4 than 2.2 but still limited).

    @till: Jup - will change (quelle ironie après ma sujet :))
  11. till

    till Super Moderator Staff Member ISPConfig Developer

    5) I guess 5.1 will be the easier option. Regarding 5.2, the encryption must be published as mysql is open source software, but I dont know how they encrypt the password in detail. ISPConfig uses the standard linux "crypt-md5" with salt for all passwords except of the mysql password as mysql does not support other encryptions except of their own one as far as I know.

    Thanks :)
  12. MaddinXx

    MaddinXx Member HowtoForge Supporter

    Here we go with the patch: http://drops.frontender.ch/nECh/kaDmJN2U
    Hope this can be used with SVN (as it's from Git -- but I guess a patch is a patch). Sorry for the whitespace changes...will try to get control over my editor next time :/

    Quickly summarized what needs to be done/may need review:

    Not implemented
    - MongoDB <-> MySQL switch (e.g. message in interface (if non-existing yet))
    - change DB name (doesn't do anything)
    - remote access control (not supported by Mongo)
    - charset changing (BSON is always UTF-8)
    - Monitor -> Services (where is it??? :D)
    - upgrade.php (noting done...)

    Needs work
    - I've created the additional field in web_database_user but when only the username changes we would still need the old plain password as the MongoDB password is based on username -> we must update password but don't have the old one...resulting PW will be false. We may need to force to enter a password when changing something in user view....
    - Credentials are hard-coded in plugin, cron_daily and backup plugin

    Should work (Tested)
    - CRUD DB/Users (expect user "Needs work")
    - Backup & Restore
    - Monitoring the service
    - Monitoring log

    Let me know if I can help further with this or you have any questions.


    // edit: patch is based on stable
  13. till

    till Super Moderator Staff Member ISPConfig Developer


    I just chcked the patch file, after some manual changes I was able to read it with the subversion patch editor but all paths of files in the patch file are wrong. e.g. the patch filecontains this path:

    diff --git interface/wesites/lilang/en_database_list.lng interface/wesites/lilang/en_database_list.lng

    while the correct path would be:

    diff --git interface/sites/lang/en_database_list.lng interface/sites/lang/en_database_list.lng

    basically git adds 2 additional chars in front of some folders.

    Please try this patch command:

    git --no-pager diff --no-prefix master..branch > somefile.diff

    source: http://stackoverflow.com/questions/708202/git-format-patch-to-be-svn-compatible

    I noticed that you added the sql changes in a file upd_0051.sql, but we have already sql file upd_0054.sql, so your sql patch file must use the next free number which is upd_0055.sql. Your current sql file overwrites or changes a existing sql patch which might not happen as it will cause system to fail due to missing database columns that install the next update.
  14. MaddinXx

    MaddinXx Member HowtoForge Supporter

  15. till

    till Super Moderator Staff Member ISPConfig Developer

    Thanks for the new patch, it applied fine. I've uploaded it to svn trunk.

    Can you write down which configuration steps the ispconfig installer has to do for mongodb? Or are there no config steps needed?

    And we have to change the hardcoded credentials. I think we should add a file mongo_clientdb.conf in /usr/local/ispconfig/server/lib/ simlar to the one for mysql. It is important that you load the credentials from this file (include the file) right bfore you need them and not at the beginning of a php file and after you dont need the credentials anymore, unset() the php variables that contain the login data similra to the procedure used in the mysql parts.

    I can add the code to the installer then. I just need to know how to reliably determine if mongodb is installed, therefor I need to know the name of the mongodb binary so that I can query it with "which" on the shell.

    Regarding the git repo, its still in the works. I will start a small tools repo so that all core devs can get usedto git. As you use github already, do you know if their issue tracker can be deactivated? We have already one for ispconfig on our own servers which contains many feature requests and the whole bug history for so I dont want to abandon that.
    Last edited: Aug 2, 2013
  16. MaddinXx

    MaddinXx Member HowtoForge Supporter

    Great :)

    I've written down most config steps in the plugin:

     * MongoDB
     * ------------------------------------------------------------------------
     * The following needs to be done before using this plugin:
     * - 1. install MongoDB server from 10gen sources (or another one with >= 2.4)
     * - 2. install php5-dev package (apt-get install php5-dev)
     * - 3. install mongo PECL extension (pecl install mongo)
     * - 4. enable mongo (echo "extension=mongo.so" > /etc/php5/mods-available/mongo.ini && php5enmod mongo)
     * - 5. create administrative user manager in Mongo (mongo -> use admin -> db.addUser({user: "root", pwd: "123456", roles: [ "userAdminAnyDatabase", "readWriteAnyDatabase", "dbAdminAnyDatabase", "clusterAdmin" ]}))
     * - 6. enable auth for Mongo (nano /etc/mongodb.conf -> auth = true)
     * - 7. restart MongoDB (service mongodb restart)
     * Unlike MySQL, MongoDB manages users per database.
     * Therefor we cannot use one user for multiple databases. Instead, we have to add him each time via the admin user.
    while point 6 is missing that setParameter = enableLocalhostAuthBypass=0 should also be added to the config to prevent local users from being root.

    To detect MongoDB you can search for the binary mongod which is the daemon. Config file is located at /etc/mongodb.conf (default).

    Regarding credentials: I'll add the code in the plugin/cron/backup like MySQL and you take care of adding/filling this file then? That's good :) I'm going to implement this directly in SVN this time.

    @Github: Ah great :) Yes, it can be deactivated (see here for example: https://github.com/Rackster/ispconfig3-nginx-reverse-proxy)

    // edit: I've removed hard-coded credentials and used MySQL way: http://drops.frontender.ch/PMrY/bSHqeBlo

    I'm wondering if it wouldn't be smarter to use one single file which contains associative arrays? Especially in files like cron_daily/backup where we need to include every file (who knows which DBs get supported sooner or later). That way there also won't be any problems with using the same variables (just change array "prefix" et voila)
    Last edited: Aug 2, 2013
  17. bluebirdnet

    bluebirdnet New Member

    Hi Maddinx and Till,

    Is there any development on this? How do we install this and can it be used in a production system ? Getting requests for this and want to setup shared hosting for developers.

    If more development is needed I may be able to contribute, please advise.

    When can we expect this to be included and supported in ISPConfig?


    thanks for any help
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    Mongodb is already in our dev branch (see master branch on git,ispconfig.org). It will be released as part of ISPConfig 3.0.6 nextyear, which is the next major version. Before 3.0.6, there will be only one bugfix release ( which is scheduled for end of december.
  19. bluebirdnet

    bluebirdnet New Member

    Thanks for the quick update Till.

    this is great news! So for now my understanding is that this is only available in a development version? How can we try it out?
  20. till

    till Super Moderator Staff Member ISPConfig Developer

Share This Page