Skip to main content

Mysql

Mysql​

All the necessary modules are already installed to use an external MySQL database, since those same modules are needed to use the internal databases used by Lurana.

By default, MySQL servers are set up to only receive local connections from the localhost. If Lurana is trying to connect to a MySQL server on another machine, then that server will have to be configured to allow external connections. To configure the server to allow external connections, edit the my.cnf file of the MySQL server and comment out the line by adding a # symbol at the beginning:

# bind-address  = 127.0.0.1

Then restart the MySQL server. To check whether MySQL is listening for external connections, issue the following command in Windows:

netstat -an

A line, similar to the one below will be displayed:

TCP      127.0.0.1:3306       0.0.0.0:0       LISTENING

In Linux/UNIX, use the command:

netstat -tanp

A line, similar to the one below will be displayed:

tcp      0      0 0.0.0.0:3306       0.0.0.0:*       LISTEN    2270/mysqld

When setting up a database connection to an external MySQL database, the character encoding must be selected, such as Latin-1 and UTF-8.

alt text

To find the character encoding used by the MySQL database, log in to MySQL, navigate to the database and issue the status command:

mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| example_db         |
+--------------------+
3 rows in set (0.00 sec)

mysql> use example_db;
Database changed
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

Connection id:		470044
Current database:	employees
Current user:		root@172.16.1.34
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:	10
Connection:		192.168.51.109 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			38 days 34 min 29 sec

Threads: 7  Questions: 5700832277  Slow queries: 0  Opens: 875509  Flush tables: 1  Open tables: 416  Queries per second avg: 1735.271
--------------

Look for the Client characterset parameter, which is the character set that Lurana will use to send queries to the MySQL database. Conn. characterset is the character set that MySQL will use to send information back to Lurana.

In the configuration of Lurana's database connection, set Encode to the character set used by Client characterset and Conn. characterset.

alt text

Lurana expects the client and connection character set to be the same. If they aren't the same, change them to be the same. To change them temporarily, use:

SET NAMES <character-set>;

To change them permanently, add the following lines to my.cnf:

character_set_client = <character-set>
character_set_results = <character-set>
character_set_connection = <character-set>

Then, restart the MySQL server.

MySQL Not Using UTF-8​

Some issues might arise if the MySQL Server database does not use the UTF-8 character set and contains non-ASCII characters, because Lurana is designed to use UTF-8.

  • The source code of Lurana is written in UTF-8, and produces UTF-8 HTML pages.
  • The character set of the Apache server needs to be UTF-8.
  • AJAX requests using JSON encode are in UTF-8.
  • The collation in MySQL for Lurana databases is in UTF-8.

These four components need to be in UTF-8 for everything to work: the source code, Apache server, interactive data and database fields.

Solving Issues with UTF-8 Characters​

To display Unicode data from a MySQL Server database, it is recommended to:

  • Setup freetds to send UTF-8 data to Lurana.