mysqldump: Got error: 1066: Not unique table/alias: foo when using LOCK TABLES when lower_case_table_names=1



I'm trying to dump a MYSQL DB on a linux server (Centos 5.2 32bit, MySQL 5.0.45)

In /etc/my.cnf the following value is defined:

[mysqld]
lower_case_table_names=1

When I try and do a dump with mysqldump I get the following error:

[root@linuxbox backups]# mysqldump --user=root --password=secret   --result-file=/tmp/out.sql--all-databases
mysqldump: Got error: 1066: Not unique table/alias: 'foo' when using LOCK TABLES

I can't figure out what I need to do to work around this, and we can't set lower_case_table_names to 0 in my.cnf because that will break some things that require case insensative table names.

How can I do a mysqldump in this situation? (or, another way to make a backup of the DB to file would work since that is the goal)
DrStalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flob9Commented:
You can try to add specific settings in [mysqldump] section of the my.cnf.

Try also quote-names in the my.cnf :

[mysqldump]
quote-names
lower_case_table_names=0
0
DrStalkerAuthor Commented:
That results in

mysqldump: unknown variable 'lower_case_table_names=0'

Which suggests to me mysqldump doesn't understand the lower_case_table_names variable.


0
DrStalkerAuthor Commented:
Fixed this - the DB contained the tables "foo" and "FOO" which obviously is a problem when lower_case_table_names=1.  This had gone unnoticed until the dump because it was an unused table.

 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.