Databases

Info: Configure Redmine on cpanel hosting account with sending and receiving emails.

Posted on Updated on

Wiki : http://en.wikipedia.org/wiki/Redmine

Redmine is a free and open source, web-based project management and bug-tracking tool. It includes a calendar and Gantt charts to aid visual representation of projects and their deadlines. It handles multiple projects. Redmine provides integrated project management features, issue tracking, and support for various version control systems.
The design of Redmine is significantly influenced by Trac, a software package with some similar features.
Redmine is written using the Ruby on Rails framework. It is cross-platform and cross-database. It is part of the Bitnami app library that provides an installer and virtual machine for ease of deployment.

Before starting installation you have to make sure that Ruby on rails is working fine in your environment, If not you can follow the installation document for more help.

Installaing Ruby on Rails with Cpanel : https://enlook.wordpress.com/2013/11/19/howto-install-ruby-on-rails-with-cpanel/

Once you have done, then start the redmine installation steps.

Login to the terminal using primary account logins.

#ssh myaccount@mydomain.com

  1. Create rails_app folder and redmine folder within it then go inside that folder
    # mkdir -p ~/rails_apps/redmine/
    # cd ~/rails_apps/redmine/
  2.  Download redmine redmine-2.3.3 or latest stable version, extract it and move the content out of it, then delete the files not being used.
    1. # wget http://files.rubyforge.vm.bytemark.co.uk/redmine/redmine-2.3.3.tar.gz
      # tar -zxvf redmine-2.3.3.tar.gz
      # mv redmine-2.3.3/* ./
      # rm -rf redmine-2.3.3/
  3. Move example files where they can be used
    # cd config
    # mv database.yml.example database.yml
    # mv configuration.yml.example configuration.yml
  4. Creating the MySQL Database/User/Password
    Login to Cpanel account, Create a database , user and grant full privilege to the new user for the particular database.
    cPanelXdatabase
  5. Modifying your database.yml file.
    # vi database.yml
    production:
    adapter: mysql
    database: redmine
    host: localhost
    username: myaccount_databaseuser
    password: newpassowd
    encoding: utf8
  6. Updating the ~/rails_apps/redmine/public/.htaccess file
    # cd ../public/
    # pwd
    1. You should see something similar to this.

    /home/myaccountuser/rails_apps/redmine/public

        Add these lines
    Options -MultiViews
    PassengerResolveSymlinksInDocumentRoot on
    #Set this to whatever environment you'll be running in
    RailsEnv production
    RackBaseURI /
    SetEnv GEM_HOME /home/myaccountuser/rails_apps/redmine/public
    
    # set to resolve avoid rails control to the folder for image resolution   
    RewriteEngine On
    RewriteCond %{REQUEST_URI} ^/images.*
    RewriteRule .* - [L]
  7. Create a subdomain eg: projects.mydomain.com
    Follow cpanel procedure to create subdomain. Subdomains
  8. Remove projects folder inside public_html and create symbolic link.
    # rm -rf ~/public_html/projects
        Creating the symlink
    # ln -s ~/rails_app/redmine/public ~/public_html/projects
  9. Updating Environment variables in ~/.bashrc file
        Add these lines to the bottom of your ~/.bashrc file
               export HPATH=$HOME
               export GEM_HOME=$HPATH/ruby/gems
               export GEM_PATH=$GEM_HOME:/lib64/ruby/gems/1.9.3
               export GEM_CACHE=$GEM_HOME/cache
               export PATH=$PATH:$HPATH/ruby/gems/bin
               export PATH=$PATH:$HPATH/ruby/gems
        after which source your .bashrc file
            # source ~/.bashrc
        You will then need to check your rails version
            rails -v && rake --version && gem -v
          You should get this message

    ruby

    Rails 4.0.1
    rake, version 0.9.2.2
    1.8.23
  10. Running bundle install
    # cd ~/rails_apps/redmine/
    # bundle install
    # rake generate_session_store
  11. Running generate_session_store or generate_secret_token
    1. # rake generate_session_store
        If you get an error saying that command is deprecated, run this command instead;
     # rake generate_secret_token
  12. Start the site session
    # rake db:migrate RAILS_ENV=production
  13. Configuring outgoing emailsUpdate the setting in configuration.yml
    default:
     email_delivery:
     delivery_method: :smtp
     smtp_settings:
     address: localhost
     port: 25
     domain: mydomain.com
     authentication: :none
    enable_starttls_auto: false

    Now the redmine have capable to send emails using exim install in the cpanel server.

  14. Configuring Incomming emails for IMAPCreate a cron job for the script to get continuous email feaching
    cPanelX

    For the first this script must execute from the terminal, so it will display error if any.

    /usr/bin/rake -f /home1/innovat4/rails_apps/redmine/Rakefile --silent redmine:email:receive_imap RAILS_ENV="production" port=143 host=mydomain.com username=projects@mydomain.com password=myemailpassword

    For more help follow the official link http://www.redmine.org/projects/redmine/wiki/RedmineReceivingEmails#Enabling-unknown-users-to-create-issues-by-email

