Online Documentation for SQL Management Studio for MySQL

EMS SQL Studio FAQ


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

 

 

Table of contents

 

Product questions

 

DB registration and connection

 

Objects manipulation

 

Query

 

Reports management

 

Data manipulation

 

Data analysis

 

Backup/Restore

 

Templates management

 

Scheduler

 

Logs management

 

Localization

 

 

Question/answer list

 

Product questions

 

Q: What is EMS SQL Management Studio?

A: EMS SQL Management Studio is a complete solution for database administration and development. SQL Studio is an integrated suite of powerful database management tools united in one easy-to-use work environment. Developers will appreciate its ability to create/edit/drop any database objects and will find metadata and data comparison, data export/import and test data generation tools extremely helpful. Database migration, backup and restore tools will be of great help to DBAs. The Scheduler will automate a sizeable part of DBA's work.

 

Q: SQL Studio embraces a variety of capabilities, but will I need them all? Is there a simpler product?

A: This question naturally comes if you are new to database administration and development. It might not be immediately obvious why you would need so many different tools. We would like to point out that your experience will build up day in and day out, you will face new, more complicated tasks and you will need more tools to solve them. SQL Studio embraces ALL must-have tools for DBAs and developers. And it will be in line with your growing expertise! Besides, once you familiarize yourself with SQL Studio tools, you will be able to find the simplest, most suitable and reliable solutions to your tasks! We do realize that our product is used not only by experts, that is why we included plenty of Wizards that will make the job easy and enjoyable even for a newbie. The Studio interface is designed so that the component tools are called only when needed, not to hinder your work. Our experience shows that the majority of our customers get used to our products really fast, and we have never received any downgrade requests.

 

Q: What do I need to start working with SQL Studio?

A: In order to start working with SQL Studio, you need to download the installation package from the download page available at our site. Then run the installation file on a MS Windows workstation (see System requirements for details). When the installation is finished, the program is ready to be used in the trial mode. You can evaluate the trial version for 30 days. This is the time for you to make a purchasing decision. You can purchase the Studio here. After the payment is complete, your will receive your registration information via e-mail (see How to register SQL Studio for details)

 

Q: Many utilities that are included in SQL Studio are also offered as stand-alone products. What are the advantages of purchasing them within the Studio?

A: Indeed, in order to perform some simple task, you can purchase a separate EMS utility or a few of them. Yet, if you purchase these utilities within the Studio, you receive additional functionalities. First of all, you will be able to launch the Studio components from Task Scheduler. That is, you can set the order to run the tools in, set the parameters to be used, the time and/or time intervals for the task to be performed and go to play a bowling game :) Task Scheduler will fulfill all your tasks, save the logs for the performed operations and will notify you by e-mail about the successful/unsuccessful task completion. Task Scheduler will help you to really automate the majority of your DB service tasks. Secondly, you will be able to store all registered Databases in one repository. Thus, you will need to register your data source once and will be able to use it in all Studio tools. This will save your time and reduce an error chance to the minimum. The third equally important benefit is the pricing. When purchasing the tools within SQL Studio, you pay a lot less, compared to purchasing them as separate products. You will receive up to 40% off the original price!

Scroll to top

 

DB registration and connection

 

Q: Can I work with several databases simultaneously?

A: Yes, SQL Studio allows you to work with an unlimited number of databases at the same time. You can also operate any number of databases via SSH and HTTP tunneling. Once the database is registered at the database source repository correctly, you are ready to use it.

 

Q: I use Windows OS, but my remote MySQL server is running on Linux OS. Will I be able to use SQL Studio to work with this server?

A: Yes, that is possible. It does not matter what OS the database server is running under, SQL Studio works only in the Windows environment (see System requirements for details).

 

Q: Why does SQL Studio use a single registered database repository?

A: The single registered database repository allows one database to be accessed with all SQL Studio tools. If you purchased our product suites (bundles) previously, you had to enter the database information in each tool you used. That eventually led to massive time wasting and typing errors. Now the problem is solved in the Studio with the help of the single registered database repository.

 

Q: What is HTTP tunneling?

