Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

ibd2sql解析表结构DDL问题 #8

Open
nicholascp opened this issue Jan 26, 2024 · 2 comments
Open

ibd2sql解析表结构DDL问题 #8

nicholascp opened this issue Jan 26, 2024 · 2 comments

Comments

@nicholascp
Copy link

解析表结果时，如果列类型 timestamp且有默认值是，建表语句
xx timestamp default 'current_timestamp' ,不需要''

如果blob/text字段，在key里面，会丢失，长度
key(xx,text) ---> key(xx,text(11))
@ddcw
Copy link
Owner

ddcw commented Jan 26, 2024

解析表结果时，如果列类型 timestamp且有默认值是，建表语句 xx timestamp default 'current_timestamp' ,不需要''

如果blob/text字段，在key里面，会丢失，长度 key(xx,text) ---> key(xx,text(11))

感谢您提供下的BUG.

  1. 原先 未考虑默认值为函数的情况, 现在支持了. 取值为 sdi default_option
  2. 原先不支持非varchar的前缀索引, 现在支持了, 并区分了utf8mb4
    你可以下载新最新版的ibd2sql (源码) 去测试一下

如下为修复后的测试例子:

SHELL> python3 main.py /data/mysql_3314/mysqldata/ibd2sql/t20240126.ibd 
CREATE TABLE IF NOT EXISTS `ibd2sql`.`t20240126`(
    `id` int NOT NULL,
    `aa` varchar(200) NULL,
    `bb` blob NULL,
    `cc` timestamp DEFAULT (now()),
    `dd` datetime DEFAULT (now()),
    `ee` int NULL,
    PRIMARY KEY  (`id`),
    KEY `aa` (`aa`,`bb`(10)),
    KEY `bb` (`bb`(10),`aa`(10)),
    KEY `bb_2` (`bb`(20),`ee`),
    KEY `bb_3` (`bb`(30),`aa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

@nicholascp
Copy link
Author

nicholascp commented Jan 29, 2024 •
edited
Loading

dd datetime DEFAULT (now()) ---》建表的时候，函数也不需要（），索引那块tinytext好像还是有问题

root@mysql 09:25:  [cpbak]> create table test(id timestamp default now());
Query OK, 0 rows affected (0.01 sec)

root@mysql 09:25:  [cpbak]> create table test1(id timestamp default (now()));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(now()))' at line 1



[root@vm1 py]# python3 main.py /opt/data/mysql8/orch/database_instance_topology_history.ibd
CREATE TABLE IF NOT EXISTS `orch`.`database_instance_topology_history`(
    `snapshot_unix_timestamp` int unsigned NOT NULL,
    `hostname` varchar(128) NOT NULL,
    `port` smallint unsigned NOT NULL,
    `master_host` varchar(128) NOT NULL,
    `master_port` smallint unsigned NOT NULL,
    `cluster_name` tinytext NOT NULL,
    `version` varchar(128) NOT NULL,
    PRIMARY KEY  (`snapshot_unix_timestamp`,`hostname`,`port`),
    KEY `cluster_name_idx_database_instance_topology_history` (`snapshot_unix_timestamp`,`cluster_name`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_general_ci ;

tinytext好像还是没有带上长度

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@nicholascp @ddcw