MySQL

SQLiteMySQL

Have a look at the article Component database as well.

MySQL / MariaDB: The fast database for multiple workstations

The SQLite database pre-installed with TARGET becomes slow with normal network transfer rates in multi-user operation. If you want to access a shared TARGET 3001! component database in a network with several workstations, you need the components in a ‘’‘MySQL database’‘’ or in a ‘’‘MariaDB’‘’. In our experience, however, the SQLite database has the edge in single-user operation.

Install MySQL / MariaDB

You can download the installers here: MySQL and MariaDB

You can install the database on a PC or on a server.

Set up / customize MySQL / MariaDB server and insert TARGET database

• Create a new empty database “target3001db” (with collation utf8mb3_general_ci) with phpMyAdmin, for example, or in the SQL console:

CREATE DATABASE target3001db CHARACTER SET utf8 COLLATE utf8_general_ci;

• Download the following SQL file and import it into the new empty database: target3001db.sql All tables, all functions and all triggers are created automatically.
• Enter users and rights for the new database. User names and passwords may have a maximum of 16 characters each. The following rights are required: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE and TRIGGER.
• In TARGET in the component browser in the “Connected to...” menu enter the settings for MySQL. These are also used for MariaDB:

Start TARGET with MySQL database <enter “Yes” here>
MySQL Server <name or IP of your MySQL/MariaDB server>
MySQL User <your user name for the MySQL/MariaDB server>
MySQL Password <your corresponding password>
MySQL SSH Key <if SSH is used: SSH key file>
MySQL SSH Cert <if SSH is used: Certificate-File>
MySQL SSH CA <if SSH is used: Certificate-Authority-File>

• If you have already created your own user components in the SQLite database, you can execute the menu item “Connected to... / Migration from SQLite to MySQL...” once and select the SQLite database ‘target3001.db’. The synchronization may take a moment. Additional *.db files can be imported later in the “Import / Export” menu.
• To be on the safe side, you can call up the “Update” menu item again in the component browser.

Particularities for Windows servers

Necessary for Windows servers (with the console):

$ mysql -u root -p

or without password: $ mysql -u root

mysql> use mysql
mysql> GRANT ALL ON *.* to root@'%' IDENTIFIED BY '<your-mysql-root-password>';
mysql> FLUSH PRIVILEGES;

You can also make this setting with phpMyAdmin or with the Workbench.

Download MySQL

https://dev.mysql.com/downloads/mysql/5.7.html

Install a MySQL server

While the SQLite database works with only one DLL and one single database file, a MySQL database needs a MySQL server. This should preferably be installed on a separate server within the network or as an auxiliary on one of the workstations. The server has always to be on power if a client in the network wants to use TARGET 3001!.

Typically you will ask your system administrator to set up a MySQL server. Linux as well as Windows computers are suitable to run such a MySQL server. You will find several tutorials in the Internet to install MySQL to the various systems:

MySQL installation

Set up / adapt a MySQL server and insert a TARGET 3001! data base

You have two options:

• Use a console (recommended)
• Use a browser application, e. g. phpMyAdmin (not recommended. Your server might be occupied up to 2 hours.)

NOTE: User names ans passwords must not have more than 16 characters.

Using a console

Set up, adapt

Please set the following options to configure the server so that TARGET 3001! can access to the database correctly:

[mysqld]
thread_stack = 256K
query_cache_size = 32M     #query_cache_size ist ab MySQL 5.7.20 deprecated, also hinfällig
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
max_allowed_packet=2M

[mysql]
max_allowed_packet=2M

The Linux configuration file is named "my.cnf", the Windows configuration file "my.ini".

Insert the initial TARGET 3001! database

In order to insert the initial TARGET 3001! component database to the MySQL server, you need a DUMP file of the database and a brief script file for the import. Whether you have a Linux or a Windows system, different scripts need to be started. Following the link please find a zip file containing all you need:

Dump and scripts

Each script needs to be adapted according to the number of users. The MySQL server is setup after that.

Using phpMyAdmin or similar

The following section explains how to install the MySQL-DB target3001db with a browser application in order to administrate MySQL-databases (e.g. phpMyAdmin).

