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


Go to End
Reply


12 Posts   6096 Views

Avatar
zav

Community Member, 6 Posts

24 February 2010 at 12:01am

I'm having issues on my live server with case sensitive table names.
When I did the install, all the tables were created with lower case table names, but all the SS queries use camel case table names

eg.
SELECT `SiteTree`.*, `GhostPage`.*, `Calendar`.*, `CalendarEvent`.*, `Reader`.*, `ErrorPage`.*, `RedirectorPage`.*, `VirtualPage`.*, `UserDefinedForm`.*, `SiteTree`.ID, if(`SiteTree`.ClassName,`SiteTree`.ClassName,'SiteTree') AS RecordClassName FROM `SiteTree` LEFT JOIN `GhostPage` ON `GhostPage`.ID = `SiteTree`.ID LEFT JOIN `Calendar` ON `Calendar`.ID = `SiteTree`.ID LEFT JOIN `CalendarEvent` ON `CalendarEvent`.ID = `SiteTree`.ID LEFT JOIN `Reader` ON `Reader`.ID = `SiteTree`.ID LEFT JOIN `ErrorPage` ON `ErrorPage`.ID = `SiteTree`.ID LEFT JOIN `RedirectorPage` ON `RedirectorPage`.ID = `SiteTree`.ID LEFT JOIN `VirtualPage` ON `VirtualPage`.ID = `SiteTree`.ID LEFT JOIN `UserDefinedForm` ON `UserDefinedForm`.ID = `SiteTree`.ID WHERE (HomepageForDomain REGEXP '(, ^) *guidinglight\.co\.nz *(,|$)') ORDER BY Sort LIMIT 1 | Table 'guidingl_ss.SiteTree' doesn't exist

So on my live server the site isn't working as it can't find the table SiteTree, even though the table sitetree is there.
I did some research and found that there is a mysql setting for lower case table names, but the problem is I am on a shared server, so I'm not sure how to change this.

Any suggestions on what I can do to fix this?

Avatar
BigChris

Community Member, 63 Posts

24 February 2010 at 12:54am

Hi Zav,

I had the same problem last month. I found this link helped me find out why - http://www.silverstripe.org/archive/show/38593

However it did not solve my problem.

What I did was probably not an ideal solution, but I got it to work. What I did was back up the mysql database, then took an sql dump of the database.

With the sql dump I opened the sql file into a text editor and changed the names of the tables and column names to be the proper case.

so sitetree becomes SiteTree and so forth. You can pretty much do a search and replace all.

Once that is done, (Make sure you have the backup of your DB) delete the existing database tables and then run the edited sql dump. You will probably still get a few errors but you will be able to go throught them one by one and fix it.

Good luck
Chris

Avatar
zav

Community Member, 6 Posts

24 February 2010 at 6:59pm

Hi Chris

Thanks for the links.
Do you or anyone else know a way to make a 'my.cnf' file to work on a per site basis?

Avatar
netnerd85

Community Member, 42 Posts

18 May 2010 at 8:14pm

What a pain in the a$$ this issue is ey? well I manually changed my SQL backup then had some issues due to words used in the content so here is a faster and much better way to rename tables than manually:

1) Backup your database.
2) From the main structure view of phpMyAdmin use the check all button down the bottom, then with selected do "Drop", DO NOT CLICK YES ON THE NEXT SCREEN, it will just give you an easy to copy list of table names you can use. Do this step to get the source FROM and TO, you will need to do a find replace on the "`" symbol and replace with single quote "'"
3) Copy paste the code below, fill in the variables up the top with your data and run the script on your local or remote server, copy and paste the list into phpMyAdmin's SQL window and run. Ta da!

<?php

//database name
$databaseName = '' . '.';
//array of table names to be renamed
$from = array();
//the array of table names to be renamed to
$to = array();

foreach($from as $tableNameFrom)
{
   foreach($to as $tableNameTo)
   {
      if(strtolower($tableNameTo) == strtolower($tableNameFrom))
      {
         echo "RENAME TABLE <br />\n";
         echo $databaseName . $tableNameFrom . ' TO ' . $databaseName . $tableNameFrom . "_B,<br />\n";
         echo $databaseName . $tableNameFrom . '_B TO ' . $databaseName . $tableNameTo . "<br />\n";
         echo ';';
      }
   }
}

?>

I hope this helps you!

