Forums

Database migration - case sensitive bugs [table names from 7.3 to 8.4.2]

Permalink 2 users found helpful
Hello,

I have migrated a concrete5 test version from Windows EasyPHP to a webserver with Linux.
I have faced problems with a case sensitive MySQL.
If you are confronted to the same problem, these SQL queries can help you :

- [-new-] for 8.4.2: https://www.concrete5.org/community/forums/installation/db-migrating...

- for 8.3.2: https://www.concrete5.org/community/forums/installation/db-migrating...

- for 8.2: https://www.concrete5.org/community/forums/installation/db-migrating...

- for 8.1 version: https://www.concrete5.org/community/forums/installation/concrete5-8-... (thanks to Cocolabaloune)

- for Concrete5.7.5: https://www.concrete5.org/community/forums/installation/db-migrating...

- for Concrete5.7.4.2: https://www.concrete5.org/community/forums/installation/db-migrating...

- for Concrete5.7.3.x:

rename table `arealayoutcolumns` to `AreaLayoutColumns` ;
rename table `arealayoutcustomcolumns` to `AreaLayoutCustomColumns` ;
rename table `arealayoutpresets` to `AreaLayoutPresets` ;
rename table `arealayouts` to `AreaLayouts` ;
rename table `arealayoutthemegridcolumns` to `AreaLayoutThemeGridColumns` ;
rename table `areapermissionassignments` to `AreaPermissionAssignments` ;
rename table `areapermissionblocktypeaccesslist` to `AreaPermissionBlockTypeAccessList` ;
rename table `areapermissionblocktypeaccesslistcustom` to `AreaPermissionBlockTypeAccessListCustom` ;
rename table `areas` to `Areas` ;
rename table `ataddress` to `atAddress` ;
rename table `ataddresscustomcountries` to `atAddressCustomCountries` ;
rename table `ataddresssettings` to `atAddressSettings` ;
rename table `atboolean` to `atBoolean` ;
rename table `atbooleansettings` to `atBooleanSettings` ;
rename table `atdatetime` to `atDateTime` ;
Viewing 15 lines of 249 lines. View entire code block.
 
Uranus replied on at Permalink Reply
Uranus
Hi,

Thanks , this is really helpful. You can also use' Database Case Sensitivity Migration'
https://www.concrete5.org/marketplace/addons/database-case-sensitivi... addon
for case sensitivity issue.

Thanks
lyc replied on at Permalink Reply
Thanks,

This seems to be a good file!

But it seems also that this add-on is from version 5.4 to 5.6.
I'm using 5.7, and maybe it works with. But I found some files on the internet listing the 5.5 tables: compared to 5.7, a lot are missing...

Thanks,

Regards,
Draze replied on at Permalink Reply
Draze
You sir, are a hero. I am also running 5.7 and could not find any easy fixes, but your SQL code worked perfectly to fix my problem.

Just to note, I was getting an error about <mydatabasename>.Package could not be found, but it was because of migrating a mySQL database from Windows to mySQL database on Linux.
lyc replied on at Permalink Reply
Thanks.

New code for the latest version: 5.7.4.2.

RENAME TABLE arealayoutcolumns TO AreaLayoutColumns ;
RENAME TABLE arealayoutcustomcolumns TO AreaLayoutCustomColumns ;
RENAME TABLE arealayoutpresets TO AreaLayoutPresets ;
RENAME TABLE arealayouts TO AreaLayouts ;
RENAME TABLE arealayoutthemegridcolumns TO AreaLayoutThemeGridColumns ;
RENAME TABLE areapermissionassignments TO AreaPermissionAssignments ;
RENAME TABLE areapermissionblocktypeaccesslist TO AreaPermissionBlockTypeAccessList ;
RENAME TABLE areapermissionblocktypeaccesslistcustom TO AreaPermissionBlockTypeAccessListCustom ;
RENAME TABLE areas TO Areas ;
RENAME TABLE ataddress TO atAddress ;
RENAME TABLE ataddresscustomcountries TO atAddressCustomCountries ;
RENAME TABLE ataddresssettings TO atAddressSettings ;
RENAME TABLE atboolean TO atBoolean ;
RENAME TABLE atbooleansettings TO atBooleanSettings ;
RENAME TABLE atdatetime TO atDateTime ;
Viewing 15 lines of 250 lines. View entire code block.


