MySql Question about Tables case sensitivity

Problems with the Windows version of XAMPP, questions, comments, and anything related.

MySql Question about Tables case sensitivity

Postby rr1024 » 26. April 2008 04:20

Hello and I must say XAMMP is very nice packege, you guys have done a fine job!

I was wondering if it is possible to force phpMyAdmin and MySQL to be case-sensitive on the windows version.

The problem I'm having is I'm hosting on a linux system and I have really large dbs that I want to backup to my little windows 2000 box.

However, when I import data from several of my tables where it is stated that 2 of the tables form UNIQUE and the data in a table has _aol and further down _AOL which works fine on all the linux servers but when I try to import it via phpmyadmin it chokes and says that there is duplicated data when there shouldn't be.

So how can I force/change/alter phpmyadmin or mysql to use case sensitivity?

Thanks for any help!

:)
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby rr1024 » 26. April 2008 05:57

Well I found out a little more about this problem it has something to do with

XAMPP sets
lower_case_table_names=1

and I think I want it to be
lower_case_table_names=2

I've tried the command line approach

I've also added it to the my
mysql_start.bat
as
mysql\bin\mysqld --defaults-file=mysql\bin\my.cnf --standalone --console
mysql\bin\mysqld --lower_case_table_names=2


and added it to
my.cnf
as
[mysqld]
port= 3306
socket= "/xampp/mysql/mysql.sock"
basedir="/xampp/mysql"
tmpdir="/xampp/tmp"
datadir="/xampp/mysql/data"
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
set-variable=lower_case_table_names=2


So far nothing seems to change the value when I do
bin/mysqld --verbose --help
the value of
lower_case_table_names
is always 1
:?: :?:
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby Nobbie » 26. April 2008 10:34

There is a big difference between names of tables and data in tables. As i understood, you have DATA (not table names) which is case sensitive.

Case-sensitive data is NOT a question of server configuration, but is to be configured in the CREATE TABLE statement. Each column which has to be case-sensitive must be declared as BINARY. Example:

Instead of

Code: Select all
CREATE TABLE blabla
myname VARCHAR(50),
...


you have to declare the columns myname as BINARY:

Code: Select all
CREATE TABLE blabla
myname VARCHAR(50) BINARY,
...


For full syntax definition see: http://dev.mysql.com/doc/refman/5.1/en/ ... table.html
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Postby Wiedmann » 26. April 2008 15:03

mysql\bin\mysqld --defaults-file=mysql\bin\my.cnf --standalone --console
mysql\bin\mysqld --lower_case_table_names=2

This can't work, because with the first line the server is allready started.

Code: Select all
set-variable=lower_case_table_names=2

"set-variable" is deprecated. Just use:
Code: Select all
lower_case_table_names = 2


So far nothing seems to change the value when I do
bin/mysqld --verbose --help

You can verify the new setting with:
Code: Select all
mysqladmin -u root variables | findstr "lower_case_table_names"
Wiedmann
AF Moderator
 
Posts: 17102
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby rr1024 » 27. April 2008 00:56

Well I do have the problem with the tables as well as the data, I guess I kinda mixed up what I was talking about.


Basically I need to make both table names, column names and data in the colomns to be case sensitive.

Otherwise it kinda makes it useless to try to backup a linux based MySQL Db on a windows system if windows wants all the table names to be lower case.

I have set my.cnf to the following and sense the bat file mysql_start.bat loads mysql\bin\mysqld --defaults-file=mysql\bin\my.cnf --standalone --console
Code: Select all
[mysqld]
port= 3306
socket= "/xampp/mysql/mysql.sock"
basedir="/xampp/mysql"
tmpdir="/xampp/tmp"
datadir="/xampp/mysql/data"
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=2


I then did
mysqld --verbose --help
It still states that lower_case_table_names=1 and not 2

I also tried
mysqladmin -u root {mypassword} | findstr "lower_case_table_names"
and
mysqladmin -u root -p {mypassword} | findstr "lower_case_table_names"

this returns
mysqladmin: connect to server at "localhost' failed error: "Access denied for user 'root'@'localhost' <using password: NO>


the second attempt basically does the same thing only it at least comes back with
Password: ******** where I enter my password then it errors out too with unknown command mypassword
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby Wiedmann » 27. April 2008 01:03

Code: Select all
mysqladmin -u root -p {mypassword} | findstr "lower_case_table_names"

mysqladmin: connect to server at "localhost' failed error: "Access denied for user 'root'@'localhost' <using password: YES>

Maybe a good idea to read the documentation for mysqladmin or other command line tools... ;-)
--> No space between "-p" and your password.
Wiedmann
AF Moderator
 
Posts: 17102
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby rr1024 » 27. April 2008 01:26

Nobbie wrote:There is a big difference between names of tables and data in tables. As i understood, you have DATA (not table names) which is case sensitive.

Case-sensitive data is NOT a question of server configuration, but is to be configured in the CREATE TABLE statement. Each column which has to be case-sensitive must be declared as BINARY. Example:

Instead of

Code: Select all
CREATE TABLE blabla
myname VARCHAR(50),
...


you have to declare the columns myname as BINARY:

Code: Select all
CREATE TABLE blabla
myname VARCHAR(50) BINARY,
...


For full syntax definition see: http://dev.mysql.com/doc/refman/5.1/en/ ... table.html



So is there a way to export the extra binary tag for each table or do I have to edit each dump file by hand and add to each create table binary?
If I have to do this then do you know of an open source text editor that can handle sql dumps of about 5Gig?

