mysqldump problem with case sensitivity? Win->linux

6
votes

When i dump a table with uppercase letters using mysqldump it comes out as lower case in my > dump.sql file. I found a report here in 2006, almost 4 years old http://bugs.mysql.com/bug.php?id=19967

A solution here suggest making linux insensitive. I rather not if possible. Whats the easiest way to copy a win32 db into linux?

2
mysqlwindowslinuxmysqldump

2 Answers

7
votes

According to the MySQL manuals, you only have a limited number of options:

  • Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

  • Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

    Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

See: http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html for full details.

4
votes

Today I've had to make it so. I already have windows db in lower case and need to import to linux db with case sensitive table names, so the play with lowecase_table_names option in not an option :)

It looks that 'show tables' displays appropriately sorted table names and the dump have escaped table names with ` character. I've succesfully imported the database with following algorithm:

  1. I have mydb.sql with lowercase windows dump
  2. I started application to create database schema in Linux, with case sensitive names.

Then I've had lower case names in dump, and case sensitive names in mysql database. I converted the dump using sed & awk with following script:

#!/bin/bash

MYSQL="mysql -u root -p mydb"
FILE=mydb.sql

TMP1=`mktemp`
TMP2=`mktemp`

cp $FILE $TMP1

for TABLE in `echo "show tables" | $MYSQL`; do
  LCTABLE=`echo $TABLE| awk '{print tolower($0)}'`
  echo "$LCTABLE --> $TABLE"
  cat $TMP1 | sed "s/\`$LCTABLE\`/\`$TABLE\`/" > $TMP2
  cp $TMP2 $TMP1
done

cp $TMP1 $FILE.conv

rm $TMP1
rm $TMP2

And the dump has been converted properly. Everything works after import in Linux.