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=20
> this.
> It seems that there is no easy way to edit such a dump without=20
> 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=20=

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 =20=

> 9
> -- MySQL dump 10.7
> --
> -- Host: localhost Database: test
> -- ------------------------------------------------------
> -- Server version 4.1.2-alpha-standard
>
> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=3D@@CHARACTER_SET_CLIENT,=20
> CHARACTER_SET_CLIENT=3Dutf8 */;
> /*!40014 SET @OLD_UNIQUE_CHECKS=3D@@UNIQUE_CHECKS, UNIQUE_CHECKS=3D0 =
*/;
> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=3D@@FOREIGN_KEY_CHECKS,=20
> FOREIGN_KEY_CHECKS=3D0 */;
> /*!40101 SET @OLD_SQL_MODE=3D@@SQL_MODE,=20
> SQL_MODE=3D"NO_AUTO_VALUE_ON_ZERO" */;
>
> --
> -- Table structure for table `foo`
> --
>
> DROP TABLE IF EXISTS `foo`;
> CREATE TABLE `foo` (
> `bar` enum('ä','ö','ü') NOT NULL default 'ö'
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1;
>
> /*!40101 SET SQL_MODE=3D@OLD_SQL_MODE */;
> /*!40014 SET FOREIGN_KEY_CHECKS=3D@OLD_FOREIGN_KEY_CHECKS */;
> /*!40014 SET UNIQUE_CHECKS=3D@OLD_UNIQUE_CHECKS */;
> /*!40101 SET CHARACTER_SET_CLIENT=3D@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 =20=

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

As you can see, the enum here is listed in Latin1 and the default seems=20=

to be in some
other coding but at least not consistent with the enum values itself.=20
This will not
import back and will generate an error that the default is not found in=20=

the enum list.

What can I do to fix this? Until then I am living dangerous because our=20=

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/bugs?unsub=3Dgcdmb-bugs@m.gmane.org