Is there away in MySQL to tell it to always use Binary when creating a new table?

Sorry if these are stupid questions, I'm kinda new to this, it is fun though :D

Oh I did answer in the post above, I have both problems my tables, column names also use case sensitivity. I figured I would never, ever put a website on a windows box other than maybe on my own system for development
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby Wiedmann » 27. April 2008 01:32

Is there away in MySQL to tell it to always use Binary when creating a new table?

Why want you set the type for your columns to VARBINARY?
Wiedmann
AF Moderator
 
Posts: 17102
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby rr1024 » 27. April 2008 02:23

Wiedmann wrote:
Code: Select all
mysqladmin -u root -p {mypassword} | findstr "lower_case_table_names"

mysqladmin: connect to server at "localhost' failed error: "Access denied for user 'root'@'localhost' <using password: YES>

Maybe a good idea to read the documentation for mysqladmin or other command line tools... ;-)
--> No space between "-p" and your password.


I have tried reading MySQL but the explainations given don't seem to be forthright and seem to conflict a lot between what is posted and the reality.

A perfect example is the command line above
I typed in
mysqladmin -u root -pmypassword | findstr "lower_case_table_names"

and it does nothing and returns
D:\xampp\mysql\bin\
I haven't found the MySQL site to be very useful, except in rare cases lol
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby rr1024 » 27. April 2008 02:29

Wiedmann wrote:
Is there away in MySQL to tell it to always use Binary when creating a new table?

Why want you set the type for your columns to VARBINARY?



I'm not sure Nobbie above said that is what I needed to do to allow the data to be case sensitive....

i.e. the data problem in a column called key which has both _aol and _AOL and the column key is set to unique.... On linux this works just fine but on windows it does not, because the XAMPP configures it to be non-case-sensitive therefore it throws and error that states two items in row Key are the same.
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby Wiedmann » 27. April 2008 02:42

the data problem in a column called key which has both _aol and _AOL and the column key is set to unique....

You can't import/using such a db into a MySQL server running on e.g. Windows.
Wiedmann
AF Moderator
 
Posts: 17102
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby rr1024 » 27. April 2008 03:19

Wiedmann wrote:
the data problem in a column called key which has both _aol and _AOL and the column key is set to unique....

You can't import/using such a db into a MySQL server running on e.g. Windows.


Actually what Nobbie said about adding Binary to the create table actually seem to work

Code: Select all
CREATE TABLE `LocalizationKeys` (
        `ID` smallint(5) unsigned NOT NULL auto_increment,
        `IDCategory` tinyint(3) unsigned DEFAULT '0' NOT NULL,
        `Key` varchar(255) BINARY NOT NULL,
   PRIMARY KEY (`ID`),
   UNIQUE Key (`Key`)
);


It successfully inserted
INSERT INTO `LocalizationKeys` VALUES ('57','26','_AOL');
INSERT INTO `LocalizationKeys` VALUES ('60','26','_aol');

with no errors ;-) Thanks Nobbie


So now I just have to fix the create tables issue
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby rr1024 » 27. April 2008 03:31

Also it appears as though the Table case sensitivity is also fixed....

While I still have not been able to view the actual var for it to see what it is set too I just created a table with MyTest Table and it worked!


I think, or guess it was adding it properly to
my.cnf file
Code: Select all
[mysqld]
port= 3306
socket= "/xampp/mysql/mysql.sock"
basedir="/xampp/mysql"
tmpdir="/xampp/tmp"
datadir="/xampp/mysql/data"
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names = 2


so if anyone else needs to do this it appears as though you edit

xampp\mysql\bin\my.cnf

also note I added spaces between the = and 2 setting??
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Postby Wiedmann » 27. April 2008 11:05

Actually what Nobbie said about adding Binary to the create table actually seem to work

Well, then Nobbie was right and you are really talking about column values.

But just to clarify that:
There is no difference between Linux and Windows about column values (and indexes comparison). You should look, how this column is defined at the Linix server. And your next dump should include "create table" statements.

Code: Select all
mysqladmin -u root -pmypassword | findstr "lower_case_table_names"

and it does nothing

Please read my first post about this command once more... (a parameter for mysqladmin is missing)
Wiedmann
AF Moderator
 
Posts: 17102
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby rr1024 » 29. April 2008 16:40

Ok will do and thanks, I'll give it a try in a few minutes ;-)



However, I found that it the Case Sensitivity wasn't working as well as I though.

It seems if I do an import of a DB which has Case sensitive names the names are all converted to lower case.


However, if I create Case sensitive names from phpmyadmin the case sensitivity is kept.

For instance

Code: Select all

DROP TABLE IF EXISTS `Admins`;
CREATE TABLE `Admins` (
  `ID` int(1) NOT NULL auto_increment,
  `NickName` varchar(10) NOT NULL default '',
  `Password` varchar(40) NOT NULL default '',
  `Email` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `NickName` (`NickName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Admins`
--


/*!40000 ALTER TABLE `Admins` DISABLE KEYS */;
LOCK TABLES `Admins` WRITE;
INSERT INTO `Admins` VALUES (1,'admin','1fa160ccd3776720ed1d50988fb649b3','webmaster@mydomain.com');
UNLOCK TABLES;
/*!40000 ALTER TABLE `Admins` ENABLE KEYS */;



I just imported this via Bigdump and when I look at it via phpmyadmin
it shows up as admin and not Admin
rr1024
 
Posts: 35
Joined: 11. March 2008 04:28

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 125 guests