Note : Each configuration required rails environment reboot for that you can follow the simple way.

# touch ~/rails_app/redmine/tmp/reboot.txt

ERROR: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Posted on Updated on

One of our server getting odbc_connection error after migration, this will usually  happens because ODBC module has not enabled on the server,  While I tried to set up ODBC with freeTDS in order to connect to a MSSQL server faced several issues.

MSSQL uses Tabular Data Stream (TDS) as a communication protocol which is same like in Sybase. freeTDS is an implementation of TDS protocol.

Before trying to connect with freetds to the mssql server, make sure, that your MSSQL server has remote access to connect.

[02-May-2013 02:21:31 America/Denver] PHP Warning: odbc_connect() [<a href='function.odbc-connect'>function.odbc-connect</a>]: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /home/anand/public_html/mssql2000.php on line 69

We need below packages installed to get this done.

1, php-odbc
2, unixODBC
3, freetds

Installation : 

#pecl  install php-odbc
#yum install unixODBC-devel unixODBC freetds-devel freetds

Or,

Download unixODBC and untar it

#wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
#tar –xzf unixODBC-2.3.0.tar.gz
#cd unixODBC-2.3.0 ; ./configure ; make ; make install

Installing freeTDS
Set environmental variable at /etc/profile. Add following lines at end

#vi /etc/profile
 # TDS
SYBASE=/usr/local 
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib 
export SYBASE LD_LIBRARY_PATH 
#http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
#tar –xzf freetds*.tar.gz ; cd freetds* ; ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local ; make ; make install

Configuration:
Note: tdsver=8.0 if you use SQL 2000, tdsver=7.0 if you use SQL 7.0, More info about freetds.conf here

root@server [~]# cat /etc/freetds.conf
[MSSQLSERVER]               
host = 11.222.333.44
port = 1433
tds version = 8.0

Setup ODBC:
Create template for both driver and configuration. before creating make sure that the driver files are valid.

root@server [~]# vi tds.driver.template
#Driver for MS SQL
[FreeTDS]                       #"FreeTDS" is the unique name for this driver
Description = FreeTDS driver
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so.1
FileUsage = 1
UsageCount =1

root@server [~]# vi tds.datasource.template
[MSSQL]               #"MSSQL" is the DSN name that we call to connect database
Driver = FreeTDS      # name that we specified in the driver file
Description = MSSQL ReflectiveLearning
Trace = No
TraceFile = /var/log/freetds.log
Servername = MSSQLSERVER    # this  name specified in the freetds.conf
Port = 1433
Database = reflective_Live
TDS_Version = 8.0
 Now install the data source and driver

The below commands will create configuration file for odbc but odbc.ini has create in users home dir by default we have to move it manually to /etc/

odbcinst -i -d -f tds.driver.template
odbcinst -i -s -f tds.datasource.template
cp -rpf ~/.odbc.ini /etc/odbc.ini
mv /etc/odbc.ini /usr/local/etc
mv /etc/odbcinst.ini /usr/local/etc
cd /etc
ln -s /usr/local/etc/odbc.ini
ln -s /usr/local/etc/odbcinst.ini
chmod 0664 /usr/local/etc/odbc*.ini
cd ~

