一、问题概述
1、近期需要在生产库新建账期表，在测试库中创建相同得表发现报没有主键的错误
mysql> CREATE TABLE `ljccdr202405` (
-> `rec_type` varchar(2) DEFAULT NULL,
-> `serv_type` varchar(2) DEFAULT NULL,
-> `content_type` varchar(2) DEFAULT NULL,
-> `sell_prov` varchar(3) DEFAULT NULL,
-> `client_id` varchar(12) DEFAULT NULL,
-> `custom_name` varchar(64) DEFAULT NULL,
-> `contract_id` varchar(15) DEFAULT NULL,
-> `order_id` varchar(12) DEFAULT NULL,
-> `product_id` varchar(24) DEFAULT NULL,
-> `action_id` varchar(12) DEFAULT NULL,
-> `action_date` varchar(8) DEFAULT NULL,
-> `target_prov` varchar(3) DEFAULT NULL,
-> `publication_name` varchar(32) DEFAULT NULL,
-> `split_num` varchar(4) DEFAULT NULL,
-> `ad_type` varchar(2) DEFAULT NULL,
-> `fee` decimal(9,2) DEFAULT NULL,
-> `telnum` varchar(22) DEFAULT NULL,
-> `billingcycle` decimal(6,0) DEFAULT NULL,
-> `subscriberid` varchar(13) DEFAULT NULL,
-> `sourfilename` varchar(40) DEFAULT NULL,
-> `accountid` varchar(13) DEFAULT NULL,
-> `destfilename` varchar(24) DEFAULT NULL,
-> `errorcode` varchar(4) DEFAULT NULL,
-> `processtime` datetime DEFAULT NULL,
-> `city_code` varchar(22) DEFAULT NULL,
-> `ad_id` varchar(22) DEFAULT NULL,
-> `sub_serv_type` varchar(22) DEFAULT NULL,
-> `package_info` varchar(64) DEFAULT NULL,
-> `tarifftrack` varchar(64) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
-> distributed by duplicate(g1);
ERROR 12805 (HY000): ERR: Must create table with primary key!
2、 sql_require_primary_key 这个参数还是off的
mysql> show variables like '%prim%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| sql_require_primary_key | OFF |
+-------------------------+-------+
1 row in set (0.01 sec)
二、 问题原因
中兴的zxdbproxy 用户下还有个参数文件proxy.ini ，
[zxdbproxy1@prigdb3 etc]$ pwd
/goldendb/app/zxdbproxy1/etc
[zxdbproxy1@prigdb3 etc]$ ll proxy.ini
-rw-r--r-- 1 zxdbproxy1 goldendb 50K 4月 25 15:21 proxy.ini
[zxdbproxy1@prigdb3 etc]$
在这个参数文件下，有关于 无主键功能相关配置
#--------------------------
# 无主键功能相关配置
#--------------------------
#无主键功能开关
##uint:NA，range:0-OFF,1-ON，default:0，dynamic:no;
support_without_primarykey = 0
三、解决方案
support_without_primarykey修改成1,这个参数是个静态参数，需要重启(注：每个节点都需要修改)
#--------------------------
# 无主键功能相关配置
#--------------------------
#无主键功能开关
##uint:NA，range:0-OFF,1-ON，default:0，dynamic:no;
support_without_primarykey = 1
[zxdbproxy1@prigdb3 etc]$ dbmoni -stop
[dbmoni -stop]The script dbmoni dbproxy process is stopped
[dbproxy]The dbproxy process is stopped
[dbmoni -stop]The script dbmoni ommagent process is stopped
[ommagent]The ommagent process is stopped
[zxdbproxy1@prigdb3 etc]$ dbmoni -start
[dbmoni -start]dbmoni dbproxy …starting…
[dbmoni -start]dbmoni ommagent …starting…
[zxdbproxy1@prigdb3 etc]$ dbstate
[dbproxy]The dbproxy process is running, PID:3941288
[ommagent]The ommagent process is running, PID:3941300
再次建表
[zxdbproxy1@prigdb3 ~]$ mysql -udbproxy -h 133.96.123.34 -p -P8880
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 301002
Server version: 8.9.99 Source distribution GoldenDB ZXCLOUD-GoldenDB-ALL-DBPROXYV6.1.03.05.r470d649 (Build build_time) (Commit 0)
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> CREATE TABLE ljccdr202406 (
-> rec_type varchar(2) DEFAULT NULL,
-> serv_type varchar(2) DEFAULT NULL,
-> content_type varchar(2) DEFAULT NULL,
-> sell_prov varchar(3) DEFAULT NULL,
-> client_id varchar(12) DEFAULT NULL,
-> custom_name varchar(64) DEFAULT NULL,
-> contract_id varchar(15) DEFAULT NULL,
-> order_id varchar(12),
-> product_id varchar(24) DEFAULT NULL,
-> action_id varchar(12) DEFAULT NULL,
-> action_date varchar(8) DEFAULT NULL,
-> target_prov varchar(3) DEFAULT NULL,
-> publication_name varchar(32) DEFAULT NULL,
-> split_num varchar(4) DEFAULT NULL,
-> ad_type varchar(2) DEFAULT NULL,
-> fee decimal(9,2) DEFAULT NULL,
-> telnum varchar(22) DEFAULT NULL,
-> billingcycle decimal(6,0) DEFAULT NULL,
-> subscriberid varchar(13) DEFAULT NULL,
-> sourfilename varchar(40) DEFAULT NULL,
-> accountid varchar(13) DEFAULT NULL,
-> destfilename varchar(24) DEFAULT NULL,
-> errorcode varchar(4) DEFAULT NULL,
-> processtime datetime DEFAULT NULL,
-> city_code varchar(22) DEFAULT NULL,
-> ad_id varchar(22) DEFAULT NULL,
-> sub_serv_type varchar(22) DEFAULT NULL,
-> package_info varchar(64) DEFAULT NULL,
-> tarifftrack varchar(64) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
-> distributed by duplicate(g1);
Query OK, 0 rows affected (0.05 sec)
mysql>
如果只修改了该节点，当连接上其他节点后，还是报错
[zxdbproxy1@prigdb3 ~]$ mysql -udbproxy -h 133.96.123.33 -p -P8880
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 201020
Server version: 8.9.99 Source distribution GoldenDB ZXCLOUD-GoldenDB-ALL-DBPROXYV6.1.03.05.r470d649 (Build build_time) (Commit 0)
Copyright © 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> CREATE TABLE ljccdr202407 (
-> rec_type varchar(2) DEFAULT NULL,
-> serv_type varchar(2) DEFAULT NULL,
-> content_type varchar(2) DEFAULT NULL,
-> sell_prov varchar(3) DEFAULT NULL,
-> client_id varchar(12) DEFAULT NULL,
-> custom_name varchar(64) DEFAULT NULL,
-> contract_id varchar(15) DEFAULT NULL,
-> order_id varchar(12),
-> product_id varchar(24) DEFAULT NULL,
-> action_id varchar(12) DEFAULT NULL,
-> action_date varchar(8) DEFAULT NULL,
-> target_prov varchar(3) DEFAULT NULL,
-> publication_name varchar(32) DEFAULT NULL,
-> split_num varchar(4) DEFAULT NULL,
-> ad_type varchar(2) DEFAULT NULL,
-> fee decimal(9,2) DEFAULT NULL,
-> telnum varchar(22) DEFAULT NULL,
-> billingcycle decimal(6,0) DEFAULT NULL,
-> subscriberid varchar(13) DEFAULT NULL,
-> sourfilename varchar(40) DEFAULT NULL,
-> accountid varchar(13) DEFAULT NULL,
-> destfilename varchar(24) DEFAULT NULL,
-> errorcode varchar(4) DEFAULT NULL,
-> processtime datetime DEFAULT NULL,
-> city_code varchar(22) DEFAULT NULL,
-> ad_id varchar(22) DEFAULT NULL,
-> sub_serv_type varchar(22) DEFAULT NULL,
-> package_info varchar(64) DEFAULT NULL,
-> tarifftrack varchar(64) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
-> distributed by duplicate(g1);
ERROR 12805 (HY000): ERR: Must create table with primary key!
mysql>
