mysqldump + enum + default + umlaut (2)

Hi,

what about the report below? It seems quite critical to me...

> I am trying to back up a database that has a field definition like i.e.
>
> foo enum('a','b','ö') default 'ö'
>
> and mysqldump does not correctly encode the umlauts in it. Depending on
> the charset I set I get either lots of '?' instead of the umlauts or if
> I specify utf8 for the charset I will get the correct umlauts in the
> enum values but not in the default value!
>
> I am using 4.1.2-alpha-standard and I couldn't see a bug fix for this
> in the recent version history.
>
> I already lost a whole day recovering a deleted database because of 
> this.
> It seems that there is no easy way to edit such a dump without 
> destroying
> the encoding and I ended up with a database that had many fields empty
> because the database values didn't match the enum definition.

One update on this though:

I tried to generate a step-by-step example and to my surprise found out 
that
something is weird. My existing tables dump different than newly created
tables.

I.e.

create table foo (bar enum('ä', 'ö', 'ü') default 'ö' not null);

will dump like this:

> > mysqldump --no-data test foo                                         
>                                       9
> -- MySQL dump 10.7
> --
> -- Host: localhost    Database: test
> -- ------------------------------------------------------
> -- Server version       4.1.2-alpha-standard
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, 
> CHARACTER_SET_CLIENT=utf8 */;
> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
> FOREIGN_KEY_CHECKS=0 */;
> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, 
> SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;
>
> --
> -- Table structure for table `foo`
> --
>
> DROP TABLE IF EXISTS `foo`;
> CREATE TABLE `foo` (
>   `bar` enum('ä','ö','ü') NOT NULL default 'ö'
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
> /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

which seems ok because its completely UTF8 and will reimport fine.

BUT one of my existing production tables dumps like this:

> > mysqldump --no-data bokrondellen artikel                             
>                                             10
> -- MySQL dump 10.7
> --
> -- Host: localhost    Database: bokrondellen
> -- ------------------------------------------------------
> -- Server version       4.1.2-alpha-standard
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, 
> CHARACTER_SET_CLIENT=utf8 */;
> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, 
> FOREIGN_KEY_CHECKS=0 */;
> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, 
> SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */;
>
> --
> -- Table structure for table `artikel`
> --
>
> DROP TABLE IF EXISTS `artikel`;
> CREATE TABLE `artikel` (
>   ...
>   `mediatyp` 
> enum('Bok','Ljudbok','Multimedia','Marknadsföringsmaterial','Övrigt') 
> NOT NULL default 'Ãvrigt',
>   ...
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
> /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

As you can see, the enum here is listed in Latin1 and the default seems 
to be in some
other coding but at least not consistent with the enum values itself. 
This will not
import back and will generate an error that the default is not found in 
the enum list.

What can I do to fix this? Until then I am living dangerous because our 
backup-restore
routines will fail.

Andreas Pardeike

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]