Jump to:

23479 Posts in 18951 Topics by 2878 members

General Questions

SilverStripe Forums » General Questions » SS table names and case sensitivity

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

Page: 1 2
Go to End
Author Topic: 5916 Views
  • zav
    Avatar
    Community Member
    6 Posts

    SS table names and case sensitivity Link to this post

    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?

  • BigChris
    Avatar
    Community Member
    63 Posts

    Re: SS table names and case sensitivity Link to this post

    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

  • zav
    Avatar
    Community Member
    6 Posts

    Re: SS table names and case sensitivity Link to this post

    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?

  • netnerd85
    Avatar
    Community Member
    42 Posts

    Re: SS table names and case sensitivity Link to this post

    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!

  • Mo
    Avatar
    Community Member
    508 Posts

    Re: SS table names and case sensitivity Link to this post

    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

  • nekranox
    Avatar
    Community Member
    31 Posts

    Re: SS table names and case sensitivity Link to this post

    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;

  • qbahamutp
    Avatar
    Community Member
    8 Posts

    Re: SS table names and case sensitivity Link to this post

    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

  • aleks
    Avatar
    Community Member
    7 Posts

    Re: SS table names and case sensitivity Link to this post

    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!)

    5916 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.