Problem creating a database with PHP PDO
- by Leandro Alonso
Hello guys,
I'm having a problem with a SQL query in my PHP Application. When the user access it for the first time, the app executes this query to create all the database:
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', '[email protected]', NULL, 1);
INSERT INTO `user_meta` VALUES (NULL, 1, 'last_tab', 1);
In some environments i get this error:
SQLSTATE[HY000]: General error: 1005
Can't create table 'dms.databases'
(errno: 150)
I tried everything that I could find on Google but nothing works.
The strange part is that if I run this query in PhpMyAdmin he creates my database, without any error.
