Drop Table if exists statement giving error

Question

I have following block of code in MySql:

DROP TABLE IF EXISTS `account.info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account.info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`account_id`,`year_id`,`school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7177 DEFAULT CHARSET=utf8;

Its giving me error on first line as:

ERROR 1103 (42000) at line 56: Incorrect table name 'account.info'

What is wrong in it?

Please help me.

I think you just have your syntax slightly out. see [here](http://stackoverflow.com/questions/6046474/can-you-drop-table-if-exists-by-specifying-database-name-with-table) `DROP TABLE IF EXISTS 'account'.'info' the dbname and table name are in separate quotes. — Squirrel5853, Jan 15 '14 at 11:24
why do you bother with if the table exists or not. just run the `DROP` statement catch the error. Then create. either way you get your result... — Squirrel5853, Jan 15 '14 at 11:28
@LOLSinger: It seems you need a `account_info` table in your database but not an `info` table in `account` database. — Ravinder Reddy, Jan 15 '14 at 11:37
@LOLSinger: Replace `account.info` with `account_info` and it should be working. — Ravinder Reddy, Jan 15 '14 at 11:39
Same error for PDO $this->query("DROP TABLE IF EXISTS " . $this->quote($table)); — ymakux, Jul 16 '17 at 17:08

mrjink · Answer 1 · 2014-01-15T12:01:11.230

From http://dev.mysql.com/doc/refman/5.1/en/identifiers.html: "Before MySQL 5.1.6, database and table names cannot contain “/”, “\”, “.”, or characters that are not permitted in file names."

Which is why I gave this answer:

You can't use dots in table names. Dots are used to separate database names and table names (and column names). You could try using `account`.`info` if your database name is account and the table name is info. If the table name is supposed to be account.info, you should change that to something else, like account_info. I don't agree with some of the other answers: Quoting never hurts, if done properly.

Since 5.1.6 you can use whatever you please, as shown by @eggyal and others.

@eggyal Older versions of MySQL don't permit this. See http://dev.mysql.com/doc/refman/5.0/en/identifiers.html — Jim, Jan 15 '14 at 11:54

eggyal · Answer 2 · 2014-01-15T11:59:33.663

3

As documented under Schema Object Names:

Before MySQL 5.1.6, database and table names cannot contain “/”, “\”, “.”, or characters that are not permitted in file names.

Incidentally, had you wanted to create a table called info within a database called account, then note that as documented under Identifier Qualifiers:

If any components of a multiple-part name require quoting, quote them individually rather than quoting the name as a whole. For example, write `my-table`.`my-column`, not `my-table.my-column`.

edited Jan 15 '14 at 11:59

answered Jan 15 '14 at 11:27

eggyal

122,705
18
212
237

1

I dont have database name as account,, i want to create "account.info" table separately itself – C Sharper Jan 15 '14 at 11:54
@LOLSinger see this http://stackoverflow.com/questions/776123/mysql-dot-in-database-name – Positivity Jan 15 '14 at 11:57

Positivity · Answer 3 · 2014-01-15T11:35:51.850

2

try this:

DROP TABLE IF EXISTS account.info;

dont use ` when using dots.

Or quote both db name and table name

DROP TABLE IF EXISTS `account`.`info`;

edited Jan 15 '14 at 11:35

answered Jan 15 '14 at 11:26

Positivity

5,406
6
41
61

1

"*don't use `\`` when using dots*" - What if the database name is a reserved word, or if either of database/table names contain a character that requires quoting? – eggyal Jan 15 '14 at 11:28
`account` and `info` are none of these! but then we should do ` db ` . ` tbl ` :) – Positivity Jan 15 '14 at 11:31
@eggyal you should never use reserved words for naming database objects ;) – Ben Jan 15 '14 at 11:36
i just said "should" :D – Ben Jan 15 '14 at 11:36
if the names is in another language, should someone use ` `.` ` ? – Yannis Dran May 31 '17 at 18:25

score 1 · Answer 4 · answered Jan 15 '14 at 11:26

1

You seem to want to create and first drop a table in the database account with the name info. If so, do it like this:

DROP TABLE IF EXISTS `account`.`info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `account`.`info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL,
  `school_id` int(11) NOT NULL,
  PRIMARY KEY (`id`,`account_id`,`year_id`,`school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7177 DEFAULT CHARSET=utf8;

answered Jan 15 '14 at 11:26

fancyPants

50,732
33
89
96

here it gives me error on CREATE TABLE `account``info` that >>> ERROR 1049 (42000) at line 60: Unknown database 'account' – C Sharper Jan 15 '14 at 11:34
Then you don't have a database named account. You want to create a table named only "account.info"? Don't use a dot in the name. – fancyPants Jan 15 '14 at 11:39
He can of course do, it's just the **easiest** way of avoiding problems. – fancyPants Jan 15 '14 at 11:59

Jim · Answer 5 · 2014-01-15T11:55:26.513

1

You're specifying a table called account.info and not a table called info in the account db. Quote each part separately:

DROP TABLE IF EXISTS `account`.`info`;

If you are trying to make a table called account.info then older versions of MySQL wont allow a . in a table name.

edited Jan 15 '14 at 11:55

answered Jan 15 '14 at 11:27

Jim

22,354
6
52
80

here it gives me error on CREATE TABLE account``info that >>> ERROR 1049 (42000) at line 60: Unknown database 'account' – C Sharper Jan 15 '14 at 11:35
@LOLSinger Singer what is the table name and the database name for the table? – Jim Jan 15 '14 at 11:50
@LOLSinger See my update. You can't create a table with a `.` in it in some versions of MySQL. Consider changing to, say, `account_info`. – Jim Jan 15 '14 at 11:56

Drop Table if exists statement giving error

5 Answers5