I'm attempting to import an MT MySQL DB export back inot a fresh DB through PHPMyAdmin and having problems. I get this error upon import:
ErrorSQL query:
-- --------------------------------------------------------
--
-- Table structure for table `mt_as_ua_cache`
--
CREATE TABLE IF NOT EXISTS `mt_as_ua_cache` (
`as_ua_cache_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`as_ua_cache_action_type` varchar( 255 ) NOT NULL ,
`as_ua_cache_etag` varchar( 255 ) default NULL ,
`as_ua_cache_last_modified` varchar( 255 ) default NULL ,
`as_ua_cache_url` varchar( 255 ) NOT NULL ,
PRIMARY KEY ( `as_ua_cache_id` ) ,
KEY `mt_as_ua_cache_url_type` ( `as_ua_cache_url` , `as_ua_cache_action_type` ) ,
KEY `mt_as_ua_cache_url` ( `as_ua_cache_url` ) ,
KEY `mt_as_ua_cache_action_type` ( `as_ua_cache_action_type` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =1993;MySQL said: Documentation
#1071 - Specified key was too long; max key length is 1000 bytes
I contacted my host thinking it was timing out due to a large DB, but that wasn't the issue. I got this response:
Your Database has been restored.The entry that your database contained was:
CREATE TABLE IF NOT EXISTS `mt_as_ua_cache` (
`as_ua_cache_id` int(11) NOT NULL auto_increment,
`as_ua_cache_action_type` varchar(255) NOT NULL,It should be:
CREATE TABLE IF NOT EXISTS `mt_as_ua_cache` (
`as_ua_cache_id` int(11) NOT NULL auto_increment,
`as_ua_cache_action_type` varchar(32) NOT NULL,I have now corrected it. Please check it out now and let us know if out need any further assistance.
My question is, will the change from varchar(255) to varchar(32) cause issues? Why would an MT export fail to import properly?
BTW - I'm on MT 4.24, there is no pick for that so I chose 4.25.
Reported on Movable Type 4.25
I've myself got a similar error over 1 year ago, but I don't recall what was the solution to solve it.
Have you tried to import it also from the command line (not within phpmyadmin)?
As for the export, there were some issues in the past.
What would definitely work is to upgrade movable type on the source server to v4.32, then export it and import it on the destination folder with a v4.32 installation.
Which version of MySQL?
The error is that one of the defined keys is too large. Looking at my system the columns are 255, how they picked that column and size is a mystery to me.
My guess is the database encoding is causing an issue with the key `mt_as_ua_cache_url_type` ( `as_ua_cache_url` , `as_ua_cache_action_type` ) which should be 510 bytes.
Mihai - I'm on a shared server, no command line access.
Rob - I just wanted to be sure that the change from 255 to 32 wasn't going to be an issue. Will it be?
What I'm doing is trying to convert my 5 year old MT database from latin1 to utf8. Francois gave me some step by step instructions that involve exporting the DB, using a text editor to change the encoding and then importing into a fresh MySQL DB to test. I guess I'll change the DB info in MT config and see what happens.
A few times in the past I've converted databases from say ISO to UTF-8 and the way I did it was to use a pure text tool like say UltraEdit and save the db export file using the format "UTF-8 - NO BOM".
However, I've then had to replace a bunch of special characters like for instance the curly quotes.
The job was tricky as I had to open the export text file on a firefox browser to identify garbage characters, then search for text near it to find out what character was that, then replace on UltraEdit and so on... Quite a bit of work.
However, I have the feeling that I've recently seen somewhere some details about how to automate the database conversion to UTF-8. If I'm not wrong it was somewhere on the documentation or the wiki...
That's what I'm trying to do and that's the result I got - lots of issues with curly quotes and stuff. Things that were fine before with latin1, but aren't now with utf8.
The thing I was trying to fix - display problems with Action Streams feeds - did get fixed, but I created a bunch more problems.
I'd love to know an automated way, I've Googled a lot but frankly I still don't really quite understand what encoding is. I simply want to display my blip.fm feeds on my blog. Without moving to utf8, it won't display properly.
When you say you opened the DB in Firefox, do you mean opened it directly?
I've opened it both in UltraEdit and Firefox.
On the firefox window I've set the encoding to ISO, so that the garbage characters that I see on UltraEdit after saving as UTF-8 won't show up like that, but as real characters.
Then I've search on UltraEdit for a character like for instance "Ã" and found a group of garbage characters. Then I've selected good text near that, copied it and switched to the firefox window where I've searched for that copied text and this allowed me to see what special character was that one replaced by garbage stuff...
Obviously, I've selected the group of garbage characters and replaced it with the right symbol/special character in UTF-8 format. I did this for each and every garbage character.
It is a bit of work, but it is providing excellent results (100% accuracy).
I was able to make my way through it, using the method you described. I had not expected to create so many bad characters during the conversion, that threw me and made me thing I had done something wrong. Thanks for the help.
You're welcome and glad to see you're following that path.
It is not an easy task, but it is something that works and that in my case produced excellent results.