I’m moving over to a new server - going from mysql 3.23 to 4.1 - and my admin says that 4.1 conflicts with field names in EE. I have a hard time believing this because EE is used on so many various systems. He suggest renaming the tables, which I certainly don’t want to do!

His message below:
“We have to run mysql 4.1

There are some reserved words in 4.1 that conflict with some column names that you have, those columns will have to be renamed, which means you’ll have to modify some of your code.

Example,

eengine database, there is a table called exp_member_search that has a column named “fields” but fields is now a reserved word, so it cant be used.

All together, I think there are about 4 or 5 tables that have issues. I will get everything migrated, make the appropriate changes, and document it. Then when you go in to test the site, you’ll have to make some minor code changes for those modified tables.

Comments, please…...

I received an answer to this from Paul by PM and will post it here for posterity:

“This is why MySQL has backtick characters that go around table and field names to protect them from this.  When your data is exported make sure whatever is doing the export adds the backticks around these fields and table names. -Paul”

My comment:
It certainly would be nice for some additional technical data about migration of this type to be located somewhere on the site. For instance, can one usually simply zip up the old dbs on one server, then move them, install a more current mysql version, and then rebuild? Mysql seems to say “no” to this, stating that dumps and command line imports are favored….which then brings out the above issue(s) of conflicting names, etc.

I guess migration is not for the faint of heart! Hopefully, it will be another 4-5 years before I have to go through this again…..

ExpressionEngine and phpMyAdmin when they export, by default, include these backticks.  I have not done a mysqldump through the command line in a while, but the last I checked it escaped these field and table names by default too.  Nevin has probably done hundreds of these before without problems.  I would not think, ever, that one could simply zip up the database files on one server (not exporting) and transfer them to a new server and expect a far newer version of the software to simply use them without some process of updating happening beforehand.

Yes, migrating MySQL data takes some knowledge and experience, but EllisLab is not the one who builds or supports the MySQL server or clients.  Your server administrator is the one who is responsible for that and the knowledge of how to do it properly.  We try to assist when we can and provide abilities to assist, but our responsibilty cannot really be seen to stretch that far.

Paul Burdick - 28 September 2007 12:29 PM

ExpressionEngine and phpMyAdmin when they export, by default, include these backticks.  I have not done a mysqldump through the command line in a while, but the last I checked it escaped these field and table names by default too.  Nevin has probably done hundreds of these before without problems.  I would not think, ever, that one could simply zip up the database files on one server (not exporting) and transfer them to a new server and expect a far newer version of the software to simply use them without some process of updating happening beforehand.

We try to assist when we can and provide abilities to assist, but our responsibility cannot really be seen to stretch that far.

That by itself is valuable info - that the “reserved” field names dump out and re-import correctly….and, yes, I agree that is not part of the “contract” - but I’d always rather learn from others experiences than to have to make the mistakes myself!

BTW, the first try seems to work well….at least in testing!

Since our particular site, Hearth.com, receives many thousands of visitors per day at this time of year, I think it is prudent for me to be both paranoid and as informed as possible.

Luckily, my admin is VERY good…..but I’m certain some of my “fear” rubbed off on him and he wanted to make certain of things. Luckily, I knew enough to tell him two things:

1. Do Not modify those table names!
2. The folks at EE will help us out quickly….

Well, maybe I will start a wiki entry on some of this, and let the smarter folks (Nevin, Paul, etc.) add to it.

Ha, someday we’ll probably see a super-tech here start a site with “ANY EE questions answered” $25 an instance (or more)...

Oh, now that’s tempting.  Except I know better - “any EE questions answered” can turn into a multi-week answer.  Fair warning to any who try.

I started a wiki entry on server migration:
http://expressionengine.com/wiki/Server_Migration/

Hopefully, Nevin and others can add to it - and maybe someday we will actually have a guide which is complete.