Table names inconsistent across platforms
Permalink 1 user found helpful
I demoed c5 on Windows 7 using XAMPP 1.7.3. I liked it so decided to move to Ubuntu server running in VMware. Rather than reproduce some of the work I did with c5 on XAMPP I attempted to move the content to c5 on Ubuntu. I moved the files directory, backed up the database and performed a restore on Ubuntu. I intentionally left some things out because they are not important.
The restore did not seem to work even after trying various things. So I checked MySQL only to find that the restore did work but the tables in the backup where all lower case while they were camel case when installed by c5 on Ubuntu.
I checked MySQL on XAMPP and found that all the table names are lower case. Meaning the backup did work in XAMPP but for some reason the case of the table names varied from one platform to the other.
Why is that? Is this a MySQL issue or a c5 issue?
The restore did not seem to work even after trying various things. So I checked MySQL only to find that the restore did work but the tables in the backup where all lower case while they were camel case when installed by c5 on Ubuntu.
I checked MySQL on XAMPP and found that all the table names are lower case. Meaning the backup did work in XAMPP but for some reason the case of the table names varied from one platform to the other.
Why is that? Is this a MySQL issue or a c5 issue?
This sounds like an issue with the export/import in mySQL.
thats a MYSQL issue, there is an addon in the MP thats free that will uppercase them for you, or you can search the forums for the sql script
@Mnkras - I did confirm it is a MySQL issue. Google revealed that there is a variable for setting identifier case sensitivity in MySQL that can be set using the lower_case_table_names system variable. However, it is not recommended to set this to use letter case specified in SQL on case in-sensitive operating systems, such as Windows, because it may corrupt the indices.
I did find an add-on in MP to fix this problem but it is $30.
http://www.concrete5.org/marketplace/addons/mysql-case-insensitive-...
Now I just need to determine if what I have is worth saving.
I did find an add-on in MP to fix this problem but it is $30.
http://www.concrete5.org/marketplace/addons/mysql-case-insensitive-...
Now I just need to determine if what I have is worth saving.
Wow that used to be free, there is a sql snippet some where that can help
you do it, or you can do it manually
On Mar 14, 2011 10:21 PM, "Concrete5 Community" <discussions@concretecms.com>
wrote:
you do it, or you can do it manually
On Mar 14, 2011 10:21 PM, "Concrete5 Community" <discussions@concretecms.com>
wrote:
Wow, I think that's pretty sheisty that someone is charging $30 for that. To each their own, I guess.
Here's some links to the forum threads Mnkras mentioned:
http://www.concrete5.org/community/forums/installation/uncaught_exc...
http://www.concrete5.org/community/forums/installation/move_from_xa...
The first link shows you how to fix the problem you have now (you may need to alter it to make sure you have all the tables because it might be from an older version of concrete5 that had less tables than the current version has).
The second link talks about how to avoid it in the future.
I'm also attaching the SQL queries you can run against concrete version 5.4.1.1 -- download the attachment, unzip it, and run it on your database in PhpMyAdmin.
NOTE that if you had any packages installed (either from the marketplace or your own that you put in manually), you'll need to update those yourself -- but this at least takes care of 115 of them for you automatically!
Best of luck.
-Jordan
Here's some links to the forum threads Mnkras mentioned:
http://www.concrete5.org/community/forums/installation/uncaught_exc...
http://www.concrete5.org/community/forums/installation/move_from_xa...
The first link shows you how to fix the problem you have now (you may need to alter it to make sure you have all the tables because it might be from an older version of concrete5 that had less tables than the current version has).
The second link talks about how to avoid it in the future.
I'm also attaching the SQL queries you can run against concrete version 5.4.1.1 -- download the attachment, unzip it, and run it on your database in PhpMyAdmin.
NOTE that if you had any packages installed (either from the marketplace or your own that you put in manually), you'll need to update those yourself -- but this at least takes care of 115 of them for you automatically!
Best of luck.
-Jordan
You have to run an sql query to rename the tables on linux to mixed case ones. It's a pain cause you need to verify all the table names in my case 121 tables. some guy here is charging 30 bucks for such a script..which is about 25 min of work to make yourself...
In an other thread I commented that this is imo VERY unprofessional and should be corrected asap. An sql export and subsequent import no matter on what platform from the SAME cms should ALWAYS repeat ALWAYS work 100% smoothly !~ And here an export from xampp (windows) and import into linux fails with some obscure adodb errors enough to put any little savvy cms user off to run faaaaaaaaaaaaaaaaarrrrrrrrrrrrrr away ! Bad thinking again Concrete5 herr Fritzl !
In an other thread I commented that this is imo VERY unprofessional and should be corrected asap. An sql export and subsequent import no matter on what platform from the SAME cms should ALWAYS repeat ALWAYS work 100% smoothly !~ And here an export from xampp (windows) and import into linux fails with some obscure adodb errors enough to put any little savvy cms user off to run faaaaaaaaaaaaaaaaarrrrrrrrrrrrrr away ! Bad thinking again Concrete5 herr Fritzl !
Well, if every database table name was lowercase it wouldn't be a problem, but it's probably too late for that now (and having CamelCase table names does improve readability when you're developing).
What would be nice is if the installation check would look for the my.ini setting regarding lowercase table names and warn you if it's not set properly.
I'm going to suggest this to Andrew...
What would be nice is if the installation check would look for the my.ini setting regarding lowercase table names and warn you if it's not set properly.
I'm going to suggest this to Andrew...
good idea ! better late than never...
Cause concrete5 devs were not thinking properly... they should have kept all table names lowercase as to avoid these kind of probs !
I don't think it's fair to say "not thinking properly" -- there are always tradeoffs involved and I believe when C5 was first developed there were no intentions of it being released publicly (i.e. would never have to worry about windows servers).
But now it is an open source project. It's great to provide feedback, but if you feel really strongly about an issue with the software, it might be more productive to dig into the code, try to fix it, and submit the change back instead of just making judgements about people's thought processes.
http://github.com/concrete5
But now it is an open source project. It's great to provide feedback, but if you feel really strongly about an issue with the software, it might be more productive to dig into the code, try to fix it, and submit the change back instead of just making judgements about people's thought processes.
http://github.com/concrete5
ok, you're right...I agree... just didn't expect this from C5 as overall C5 was judged by me to be higly professional. so it was kind of disappointing to stumble upon this flaw just as I wanted to go live and mistakenly thought restoring the db on linux would be a poc (piece of cake) ! haha too bad...
Hi, yes. We work on windows and Mac's and use SVN now we SVN the code but the databases are different lowercase/camelcase. Maybe in the next concrete5 release all database can be lowercase?
You can fix this by adding this line to your windows mysql config files (my.ini):
(If that line already exists but the value is different, change the value to zero).
Now this won't fix any existing databases you have (you'll need to follow some of the links posted above to get queries that will change that for you), but going forward it will prevent problems.
set-variable=lower_case_table_names=0
(If that line already exists but the value is different, change the value to zero).
Now this won't fix any existing databases you have (you'll need to follow some of the links posted above to get queries that will change that for you), but going forward it will prevent problems.
It would be a solution to have concrete5 installation script do this for you otherwise it's normally allready too late and you will find out if you're importing the tables into your hosting provider sql-db...
So your web host is on Windows? If not, then this does work -- you set this on your local development machine, and then it magically works when you upload it to the unix host (assuming you've set this *before* you create the database).
no it's on linux as usual... the word BEFORE is critical here..and precisely what it meant in my previous comment.
cheers
cheers
Did you see the script I attached to my message up above (from March 15, 2011) -- you can run that on your database and it will update all the table names for an existing site.
You'll also need to do some additional queries for any addons you've installed (and that script is for 5.4.1, so there might be some slight differences for 5.4.2), but it will get you most of the way there.
I agree that it would be nice if a script were included (or better yet if the installation page would check for this variable somehow).
You'll also need to do some additional queries for any addons you've installed (and that script is for 5.4.1, so there might be some slight differences for 5.4.2), but it will get you most of the way there.
I agree that it would be nice if a script were included (or better yet if the installation page would check for this variable somehow).
Yep great, I remember I myself uploaded such a script as well can't remember where though, anyway hopefully next release tablenames will be universal accross platforms...
Hi, Yes we fixed the problem with a bit of fiddling, insert the script into the my.ini file on the windows machine to keep the camel case database. Re-import the camel case db to the windows machine (without any data) export the lowercase db on the windows machine (with data) and insert the lowercase db (with data) into the camel case db(without data) - No we had a camelcase db with the corret data..
Hi all,
Attached is a fix script for 5.5.1 - hopefully someone can confirm whether it works on 5.5.2 in the near future.
This should be used on your live site and not your WAMP site.
Thanks and enjoy.
Attached is a fix script for 5.5.1 - hopefully someone can confirm whether it works on 5.5.2 in the near future.
This should be used on your live site and not your WAMP site.
Thanks and enjoy.
And if you want to take your add-ons with the migration:
http://www.concrete5.org/marketplace/addons/database-case-sensitivi...
And by the way, that's MIT licensed, so it'll always be free.
http://www.concrete5.org/marketplace/addons/database-case-sensitivi...
And by the way, that's MIT licensed, so it'll always be free.
Here's a SQL script to rename the tables for 5.6.1.2.
(just the core tables, not any add-ons)
Run it on the target mysql server, not on your windows dev one. Use at your own risk, blah blah.
(just the core tables, not any add-ons)
Run it on the target mysql server, not on your windows dev one. Use at your own risk, blah blah.
New "ALTER TABLE" for version 6.2.1.
Hope it help someone because it was a HUGE pain for me.
Hope it help someone because it was a HUGE pain for me.
Had the same problem with 5.7 and no script was available.
Instead of manually creating a SQL script or changing the table names, I made a PHP script that get the original table names from the config files (db.xml) and the it creates the SQL script to change the table names.
Script is here:
https://gist.github.com/chris03/af05e345bedf254fded5...
Note that when running with phpMyAdmin, you should temporary enable: Settings -> SQL Queries -> Ignore multiple statement errors
And once done, check the table names, a few tables might not have been renamed.
Enjoy
Instead of manually creating a SQL script or changing the table names, I made a PHP script that get the original table names from the config files (db.xml) and the it creates the SQL script to change the table names.
Script is here:
https://gist.github.com/chris03/af05e345bedf254fded5...
Note that when running with phpMyAdmin, you should temporary enable: Settings -> SQL Queries -> Ignore multiple statement errors
And once done, check the table names, a few tables might not have been renamed.
Enjoy
