Missing Features

[Marjolein Katsma]

I'm in the process of migrating a number of websites to a new host; the new hoster installed MySQL for me (as a part of installing DirectAdmin); initially it was 4.1.x, I requested replacement with 5.x (it is 5.0.45 now).

I also hurriedly upgraded my SQLyog version (yesterday), so I have now 6.05. 🙂

Now the problem: The server schema is set, and I cannot change it (this is read-only by design). But I found that for new databases the default character set is latin1, and the default collation is latin1_swedish_ci – not exactly what I want (utf8).

Missing features for database:

• When I look with SQLyog at a database, I cannot see what its CHARACTER SET and COLLATE properties are: I would expect this information on the Objects tab for the selected database
• There is no dialog to see or ALTER these properties
• When creating a new database, it is not possible to define these properties (I would have expected something like “Advanced Properties” on the create/alter table dialogs)

Missing features for table:

• While DEFAULT CHARSET and (if defined) COLLATE can be seen in the DDL on the Objects tab for the selected table, it is not possible to define these properties in the create/alter table dialogs – I would expect them in the Advanced Properties dialog.

Of course it is possible to enter the appropriate ALTER statements after creating a database or a table (when it must be different from the database's default), but it would be extremely helpful if it could just be done in the dialogs already available for creation of database and table. And – especially for databases that were created by some application and not myself – it would be helpful if the settings were visible on the database's Objects tab!

Regards,

Marjolein

[peterlaursen]

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.

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)

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.

Did I overlook something in your post?

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

[peterlaursen]

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.. . Collation default for every charset is defined here: http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

The also applies when importing a dump: if the CREATE statements in the dumps contain CHARSET specifications they will be used, if not defaults will be used.

Whether a dump contains charset/collation specifications for tables or not depends on the program generating that dump. 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.

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

[peterlaursen]

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.

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)

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!

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

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).

[peterlaursen]

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!

And yes .. when some DBA is upgrading MySQL without running the appropriate update scripts/programs we get accused that our (CLIENT) program malfunctions, but it really is the server that does not return the expected information or does not give access because privilege tables were not updated as they should 🙁

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

[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. 🙁

[peterlaursen]

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.

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!

[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. 😛

[Author]

Posts