One line added.

--> SQL file: https://www.sendspace.com/file/6wuc9b...
lyc replied on at Permalink Reply
Now, new SQL code for database migration to 5.7.5 :

RENAME TABLE arealayoutcolumns to AreaLayoutColumns ; 
RENAME TABLE arealayoutcustomcolumns to AreaLayoutCustomColumns ; 
RENAME TABLE arealayoutpresets to AreaLayoutPresets ; 
RENAME TABLE arealayouts to AreaLayouts ; 
RENAME TABLE arealayoutsusingpresets to AreaLayoutsUsingPresets ; 
RENAME TABLE arealayoutthemegridcolumns to AreaLayoutThemeGridColumns ; 
RENAME TABLE areapermissionassignments to AreaPermissionAssignments ; 
RENAME TABLE areapermissionblocktypeaccesslist to AreaPermissionBlockTypeAccessList ; 
RENAME TABLE areapermissionblocktypeaccesslistcustom to AreaPermissionBlockTypeAccessListCustom ; 
RENAME TABLE areas to Areas ; 
RENAME TABLE ataddress to atAddress ; 
RENAME TABLE ataddresscustomcountries to atAddressCustomCountries ; 
RENAME TABLE ataddresssettings to atAddressSettings ; 
RENAME TABLE atboolean to atBoolean ; 
RENAME TABLE atbooleansettings to atBooleanSettings ;
Viewing 15 lines of 252 lines. View entire code block.
lyc replied on at Permalink Reply
For "8" version, it's here :
https://www.concrete5.org/community/forums/installation/concrete5-8-...
Thanks to Cocolabaloune,
lyc replied on at Permalink Reply
For 8.2 :

RENAME TABLE    arealayoutcolumns    TO    AreaLayoutColumns
RENAME TABLE    arealayoutcustomcolumns    TO    AreaLayoutCustomColumns
RENAME TABLE    arealayoutpresets    TO    AreaLayoutPresets
RENAME TABLE    arealayouts    TO    AreaLayouts
RENAME TABLE    arealayoutsusingpresets    TO    AreaLayoutsUsingPresets
RENAME TABLE    arealayoutthemegridcolumns    TO    AreaLayoutThemeGridColumns
RENAME TABLE    areapermissionassignments    TO    AreaPermissionAssignments
RENAME TABLE    areapermissionblocktypeaccesslist    TO    AreaPermissionBlockTypeAccessList
RENAME TABLE    areapermissionblocktypeaccesslistcustom    TO    AreaPermissionBlockTypeAccessListCustom
RENAME TABLE    areas    TO    Areas
RENAME TABLE    ataddress    TO    atAddress
RENAME TABLE    ataddresssettings    TO    atAddressSettings
RENAME TABLE    atboolean    TO    atBoolean
RENAME TABLE    atbooleansettings    TO    atBooleanSettings
RENAME TABLE    atdatetime    TO    atDateTime
Viewing 15 lines of 319 lines. View entire code block.
lyc replied on at Permalink Reply
For Concrete5 8.3.2 :