check by using command line
#tsql -H Hostname_OR_IP -p PortNumber -U Username

root@server [~]# isql -v DSNname Username 'Password'
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
That’s all, now the application will works fine.
some PHP code to connect to it
<?php
//*************************************************************************
//Open Database Connection
//************************************************************************* 
$dbserver="ipaddress";
$dbusername="tester4";
$dbpassword="password1234";
$defaultdb="testdb";
$cn = mssql_connect($dbserver,$dbusername,$dbpassword) or die("Connection Error");
$db = mssql_select_db($defaultdb,$cn) or die("Database Error");
echo "Connection Success"
?>

Some of the useful commands,

odbcinst -j         # to check the ODBC version and configuration.
odbcinst -q -d   # View loaded drivers.
odbcinst -q -s   # View DSN entry
tsql -LH 22.33.55.44   #To list MSSQL server instant name,version etc.,
osql -S DSN -U Username -P ‘Password’ #list the configuration files loaded to connect ODBC

Install / Enable PHP MsSQL Extension in cPanel/WHM Server

Posted on

For enabling mssql support in PHP on cpanel/WHM installed server we can’t use easyapache in this case,  we need FreeTDS installed and configured which is a set of Unix/Linux libraries that implement the TDS protocol. First we will need to download and install FreeTDS, you can find more information and download link at http://www.freetds.org URL. Note the exact installation steps below :

Packages :
1. unixODBC
2. freeTDS
3. mssql.so
step :1  unixODBC

yum install  unixODBC unixODBC-devel

Step :2  freeTDS

cd /usr/local/src
wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar zfvx freetds-stable.tgzcd freetds-*;
./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld; make ; make install 
echo "--with-mssql=/usr/local/freetds" >> /var/cpanel/easy/apache/rawopts/all_php5
touch /usr/local/freetds/lib/libtds.a
touch /usr/local/freetds/include/tds.h

Use this line if you get any error like no such file or directory

ln -s /usr/include/sqlext.h /usr/local/include/sqlext.h

If it it is a 64 bit server then make the following softlink

ln -s /usr/local/freetds/lib/  /usr/local/freetds/lib64

Step :3  mssql.so

Now run Easy Apache and make sure that Mysql, Mysql of the system, amd Mysqli are all selected.

/scripts/easyapache

 Now you can check Mysql enabled

root@server [~]# php --info |grep  "mssql"
MSSQL Support => enabled

Error: unable to start mysql 130207 5:33:16 /usr/sbin/mysqld: Can’t open file: ‘sends.MYI’. (errno: 145)

Posted on Updated on

MySQL crashes frequently,  Getting error while starting the service “No mysqld pid file found. Looked for /var/lib/mysql/server.910mall.jp.pid.” .  It is because of  table crash. repair crashed tables using below commands.

Engine : MyISM

# repair the tables
root@server [/var/lib/mysql]# myisamchk -r */*.MYI
- recovering (with sort) MyISAM-table 'DB_wrdp1/wp_post2cat.MYI'
Data records: 1
- Fixing index 1
- Fixing index 2

---------
- recovering (with keycache) MyISAM-table 'DB_wrdp1/wp_postmeta.MYI'
Data records: 0

---------
- recovering (with sort) MyISAM-table 'DB_wrdp1/wp_posts.MYI'
Data records: 2
- Fixing index 1
- Fixing index 2

---------- recovering (with sort) MyISAM-table 'DB_wrdp1/wp_usermeta.MYI'

Data records: 2
- Fixing index 1
- Fixing index 2
- Fixing index 3

---------

# Start Mysql
root@server [/var/lib/mysql]# /etc/init.d/mysql start

Error: MySQL Innodb “Failed to locate MySQL socket. Please check the mysql configuration.”

Posted on Updated on

I recently came across a Cpanel server which mysqld refused to start, Getting error “Failed to locate MySQL socket. Please check the mysql configuration“. In mysql error log  default /var/lib/mysql/HOSTNAME.err it was no surprise to find that the mysql user table had been marked as crashed.

