Migrating a Concrete5 Site from Windows to Linux, and MySQL Case Sensitivity Hell

21^st October 2011

If, like me, you often develop on a Windows platform and then host using a *Nix-based server, Concrete5 has a gotcha which will probably hit you when you come to publish your site. At some point no doubt you'll reach the point where you've copied the codebase to your server, created a database dump and imported it into the MySQL instance on your *Nix-based server. Try browsing to your site at this point, though, and you'll get a message a lot like this:

Fatal error: Uncaught exception 'ADODB_Exception' with message 'mysql error: [1146: Table 'mysite.Config' doesn't exist] in EXECUTE("select * from Config where uID = 0 order by cfKey asc") ' in /var/www/mysite/htdocs/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php:78 Stack trace: #0 /var/www/mysite/htdocs/concrete/libraries/3rdparty/adodb/adodb.inc.php(1037): adodb_throw('mysql', 'EXECUTE', 1146, 'Table 'mysite...', 'select * from C...', false, Object(ADODB_mysql)) #1 /var/www/mysite/htdocs/concrete/libraries/3rdparty/adodb/adodb.inc.php(1012): ADOConnection->_Execute('select * from C...', false) #2 /var/www/mysite/htdocs/concrete/libraries/database.php(75): ADOConnection->Execute('select * from C...') #3 /var/www/mysite/htdocs/concrete/models/config.php(151): Database->__call('Execute', Array) #4 /var/www/mysite/htdocs/concrete/models/config.php(151): Database->Execute('select * from C...') #5 /var/www/mysite in /var/www/mysite/htdocs/concrete/libraries/3rdparty/adodb/adodb-exceptions.inc.php on line 78

The reason for this is simple - although it might not be apparent. Concrete5 uses CamelCase table names such as BlockTypes, CollectionAttributeValues and FileSearchIndexAttributes. As soon as you install Concrete5 on Windows (under a default configuration) these table names are converted to lowercase - so in those specific examples, you'll get table names such as blocktypes, collectionattributevalues and filesearchindexattributes. On Windows, MySQL table names are not case sensitive, but it's set up such that rather than ignore capitalisation, it does away with it altogether. Linux and Unix on the other hand, are case sensitive and so in this instance, the table Config can't be found because you'll have unwittingly imported the table config - and you have Windows to thank. There is an option in MySQL which allows you to turn off this feature of converting table names to lowercase. In your my.ini, adding or amending the line: set-variable=lower_case_table_names=0 However, the MySQL documentation reccomends that this value be set to 1 if you're using InnoDB. Also bear in mind that changing this value will only affect newly created tables. You would of course find that this method is useless if you find yourself in the situation above, as the conversion has already been done. Your best option, therefore, is to convert the table names on your *Nix database sever back to CamelCase. To do so you can run the following SQL script (it's based on Concrete5 5.4.2.2, and do bear in mind that any tables created by third-party add-ons won't be included):

