By albertc on

Hi

Due to Fantastico's now well-known problems with Drupal upgrades, my site got stuck in the middle of an upgrade to 4.6 (to be immediately upgraded itself to 4.7, of course) and the only thing I have now are the site backups (code+DB) that Fantastico made.

Now I'm trying to restore my site manually from those backups, in order to upgrade it manually later. However, I'm unable to find the appropriate selection of DB character set in phpMyAdmin when importing the SQLdump that Fantastico made:

  • When I choose the default UTF-8 in phpMyAdmin, the import process breaks due to problems in some tables.
  • When I choose the LATIN1 or CP850 encodings in phpMyAdmin, the import process goes through the end, but all the 'foreign' characters in my content (i.e. accented letters and so on) come out garbled, which is unacceptable.

Can somebody explain how to detect the charset of an SQLdump? This is what I have in the first lines of my 35 MB backup.sql file:

-- MySQL dump 9.11
--
-- Host: localhost    Database: c10blogs_drpl1
-- ------------------------------------------------------
-- Server version	4.0.26-standard

Please note that I can't do a fresh SQL dump using a specific charset, because the site is broken, so I must work with the one Fantastico made.

Thank you for any assistance
--
Albert

⋅ Categories: Drupal 4.5.x or older

Comments

carlmcdade’s picture

carlmcdade commented

I have the same problem and have not found any good solution. There does not seem to be anyway of doing this so I went about manually fixing the characters after recovering the dump. I guess it depends on the language how long and drawn out this gets but once you have written the script then you have it on ice for later use.


UPDATE `node_revisions` SET teaser=replace(teaser,'ä','ä');
UPDATE `node_revisions` SET teaser=replace(teaser,'ö','ö');
UPDATE `node_revisions` SET teaser=replace(teaser,'Ã¥','å');
UPDATE `node_revisions` SET body=replace(body,'ä','ä');
UPDATE `node_revisions` SET body=replace(body,'ö','ö');
UPDATE `node_revisions` SET body=replace(body,'Ã¥','å');
UPDATE `node_revisions` SET title=replace(title,'ä','ä');
UPDATE `node_revisions` SET title=replace(title,'ö','ö');
UPDATE `node_revisions` SET title=replace(title,'Ã¥','å');

Hiveminds Magazine
http://www.hiveminds.co.uk
for web publishers and community builders

albertc’s picture

albertc commented

Hiveminds

Thanks for your reply. Actually, I have also changed the characters manually in a text editor, but they still are restored wrong.

Sorry, but I don't understand what you mean with a 'script'. I don't have SSH access to the server, so how can I run such a thing?

Thank you
--
Albert

carlmcdade’s picture

carlmcdade commented

Sorry, that was one of those "developer assumes user knows" posts.

You can run those lines in phpMyAdmin SQL box and it will make the changes in the database tables. So rather than changing the db export script you would just run it and make the changes at the database level. This is a little faster and more reliable than doing a search and replace on a 30mb db file.

As far as them being stored wrong you can't do much about that if the server is not configured correctly. I am right now trying to figure out how to get a server to post utf-8 via a PHP file. Nothing seems to work. I have tried htaccess Addtype and other tricks. My final destination is the server admin to tell him to change php.ini

Hiveminds Magazine
http://www.hiveminds.co.uk
for web publishers and community builders

albertc’s picture

albertc commented

Well, I did this:

UPDATE  `node` SET teaser = replace( teaser,  '?�',  'í' ) ;

and this is what I got:

#1270 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_unicode_ci,COERCIBLE), (utf8_unicode_ci,COERCIBLE) for operation 'replace'

Any suggestions?

carlmcdade’s picture

carlmcdade commented

If you are seeing question marks in phpMyAdmin then you have some serious issues which go beyond a simple changing of characters.

Hiveminds Magazine
http://www.hiveminds.co.uk
for web publishers and community builders