The dump import can take up to two hours. You should use the console (s. 4) not to block the server for too long time.

In your application you may enter the SQL-commands as text or by menus and dialogs. In terms of common understanding we denote the commands here as text.

The database target3001db etc.:

-- Create User 1, furnish him with rights needed  *** if needed replace target3001user1 and target3001user1_password ***
GRANT USAGE ON *.* TO 'target3001user1'@'%' IDENTIFIED BY 'target3001user1_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE,
 ALTER ROUTINE, TRIGGER ON target3001db.* TO 'target3001user1'@'%';

-- Create User 2, furnish him with rights needed  *** if needed replace  target3001user2 and target3001user2_password ***
GRANT USAGE ON *.* TO 'target3001user2'@'%' IDENTIFIED BY 'target3001user2_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE,
 ALTER ROUTINE, TRIGGER ON target3001db.* TO 'target3001user2'@'%';
 
-- further User

-- create database
CREATE DATABASE target3001db CHARACTER SET utf8 COLLATE utf8_general_ci;

-- modify variables
-- To change a global variable you need the permission SUPER.
SHOW VARIABLES LIKE 'thread_stack';
 -- if smaller than 256K, then:
 SET GLOBAL thread_stack = 256K;
SHOW VARIABLES LIKE 'query_cache_size';
 -- if smaller than 32M, then:
 SET GLOBAL query_cache_size = 32M;
SHOW VARIABLES LIKE 'max_allowed_packet';
 -- if smaller than 2M, then:
 SET GLOBAL max_allowed_packet = 2M;

Now the DB-Dump is going to be imported without components (empty3001db.sql):

Not the whole dump with components at once! Remember the db has over 60MB but unfortunately the dump import always is limited, see image. Example phpMyAdmin:

Download the Dump here.

TARGET 3001! Online DB Update: Load all components etc. from our server. The download only takes a few seconds but the dump import would take up to two hours. Your server/network would be charged accordingly.

Particularity for Windows server

Required for Windows server (with console):

$ mysql -u root -p

or without password: $ mysql -u root

mysql> use mysql
mysql> GRANT ALL ON *.* to root@'%' IDENTIFIED BY '<your-mysql-root-password>';
mysql> FLUSH PRIVILEGES;

These setting you can also do with phpMyAdmin or the workbench.

Setting up a MySQL client in TARGET 3001!

In the same directory as the TARGET 3001! program you will find a file called "libmysql.dll". It represents the client driver to connect to the MySQL server. In TARGET 3001! itself you need to enter few things in order to get response from the MySQL server:

Open an new project and press function key [F2] to open the component browser. At the end of the top menu line you will see that you are still connected to the SQLite database:

(Image 1: Component browser, migration)

Now click on the menu entry "[ connected with ... ]". In the opening menu choose option "Settings". Now enter the following:

Start TARGET 3001! using MySQL database (enter "Yes")
MySQL Server (name or IP of your MySQL server)
MySQL User (your user name for the MySQL server)
MySQL Password (your respective password)
MySQL SSH Key (if SSH is used: SSH key file)
MySQL SSH Cert (if SSH is used: certificate file)
MySQL SSH CA (if SSH is used: certificate authority file)

Multiple users can use the same MySQL user name.

Insert your customized components (migration)

If you already created your own USER components in SQLite (target3001.db), you can import them once only from your TARGET 3001! version to the bare database provided by IBF. All component IDs and custom component types and so on remain the same in the database. We call this action "migration". Select the most important SQLite database from one of the several workstations. Further components from the other users can be added later on.

First use menu "[connected with...]" in order to switch to the MySQL database. In this menu select "Migration from SQLite to MySQL". Then enter this most important SQLite database target3001.db, which shall be migrated to MySQL. So the easiest way to perform the migration, is to do it on the computer with that most important SQLite database.

Now all clients will have access to the MySQL database.

Does MariaDB also work instead of MySQL?

Here there is a tip from a customer.

Help, MySQL throws error messages!

If TARGET can no longer connect to the database or throws other error messages, this may be due to deleted users. If necessary, recreate these users in the MySQL database.