使用phpmyadmin导出的表无法重新导入。

huangapple go评论68阅读模式
英文:

Table exported with phpmyadmin can't be reimported

问题

我有两台服务器,一台是开发服务器,另一台是生产服务器,它们上面都安装了相同的软件。我想将生产服务器上的数据库导出到开发服务器,所以我使用了phpMyadmin,但是在将sql重新导入到开发服务器时,出现了一个错误:

#1005 - 无法创建表 `omekas`.`asset`(错误号:150“外键约束形式不正确”)(详情…)

这真的让我吃惊,因为我期望这个过程应该是自动的。我做错了什么?

这是抛出错误的创建表:

--
-- 表结构 `asset`
--

DROP TABLE IF EXISTS `asset`;
CREATE TABLE `asset` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `media_type` varchar(255) NOT NULL,
  `storage_id` varchar(190) NOT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `alt_text` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- `asset`表的约束
--
ALTER TABLE `asset`
  ADD CONSTRAINT `FK_2AF5A5C7E3C61F9` FOREIGN KEY (`owner_id`) REFERENCES `user` (`id`) ON DELETE SET NULL;

编辑:

这是“user”表的结构:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `email` varchar(190) NOT NULL,
  `name` varchar(190) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime DEFAULT NULL,
  `password_hash` varchar(60) DEFAULT NULL,
  `role` varchar(190) NOT NULL,
  `is_active` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

编辑2:

我尝试创建表时干脆不加外键约束,将alter table移除了……但是仍然得到了相同的错误?

运行

CREATE TABLE `asset` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `media_type` varchar(255) NOT NULL,
  `storage_id` varchar(190) NOT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `alt_text` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

返回

CREATE TABLE `asset` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `media_type` varchar(255) NOT NULL,
  `storage_id` varchar(190) NOT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `alt_text` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

MySQL错误:文档
#1005 - 无法创建表 `omekas`.`asset`(错误号:150“外键约束形式不正确”)(详情…)
英文:

I have two servers, a dev and a prod kind of, with the same softwares installed on them. I want to export the db from prod to dev, so i used phpMyadmin, but when reimporting the sql on the dev server, an error occurs:

#1005 - Can't create table `omekas`.`asset` (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

This really surprises me, as i expected this process to be sort of automatic. What did i do wrong ?

Here is the Create table throwing the error:

    --
-- Table structure for table `asset`
--

DROP TABLE IF EXISTS `asset`;
CREATE TABLE `asset` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `media_type` varchar(255) NOT NULL,
  `storage_id` varchar(190) NOT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `alt_text` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Constraints for table `asset`
--
ALTER TABLE `asset`
  ADD CONSTRAINT `FK_2AF5A5C7E3C61F9` FOREIGN KEY (`owner_id`) REFERENCES `user` (`id`) ON DELETE SET NULL;

Thanks

Edit:

Here is the "user" table structure:

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `email` varchar(190) NOT NULL,
  `name` varchar(190) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime DEFAULT NULL,
  `password_hash` varchar(60) DEFAULT NULL,
  `role` varchar(190) NOT NULL,
  `is_active` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

edit 2:

I tried to create the table without the foreign key constraint at all, removing the alter table... Which still gave me the same error ?

Running

CREATE TABLE `asset` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `media_type` varchar(255) NOT NULL,
  `storage_id` varchar(190) NOT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `alt_text` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Returns

CREATE TABLE `asset` (
  `id` int(11) NOT NULL,
  `owner_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `media_type` varchar(255) NOT NULL,
  `storage_id` varchar(190) NOT NULL,
  `extension` varchar(255) DEFAULT NULL,
  `alt_text` longtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

MySQL said: Documentation
#1005 - Can't create table `omekas`.`asset` (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

答案1

得分: 0

这个问题已经解决了,但可惜的是,并不是通过一个有用的方法。我删除了整个数据库并重新导入,这次Mariadb没有抱怨。也许这个问题最好删除。

英文:

This has been solved, but sadly not by an useful mean. I dropped the entire db and reimported it, and this time Mariadb didn't complained. Maybe this question is better deleted

huangapple
  • 本文由 发表于 2023年3月9日 19:03:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/75683717.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定