Brainwave!

[Marjolein Katsma]

Oooohkay. I've been grumbling a large part of today about how I can't see what the current default settings are for (particularly) character set and collation – which get used when creating a database and tables inside that, unless you override it in the CREATE statement (but then how do you know you need to do that?) See [topic=”3468″]Missing Features[/topic].

Well, we got that sorted (apart from being implemented, of course). 😉

So then I went and had dinner. And my brain relaxed, and then came up with a sudden BrainWave!

Before I used SQLyog, I used MySQL-Front (as no doubt many SQLyog users did) – but that was a couple of years ago by now. But one thing always nagged me, something MySQL-Front had (I still have version 2.2 installed): when connecting to a database server, the first thing you saw was a screenful of server data. At the time, most of that was cryptic to me, but I know it had a lot of useful things – things I don't see when connecting to a server with SQLyog. So… to refresh my memory, I just fired up good old MySQL-Front to have a look – attached is a screenshot of part of that, mostly to show how it's presented: Directly useful information here on the “Host” tab:

• remote server (IP address here)
• version of the MySQL server
• uptime (and that little clock keeps ticking, too)

Then there are two child tabs of the Host tab: Variables and Pocess-List (each with the number of rows between brackets).

Not shown, but at the bottom MySQL-Front has an SQL tab that's morfe or less equivalent to SQLyog's History tab: it shows the statements it's executed – preceded by a few useful comments:

# Server: 4.0.27


# Client: 3.23.39


# Protocol-Version: 10


# Uptime: 1965836


# Threads: 4


# Questions: 1966807


# Slow queries: 2


# Opens: 665


# Flush tables: 1


# Open tables: 64


# Queries per second avg: 1.000


SELECT VERSION()


SHOW VARIABLES


SHOW STATUS


SHOW PROCESSLIST


SHOW DATABASES


SHOW TABLES FROM `SH_backup` SHOW TABLES FROM `c419` SHOW TABLES FROM `clickcount`


...

Now guess what's on that Variables tab? The combined output of SHOW VARIABLES and SHOW STATUS. What's on the Process-List tab is of course the result of SHOW PROCESSLIST. The rest is obviously used to build the tree on the left.

Even more interesting is that in the Variables tab I find (for the server running at my “old” host) variables like 'character_set' and 'character_sets' (no collation, since that's not supported by version 4.0.27).

So… I went back to SQLyog, and tried out the same “start-up” statements on the connection to my new server. Bingo! SHOW VARIABLES gives back 231 rows – a few highlights (there's much more, of course):

Code:

Variable_name Value

character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server latin1_swedish_ci
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
language /usr/share/mysql/english/
lc_time_names en_US
socket /var/lib/mysql/mysql.sock
storage_engine MyISAM
system_time_zone CEST
table_type MyISAM
time_format %H:%i:%s
time_zone SYSTEM

… and included there are those elusive defaults I was looking for: character_set_server = latin1 and collation_server = latin1_swedish_ci !

Of course, any time I want to know that, I can (re-)execute the SHOW VARIABLES query.

But could we please have it just a mouse click away, maybe an extra tab, or a popup window? And the host address and server version are also really handy to have available at a glance. Could we have those, too, please? SQLyog has a lot more to offer now now than MySQL-Front ever had – but those little things I still miss.

Regards

[peterlaursen]

1) there is the menu tools .. show

2) there is an icon in the icon bar, see image

[Marjolein Katsma]

peterlaursen wrote on Sep 1 2007, 10:14 AM:

1) there is the menu tools .. show

2) there is an icon in the icon bar, see image

Duh! Hidden away in plain sight… Glad to have it! 🙂

Although… none of those show (let alone keep visible) the host address/IP or MySQL version.

Frankly though, I find some parts of the interface really hard to learn because not all buttons have a same-named corresponding menu item (they should) and not all menu items that have a toolbar shortcut have the corresponding icon on the menu. And things with quite different names have the same icon. Or tooltips for the buttons don't correspond to the name of a menu item – so is it really the same thing?

Keeping all of these consistent is part of basic Windows GUI guidelines.

For instance: there is a button like a document with a pin – its tooltip says: 'Template'; there is a menu item Edit->Insert templates… with the same icon. If it's the same thing (and it is in this case) then the tooltip for the button should have the same text as the menu item. That's just one example but there are many more.

For this case: there is a – rather cryptic – button (with a down-arrow to indicate it will lead to a submenu) and its tooltip says “Show Values”; but the menu item under the Tools menu is called “Show” and doesn't have the corresponding icon either. In this case the menu item should be renamed to “Show values” since that makes its purpose clearer, and it should have the same icon – without the down-arrow.