RENAME TABLE   arealayoutcolumns    TO   AreaLayoutColumns    ;
RENAME TABLE   arealayoutcustomcolumns    TO   AreaLayoutCustomColumns    ;
RENAME TABLE   arealayoutpresets    TO   AreaLayoutPresets    ;
RENAME TABLE   arealayouts    TO   AreaLayouts    ;
RENAME TABLE   arealayoutsusingpresets    TO   AreaLayoutsUsingPresets    ;
RENAME TABLE   arealayoutthemegridcolumns    TO   AreaLayoutThemeGridColumns    ;
RENAME TABLE   areapermissionassignments    TO   AreaPermissionAssignments    ;
RENAME TABLE   areapermissionblocktypeaccesslist    TO   AreaPermissionBlockTypeAccessList    ;
RENAME TABLE   areapermissionblocktypeaccesslistcustom    TO   AreaPermissionBlockTypeAccessListCustom    ;
RENAME TABLE   areas    TO   Areas    ;
RENAME TABLE   ataddress    TO   atAddress    ;
RENAME TABLE   ataddresssettings    TO   atAddressSettings    ;
RENAME TABLE   atboolean    TO   atBoolean    ;
RENAME TABLE   atbooleansettings    TO   atBooleanSettings    ;
RENAME TABLE   atdatetime    TO   atDateTime    ;
Viewing 15 lines of 317 lines. View entire code block.
lyc replied on at Permalink Best Answer Reply
For Concrete5 8.4.2 :

RENAME TABLE    arealayoutcolumns     TO    AreaLayoutColumns     ;
RENAME TABLE    arealayoutcustomcolumns     TO    AreaLayoutCustomColumns     ;
RENAME TABLE    arealayoutpresets     TO    AreaLayoutPresets     ;
RENAME TABLE    arealayouts     TO    AreaLayouts     ;
RENAME TABLE    arealayoutsusingpresets     TO    AreaLayoutsUsingPresets     ;
RENAME TABLE    arealayoutthemegridcolumns     TO    AreaLayoutThemeGridColumns     ;
RENAME TABLE    areapermissionassignments     TO    AreaPermissionAssignments     ;
RENAME TABLE    areapermissionblocktypeaccesslist     TO    AreaPermissionBlockTypeAccessList     ;
RENAME TABLE    areapermissionblocktypeaccesslistcustom     TO    AreaPermissionBlockTypeAccessListCustom     ;
RENAME TABLE    areas     TO    Areas     ;
RENAME TABLE    ataddress     TO    atAddress     ;
RENAME TABLE    ataddresssettings     TO    atAddressSettings     ;
RENAME TABLE    atboolean     TO    atBoolean     ;
RENAME TABLE    atbooleansettings     TO    atBooleanSettings     ;
RENAME TABLE    atdatetime     TO    atDateTime     ;
Viewing 15 lines of 340 lines. View entire code block.
b3rimbau replied on at Permalink Reply
b3rimbau
For Concrete5 8.5.2 :

RENAME TABLE arealayoutcolumns TO AreaLayoutColumns;
RENAME TABLE arealayoutcustomcolumns TO AreaLayoutCustomColumns;
RENAME TABLE arealayoutpresets TO AreaLayoutPresets;
RENAME TABLE arealayouts TO AreaLayouts;
RENAME TABLE arealayoutsusingpresets TO AreaLayoutsUsingPresets;
RENAME TABLE arealayoutthemegridcolumns TO AreaLayoutThemeGridColumns;
RENAME TABLE areapermissionassignments TO AreaPermissionAssignments;
RENAME TABLE areapermissionblocktypeaccesslist TO AreaPermissionBlockTypeAccessList;
RENAME TABLE areapermissionblocktypeaccesslistcustom TO AreaPermissionBlockTypeAccessListCustom;
RENAME TABLE areas TO Areas;
RENAME TABLE ataddress TO atAddress;
RENAME TABLE ataddresssettings TO atAddressSettings;
RENAME TABLE atboolean TO atBoolean;
RENAME TABLE atbooleansettings TO atBooleanSettings;
RENAME TABLE atdatetime TO atDateTime;
Viewing 15 lines of 329 lines. View entire code block.
b3rimbau replied on at Permalink Reply
b3rimbau
And don't forget that constraints also need fixing, as well as tables associated with any non-core blocks.
ConcreteOwl replied on at Permalink Reply
ConcreteOwl
Yup
I have found the best way to fix badly formatted constraints is to open up the database file in a text editor and search for the naughty ones.
It is more of a problem when using a windows based development server.

Well done for posting the Rename Tables fix, very useful...