Handle charset problems while upgrading

Please post all install related problems here. Visit this forum first for troubleshooting.
Post Reply
User avatar
Oliver Georgi
Site Admin
Posts: 9443
Joined: Fri 3. Oct 2003, 22:22
Location: Dessau
Contact:

Handle charset problems while upgrading

Post by Oliver Georgi » Mon 2. Apr 2012, 08:44

While upgrading an older phpwcms I stepped into a problem which might be a problem for you too. I try to describe what you can do if this happens to you too and you do not know why why why…

Some older installations of phpwcms were installed on hosting accounts which are online since a long time. So your hosting company/sysadmin might have upgraded MySQL 3.x to 4.x to 5.x. MySQL saw a lot of improvements regarding charset handling. This can cause problems when you try to upgrade on another account than the one is still productive.

I always recommend to make a copy and do the upgrade there. But export and re-import into new database might fail. I saw such behavior while upgrading a phpwcms install with windows-1250/cp1250 and collation cp1250_czech_cs. That forced me to find a solution. Keep in mind that a backup might make problems too, so test against such possible problems before it is too late. phpMyAdmin, MySQLDumper and also Chive failed. But I stick with phpMyAdmin. It is still the best browser based MySQL tool.

The only chance was to make a copy into the same existing database. Be warned — handle with care! You can loose all data!

Copying into the same database means to add a new table prefix to get a new table name like newprefix_phpwcms_article ($phpwcms['db_prepend'] = 'newprefix').

First you need the list of all table names of your old phpwcms install – select SQL tab of your database and fill in

Code: Select all

SHOW TABLES
You get a list of all table names – select and copy these into the clipboard then open Excel or LibreOffice/OpenOffice or any other spreadsheet application. Paste the table names into the first column (A). Then use a second column (B) and fill in the formula to generate the new table name including a new prefix or replace existing prefix. If your table names still have prefixes (maybe myprefix8 chars long) do something like this:

Code: Select all

=REPLACE(A1;1;8;"mynewprefix")
If you just want to add a prefix:

Code: Select all

="mynewprefix_"&A1
Keep in mind that phpwcms will add prefix seperator "_" automatically.

The last step is to generate the formula necessary to duplicate all existing old tables into new named.

Code: Select all

="CREATE TABLE "&B1&" LIKE "&A1&"; INSERT INTO "&B1&" SELECT * FROM "&A1&";"
Then select the column with the final SQL queries and copy into clipboad then paste it into the SQL tab again and pray ;-). You might see problems – so check double indexes first and so on.

If you are not really sure what you are doing there try on a backup first or ask someone else.

Then duplicate your existing installation and make it running based on duplicated database tables. Then start the final upgrade!
Oliver Georgi | phpwcms Developer | GitHub | LinkedIn | Kleintierpraxis am Georgengarten

Post Reply