EMS SQL Manager for MySQL provides powerful tools for MySQL Database Server administration and object management. Its Graphical User Interface (GUI) allows you to create/edit all MySQL database objects in a simple and direct way, design databases visually, run SQL scripts, manage users and administer user privileges, visually build SQL queries, extract and print metadata, create database structure reports in HTML format, export/import data, view/edit BLOBs, and supplies many other services that will make your work with the MySQL server as easy as it can be.

These editions of SQL Manager for MySQL differ in their functionality. Being a light edition, SQL Manager Freeware has certain restrictions: it can handle not more than 5 databases, the maximum size of one database should not exceed 100 MB, and some others. You can view the list of all functional differences between full and freeware versions at our Features Page.

First of all you must have an opportunity to connect to some local or remote MySQL server. Besides you need your computer to satisfy the system requirements of EMS SQL Manager for MySQL.

EMS Data Export/Import for MySQL includes some additional features, which are not available in SQL Manager for MySQL such as:

• export/import data from/to several tables at once;
• export/import data from/to tables selected from different databases on one host;
• command line utility to export/import data using the configuration file with all the export/import options.

First of all, EMS SQL Query for MySQL works faster as it is a much lighter product. Besides it provides additional features for query building, e.g.:

• keeping query history, which allows you to rollback to any edited query;
• various interface improvements for more productive and easy work.

EMS DB Extract for MySQL includes some additional features, which are not available in SQL Manager for MySQL, such as:

• extracting metadata from several databases on one host;
• console application for executing extract in one-touch;
• faster execution speed.

The ‘DDL‘ tabs of the Table Editor and UDF Editor are read-only. They display the SQL text of the operations you carry over the table on the tabs ‘Fields‘, ‘Indices‘, etc, or over the UDF. To modify this text you can copy it to the clipboard and modify it using SQL Script Editor.

You can customize all display formats: integer, float, date, time and date/time in the Environment Options window.

It seems that the value of the “lower_case_table_names” variable is 1. Please visit https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html for additional information about this variable.

To create a report you can use the Create Report Wizard ("Create-->Report") or Report Designer Tool ("Tools|Report Designer"). To create a simple report based on a query you need to choose "Master Data band" on the second step of the wizard, double-click on it or click the Edit button and specify the query for your report. After that you need to tune the remaining options on subsequent steps of the wizard and press Finish.

To get data source in a report you need to add a dialog form to the report (menu 'Edit' 'Add dialog form' of Report Designer), then place database and query components from the left designer panel on it and set connection and query properties. Then the query appears in data source lists.

SSH (Secure Shell Host) protocol is used to heighten computer security when working with Unix systems on the Internet. SSH uses several encryption algorithms of different reliability. The spread of SSH is also connected with the fact that a number of Linux-like OS's (for example FreeBSD) include SSH server in their standard integration. To learn more information on this issue, please, visit http://openssh.org. SSH tunneling feature of SQL Manager is a means of ensuring secure connection to MySQL servers when working over insecure connection channels. You can also use SSH tunnel to get access to the remote MySQL servers when port 3306 is closed for external connections due to some reasons. The connection over SSH tunnel works in the following way. First a connection is established and authentication between SSH client built in SQL Manager and remote MySQL server is performed. Then all incoming and outgoing information between the program and MySQL server is transmitted through SSH server with the help of communication port (usually it is 22), and SSH server transfers this information directly to MySQL server. To register a database to which one connects through SSH tunnel, you should set the following parameters in Database Registration Wizard of SQL Manager for MySQL: On the first step:
Host is a host where MySQL server is located from the point of view of SSH server. If SSH and MySQL server are on the same machine, it is equal to SSH Host, or may be 'localhost'.
Port is a port of MySQL server on Remote Host, by default it is 3306.
User Name is a MySQL user name.
Password is a password of the MySQL user.
Please note that MySQL host name should be set relative to the SSH server in this case. For example, if both of MySQL and SSH servers are located on the same computer, you should specify localhost as Host name instead of server's external host name or IP address.
Please, do not forget to choose Use tunneling and SSH tunneling.
On the second step:
SSH host name is a host where SSH server is activated.
SSH port is a port where SSH server is activated.
SSH user name is a user on Linux machine. (It is a Linux user. It is not a user of MySQL server.)
SSH password is a Linux user password.
 

To setup SSH connection correctly, input the following values in the corresponding fields:

• On SSH page/tab:
  • SSH host name is a host where SSH server is activated.
  • SSH port is a port where SSH server is activated.
  • SSH user name is a user on Linux machine. (It is a Linux user. It is not a user of MySQL server.)
  • SSH password is a Linux user password.
