[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:28 PM:

Actually …

Currently we show tables and basic information about the tables only in OBJECTS for a database.

I think we should also list SPs, Functions, Views etc. with similar basic information about those – at least this information

* DEFINER

* SQL_SECURITY

And there could be a 'additional information' like default charset/collation as you proposed (I verified that there is nothing more added to SHOW CREATE DATABASE in MySQL 6.0)

Just like I think the OBJECTS for tables should display triggers defined as 'triggered by' operations on that table with information about each like 'BEFORE UPDATE', 'AFTER INSERT' etc.

Well, I'm of the give-me-all-information-and-I'll-make-my-own-selection type. 😉 If there's information to be had about all those things, by all means, show it! Or would other people then start to complain there's too much information?

peterlaursen wrote:

Basically the information in OBJECTS as of now does not reflect changes in MySQL after verson 4.0! A little strange only that we never considered this ourselves and had no similar requests before!

Heh. And here when I got myself an upgrade to the latest and greatest MySQL 5.0.45 I hurried to upgrade my SQLyog because I was sure there would be enhancements for it in the long interval after 5.22. Clearly there were enhancements (no regrets!)… but. Funny, actually. 😛

[Marjolein Katsma]

OK, here's a result:

I had version 5.32 running since yesterday afternoon (my post was just after I started it up). Late this morning I tried something, but there was still a connection – no auto-reconnect either, I looked at the history tab since in a few other cases I'd noted much longer response times which I suspect was a transparent auto-reconnect.

Meanwhile I had also 6.05 running all the time, using it on and off. Off actually since late morning, I was doing other things on the new server.

Just now I went to have a look at something – and 5.32 gave me a “MySQL server has gone away” – twice. So I went to my instance of 6.05 – and had exactly the same result. (Both versions are using a binary-identical version of plink.exe.)

Then I went to look at my server, and find that both SSH and MySQL are, in fact, running (I'd been using SSH intensively during most of the interval).

Now, I can do a little digging to see if I can find if in the meantime either of the two services had stopped and auto-restarted. (Will report back if I find anything – I'm not familiar yet with where which logs are to be found so that may take me a while.)

Still, even if one of the two had stopped and restarted, when both are running shouldn't SQLyog (plink.exe) be able to auto-reconnect when an event happens that requires it to go to the server (like clicking on another database in the object browser)? A click is all I did in both cases.

And if it's not a matter of a server “going away”and coming back, is there some sort of built-in time-out in plink.exe?

[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,

[Marjolein Katsma]

peterlaursen wrote on Aug 31 2007, 10:36 PM:

I told that also I think we should add the information that SHOW CREATE DATABASE exposes about the database to the OBJECTS information. For 5.0.x that is 'default charset' only but maybe that is even more for 5.1, 5.2 and 6.0. Please give us a few hours to research and discuss this on Monday!

Sure! 😀

peterlaursen wrote:

I know nothing about DirectAdmin – only YaST (SuSE) and up2date (RHEL)

It's a control panel for webhosting. Personally, I'm not fond of control panels at all, but I could choose one for free… 😉 Now I need to learn how it organizes things on the system so I don't step on its toes. 🙁

[Marjolein Katsma]

Marjolein Katsma wrote on Aug 31 2007, 06:13 PM:

… then the DirectAdmin (DA) installation is run (it needs parameters to set it up for the customer). In this scenario it's possible MySQL is already available as part of the distro, but it is part of what DA installs (since DA needs MySQL for itself to support its messaging/ticketing system in the da_roundcube database I've mentioned; DA also assigns a MySQL root password, for instance – I had to hunt around for that one!), or just put there (but not installed) by another means before running the installation script for DA.

Well, FYI, I figured that one out: in /usr/local/directadmin/scripts/packages I found a bunch of RPMs, including MySQL-client-4.1.21-0.i386.rpm, MySQL-devel-4.1.21-0.i386.rpm and MySQL-server-4.1.21-0.i386.rpm. The DA install scripts show these are used by DA to initially install MySQL.

I suspect the upgrade was then done by simply replacing the result of that with a pre-built binary (while forgetting to run mysql_upgrade).

[Marjolein Katsma]

peterlaursen wrote on Aug 31 2007, 04:34 PM:

1)

“I don't have a my.cnf”. Sounds strange to me! But I shall not totally exclude that you are right that some builds have compiled-in defaults and don't need it. Is it a build distributed by MySQL AB or one shipped with a Linux distro? On Linux the my.cnf file would normally be in /etc/ folder.

It didn't sound all that strange to me because I've run MySQL before without any my.cnf – so I know that is also just overriding defaults. 🙂

As to how it got onto “my server”… that's a bit hard for me to tell, as I'm new to the system – it's a CentOS4 server (VPS running on top of Virtuozzo); I've “played” with CentOS4 a little before (different host) but that was a really “bare” system, not managed at all (but also VPS), while this is fully managed and set up with the DirectAdmin control panel which also imposes its own conventions. I've been prodding and poking for nearly a week now, and slowly becoming familiar with the data structures, and how things are “supposed” to be done. (What I'm used to most for a server is FreeBSD (old hoster), and at home I'm running Win2K.)

My guess is an initial system gets installed from a pre-configured CentOS4 image, and then the DirectAdmin (DA) installation is run (it needs parameters to set it up for the customer). In this scenario it's possible MySQL is already available as part of the distro, but it is part of what DA installs (since DA needs MySQL for itself to support its messaging/ticketing system in the da_roundcube database I've mentioned; DA also assigns a MySQL root password, for instance – I had to hunt around for that one!), or just put there (but not installed) by another means before running the installation script for DA. When I requested an upgrade of both PHP (from 4.x to 5.2.2 now) and MySQL (from 4.1.21 to 5.0.45 now), the 'directadmin' directory acquired a new subdirectory, 'customapache' which included both PHP 4.4.7 and PHP 5.2.2 – but no MySQL. I see a bunch of .a files in /usr/lib/mysql but no source, and none of the support_files MySQL docs refer to when explaining how to install it.

There is of course /etc, but that doesn't contain my.cnf, nor a mysql subdirectory (MySQL docs also refer to it possibly being in a home directory, but none of the home directories (including /root) contain it either. It's also not in the mysql data directory. And I did a complete download of the system as originally installed to study it, so I'm quite sure it wasn't there in the first place, and wasn't put anywhere (or removed) when they did the upgrade for me. I even tried a 'find' in the shell – nothing. What's suggestive though is that they “forgot” to run 'mysql_upgrade' as I found out when trying to add a user and SQLyog gave me a very helpful error message! So I suspect they just put in a new binary…

(And a tidbit: I've been poking in the mysql libraries, too, and found the string 'my_charset_latin1' in libmysys.a – that at least suggests to me where that compiled-in default is coming from ;))

Maybe you can tell me whether MySQL 4.1.21 is part of the CentOS4 distro? 😉

peterlaursen wrote:

However I would create a my.cnf if there is none! There MUST be some default settings you want to override (like 'max_allowed_package' for instance)

That was my idea, too (now, if only to override the default charset with utf8!). But I'm finding it difficult as always to find documentation what exactly can be configured in my.cnf. The documentation for MySQL's SQL and DDL languages is very good, documentation for the tools and scripts is pretty good, but a complete description for my.cnf I can't find – and I'm not searching for the first time. 🙁

That's not your problem though!

peterlaursen wrote:

2)

“So why can't SQLyog show me this database information directly”.

We populate the OBJECTs tab with information for a database using the query

SHOW TABLE STATUS FROM … (HISTORY tab will tell you what queries SQLyog sends).

We will discuss adding information returned by SHOW CREATE DATABASE to OBJECTS tab. I think you are right we should. Basically this OBJECT TAB design goes back to server versions 3.23 and 4.0. Those versions do not support SHOW CREATE DATABASE at all (try and you'll get 1064 syntax error!). But we can differentiate that, of course. We do SELECT VERSION() after connection, so we will know when to send SHOW CREATE DATABASE!

Indeed, looking at the history tab, for a database I see:

/*[17:50:27][  30 ms]*/ show table status from `travelblog`

(seemingly always doubled??) and occasionally with much longer response times which I suspect are transparent re-connects – while for a table I see:

/*[17:49:47][  30 ms]*/ show full fields from `travelblog`.`country`


/*[17:49:47][  10 ms]*/ show keys from `travelblog`.`country`


/*[17:49:47][  20 ms]*/ show create table `travelblog`.`country`


/*[17:49:48][  10 ms]*/ use `travelblog`

So the “show create table” is there for the table (resulting in the DDL). It would be really great if SQLyog could do the corresponding “show create database” (if supported by the MySQL version).

And yes, I know you're grabbing a version, since I got that very helpful error message about missing columns in the mysql.user table!

So thanks for such a great product – but as always something good makes us want even more “good”! 😀

Regards,

[Marjolein Katsma]

peterlaursen wrote on Aug 31 2007, 02:12 PM:

The 'upper level' of a database is the server! CREATE DATABASE creates that database with the server default as specified in the server configuration file (my.ini/my.cnf) unles you specify differently like CREATE DATABASE .. CHARSET.. .

Well, I've been searching all over, since the `information_schema` database consists entirely of temporary tables which must be created on the fly – and the information to do that must come from somewhere. So either it's the server itself (indeed) or my.cnf/my.ini (which itself only overrides defaults: you don't have to specify anytrhing). Guess what? I don't have a my.cnf file anywhere. So whatever MySQL uses must be compiled-in defaults, right?

There still must be some “ultimate” default it's using when creating a database – and I can't see what it is or even where it comes from – or what values have been used to override that elusive default when creating a database.

(Excuse me if I seem a bit dense here, but I'm new to both MySQL 5 and the new server with my new hoster. So I'm doing a lot of poking around before getting up to speed with it. Quite a steep learning curve, though it's actually fun. :D)

peterlaursen wrote:

Collation default for every charset is defined here: http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

That matches exactly the content of the CHARACTER_SET and COLLATIONS tables I referred to . 🙂 except it leaves out some columns but gives a bit of extra commentary.

But there's no default character set visible anywhere. Since I also don't have a my.cnf/my.ini file I can only conclude that it must be a compiled-in default value.

peterlaursen wrote:

But most programs will simply fetch and use the returns of “SHOW CREATE …” with the addition that version dependent conditional comments (/*!… */) may be used to conditionally comment out features not supported on lower server versions.

Aha!, that helps:

SHOW CREATE DATABASE mysql;


CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */





SHOW CREATE DATABASE da_roundcube;


CREATE DATABASE `da_roundcube` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */





SHOW CREATE DATABASE travelblog;


CREATE DATABASE `travelblog` /*!40100 DEFAULT CHARACTER SET utf8 */

It certainly supports my observations (and actions) so far:

• the default charset does seem to be 'latin1' (which for the mysql database is completely overridden with utf8 for all tables, but was used for the da_roundcube database (and its tables) on conversion to MySQL 5)
• da_roundcube shows what I later converted it to; the latin1_general_ci collation is mentioned because it's not the default for latin1
• travelblog is a database I created myself, then ALTERED to use utf8 / utf8_general_ci, the default collation for the utf8 charset, so the collation is not mentioned here, but the charset is.

So why can't SQLyog show me this database information directly (on the Objects tab) when it does show charset and collation info for tables in the DDL info on the Objects tab?

[Marjolein Katsma]

peterlaursen wrote on Aug 31 2007, 11:28 AM:

There is no intentional change between the two versions in this respect. Only reconnection in Structure Sync in know to be broken in 6.0 to 6.05 (fixed in 6.06).

However we did rebuild the PLINK in 6.0 (technically it is plink.exe and not sqlyog.exe that loses connection).

It will take a little time to research this. I will PM you a download link to version 5.32 ENTERPRISE with registration details. Please install this one (to another folder) open a connection with it and let be open overnight and report how this one behaves.

Downloaded, installed (indeed it didn't ask me for a registration code but I have 5.22 still installed as well), and set up with another connection to my host. I'll now let it sit idle for a while, at least until tomorrow morning (if nothing happens that forces me to reboot, that is)…

Interestingly though, although the connection dialog in 5.32 looks (almost) like that in 6.05 but different from 5.22, a version compare shows the plink.exe bundled with all three versions is exactly the same, and they are binary identical, too. There is a difference with 5.12 (which I still have installed) though, but I never used it with that version: I just never had a need for tunneling until now, so I used it a bit in 5.22 and then upgraded to 6.05.

Anyway, I'll report back what happens, if anything, or if nothing. 😉

[Marjolein Katsma]

peterlaursen wrote on Aug 31 2007, 11:13 AM:

1) Display of charset/collation information:

If no charset/collation is shown in OBJECCTS it is becuase it is the same a the default for the 'upper-level' object. This is how MySQL reports it.

So what is the “upper level” for a database? My guess would be what's in the `information_schema` database.

There, the `COLLATIONS` table defines (among other things) what the default collation is for each particular character set. But when I go to the `CHARACTER_SET` table, all I see is effectively a list of possible character sets, but not which is currently the default one used when creating a database.

SQLyog (or MySQL?) not showing what's defined in an upper level makes sense – this is apparently what does happen for table definitions, where the default collation is not shown when it is the same as for the database (the `mysql` database is a nice example of this: all tables have the charset defined as 'utf8' and only some of them have a collation defined – apparently where this this different from the default collation for the utf8 charset, as defined in information_schema.COLLATIONS).

But if no default CHARACTER SET is defined (only a list of possible ones) how could one see what the default is going to be when creating a database? I only found out it's latin1 because all imported tables (from a 4.x MySQL where charset is not defined) had latin1 as charset, and all string-type columns ended up with collation latin1_swedish_ci as a result). And a pre-existing database also suddenly had everything set to latin1/latin1_swedish_ci after the conversion to MySQL 5.0.45.

So surely it should be possible to at least see somewhere what the “global” default character set is? And also, if a database is now defined with a different character set than that elusive default, it should be possible to see what it is? And likewise a default collation if that is different from the default collation for the database's characters set? But even for the two databases I now defined as using CHARACTER SET 'utf8' (instead of the hidden 'latin1'), I still cannot see anywhere how it is defined for that database. This information is what I would expect on the Objects tab for the database!

peterlaursen wrote:

2) Definign charset/collations:

*************************

Option to define charset/collations defaults different from 'upper-level' defaults is planned for version 6.1 (and the coding of this feature is completed allredy, I think we can release beta1 next week or beginning of the week after that)

Excellent! 🙂 Can anyone download and try these beta versions?

That still leaves the lack of visible information on these settings for an existing database (not tables, where this information is available).

peterlaursen wrote:

With versions <6.1 you will have to execute SQL like

CREATE DATABASE CHARSET .. [COLLATE …];

ALTER TABLE … CHARSET .. [COLLATE …];

etc. (there might be slight variations depending of server version)

Note that existing columns will not change – they will have to be changed one-by-one.

Yes, that's what I've been doing (but using ALTER TABLE CONVERT TO CHARACTER SET … COLLATE.. – which changes all columns in the table – for a database that was preinstalled by DirectAdmin). Example:

ALTER DATABASE da_roundcube COLLATE latin1_general_ci;


ALTER TABLE da_roundcube.cache CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;


...

For importing databases from the old server it was simpler to just re-import (copy) all tables, after doing an ALTER DATABASE.

Regards,

[Author]

Posts