Online Documentation for SQL Manager for MySQL

EMS SQL Manager FAQ


Please read this page attentively if you have questions about EMS SQL Manager for MySQL.

 

Table of contents

 

Product questions

 

Common questions

 

Export/Import questions

 

Troubleshooting

 

Question/answer list

 

Product questions

 

Q: What is EMS SQL Manager for MySQL?

A: EMS SQL Manager for MySQL is a powerful tool for MySQL database server administration and development. SQL Manager for MySQL works with any MySQL versions from 4.1 to 5.6 and supports all of the latest MySQL features including views, stored procedures and functions, InnoDB foreign keys, events and so on. It offers plenty of powerful tools for experienced users to satisfy all their needs. SQL Manager for MySQL has a new state-of-the-art graphical user interface with well-described wizard system, so clear in use that even a newbie will not be confused with it.

 

Q: What is the difference between Full/Lite editions of EMS SQL Manager for MySQL?

A: These editions of SQL Manager for MySQL differ in price and features. To register SQL Manager for MySQL, see the Purchase page, and to learn about the difference in features please go to our Feature Matrix page.

 

Q: What do I need to start working with EMS SQL Manager for MySQL?

A: First of all you must have a possibility to connect to some local or remote MySQL server to work with SQL Manager for MySQL. You can download MySQL server from https://www.mysql.com/downloads (download is free). Besides, you need your workstation to satisfy the system requirements of SQL Manager for MySQL.

 

Q: What is the difference between the Export/Import functions in SQL Manager and the Data Export/Import utilities?

A: The Data Export/Import for MySQL utilities include some additional features which are not available in SQL Manager, such as:

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

 

Q: What is the difference between the Query Builder module in SQL Manager and the SQL Query for MySQL utility?

A: First of all, 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 easier work.

 

Q: What is the difference between the Extract Database function in SQL Manager for MySQL and the DB Extract for MySQL standalone utility?

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

  • extracting metadata and/or data from several databases on one host;
  • a console application for performing extract in one-touch;
  • faster extraction speed.

 

Scroll to top

 

Common questions

 

Q: I can't modify DDL. Why?

A: The DDL tabs of the SQL Manager editors are read-only. To modify an object, you can copy the text to the clipboard and modify it using SQL Editor. For more details refer to Viewing object DDL structure.

 

Q: How can I customize data formats in grid?

A: You can customize all display formats: integer, float, date, time and datetime using the Color & Formats page of the Environment Options dialog.

 

Q: I can't backup/restore tables on the remote server. Why?

A: The Backup/Restore Tables functions do not work with remote MySQL server. This is not a limitation of SQL Manager for MySQL. MySQL server tries to backup tables only on the server side, so the Backup Tables function works properly only with local MySQL server. If you want to backup tables from the remote server, you can use the Extract Database feature.

 

Q: When I create a table like `TestTable`, it will get stored as `testtable`. This is wrong!

A: Probably the value of the "lower_case_table_names" variable is currently set to 1. Please visit https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html for additional information about this variable.

 

Q: I am trying to create a report in Report Designer, but I can't get access to the table data: 'Band data source' list is empty.

A: It is recommended to use Create Report wizard which is run by right-clicking the Reports node in DB Explorer and selecting the 'New Report...' context menu item. The wizard will create all necessary data sources.

If you still want to use Report Designer, to get the data source in a report, you need to add database and query components to the report.

 

Q: How can I speed up my work with large tables?

A: For your convenience and to speed up your work, the Data Grid allows customizing a number of data display parameters. Here are the most important of them (accessible through the Grid | Data Options section of the Environment Options 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. A man cannot process a massive amount of information at once. Hence, we came up with this mode. 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 the default mode. When in this mode, enabling the 'Use SQL sorting in data view' and 'Use SQL filter 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.
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

 

Q: I have a table with appr. 1000 records only, and a large number of fields. Opening this table on the 'Data' tab takes too much time.

A: You need to set the Grid Mode for the table to 'Load Visible Rows'. Please right click within the table grid and select the 'Grid Mode' | 'Load Visible Rows' context menu item.

 

Q: I cannot see procedures and functions in the object tree,  but they are seen in the database. How can I fix it?

A: 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.

 

Q: I cannot find a way to create a foreign key. How can I create it?

A: 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.

 

Q: I cannot connect to the hosting provider database, but when using PHPMyAdmin the connection to the database is established successfully.

A: 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'.

 

Q: My database uses a 'Greek' codepage. In data grid view all information is displayed as "??????". What is wrong?

A: 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.

 

Q: Is it possible somehow to restore my database queries/settings/registrations from the old or faulty HDD, where SQL Manager was installed?

A: SQL Manager for MySQL 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.

 

Scroll to top

 

Export/Import questions

 

Q: I'm trying to export data from a table, but LONGTEXT fields are not exported.

A: Fields of type LONGTEXT are not exported by default. You should select these fields manually at the Selecting fields for export step.

 

Q: What is the difference between the "Extract Database" and "Export as SQL Script" functions?

A: Export as SQL Script is intended for exporting table data that will be inserted into a database system other than MySQL. Use Extract Database Wizard to copy metadata and/or data to a database on MySQL afterwards.

 

Q: How can I change the default directory where exported data are saved?

A: Follow the steps below to change the default directory:

1. Right-click the database alias in DB Explorer and select the 'Database Registration Info...' context menu item (you can also find this item in the 'Database' main menu) to open the Database Registration Info dialog.

2. Proceed to the Directories section within the dialog.

3. Set the 'Default directory for Export Data'.

 

Scroll to top

 

Troubleshooting

 

Q: When I try to register a database, I get the following message:

Access denied for user: user@host.domain

Why does it happen?

A: This error is generated by MySQL server because you do not have privileges to connect to the server from your host. Please contact your database administrator to resolve this problem.

 

Q: I try to connect to remote MySQL host, but I receive only the "Host not allowed to connect to server" message. What can be a reason?

A: 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:

GRANT ALL PRIVILEGES ON *.* TO 'user'

IDENTIFIED BY 'user_password'

WITH GRANT OPTION;

Please read the "GRANT and REVOKE Syntax" chapter of MySQL reference.

 

Q: I've registered the DB, but on attempt to open it the "dynamic library libmysql.dll not found" message appears.

A: Please make sure that the libmysql.dll file exists in the SQL Manager for MySQL directory. Reinstallation of the application can solve the problem.

 

Q: I get "Bad Handshake"message when I try to connect to MySQL 4.1.x. Does this product support 4.1.x?

A: You seem to use an old version of the client library libmysql.dll. You should use the client library from our installation package. Please try to reinstall the application.

 

Q: Every time I try to connect to MySQL on a remote Linux server, I get the 'Lost connection to MySQL server during query' error. What's wrong?

A: 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 resolve 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

 

Q: I tried to create a foreign key for InnoDB table, but received the "Can't create table ... (errno: 150)" error. What's wrong?

A: The referenced column should appear as the first column in some index, both in parent and child tables.

 

Scroll to top

 

If you still have any questions, contact us at our Support Center.