APPENDIX H - MySQL quick guide

This section wouldn't to be a MySQL guide replacement, but a summary of the main SQL commands and configuration options that can be useful to prepare databases for VEGA and VEGA ZZ.

To download and install MySQL, visit the Web site: http://www.mysql.com

 

Server configuration

If you intend to put to the databases large molecules as protein models, you must consider to increase the max_allowed_packet variable that defines the size of transfer packed. If the data sent to the server is larger than the value of this variable, the stored information will be truncated. For this reason, it will impossible to extract the big molecules from the database. To avoid this problem, you must increase the max_allowed_packet in permanent way changing the MySQL configuration file. More in detail, you can add the following line:

max_allowed_packet=16777216

before [mysqld] section to enlarge the packet size to 16 Mb. For Windows operating systems, the configuration file is placed in the MySQL installation directory (usually C:\Program Files\MySQL\MySQL Server X.Y) and is named my.ini. For Unix operating systems and MacOS, it's placed in the /etc or /etc/mysql directory and is named my.cnf. Please remember to restart MySQL when you change its configuration file.

 

Client configuration

VEGA and VEGA ZZ uses the standard ODBC interface to communicate with MySQL server and, for this reason, you must install the MySQL ODBC driver (MySQL Connectors) and eventually the ODBC layer (unixODBC). Windows doesn't need unixODBC, because the layer is integrated in the operating system.

To connect to a remote database, you must create an ODBC file data source (.dsn file) which generic content can be:

[ODBC]
DRIVER=MySQL ODBC 5.1 Driver
UID=user_name
PWD=password
DFLT_BIGINT_BIND_STR=1
PORT=3306
DATABASE=database_name
SERVER=server_address

in which:

 

Field Description
DRIVER

ODBC driver. You must keep the string as shown in the example if you want to connect to MySQL by the 5.1 version of the driver.

UID

User name. It must be the same created in MySQL and must have the rights to access to the database to which you want to connect. For more details, look the next section.
PWD User password.
PORT

This is number of the TCP/IP communication port. If you don't change in the server configuration, keep the default value.
DATABASE Database name. Look the next section to retrieve the list of the databases or to create a new one.
SERVER IP or DNS name of the server.

If you want to open the data sources in easy way by the context menu of the Database explorer window, put the file in the ...\VEGA ZZ\Data\Databases directory, keeping the .dsn extension.

 

SQL basic commands

Use the MySQL command line client to enter the SQL commands.

 

 

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';

Example:

CREATE USER 'lab'@'%' IDENTIFIED BY 'my_pass';

The creation of a new user could be required to limit the access to specific databases.

DROP USER 'user_name'@'%';

Example:

DROP USER 'lab'@'%';
CREATE DATABASE database_name;

Example:

CREATE DATABASE my_new_database;

Remember that you don't need to create the tables, because VEGA makes it for you.

DROP DATABASE database_name;

Example:

DROP DATABASE my_new_database;

Take care: with this command you loose all data contained in the database.

SHOW DATABASES;

Examples:

SHOW DATABASES;
SHOW DATABASES LIKE 'my_%';

As in the last example, this SQL command allows the pattern matching.

USE database_name;

Example:

USE my_new_database;
SELECT * FROM molecules;

This command sets the default database for the next commands.

GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'%' WITH GRANT OPTION;

Example:

GRANT ALL PRIVILEGES ON my_new_database.* TO 'lab'@'%' WITH GRANT OPTION;

Remember to set the access privileges every time that you create a new database.

SHOW TABLES;

Example:

SHOW TABLES;
ALTER TABLE table ADD COLUMN new_column data_type [AFTER column_name];

Example:

ALTER TABLE molecules ADD COLUMN Activity REAL AFTER Volume;

Add the Activity column to molecules table after Volume column as REAL data type. The most common data types are: CHAR(n) (fixed length character string with the n size), DATE (date), INT (integer number), REAL or FLOAT (real number), VARCHAR(n) (variable length character string with the n max. size), TIME (time).

ALTER TABLE table DROP column_name;

Example:

ALTER TABLE molecules DROP Activity;

Take care to not remove columns made by VEGA, otherwise, when you add new molecules to the database, an error message will be showed.

SHOW COLUMNS FROM table;

Example:

SHOW COLUMNS FROM molecules;
SHOW [GLOBAL] VARIABLES;

Example:

SHOW VARIABLES;