A: HTTP tunneling is a method that allows one 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 the direct connection is not available because of security reasons. The HTTP tunnel works the following way: all outgoing queries and commands sent be the client's software are encoded and transmitted through the HTTP\HTTPS protocol using port 80 to the specific script that decodes the received data and sends them to the processing 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 the Linux hosting solutions. Also, you need to upload the emsproxy.php script to your web-server to access it remotely (you can place it to the directory with other PHP scripts). If your web-server complies with the requirements and the script is installed correctly, you will see the message: "EmsProxy v 1.31" (version number can be different) in your browser when opening the http://<yourwebservername>/emsproxy.php page.

To register a remote database that will be accessed through the HTTP tunnel, you have to enter the following parameters in Register Database Wizard of SQL Manager for MySQL:

'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 the remote host.

'User name' is a MySQL user name.

'Password' is the password of the MySQL user.

Check "Use tunneling" and "HTTP tunneling".

'URL' is the address of emsproxy.php script (e.g. http://mywebserver /emsproxy.php).

 

Q: What is SSH tunneling?

A: SSH (Secure Shell Host) protocol is used to heighten the computer security when working with Unix systems in Internet. SSH uses several encryption algorithms of different reliability. The spread of SSH is also concerned with the fact that a number of Linux-like OS's (for example, FreeBSD) include SSH server in their standard integration. To get more information about this issue, please, visit https://www.openssh.org.

SSH tunneling feature of SQL Manager is a means for 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 the default port is closed for external connections due to some reasons. Connection over SSH tunnel works in the following way. First, a connection is established, and authentication between SSH client built in SQL Manager and the remote MySQL server is performed. Then all in- and outgoing information between the program and MySQL server is transmitted through SSH server with the help of the 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 Register Database Wizard of SQL Manager for MySQL:

'Host' is a host where MySQL server is located from the point of view of SSH server. If SSH and  MySQLserver are on the same machine, it is equal to SSH Host, or may be 'localhost'.

'Port' is a port of MySQL server on the remote host.

'User name' is a MySQL user name.

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

Check "Use tunneling" and "SSH tunneling".

'SSH host name' is the host where SSH server is activated.

'SSH port' is the port where SSH server is activated.

'SSH user name' is a user on the Linux/Windows machine (it is a Linux/Windows user, not a user of MySQL server)

'SSH password' is the Linux/Windows user password.

If necessary, check 'Use Private Key for authentication'.

 

Q: I work behind a firewall (router). Can I use SQL Studio to manage a remote MySQL server?

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

 

Q: I cannot connect to the local MySQL server. Why?

A: There are several reasons why you cannot connect to the local database.

 If during connection you get error "Can't connect to MySQL server on 'localhost' (10061)", then probably MySQL server is installed incorrectly or the service (usually named '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 using the Start item available in the context menu.

 If you get 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 changed it during installation. If you installed MySQL with default values, you should use the user name 'root' with the 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)

 

Q: I cannot connect to a remote MySQL server. Why?

A: There could be several possible reasons that prevent you from successful connection to the 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 have entered and also if the remote server is running. It often happens that the port through which the connection is set with MySQL server (normally, it is 3306) is closed for security reasons by a local firewall, corporate firewall or remote server firewall. The remote server port can also be closed by ISP, or TCP/IP protocol support is 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 tunneling.

If the error message says, "Access denied for user: root@host.domain" or "Host not allowed to connect to server", then the reason is that the user does not have permission to access the database.

 

Q: I can't configure the connection to MySQL server by SSH. Which values do I need to input on the SSH page of the Database Registration Info dialog?

A: To setup SSH connection correctly, input the following values into the corresponding fields within the SSH Tunneling section of the Database Registration Info dialog of SQL Manager for MySQL:

'SSH host name' is the host where SSH server is activated.

'SSH port' is the port where SSH server is activated.

'SSH user name' is a user on the Linux/Windows machine (it is a Linux/Windows user, not a user of MySQL server)

'SSH password' is the Linux/Windows user password.

If necessary, check 'Use Private Key for authentication'.

 

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: MySQL server uses client's login ('root' in your case) and the name of the host which it tries to set the connection from ('somehost.somedomain' in your case) for the client authentication. In your case the reason is that your 'root' user from the 'somehost.somedomain' host does not have permissions to access your MySQL server. It is 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 'root' user from the 'localhost' host which has the necessary permissions and allows you the access. To solve this problem you should grant the necessary permissions to user root@ somehost.somedomain. You can do this with the help of phpMyAdmin or with sql commands:

/*!50003 CREATE USER 'root'@ 'somehost.somedomain'*/

GRANT ALL PRIVILEGES ON *.* TO 'root'@ 'somehost.somedomain' IDENTIFIED BY 'user_password';

Or, you can apply to your system administrator. (More info at: https://dev.mysql.com/doc/refman/8.0/en/error-access-denied.html)

 

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

A: This error occurs because you do not have a permission to connect to the 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 ';

Or, you can apply to your system administrator. (More info at: https://dev.mysql.com/doc/refman/8.0/en/error-access-denied.html)

 

Q: I receive the following error message:"MySQL server has gone away". What am I doing wrong?

A: The most probable reason for this issue is the connection timeout. In this case you need to increase the value of the wait_timeout variable  when launching mysqld. The error also occurs in the following cases:

- Somebody (your database administrator) used the KILL sql or mysqladmin kill command to interrupt your query thread.

- You are trying to run a query after closing the connection with the server.

- You are trying to send a too large query to the server, e.g. you are working with very big BLOB fields or the INSERT command tries to insert too many records. You can increase the maximum allowed packet size by setting the max_allowed_packet variable whose default value is 1Mb.

If this error occurs during the data import or copy database process, you can lower the value of the "Record count per each data block" option.

 

Q: I receive error message 1251: "Client does not support authentication ...". What do I need to do?

A: Our program seems to be trying to use some old version of libmysql.dll client library. Please use the libmysql.dll file from the Studio installation package only. In order to solve the problem, you need to reinstall the Studio (all your settings will be saved).

 

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

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

 

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

A: There are two most common reasons for this issue.

  • If you use SSH tunnel to connect to your database, then most likely you indicated a 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. Keep in mind that it should be specified relatively 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 more than 128K of stack size to resolve a host name. 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.

 

Q: I'm trying to register a MySQL database located on a web-server, but I receive an error. I can access this database from my PHP scripts and the same login and password work perfectly. How can I access my website database with the help of SQL Studio?

A: The most probable reason of the problem is one of the following:

port 3306 used by MySQL could be blocked by a firewall installed in your network or by your hosting provider;

the TCP protocol is disabled on the remote MySQL server;

your user and host do not have the permission to connect to the server.

Scroll to top

 

Objects manipulation

 

Q: Which database objects will be accessible through SQL Studio?

A: You can work with any objects in MySQL databases such as tables, fields, indices, views, stored procedures, functions, triggers, etc.

 

Q: I have over a million records in the table, yet the data grid displays only 1,000 of them. What is the problem?

A: Please select the "Select all records from a table" option within the Grid section of Environment Options dialog in SQL Manager for MySQL. Note that fetching all records from a table may require considerable time.

 

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.
- Use SQL sorting in data view. With this option enabled, when the user sets data sorting, a new request is sent to the server, and the result is shown anew in the Grid. Enabling this option is very helpful with the 'Select only' mode enabled, but it increases the traffic. When this option is disabled, 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.
- Use SQL filter in data view. With this option enabled, 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. Enabling this option is also helpful when the 'Select only' mode is on, but it increases the traffic. When this option is disabled, the filtering is made on the client computer without any references to the server but only loaded data are filtered. That is, if you set 'Select only 1,000 records', then only these records will be filtered.
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
- Use SQL sorting in data view – ON
- Use SQL filter in data view – ON

 

Q: Which operations can I perform over database objects?

A: You can create new objects, drop and edit the existing ones. You can also copy the objects from/to databases located on one or different hosts.

 

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

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

 

Q: When creating a table like 'TestTable', it will get stored as 'testtable'. How can I get objects stored as I create them using SQL Manager?

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

 

Q: How can I customize data formats in a grid?

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

 

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

A: You need to set up the "Client charset" parameters to make the server return the information to the client (our program) in the needed code page. Please, open the Database Registration Info window and specify the "Client Charset" option to '"greek (ISO 8859-7 Greek)".

 

Q: I can't modify DDL. Why?

A: The DDL tabs of the SQL Studio editors are read-only. To modify an object, you can copy the text to the clipboard and modify it using SQL Editor or SQL Script.

Scroll to top

 

Query

 

Q: I'm new to SQL, and I can hardly compose the SQL statements by hand. Does SQL Studio have a visual query builder like in MS Access?

A: Yes, SQL Studio offers a powerful Query Builder that allows newbies to build complex queries visually, and drastically saves time for advanced users. All you need to do is to specify the tables that will be used in the query, their relations and the query conditions for the data you want to get. Visual Query Builder will generate the text of the query for you. You will only need to run it. You can see the result of your query and make corrections if necessary. The changes can be made from the visual builder or from the text editor. As changes made in visual query builder, the text editor will display them too and vice versa.

 

Q: Can I use the visual query builder in STUDIO to build the complex queries with unions and subqueries?

A: Yes, visual Query Builder can build complex queries including UNION operator and subqueries.

 

Q: Can I save a created query for future use?

A: Yes, of course. SQL Studio can save the needed query in the following ways:

1. ALL executed queries are automatically saved in the History. You can view the History and execute or edit any query you need.

2. You can add the frequently used queries to the list of Favorite queries.

3. You can save any query as an SQL-script into a file on your hard drive and load it when needed, or execute it with a command line SQL script tool.

4. You can save your query as a chart. In this case after the query is loaded into SQL Studio, the the chart will be displayed the way it has been created. SQL Studio allows you to save the query chart as a bitmap image.

 

Q: Can I run a query from the command line?

A: Yes, SQL Studio offers SQL Script tool for this purpose. It can also run a script from a file saved on your hard drive. This feature allows you to create queries and schedule their execution using Task Scheduler. Task Scheduler allows you to run your query when you need it and notifies you when done.

 

Q: Can I save a query result into a CSV or an XLS file?

A: Yes, SQL Studio has a powerful tool for exporting your data (including the query results). You can export the result dataset to MS Access, MS Word, CVS, XLS, HTML, RTF, DBF, XML and other formats. To ease the exporting procedure, Export Data tools are implemented.

Scroll to top

 

Reports management

 

Q: How can I create a report?

A: To create a report, you can use the Create Report Wizard or the Report Designer tool in SQL Manager for MySQL. To create a report based on a query, see Q. How can I create a report based on a query?

 

Q: How can I create a report based on a query?

A: To create a report based on a query, you can use the Create Report Wizard in SQL Manager for MySQL. 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 set options in other steps of the wizard and press Finish.

 

Q: Can I save my report as HTML, Excel or Word?

A: Yes, open the report by double-clicking on it in the DB Explorer of SQL Manager and select the 'Save report as' item in the Navigation bar or toolbar, and specify the format you want to save to in the "Save as type" field.

Scroll to top

 

Data manipulation

 

Q: What is the difference between the Export/Import functions in SQL Studio and stand-alone SQL Manager?

A: The Data Export/Import tools of SQL Studio include some additional features which are not available in SQL Manager for MySQL, 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;
  • a single registered database repository.

 

Q: I need to import data from a CSV file. How can I do it with the help of SQL Studio?

A: You can use Import Data Wizard for this purpose. It will help you to set all the necessary import parameters. Moreover, you can save these settings as a template and use them in the future. Besides, you can also import from MS Excel, MS Access, DBF, XML, TXT and other formats.

 

Q: How can I change the default directory where exported data will be 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'.

 

Q: For which purpose are the console versions of the utilities for manipulating data included in SQL Studio?

A: The main function of the console versions of SQL Studio tools is execution of some service operations with the database without a user involved. All console tools support configuration files (templates) that are created in the corresponding GUI versions. The template file contains all information necessary for working with the console version. With the help of the console version and a template file you can create a task in the Task Planner and set the necessary time and/or period for execution. Of course, you can use the console versions of SQL Studio tools in your own automatic applications.

 

Q: I have a database in MS Access. I want to migrate to the remote MySQL database. How can I do it with the help of SQL Studio?

A: You can use Data Pump for MySQL for this purpose. To launch Data Pump from SQL Studio, select the Data Manipulation tab and click on Data Pump for MySQL, or select Start -> Programs -> EMS -> SQL Studio for MySQL -> Data Pump for MySQL. We recommend that you use Microsoft Jet 4.0 OLE DB Provider to connect to your MS Access database.

 

Q: I have a ODBC data source. Can I migrate from it to MySQL?

A: ODBC (Open Database Connectivity) is an open standard interface to access databases developed by Microsoft. Most database management systems have their own ODBC driver or the one produced by other software developers.

To migrate from a ODBC data source, you need to use Data Pump for MySQL. To launch Data Pump from SQL Studio, select the Data Manipulation tab and click on Data Pump for MySQL, or select Start -> Programs -> EMS -> SQL Studio for MySQL -> Data Pump for MySQL. When working with the utility, you should use OLE DB Provider for ODBC Drivers to connect to your ODBC data source.

 

Q: From which databases can I migrate to my database server with the help of SQL Studio?

A: With the help of Data Pump for MySQL you can migrate from any databases to which you can connect using OLE DB Provider (for example SQL Server, Oracle, MS Access, DB2, etc.) and ODBC Driver (MySQL, PostgreSQL, InterBase, Firebird, dBase files, etc.)

 

Q: I am working on an application. The database structure is already created. How can SQL Studio help me to fill the database with test data?

A: SQL Studio has a powerful instrument for generating test data - Data Generator for MySQL. The wizard will help you to set such parameters as the amount of records to be generated, range value for each field, the quantity of lines with NULL value and others. Data Generator performs various algorithms including random or increment data generation as well as the one according to the given list of values and many others. And of course, it is possible to save all these settings as a template for future use.

 

Q: My database contains many relations between tables and other constraints. Will SQL Studio consider them while generating test data?

A: Yes, of course. When generating test data, the Data Generator module of SQL Studio takes into consideration all relations and restrictions that already exist in your database.

Scroll to top

 

Data analysis

 

Q: I need to find the differences in the metadata of two databases and then synchronize them. How can SQL Studio help me with this task?

A: There is a tool specially designed for this purpose - DB Comparer for MySQL available in the Data Analysis group of SQL Studio. Its interface is simple and user-friendly, but the results of its work are quite impressive! You only need to choose two databases to compare metadata and set some additional parameters, such as database objects to compare, direction of synchronization and others. The result of DB Comparer's work will be a list of differences in the metadata of these two databases and a script, with the help of which you can synchronize the databases. Besides, you can synchronize each difference separately, using only the needed changes in the required order. Do you remember how much time you spent searching for the single field added to the table two months ago? :)

 

Q: I need to find the differences in the data between the existing database and its yesterday's backup. What should I do?

A: There is a tool specially designed for this purpose - Data Comparer for MySQL available in the Data Analysis group of SQL Studio. You should specify the existing working database as a source database. Then specify the database of the yesterday's backup as a target database (you should first restore it on any available MySQL server). Now choose data in which tables you would like to compare and follow the other steps of the wizard. Mind, that Data Comparer allows comparing data in tables with non-identical metadata. After the comparison is complete you will see the list of differences between your databases and will be asked if you would like to synchronize data from source to target or from target to source. You can save SQL script that performs synchronization in a separate file and run it later with the help of SQL Script tool (probably using the Task Planer).

 

Q: Can I start synchronization of metadata or data from the command line?

A: Yes, for this purpose you can use the console versions of our utilities - DB Comparer Console Application and Data Comparer Console Application. All console tools support configuration files (templates) that are created in GUI versions of the programs. The template file contains all the information necessary for working with the console tool. With the help of the console version and the template file you can easily perform synchronization from a command line without setting a great number of parameters manually.

 

Q: Can SQL Studio synchronize my database with a database of another DBMS?

A: This feature is not supported by our utilities as they allow synchronizing data and metadata only between one database engine, that is between MySQL and MySQL.

Scroll to top

 

Backup/Restore

 

Q: What Backup capabilities does SQL Studio provide?

A: SQL Studio offers several methods of creating database backups.

This method can be used for creating backups of MyISAM tables. It is characterized by high speed and reliability. When creating a backup in this way, MyISAM tables selected by a user are blocked (LOCK TABLES SQL command) and then the files where structure (.frm) and data (.MYD) are physically kept, are copied according to the specified path (BACKUP TABLES command). The latter can be either a local directory on the server or a shared folder in the network. This path is defined from the server side where MySQL is installed.

To call this service, open the Database Management tab in SQL Studio and select the Backup Tables item of the Services group.

To call this service from SQL Manager for MySQL, use the Services | Backup Tables... main menu item.

In order to restore the tables from the backup files created with this method, use the Restore Tables service. This service restores MyISAM tables (RESTORE TABLES SQL command) previously saved by Backup Tables service. Restore Tables service does not overwrite the existing tables.

  • Using DB Extract for MySQL or Extract Database Wizard of SQL Manager for MySQL

Both tools create backup copies of the whole database or of user-specified objects in the form of SQL script. The generated script contains Data Definition Statements and Insert Data commands. The main advantages of this method are the complete or partial backup of the database structure and data and flexible customization possibility. However, with this method creating back up copies of large databases is time and traffic consuming.

To call DB Extract, open the Data Analysis tab in SQL Studio and select DB Extract for MySQL.

To call Extract Database Wizard, open SQL Manager for MySQL and select the Tools | Extract Database main menu item.

In order to restore the database from the script, you need to run this script on the MySQL server. Please use SQL Script available  in SQL Studio or SQL Script tool of SQL Manager for MySQL.

  • Using Data Export for MySQL

This method can be used to back up data from any tables. Please use it if you need to save only data but not the database structure. We would recommend to use CSV format to back up data.

To call Data Export, open the Data Manipulation tab in SQL Studio and select Data Export for MySQL.

To call Data Export Wizard from SQL Manager for MySQL, open your table in Table Editor, proceed to the Data tab and select 'Export Data'.

In order to restore data from the file created with this method, you can use Data Import for MySQL or Data Import Wizard of SQL Manager for MySQL.

To learn about the differences between Data Import for MySQL and Data Import Wizard of SQL Manager for MySQL, see Q. What is the difference between...

  • Using Save Data Wizard of SQL Manager for MySQL

It is almost the same as the previous method. The only difference is that data are saved in the directory on the server or in the shared folder in the local network with the help of the SELECT INTO … OUTFILE SQL command. The data are saved in CSV format, but the speed of creating backups and their restoring is much higher then in case of using Data Export. However, this method does not allow saving files in any client folder.

To call Save Data Wizard from SQL Manager for MySQL, please, open your table in Table Editor, proceed to the Data tab and select the "Save data to file on server".

In order to restore the data, use Load Data Wizard.

 

Q: Is it possible to restore the backup on one server while it was created on another one?

A: Please observe the following cases:

  • You can restore a backup file, created by Backup Tables service on the server if its version (major and minor part) coincides with the server version where this backup was done. In other cases some problems may appear due to the differences in MyISAM file formats in different MySQL server versions.
  • You can restore a backup file created by DB Extract for MySQL or with the help of Extract Database Wizard of SQL Manager for MySQL on any server that has the same or higher version than the one where this backup was created.
  • The Data backed up with Data Export for MySQL or Export Data Wizard of SQL Manager for MySQL can be restored to a table on any version of MySQL server.

 

Q: Where can the backup file be located?

A: A backup file created by Backup Tables Service can be found in the local directory on the server or in the shared folder in the network.

 

Q: What is the difference between Extract and Backup functions in SQL Studio?

A: Backup Tables service can be used only for creating backups of MyISAM tables. When creating a backup in this way, MyISAM tables chosen by a user are blocked (LOCK TABLES SQL command) and then the files where structure (.frm) and data (.MYD) are physically kept, are copied according to the chosen path (BACKUP TABLES command). The latter can be either a local directory on the server or a shared folder in the network. This path is defined from the server side where MySQL is installed.

DB Extract for MySQL or Extract Database Wizard of SQL Manager for MySQL creates backups of databases or user-defined objects in a form of SQL script. Thus, Backup Tables service uses MySQL server capabilities to create backups of only structure and data of MyISAM tables. This service is characterized by high speed and reliability, but it cannot place backup files in any client folder. This method can be used for creating backups of a whole database, that contain only MyISAM tables and no other objects, or of database parts (only MyISAM tables). The main advantage of creating backups with DB Extract for MySQL is the possibility of saving database structure and data as a whole as well as partially. The weak point of this method is much time and traffic that are required for creating backups of large databases.

Scroll to top

 

Templates management

 

Q: What are the templates used, for example, in export/import? What are they for?

A: Templates in SQL Studio are intended for storage of the parameters required for running SQL Studio tools. Using the templates you will be able to repeatedly perform one and the same operation without a need to enter 10-20 wizard parameters each time. The parameters stored in a template can be easily modified, or you can create a new template based on the existing one.

 

Q: Where are SQL Studio templates located?

A: A template is a file that can store necessary parameters in certain format. Template files can be located anywhere on your PC. To simplify the task of template storage and search, SQL Studio provides Template Manager. The Manager allows you to register the necessary templates in SQL Studio and not to worry anymore about the place where you saved them to. When you create a new template in SQL Studio the Template Manager automatically remembers its location on the disk. All the registered templates are grouped by the tools they are intended for and will be at your disposal any time. To run a tool with the selected template, you will just need to double-click the template.

 

Q: If I delete a template from the list in SQL Studio, will it be deleted from the hard disk?

A: Template Manager allows you to perform both operations: you can either delete a template from the registered template list only, or delete it also from the hard disk.

 

Q: How can I create a new template for Import?

A: To create a template for the Import tool, you should run the Import tool (Run | Data Import for MySQL). Then you will need to fill in all the necessary import parameters using the Next button. At the last wizard step you should define the name and location of the newly created template using the Tools | Save Template menu item. See Using templates for more information.

Scroll to top

 

Scheduler

 

Q: I need to perform certain operations on my database (export from one database, import of those data to another one, backup of both databases) outside business hours (at night). Does SQL Studio provide any solution for this?

A: Yes, sure. The Studio Scheduler is intended for such situations. The Scheduler can run for execution of the previously created tasks. You can set the time and periodicity for the task execution. Each Task can include an unlimited number of Steps (elementary operations). A Task must include at least one step. In your case you should perform the following:

1. Create new Task

2. Create steps in the Task (Export, Import, etc.). When creating these steps, you will need to indicate in what template files the necessary tool parameters are saved.

3. Create a Scheduled Task in the Scheduler that will use the Task you have just created (1).

 

Q: How do I configure a task so that I receive an e-mail notification at the defined address in case of the task failure?

A: In the Task editor there is a tab called Alerts. By switching to this Tab you will be able to define the most suitable notification method to be informed of successful task execution and/or of the task failure. The current version of SQL Studio supports the following notification types: Net Send, System App Log (WIN), e-mail.

 

Q: Can the task process be changed depending on the results of the task steps?

A: Yes. When creating/editing each Task Step, you can define which step should go next. Moreover, for each of the three processed events (successful step termination, erroneous step termination, step termination by time-out) you can indicate a separate variant of the next Task operations.

 

Q: How can I view the information on the performed tasks?

A: All the information on the Task execution is fixed in the log. In the log you can find not only the information on the Task, but also on the Steps it includes. Moreover, you can view the log file of the attachment executed at a certain step. It is very convenient while analyzing the reasons of the Task/Step failure. The powerful log filters will allow you to display only selected records.

 

Q: What will happen if during a task execution the running application cannot terminate (critical error, does not respond)? Will the task execution stop?

A: To resolve such conflicts, in every Task Step you can define its time-out. If a Step is being executed longer than it is defined in the time-out, the Step will terminate with the "by time-out" event, and the Task execution goes on according to the algorithm you set.

 

Q: What do I need to work with Task Scheduler?

A: To work with Task Scheduler, you need to run the Studio Agent (a shortcut in the task bar). Also, your computer should be running during all the time while the tasks will be run.

 

Q: What will happen if, after running the computer, Task Scheduler finds the tasks that should have been executed in the past, but it has not happened?

A: Those tasks will not be performed.

Scroll to top

 

Logs management

 

Q: It is important for me to get the information from log files of applications executed by the Task Scheduler? Does SQL Studio save these log files?

A: Yes, SQL Studio has a convenient tool to work with log files. While creating a Step in the Task, you can specify the location of the log file of the application to be executed. When choosing the tools from SQL Studio, the location of the log file is filled automatically. Log files of the performed steps are saved in the internal SQL Studio database and are available within the Tasks | Logs menu.

 

Q: The external application that is executed in one of the tasks does not have a log file, but only a screen log. How can I save the data from the screen?

A: If a utility does not have a log file, and the log information is only displayed on the screen (in text mode), then SQL Studio can save these data as a log. To do so, please check the "Save screen to log" box when creating a task.

 

Q: How long are log files stored in SQL Studio?

A: Log files are stored in SQL Studio during an unlimited period. You can delete the unnecessary log files manually in SQL Studio (within the Logs tab).

Scroll to top

 

Localization

 

Q: What interface localizations are available in SQL Studio?

A: The current version of SQL Studio supports the following interface localizations: English, German, French and Russian.

Scroll to top

 

 

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