Nacos使用sqlserver做持久化
背景：因为甲方爸爸项目要求使用sqlserver，我们得对nacos做相关适配
对于刚刚做完pg适配的我来说这不是手拿把掐直接开始操作，拉源码，改配置，换驱动，做适配；
一、 拉取源码
从github拉取nacos源码,版本1.4.4
https://github.com/alibaba/nacos/releases/tag/1.4.4
二、修改配置文件
修改console模块中的application.properties配置文件
#*************** postgresql数据库的支持 ***************#
### If use PG as datasource:
spring.datasource.platform=mssql
### Count of DB:
db.num=1
### Connect URL of DB:
db.url.0=jdbc:sqlserver://localhost:1433;databaseName=nacos
db.user.0=test
db.password.0=test
#*************** postgresql数据库的支持 ***************#
三、引入pg驱动
1.修改pom
nacos-all添加依赖
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>9.2.1.jre8</version>
</dependency>
naming模块中添加依赖
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
</dependency>
config模块中添加依赖
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
</dependency>
2.修改驱动加载相关类
ExternalDataSourceProperties.java
//源码
poolProperties.setDriverClassName(JDBC_DRIVER_NAME);
//修改为sqlserver加载
String driverClassName = JDBC_DRIVER_NAME;
if("mssql".equals(EnvUtil.getProperty("spring.datasource.platform"))){
driverClassName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
}
poolProperties.setDriverClassName(driverClassName);
相关问提见：第九节 3.容器化部署报错
四、修改配置类
PropertyUtil.java
// 源码
// External data sources are used by default in cluster mode
setUseExternalDB("mysql".equalsIgnoreCase(getString("spring.datasource.platform", "")));
// 修改为支持postgresql
String platfrom = getString("spring.datasource.platform", "");
setUseExternalDB("mysql".equalsIgnoreCase(platfrom) || "postgresql".equalsIgnoreCase(platfrom)||"mssql".equalsIgnoreCase(platfrom));
StartingApplicationListener.java
// 源码
boolean useExternalStorage = ("mysql".equalsIgnoreCase(env.getProperty("spring.datasource.platform", "")));
// 修改为支持postgresql
String platform = env.getProperty("spring.datasource.platform", "");
boolean useExternalStorage = ("mysql".equalsIgnoreCase(platform) || "postgresql".equalsIgnoreCase(platform));
五、修改sql
修改获取返回主键ID，GroupCapacityPersistService.java、TenantCapacityPersistService.java、ExternalStoragePersistServiceImpl.java
// 源码
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// 修改为支持sqlserver
PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
ExternalStoragePaginationHelperImpl.java
// 源码
selectSql = sqlFetchRows + " limit " + startRow + "," + pageSize;
// 修改为支持postgresql
selectSql = sqlFetchRows + "order by 1 ASC OFFSET " + startRow + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
修改角色模糊查询ExternalRolePersistServiceImpl.java
@Override
public List<String> findRolesLikeRoleName(String role) {
// String sql = "SELECT role FROM roles WHERE role like '%' ? '%'";
// List<String> users = this.jt.queryForList(sql, new String[] {role}, String.class);
// return users;
String sql = "SELECT role FROM roles WHERE role like '%" + role + "%'";
List<String> users = this.jt.queryForList(sql, null, String.class);
return users;
}
修改用户模糊查询ExternalUserPersistServiceImpl.java
@Override
public List<String> findUserLikeUsername(String username) {
// String sql = "SELECT username FROM users WHERE username like '%' ? '%'";
// List<String> users = this.jt.queryForList(sql, new String[]{username}, String.class);
// return users;
String sql = "SELECT username FROM users WHERE username like '%" + username + "%'";
List<String> users = this.jt.queryForList(sql, null, String.class);
return users;
}
利用工具全局搜索，将所有limit ?,? 替换为 OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
ExternalStoragePersistServiceImpl.java
@Override
public Page<ConfigInfoWrapper> findAllConfigInfoFragment(final long lastMaxId, final int pageSize) {
String select = "SELECT id,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,type from config_info where id > ? order by id asc offset ? limit ?";
PaginationHelper<ConfigInfoWrapper> helper = createPaginationHelper();
@Override
public Page<ConfigInfoAggr> findConfigInfoAggrByPage(String dataId, String group, String tenant, final int pageNo,
final int pageSize) {
String tenantTmp = StringUtils.isBlank(tenant) ? StringUtils.EMPTY : tenant;
String sqlCountRows = "SELECT COUNT(*) FROM config_info_aggr WHERE data_id = ? and group_id = ? and tenant_id = ?";
String sqlFetchRows =
"select data_id,group_id,tenant_id,datum_id,app_name,content from config_info_aggr where data_id=? and "
+ "group_id=? and tenant_id=? order by datum_id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
EmbeddedStoragePersistServiceImpl.java
@Override
public Page<ConfigInfoWrapper> findAllConfigInfoFragment(final long lastMaxId, final int pageSize) {
String select =
"SELECT id,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,type from config_info where id > ? "
+ "order by id asc offset ? limit ?";
@Override
public Page<ConfigInfoAggr> findConfigInfoAggrByPage(String dataId, String group, String tenant, final int pageNo,
final int pageSize) {
String tenantTmp = StringUtils.isBlank(tenant) ? StringUtils.EMPTY : tenant;
String sqlCountRows = "SELECT COUNT(*) FROM config_info_aggr WHERE data_id = ? and group_id = ? and tenant_id = ?";
String sqlFetchRows =
"select data_id,group_id,tenant_id,datum_id,app_name,content from config_info_aggr where data_id=? and "
+ "group_id=? and tenant_id=? order by datum_id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
Excess：额外的修改
基于之前pg的经验优先处理以下问题
1.编译报错
找不到com.alibaba.nacos.consistency.entity.*包；
解决：mvn compile 目标包是由protobuf在编译时自动生成的
2.打包报错
插件版本过高，降低插件版本为2.6.4
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.6.4</version>
</plugin>
3.容器化部署报错
报错原因：切换pg后驱动切换失败 见第三节 2.修改驱动加载相关类
解决方案：源码中驱动是直接写死的mysql 这边直接暴力解决更换成sqlserver
//源码
//private static final String JDBC_DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
//修改
private static final String JDBC_DRIVER_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
六、 SQLSERVER脚本
CREATE TABLE config_info (
id int identity(1,1) NOT NULL,
data_id varchar(255) NOT NULL ,
group_id varchar(255) DEFAULT NULL,
content nvarchar(max) NOT NULL ,
md5 varchar(32) DEFAULT NULL ,
gmt_create datetime NOT NULL DEFAULT '2010-05-05 00:00:00' ,
gmt_modified datetime NOT NULL DEFAULT '2010-05-05 00:00:00' ,
src_user nvarchar(max) ,
src_ip varchar(20) DEFAULT NULL ,
app_name varchar(128) DEFAULT NULL,
tenant_id varchar(128) DEFAULT '' ,
c_desc nvarchar(256) DEFAULT NULL,
c_use nvarchar(64) DEFAULT NULL,
effect nvarchar(64) DEFAULT NULL,
type nvarchar(64) DEFAULT NULL,
c_schema nvarchar(max),
PRIMARY KEY (id),
constraint uk_configinfo_datagrouptenant unique(data_id,group_id,tenant_id)
) ;
--comment on table config_info is 'config_info';
--comment on column config_info.id is 'id';
--comment on column config_info.content is 'content';
--comment on column config_info.md5 is 'md5';
--comment on column config_info.gmt_create is '创建时间';
--comment on column config_info.gmt_modified is '修改时间';
--comment on column config_info.src_user is 'source user';
--comment on column config_info.src_ip is 'source ip';
--comment on column config_info.tenant_id is '租户字段';
CREATE TABLE config_info_aggr (
id int IDENTITY(1,1) NOT NULL,
data_id varchar(255) NOT NULL ,
group_id varchar(255) NOT NULL ,
datum_id varchar(255) NOT NULL ,
content nvarchar(max) NOT NULL ,
gmt_modified datetime NOT NULL ,
app_name varchar(128) DEFAULT NULL,
tenant_id varchar(128) DEFAULT '' ,
PRIMARY KEY (id),
constraint uk_configinfoaggr_datagrouptenantdatum unique(data_id,group_id,tenant_id,datum_id)
);
--comment on table config_info_aggr is '增加租户字段';
--comment on column config_info_aggr.id is 'id';
--comment on column config_info_aggr.data_id is 'data_id';
--comment on column config_info_aggr.group_id is 'group_id';
--comment on column config_info_aggr.datum_id is 'datum_id';
--comment on column config_info_aggr.content is '内容';
--comment on column config_info_aggr.gmt_modified is '修改时间';
--comment on column config_info_aggr.tenant_id is '租户字段';
CREATE TABLE config_info_beta (
id int identity(1,1) NOT NULL,
data_id varchar(255) NOT NULL ,
group_id varchar(128) NOT NULL ,
app_name varchar(128) DEFAULT NULL ,
content nvarchar(max) NOT NULL ,
beta_ips varchar(1024) DEFAULT NULL ,
md5 varchar(32) DEFAULT NULL ,
gmt_create datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
gmt_modified datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
src_user nvarchar(max) ,
src_ip varchar(20) DEFAULT NULL ,
tenant_id varchar(128) DEFAULT '',
PRIMARY KEY (id),
constraint uk_configinfobeta_datagrouptenant unique(data_id,group_id,tenant_id)
);
--comment on table config_info_beta is 'config_info_beta';
--comment on column config_info_beta.id is 'id';
--comment on column config_info_beta.data_id is 'data_id';
--comment on column config_info_beta.group_id is 'group_id';
--comment on column config_info_beta.app_name is 'app_name';
--comment on column config_info_beta.content is 'content';
--comment on column config_info_beta.beta_ips is 'betaIps';
--comment on column config_info_beta.md5 is 'md5';
--comment on column config_info_beta.gmt_create is '创建时间';
--comment on column config_info_beta.gmt_modified is '修改时间';
--comment on column config_info_beta.src_user is 'source user';
--comment on column config_info_beta.src_ip is 'source ip';
--comment on column config_info_beta.tenant_id is '租户字段';
CREATE TABLE config_info_tag (
id int identity(1,1) NOT NULL,
data_id varchar(255) NOT NULL ,
group_id varchar(128) NOT NULL ,
tenant_id varchar(128) DEFAULT '' ,
tag_id varchar(128) NOT NULL ,
app_name varchar(128) DEFAULT NULL,
content nvarchar(max) NOT NULL ,
md5 varchar(32) DEFAULT NULL ,
gmt_create datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
gmt_modified datetime NOT NULL DEFAULT '2010-05-05 00:00:00' ,
src_user nvarchar(max) ,
src_ip varchar(20) DEFAULT NULL ,
PRIMARY KEY (id),
constraint uk_configinfotag_datagrouptenanttag unique(data_id,group_id,tenant_id,tag_id)
) ;
--comment on table config_info_tag is 'config_info_tag';
--comment on column config_info_tag.id is 'id';
--comment on column config_info_tag.data_id is 'data_id';
--comment on column config_info_tag.group_id is 'group_id';
--comment on column config_info_tag.tenant_id is 'tenant_id';
--comment on column config_info_tag.tag_id is 'tag_id';
--comment on column config_info_tag.app_name is 'app_name';
--comment on column config_info_tag.content is 'content';
--comment on column config_info_tag.md5 is 'md5';
--comment on column config_info_tag.gmt_create is '创建时间';
--comment on column config_info_tag.gmt_modified is '修改时间';
--comment on column config_info_tag.src_user is 'source user';
--comment on column config_info_tag.src_ip is 'source ip';
--comment on column config_info_tag.tenant_id is '租户字段';
CREATE TABLE config_tags_relation (
id bigint NOT NULL ,
tag_name varchar(128) NOT NULL ,
tag_type varchar(64) DEFAULT NULL ,
data_id varchar(255) NOT NULL ,
group_id varchar(128) NOT NULL ,
tenant_id varchar(128) DEFAULT '' ,
nid int identity(1,1) NOT NULL,
PRIMARY KEY (nid),
constraint uk_configtagrelation_configidtag unique(id,tag_name,tag_type)
) ;
--comment on table config_tags_relation is 'config_tag_relation';
--comment on column config_tags_relation.id is 'id';
--comment on column config_tags_relation.tag_name is 'tag_name';
--comment on column config_tags_relation.tag_type is 'tag_type';
--comment on column config_tags_relation.data_id is 'data_id';
--comment on column config_tags_relation.group_id is 'group_id';
--comment on column config_tags_relation.tenant_id is 'tenant_id';
CREATE TABLE group_capacity (
id int identity(1,1) NOT NULL ,
group_id varchar(128) NOT NULL DEFAULT '',
quota int NOT NULL DEFAULT '0' CHECK (quota >= 0) ,
usage int NOT NULL DEFAULT '0' CHECK (usage >= 0),
max_size int NOT NULL DEFAULT '0' CHECK (max_size >= 0),
max_aggr_count int NOT NULL DEFAULT '0' CHECK (max_aggr_count >= 0),
max_aggr_size int NOT NULL DEFAULT '0' CHECK (max_aggr_size >= 0),
max_history_count int NOT NULL DEFAULT '0' CHECK (max_history_count >= 0),
gmt_create datetime NOT NULL DEFAULT '2010-05-05 00:00:00' ,
gmt_modified datetime NOT NULL DEFAULT '2010-05-05 00:00:00' ,
PRIMARY KEY (id),
constraint uk_group_id unique(group_id)
) ;
--comment on table group_capacity is '集群、各Group容量信息表';
--comment on column group_capacity.id is '主键ID';
--comment on column group_capacity.group_id is 'Group ID，空字符表示整个集群';
--comment on column group_capacity.quota is '配额，0表示使用默认值';
--comment on column group_capacity.usage is '使用量';
--comment on column group_capacity.max_size is '单个配置大小上限，单位为字节，0表示使用默认值';
--comment on column group_capacity.max_aggr_count is '聚合子配置最大个数，，0表示使用默认值';
--comment on column group_capacity.max_aggr_size is '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值';
--comment on column group_capacity.max_history_count is '最大变更历史数量';
--comment on column group_capacity.gmt_create is '创建时间';
--comment on column group_capacity.gmt_modified is '修改时间';
CREATE TABLE his_config_info (
id bigint NOT NULL CHECK (id >= 0),
nid int identity(1,1) NOT NULL CHECK (nid >= 0),
data_id varchar(255) NOT NULL ,
group_id varchar(128) NOT NULL,
app_name varchar(128) DEFAULT NULL ,
content nvarchar(max) NOT NULL,
md5 varchar(32) DEFAULT NULL,
gmt_create datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
gmt_modified datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
src_user nvarchar(max),
src_ip varchar(20) DEFAULT NULL,
op_type char(10) DEFAULT NULL,
tenant_id varchar(128) DEFAULT '' ,
PRIMARY KEY (nid)
);
--comment on table his_config_info is '多租户改造';
--comment on column his_config_info.app_name is 'app_name';
--comment on column his_config_info.tenant_id is '租户字段';
CREATE TABLE tenant_capacity (
id int IDENTITY(1,1) NOT NULL CHECK (id >= 0),
tenant_id varchar(128) NOT NULL DEFAULT '',
quota int NOT NULL DEFAULT '0' CHECK (quota >= 0),
usage int NOT NULL DEFAULT '0' CHECK (usage >= 0),
max_size int NOT NULL DEFAULT '0' CHECK (max_size >= 0),
max_aggr_count int NOT NULL DEFAULT '0' CHECK (max_aggr_count >= 0),
max_aggr_size int NOT NULL DEFAULT '0' CHECK (max_aggr_size >= 0),
max_history_count int NOT NULL DEFAULT '0' CHECK (max_history_count >= 0),
gmt_create datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
gmt_modified datetime NOT NULL DEFAULT '2010-05-05 00:00:00',
PRIMARY KEY (id),
CONSTRAINT uk_tenant_id UNIQUE (tenant_id)
);
--comment on table tenant_capacity is '租户容量信息表';
--comment on column tenant_capacity.id is '主键ID';
--comment on column tenant_capacity.tenant_id is 'Tenant ID';
--comment on column tenant_capacity.quota is '配额，0表示使用默认值';
--comment on column tenant_capacity.usage is '使用量';
--comment on column tenant_capacity.max_size is '单个配置大小上限，单位为字节，0表示使用默认值';
--comment on column tenant_capacity.max_aggr_count is '聚合子配置最大个数';
--comment on column tenant_capacity.max_aggr_size is '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值';
--comment on column tenant_capacity.max_history_count is '最大变更历史数量';
--comment on column tenant_capacity.gmt_create is '创建时间';
--comment on column tenant_capacity.gmt_modified is '修改时间';
CREATE TABLE tenant_info (
id int identity(1,1) NOT NULL ,
kp varchar(128) NOT NULL,
tenant_id varchar(128) default '' ,
tenant_name varchar(128) default '' ,
tenant_desc varchar(256) DEFAULT NULL ,
create_source varchar(32) DEFAULT NULL ,
gmt_create bigint NOT NULL,
gmt_modified bigint NOT NULL ,
PRIMARY KEY (id),
constraint uk_tenant_info_kptenantid unique(kp,tenant_id)
);
--comment on table tenant_info is 'tenant_info';
--comment on column tenant_info.id is 'id';
--comment on column tenant_info.kp is 'kp';
--comment on column tenant_info.tenant_id is 'tenant_id';
--comment on column tenant_info.tenant_name is 'tenant_name';
--comment on column tenant_info.tenant_desc is 'tenant_desc';
--comment on column tenant_info.create_source is 'create_source';
--comment on column tenant_info.gmt_create is '创建时间';
--comment on column tenant_info.gmt_modified is '修改时间';
CREATE TABLE users (
username varchar(50) NOT NULL PRIMARY KEY,
password varchar(500) NOT NULL,
enabled bit NOT NULL
);
CREATE TABLE roles (
username varchar(50) NOT NULL,
role varchar(50) NOT NULL
);
CREATE UNIQUE INDEX idx_user_role ON roles (username ASC,role ASC);
CREATE TABLE permissions (
role varchar(50) NOT NULL,
resource varchar(255) NOT NULL,
action varchar(8) NOT NULL
);
CREATE UNIQUE INDEX uk_role_permission ON permissions (role,resource,action);
INSERT INTO users (username, password, enabled) VALUES ('nacos', '$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu', 1);
INSERT INTO roles (username, role) VALUES ('nacos', 'ROLE_ADMIN');
七、源码打包
mvn -Prelease-nacos '-Dmaven.test.skip=true' '-Dpmd.skip=true' '-Dcheckstyle.skip=true' '-Drat.skip=true' clean install -U
跳过编码检查和测试
位置 distribution/target
八、源码启动
1.初始化数据源
创建数据库nacos（随意配置）
执行PG脚本初始化数据
修改distribution/conf/路径下的application.properties文件中的数据库连接部分（如下配置）
### If use PG as datasource:
spring.datasource.platform=mssql
### Count of DB:
db.num=1
### Connect URL of DB:
db.url.0=jdbc:sqlserver://localhost:1433;databaseName=nacos
db.user.0=test
db.password.0=test
2. 配置启动参数
启动nacos-console
配置启动参数为：
-Dnacos.standalone=true 单机模式启动
-Dnacos.home=D:\Gitee\nacos-1.4.4.tar\nacos-1.4.4\nacos-1.4.4\distribution 源码中distribution文件夹的位置
访问地址：http://localhost:8848/nacos/
九、问题
1.登录报错
不出意外的话还是除了意外，正常启动部署没问题，但是登录报错，淦！！！
首先定位到login接口/v1/auth/users/login -->UserController.java -->login（）
开启debug追踪，最终定位到NacosAuthManager.java --> resolveTokenFromUser()方法，这边调用到springscecurity内部的接口没有深究
这边利用浏览器缓存先切换到pg库成功登录获取到token，然后在切换到sqlserver绕过登录，考虑到可能是加密的问题直接修改密码发现还是报错“用户名或者密码错误“
换个思路在后台新建一个用户尝试用新用户登录，这时发现问题，用户列表加载失败，怀疑是SQL语句不兼容问题，追踪查询用户列表接口GET /v1/auth/users -->UserController.java --> getUsers()，开启debug追踪，定位到ExternalStoragePaginationHelperImpl.java --> fetchPage() 发现分页语法错误导致查询用户列表失败
//源码
selectSql = sqlFetchRows + "OFFSET " + startRow + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
//修改
selectSql = sqlFetchRows + "order by 1 ASC OFFSET " + startRow + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
// sqlserver 的分页语法， OFFSET ？ ROWS FETCH NEXT ？ROWS ONLY 必须要接在 order by 后面
见第五节：修改SQL 已经修改为正确版本
高谈阔论