#1062 Default character set and collation

[Anonymous]

Originally created by: miki...@gmail.com

What is the problem that needs to be solved (required)?
Every time I create new table I need to choose UTF char set + collation,
because latin1 + latin1_swedish_ci is selected. That's kind of annoying.

What is the idea in abstract terms (optional)?
Default char set + collation derived from "parent" element - for a new
table from database, for a new column from table. I guess it's better than
setting in Preferences.

Example workflow using the new feature (optional)?

What is a practical way to implement this idea (optional)?
Just one extra db-select to get current settings from "parent" element,
that's all ;-)

P.S. I really tried hard to find similar request here, my apologize for
eventual duplicity.

[Anonymous]

Originally posted by: a...@anse.de

Confirmed. However, I won't put much work on the old create table dialog, instead
I'll implement everything what's missing and more in a new dialog which is discussd
in issue #436.

Status: Accepted

[Anonymous]

Originally posted by: a...@anse.de

Issue 1102 has been merged into this issue.

[Anonymous]

Originally posted by: a...@anse.de

Please have a look at the new table editor, it should have solved your issue.
Although it's odd... I have a db with collation "latin1_german1_ci", but if I switch
to that db and query a "SHOW VARIABLES LIKE 'collation_database'" I get
"latin1_swedish_ci". Wtf? That's the reason why that might seem unsolved.

Status: NeedInfo

[Anonymous]

Originally posted by: rosenfie...@gmail.com

hmm 'collation_database' is the default collation for databases created with a
'CREATE TABLE' statement that does not include a collation, not the collation of the
currently USEd database, as far as I can recall

[Anonymous]

Originally posted by: gabriel....@gmail.com

As I said in Issue 1102:
<quote>
"character set & collation" are "inherited":
- on database from the server if not specified at database creation
- on tables from the database if not specified at the table creation
- on columns from the table if not specified at the column creation (and
of course if they even apply).
</quote>

So tables can have charset too... so you need an extra select in table creation dialog.

The change is ok, now the table creation dialog inherits corectly the charset &
collation from database...

[Anonymous]

Originally posted by: a...@anse.de

> The change is ok, now the table creation dialog inherits
> corectly the charset & collation from database...

Yes, I thought so too, only in one case that's somehow not working here, where the
database has latin1_german1_ci and the table editor shows latin1_swedish_ci by default.

So, how do you check what's the current database collation? Isn't it "SHOW VARIABLES
LIKE 'collation_database'"?

[Anonymous]

Originally posted by: gabriel....@gmail.com

1. Database charset & collation:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'test-utf8'

2. Table charset & collation:

SELECT TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test-utf8'

3. Columns charset & collation:

SELECT CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'test-utf8'
AND TABLE_NAME = 'some-table'

The old fashion way is:
SHOW CREATE DATABASE `test-utf8`

That gives you something to parse by hand... not pleasant !

(CREATE DATABASE `test-utf8` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE
utf8_unicode_ci */)

[Anonymous]

Originally posted by: a...@anse.de

What about "SHOW VARIABLES LIKE 'collation_database'" ? It gives me a different
collation each time I switch to a different database. Shouldn't that be the solution
to cover old + new servers?

[Anonymous]

Originally posted by: gabriel....@gmail.com

Yes ! I saw that variable.

But it has little or no mentionings in manual (except
http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html). So I wouldn't go
with it !

I would definitely go with "information_schema" for almost all infos...
I don't know maybe you need a level of abstraction that would cover the differences...

[Anonymous]

Originally posted by: a...@anse.de

Ah, thanks for the URL, didn't see that doc yet. Looks very clear and comfortably to use:
"collation_database: The collation used by the default database. The server sets this
variable whenever the default database changes. If there is no default database, the
variable has the same value as collation_server."

So I'd prefer going on using that instead of doing different things for different
server versions again.

Status: Fixed