mysql用CATS方式备份数据报This table type requires a primary key错误
来源:互联网 发布:注册淘宝 编辑:程序博客网 时间:2024/06/07 09:24
一.问题描述:
版本:10.1.16-MariaDB
在对表进行数据修改操作前,需要用CATS方式对数据进行备份,然后执行时发现报错如下:
create table a as select * from g_w_compare;
ERROR 1173 (42000): This table type requires a primary key
二.问题分析
从上面报错很明显是建立的表需要有主键,也就是说有限制建表时一定要有主键,才能建成功。
查看mysql强制主键参数如下:
show global variables like 'innodb_force_primary_key';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_force_primary_key | ON |
+--------------------------+-------+
有开启强制主键功能。
查看表结构:
show create table g_w_compare\G
| g_w_compare | CREATE TABLE `g_w_compare` (
`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`id` varchar(50) NOT NULL COMMENT '系统自动生成',
`finance_ware_code` varchar(32) DEFAULT NULL ,
`finance_ware_name` varchar(32) DEFAULT NULL ,
`ware_code` varchar(32) DEFAULT NULL COMMENT ,
`ware_name` varchar(32) DEFAULT NULL COMMENT ,
`rec_status` tinyint(1) DEFAULT '0',
`create_time` datetime DEFAULT NULL ,
`creator` varchar(36) DEFAULT NULL ,
`modify_time` datetime DEFAULT NULL,
`MODIFIER` varchar(36) DEFAULT NULL ,
`company_code` varchar(32) DEFAULT NULL,
`tenant_code` varchar(32) DEFAULT NULL,
`recVer` int(11) DEFAULT NULL,
PRIMARY KEY (`_id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
三.问题解决
重新在建表时加上对应的主键定义:
create table a (`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',PRIMARY KEY (`_id`)) as select * from g_w_compare;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
show create table a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`id` varchar(50) NOT NULL COMMENT '系统自动生成',
`finance_ware_code` varchar(32) DEFAULT NULL,
`finance_ware_name` varchar(32) DEFAULT NULL,
`ware_code` varchar(32) DEFAULT NULL COMMENT ,
`ware_name` varchar(32) DEFAULT NULL COMMENT,
`rec_status` tinyint(1) DEFAULT '0',
`create_time` datetime DEFAULT NULL,
`creator` varchar(36) DEFAULT NULL ,
`modify_time` datetime DEFAULT NULL,
`MODIFIER` varchar(36) DEFAULT NULL,
`company_code` varchar(32) DEFAULT NULL,
`tenant_code` varchar(32) DEFAULT NULL,
`recVer` int(11) DEFAULT NULL,
PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
查询最新主键ID号对比是否相同:
select `_id` from a order by `_id` desc limit 1,2;
+-----+
| _id |
+-----+
| 841 |
| 840 |
+-----+
select `_id` from `g_w_compare` order by `_id` desc limit 1,2;
+-----+
| _id |
+-----+
| 841 |
| 840 |
+-----+
2 rows in set (0.00 sec)
select count(*) from `g_w_compare`;
+----------+
| count(*) |
+----------+
| 242 |
+----------+
1 row in set (0.00 sec)
select count(*) from b;
+----------+
| count(*) |
+----------+
| 242 |
+----------+
1 row in set (0.00 sec)
上面数据完全相同。
- mysql用CATS方式备份数据报This table type requires a primary key错误
- MySQL 表分区 A PRIMARY KEY must include all columns in the table's partitioning function
- mysql primary key key
- 重建分区表主键 - Recreate Primary Key on a partition table
- MySql插入数据时错误Duplicate entry '131' for key 'PRIMARY'
- MySql插入数据时错误Duplicate entry '131' for key 'PRIMARY'
- mysql报错:Duplicate entry ‘1’ for key ‘PRIMARY’
- Mysql错误:Duplicate entry '127' for key 'PRIMARY'的解决方法
- MySQL:Duplicate entry '1-2' for key 'PRIMARY' 错误
- mysql出现Duplicate entry '0' for key 'PRIMARY'的错误
- Mysql错误提示:Error: Duplicate entry '0' for key 'PRIMARY'
- 【mysql】【报错】Mysql外键名重复的错误:duplicate key in table '#sql-*'
- PLSQL Tools for query primary key and foreign key of a table
- 一例Unable to determine composite primary key ordering for type错误的解决
- 解决“requires a Promise polyfill in this browser”错误
- 解决“requires a Promise polyfill in this browser”错误
- ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
- MySQL如何创建主键?Alter table add PRIMARY KEY的用法和例子
- linux下devicetree中常用的of函数
- hiho#10362 最长回文子串
- jQuery.validator 验证规则详解
- python os模块 常用命令
- TestNG介绍
- mysql用CATS方式备份数据报This table type requires a primary key错误
- Handlebars学习之——块表达式
- 虚拟机字节码执行引擎
- 【BZOJ 1055】【HAOI 2008】玩具取名 【区间DP】
- WEB前端开发书籍
- 2016-11-14 我的第一篇博客
- 机器学习:特征选择之 Filter :Relief方法
- Buy and sell stock at best time(i,ii,iii)
- 第十一周-判断三角形