Avatar
Mo

Community Member, 510 Posts

19 May 2010 at 10:41am

This topic has cropped up a few times. Generally it happens when you are using Linux on one platform and Windows on another. Windows by default makes all MySQL table names case insensitive.

If you do some searching on the forums, there are a few topics on this. Someone wrote a script that will go through and re-name your tables for you. Not sure where it is, but it should be on here somewhere.

Cheers,

Mo

Avatar
nekranox

Community Member, 31 Posts

30 July 2010 at 8:59pm

we need a setting in SS to prevent this issue!! heres my code fix for those that want it. running it over your SQL dump being sure to add any custom tables that you've created in your project:

$file = file_get_contents('mysqldump.sql');

$fixed[0] = "ArchiveWidget";
$fixed[1] = "BlogEntry";
$fixed[2] = "BlogEntry_Live";
$fixed[3] = "Blogentry_versions";
$fixed[4] = "BlogHolder";
$fixed[5] = "BlogHolder_live";
$fixed[6] = "BlogHolder_versions";
$fixed[7] = "BlogTree";
$fixed[8] = "BlogTree_live";
$fixed[9] = "BlogTree_versions";
$fixed[10] = "ContactPage";
$fixed[11] = "ContactPage_live";
$fixed[12] = "ContactPage_versions";
$fixed[13] = "Email_BounceRecord";
$fixed[14] = "ErrorPage";
$fixed[15] = "ErrorPage_live";
$fixed[16] = "ErrorPage_versions";
$fixed[17] = "File";
$fixed[18] = "Gallery";
$fixed[19] = "Group";
$fixed[20] = "Group_Members";
$fixed[21] = "Group_Roles";
$fixed[22] = "HomePage";
$fixed[23] = "HomePage_Live";
$fixed[24] = "HomePage_versions";
$fixed[25] = "LoginAttempt";
$fixed[26] = "Member";
$fixed[27] = "MemberPassword";
$fixed[28] = "MageComment";
$fixed[29] = "Panel";
$fixed[30] = "Permission";
$fixed[31] = "PermissionRole";
$fixed[32] = "PermissionRoleCode";
$fixed[35] = "Products";
$fixed[36] = "ProductsPage";
$fixed[37] = "ProductsPage_Live";
$fixed[38] = "ProductsPage_versions";
$fixed[39] = "Products_Live";
$fixed[40] = "Products_versions";
$fixed[41] = "QueuedEmail";
$fixed[42] = "RedirectorPage";
$fixed[43] = "RedirectorPage_Live";
$fixed[44] = "RedirectorPage_versions";
$fixed[45] = "RSSWidget";
$fixed[46] = "SiteConfig";
$fixed[47] = "SiteConfig_CreateTopLevelGroups";
$fixed[48] = "SiteConfig_EditorGroups";
$fixed[49] = "SiteConfig_ViewerGroups";
$fixed[50] = "SiteTree";
$fixed[51] = "SiteTree_EditorGroups";
$fixed[52] = "SiteTree_ImageTracking";
$fixed[53] = "SiteTree_LinkTracking";
$fixed[54] = "SiteTree_Live";
$fixed[55] = "SiteTree_versions";
$fixed[56] = "SiteTree_ViewerGroups";
$fixed[57] = "TagCloudWidget";
$fixed[59] = "TrackBackPing";
$fixed[60] = "VirtualPage";
$fixed[61] = "VirtualPage_Live";
$fixed[62] = "VirtualPage_versions";
$fixed[63] = "Widget";
$fixed[64] = "WidgetArea";

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

print $file;

Avatar
qbahamutp

Community Member, 8 Posts

23 December 2010 at 11:16pm

In order to avoid these kinds of problems between Windows and Linux machines, have a look at this article. It describes MySQL's corresponding configuration settings.

http://cosminswiki.com/index.php/MySQL_case_insensitive_table_and_column_names

Avatar
aleks

Community Member, 7 Posts

8 February 2011 at 2:34pm

This is a major pain indeed.

The installer should display some blinking red notice about this on Windows systems. I am basically re-doing a project in progress from scratch now (after changing the MySQL configuration) to prevent the hassle I had when I wanted to "quickly deploy" a preview version on a unix system the other day. Never experienced this kind of problem with any other software before, was not exactly amused. (Still got love for SilverStripe though!)

Go to Top