RENAME TABLE areagroupblocktypes TO AreaGroupBlockTypes; 
RENAME TABLE areagroups TO AreaGroups; 
RENAME TABLE areas TO Areas; 
RENAME TABLE atdefault TO atDefault;
RENAME TABLE attributesetkeys TO AttributeSetKeys; 
RENAME TABLE attributesets TO AttributeSets; 
RENAME TABLE attributekeys TO AttributeKeys; 
RENAME TABLE attributekeycategories TO AttributeKeyCategories; 
RENAME TABLE attributetypecategories TO AttributeTypeCategories; 
RENAME TABLE attributetypes TO AttributeTypes; 
RENAME TABLE attributevalues TO AttributeValues; 
RENAME TABLE blockrelations TO BlockRelations; 
RENAME TABLE blocktypes TO BlockTypes; 
RENAME TABLE blocks TO Blocks; 
RENAME TABLE collectionattributevalues TO CollectionAttributeValues; 
RENAME TABLE collectionversionblockpermissions TO CollectionVersionBlockPermissions; 
RENAME TABLE collectionversionblocks TO CollectionVersionBlocks; 
RENAME TABLE collectionversionblockstyles TO CollectionVersionBlockStyles; 
RENAME TABLE collectionversionarealayouts TO CollectionVersionAreaLayouts; 
RENAME TABLE collectionversionareastyles TO CollectionVersionAreaStyles; 
RENAME TABLE collectionversions TO CollectionVersions; 
RENAME TABLE collections TO Collections; 
RENAME TABLE composercontentlayout TO ComposerContentLayout;
RENAME TABLE composerdrafts TO ComposerDrafts;
RENAME TABLE composertypes TO ComposerTypes;
RENAME TABLE config TO Config; 
RENAME TABLE dashboardhomepage TO DashboardHomepage; 
RENAME TABLE downloadstatistics TO DownloadStatistics; 
RENAME TABLE fileattributevalues TO FileAttributeValues; 
RENAME TABLE filepermissionfiletypes TO FilePermissionFileTypes; 
RENAME TABLE customstylepresets TO CustomStylePresets; 
RENAME TABLE customstylerules TO CustomStyleRules; 
RENAME TABLE filepermissions TO FilePermissions; 
RENAME TABLE taskpermissionusergroups TO TaskPermissionUserGroups; 
RENAME TABLE taskpermissions TO TaskPermissions; 
RENAME TABLE filesetpermissions TO FileSetPermissions; 
RENAME TABLE fileversions TO FileVersions; 
RENAME TABLE fileversionlog TO FileVersionLog; 
RENAME TABLE filestoragelocations TO FileStorageLocations; 
RENAME TABLE files TO Files; 
RENAME TABLE groups TO Groups; 
RENAME TABLE jobs TO Jobs; 
RENAME TABLE jobslog TO JobsLog; 
RENAME TABLE layouts TO Layouts; 
RENAME TABLE layoutpresets TO LayoutPresets; 
RENAME TABLE systemnotifications TO SystemNotifications; 
RENAME TABLE packages TO Packages; 
RENAME TABLE pagepaths TO PagePaths; 
RENAME TABLE pagesearchindex TO PageSearchIndex; 
RENAME TABLE pagepermissionpagetypes TO PagePermissionPageTypes; 
RENAME TABLE pagepermissions TO PagePermissions; 
RENAME TABLE pagestatistics TO PageStatistics; 
RENAME TABLE pagethemes TO PageThemes; 
RENAME TABLE pagethemestyles TO PageThemeStyles; 
RENAME TABLE pagetypeattributes TO PageTypeAttributes; 
RENAME TABLE pagetypes TO PageTypes; 
RENAME TABLE pages TO Pages; 
RENAME TABLE pilecontents TO PileContents; 
RENAME TABLE piles TO Piles; 
RENAME TABLE userattributekeys TO UserAttributeKeys; 
RENAME TABLE userattributevalues TO UserAttributeValues; 
RENAME TABLE userprivatemessages TO UserPrivateMessages; 
RENAME TABLE userprivatemessagesto TO UserPrivateMessagesTo; 
RENAME TABLE userbannedips TO UserBannedIPs; 
RENAME TABLE usergroups TO UserGroups; 
RENAME TABLE uservalidationhashes TO UserValidationHashes; 
RENAME TABLE logs TO Logs; 
RENAME TABLE mailimporters TO MailImporters; 
RENAME TABLE mailvalidationhashes TO MailValidationHashes; 
RENAME TABLE useropenids TO UserOpenIDs; 
RENAME TABLE users TO Users; 
RENAME TABLE usersfriends TO UsersFriends; 
RENAME TABLE signuprequests TO SignupRequests; 
RENAME TABLE filesets TO FileSets; 
RENAME TABLE filesetsavedsearches TO FileSetSavedSearches; 
RENAME TABLE filesetfiles TO FileSetFiles; 
RENAME TABLE atboolean TO atBoolean; 
RENAME TABLE atbooleansettings TO atBooleanSettings; 
RENAME TABLE atdatetimesettings TO atDateTimeSettings; 
RENAME TABLE atdatetime TO atDateTime; 
RENAME TABLE atfile TO atFile; 
RENAME TABLE atnumber TO atNumber; 
RENAME TABLE atselectsettings TO atSelectSettings; 
RENAME TABLE attextareasettings TO atTextareaSettings; 
RENAME TABLE atselectoptions TO atSelectOptions; 
RENAME TABLE atselectoptionsselected TO atSelectOptionsSelected; 
RENAME TABLE ataddress TO atAddress; 
RENAME TABLE ataddresscustomcountries TO atAddressCustomCountries; 
RENAME TABLE ataddresssettings TO atAddressSettings; 
RENAME TABLE btnavigation TO btNavigation; 
RENAME TABLE btdatenav TO btDateNav; 
RENAME TABLE btexternalform TO btExternalForm; 
RENAME TABLE btcontentfile TO btContentFile; 
RENAME TABLE btflashcontent TO btFlashContent; 
RENAME TABLE btform TO btForm; 
RENAME TABLE btformquestions TO btFormQuestions; 
RENAME TABLE btformanswerset TO btFormAnswerSet; 
RENAME TABLE btformanswers TO btFormAnswers; 
RENAME TABLE btgooglemap TO btGoogleMap; 
RENAME TABLE btguestbook TO btGuestBook; 
RENAME TABLE btguestbookentries TO btGuestBookEntries; 
RENAME TABLE btcontentlocal TO btContentLocal; 
RENAME TABLE btcontentimage TO btContentImage; 
RENAME TABLE btfile TO btFile; 
RENAME TABLE btnextprevious TO btNextPrevious; 
RENAME TABLE btpagelist TO btPageList; 
RENAME TABLE btrssdisplay TO btRssDisplay; 
RENAME TABLE btsearch TO btSearch; 
RENAME TABLE btslideshow TO btSlideshow; 
RENAME TABLE btslideshowimg TO btSlideshowImg; 
RENAME TABLE btsurvey TO btSurvey; 
RENAME TABLE btsurveyoptions TO btSurveyOptions; 
RENAME TABLE btsurveyresults TO btSurveyResults; 
RENAME TABLE bttags TO btTags; 
RENAME TABLE btvideo TO btVideo; 
RENAME TABLE btyoutube TO btYouTube; 
RENAME TABLE collectionsearchindexattributes TO CollectionSearchIndexAttributes; 
RENAME TABLE filesearchindexattributes TO FileSearchIndexAttributes; 
RENAME TABLE usersearchindexattributes TO UserSearchIndexAttributes;
RENAME TABLE btcorescrapbookdisplay TO btCoreScrapbookDisplay;
RENAME TABLE btcorestackdisplay TO btCoreStackDisplay;
RENAME TABLE btdashboardnewsflowlatest TO btDashboardNewsflowLatest;
RENAME TABLE collectionversionrelatededits TO CollectionVersionRelatedEdits;
RENAME TABLE stacks TO Stacks;
RENAME TABLE systemantispamlibraries TO SystemAntispamLibraries;
RENAME TABLE systemcaptchalibraries TO SystemCaptchaLibraries;