Updating Collation With

[dbusr]

I'm not sure if this a bug or if I am using the sync tool incorrectly. I ran the schema sync tool and part of the changes it found was updating the table type. It gave a command like this

ALTER TABLE `my_table`, DEFAULT CHARSET='utf8'

When I tried to apply the changes, I got a mysql error on that line. When I did it manually, the command that resulted was

ALTER TABLE `my_table` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

In those cases where it failed, the only change to the table was that line. Other tables that need that change as well as others process fine, like this one

CREATE TABLE `cache`(


`cache_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL  ,


UNIQUE KEY `cache_name`(`cache_name`)


) ENGINE=InnoDB DEFAULT CHARSET='utf8';

Using Enterprise 10.51

[peterlaursen]

If the statement generated really is like “ALTER TABLE `my_table`, DEFAULT CHARSET='utf8'” it is a bug. There should not be a comma before 'DEFAULT'.

it is also a little inconsistent that we quote 'utf8' in one context and not in another (but cosmetical as both are valid)

[sathish]

Hello,

The statement should generate COLLATE. This is a bug which will be fixed. But we are unable to reproduce the statement with comma before 'DEFAULT'. Could you please provide us a sample dump of the tables where this is reproducible?

Regards,

Sathish

[dbusr]

This should allow you to see the failure. This is one of the original tables

CREATE TABLE IF NOT EXISTS `products_tags` (


`products_id` int(11) NOT NULL,


`tag_id` int(10) NOT NULL


) ENGINE=MyISAM DEFAULT CHARSET=latin1;

This is the new table

CREATE TABLE IF NOT EXISTS `products_tags` (


`products_id` int(11) NOT NULL,


`tag_id` int(10) NOT NULL


) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When I run the sync tool on the above, this is what it shows as a result:

/* Alter table in target */


ALTER TABLE `products_tags` ENGINE=MyISAM, DEFAULT CHARSET='latin1' ;

[peterlaursen]

Well – the comma is not a bug (and I was wrong).

Please refer MySQL documentation:

http://dev.mysql.com…lter-table.html

table_options:

table_option ,] [i]table_option[/i …

The comma in square brackets (” [,] “) indicates that the comma is optional. And it works fine for us (no error) with both comma and without – and it does not matter if we execute from the editor or from the Schema Sync dialog (but we still don't have the COLLATE clause).

So what is the exact server version you are using (execute “SELECT VERSION();” to get full details)?

Also please doublecheck that the comma is the problem. Copy the statement from Schema Sync dialog to the editor and execute with and without comma.

[peterlaursen]

Both issues confirmed.

1) COLLATE clause not handled properly

2) If one and only oen table option is changed a comma may erroneously be added

This statement is OK: “ALTER TABLE `products_tags` ENGINE=MyISAM, DEFAULT CHARSET='latin1' ; “

This is not:” ALTER TABLE , DEFAULT CHARSET='latin1' ; “

(between table options a comma is valid. But before 1st table option it is not. This happens sometimes if there is only one option specified. We are analyzing the code now)

[Author]

Posts