[root@server mysql]# tail -f /var/lib/mysql/HOSTNAME.err
110108 10:37:45  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 1 3749263016.
InnoDB: Doing recovery: scanned up to log sequence number 1 3749263050
InnoDB: Last MySQL binlog file position 0 79, file name ./host89-bin.000005
110108 10:37:45  InnoDB: Flushing modified pages from the buffer pool...
110108 10:37:45  InnoDB: Started; log sequence number 1 1234563550
InnoDB: !!! innodb_force_recovery is set to 1 !!!
110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed and should be repaired
110108 10:37:45  mysqld ended

Now set mysql recovery option as 1 and start MySQL without privilege safe mode

[mysqld]
innodb_force_recovery = 1

 

[root@server ~]#/usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock

Now I can login to the database, its time to repare user tables by following below steps,

[root@server ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 183 to server version: 4.1.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
mysql> check table user;
+------------+-------+----------+----------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                                 |
+------------+-------+----------+----------------------------------------------------------+
| mysql.user | check | warning  | Table is marked as crashed                               |
| mysql.user | check | warning  | 6 clients are using or haven't closed the table properly |
| mysql.user | check | error    | Record at pos: 28492 is not remove-marked                |
| mysql.user | check | error    | record delete-link-chain corrupted                       |
| mysql.user | check | error    | Corrupt                                                  |
+------------+-------+----------+----------------------------------------------------------+
5 rows in set (0.02 sec)

mysql> repair table user;
+------------+--------+----------+------------------------------------------+
| Table      | Op     | Msg_type | Msg_text                                 |
+------------+--------+----------+------------------------------------------+
| mysql.user | repair | warning  | Number of rows changed from 1384 to 1385 |
| mysql.user | repair | status   | OK                                       |
+------------+--------+----------+------------------------------------------+
2 rows in set (0.48 sec)

mysql> check table user;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.user | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.01 sec)

mysql>exit

Now remove the innodb recovery option and start MySQL as normal mode.

[root@server mysql]# service mysql restart

Error: Unable to connect DynamoDB in AWS Eclipse Toolkit

Posted on Updated on

I am using Eclipse to connect AWS Dynamo Db and manage TABLES : by installing aws tool kit. everything will works fine but Dynamo db shows unable to connect, unfortunately here is the solution for this.If you can’t get anything else to work, you can screw with the plugin’s metadata to force an http:// connection and avoid ssl entirely.

Follow the below setps to change the metadata to http://

In your eclipse workspace, open the .metadata directory. Browse to .metadata/.plugins/com.amazonaws.eclipse.core/regions. In there is a file called regions.xml. Edit it, changing the DynamoDB entry from https://... to http://... Then restart eclipse, and you will be connecting to DynamoDB over http:// .

Steps to install AWS tool kit.

1. Install AWS Eclipse tools in a clean Eclipse environment (from Help->Eclipse Marketplace only), with no prior installations of the tools on the test machine. Simply wiping a prior installation might leave working settings, config items behind.
2. Note the DynamoDB (as of this writing) is not included in Help->Eclipse Marketplace distribution.
3. Add in DynamoDB from Help->Install New Software
4. Note that it doesn’t connect after entering credentials.

 

Thats it..:)

 

 

 

Error: #2002 – The server is not responding (or local MySQL server’s socket is not correctly configured).

Posted on Updated on

While accessing PhpMyAdmin getting the following error.

ERROR
#2002 – The server is not responding (or local MySQL server’s socket is not correctly configured).

Reason :-
The  mysql socket file is missing from the /tmp directory

Fix :-

1. Create a symbolic link from the original mysql socket file to /tmp

[root@test ~]# ln -s /var/lib/mysql/mysql.sock /tmp

OR

2. Restart MySQL service from WHM

WHM Login >> Main >> Restart Services >>SQL Server (MySQL)

OR

3.  Edit the PhpMyAdmin configuration file to use the original MySQL socket file.

vi /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

Make sure, the correct mysql socket file is mentioned there.
~~~~~~~~~~~~~~~~
$cfg[‘Servers’][$i][‘socket’] = ‘/var/lib/mysql/mysql.sock’;
$cfg[‘Servers’][$i][‘connect_type’] = ‘socket’;
~~~~~~~~~~~~~~~~

OR

4.Change the server addr local host to physical IP .