MySQL

Latest revision as of 13:18, 4 November 2013

Module mysqldb is used to make the connection between Yate and one or more MySQL databases.

This module supports connection pool and maintains connection to database alive.

When using this module for storing CDRs, also read [call.cdr] section in Register.

[edit] Requirements

Module mysqldb needs devel packages installed. When Yate is installed at configuration step, normally the libraries needed for MySQL will be corrected detected by Yate, if the MySQL packages were installed.
Yate will detect them only if they are installed in the distribution's default location. If you change the location then you have to specify the path like this:

./configure --with-mysql=/path/to/DIR
make
./run -vvvvv -CDo

After starting Yate check if module mysqldb is loaded.

[edit] Check if module is loaded

Note: Check if module mysqldb is loaded. It is required to be loaded so that Yate can connect to the database defined in mysqldb.conf.

There are two ways of checking if module is loaded:

• Verify module loading when starting Yate

When Yate starts you can see a list of modules that are loaded. If MySQL does not appear, you must install the devel packages for MySQL.

./run -vvvvv -CDo
..........
Loaded module MySQL based on 5.1.42
.......

• Verify module loading from rmanager

Another way to check if module is loaded is from telnet, use command status check if mysqldb is in the list:

 %%+status                                                             
 ...............
name=mysqldb,type=database,format=Total|Failed|Errors|AvgExecTime;conns=0,failed=0
............
%%-status

Or simply type after command status the name of the module like this:

status mysqldb
%%+status:mysqldb
name=mysqldb,type=database,format=Total|Failed|Errors|AvgExecTime;conns=0,failed=0
%%-status

If line name=mysqldb,type=database.... don't appear in the list that means that module is not loaded.

[edit] Solutions if module is not loaded

• A reason that the module is not loaded may be that you don't have the devel packages installed.

Install devel packages. Then run ./configure and make in Yate's sources.

• Another reason could be that the module is not listed in file yate.conf. If parameter modload is disabled, then you have to check in [modules] section that module is enabled.

yate.conf

[general]
modload=no

[modules]
mysqldb.yate=yes

[edit] Configuration file

The configuration file is mysqldb.conf. It is found in directory conf.d where Yate sources are.
The file must be renamed from mysqldb.conf.sample into mysqldb.conf.

This file has the general section that holds the common settings to all connections.
The other sections in this file will describe each database connection.
The section name written between [], is the account name of the connection that can be used in other configuration files.

There can be multiple connections to different databases.

mysqldb.conf

[general]
; This section is special - holds settings common to all connections

; priority: int: Handler priority
;priority=100


; Each other section in this file describes a database connection

;[default]
; The section name is used as the database connection name

; timeout: int: Query timeout in milliseconds - will be rounded to seconds
;timeout=10000

; initretry: int: Interval (in seconds) to retry creating failed connections. Setting it to 0 will disable retrying.
;initretry=10

; host: string: MySQL server to connect to, defaults to local
;host=

; port: int: Port on which the server is listening for connections
;port=0

; database: string: Name of the database to use in queries
;database=yate

; user: string: Username to use to access the database
;user=mysql

; password: string: Password to authenticate the user to the database server
;password=

; socket: string: UNIX socket to use for local connections
;socket=

; compress: bool: Enable use of compression protocol
;compress=disable 

; encoding: string: Character set encoding used to communicate with the server
; If not set or empty will use the default for your system
; This setting is not available on older MySQL client libraries
;encoding=

; poolsize: int: Number of connections to establish for this account
; If not set or empty, it will create only one connection
; Minimum number of connections is 1, maximum is 10
;poolsize=1

[edit] Example

This is how we create a new connection to a MySQL database.

mysqldb.conf

[yatetest]
host=1.2.3.4
port=3306
database=yate
user=mysql
password=yate

To use this connection from another module the account must be set in another file.

• If we need to store CDRs, we will have to use register.conf file.

In this file, we are going to set into [general] section the account name of the database so that Yate will know which connection to make.

register.conf

[general]
account=yatetest

• Another way to use the account created in mysqldb.conf is when using module javascript to make queries, we have to send a database message.

Specify the connection to use by setting in parameter account the name of the database account created in mysqldb.conf.

See also

• Register
• PostgreSQL