MySQL Bugs: #73153: CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET

Bug #73153	CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET
Submitted:	30 Jun 2014 22:10	Modified:	1 Jul 2014 4:53
Reporter:	Daniel Black (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.6, 5.6.20, 5.7.5	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:

in the case where no text columns

original report: https://mariadb.atlassian.net/browse/MDEV-6390

How to repeat:
> select count(*) from seller_counters;
| count(*) |
|     1859 |

> alter table seller_counters CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

> show create table seller_counters;
| Table           | Create Table                                                                                                                                                                                                                  |
| seller_counters | CREATE TABLE `seller_counters` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `counter` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Hello Daniel,

Thank you for the bug report.

Thanks,
Umesh

// 5.6

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `seller_counters` (   `seller_id` int(11) NOT NULL,   `category_id` int(11) NOT NULL,   `counter` int(11) NOT NULL DEFAULT '0',   PRIMARY KEY (`seller_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.43 sec)

mysql> show create table seller_counters\G
*************************** 1. row ***************************
       Table: seller_counters
Create Table: CREATE TABLE `seller_counters` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `counter` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table seller_counters CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table seller_counters\G
*************************** 1. row ***************************
       Table: seller_counters
Create Table: CREATE TABLE `seller_counters` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `counter` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

// 5.7

mysql> CREATE TABLE `seller_counters` (
    ->   `seller_id` int(11) NOT NULL,
    ->   `category_id` int(11) NOT NULL,
    ->   `counter` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`seller_id`,`category_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.42 sec)

mysql> alter table seller_counters CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table seller_counters\G
*************************** 1. row ***************************
       Table: seller_counters
Create Table: CREATE TABLE `seller_counters` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `counter` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)

## Observed that this issue is happens when there are no char columns n the table

mysql> create table ttt(seller_id int not null, category_id int(11) NOT NULL DEFAULT '0',seller char(10) not null, PRIMARY KEY (`seller_id`,`category_id`))ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.34 sec)

mysql> show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL DEFAULT '0',
  `seller` char(10) NOT NULL,
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table ttt  CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.60 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL DEFAULT '0',
  `seller` char(10) NOT NULL,
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

This also seems to happen if the columns in the table are already the charset/collation that you're trying to convert the table to:

mysql> show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_string` varchar(255) CHARACTER SET utf8 NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table test_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_string` varchar(255) CHARACTER SET utf8 NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Maybe when there's no column to convert the default change is forgotten.

If you want to change default charset to such talbe use "default charset=utf8;" instead of "convert":

mysql> CREATE TABLE `seller_counters` (
    ->   `seller_id` int(11) NOT NULL,
    ->   `category_id` int(11) NOT NULL,
    ->   `counter` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`seller_id`,`category_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.28 sec)

mysql> alter TABLE `seller_counters` default charset=utf8;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create  TABLE `seller_counters`\G
*************************** 1. row ***************************
       Table: seller_counters
Create Table: CREATE TABLE `seller_counters` (
  `seller_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `counter` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`seller_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)