Avatar of HelpNearMe
HelpNearMe
Flag for Afghanistan asked on

MySQL change Collation in tables

Hi Experts,

I have a database that was restored from a backup by the ISP.  The dump file seems to have used UTF-general and there are menu tables that had Spanish characters in them.  I see now when I use phpmyadmin that these columns are using UTF-general-ci and the Spanish characters are appearing as crazy lookig characters.  How can I change the collation so these characters appear correctly without restoring the database?

Thanks,

HNM
MySQL ServerDatabasesJoomla

Avatar of undefined
Last Comment
Muhammad Wasif

8/22/2022 - Mon
Carlos Llanos

Maybe this is something your ISP would be able to assist you with?  I'll check into, but personally they would be my first stop...
HelpNearMe

ASKER
I agree, but heres the thing.. and I'm so pissed now that I don't mind naming names.   The host is myhosting.com and for 42 hours now a client site has been down and I have been trying to get them to restore the DB.  They asked me for a dump file (why I don't know) so I sent it over and the collation in the dump file is UTF-8 so the Spanish characters don't showup correctly.  I've asked them again.. for the 12th time to pull a backup and restore on their end.  42 hours with a live sales site down and 70+ agents breathing down my next and all I get are canned responsed from their 'escalated' tech support.  yes I'm ranting.  

Thanks for your comments and I'll leave the question open in someone can think of something, besides changing ISPs.

Thanks,

HNM
Carlos Llanos

Wow, that's horrible customer service.  I will do some tests on my side to see if I can come up with something, but I have to be honest, I've never tried anything like that...so I'm not promising anything.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Carlos Llanos

Do you happen to have a copy of the sql file that is not working properly?
HelpNearMe

ASKER
I do yes but when I did the dump in PHPMyAdmin I think the specified collation was set to UTF8... thats my guess.  Here is the rub... there PHPMyAdmin times out after 300 seconds when uploading a sql file.  This one is 24mb so it always times out even with a fast connection.  So I'm really at the mercy of their support guys.  I can't upload the file myself.  So if I make edits to the file.. I still have to wait for them to install it, thats why I'm insisting the just pull the DB from thier backup.

Short answer.. yes I have a copy ;)

HNM
ASKER CERTIFIED SOLUTION
Carlos Llanos

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
HelpNearMe

ASKER
thanks for the comments, seems like I'm stuck waiting for them.

Cheers,

HNM
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Muhammad Wasif

This is not a problem of slow connection, this is due to the upload limit of phpMyAdmin (php settings). You may want to try Bigdump which was made for similar kind of situations.
Muhammad Wasif

Which versions of MySQL you are running on old and new machines? Which method you used to export the data (mysqldump or phpMyAdmin)?

I believe you should use –default-character-set=charset_name (replace charset_name with your desired character set when exporting and importing from client side).