Author Topic: Garbled characters from database import (SOLVED)  (Read 17844 times)

phoenix

  • Jr. Member
  • **
  • Posts: 19
Garbled characters from database import (SOLVED)
« on: March 07, 2008, 04:55:06 PM »
We recently moved http://terrafirmascapers.com to GCS from LunarPages. Upon install of the database, we discovered that the Word autoformat characters aren't formatting properly in SMF 1.1.4. Here is a list of the garbles that we have found:

“ = “
” = â€
’ = ’
‘ =
– = –
— = 
… = …
In addition all foreign characters are displaying garbled as well.

I verified the settings for character collation between the two myphpadmins and they are identical. I have tried setting MySQL Character collation to both utf8_unicode and latin1_swedish_ci (which is the collation all the tables are in) and with both compressed and uncompressed backups of the old database. I am running these tests on my server rather than keep losing messages on the forum at our site. This mostly affects the large amount of fanfiction we have posted and new posts made using Word special characters are displaying just fine.

One of our forum admins has a son involved in computers and databases who believes this may be a collation setting difference between the two servers, but I didn't notice anything different. Looking into the database on your server, the garbled characters are part of the database now.

I opened the .sql file and notice that it has the quotations formatted properly. Any thoughts as to what settings are needed to correct this are greatly appreciated.


« Last Edit: March 08, 2008, 10:10:58 PM by G.C. Admin »

G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #1 on: March 07, 2008, 05:02:26 PM »
Phoenix,

 I had contacted our server managment company concerning this issue. After it first reared its ugly head.

 They had re-imported the database for a second time just a few days ago. And the strange chars still reside. There is nothing they can really do.

 I would suggest that anymore imports would not be acceptable since the further your forums move along in the database the more post you will lose if we do another import.

 Now if we could find a MYSQL wizard that could give us some sort of query that we could run to replace these chars that would be the best thing to do. You might want to hit up MYSQL type forums and post in them. And maybe we can find a query to fix those issues.

 Frank

 
Frank Doud
Owner/Operator
G.C. Solutions

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #2 on: March 07, 2008, 05:08:51 PM »
Oh, I agree in not reimporting anymore. I was just wondering if perhaps someone else here might have heard of this since we are getting a little narrowing down on the issue. We are just working with a database not associated with the forum as it is now so as not to lose more posts. I think we'll wait to hear back from our admin's kids and see if they have anything to offer and then head on over to the MySQL folks and see if they know how to fix this.

Without seeing the errors in the database he suggested it might be UTF8 data being treated like Latin1 data, thus the various exports with different settings.

G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #3 on: March 07, 2008, 05:12:53 PM »
Phoenix,

 Since the chars are residing in the current database. A MYSQL query type solution is the best and quickest way to deal with this issue.

 Otherwise it is modifying each post by hand DOH!!!!

 Please keep this post updated if and when you find a solution for this issue.

Frank
Frank Doud
Owner/Operator
G.C. Solutions

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #4 on: March 07, 2008, 06:31:42 PM »
Frank (I should have said this is Jean :D),

I did find a solution. Thankfully Site5 grants shell access so I just took the old database and uploaded it to my server. From there I accessed the shell and changed over to that directory and ran the following query to upload it to the test forum I have:

mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql

If we can either arrange for shell access on our side or coordinate a time to enter maintenance mode so that someone who does can do this, it would be much appreciated. There's no huge rush on this. :) In fact, we would like to give the folks some warning this is going to happen and an approximate time frame.



G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #5 on: March 07, 2008, 07:11:54 PM »
Frank (I should have said this is Jean :D),

I did find a solution. Thankfully Site5 grants shell access so I just took the old database and uploaded it to my server. From there I accessed the shell and changed over to that directory and ran the following query to upload it to the test forum I have:

mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql

If we can either arrange for shell access on our side or coordinate a time to enter maintenance mode so that someone who does can do this, it would be much appreciated. There's no huge rush on this. :) In fact, we would like to give the folks some warning this is going to happen and an approximate time frame.




Jean,

 Are you saying you wish to re-import the old databse that was taken from LP? If that is the case please upload that database into the public_html folder of the site. Please do not name it dargo.sql because that database export still resides on the server.

 After you have uploaded the Db and have warned your users. Plesee contact me. I will then drop all tables and re-install the Db per your code.

Frank
Frank Doud
Owner/Operator
G.C. Solutions

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #6 on: March 07, 2008, 07:19:54 PM »
Frank, we would be going into maintenance mode and making a current backup of the forum. There would be no going back in time. Everything I saw shows that it should work from the database we have now. And if not, well, folks can manually copy files from the backup I loaded to a different forum.

Is there any time that is better for you to do the upload? The weather here is icky so I'm not going anywhere. We would like to at least give folks a couple hours of heads up if possible, but if not, that's what the Maintenance Mode message is for.

G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #7 on: March 07, 2008, 07:23:04 PM »
Jean,

Just let me know when you would want this completed. I will then export the current Db and re-import via your code.

Frank
Frank Doud
Owner/Operator
G.C. Solutions

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #8 on: March 07, 2008, 08:02:31 PM »
Frank,

Let's shoot for 3pm PST, it's pretty quiet then. We'll put the site in Maintenance Mode then.

G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #9 on: March 07, 2008, 08:05:55 PM »
Frank,

Let's shoot for 3pm PST, it's pretty quiet then. We'll put the site in Maintenance Mode then.

Ok i will be there :)
Frank Doud
Owner/Operator
G.C. Solutions

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #10 on: March 07, 2008, 10:00:17 PM »
Sounds good. Since you are going to do the back up of the database, I though the rest of the directions might be useful. I'm not sure if it would be necessary to export through the shell in the way listed there to ensure all fields are the latin1. I didn't end up using the chgrep or sed commands, just essentially followed what would have been xy77's directions, though the database I used was one made from the latin1 download I did since I accidentally overwrote the one I did with the default settings. LOL I also just used the cPanel to create the initial database on my server and dropped all the tables in it through phpmyadmin every time I did a reinstall, so I'm guessing that was just default settings, which is likely what the one on this server has, so I would assume you could empty it either through the shell or through phpmyadmin and it would work. My knowledge of using the shell is incredibly limited. :D

http://textsnippets.com/posts/show/84#comments

G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #11 on: March 07, 2008, 10:07:46 PM »
No worries i wll post up here when it has been completed.

Frank
Frank Doud
Owner/Operator
G.C. Solutions

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #12 on: March 07, 2008, 10:25:50 PM »
Thanks, Frank. I appreciate all the support you are giving us trying to get everything sorted, stable and back to the way it was. :D I have this thread on notify, so I'll see your reply that everything is uploaded.

phoenix

  • Jr. Member
  • **
  • Posts: 19
Re: Garbled characters from database import
« Reply #13 on: March 07, 2008, 11:05:06 PM »
Okay, we are in maintenance mode now. :D

G.C. Admin

  • Performance Based Hosting
  • Administrator
  • Hero Member
  • *****
  • Posts: 990
Re: Garbled characters from database import
« Reply #14 on: March 07, 2008, 11:54:42 PM »
Okay, we are in maintenance mode now. :D

Just a heads up. I had some fires to put out here at work. so i tossed this over to our server magment company. The ticket was placed about 30 minutes ago.

I am now heading home from work. I will check back with the server management when i get home.

Frank
Frank Doud
Owner/Operator
G.C. Solutions