MySQL Bugs: #20807: Reinstall mysql with different character set causes problem in columns datatype

Bug #20807	Reinstall mysql with different character set causes problem in columns datatype
Submitted:	2 Jul 2006 1:22	Modified:	3 Jul 2006 17:51
Reporter:	Carlos Collazos	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Linux (Linux, Windows)
Assigned to:		CPU Architecture:	Any

Description:
I had Mysql server installed with default (latin1) character set.the I Uninstalled Mysql server and reinstalled it again keeping my the databases (of course not mysql database) but installed Mysql server with unicode character set. As a result some of my queries started to give incoherent result because some of my index columns had length 0 for the datatype (i.e. (age char(?))). It only happened to my index columns all other table's columns remain untouched. It seems to be a problem with any change in character set (I also experienced the same with for example win1256 character set).

How to repeat:
Install mysql server with default character set. Create tables and some indexes over these tables. Uninstall mysql server. Reinstall mysql server using the same database created before but reinstall mysql server using for example unicode character set.

Hi Carloas and thanks for your problem report. I was unable to verify it with info provided.
First session, server defaults:
C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-log (BK)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists test20807;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test20807 (
    -> ID int unsigned not null auto_increment primary key,
    -> testdat1 char(20) not null,
    -> testdat2 char(25) not null,
    -> index key_testdat1(testdat1));
Query OK, 0 rows affected (0.05 sec)

mysql> show create table test20807\G
*************************** 1. row ***************************
       Table: test20807
