MyJgui logoCross-platform graphical MySql client/front-end

Table data

Table data

The table data pane has 4 tabs.

The first - Tables is shared among all the tables within a single database. It shows the output of show table status query.

tablestab

The Table description pane holds the output of the describe table query.

tablesdesctab

Content of table pane holds the output of select query on the current table. This pane is currently the only one that has another toolbar included.

The refresh button refresh the data in the table
Hide/Show columns button enables to see only selected columns in table.
When insert button is pressed, the new line is added to the front of the table, where you can put values. Then you can press commit or rollback button. After pressing the commit button, the program will try to insert the line in the database and refresh view. Rollback will remove the line and your changes from table.
When you select a distinct line, you can update it`s values. You cannot update values, which are part of the primary key of the table. After pressing update button, the line will have all it`s cells editable. When you finish editing cells, you can also press commit or rollback to accept or throw away the changes.
After pressing the delete button, you get the confirm dialog, if you really want to delete the line. If yes, the delete in database table will be performed.
The first, previous, next and last buttons are supposed to move the result set showed in the result grid by the limit and count set at the end of the toolbar. Checkbox - do not use limit - to disable use of the limit in the select query.
The advanced search button lets you chose a display filter.
Checkbox - Limit - enables use of the limit in select query.

Search conditions

You can simply add and remove the conditions. Display the sql of the selected conditions (show sql) and run the filter (Apply search). To apply search you can use a keyboard shortcut: 'Alt +ENTER' to apply serach. The idea of this filters is to limit the output of the content of table by adding a quick where clause without having to actually write the query. To use parenthesis simply left (add) or right (remove) click on the disabled text field (first and last column).

The last tab is MySQL Query, which displays the contents of SQL queries ran through the SQL Console.

Encoding

The encoding has been handled in all components for input and output operations. You can choose the input/output encoding in the Content of table tab. The data in the tab will get encoded without calling the query again without changing the scroll position. If the table data are viewed for the first time, MyJgui will use either the encoding detected for the particular table.
Using MySQL character sets with MyJgui input/output encodings: There are 3 basic factors that affect if the output encoding of the presented data is correct.

  • correct encoding of stored table data
  • mysql server character set and collation
  • myjgui encoding settings

Stored data encoding

You have to know the original encoding of the data set inserted in mysql. This is the most significant factor and somehow you have to find out. MyJgui can help you play around with all encoding parameters as shown below, but as you will see there are quite a lot of combinations.

Mysql character set and collation

It is neccessary that mentioned mysql parameters are set according with stored data encoding mentioned in previous section.

If you dont want to know more about how mysql handles character sets skip to the Charsets in MyJgui. Character set and collation parameters (set through my.cnf file, command line parameters or SET [GLOBAL] VARIABLE queries) take both 3 identical parameters: connection, database, server.

These are:

  • collation_connection
  • collation_database
  • collation_server
  • character_set_connection
  • character_set_database
  • character_set_server

The suffix "connection" limits the parameter lifetime for the period since the client opens the connection unit it's closed. The database suffix will be used for all connections to specified database. The server suffix limits the lifetime until the server gets shut down.

Mysql server takes more parameters which affect the system defaults:

  • character_set_client
  • character_set_filesystem
  • character_set_results
  • character_set_system
  • character_sets_dir

Easy way to set all parameters for current connection is to run this query: SET NAMES [charset] e.g. SET NAMES UTF-8.

Charsets in MyJgui (and other clients)

Clients in general using a mysql database have to know the encoding the server will spit out to be able to present it to the user as it is, or recode it if needed. If the expectation is faulty, the data will be scrambled. All you have to do in MyJgui (and for instance firefox/text editor as well) should be to select the right encoding in preferences for defaults and in "Content of table" tab for spefic data output. If you can't get the data to unscramble by switching encoding in combo box, you have to verify that the data is stored in expected encoding (using mysqldump and viewing the data in firefox/text editor capable of encoding changing) and that mysql server presents the data in expected encoding (by changing "charset" parameter in SET NAMES "charset" query).

Best practices or how to avoid encoding headaches:

Use utf-8 for all the options above - primary data encoding, mysql parameters and client view/edit settings. To conclude, you shold have the basic idea about how to work with encoding in mysql server and MyJgui, but what if the primary data encoding itself needs to be converted (into utf-8!)? For the lucky ones of out who have access to linux tool iconv:

  • dump the database
    mysqldump -u user -p password database > database.iso-8859-2.dump
  • convert data into other charset
    iconv -L #will list all available character set names
    iconv -f'iso-8859-2' -t'utf-8' database.iso-8859-2.dump > database.utf-8.dump
  • import the data
    mysql -u user -p password database < database.utf8.dump

For the courageous (caution - without the backup step): mysqldump -u user -p password database | iconv -f 'iso-8859-2' -t 'utf-8' database.iso-8859-2.dump | mysql -u user -p password database

You may need to specify the SET NAMES charset for mysqldump (--set-charset option) and mysql (--default-character-set= option) command as well.