Creating tables in MySQL

Question

I created a table license_serial in my "database_name". But when I tried creating a second table named license, it says that it already exists.
Why is that ? My database contains only a license_serial.frm and a db.opt.

mysql> SHOW TABLES;
+---------------------+
| Tables_in_mobilemp3 |
+---------------------+
| license_serial      |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from license;
ERROR 1146 (42S02): Table 'mobilemp3.license' doesn't exist

Creating the second table:

CREATE TABLE `license` (
`id` int(10) unsigned NOT NULL auto_increment,
`serial_id` int(10) unsigned NOT NULL,
`uid` bigint(20) unsigned NOT NULL,
`active` tinyint(1) NOT NULL,
`first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP,
`last_seen` timestamp NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `serial_uid` (`serial_id`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

gives the following error message:

ERROR 1050 (42S01): Table 'mobilemp3.license' already exists

EDIT:

And the solution is this(in this order):

drop database databasename;
create database databasename;
use databasename;

I read this post http://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not but I don't get the solution, what files shoudl i delete ? — Adrian, Jun 23 '11 at 07:19
What is the exact error message you get when running the CREATE TABLE? — , Jun 23 '11 at 07:22
@a_horse_with_no_name: ERROR 1050 (42S01): Table '`mobilemp3`.`license`' already exists — Adrian, Jun 23 '11 at 07:23
There must be something you are not telling us. If the posted result from `show tables` is correct, then that error should not happen. Are you sure you are connected to the same server and database when running the CREATE TABLE — , Jun 23 '11 at 07:26
@a_horse_with_no_name: check the link i posted in my first answer , you should see the same thing happend to that guy to, and nobody belive him:) but i did what that post said, and still the same — Adrian, Jun 23 '11 at 07:30
I can reproduce a similar issue - I'd suggest you check the various system log files - it's highly likely that MySQL has hit some sort of problem --- e.g. I see MySQL leading effectively a oops message with hopefully helpful things for logging a bug on bugs.mysql.com. — David Goodwin, May 10 '12 at 18:06

score 1 · Accepted Answer · answered Jun 23 '11 at 07:36

1

The only thing I can think of, is that the table was created e.g. by root and the user you are using does not have access to that table. In that case you cannot select from it (not sure if it would be filtered out in the show tables command though)

Log in as root to that database and check if that table exists.

answered Jun 23 '11 at 07:36

if I try to create it using MySQL Querry Browser, it says: Table 'mobilemp3.license' doesn't exist very strange – Adrian Jun 23 '11 at 07:50
@vBx: What does `SELECT * FROM information_schema.tables` give you when run as roo? – Jun 23 '11 at 07:52
A select cannot return a "file". Just check if the table license appears in the result. – Jun 23 '11 at 08:01
@vBx: deleting everything and starting from a clean database is not really a solution. Would have been interesing to find out the *real* cause of this problem. – Jun 23 '11 at 08:33
I just wanted the database created, so for me it is a solution :) – Adrian Jun 23 '11 at 08:40

Creating tables in MySQL

1 Answers1