mysqldump restore problem due to key length related to utf8?

Started by Robomcd, January 28, 2015, 07:17:52 AM

Previous topic - Next topic

Robomcd

I did a mysqldump on my first forum running on a local server (manually installed, all the latest versions). On restoring it to our website MySQL, it got stuck on a key-length error. Probably two factors played a role here. The default storage engine is mentioned as Innodb, but in the mysqldump file the engine is several times specifically mentioned as myisam.

The other thing is that I am using a multi-language forum with 6 languages including Japanse, so the db and forum are both running utf-8. Maybe that had an influence as well?

Regardless, my host is excluding Innodb, so myisam is standard.

Question now is, can I restrict key-length so it stays within the myisam standard? Or is there another way to solve this problem?   

Arantor


Robomcd

Seems to happen more often

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes at line 1021

Line 1021 and the associated text is below

CREATE TABLE ncf_openid_assoc (
  server_url TEXT NOT NULL,
  handle VARCHAR(255) NOT NULL DEFAULT '',
  secret TEXT NOT NULL,
  issued INT(10) NOT NULL DEFAULT 0,
  expires INT(10) NOT NULL DEFAULT 0,
  assoc_type VARCHAR(64) NOT NULL,
  PRIMARY KEY (server_url(125), handle),
  INDEX expires (expires)

Arantor


Robomcd

A MySQL and SMF newbie here. Should I do this only in the backup file or should I set that value somewhere in the actual database itself to avoid future problems?

Arantor

Well, you can't do it in the database because that's the problem: it won't let you create the table with that key.

You would need to do it in the backup file (but... and I know this sounds ridiculous, take a backup first!)

Robomcd

It works

Just did what you mentioned with the original database backup file (the one that caused the problem) on my local server and restored that to a new and empty database. I am using DB Forge Studio as admin interface for the local mysql.

Maybe this is getting too much a MySQL problem to be discussed here,but maybe it can help the next stranded newbie, so I explain extensive what I did. 

Something weird happened. When I tried to install the original backup file into the new database, I ran into the same key length error. I changed the key length to 75 and the restoring went without a hitch ....  until I checked the database.

Nic, nada, zilch. Pulled my last few hairs out, took a deep breath ... and made a new backup mysqldump. Strange thing is that this time the backup file had the server value from start at 75. However, same problem.

Finally found out as well that if you use a different database name, you have change that name in the restore file. Now, all my restore files were restoring to the original database even though I started with pointing to the new database. Worked that one out as well. At the very beginning of the restore file, changed the USE value from the old to the new database name and all went without a hitch. Database fully restored in new location. Doing the same with the original backup file made that one work as well.

When restoring, a normal entry to the database was forbidden, so I entered the full url including ...forum/repair_setting.php. That bypassed the forbidden and let me enter the repair file to set the proper names and locations. Worked a charm.

A BIG THANKS, set to go public tonight!






Advertisement: