TABLE and VIEW same identifier: Please help

soup_or_power@yahoo.com (soup_or_power@yahoo.com) wrote:
: I have some views whose identifier is same as that of table. I am
: unable to import the data with
: the command

: mysql -uuser -ppassword dbname < mydump.sql

: Can anyone suggest a fix without having to rename each view?

: Thanks

: ps: why did the mysql allow view and table to have the same identifier

huh?

According to the MySQL 5.0 Reference Manual :

"...a database cannot contain a base table and a view that have
the same name."

How did you get a view and a table with the same name?

Malcolm Dew-Jones wrote:
> soup_or_power@yahoo.com (soup_or_power@yahoo.com) wrote:
> : I have some views whose identifier is same as that of table. I am
> : unable to import the data with
> : the command
>
> : mysql -uuser -ppassword dbname < mydump.sql
>
> : Can anyone suggest a fix without having to rename each view?
>
> : Thanks
>
> : ps: why did the mysql allow view and table to have the same identifier
>
> huh?
>
> According to the MySQL 5.0 Reference Manual :
>
> "...a database cannot contain a base table and a view that have
> the same name."
>
> How did you get a view and a table with the same name?

Thanks for your reply. I'm not sure if the views already existed or the
views were created by mysqldump. Is there a way to list the views a la
"show tables" command?
Anyway, my objective is to copy the tables from one database to another
on the same host. Is there a way to do this? I am using mysqldump
because it is recommended by some web sites.

Thanks

soup_or_power@yahoo.com wrote:
> I have some views whose identifier is same as that of table. I am
> unable to import the data with
> the command
>
> mysql -uuser -ppassword dbname < mydump.sql
>
> Can anyone suggest a fix without having to rename each view?
>
> Thanks
>
> ps: why did the mysql allow view and table to have the same identifier
>

MySQL does not allow you to have one table and a view with the same name.
There are two ways you may have got into this problem:

1) You have already a table named 'ABC' in your database and you are trying to
load from a dump file a view named 'ABC' as well.

2) You have a table named 'ABC' and a view named 'abc' (note the case difference).
In Unix systems, table and view names are case sensitive, so, if you
load the dump into a Unix server, nothing wrong happens, even though I question
the wiseness of such a name choice.
However, Windows table names are case insensitive. Thus if you are loading to a
Windows server a dump produced in a Unix one you could have a conflict.


Workaround: change the name of one of the offending items.

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.org/