Saturday, October 24, 2009

Troublesome MySQL Configuration

Quite a few of users who attempt to install IndiMail first time, face the biggest issue with MySQL installation and configuration. Most of the issues relate to the following
  1. MySQL version less than 5.1: MySQL fails to startup under supervise
  2. MySQL version less than 5.1: svctool fails to create a default database for IndiMail during rpm installation.
  3. /etc/my.cnf uses socket=/var/lib/mysql/mysql.sock. IndiMail uses /tmp/mysql.sock. Connection to MySQL fails if you use mysql(1) (without -S opton).
  4. You get MySQL syntax error when running indimail programs
The solution to the first problem is to delete the lines --general-log-file and the line --slow-query-log from the file /service/mysql.3306/run. After deleting the lines you can issue the command

/var/indimail/bin/svc -u /service/mysql.3306

to start up MySQL.

The solution to the second problem is to create a blank MySQL database by running the following command

/var/indimail/bin/svc -d /service/mysql.3306
/bin/rm -r /var/indimail/mysqldb/data
/usr/bin/mysql_install_db --user=mysql --datadir=/var/indimail/mysqldb/data

chown -R mysql:mysql /var/indimail/mysqldb/data
/var/indimail/bin/svc -u /service/mysql.3306
mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD('some_pass') where user='root';
mysql> CREATE USER indimail identified by 'ssh-1.5-';
mysql> CREATE USER mysql identified by '4-57343-';
mysql> CREATE USER admin identified by 'benhur20';
mysql> CREATE USER repl identified by 'slaveserver';
mysql> GRANT SELECT,CREATE,ALTER,INDEX,INSERT,UPDATE,DELETE, \
mysql> CREATE TEMPORARY TABLES, \
mysql> LOCK TABLES ON indimail.* to 'indimail';
mysql> GRANT RELOAD,SHUTDOWN,PROCESS,SUPER on *.* to admin;
mysql> GRANT REPLICATION SLAVE on *.* to repl;


The third problem can be solved by replacing socket=/var/lib/mysql/mysql.sock with socket=/tmp/mysql.sock in /etc/my.cnf or equivalent file. You can also copy /var/indimail/etc/indimail.cnf as .indimail.cnf in your home directory. i.e.

cp /var/indimail/etc/indimail.cnf $HOME/.indimail.cnf


To avoid the above problems, it is recommended to use one of the below MySQL versions
  • mysql-6.0.3-alpha
  • mysql-6.0.9-alpha
  • mysql-5.0.26
  • mysql-5.0.77
  • mysql-5.1.40
  • mysql-5.4.3-beta
  • mysql-5.5.0-m2
  • MariaDB 5.1.42
Let me know if you are using any version of MySQL other than the above. Run the following command to get the MySQL version

% mysql_config --version

The fourth problem relates to a workaround made in IndiMail to prevent MySQL injection. What is needed is to set NO_BACKSLASH_ESCAPES in the MySQL server. You can use either of the two methods below
  1. This SQL mode also can be enabled automatically when the server starts by using the command-line option
    `--sql-mode=NO_BACKSLASH_ESCAPES' or by setting
  2. Set `sql-mode=NO_BACKSLASH_ESCAPES' in the server option file (for
    example, `my.cnf' or `my.ini', depending on your system).


5 comments:

calculateonlinedotorg said...

I am struggling with mysql problem number 3. The installed Mysql version is 5.1.42 on a Centos.

When I change the socket file in my.cnf then mysql does not start up any more.

Mysql still does not start up after changing /etc/init.d/mysqld to reflect the socket change.

cprogrammer said...

we need to find out why mysqld fails to start. Do the following

1. Make sure mysql is not running
ps -ef|grep mysql
If mysql is running, stop it
% /etc/init.d/mysqld stop
% /var/indimail/bin/svc -d /service/mysql.3306

2. Now start mysql on the foreground as below
/service/mysql.3306/run

The above command should print all errors on the screen as mysqld is started up.

calculateonlinedotorg said...

Thanks for your help !

I now realize that I also have problem 1 and 2 though "mysql_config --version" shows that I have Mysql 5.1.42 > 5.1. So before trying to solve problem 3 I might have to solve problem 1 and 2 first.

I followed your advice and changed /etc/service/mysql.3306/run. When I execute
/var/indimail/bin/svc -u /service/mysql.3306

then the error message is

svc: warning: unable to control /service/mysql.3306: file does not exist

By the way I found another advice for problem 3, which is adding the following line to the [client] section of my.cnf:

socket = /var/lib/mysql/mysql.sock

Will this also solve problem 3 ?

cprogrammer said...

MySQL 5.1.42 should be fine.

Does the directory /service/mysql.3306 exists. IF it does, can you send me the output of /service/mysql.3306/run

You can send it to mbhangui at gmail.com

cprogrammer said...

If you use a socket different from /tmp/mysql.sock then you need to specify it in the file /var/indimail/control/host.mysql (for a non-clustered setup) . The format of host.mysql is

host:user:password:socket_path

i.e.

localhost:indimail:ssh-1.5-:/var/lib/mysql/mysql.sock

IndiMail Queue Mechanism

Indimail has the ability of configuring multiple local and remote queues. A queue is a location on your hard disk where email are deposited ...