4

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.

Improve this question
6
  • probably the ` Try to do a select statement on that table. Commented Jan 15, 2014 at 11:23
  • 3
    I think you just have your syntax slightly out. see here `DROP TABLE IF EXISTS 'account'.'info' the dbname and table name are in separate quotes. Commented Jan 15, 2014 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... Commented Jan 15, 2014 at 11:28
  • @LOLSinger: It seems you need a account_info table in your database but not an info table in account database. Commented Jan 15, 2014 at 11:37
  • @LOLSinger: Replace account.info with account_info and it should be working. Commented Jan 15, 2014 at 11:39

5 Answers 5

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@eggyal Older versions of MySQL don't permit this. See dev.mysql.com/doc/refman/5.0/en/identifiers.html
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`.

Improve this answer

2 Comments

I dont have database name as account,, i want to create "account.info" table separately itself
@LOLSinger see this stackoverflow.com/questions/776123/mysql-dot-in-database-name
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`;
Improve this answer

5 Comments

"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?
account and info are none of these! but then we should do ` db ` . ` tbl ` :)
@eggyal you should never use reserved words for naming database objects ;)
i just said "should" :D
if the names is in another language, should someone use ` . ` ?
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;
Improve this answer

3 Comments

here it gives me error on CREATE TABLE account``info that >>> ERROR 1049 (42000) at line 60: Unknown database 'account'
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.
He can of course do, it's just the easiest way of avoiding problems.
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.

Improve this answer

3 Comments

here it gives me error on CREATE TABLE account``info that >>> ERROR 1049 (42000) at line 60: Unknown database 'account'
@LOLSinger Singer what is the table name and the database name for the table?
@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.