开源的从16.1升级到18.7 （16.1的版本也是一路升级过来的）已解决悬赏5积分

提问者蜡笔小新答案数1 阅读数438 发表时间2023-12-06 18:09:13

报错如下 mysql 版本是5.5.45

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'updatedDate'The sql is:
CREATE TABLE IF NOT EXISTS `zt_solution` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` char(50),
`appID` mediumint(8) unsigned NOT NULL,
`appName` char(50) NOT NULL,
`appVersion` char(20) NOT NULL,
`version` char(50) NOT NULL,
`chart` char(50) NOT NULL,
`cover` varchar(255),
`desc` text,
`introduction` varchar(500),
`source` char(20) NOT NULL,
`channel` char(20),
`components` text,
`status` char(20) NOT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT 0,
`createdBy` char(30) NOT NULL,
`createdAt` datetime NOT NULL,
`updatedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'createdDate'The sql is:
CREATE TABLE `zt_artifactrepo` (
`id` smallint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(45) CHARACTER SET utf8 NOT NULL,
`products` varchar(1000) CHARACTER SET utf8 NOT NULL,
`serverID` smallint(8) NOT NULL,
`repoName` varchar(45) CHARACTER SET utf8 NOT NULL,
`format` varchar(10) CHARACTER SET utf8 NOT NULL,
`type` char(7) CHARACTER SET utf8 NOT NULL,
`status` varchar(10) CHARACTER SET utf8 NOT NULL,
`createdBy` varchar(30) CHARACTER SET utf8 NOT NULL,
`createdDate` datetime DEFAULT CURRENT_TIMESTAMP,
`editedBy` varchar(30) CHARACTER SET utf8 NOT NULL,
`editedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`deleted` tinyint(4) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

禅道版本：禅道开源版 18.7

安装包类型：源码包

操作系统：CentOS

客户端浏览器：Chrome

答案列表

🍋

朱金勇 2023/12/07 最佳答案

这个错误是因为禅道代码中使用了MySQL5.5版本不支持的语法特性。
您可以下载最新的禅道18.9版本，该版本优化了MySQL的兼容性，支持MySQL5.5的安装升级。
基于安全、性能和兼容性考虑，建议您升级一下服务器的数据库版本，MySQL5.5官方已于2021年停止维护和安全更新。

7条回复

😎

蜡笔小新 2023/12/07

下载最新的禅道18.9版本数据怎么迁移呢（跨版本迁移有没有文档），一键安装包安装的怎么升级mysql

⛄

马超 2023/12/07

升级之前是否有做备份，可以使用备份导入全新安装的18.9版本中。
1、搭建18.9的禅道
18.9下载地址：https://www.zentao.net/download/zentaopms18.9-83122.html
linux一键安装包使用手册：https://www.zentao.net/book/zentaopms/90.html
2、将备份文件 sql.php 用文本编辑器打开删除开头的 <?php die();?>，然后重命名去掉.php，然后导入禅道数据库中；
禅道一键安装包访问数据库可以参考这个目录格式（/opt/zbox/bin/mysql -h 127.0.0.1 -u root -P 3306 -p ），登录禅道数据库之后可以使用source 命令导入sql，注意：导入之前需要先清空下18.9版本所有表。
3、将备份中的附件.file目录下1文件放到新禅道 /opt/zbox/app/www/data/upload/ 对应的目录下面。
4、可以再次下载禅道18.9安装包：https://www.zentao.net/dl/zentao/18.9/ZenTaoPMS-18.9-php7.2_7.4.zip
下载之后解压得到zentaopms目录，复制zentaopms/www/upgrade.php到/opt/zbox/app/zentao/www 目录下，增加读写权限。
5、然后访问禅道按照提示进行升级

😎

蜡笔小新 2023/12/07

mcood 2023/12/07
升级之前是否有做备份，可以使用备份导入全新安装的18.9版本中。
1、搭建18.9的禅道
18.9下载地址：https://www.zentao.net/download/zentaopms18.9-83122.html
linux一键安装包使用手册：https://www.zentao.net/book/zentaopms/90.html
2、将备份文件 sql.php 用文本编辑器打开删除开头的 <?php die();?>，然后重命名去掉.php，然后导入禅道数据库中；
禅道一键安装包访问数据库可以参考这个目录格式（/opt/zbox/bin/mysql -h 127.0.0.1 -u root -P 3306 -p ），登录禅道数据库之后可以使用source 命令导入sql，注意：导入之前需要先清空下18.9版本所有表。
3、将备份中的附件.file目录下1文件放到新禅道 /opt/zbox/app/www/data/upload/ 对应的目录下面。
4、可以再次下载禅道18.9安装包：https://www.zentao.net/dl/zentao/18.9/ZenTaoPMS-18.9-php7.2_7.4.zip
下载之后解压得到zentaopms目录，复制zentaopms/www/upgrade.php到/opt/zbox/app/zentao/www 目录下，增加读写权限。
5、然后访问禅道按照提示进行升级
这个方法到最后升级从16.1 到18.9 跑一半执行的sql就报错了
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_e x e cutionsummary.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_e x e cutionsummary` AS select `zt_task`.`e x e cution` AS `e x e cution`,sum(if((`zt_task`.`parent` >= '0'),`zt_task`.`estimate`,0)) AS `estimate`,sum(if((`zt_task`.`parent` >= '0'),`zt_task`.`consumed`,0)) AS `consumed`,sum(if(((`zt_task`.`status` <> 'cancel') and (`zt_task`.`status` <> 'closed') and (`zt_task`.`parent` >= '0')),`zt_task`.`left`,0)) AS `left`,count(0) AS `number`,sum(if(((`zt_task`.`status` <> 'done') and (`zt_task`.`status` <> 'closed')),1,0)) AS `undone`,sum((if((`zt_task`.`parent` >= '0'),`zt_task`.`consumed`,0) + if(((`zt_task`.`status` <> 'cancel') and (`zt_task`.`status` <> 'closed') and (`zt_task`.`parent` >= '0')),`zt_task`.`left`,0))) AS `totalReal` from `zt_task` where (`zt_task`.`deleted` = '0') group by `zt_task`.`e x e cution`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_projectsummary.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_projectsummary` AS select `zt_task`.`project` AS `project`,sum(if((`zt_task`.`parent` >= '0'),`zt_task`.`estimate`,0)) AS `estimate`,sum(if((`zt_task`.`parent` >= '0'),`zt_task`.`consumed`,0)) AS `consumed`,sum(if(((`zt_task`.`status` <> 'cancel') and (`zt_task`.`status` <> 'closed') and (`zt_task`.`parent` >= '0')),`zt_task`.`left`,0)) AS `left`,count(0) AS `number`,sum(if(((`zt_task`.`status` <> 'done') and (`zt_task`.`status` <> 'closed')),1,0)) AS `undone`,sum((if((`zt_task`.`parent` >= '0'),`zt_task`.`consumed`,0) + if(((`zt_task`.`status` <> 'cancel') and (`zt_task`.`status` <> 'closed') and (`zt_task`.`parent` >= '0')),`zt_task`.`left`,0))) AS `totalReal` from `zt_task` where (`zt_task`.`deleted` = '0') group by `zt_task`.`project`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_projectstories.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_projectstories` AS select `t1`.`project` AS `e x e cution`,count('*') AS `stories`,sum(if((`t2`.`status` = 'closed'),0,1)) AS `undone` from ((`zt_projectstory` `t1` left join `zt_story` `t2` on((`t1`.`story` = `t2`.`id`))) left join `zt_project` `t3` on((`t1`.`project` = `t3`.`id`))) where ((`t2`.`deleted` = '0') and (`t3`.`type` in ('sprint','stage'))) group by `t1`.`project`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_projectteams.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_projectteams` AS select `zt_team`.`root` AS `e x e cution`,count('*') AS `teams` from `zt_team` where (`zt_team`.`type` = 'e x e cution') group by `zt_team`.`root`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_projectbugs.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_projectbugs` AS select `zt_bug`.`e x e cution` AS `e x e cution`,count(0) AS `bugs`,sum(if((`zt_bug`.`resolution` = ''),0,1)) AS `resolutions`,sum(if((`zt_bug`.`severity` <= 2),1,0)) AS `seriousBugs` from `zt_bug` where (`zt_bug`.`deleted` = '0') group by `zt_bug`.`e x e cution`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_productbugs.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_productbugs` AS select `zt_bug`.`product` AS `product`,count(0) AS `bugs`,sum(if((`zt_bug`.`resolution` = ''),0,1)) AS `resolutions`,sum(if((`zt_bug`.`severity` <= 2),1,0)) AS `seriousBugs` from `zt_bug` where (`zt_bug`.`deleted` = '0') group by `zt_bug`.`product`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_productstories.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_productstories` AS select `zt_story`.`product` AS `product`,count('*') AS `stories`,sum(if((`zt_story`.`status` = 'closed'),0,1)) AS `undone` from `zt_story` where (`zt_story`.`deleted` = '0') group by `zt_story`.`product`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_dayuserlogin.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_dayuserlogin` AS select count(*) AS `userlogin`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'user') and (`zt_action`.`action` = 'login')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_dayeffort.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_dayeffort` AS select round(sum(`zt_effort`.`consumed`),1) AS `consumed`,`zt_effort`.`date` AS `date` from `zt_effort` group by `zt_effort`.`date`
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_daystoryopen.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_daystoryopen` AS select count(*) AS `storyopen`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'story') and (`zt_action`.`action` = 'opened')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_daystoryclose.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_daystoryclose` AS select count(*) AS `storyclose`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'story') and (`zt_action`.`action` = 'closed')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_daytaskopen.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_daytaskopen` AS select count(*) AS `taskopen`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'task') and (`zt_action`.`action` = 'opened')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_daytaskfinish.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_daytaskfinish` AS select count(*) AS `taskfinish`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'task') and (`zt_action`.`action` = 'finished')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_daybugopen.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_daybugopen` AS select count(*) AS `bugopen`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'bug') and (`zt_action`.`action` = 'opened')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_daybugresolve.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_daybugresolve` AS select count(*) AS `bugresolve`,left(`zt_action`.`date`,10) AS `day` from `zt_action` where ((`zt_action`.`objectType` = 'bug') and (`zt_action`.`action` = 'resolved')) group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_dayactions.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_dayactions` AS select count(*) AS `actions`,left(`zt_action`.`date`,10) AS `day` from `zt_action` group by left(`zt_action`.`date`,10)
SQLSTATE[HY000]: General error: 35 Warning: Can't copy ownership for file './zentao/ztv_normalproduct.frm-' (Errcode: 1 "Operation not permitted")The sql is:
CREATE OR REPLACE VIEW `ztv_normalproduct` AS SELECT * FROM `zt_product` WHERE `shadow` = 0

⛄

马超 2023/12/07

您好，报错提示没有权限创建ztv_开头的视图，可以尝试到zentao/db/zentao.sql文件中找下这些视图的创建sql复制到禅道数据库手动创建一下，需要使用root账号登录下禅道的数据库。

😎

蜡笔小新 2023/12/07

注意：导入之前需要先清空下18.9版本所有表这个是删除表还是清空表数据

⛄

马超 2023/12/07

是删除表，不好意思是我没有描述清楚。

😎

蜡笔小新 2023/12/07

谢谢