backport: support 'ALTER TABLE t1 CHARACTER SET = ...' clause

[hustjieke]

Is your feature request related to a problem? Please describe.

previous working issue: #848
commit id: cf1c98c

Describe the solution you'd like

Describe alternatives you've considered

Additional context

[hustjieke]

After backport, no data selected in mtr issue848.test

mysql> select hex(c_char),hex(c_varchar),hex(c_text) from t1; 
Empty set (0.00 sec)

actually the result should be:

mysql> select hex(c_char),hex(c_varchar),hex(c_text) from t1; 
+-------------+----------------+-------------+
| hex(c_char) | hex(c_varchar) | hex(c_text) |
+-------------+----------------+-------------+
| D6D0B9FA    | D5E3BDAD       | BABCD6DD    |
+-------------+----------------+-------------+
1 row in set (0.00 sec)

[hustjieke]

After backport, no data selected in mtr issue848.test

mysql> select hex(c_char),hex(c_varchar),hex(c_text) from t1; 
Empty set (0.00 sec)

actually the result should be:

mysql> select hex(c_char),hex(c_varchar),hex(c_text) from t1; 
+-------------+----------------+-------------+
| hex(c_char) | hex(c_varchar) | hex(c_text) |
+-------------+----------------+-------------+
| D6D0B9FA    | D5E3BDAD       | BABCD6DD    |
+-------------+----------------+-------------+
1 row in set (0.00 sec)

Simplify sql:

mysql> CREATE TABLE `t1` (   `c_char` char(10) DEFAULT NULL COMMENT 'char') ENGINE=TIANMU DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(x'D6D0B9FA');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------+
| c_char |
+--------+
| 中国   |
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY c_char char(10) CHARACTER SET UTF8MB4;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;
Empty set (0.00 sec)

[hustjieke]

After alter table t1, the commit version changed to 0

In 8.0, the data version missed after alter table ...:

$:/github/stonedb/build/install8/data/test# ls t1.tianmu/columns/0/v/
0000000000000000----->1. create table t1....
$:/github/stonedb/build/install8/data/test# ls t1.tianmu/columns/0/v/
6459a9c40000272a----->2. insert into t1 values....
$:/github/stonedb/build/install8/data/test# ls t1.tianmu/columns/0/v/
0000000000000000----->3. alter table t1 ....

In 5.7, data version is reserved

root@stonedb-gaoriyao:/github/stonedb/build/install57/data/test# ls t1.tianmu/columns/0/v/
0000000000000000----->1. create table t1....
root@stonedb-gaoriyao:/github/stonedb/build/install57/data/test# ls t1.tianmu/columns/0/v/
6458ee7d00002712----->2. insert into t1 values....
root@stonedb-gaoriyao:/github/stonedb/build/install57/data/test# ls t1.tianmu/columns/0/v/
0000000000000000  6458ee7d00002713----->3. alter table t1 ....

[hustjieke]

mysql> CREATE TABLE t1 ( c_char char(10) DEFAULT NULL COMMENT 'char') ENGINE=TIANMU DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(x'D6D0B9FA');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+--------+
| c_char |
+--------+
| 中国 |
+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY c_char char(10) CHARACTER SET UTF8MB4;
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t1;
Empty set (0.00 sec)

After debug, I found txn not commited, the commit logic had been skiped:

void Engine::CommitTx(THD *thd, bool all) {
  TIANMU_LOG(LogCtl_Level::INFO, "commit txn");

  // fix bug: issue759, in file sql_trunction.cc and function end_transaction(), will call CommitTx() twice
  // skip for first time, called in trans_commit_stmt(), thd->server_status = SERVER_STATUS_IN_TRANS and all = false
  // then called in trans_commit_implicit(), thd->server_status will be set "~SERVER_STATUS_IN_TRANS", and all = true
  // tianmu truncate table in 5.7 is ok, 8.0 has this problem.
  if ((thd->lex->sql_command == SQLCOM_TRUNCATE) && (thd->server_status & SERVER_STATUS_IN_TRANS)) {
    return;
  }
  if (all || !thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)) {------> condition is false
    GetTx(thd)->Commit(thd);
  }
  ClearTx(thd);
}

[hustjieke]

The bug is similar to #759, just like the comment said:

  // fix bug: issue759, in file sql_trunction.cc and function end_transaction(), will call CommitTx() twice
  // skip for first time, called in trans_commit_stmt(), thd->server_status = SERVER_STATUS_IN_TRANS and all = false
  // then called in trans_commit_implicit(), thd->server_status will be set "~SERVER_STATUS_IN_TRANS", and all = true
  // tianmu truncate table in 5.7 is ok, 8.0 has this problem.