Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As in the question title - is it possible for a MySQL database to have duplicate tables with the same name? Is there a directive that can be used for a database to allow this?

Normally, when trying to add duplicate tables, MySQL should return an error #1050 - Table 'tablename' already exists, but recently, while working on a project, I noticed that almost every table in the database is duplicated (only the structure, new tables are empty). While I know that some table-creating scripts could be executed, MySQL shouldn't allow this, right?

How to even deal with such an issue? The program causing this is a Magento - a PHP-based eCommerce platform.

enter image description here

There are parts of a dump from this database:

--
-- Table structure for table `adminnotification_inbox`
--

CREATE TABLE IF NOT EXISTS `adminnotification_inbox` (
  `notification_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Notification id',
  `severity` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Problem type',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Create date',
  `title` varchar(255) NOT NULL COMMENT 'Title',
  `description` text COMMENT 'Description',
  `url` varchar(255) DEFAULT NULL COMMENT 'Url',
  `is_read` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag if notification read',
  `is_remove` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag if notification might be removed',
  PRIMARY KEY (`notification_id`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_SEVERITY` (`severity`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_IS_READ` (`is_read`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_IS_REMOVE` (`is_remove`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Adminnotification Inbox' AUTO_INCREMENT=26 ;

--
-- Dumping data for table `adminnotification_inbox`
--
-- there was some data from this table, not relevant here

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

--
-- Table structure for table `
                adminnotification_inbox`
--
-- in use(#1146 - Table 'dev_testproject_1_12.
                adminnotification_inbox' doesn't exist)
-- in use (#1146 - Table 'dev_testproject_1_12.
                adminnotification_inbox' doesn't exist)

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

I don't think this dump would even run without errors.

share|improve this question
    
Looks like a serious bug to me. If you make a dump of this DB, do you still have duplicated tables in it? –  dezso Nov 9 '12 at 11:01
    
The dump seems to be ok. –  Bartosz Górski Nov 9 '12 at 11:41
    
(copied my comment from an answer here, so it's easier to find if there are any more answers) My mistake - I didn't understand the dump correctly. There were some additional things there that I initially classified as part of the comments for the 'real' (not-duplicated) tables, because they were all commented. Those were in fact declarations of the same tables with whitespaces, which were NOT dumped, because the mysqldump couldn't handle them. This answer seems to be correct about whitespaces. I will update my question with parts of my dump from phpmyadmin. –  Bartosz Górski Nov 9 '12 at 12:53

1 Answer 1

up vote 8 down vote accepted

Make sure that the table name has no whitespaces that are not displayed in HTML.

share|improve this answer
    
If the duplicates are not found in the dump (as Bartosz writes in his comment), this cannot be the problem. –  dezso Nov 9 '12 at 12:34
    
My mistake - I didn't understand the dump correctly. There were some additional things there that I initially classified as part of the comments for the 'real' (not-duplicated) tables, because they were all commented. Those were in fact declarations of the same tables with whitespaces, which were NOT dumped, because the mysqldump couldn't handle them. This answer seems to be correct about whitespaces. I will update my question with parts of my dump from phpmyadmin. –  Bartosz Górski Nov 9 '12 at 12:44
    
@BartoszGórski +1 then for hancz :) –  dezso Nov 9 '12 at 12:59
    
Now, when I know what the issue is, I can manually drop all those duplicated tables. Marking this answer as accepted - thank you both, guys, for helping me. –  Bartosz Górski Nov 9 '12 at 13:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.