Sorun PHP PDO ile bir veritabanı oluşturma

1 Cevap php

Benim PHP uygulama bir SQL sorgusu ile ilgili bir sorun yaşıyorum. Kullanıcı erişim ilk defa app tüm veritabanı oluşturmak için bu sorguyu yürütür:

CREATE TABLE `databases` (
  `id` bigint(20) NOT NULL auto_increment,
  `driver` varchar(45) NOT NULL,
  `server` text NOT NULL,
  `user` text NOT NULL,
  `password` text NOT NULL,
  `database` varchar(200) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `modules`
--

CREATE TABLE `modules` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `type` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

-- --------------------------------------------------------

--
-- Table structure for table `modules_data`
--

CREATE TABLE `modules_data` (
  `id` bigint(20) NOT NULL auto_increment,
  `module_id` bigint(20) unsigned NOT NULL,
  `key` varchar(150) NOT NULL,
  `value` tinytext,
  PRIMARY KEY  (`id`),
  KEY `fk_modules_data_modules` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=184 ;

-- --------------------------------------------------------

--
-- Table structure for table `modules_position`
--

CREATE TABLE `modules_position` (
  `user_id` bigint(20) unsigned NOT NULL,
  `tab_id` bigint(20) unsigned NOT NULL,
  `module_id` bigint(20) unsigned NOT NULL,
  `column` smallint(1) default NULL,
  `line` smallint(1) default NULL,
  PRIMARY KEY  (`user_id`,`tab_id`,`module_id`),
  KEY `fk_modules_order_users` (`user_id`),
  KEY `fk_modules_order_tabs` (`tab_id`),
  KEY `fk_modules_order_modules` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `tabs`
--

CREATE TABLE `tabs` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `title` varchar(60) NOT NULL,
  `columns` smallint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `tabs_has_modules`
--

CREATE TABLE `tabs_has_modules` (
  `tab_id` bigint(20) unsigned NOT NULL,
  `module_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`tab_id`,`module_id`),
  KEY `fk_tabs_has_modules_tabs` (`tab_id`),
  KEY `fk_tabs_has_modules_modules` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `login` varchar(60) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(100) NOT NULL,
  `name` varchar(250) default NULL,
  `user_level` bigint(20) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_users_user_levels` (`user_level`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `users_has_tabs`
--

CREATE TABLE `users_has_tabs` (
  `user_id` bigint(20) unsigned NOT NULL,
  `tab_id` bigint(20) unsigned NOT NULL,
  `order` smallint(2) NOT NULL,
  `columns_width` varchar(255) default NULL,
  PRIMARY KEY  (`user_id`,`tab_id`),
  KEY `fk_users_has_tabs_users` (`user_id`),
  KEY `fk_users_has_tabs_tabs` (`tab_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user_levels`
--

CREATE TABLE `user_levels` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `level` smallint(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `user_meta`
--

CREATE TABLE `user_meta` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` bigint(20) unsigned default NULL,
  `key` varchar(150) NOT NULL,
  `value` longtext NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_user_meta_users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `modules_data`
--
ALTER TABLE `modules_data`
  ADD CONSTRAINT `fk_modules_data_modules` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

--
-- Constraints for table `modules_position`
--
ALTER TABLE `modules_position`
  ADD CONSTRAINT `fk_modules_order_modules` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_modules_order_tabs` FOREIGN KEY (`tab_id`) REFERENCES `tabs` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_modules_order_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_user_levels` FOREIGN KEY (`user_level`) REFERENCES `user_levels` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `user_meta`
--
ALTER TABLE `user_meta`
  ADD CONSTRAINT `fk_user_meta_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

INSERT INTO `user_levels` VALUES(1, 10);
INSERT INTO `user_levels` VALUES(2, 1);


INSERT INTO `users` VALUES(1, 'admin', 'password', 'changethis@testing.com', NULL, 1);
INSERT INTO `user_meta` VALUES (NULL, 1, 'last_tab', 1);

Bazı ortamlarda ben bu hatayı alıyorum:

SQLSTATE[HY000]: General error: 1005 Can't create table 'dms.databases' (errno: 150)

Ben Google'da bulabildiğim her şeyi denedim ama hiçbir şey çalışır.

Garip bir parçasıdır ben PhpMyAdmin bu sorguyu çalıştırırsanız o herhangi bir hata olmadan, benim veritabanı oluşturur olmasıdır.

1 Cevap

Sorun sorguları kısıtlamalar parçası olması muhtemeldir.

Hata 150 InnoDB Belgeleri'nde söz edilir:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error 150, that means a foreign key definition would be incorrectly formed for the altered table. You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server.

Source: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Ayrıca referansların tanımları (bir imzasız Bigint olan, yani bir imzalanmıştır) aynı değilse oluşabilir ya da onlarla birkaç sütun üzerinde benzersiz dizinleri karıştırma olacak.

etiketler