Create Table: CREATE TABLE `test20807` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `testdat1` char(20) NOT NULL,
  `testdat2` char(25) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `key_testdat1` (`testdat1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show variables like "%char%";
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | latin1                      |
| character_set_connection | latin1                      |
| character_set_database   | latin1                      |
| character_set_filesystem | binary                      |
| character_set_results    | latin1                      |
| character_set_server     | latin1                      |
| character_set_system     | utf8                        |
| character_sets_dir       | C:\mysql507\share\charsets\ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql>  INSERT INTO `test`.`test20807` VALUES(NULL,'test dat 1, row 1','test dat
 2, row 1');
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO `test`.`test20807` VALUES(NULL,'testdat1, row2','testdat2, r
ow2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test20807\G
*************************** 1. row ***************************
      ID: 1
testdat1: test dat 1, row 1
testdat2: test dat 2, row 1
*************************** 2. row ***************************
      ID: 2
testdat1: testdat1, row2
testdat2: testdat2, row2
2 rows in set (0.00 sec)

mysql> quit

Second session, utf8:
mysql> show variables like "%char%";
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | C:\mysql507\share\charsets\ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> select * from test20807 where testdat1 like "% 1,%"\G
*************************** 1. row ***************************
      ID: 1
testdat1: test dat 1, row 1
testdat2: test dat 2, row 1
1 row in set (0.00 sec)

mysql> select * from test20807\G
*************************** 1. row ***************************
      ID: 1
testdat1: test dat 1, row 1
testdat2: test dat 2, row 1
*************************** 2. row ***************************
      ID: 2
testdat1: testdat1, row2
testdat2: testdat2, row2
2 rows in set (0.00 sec)

mysql>

Is there any way of attaching a table in this bug report?. 

If this helps. I created a table with mysql 5 and filled with data. I installed previously mysql 5 with latin1 as default character set. I defined three columns as primary keys (id, year, month). I uninstalled mysql. I reinstalled mysql using unicode as an option for default character set. I checked the table structure and found out that month is defined as month char(0). Issuing the query "select distinct month from ab" the query return no result even though there are rows in the table.

As I told you I could send you a table example (it happens with any table with index or primary key)

Carlos, use "Files" tab to add files to report. When you add files, put the state back to "Open" please. If you have troubles adding files, contact me again.

a table create with mysql 5 and latin1 character set

Attachment: ab.rar (application/x-rar, text), 144.16 KiB.

check the attached table with mysql 5 and unicode character set as default for the database, see the month colum

Hi Carlos. I can verify your findings but *only* with your table files:
C:\mysql507\bin>myisamchk.exe -d c:\mysql507\data\test\ab.MYI

MyISAM file:         c:\mysql507\data\test\ab.MYI
Record format:       Fixed length
Character set:       latin1_swedish_ci (8)
Data records:                 3971  Deleted blocks:             10003
Recordlength:                  126

table description:
Key Start Len Index   Type
1   5     8   unique  char
    13    4           char
    17    2           char

Latin1 test:
C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.23-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table ab\G
*************************** 1. row ***************************
       Table: ab
Create Table: CREATE TABLE `ab` (
  `PlaceId` char(8) NOT NULL default '',
  `Year` char(4) NOT NULL default '',
  `Month` char(2) NOT NULL default '',
  `AB1` int(11) default NULL,
  `AB2` int(11) default NULL,
  `AB3` int(11) default NULL,
  `AB4` int(11) default NULL,
  `AB5` int(11) default NULL,
  `AB6` int(11) default NULL,
  `AB7` int(11) default NULL,
  `AB8` int(11) default NULL,
  `AB9` int(11) default NULL,
  `AB10` int(11) default NULL,
  `AB11` int(11) default NULL,
  `AB12` int(11) default NULL,
  `AB13` int(11) default NULL,
  `AB14` int(11) default NULL,
  `AB15` int(11) default NULL,
  `AB16` int(11) default NULL,
  `AB17` int(11) default NULL,
  `AB18` int(11) default NULL,
  `AB19` int(11) default NULL,
  `AB20` int(11) default NULL,
  `AB21` int(11) default NULL,
  `AB22` int(11) default NULL,
  `AB23` int(11) default NULL,
  `AB24` int(11) default NULL,
  `AB25` int(11) default NULL,
  `AB26` int(11) default NULL,
  `AB27` int(11) default NULL,
  PRIMARY KEY  (`PlaceId`,`Year`,`Month`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

mysql>select * from ab\G
...
   AB24: 1
   AB25: 0
   AB26: 1
   AB27: 0
*************************** 3665. row ***************************
PlaceId: P1946
   Year: 2004
  Month: 12
    AB1: 4
    AB2: 1
    AB3: 4
    AB4: 0
    AB5: 0
    AB6: 2
    AB7: 2
    AB8: 0
    AB9: 4
   AB10: 0
   AB11: 0
   AB12: 4
Aborted

Switching to UTF8
mysql> show create table ab\G
*************************** 1. row ***************************
       Table: ab
Create Table: CREATE TABLE `ab` (
  `PlaceId` char(2) NOT NULL default '',
  `Year` char(1) NOT NULL default '',
  `Month` char(0) NOT NULL default '', <<<<
  `AB1` int(11) default NULL,
  `AB2` int(11) default NULL,
  `AB3` int(11) default NULL,
  `AB4` int(11) default NULL,
  `AB5` int(11) default NULL,
  `AB6` int(11) default NULL,
  `AB7` int(11) default NULL,
  `AB8` int(11) default NULL,
  `AB9` int(11) default NULL,
  `AB10` int(11) default NULL,
  `AB11` int(11) default NULL,
  `AB12` int(11) default NULL,
  `AB13` int(11) default NULL,
  `AB14` int(11) default NULL,
  `AB15` int(11) default NULL,
  `AB16` int(11) default NULL,
  `AB17` int(11) default NULL,
  `AB18` int(11) default NULL,
  `AB19` int(11) default NULL,
  `AB20` int(11) default NULL,
  `AB21` int(11) default NULL,
  `AB22` int(11) default NULL,
  `AB23` int(11) default NULL,
  `AB24` int(11) default NULL,
  `AB25` int(11) default NULL,
  `AB26` int(11) default NULL,
  `AB27` int(11) default NULL,
  PRIMARY KEY  (`PlaceId`,`Year`,`Month`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select distinct month from ab\G
*************************** 1. row ***************************
month: 1
1 row in set (0.09 sec)

But if I do:
  Set Latin1 as default
  Create table ab1 with same structure as ab
  INSERT INTO ab1 SELECT * FROM ab;
  Stop server
  Set UTF8 as default
  Start server
  Table ab1 works as expected.
Will cousult more on this.

Carlos, it looks like binary copy went wrong...
Try doing "ALTER TABLE ab ENGINE MyISAM;" and then use it again.

mysql> show variables like "%char%";
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | C:\mysql507\share\charsets\ |
+--------------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> select distinct month from ab;
+-------+
| month |
+-------+
| 1     |
| 10    |
| 11    |
| 12    |
| 2     |
| 3     |
| 4     |
| 5     |
| 6     |
| 7     |
| 8     |
| 9     |
+-------+
12 rows in set (0.02 sec)

What do you mean with binary copy?

I did not copy the table. I just reinstalled mysql in the same directory that the previous mysql installation was keeping the data subdirectory in the uninstall process. 

So everytime I do this process I have to use the alter table.... command??

Thank you.

Carlos, I was unable to repeat the problem using steps provided but that doesn't mean it's non-existent. I can't duplicate exact steps and environment you have. What I see is that table files are somehow modified which can happen during copy, for example. So after reinstall, if you expirience problems like the one reported you should use ALTER TABLE... It would be best to use propper upgrade procedure like stated in manual: http://dev.mysql.com/doc/refman/5.0/en/windows-upgrading.html