| Bug #5693 | mysqldump encoding of enum/default values | ||
|---|---|---|---|
| Submitted: | 22 Sep 2004 6:59 | Modified: | 27 Oct 2004 12:31 |
| Reporter: | Andreas Pardeike | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 10.7 Distrib 4.1.2-alpha | OS: | pc-linux (i686) |
| Assigned to: | CPU Architecture: | Any | |
[8 Oct 2004 11:33]
Hartmut Holzgraefe
I can't completely reproduce this, on my 4.1.2 test system
the default value for mediatype is completely empty in the dump
`mediatyp` enum('Bok','Ljudbok','Multimedia','Marknadsföringsmaterial','Övrigt') NOT NULL default '',
when inserting into table artikel the default for mediatyp isn't used either
can you please add the output of SHOW VARIABLES to this report?
[8 Oct 2004 17:15]
Andreas Pardeike
Here's the output for 'SHOW VARIABLES': mysql> show variables; +---------------------------------+---------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------+ | back_log | 50 | | basedir | / | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 1 | | ft_query_expansion_limit | 20 | | ft_stopword_file | | | group_concat_max_len | 1024 | | have_bdb | NO | | have_compress | YES | | have_crypt | YES | | have_innodb | YES | | have_isam | NO | | have_ndbcluster | NO | | have_openssl | NO | | have_query_cache | YES | | have_raid | NO | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_fast_shutdown | ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 8388600 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | license | GPL | | local_infile | ON | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_error | | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_update | OFF | | log_warnings | ON | | long_query_time | 2 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 268435456 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | ON | | open_files_limit | 1024 | | pid_file | /var/lib/mysql/bokrondellen.pid | | port | 3306 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | rpl_recovery_rank | 0 | | secure_auth | OFF | | server_id | 0 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slow_launch_time | 1 | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 2097144 | | sql_mode | | | storage_engine | MyISAM | | table_cache | 64 | | table_type | MyISAM | | thread_cache_size | 0 | | thread_stack | 126976 | | time_format | %H:%i:%s | | timezone | CEST | | tmp_table_size | 33554432 | | tmpdir | | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.2-alpha-standard | | version_comment | Official MySQL RPM | | version_compile_machine | i686 | | version_compile_os | pc-linux | | wait_timeout | 28800 | +---------------------------------+---------------------------------+ 163 rows in set (0.00 sec) mysql>
[8 Oct 2004 17:16]
Andreas Pardeike
And I would consider the missing default value a bug too. Might be the same problem but it shows different in 4.1.2 then in my version...
[20 Oct 2004 4:25]
Alexander Barkov
This bug was fixed quite long ago, but for some reasons it didn't get into ChangeLog. Andreas, you have to upgrade to make it work correctly. To support team: I suggest to close this bug report, and inform doc team.
[20 Oct 2004 4:28]
Alexander Barkov
This is the first bug report: http://bugs.mysql.com/bug.php?id=2077
Description: Under certain circumstances, mysqldump has problems encoding swedish letters in the table definition. This happens with enum fields and results in two different failures. a) all enum values contain questionmarks '?' instead of the umlaut b) all enum values contain latin1 encoded umlauts but the default value is encoded different and thus does not match any of the enum values. Regardless of which case happens it always differs from the normal behaviour of encoding in UTF8. The problem is not database or table related and is not reproducable with a simplified table layout (i.e. only one column total which is an enum field). It does however reproduce with our production layout which is listed under "How to repeat". I don't use the latest version (mine is 4.1.2-alpha-standard) but I did not read any fixes in mysqldump in the recent release notes so I assume that the problem is still there. How to repeat: Use the test database and insert the following table: DROP TABLE IF EXISTS `artikel`; CREATE TABLE `artikel` ( `t_changed` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `t_created` timestamp NOT NULL default '0000-00-00 00:00:00', `t_changed_bd` timestamp NOT NULL default '0000-00-00 00:00:00', `uppdaterad` timestamp NOT NULL default '0000-00-00 00:00:00', `granskad` enum('Ja','Nej') NOT NULL default 'Nej', `artikelnummer` varchar(13) NOT NULL default '', `hanvisnings_isbn` varchar(13) NOT NULL default '', `ean` varchar(16) NOT NULL default '', `titel` varchar(255) NOT NULL default '', `arbetstitel` varchar(255) NOT NULL default '', `interntitel` varchar(255) NOT NULL default '', `mediatyp` enum('Bok','Ljudbok','Multimedia','Marknadsföringsmaterial','Övrigt') NOT NULL default 'Övrigt', `bandtyp` varchar(32) NOT NULL default '', `forlag_id` int(11) NOT NULL default '0', `forlag` varchar(255) NOT NULL default '', `distributor_id` int(11) NOT NULL default '0', `moms` float NOT NULL default '0', `saljperiod` varchar(12) NOT NULL default '', `utgivningsdatum` timestamp NOT NULL default '0000-00-00 00:00:00', `upplagenummer` int(11) NOT NULL default '0', `omfang` int(11) NOT NULL default '0', `omfang_typ` enum('','sidor','CD','Kassett','DVD') NOT NULL default '', `bredd` int(11) NOT NULL default '0', `hojd` int(11) NOT NULL default '0', `ryggbredd` int(11) NOT NULL default '0', `vikt` int(11) NOT NULL default '0', `paket` enum('','Ja','Nej') NOT NULL default '', `paket_typ` enum('','A','B','C','D') NOT NULL default '', `antal_per_forpackning` int(11) NOT NULL default '0', `komponent` varchar(255) NOT NULL default '', `rea_ar` timestamp NOT NULL default '0000-00-00 00:00:00', `rea_fpris` float NOT NULL default '0', `reapris_fran_datum` timestamp NOT NULL default '0000-00-00 00:00:00', `illustrerad` enum('','Ja','Nej') NOT NULL default '', `laromedel` enum('','Ja','Nej') NOT NULL default '', `originaltitel` varchar(255) NOT NULL default '', `originalforlag` varchar(255) NOT NULL default '0', `bic` varchar(255) NOT NULL default '', `serie` varchar(128) NOT NULL default '', `lasordning` varchar(32) NOT NULL default '', `varugrupp` varchar(64) NOT NULL default '', `saga` text NOT NULL, `aldersgrupp` enum('','0-3','3-6','6-9','9-12','12-15','Unga vuxna') NOT NULL default '', `katalogtext` text NOT NULL, `kommentarfalt` varchar(255) NOT NULL default '', `internforfattare` text NOT NULL, PRIMARY KEY (`artikelnummer`), KEY `mediatyp` (`mediatyp`), KEY `bandtyp` (`bandtyp`), KEY `serie` (`serie`), KEY `bic` (`bic`), KEY `varugrupp` (`varugrupp`), KEY `interntitel` (`interntitel`), KEY `ean` (`ean`), KEY `originaltitel` (`originaltitel`), KEY `originalforlag` (`originalforlag`), KEY `rea_ar` (`rea_ar`), KEY `upplagenummer` (`upplagenummer`), KEY `forlag_id` (`forlag_id`), KEY `distributor_id` (`distributor_id`), KEY `t_changed` (`t_changed`), KEY `t_created` (`t_created`), KEY `saljperiod` (`saljperiod`), KEY `t_changed_bd` (`t_changed_bd`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; then use mysqldump to dump the table definition back to text by writing mysqldump test artikel and compare the result to the original table definition. Pay attention to the column 'mediatyp'. If the bug is present, it should encode wrong. On my system, it reads i.e. ... `mediatyp` enum('Bok','Ljudbok','Multimedia','Marknadsföringsmaterial','Övrigt') NOT NULL default 'Ãvrigt', ... My system is RedHat 9 with the following settings: root@bokrondellen:~> printenv 4 USER=root LOGNAME=root HOME=/root PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/ usr/X11R6/bin:/root/bin MAIL=/var/mail/root SHELL=/bin/tcsh TERM=xterm-color HOSTTYPE=i386-linux VENDOR=intel OSTYPE=linux MACHTYPE=i386 SHLVL=1 PWD=/root GROUP=root HOST=bokrondellen REMOTEHOST=fwgate1.fsys.se G_BROKEN_FILENAMES=1 SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass LANG=en_US SUPPORTED=en_US.UTF-8:en_US:en LESSOPEN=|/usr/bin/lesspipe.sh %s HOSTNAME=bokrondellen and my mysql version is 4.1.2-alpha-standard