Copyright © 2004 MySQL AB
This manual is NOT distributed under a GPL style license. Use of the manual is subject to the following terms:
Conversion to other formats is allowed, but the actual content may not be altered or edited in any way.
You may create a printed copy for your own personal use.
For all other uses, such as selling printed copies or using (parts of) the manual in another publication, prior written agreement from MySQL AB is required.
Please email docs@mysql.com for more information or if you are interested in doing a translation.
Table of Contents
- 1. Introduction to the MySQL Query Browser
- 2. Installing MySQL Query Browser
- 3. Starting MySQL Query Browser
- 4. A Tour of the MySQL Query Browser
- 5. Using The MySQL Query Browser
- 5.1. Entering and Editing Queries Manually
- 5.2. Building Queries Visually
- 5.3. Navigating Result Sets Within the Result Area
- 5.4. Exporting Result Sets
- 5.5. Handling BLOB and TEXT Columns
- 5.6. Editing Result Sets Within the Result Area
- 5.7. Comparing Result Sets
- 5.8. Creating Master-Detail Views
- 5.9. Editing Queries From a Development Tool
- 6. The MySQL Table Editor
- 7. Options Dialog
- A. Installing From Source
- B. Troubleshooting Application Errors
- C. How Connections Are Stored
- D. XML Files Common to the MySQL GUI Applications
- E. XML Files Used by MySQL Query Browser
- F. Notes for Translators
- G. Third-party Software Used by the MySQL GUI Tools
List of Figures
- 3.1. Connection dialog
- 4.1. The MySQL Query Browser main query window
- 4.2. The query area expanded to ten lines
- 4.3. The MySQL Query Browser status icons
- 4.4. The advanced toolbar
- 4.5. The database browser
- 5.1. The table tool
- 5.2. The BLOB handling icons
- 5.3. The dynamic parameter list
- 6.1. The MySQL Table Editor
- 6.2. The column editor
- 6.3. The index editor
- 6.4. The Confirm Table Edit dialog
- 7.1. Options dialog
- 7.2. Options dialog: Connections
- B.1. Error message dialog
The MySQL Query Browser is a graphical tool provided by MySQL AB for creating, executing, and optimizing queries in a graphical environment. Where the MySQL Administrator is designed to administer a MySQL server, the MySQL Query Browser is designed to help you query and analyze data stored within your MySQL database.
While all queries executed in the MySQL Query Browser could also be performed in the mysql command-line utility, the MySQL Query Browser allows for the querying and editing of data in a more intuitive, graphical manner.
MySQL Query Browser is designed to work with MySQL versions 4.0 and higher.
MySQL Query Browser is to a large extent the result of feedback MySQL AB has received from many users over a period of several years. However, if you find it's lacking some feature important to you, or if you discover a bug, please use our MySQL Bug System to request features or report problems.
Table of Contents
The MySQL Query Browser is available for both Windows and Linux, in source and binary forms. MySQL Query Browser can be downloaded from The MySQL web site.
MySQL Query Browser runs on 32-bit Windows operating systems, including Windows 95, 98, Me, NT, 2000, and XP.
MySQL Query Browser is installed through the use of a Windows Installer (.msi) installation package, which can be used on all Windows operating systems. The MSI package in contained within an archive named mysql-query-browser-version-win.zip, where version indicates the MySQL Query Browser version. Download the ZIP file, extract it and change into the directory you extracted it into.
The Windows Installer system was updated with the release of Windows XP; those using an older version of Windows can reference this Microsoft Knowledge Base article for information on upgrading to the latest version.
To install MySQL Query Browser, right-click on the MSI file and select . The installation will begin automatically after the installer prompts you for your installation preferences. During installation, you can choose whether you want the installer to place a shortcut in the Start menu and an icon on the desktop.
If you are having problems running the installer, you can download a ZIP file without an installer as an alternative. That file is called mysql-query-browser-version-win-noinstall.zip. Using a ZIP program, unpack it to a directory of your choice. You may want to create shortcuts to MySQLQueryBrowser.exe for your desktop or quick launch bar.
Unless you choose otherwise, MySQL Query Browser is installed in C:\%PROGRAMFILES%\MySQL\GUITools\MySQL Query Browser\MySQLQueryBrowser.exe, where %PROGRAMFILES% is the default directory for programs on your machine. For example, this directory might be C:\Program Files or C:\programme.
MySQL Query Browser runs on Linux machines that have a graphical desktop installed. It is designed to run under the Gnome desktop, but you may also use other desktops, such as KDE, as long as GTK2 is installed. It has been tested on Linux kernel versions 2.4 and 2.6, but it should also run on other versions, and even on a number of Unix-like operating systems.
To install MySQL Query Browser, first download the installation tarball. The tarball file is called mysql-query-browser-version-linux.tar.gz, where version indicates the MySQL Query Browser version (for example 1.0.5-alpha).
To see all files in the tarball, run this command:
shell> tar -tzf mysql-query-browser-version-linux.tar.gz
To install MySQL Query Browser, run this command:
shell> tar --directory=/opt -xzvf mysql-query-browser-version-linux.tar.gz
This installs the application binary in /opt/mysql-query-browser/bin. Change into that directory and run mysql-query-browser to start the application. You can replace /opt with your desired installation path.
Distribution-specific packages will be available at some point.
Table of Contents
The way you start MySQL Query Browser depends on the operating system you are using:
On Windows, start MySQL Query Browser by double-clicking its desktop icon, or by selecting its entry from the Start menu (typically the entry of the section of the Start menu). Alternatively, you can open a DOS window and start the MySQL Query Browser from the command line:
C:\%PROGRAMFILES%\MySQL\GUITools\MySQLQueryBrowser.exe
%PROGRAMFILES% is the default directory for programs on your machine. For example, C:\program files or C:\programme. If your path contains spaces, you should enclose the command within double quotes. For example:
C:\> "C:\program files\MySQL\GUITools\MySQLQueryBrowser.exe"
On Linux desktops, start MySQL Query Browser by changing directories into /opt/mysql-query-browser/bin, and then executing mysql-query-browser.
Once MySQL Query Browser has been started, it displays a connection dialog. You have to specify the MySQL server to which you would like to connect, the credentials needed for authorization on that server, which machine that server runs on (and which port it listens to), and the default database (Schema) you will be querying from. You may also specify a number of other options, if required.
You must choose a default database in order to issue queries. It is possible to choose a default database after connecting to the server, but setting the default from the connection dialog can save time on subsequent connections.
If the server connection is successfully established, all of the values filled in the fields of the connection dialog is saved for future connections (see the section that describes how MySQL Query Browser stores connection information). The Password field, however, is always empty: For security reasons, the password is not stored along with the other options, unless you explicitly specify otherwise in the General Options section of the Options dialog.
If you have difficulties connecting, please see the Troubleshooting Connection Errors section in the Troubleshooting Application Errors appendix.
You can change any of the values in the connection profiles just by overwriting the existing values with new ones. Similarly, if you select another connection profile, you can change any of its values. When you click the button after changing a profile's values, the changes are stored permanently if a successful connection to the MySQL server is established. You can also click the button next to the Connection drop-down box. This brings up an Options dialog window that has a Connections section for modifying connection profiles. You can find the values you can set in the Connection dialog window in the description of the Connections section of the Options dialog.
Table of Contents
Once you successfully connect to a MySQL server you are presented with the main query window of the MySQL Query Browser. All of the application's functionality is available through this window.
[MH] OUTDATED FIGURE AS OF QB 1.0.8.5 The main query window is divided up into several sections:
Query Toolbar: The query toolbar is where you create and execute your queries. It is composed of three navigation buttons (, , ), the query area, two action buttons ( and ), and a status indicator.
Advanced Toolbar: The advanced toolbar contains three sets of buttons: the Transaction Buttons (, , ), the query management buttons (, ), and the query building buttons (, , , and so on.)
Result Area: All query results are displayed in the result area. You can have multiple tabs active at one time, allowing you to work on multiple queries. The result area can be split vertically and horizontally for performing comparisons, and queries in different parts of a split result area can be joined together for master-detail analysis.
Object Browser: The object browser is part of the sidebar and allows you to manage your databases, bookmarks, and history. You can choose which database and tables to query, add commonly-used queries to a collection of bookmarks, and browse through previously issued queries to use them again.
Parameter and Syntax Browser: The parameter and Syntax Browser is part of the sidebar and is used to look up built-in functions, and to manage queries that contain parameters. One example of this is when establishing a master-detail pair of queries. The data in the master query is available as parameters to the detail query.
Most of these sections can be displayed and hidden using the menu by checking and unchecking the elements you wish to view.
Each of these topics are covered in more detail in the sections that follow.
All queries, whether generated automatically, graphically, or manually, appear in the Query Toolbar. The simplest way to use the MySQL Query Browser is to type a query into the query area and click the execute button. A statement terminator such as ; or \G is not required.
To the left of the query area are the navigation buttons. The navigation buttons allow you to browse through your query history so that you can review and re-execute a previously executed query. The query history is available through the object browser.
Clicking the button loads the previous query in your history, wheras the button loads the following query. Only queries that execute without errors are added to your history.
As you navigate with the and buttons, the queries you navigate through are not executed unless you explicitly execute them by clicking the button. The button re-executes the last executed query, which may or may not be the current query in the query area.
The query area is where the text of all queries and statements are displayed. The query area is three lines high by default and automatically expands to a maximum of ten lines in height. For queries longer than ten lines, the query area is scrollable.
The following commands are available by right-clicking the query area: (Ctrl+X), (Ctrl+C), (Ctrl+V), (Ctrl+N), (Ctrl+O), and (Ctrl+S). The Open and Save commands load the contents of an SQL file into the query area or save the contents of the query area into a text file.
To the right of the query area are the action buttons. The button executes any queries in the query area, wheras the button ceases execution.
If you click the down-arrow below the button, there are three potential execution options:
(Ctrl+Enter): This executes the query and displays the results in the currently active result area.
(Ctrl+Shift+Enter ): This executes the query and displays the results in a newly created result area.
(Ctrl+Alt+Enter ): This splits the result area horizontally and then displays the query results in the lower half of the result area.
To the right of the action buttons is the status indicator. The status indicator shows the standard MySQL logo when the MySQL Query Browser is idle, and displays an animated logo when the MySQL Query Browser is busy processing a query. Both icons can be seen in the following illustration:
Below the query bar is the Advanced Toolbar. The Advanced Toolbar contains a set of buttons for transaction control, query management, and query building.
The left panel of the advanced toolbar contains the transaction control buttons. From left to right, the buttons allow you to start, commit, and roll back a transaction. As when using the command-line client, you can only use transactions with table handlers that support them (InnoDB for example). More information on transactions can be found in the the MySQL Reference Manual.
The center panel provides buttons for query management. The button can be used to get the EXPLAIN output for the current query from the MySQL server, wheras the button allows you to compare the results of two queries.
The right panel contains the query building buttons. You can use these buttons to build a query visually by clicking on the tables and columns that you wish to involve in your query, using specialized mouse pointers to indicate which part of the query the different fields and tables occupy.
All query results are displayed in the result area. Within the result area you can use multiple tabs, and individual result areas can be split either vertically or horizontally. New tabs can be created either by choosing the option on the query bar, or by clicking the button at the top of the result area. In addition, you can also right-click on the current result area and choose the option.
Individual tabs can closed by either right-clicking within the result area and choosing the option, or by clicking the red X icon on the tab you wish to close.
To split a result area, right-click on it and choose either the or the option. After splitting the result area you can then choose one half of the result area and view result sets within it. You can remove sections of the result area by right-clicking on the section you wish to remove and clicking .
The result area can be used to review and edit the results of a query, with editing permitted as long as the query is based on a single table and there is sufficient key information to uniquely identify rows. [MH] RESULT SETS BASED ON JOINS WILL EVENTUALLY BE EDITABLE, UPDATE THIS WHEN IT IS CHANGED! To edit the contents of the result area you must enable edit mode through the use of the button at the bottom of the result area. Any edits you make are not immediately applied, but instead you need to click the button next to the button. Clicking the button throws away any changes you have made to the data.
The object browser allows you to browse your server's databases, your bookmarks, and your query history.
The database (schemata) browser is the primary screen of the object browser. You can use the database browser not only to select tables and fields to query, you can also edit tables, create new tables and databases, and drop tables and databases. The database browser is also one of the places where you can set the default database, which is a required action before you can start issuing queries.
You can filter the databases shown by using the search bar at the top of the database browser. As you fill in the search bar, databases that do not match your search are hidden from view. You can clear the search bar by clicking the button on the right side of the search bar. All databases will once again be displayed.
To view a database's tables click the black arrow on the left of the database name. You can view a table's columns by clicking on the black arrow to the left of the table name. You can also double-click on a database or table name to expand the database or table contents. Fields that form part of an index have a small key icon to the left of their name, otherwise they have a blue diamond icon.
To create a new database, right-click within the database browser and choose the option. You can create a new table by right-clicking the database you wish to add a table to and choosing the option. You can drop tables and databases by right-clicking on the table or database you wish to drop and choosing either the or option.
You can edit a table by right-clicking on it and choosing the option. The MySQL Table Editor is identical to the one used in the MySQL Administrator application. For more information on editing tables, see The MySQL Table Editor.
You can place your more commonly used queries in bookmarks so that you can quickly retrieve them and re-use them later. To add a query to your bookmarks highlight and drag it from the query area into the bookmark browser.
Your bookmarks can be organized into folders and subfolders to help with management of your queries. To add a new subfolder right-click on an existing folder and choose the option. You can remove bookmarks and folders by right-clicking on them and choosing the option. After you confirm that you wish to delete the item it will be removed from your bookmark list. If you delete a folder all items and subfolders within the folder are also deleted.
With the history browser you can browse through all the queries you have previously issued. To expand a given day's queries, double-click on the day. To load a history item into the query area, double-click on it or drag it to the query area.
You can remove history items by right-clicking on them and choosing the option from the drop-down menu. You can also use the option to erase all history entries.
You can create bookmarks from history items by right-clicking on a selected history item and choosing the menu option.
Within the parameter and syntax browser are shortcuts to query elements that are not part of the tables themselves. The parameter browser provides different local, global, and dynamic parameters that can help build your queries, wheras the syntax browser provides a convenient reference to the built-in functions of the MySQL server.
The parameter browser contains all the local, global, and dynamic parameters that can be included in your query. At the moment only the dynamic parameters are available, which allow you to create master-detail queries. [MH] UPDATE AS NECESSCARY
The syntax browser provides a quick reference tool for determining proper syntax to use when constructing queries. By double-clicking on the various types of syntax (SELECT, REPLACE, and so forth) you can bring up reference information using the in-line help system.
Table of Contents
- 5.1. Entering and Editing Queries Manually
- 5.2. Building Queries Visually
- 5.3. Navigating Result Sets Within the Result Area
- 5.4. Exporting Result Sets
- 5.5. Handling BLOB and TEXT Columns
- 5.6. Editing Result Sets Within the Result Area
- 5.7. Comparing Result Sets
- 5.8. Creating Master-Detail Views
- 5.9. Editing Queries From a Development Tool
In this chapter we further describe the tools available in the MySQL Query Browser through practical examples of their use.
The most common task performed with the query browser is that of executing queries and analyzing their results. The most direct way to create a query is to type it directly into the query area. As you type in your query, the SQL syntax portions of the query(SELECT, FROM, WHERE, and so on.) are highlighted in blue and changed to uppercase.
Once you have entered your query, click on the button and your query results are displayed in the result area. You can also press Ctrl+ Enter to execute the query. If there is an error with your query an error area appears at the bottom of the result area and displays the relevant error message and error number.
In addition to loading the query results into the current active result area, you can also create a new result area for the results of your query or split your current result area and load results into the new section.
To execute the query and load the results into a new result area click the down-arrow below the button and choose the option or press Ctrl+Shift+Enter .
To split the active result area and display the query results click the down-arrow below the button and choose the option or press Ctrl+Alt+Enter .
You must set a default database before you can query the database successfully. You can set the default database at the connection screen, by right-clicking on a database in the database browser and choosing , or by choosing the option from the menu.
One feature of the MySQL Query Browser is the ability to build queries visually. This allows you to select the columns and tables you wish to query from the database browser and have the query created automatically based on your choices.
The first step to creating a query visually is to choose a table to query. Click and drag the table you wish to query to the query area to start a query. For example, by dragging the City table to the query area, SELECT * FROM City C is added to the query area. You can also double-click a table to start a new SELECT query.
You can drag additional tables to the query area while holding the Ctrl or Shift keys to add them to the FROM clause. By holding the Shift key while dragging, the tables are joined. By holding the Ctrl key while dragging, a LEFT OUTER JOIN is performed. The tables are joined based on identical column names for MyISAM tables and foreign key information for InnoDB tables.
When you select a table from the database browser and drag it over the query area, a table tool with some query composition actions is displayed. Drop the table you're dragging on the desired action and the query is modified accordingly. The following actions are possible:
SELECT replaces the current statement with a SELECT query containing the dragged table.
Add Table adds the dragged table to the list of tables in the current SELECT query
JOIN Table: If a SELECT query is already in the query box, with a table in it, the new dragged table is added and the appropriate WHERE clauses to perform a JOIN will be added
LEFT OUTER JOIN is the same as the previous, but does a LEFT OUTER JOIN instead of a JOIN
UPDATE replaces the current statement with an UPDATE statement containing the dragged table
INSERT replaces the current statement with an INSERT statement containing the dragged table
DELETE replaces the current statement with a DELETE statement containing the dragged table
Once a table is selected, you can choose specific columns to query; click the button from the query building buttons on the button bar. Your mouse pointer is changed to a Select pointer, which you can use to choose columns from the database browser. For example, by clicking on the Id, Name, and Country fields of the world sample database, the query SELECT C.Id, C.Name, C.Country FROM City C is built in the query area.
Once you have chosen the columns you wish to query, you can use the other query building buttons to complete your query with WHERE, GROUP BY, and ORDER BY clauses. When a new section of the query is added with the query building buttons, the cursor in the query area is placed in position for editing; if you click a field with the WHERE pointer, the cursor is in position for you to type in the details of the WHERE clause.
You can change between the different query building pointers by clicking on the query building buttons in the button bar, or by using a combination of Ctrl+Alt and the first letter of the pointer you wish to use (Ctrl+Alt+S for SELECT, Ctrl+Alt+W for WHERE, and so forth.)
Once you have successfully executed a query you are then able to view and manipulate the result set within the result area.
You can navigate the result area using the arrow keys, tab key, and PageUp/PageDown keys. The Home and End keys can be used to move to the first and last column within a given row. The and buttons at the bottom of the result area can be used to move to the first and last rows of the result set. The button at the bottom of the result area can be used to find a specific value within the result set.
You can export any result set from MySQL Query Browser by right-clicking within the result set and choosing an option from the sub-menu. You can choose to export the result set in CSV, XML, HTML, or Microsoft Excel XLS formats.
The MySQL Query Browser provides functionality for dealing with BLOB and TEXT columns through a series of special icons.
These icons appear in any BLOB or TEXT columns in your result set. From left to right the following icons are available:
Open File: This icon looks like a file folder and is used to open a file for loading into the field.
View: This icon looks like an eye and is used to open the field viewer to view the contents of the field. The field viewer can be used to view TEXT fields and BLOB fields that contain images.
Edit: This field looks like a wrench and opens the field viewer in edit mode, allowing you to make changes to the data and apply the changes to the result set.
Save: This icon looks like a floppy disk and is used to save the contents of a TEXT or BLOB field into a file.
Clear: This icon looks like a red X within a red box and is used to clear the contents of a TEXT ot BLOB field.
Only the View and Save icons are visible is you have not enabled editing for your record set. See Section 5.6, “ Editing Result Sets Within the Result Area ” for information on editing result sets.
When a query is based on a single table, with sufficient identifying information, the result set can be edited from within the result area. [MH] THIS WILL CHANGE, UPDATE WHEN APPROPRIATE To edit a result set click the button. If the button is not active, your result set is not editable.
Once in edit mode you can insert, update, and delete rows from the result set visually. You can navigate the fields with the Tab and arrow keys, and pressing Enter allows you to edit the content of a field. You can also double-click a field to make it editable. When editing a field, the tab key will move you to the next field in an editable state. All edited fields are highlighted in blue for easy identification.
To add rows to the result set, scroll to the bottom of the result area and fill in the fields of the blank row found there. All new rows are highlighted in green.
To delete a row right-click on the row and choose the option. All deleted rows are highlighted in red.
Changes made to the result set are not applied immediately, but instead are cached until the button is pressed. You can abort your edits with the button. Exiting edit mode without choosing to apply or discard your changes results in you being prompted to apply or discard your work.
You can compare result sets graphically with the MySQL Query Browser, allowing you to easily determine where rows have been inserted, updated, or deleted.
To compare two result sets, execute the first of the queries you wish to compare. Once the result set has loaded, right-click on the result set and choose the option. Load your second query into the new section of the result area and click the button to compare the two result sets.
When you have activated the compare mode, both result sets will scroll in unison, both vertically and horizontally. Rows are matched for comparison, with blank rows added when one set has a row that the other set lacks.
If one result set has a row that the other result set does not have, that row is highlighted in green. The other result set has a blank row inserted that is highlighted in red. If both result sets have a matching row, but individual fields are different, those fields are highlighted in blue.
In order to successfully compare two result sets, you need two queries with matching column names. The tables which the data is based on need to have primary keys defined in order for the MySQL Query Browser to match rows. Fields in your result sets must have the same names, and must be in the same order.
MySQL Query Browser makes it easy to view data that is in a master-detail (one to many) relationship. This can be useful for viewing customer/order data, group/member data, and so forth.
To display a master-detail view, first query the master table in a new result area. For example, using the world sample database, you could issue a query like SELECT Code, Name, Continent FROM Country.
Once you have created the master result set, right-click on the result area and choose the option. For the detail query, create a query that uses the dynamic parameters available from the master query, as seen in the parameter browser. In this example, we could query the City table like this: SELECT Id, Name FROM City WHERE Country = :Code.
The colon character indicates to the MySQL Query Browser that you are adding a dynamic parameter, and a pop-up list of available parameters should appear within the query area, allowing you to choose a parameter using the arrow keys. You can also type the name of the parameter that you would like to use to link the detail query.
Once you execute the detail query it will automatically refresh any time you change the active row in the master query, allowing you to quickly see all detail rows as you navigate the master result set.
You can repeat this process, splitting the result area additional times, allowing you to have detail result sets for detail result sets.
In order to help programmers optimize and troubleshoot their queries more efficiently, the MySQL Query Browser can copy queries from application code using your favorite IDE.
This functionality is only available for the Windows version of MySQL Query Browser.
The following PHP code will be used as an example:
$SQL = "SELECT Id, Name, Country FROM City";
mysql_query($SQL,$db);
To copy the listing into the MySQL Query Browser, highlight the query from within your editor and press Ctrl + Alt + Shift + C. The non-query portions of the highlighted area will be stripped and the query will be pasted into the query area.
After editing the query, press Ctrl + Alt + Shift + V to load the modified query back into your original code. The surrounding PHP code will be re-inserted along with the modified query. This functionality allows you to edit queries quickly while programming.
In order to use this functionality your editing application needs to support the standard Windows clipboard system, which most Windows applications do.
Table of Contents
The MySQL Table Editor is a component of both the MySQL Query Browser and MySQL Administrator, and allows for the visual creation and modification of tables.
The MySQL Table Editor can be accessed from the MySQL Query Browser by right-clicking on a table within the database browser and choosing the option, or by right-clicking on a database within the database browser and choosing the option.
The MySQL Table Editor can be accessed from MySQL Administrator through the screen. Once you have selected a database, right-click on a table and choose the option from the drop-down menu. You can also select a table and click the button to access the MySQL Table Editor.
The MySQL Table Editor consists of a work space divided into three tabs, some general information prompts, and three action buttons.
Regardless of the tab you have active, you can always edit the table name, the database it belongs to, and the table comment.
The tabbed area is divided into three sections:
Columns and Indices: Use the Columns and Indices tab to create and modify the table's column and index information. You can also create FOREIGN KEY relationships using this tab.
Table Options:Use the Table Options tab to choose the storage engine used by the table and the table's default character set.
Advanced Options: Use the Advanced Options tab to configure advanced options such as per-table storage directory, MERGE and RAID table options, and table/row length options.
Each of these areas are discussed in further detail in the following sections.
The Columns and Indices tab can be used to display and edit all column and index information for your table. Using this tab, you can add, drop, and alter columns and indexes.
You can use the column editor to change the name, data type, default value, and other properties of your table's columns.
To change the name, data type, default value, or comment of a column, double-click on the value you wish to change. The value becomes editable and you can the complete your changes by pressing the Enter key.
To modify the flags on a column (UNSIGNED, BINARY, ASCII, and so on) check and uncheck the boxes corresponding to the flag you wish to change.
To the left of the column name is an icon that indicates whether the column is a member of the primary key. If the icon is a small key, that column belongs to the primary key, otherwise the icon is a blue diamond. To add or remove a column from the primary key, you can click on the icon.
The detail area of the Columns and Indices tab is divided into three tabs and is used for modifying the index, foreign key, and miscellaneous information regarding your columns.
The Indices tab holds all index information for your table. You can add, drop, and modify indexes using the indices tab, in combination with the column editor.
To add an index, click the (plus) button below the index list. You will be prompted for an index name and then the new index is created. To drop an index, select the index and click the (minus) button to drop the index from the index list.
Use the Index Name and Index Kind dialogs to modify the name and type (UNIQUE, FULLTEXT, and so on) of the index.
To add columns to an index, either click and drag the column to the Index Columns box or select the column you wish to add and click the (plus) button to the right of the Index Columns box. You can remove a column from the index by selecting the column and clicking the (minus) button to drop the column from the index.
To change an index to only refer to a column prefix (such as with the CHAR and VARCHAR string data types), select the index column you wish to prefix in the Index Columns box and then click the arrow icon to the right of the Index Columns box. Select the option from the drop-down menu that appears. You can set the desired prefix length in the dialog that appears.
The Foreign Keys tab is divided into two sections, one with a list of foreign keys and one with various dialogs for foreign key settings.
To add a foreign key, click the (plus) button below the foreign key list. You will be prompted to name the new foreign key, and it is then be added to the list.
To drop a foreign key, select the foreign key and click the (minus) button below the foreign key list. The foreign key is then dropped from the foreign key list.
You can modify the name of the foreign key, its ON DELETE, and its ON UPDATE actions using the dialogs provided in the Foreign Key Settings section of the tab.
To establish a foreign key relationship, choose a table from the Ref. Table drop-down list. The columns that can be referenced as foreign keys are listed in the area below, and you can drag a column from the column editor to the Column section to the left of the column you wish to reference.
The Column Details tab provides an interface for setting the parameters of a column without using the table interface of the column editor.
All settings that are available in the Column Editor are also available in the Column Details tab, and in addition you can also configure the column character set and column default collation from the Column Details tab.
The Table Options tab allows you to change the storage engine and character set of your table. The potential storage engines are listed, along with a brief summary of each storage engine's features and strengths.
To change the storage engine for your table, click on the radio button next to the desired storage engine.
To change the default character set or collation of your table, choose a new option from the drop-down list of available character sets.
The Advanced Options tab is used to configure table options that would be considered outside the standard set of options that most users designate when creating and modifying tables.
The Advanced Options tab is divided into several sub-sections, each of which is described in the upcoming sections of this manual.
The Various section of the Advanced Options tab contains options for you to set the PACK KEYS behavior, the table password, the initial AUTO_INCREMENT value, and the delayed key update behavior.
The AUTO_INCREMENT and delayed key update behaviors apply only to MyISAM tables.
The Row Options section can be used to configure options such as the row format, checksum use, and the row size parameters needed for large tables.
To set the row format, choose the desired row format from the drop-down list. See http://dev.mysql.com/doc/mysql/en/MyISAM_table_formats.html for more information on the different row formats that are available. This option only applies to MyISAM tables.
When you expect a table to be particularly large, use the Avg Row Length, Min Rows, and Max Rows options to enable the MySQL server to better accommodate your data. See http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html for more information on how to use these options.
The Storage Options section is used to configure a custom path to the table storage and data files. This option can help improve data integrity and server performance by locating different tables on different hard-drives.
This option is only available for MyISAM tables and is not available for servers running under the Windows operating system.
The Merge Table Options section is used to configure MERGE tables in MyISAM. To create a MERGE table, select MERGE as your storage engine in the Table Options Tab and then specify the tables you wish to MERGE in the Union Tables dialog.
You can also specify the action the server should take when users attempt to perform INSERT statements on the merge table. See http://dev.mysql.com/doc/mysql/en/MERGE.html for more information on MERGE tables.
The Table RAID Settings section allows you to configure RAID support for MyISAM tables. RAID allows MyISAM table data files to grow larger than the 2GB/4GB size limit imposed by some operating systems.
For more information on using RAID support with MyISAM, see http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html
The changes you make with the MySQL Table Editor are not immediately applied but are instead queued to be applied in batches after you have made all your edits.
To apply the changes you have made, click the button. The Confirm Table Edit dialog will appear.
You can click the button to confirm the changes and have them applied, or click the button to discard the changes. You can also click the button in the main MySQL Table Editor window to discard all changes you have made.
You can also copy the proposed changes to the clipboard for further editing by highlighting the ALTER TABLE or CREATE TABLE statement, right-clicking and choosing from the drop-down menu.
Table of Contents
The Options dialog allows you to configure connection profiles, general program settings, and more. You can open the Options dialog window using one of the following methods:
In the connection dialog window, click the button.
In the main application window select from the menu.
In the sidebar of that dialog, you can select the section you wish to configure. There are three action buttons in the lower right corner of the window:
applies and saves changes.
discards any changes you have made.
closes the Options dialog window. If you have not applied or discarded your changes, you will be prompted to do so.
The General Options section allows you to specify a number of settings that are valid for all graphical MySQL applications. At the time of writing, MySQL Administrator and MySQL Query Browser are the only programs in the MySQL GUI suite, but other programs will follow soon.
Store Window Positions: When an application is started the next time, its recent window positions will be reused.
Show Tip of Day: If checked, a pop-up window with the tip of the day appears at program startup.
Store Passwords: If checked, passwords are stored in the user's connection profile. In this case, you can select the password storage method:
Plaintext means they are stored without encryption; this can be insecure. Obscured means they are encrypted using a weak algorithm. This encryption method, however, is operating system independent. Selecting OS Specific will use the default encryption method provided by your operating system. The default option is Plaintext.
Language: Select the interface language. The default is English.
Default Font: The font used for all aplication text.
Data Font: The font used for all query and table data displayed.
Code Font: The font used for all queries entered by the user..
Ignorelist: Whenever you check the Do Not Show This Message Again option on error and message prompts, they are added to this list. If you would like a particular message to be shown again, click the button after selecting the message from the list.
You may change the font and font size of any of the application fonts by clicking the button to the right of the font.
The Connections section allows you to create, edit, and delete connection profiles. The center box displays a list of currently available profiles, together with a history of connections that were made without being stored in a profile. You can collapse or expand both the Connections and History trees by double-clicking them.
Connections are automatically added to the History tree whenever you establish a connection to a MySQL server without using one of the profiles stored under the Connections tree. They do not appear in the drop-down box of the Connection dialog, but you can use any of them by manually typing their name into the Connection box of the Connection dialog.
To edit an existing connection profile, click on its name and change the values that appear in the Connection Parameters and Advanced Parameters tabs, then click on the button to save your changes.
When you select a connection profile from either the Connections or History trees, the Connection Parameters tab displays the following fields:
Connection: The connection profile label. This is the name by which you refer to the profile and that appears in the Connection drop-down box of the Connection dialog. It may contain any characters, including spaces. Choose distinctive names so that you can easily tell which profiles they refer to. The names can help you distinguish connections to different MySQL servers, or connections as different MySQL users to a given server.
Username: The username used to connect to the MySQL server.
Password: The password used to connect to the MySQL server. Note that passwords are not stored in the connection profile, unless you specify otherwise in the General Options section.
Hostname: The name of the host machine where the MySQL server runs, or its IP address.
Port: The TCP/IP port that the MySQL server listens to on the host machine.
Type: Specifies the protocol used to connect to the database server. The default protocol is MySQL (which uses the native MySQL protocol). The other protocols listed are not currently available.
Schema: The default database for a connection when using the MySQL Query Browser.
Notes: You can use this field to enter comments or additional information describing the connection profile.
Note that the Advanced Parameters tab may not be available in some of the first releases of MySQL Administrator. If that is the case in the version you are using, you can still set those parameters in the Connection dialog. Use the button of that dialog to display the Advanced Connection Options.
When you select a connection profile from either the Connections or History list, the Advanced Parameters tab displays the following checkboxes:
Use compressed protocol: If checked, the communication between the application and the MySQL server will be compressed, which may increase transfer rates. This corresponds to starting a MySQL command-line tool with the --compress option.
Return number of found rows, not number of affected rows: By default, MySQL returns the number of rows changed by the last UPDATE, deleted by the last DELETE or inserted by the last INSERT statement. When this option is checked, the server returns the number of rows matched by the WHERE statement for UPDATE statements.
Ignore spaces after function names, make them reserved words: Normally, any refereence to a function name in an SQL statement must be followed immediately by anopening parenthesis. If this option is checked, spaces may appear between the function name and the parenthesis, like this:
COUNT (*)Enabling this option has the effect that function names become reserved words. This option corresponds to starting a MySQL command-line tool with the --ignore-spaces option.
Allow interactive_timeout seconds of inactivity before disconnect: Normally, the connection is closed by the MySQL server after a certain period of inactivity on the client side. This period can be set with the interactive_timeout variable. If checked, the server will not close the connection unless the period of inactivity exceeds the value set by interactive_timeout. This corresponds to starting a MySQL command-line tool with the --connect-timeout=seconds option.
Enable LOAD DATA LOCAL handling: By default, the LOCAL option of the LOAD DATA statement is disabled for security reasons. Enabling this option will allow to load data from the local machine (the machine where the client GUI application is running). This option corresponds to starting a MySQL command-line tool with the --local-infile=1 option. (Note that this option is ineffective unless the MySQL server allows LOCAL handling.)
The Editors section is used to configure options specific to the different editors available within the MySQL GUI Suite. At the time of writing this is limited to the MySQL Table Editor.
Show SQL command before applying changes toggles whether the MySQL Table Editor will show you the ALTER TABLE statement it is about to execute for confirmation when you click .
All columns Not Null per default determines whether the MySQL Table Editor will designate columns as being NOT NULL by default when creating new columns.
All integer columns unsigned per default sets whether integer columns are declared UNSIGNED by default when creating new columns.
PK Naming dictates the format that should be used to name PRIMARY KEY columns that are automatically generated. the %tablename% portion will be replaced with the name of the appropriate table.
Index Naming sets the name automatically generated for new indexes. The %nr% string will be replaced with an automatically incrementing number.
FK Naming configures the name used when creating new foreign keys.
PK Datatype is the datatype used when creating a PRIMARY KEY column.
Def. Datatype is the default datatype assigned to all new columns that are not part of a PRIMARY KEY.
The Browser section allows you to specify options that relate to the MySQL Query Browser. The Browser section is divided into three areas: Display Options, Query Options, and Various.
The display options affect the appearance of the MySQL Query Browser.
Show advanced toolbars turns on and off the display of the The Advanced Toolbar.
Hide the tab when only one tab is open determines whether or not to display a tab icon at the top of the Result Area if there is only one active result area.
Toolbars use gradient background enables and disables use of gradient effects in the background of the Query Toolbar.
Show field overlay images for long VARCHAR, TEXT, and BLOG fields toggles on and off the BLOB management icons.
Enforce queries to be editable by adding primary key columns to the select will add PRIMARY KEY columns to a query that does not explicitly SELECT them to ensure that the results of a query can be edited. The PRIMARY KEY columns will not be displayed in this case, but will still be stored.
Open resultset in associated application after export will cause the associated application to be opened after choosing the option from the right-click menu of the Result Area.
Table of Contents
Normally, you will want to install the MySQL GUI tools from binaries. We put a lot of effort into making sure that our binaries are built with the best possible options. If, for whatever reason, you wish to compile the tools yourself, follow these instructions.
You can get the source code of the MySQL GUI tools from our public BitKeeper trees. To be able to access trees, you have to have BitKeeper installed. BitKeeper is freely available from Bitmover. To run under Windows, BitKeeper requires Cygwin. If, during the installation process, BitKeeper finds that either Cygwin is not installed, or that the Cygwin version is too old, it will suggest to download and install Cygwin (or a newer version). You may, however, install Cygwin before installing BitKeeper. You can get Cygwin from Cygwin.com.
Our public BitKeeper trees that contain the MySQL GUI tools source code can be retrieved by the following BitKeeper commands:
bk clone bk://mysql.bkbits.net/mysql-administrator mysql-administrator
bk clone bk://mysql.bkbits.net/mysql-gui-common mysql-gui-common
bk clone bk://mysql.bkbits.net/mysql-query-browser mysql-query-browser
You will need the mysql-gui-common tree regardless of which GUI tools you intend to build. The mysql-gui-common tree contains source and graphics files used for all MySQL GUI tools.
You can also download the source for the various GUI tools from the downloads page.
Installing from source under Windows requires multiple commercial compilers and because of this it is preferable to install from binaries. If you meet the prerequisites needed you can build the MySQL GUI tools from source under Windows.
To compile the MySQL GUI tools from source under Windows, you need Borland Delphi 7, Microsoft Visual Studio.NET 2003, and some libraries which are available on the internet as open source software. Make sure the programs DCC32.exe and DEVENV.exe /? can be launched from the command line.
You will need the following libraries to build from source:
glib-2.0
libxml-2
mysql client libraries (4.0 or newer, 5.0 prefered)
pcre-1.4
MS Visual C runtime libraries 7.1
Java include files 1.4.2_04
Lua 5.0.2
Make sure you have cloned the mysql-administrator, mysql-gui-common, and mysql-query-browser trees, and that their local directories are located in the same directory, for example in Personal Files\MySQLGUI\.
The source files and libraries should be placed into the following tree structure:
Working Directory
|
|- mysql-administrator
|- mysql-gui-common
|- mysql-query-browser
|- mysql-gui-win-res
|
|-include
| |
| |-glib-2.0
| |-java
| | |
| | |-win32
| |
| |-libxml
| |-lua
| |-mysql
| |-pcre
| |-windows
|
|-lib
|
|-glib-2.0
|-java
|-libxml
|-lua
|-mysql
|-pcre
|-windows
To build from the command line open a windows command line prompt and change into the main repository of the tool (i.e. for MySQL Administrator this is work\mysql-administrator). There are three batch-files to start the build:
build_debug.cmd
build_release.cmd
build_commercial.cmd
Execute one of those to generate a runtime image of the application. It will be generated in a bin\windows directory.
Another method is to doubleclick on one of the build_buildtype.cmd files, where buildtype is one of debug, release, and commercial. The commercial build script is for users with commercial licenses, and makes use of the commercial version of the MySQL client library.
Before you can open any projects, you have to install these components. Otherwise some of the forms will not open and you will receive error messages.
To install the components, goto the mysql-gui-common\source\windows\ directory. Copy the following directories to your Delphi source directory (C:\Program Files\Borland\Delphi7\Source\).
png
SynEdit
TNT
UniCodeEditor
VirtualTreeview
Then doubleclick each *.dpk file outlined below. It will open Delphi. Press compile and install to install the components. Repeat this for each *.dpk file.
SynEdit\Packages\SynEdit_D7.dpk
TNT\Packages\TntUnicodeVcl_D70.dpk
VirtualTreeview\Packages\VirtualTreesD7D.dpk
UniCodeEditor\Packages\UniCodeEditorD7.dpk
These components have to be installed via the option of the menu. Click on the button and select the the files below. Press after each file.
png\PNGImage.pas
After installing the components you can open the projects as outlined below.
To build the application from the Delphi 7 IDE simply doubleclick the edit_xxxx.cmd. This will launch Delphi 7 and open the file. To generate the complete runtime image select option from the menu.
Like for building from command line there are three different files.
edit_debug.cmd
edit_release.cmd
edit_commercial.cmd
The runtime image will be generated in a bin\windows directory.
Opening the Delphi project not using the edit_*.cmd script will lead to incorrect project settings and problems while building the projects.
To be able to build the MySQL GUI tools from source, you need the following software:
g++ development suite
gtk-2.x
gtkmm-2.0 or gtkmm-2.2
libglade-2.x
libxml-2.6.x
mysql client libraries (4.0 or newer, 5.0 prefered)
pcre-1.4
autoconf-2.54, automake-1.7.x
In addition, you will need gtkhtml-3.0.x to build the MySQL Query Browser.
To build any of the tools, you need to have the bk tree for mysql-gui-common and the tree for the desired app (i.e.: mysql-administrator, mysql-query-browser) in the same top-level directory.
First build mysql-gui-common:
cd mysql-gui-common
sh ./autogen.sh
./configure --prefix=/opt/mysql-administrator
make
make install
The --prefix option can have whatever value you want, normally one would specify /usr/local (the default) or /usr, but for making precompiled binaries, something in /opt might be more appropriate.
After you have successfully built mysql-gui-common, you can build the individual tools (in this example we will build mysql-administrator):
cd mysql-administrator
sh ./autogen.sh
./configure --prefix=/opt/mysql-administrator
make
make install
If everything went well, things should be properly built and installed.
Building static binaries is non-trivial under Linux, due to the enormous amount of library dependencies that come from GNOME and associated libraries. Additionally:
Many libraries do not come with their static (lib*.a) versions shipped, so we need to build them ourselves.
Some libraries can't be linked statically, such as glibc, because of libnss*.so.
Some libraries depend on data files and modules from the original package, esp. in case of gtk/gnome. Problems don't just appear when the user don't have these data files installed; things may not work if they use distributions that put data files in different paths.
The current solution is to build a partially static binary, with the most common libraries left dynamically linked (glibc and gtk). gtkmm, gtksourceview, gtkhtml and dependencies (like gnome) are being linked statically. The ideal solution would be to build custom binaries for each major/chosen distribution, but we don't have time/resources for that at the moment.
To build these binaries:
Look at the source/linux/static_make script for all the files that should be removed/moved out of /usr/lib.
Run the script.
That's needed to force the linker to look for the static version of each library and also to explicitly list the dependency libs that were otherwise linked to the dynamic libs.
For building RPMs, a spec file is already supplied and will be made automatically after ./configure is ran. The spec file expects a source tarball with the following structure:
mysql-administrator/ mysql-administrator/mysql-gui-common/* mysql-administrator/mysql-administrator/*
The contents of each subdir is the entire bk tree for each tool (Windows specific files and bk metadata are optional, of course). You must make a tar.gz file and put it in /usr/src/redhat/SOURCES (or whatever is your rpm SOURCES directory).
Then, execute: rpmbuild -ba mysql-administrator.spec
That should build the srpm and rpm files for the tool.
If an error occurs when you select a connection profile and attempt to establish a connection to the MySQL server named in the profile, a window containing error information will be displayed (see figure).
Normally, this means either that the credentials specified in the profile are wrong (wrong username, wrong password), or that you do not have privileges to connect to the MySQL server from the client machine. You can find more information about the error in the Causes of Access denied Errors section of the reference manual.
However, another cause of connection failure is that there might be a network problem that prevents you from reaching the machine where the MySQL server is running. For this reason, the error dialog provides a button. Clicking this button will send an ICMP PING request to the MySQL server host machine. If the host machine is available over the network, you will see something like this:
Reply from 127.0.0.1: Time=0ms TTL=128
Reply from 127.0.0.1: Time=1ms TTL=128
This would indicate that the machine is reachable, and that the cause of connection failure is not a network problem.
If, under Windows, the controls in the windows look somehow displaced, select , , . Select the Settings tab and click the button. Change your DPI settings to Normal (96 DPI).
If, under Linux, messages and captions appear very big, your display resolution is probably set too high. Use gnome-font-properties to change your default font to a smaller value or go to Details and set your resolution to 72 or some other more appropriate value.
On Windows, connection profiles are stored in C:\Documents and Settings\UserName\Application Data\MySQL directory, where UserName is the name of the current Windows user. On Linux, the files are stored in the ~/.mysqlgui directory. The settings are stored in a file called mysqlx_user_connections.xml. The contents of that file look like this:
<?xml version="1.0"?>
<user_connections>
<last_connection>1</last_connection>
<password_storage_type>2</password_storage_type>
<user_connection>
<connection_name></connection_name>
<username>root</username>
<hostname>localhost</hostname>
<port>3306</port>
<schema></schema>
<advanced_options/>
<storage_path></storage_path>
<notes></notes>
<connection_type>0</connection_type>
<storage_type>2</storage_type>
<password></password>
</user_connection>
...
You can edit the file manually, but take care not to invalidate the XML. When applying changes by editing and saving the file, those changes will show up the next time you open the Connections section of the Options Dialog. You do not need to restart your application for the changes to take effect.
As a database administrator, you may also edit the file according to your preferences, and then copy it to any other machine running the MySQL GUI tools. This makes it easy to have identical connection profiles on all machines, without having to set up those profiles individually. All XML configuration files for the MySQL GUI Tools are cross-platform compatible and can be transferred between Windows and Linux machines.
On Windows, XML files common to all MySQL GUI applications are stored in the C:\Documents and Settings\UserName\Application Data\MySQL directory, where UserName is the name of the current Windows user. On Linux, the files are stored in the ~/.mysqlgui directory.
mysqlx_common_options.xml: Stores options selected in the Options dialog.
mysqlx_user_connections.xml: This file is described in How Connections are Stored.
Besides the common files, MySQL Query Browser uses a number of XML files for internal purposes.
mysqlqb_functions.xml: Stores list of functions and operators for use in the Function Browser.
mysqlx_dbm_data types.xml: Stores a list of the available MySQL data types for use with auto-completion and with the MySQL Table Editor
mysqlx_dbm_charsets.xml: Stores the available MySQL character sets.
mysqlqb_history.xml : Stores the queries that make up the History Browser
mysqlqb_bookmark.xml: Stores the queries that form the Bookmark Browser
You may find that the MySQL GUI tools and/or their documentation are not available in your preferred language. If you would like to translate the software, or its documentation, or participate in that translation, please contact the MySQL documentation team before starting your translation!
The MySQL documentation team's address is <docs@mysql.com>. In your mail, please state what you would like to translate (the tools, their documentation, or even the whole MySQL reference manual if you dare), and give some information regarding your background:
What is your name?
In which country and city are you located?
How long have you used MySQL?
Have you done other translations?
Will you work alone, or with a group of translators?
What's your timetable regarding the translation?
What is your motivation for translating?
We will get back to you as soon as possible.
The format used for both software and documentation is DocBook XML. From that base format, all other available formats (HTML, CHM, PDF, etc.) are being generated. It would be a pity if you started translating, say, the HTML version of this documentation, because we will not be able to use it in that format. Also, you might find that someone else has already done (or is in the process of doing) that translation. So, once again, please contact the MySQL documentation team first!
Table of Contents
The MySQL GUI tools incorporate PCRE and PNG support through the use of third-party libraries.
Regular expression support is provided by the PCRE library package, which is open source software, written by Philip Hazel, and copyright by the University of Cambridge, England. The source for the PCRE library can be found at: ftp:/ /ftp.csx.cam.ac.uk/pub/software/programming/pcre/.
PNG support for the Windows version is provided by the TPNGImage component which is open source software, written and copyright by Gustavo Daud. The source for the TPNGImage can be found at: http://pngdelphi.sourceforge.net .