Capitalized table names

[rastkocvetkovic]

I'm running MySQL 3.23.49 on my Windows XP machine. Now I'm having troubles with tables names which start with capital letters.
For instance, the following syntax...

CREATE TABLE Test (
Test_field VARCHAR( 30 ) NOT NULL
) COMMENT = 'Test table';

... creates a table named test with Test_field field.

What could be the problem? On my Linux server everything works without any problems. Any suggestions, thanks for help.

Rastko Cvetkovic

 

[piti]

it's probably a win filesystem prob or the prob of win build of mysql - every table is represented by three files named <tablename>.ext and if those start with a capital letter (Test.ext) the table's name is then Test, otherwise ...
i have not seen a win machine using mysql with tables starting with a capital letter - but i have not seen so much of such systems though ;-)

 

[sleipnir214]

What, exactly, is the problem? ______________________________________________________________________
TANSTAAFL!

 

[rastkocvetkovic]

Well, the problem persists.
I've found the &quot;three files&quot; that describe one table, and after I renamed them from WinXP, they work in mysql environment as a capitalized tables. But, how could I solve the problem on a more easy way.

Temporary solution is this .bat file, created with PHP:

<?
include(&quot;../_library/db_connect.php&quot; //connection to database
$table_name = &quot;pizzerije&quot;; //name of the table you want to retrieve information from
$result = mysql_list_tables($table_name); //function to retrieve all the table names
while ($row = mysql_fetch_row($result)) { //while to scroll over all table names
$table_lower = strtolower($row[0]); //name of the table
$table_capitalized = substr_replace($table_lower, strtoupper($table_lower{0}) ,0,1); //capitalized name of the table
//renaming the files (DOS based)
echo &quot;ren $table_lower.frm $table_capitalized.frm\r\n&quot;;
echo &quot;ren $table_lower.MYD $table_capitalized.MYD\r\n&quot;;
echo &quot;ren $table_lower.MYI $table_capitalized.MYI\r\n&quot;;
}
mysql_free_result($result); //freeing the result
include(&quot;../_library/db_disconnect.php&quot; //disconnecting from the database
?>

I hope there's a better solution, that works from mysql. Thanks in advance!

P.S.: sleipnir214, could I make it more obvious?

 

[sleipnir214]

rastkocvetkovic,

Yes, you still can make it more obvious. What are the symptoms of the problem? ______________________________________________________________________
TANSTAAFL!

 

[rastkocvetkovic]

sleipnir214,
the symptoms of the problem are a bunch of scripts that don't work because of the fact that the table name doesn't start with a capital letter, but with a lower one.
Do you think they ought to work?
It's a huge problem due to incompatibility because I have LINUX server and cooperants, which are mobile using notebooks with Windows platform running almost identical application.
Thanks for answer!

 

[sleipnir214]

rastkocvetkovic,

You're still not getting me.

Pretend for a minute that not all of us are stressed out because of an unexpected weird problem in a project.

Pretend for a minute that not all of us have your experience with MySQL on Win32.

Pretend that I have never seen a single error message from your application.

Pretend for a minute that I am genuinely curious about how the problem came to your attention, and how you diagnosed it.

Pretend for a minute that Tek-Tips exists to share experience, and that I am asking you to share yours.

Now, what were the symptoms of the problem? Not the cause, the symptoms. What error messages did you get? ______________________________________________________________________
TANSTAAFL!

 

[rastkocvetkovic]

OK, I didn't get the point, I admit. I was talking about the consequences, not the cause for this problem.
So here it goes.
I've installed my mySQL normally on my notebook, without any problems. The example-scripts also worked without any problems.

Now, I'm working on a new project and it was mostly developed on Linux machine - with mySQL 3.23.45. I've named all the tables in this database capitalized, to distinct them from columns (I hope this is the right approach). I've ran PHPmyAdmin on this Linux Machine, chosen this database and chosen &quot;View dump (schema) of database&quot; with &quot;Structure only&quot;. The file with dump of databse - my_database_dump.sql - had the names of a tables capitalized, just like it was supposed to, for instance:

CREATE TABLE Test_table (
Test_field VARCHAR( 30 ) NOT NULL
)

Then I ran PHPmyAdmin on one of the notebooks which are running on Windows XP operating systems with mySQL version 3.23.49-nt-log, which is stable release for Windows environment. I 'vecreated the database with the same name as it exists on the server, and chosen Run SQL / Location of the textfile and chosen previosly mentioned my_database_dump.sql. After running it, the table &quot;Test_table&quot; on the notebook's mySQL wasn't &quot;Test_table&quot;, but lowercased &quot;test_table&quot;.
Because the SQL statement is correct, I assume it has something to do with Windows file system, that doesn't permit mySQL to create files capitalized, as user piti already mentioned.
The working, but simple solution was to rename all the files which represent a table, such as:
path_to_mysql\data\database_name\table_name > Table_name

Therefore I made the script above to rename the files. After renaming them, I found out that mySQL recognizes the tables now as capitalized, therefore, as it should.

I hope I've been descriptive enough. If any user has the same problem, I hope we'll solve it here, or if you're already solved it, share the solution with us.

Thank you in advance, and thank you sleipnir214.

 

[piti]

hi

i searched the mysql documentation and this came out

&quot;In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case-sensitivity of the underlying operating system determines the case-sensitivity of database and table names. This means database and table names are case-insensitive in Windows, and case-sensitive in most varieties of Unix (Mac OS X being an exception).&quot;

 

[rastkocvetkovic]

That solves the puzzle.
We now know the cause, but why has this been implemented like that on Windows based systems - that we don't know - and it brings nothing but troubles.
Thank you for you efforts - we'll go back to DOS-kind of renaming tables.