The same goes for Tools->User Manager (no icon) and a button with a nice icon with tooltip that says “Manage User Permissions”. Not only doesn't that correspond to the menu item, it also isn't a good description since the button also has Add User… and Delete User… in its submenu. Here, the menu item is better-named, but it should have the corresponding icon (again, without the down-arrow, since the menu item already has a left-arrow for the submenu).

Then there's a button that says “Execute Batch File”, and a menu item called “Restore From SQL Dump…” with the same icon. Are they the same thing? If so, they should have the same name; if not, they should have different icons.

If a button leads to a dialog, instead of directly executing something (like Execute Current Query does), its name (tooltip) should also have an ellipsis, just like the corresponding menu item.

(And some tooltips start with a space, others don't.)

Etc…

It would even help more if the “groups” formed by separators on the toolbar would correspond to menus – there are inconsistencies here, too. Even better if the order of the groups matched the order of the menus on the menu bar. The database dropdown should be its own group since it doesn't correspond to any menu item.

Just go through your menus and the toolbar once to make all these things (not just the ones I've mentioned) consistent, and the application will become a lot more “learnable”.

So… sorry to bother you with something that already (mostly) existed, but that is largely a result of the “unlearnable” interface.

Regards,

[peterlaursen]

Thanks for your considerations.

We will check the inconsistencies you point to here.

For 3 years or so focus has been on features, the GUI is almost untouched.

Maybe you are right that we should 'balance that focus' soon.

[peterlaursen]

I think the best solution is that we should populate the OBJECTS tab for the connection itself!

We could display the connections manager information and the variables that are most important for SQLyog operation. We should also briefly explain how the affect SQLyog operations

I do not think 'uptime' is very relevant in the SQLyog context (we have MONyog for monitoring). Those are most relevant I think:

* server version

* server default charset and collation

* max_allowed_packet value

* storage engines available (and which is default)

* local infile (ON/OFF) setting

* lowercase_table_name value (1/2/3) setting

* global sql_mode

* event_scheduler status

and maybe

* # of active threads, idle / not idle count

[Marjolein Katsma]

peterlaursen wrote on Sep 1 2007, 01:21 PM:

Thanks for your considerations.

We will check the inconsistencies you point to here.

For 3 years or so focus has been on features, the GUI is almost untouched.

Maybe you are right that we should 'balance that focus' soon.

Well, feautures is what ultimately made me switch away from MySQL-Front, even though at first that meant losing ease of use. The functionality is absolutely great (even if I have some niggles about character sets and such ;)).

If you do start working on the UI, I'd be happy to beta- (or alpha-) test your you!

[Marjolein Katsma]

peterlaursen wrote on Sep 1 2007, 04:16 PM:

I think the best solution is that we should populate the OBJECTS tab for the connection itself!

Brilliant! It's completely blank now on the connection, and it makes sense.

Also, while typing this message I just found out that I cannot even copy anything from the window that pops up for the SHOW VARIABLES result – having the information on the Objects tab would solve that, too.

peterlaursen wrote:

We could display the connections manager information and the variables that are most important for SQLyog operation. We should also briefly explain how the affect SQLyog operations

I do not think 'uptime' is very relevant in the SQLyog context (we have MONyog for monitoring).

I considered the uptime mostly a gimmick (especially with the ticking seconds) in MySQL-Front. But server hostname or IP address is valuable – especially when you are working with multiple servers – and having it available at a glance (not even mouseclick). Maybe it could just be shown in the title bar?

peterlaursen wrote:

Those are most relevant I think:

Picking a few (going by what I see for my 5.0.45 server):

* server version

definitely – again, especially valuable if you are working with multiple servers, of different versions

* server default charset and collation

those, certainly, but I'd really like to see the whole set of character_set_* and collation_* values:

• character_set_client
• character_set_connection
• character_set_database
• character_set_filesystem
• character_set_results
• character_set_server
• character_set_system
• collation_connection
• collation_database
• collation_server

Those will be valuable when figuring out the odd problems that can come from working with combinations of different charsets and collations.

(BTW, the connection dialogs for 5.22 and 5.23 had a field for connection charset but it has disappeared in 6.05 – why? And since there's apparently a collation for the connection, wouldn't it be useful to be able to specify that, too? You might get some puzzling results if, say, your connection sorts “general” while the server does it “swedish”.)

(yes on the rest of the list)

and maybe

* # of active threads, idle / not idle count

Yes, definitely

Also:

All the various path values like:

• character_sets_dir
• datadir
• innodb_log_group_home_dir
• language
• pid_file
• slave_load_tmpdir
• socket
• tmpdir

They will be valuable as well when working with different systems, or a new system you're unfamiliar with. If you want to know if there is a socket, it helps if you know where to look. 😉

The various date and time formats could also be handy.

That's about what I can think of, based on the things I've been looking for when troubleshooting. But maybe others would have a need for other data…

Regards,

[Author]

Posts