Sql Generated By Structure Sync Fails To Copy Character Set

[jrossiter]

I ended up losing a couple hours to this due to MySQL's completely ambiguous error messages.

I used Structure Sync to copy new tables from one DB to another. During the process the character set options for the table were completely lost. The table on the source server was 'DEFAULT CHARSET=utf8', and on the source server they ended up being latin1 (the default for the server).

There seems to be one issue and one ponderance here:

1. Structure sync ignores character set options

2. Why doesn't structure sync simply use the results from 'SHOW CREATE TABLE' when it needs to create a new table instead of rebuilding the SQL from scratch?

[peterlaursen]

1)

Quote:

The table on the source server was 'DEFAULT CHARSET=utf8', and on the source server they ended up being latin1 (the default for the server).

This is unclear to me. I would expect “on the target server they ended up being latin1..”. A typo? :huh:

I think most people would not bring the charset from source to target when syncing between two databases having different charsets. However that could be an option. Or a warning/a message could be printed.

But it is always possible to edit the CREATE and ALTER statements.

2)

I don't understand!? It generates a quite normal CREATE SQL statement herre ?

[jrossiter]

peterlaursen wrote on Mar 22 2006, 10:52 PM:

1) This is unclear to me. I would expect “on the target server they ended up being latin1..”. A typo? :huh:

Yes, typo.

peterlaursen wrote on Mar 22 2006, 10:52 PM:

I think most people would not bring the charset from source to target when syncing between two databases having different charsets. However that could be an option. Or a warning/a message could be printed.

I disagree. If you are synchronizing the databases that means they should end up identical. That includes character sets. Also, it's not really a matter of the database character set, it's the MySQL server default. If one server defaults to latin1 and the other defaults to utf8 that doesn't mean that the tables should differ. For instance, if someone were in the same situation I was in during this situation:

Two databases were created on different servers using the same SQL, thus ending up with the same character sets.

One of the databases has new tables added to it.

I use Structure Sync to copy those tables to the second database.

Now I create a new foreign key on the first system and everything works great.

I create the foreign key on the second system and it fails obtusely and takes me quite a while to figure out.

The reason? Because when SQLYog created the new tables on the second DB, it failed to make them identical. That means that when I went to create the foreign key the column collation didn't match between the new table and one of the old tables.

In a situation like this Structure Sync is actually a hinderance because it didn't actually synchronize them. They had the same columns, but they were not structured the same way. If I had simply done the 'SHOW CREATE TABLE' and copied the SQL by hand, I wouldn't have had any implementation problems and saved time in the long run.

I would also argue that character set can be very important in other situations. What if, for instance, I had one table that was used for storing mutlilanguage data. I set the collation in one column to be latin1, another to be geostd8, yet another to be hebrew. Those columns were set with those character sets for a real reason. The rest table itself could be utf8, and the server's default could be latin1, but the character set information is not something that can be trivially ignored.

peterlaursen wrote on Mar 22 2006, 10:52 PM:

But it is always possible to edit the CREATE and ALTER statements.

Sure… if you know that it's something you need to watch for. I'm fairly experienced with SQL and it took me a couple hours to figure out because I'd never seen the problem before, and SQL sure as hell wasn't giving me any worthwhile hints.

peterlaursen wrote on Mar 22 2006, 10:52 PM:

2) I don't understand!? It generates a quite normal CREATE SQL statement herre ?

It's an effort and accuracy issue. If SQLYog simply returned the results from 'SHOW CREATE TABLE', it wouldn't have to go to the effort of rebuilding the SQL, and also things like character sets wouldn't be lost, because 'SHOW CREATE TABLE' automatically includes those creation flags.

[peterlaursen]

I agrre that there is at least a documentation issue! I still think that your situation and need is atypical. But a warning of some kind would be nice.

This:

Quote:

I set the collation in one column to be latin1, another to be geostd8, yet another to be hebrew

I think mixing charsets like that in a table is not wise (although possible and legal). I never think a GUI tool like SQLyog will be able to handle this a reasonable way (for the simple reason that you have one and only one localized Windows and only one localized keyboard). I would recommend that you make more tables – each with their charsets – and use UNIONs and JOINs to query more when you need. Actually as of now mixing tables with different charset in the same database is not without problems. That will be better with next BETA. And I shall request (again) that each time SQLyog sends a 'use ..' statement it also should send a 'set character_set_result …' —

BTW: If you'd been follwing this Forum for a while you'd know that I personally have been 'pushing' quite a lot for putting focus on charset and localization issues – for more than 2 years! Give it two months more …

[jrossiter]

My issue isn't with the actual data handling or input (which is the only place that my OS character set and keyboard would come in), it's with the database structure syncronization itself.

Syncing the character set is as simple as adding “DEFAULT CHARSET=” to the end of the table definition, or “character set ” to column definitions.

Here's an example:

The MySQL default is latin1.

Code:

CREATE TABLE `testTable` (
`hewbrewCol` CHAR(10) character set hebrew,
`asciiCol` CHAR(10) character set ascii,
`cp1252Col` CHAR(10) character set cp1252
) DEFAULT CHARSET=utf8;

This is a completely legal table, and highly specified for very important reasons. The problem is that if I were to use Structure Sync to copy this table structure to a separate DB, it would lose all of that character set information. It would end up being:

Code:

CREATE TABLE `testTable` (
`hewbrewCol` CHAR(10),
`asciiCol` CHAR(10),
`cp1252Col` CHAR(10),
);

Which means that all of those columns, as well as the table itself, would be latin1 because that's the server's default. That's bad. That's not syncrhonization. If I inserted data into those columns expecting them to be the same as they were on the other server, I'd end up with potentially corrupted data.

Would you consider “rsync” (Remote Sync) a useful application if it took all of your Japanese text files and transferred them as ASCII instead of Binary, thereby destroying all of the data?

The dictionary definitions are all specific towards time, however they all have the same implication – things happening simultaneously, e.g. the same. If it isn't the same on both ends, it isn't synchronized.

[peterlaursen]

Quote:

if it took all of your Japanese text files …

You are not supposed to work with SQLyog and multibyte charsets as of now!

But yes .. if you are not aware you may destroy data.

I would expect any user to have a backup before doing such operation!

The syntax/implementation of charsets varies across different MySQL versions, so your examples will have to be 'expanded' to fit all (as SQLyog supports all MySQL versions from 3.23). But you probably know this.

I can follow your idea that if a charset is specified with a column or a table it should be sync'ed.

However if no charset is specified with columns (or tables) and you are syncing across servers having different defaults then I think the sync should not 'bring over' the charset from source to target. That would result in 'mixed' databases or tables. And that is what I think most users don't want. At least I would request a user approval of this. Ideally a popup should appear for user to decide what to do.

But it is true that this charset/collation discussion is related to the FK-sync as well.

I think now we should wait for a response from the development team. They will need a few hours to consider things. But thanks for your observations and considerations.

[peterlaursen]

Let me answer this too:

Quote:

Why doesn't structure sync simply use the results from 'SHOW CREATE TABLE' when it needs to create a new table

Because the STRUCTURE SYNC tool is designed to work across different MySQL versions, and 'Create Table' syntax varies with different MySQL versions

[Author]

Posts