• On Connection properties/General page/tab:
  • Host is a host where MySQL server is located from the point of view of SSH server. If SSH and MySQL server are on the same machine, it is equal to SSH Host, or may be 'localhost'.
  • Port is a port of MySQL server on Remote Host, by default it is 3306.
  • User Name is a MySQL user name.
  • Password is a password of the MySQL user.

Please note that MySQL host name should be set relative to the SSH server in this case. For example, if both of MySQL and SSH servers are located on the same computer, you should specify localhost as Host name instead of server's external host name or IP address.

HTTP tunneling is a method that allows to connect and transmit data between the program and a MySQL server through the HTTP/HTTPS protocols using port 80, which is used by a regular internet browser. This method is used to connect to the remote MySQL server of a hosting company when direct connection is not available because of the security reasons. The HTTP tunnel works the following way: All outgoing queries and commands sent by the client's software are encoded and transmitted through the HTTP/HTTPS protocol using port 80 to the special script that decodes the received data and sends it for processing to MySQL server and then sends the result back. This method requires the HTTP server (Apache) and PHP with MySQL to be installed on the remote server. Normally this software is provided by a hosting company that offers Linux hosting solutions. Also you need to upload the special emsproxy.php script to your web-server to access it remotely (you can place it to the folder with your other PHP scripts). If your web-server complies with the requirements and the script is installed correctly, you'll see the message "EmsProxy v 1.31" (version number can be different) in your browser when opening the http://<your_webserver_name>/emsproxy.php page. To register a remote database that will be accessed through the HTTP tunnel you have to enter the following parameters in the Database Registration Wizard of SQL Manager for MySQL:
The First step of the Wizard:
Host is a host where MySQL server is located from the point of view of HTTP server. Usually HTTP and MySQL server are on the same machine, and it is 'localhost'.
Port is a port of MySQL server on Remote Host, by default it is 3306.
User Name is a MySQL user name.
Password is a password of the MySQL user.
Check "Use tunneling" and "HTTP tunneling".
The Second step of the Wizard:
URL is the address of emsproxy.php script (e.g. http://mywebserver /emsproxy.php).

For your convenience and to speed up your work Data Grid allows to customize many data display parameters. Here are the most important of them (accessible through Data Options tab of Database Registration Info dialog):
- Limit options in table and view editors. The “Select all records of a table” option will enable you to see all table records without extra references to the server, yet in case of large tables or low speed connection channel the data may be fetched with huge delays and the incoming traffic might grow considerably. This mode is recommended when working with local databases or in a private network. The “Select only” mode restricts the maximum number of records returned after the query. Man can't process a massive amount of information at once. Hence, we came up with this mode. To request and display the next portion of data use the Next button in the Data Grid toolbar. This mode speeds up table data viewing considerably, prevents hanging and connection timeout. It is recommended to work with large tables, in case of low speed connection channels and when the traffic volume is of importance. This is a default mode. With this mode on disabling the “Perform data sorting on client in data view” and “Perform data filtration on client in data view” options comes really helpful. 
- Default Grid Mode. This option defines whether the requested rows will be loaded in the Grid all at once ("Load all rows"), or in parts ("Load visible rows") as the user scrolls down table data. The first mode increases the query opening time but speeds up scrolling. In the second mode the query opens very fast but there might be delays when navigating the grid.
- Perform data sorting on client in data view. With this option disabled, when the user sets data sorting a new request is sent to the server, and the result is shown anew in the Grid. Disabling this option is very helpful with the “Select only” mode enabled, but it increases the traffic. When this option is enabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1,000 records", then only these records will be sorted. 
- Perform data filtration on client in data view. With this option disabled, the data will be sorted on the server to which a new request is sent, and the result will be shown anew in the Grid. Disabling this option is also helpful when the “Select only” mode is on, but it increases the traffic. When this option is enabled, the sorting is made on the client computer without any references to the server but only loaded data are sorted. That is, if you set "Select only 1,000 records", then only these records will be sorted. 

We recommend that you set the following option values to achieve maximum efficiency when working with large tables:
- Select only – On
- Load visible rows – On
- Perform data sorting on client in data view – Off
- Perform data filtration on client in data view – Off

You probably have the "Autofit column widths" option enabled in the Grid tab of Options/Environment Options dialog. Try to disable it.

Please select the option 'Select all records from a table' on ‘Data Options’ tab of the Database Registration Info dialog. To set the default value of this option for newly registered databases you can use the option 'Select all records from a table' on 'GridData Options' tab of Options|Environment Options dialog.

Database Registration Info dialog contains Log tab where you can enable logging metadata changes, which are performed on a database, and SQL statements, that are executed in SQL Editor. You should switch on the ‘Enable log of metadata changes’ option for your test database, make the necessary modifications in the database and then execute the metadata log file on your master database.

Fields of types TEXT, LONGTEXT are not exported by default. You should select these fields manually on the Fields tab.

“Export As SQL Script” is intended to export table data that will be inserted into a database system other than MySQL server (SQL Server, Oracle, PostgreSQL etc.). Use “Extract Database” to copy data to a table on MySQL server.

Follow the steps below to change directory.

• Right click on the database you need in DB Explorer and click on "Database Registration Info..." item in popup menu (you can also find this item in main menu "Database"). The Database Properties form will be opened.
• Click on the "Directories" tab.
• In the "Default Directory for Export Data" section you can choose the default directory for export file.

There are several reasons why you cannot connect to the local database. If during connection you get the error "Can't connect to MySQL server on 'localhost' (10061)", then probably MySQL server is installed incorrectly or the service (usually with the name mysql) is not running. To check if the service is launched, open Control Panel->Administrative Tools->Services and find service MySQL. If you cannot find this service, you should try to reinstall MySQL. In case you find it, run it with the Start button or use Start item in the context menu.

If you get the error "Access denied for user 'root'@'localhost' (using password: YES)", then check if you enter the password for the root user correctly in case you change it during installation. If you installed MySQL with default values, you should use “root” as user name with blank password and port 3306 to connect to the server. (more info at: https://dev.mysql.com/doc/refman/8.0/en/can-not-connect-to-server.html)

MySQL server uses client's login ('myuser' in your case) and the name of the host from which it tries to set the connection ('myhost.mydomain' in your case) for the client authentication. In your case the reason is that your ‘myuser’ user from the ‘myhost.mydomain’ host doesn't have permissions to access your MySQL server. It's quite possible that you successfully connected to your database with the same login and password in your PHP scripts or with the help of phpMyAdmin, but in this case MySQL server recognizes you as the ‘myuser’ user from the ‘localhost’ host who has the necessary permissions and allows you the access.

To solve this problem you should grant the necessary permissions to user myuser@ myhost.mydomain. You can do this with the help of phpMyAdmin or with sql commands:

/*!50003 CREATE USER ‘myuser’@ ‘myhost.mydomain’*/;

GRANT ALL PRIVILEGES ON *.* TO ‘myuser’@ ‘myhost.mydomain’ IDENTIFIED BY 'user_password';

Or, you can apply to your system administrator.

(https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html)

If due to the security policy accepted in your corporate network or by your host provider you cannot connect to your MySQL server directly through TCP/IP (for example a firewall prevents this), you can use SSH or HTTP tunneling feature of SQL Manager to fulfill your task.

There could be several possible reasons that prevent you from successful connection to a remote database. If the error message says "Can't connect to MySQL server on ‘some host' (10061)" then probably you should check the correctness of port and host name you've entered and also if the remote server is run. It often happens that the port through which the connection is set with MySQL server (normally, it's 3306) is closed for the security reasons by local firewall, corporate firewall or remote server firewall. The remote server port could also be closed by ISP, or TCP/IP protocol support could be disabled on MySQL server. Please check this with your system administrator or ISP. In order to avoid this limitation you can use SSH and HTTP tunnels. If the error message says “Access denied for user: root@somehost.somedomain” or "Host not allowed to connect to server", then the reason is that the user doesn't have permission to access the database.

The most probable reason of the problem is one of the following: port 3306 used by MySQL could be blocked by Firewall installed in your network or by your hosting provider; or the TCP protocol is disabled on the remote MySQL server; or your user and host do not have the permission to connect to the server.

This error occurs because you don't have a permission to connect to remote MySQL server from your host. Please contact your database administrator or, if you have access to MySQL server with grant privilege, you can use the GRANT statement to add a new user. For example, the following command will give full access from your host to the user: /*!50003 CREATE USER 'user'@'user_host'*/; GRANT ALL PRIVILEGES ON *.* TO 'user'@'user_host' IDENTIFIED BY ' user_password '; (https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html)

You receive this error message because the program cannot find the libmysql.dll client library that is essential to work with MySQL server. The libmysql.dll file is included in the SQL Manager installation package. You might have deleted this file accidentally or moved the exe file to another folder. In order to solve the problem you need to reinstall the SQL Manager (all your settings will be saved) or copy the libmysql.dll file from the installation folder to the folder with the exe file.

It seems that you use old version of the client library libmySQL.dll. You should use the client library from our installation package. Try to reinstall the application.

That means a foreign key definition would be formed incorrectly for the altered table. For instance, referenced column doesn’t appear as the first column in some index, both in parent and child tables, or the types of corresponding fields in parent and child tables do not coincide. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Yes, that is possible. It doesn't matter what OS your MySQL Server is under, but SQL Manager works only in the Windows environment.

There are two most common reasons of this problem. - If you use SSH tunnel to connect to your database, then most likely you indicated wrong MySQL host. Upon establishing a connection with the SSH server the connection attempt to MySQL server initiated by SSH server fails. Please check the MySQL host name. Remember that it should be specified relative to SSH host and if these are installed on the same workstation then you should use "localhost" as MySQL host name. - This error can occur if your Linux glibc requires greater than 128K of stack size to resolve a hostname. It happens primarily on RedHat 8.0 system with MySQL version lower than 4.0.10, but also can happen with another configuration. To solve such issue, add/edit the following line to the [mysqld] section of MySQL configuration file: set-variable = thread_stack=192k # value must be 192K or higher.

This situation may occur if you do not have permissions to extract data from the 'mysql.proc' table. Please contact your MySQL server administrator to provide you with the necessary permissions. If it is not allowed to change the privilege settings, and if you are using MySQL 5.5 or higher, try to enable the 'Use INFORMATION_SCHEMA database to refresh metadata' option of the 'Database Registration Info -> Options' dialogue.

Please note that foreign keys are supported not for all table engines of MySQL server. If the engine (set for your table) supports foreign keys (i.e. InnoDB), there should be the 'Foreign keys' tab available in the editor. Switch to this tab and chose the 'Add a new foreign key' option on the navigation bar.

If you want to connect to MySQL server of your hosting provider through the Internet, please note that our program establishes the direct TCP/IP connection to the server port, and most of the hosting providers do not allow such remote connections. When connecting to your database via CPanel, PHPMyAdmin or your backend scripts (PHP, ASP, etc.), the connection to MySQL server is performed by the HTTP Server. In most cases, this is a local connection or connection inside a subnet of the hosting provider. In this case your Internet browser just receives the processed data from the HTTP Server. 
To establish a connection to your database, you need to find out (with your hosting provider or from the provided by him documentation) whether they support direct connection on port 3306 (MySQL port by default) from the remote client machines. If it is not supported, you need to find out whether they provide SSH port to connect to the remote server. If so, you can use the SSH-tunneling feature in SQL Manager specifying the SSH authentication parameters provided by your hosting provider.
To connect in such way, you should perform the following: 
1) Upload the ‘emsproxy.php’ script (which is included in the installation package of SQL Manager) to your site root directory using the available FTP-account. In your browser specify the URL path to the downloaded 'emsproxy.php' script (e.g.: http://mysite.com/emsproxy.php). The ‘emsproxy.php script is installed correctly’ message should be displayed. It means that the script works correctly.
2) Launch SQL Manager.
3) At Step 1 of Register Database Wizard specify the connection parameters for your MySQL database, as they are specified in PHPMyAdmin or your scripts (e.g.: specify ‘localhost’ in the ‘Host’ field), enable the ‘Use tunneling’ and ‘HTTP tunneling’ options. Press ‘Next’. 
4) Specify the URL path to the uploaded ‘emsproxy.php’ script as you specified it in the browser (Item 1). 
5) Press ‘Next’.
6) Specify your database name (the database may not be available, if you have limited permissions on the server) and press ‘Finish’.

Data is often displayed in such way, when the actual data encoding does not match the encoding of the database fields. For example, Greek characters are stored in a Latin1 encoded field. If so, set the 'Windows charset' value in the 'Client charset' field of the 'Database Registration Info' dialogue. If in this case the data is displayed correctly, this mode can be used as a temporary solution. However, you should remember that this is the incorrect database configuration, therefore the server-side comparisons and data sorting will work incorrectly. We recommend you to contact our technical support team in this case.

SQL Manager stores all your settings in the Windows registry. It means that the only way to transfer your database data is to get access to the Windows registry. You can do it by loading OS from your old HDD (if possible) or by opening the registry file with a special editor program. If you managed it, you can unload the 'HKEY_CURRENT_USER\Software\EMS\MySQL Manager' branch to the *.REG file, transfer this file to a new system and add information to the registry by double-clicking the file.