Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

SS table names and case sensitivity


Reply

12 Posts   5974 Views

Avatar
Willr

8 February 2011 at 8:36pm Forum Moderator, 5511 Posts

Aleks - you shouldn't need to start from scratch. You can find several tools online to help with migrate between the 2 formats. I'll dig up the script that we used back in the day when half the developers had MS, half Unix.

Avatar
aleks

9 February 2011 at 6:49am Community Member, 7 Posts

Willr, thanks for the help but I pretty much sorted it out now.

Just thought it would be good to mention it again and maybe do something that will prevent other 'newcomers' running into this trap.

Avatar
KlamathPort

3 August 2011 at 9:01pm (Last edited: 3 August 2011 9:01pm), Community Member, 2 Posts

Hi Willr, This issue still exists. Anybody creating the database on a Windows box and deploying to Linux would suffer this problem...
Did you have that script handy?

Here's one I threw together using nekranox's list:

<?php
/**
* If you installed SilverStripe under windows MySQL with the ignore_case feature enabled (as it is by default) then you probably need this.
* It will help you rename all the tables in an SQL Dump back into case sensitivity
*
* Directions:
* set $file to the sqldump.
* Include this class from your _config.php
* If it says "The follow tables aren't accounted for" and lists some tables, add the correct case version of them to the list below, and try again.
*
* If it succeeds, it will print the new SQLDump for you to reimport.
*
* More info:
* http://silverstripe.org/general-questions/show/11285
* http://cosminswiki.com/index.php/MySQL_case_insensitive_table_and_column_names
*
* @author Dean Rather <dean@deanrather.com>
* @version 1.1
* @date 2011-08-03
*/

$file = file_get_contents('../mysqldump.sql');
$defaultTables = "ArchiveWidget
BlogEntry
BlogEntry_Live
Blogentry_Lersions
BlogHolder
BlogHolder_Live
BlogHolder_Versions
BlogTree
BlogTree_Live
BlogTree_Versions
ContactPage
ContactPage_Live
ContactPage_Versions
Email_BounceRecord
ErrorPage
ErrorPage_Live
ErrorPage_Versions
File
Gallery
Group
Group_Members
Group_Roles
HomePage
HomePage_Live
HomePage_Versions
LoginAttempt
Member
MemberPassword
MageComment
Panel
Permission
PermissionRole
PermissionRoleCode
Products
ProductsPage
ProductsPage_Live
ProductsPage_Versions
Products_Live
Products_versions
QueuedEmail
RedirectorPage
RedirectorPage_Live
RedirectorPage_Versions
RSSWidget
SiteConfig
SiteConfig_CreateTopLevelGroups
SiteConfig_EditorGroups
SiteConfig_ViewerGroups
SiteTree
SiteTree_EditorGroups
SiteTree_ImageTracking
SiteTree_LinkTracking
SiteTree_Live
SiteTree_versions
SiteTree_ViewerGroups
TagCloudWidget
TrackBackPing
VirtualPage
VirtualPage_Live
VirtualPage_versions
Widget
WidgetArea
PageComment";

$fixed = explode("\r\n", $defaultTables);
$lower = explode("\r\n", strtolower($defaultTables));

// What tables do I actually have
$database = $databaseConfig['database'];
$sql = "SHOW TABLES FROM `$database`";
DB::connect($databaseConfig);
$result = DB::query($sql);
$actual = array();
foreach($result as $row) {
foreach($row as $table) {
$actual[] = $table;
}
}

// Which tables are not catered for in the default list
$missing = '';
foreach($actual as $actualTable) {
if(array_search($actualTable, $lower)===false) $missing .= "<br />$actualTable";
}
if($missing) {
exit("<b>The follow tables aren't accounted for:</b> $missing");
}

foreach($fixed as $word) {
$file = str_replace(strtolower("`$word`"), "`$word`", $file);
}

exit("<pre>$file</pre>");

[url]http://dl.dropbox.com/u/1574931/fixTables.php[/url]

Avatar
onion

22 February 2012 at 8:37am Community Member, 10 Posts

I also had this problem today and caused a massive issue. I have been adding content to my development site in Windows (using Xampp) but now when adding it to the server it doesn't work because of mismatched cases. It's caused a horrible headache in the final hour that I didn't need!

Apparently this could all have been avoided by changing this option in xampp:
lower_case_table_names=0

But I didn't know until I'd added all my pages and content. Annoying!

Anyway rant over. I managed to solve this by renaming all the tables to put the uppercase letters back in :-S