Advertisement:

Author Topic: "Specified key was too long" error when importing structure  (Read 1824 times)

Offline SomeoneElse

  • Semi-Newbie
  • *
  • Posts: 16
"Specified key was too long" error when importing structure
« on: April 08, 2018, 05:34:00 PM »
OK, as per my earlier message, I am trying to move an existing SMF board to a new server.

I am an administrator on the old one and can backup the database.

Curiously, if I tick both 'Save the table structure' and 'Save the table data (the important stuff)' the result is smaller than if I just tick the latter:

44M Apr  8 21:16 example_example-complete_2018-04-08.sql (both)
110M Apr  8 21:42 example_example-data_2018-04-08.sql (data)
42K Apr  8 21:41 example_example-structure_2018-04-08.sql (structure)

(None are compressed, and the old server doesn't seem to support that.)

When I attempt to import the structure into the new server,

Output from SQL commands in file /home/user/example_example-structure_2018-04-08.sql ..

ERROR 1071 (42000) at line 925 in file: '/home/user/example_example-structure_2018-04-08.sql': Specified key was too long; max key length is 1000 bytes

69 tables were created.

Line 925 is the..

CREATE TABLE `smf_messages` (
 `id_msg` int(10) unsigned NOT NULL auto_increment,
 `id_topic` mediumint(8) unsigned NOT NULL default 0,
 `id_board` smallint(5) unsigned NOT NULL default 0,
 `poster_time` int(10) unsigned NOT NULL default 0,
 `id_member` mediumint(8) unsigned NOT NULL default 0,
 `id_msg_modified` int(10) unsigned NOT NULL default 0,
 `subject` varchar(255) NOT NULL default '',
 `poster_name` varchar(255) NOT NULL default '',
 `poster_email` varchar(255) NOT NULL default '',
 `poster_ip` varchar(255) NOT NULL default '',
 `smileys_enabled` tinyint(4) NOT NULL default 1,
 `modified_time` int(10) unsigned NOT NULL default 0,
 `modified_name` varchar(255) NOT NULL default '',
 `body` text NOT NULL,
 `icon` varchar(16) NOT NULL default 'xx',
 `approved` tinyint(3) NOT NULL default 1,
 PRIMARY KEY (`id_msg`),
 UNIQUE `topic` (`id_topic`, `id_msg`),
 UNIQUE `ID_BOARD` (`id_board`, `id_msg`),
 UNIQUE `ID_MEMBER` (`id_member`, `id_msg`),
 KEY `participation` (`id_member`, `id_topic`),
 KEY `showPosts` (`id_member`, `id_board`),
 KEY `ID_TOPIC` (`id_topic`),
 KEY `ipIndex` (`poster_ip`(15), `id_topic`),
 KEY `approved` (`approved`),
 KEY `id_member_msg` (`id_member`, `approved`, `id_msg`),
 KEY `current_topic` (`id_topic`, `id_msg`, `id_member`, `approved`),
 KEY `related_ip` (`id_member`, `poster_ip`, `id_msg`),
 FULLTEXT `body` (`body`)
) ENGINE=MyISAM;


one. Is this down to specifying utf-8 when initially installing SMF on the new server?

I do not know what character set the original used but within the admin panel is "Convert the database and data to UTF-8", complete with a warning "Your messages table is using a fulltext index for use when searching. You cannot proceed in converting to UTF-8 until that index is removed. You can re-create it after the conversion has been completed."



Offline SomeoneElse

  • Semi-Newbie
  • *
  • Posts: 16
Re: "Specified key was too long" error when importing structure
« Reply #1 on: April 08, 2018, 05:42:20 PM »
Curiously, if I tick both 'Save the table structure' and 'Save the table data (the important stuff)' the result is smaller than if I just tick the latter:

44M Apr  8 21:16 example_example-complete_2018-04-08.sql (both)
110M Apr  8 21:42 example_example-data_2018-04-08.sql (data)
42K Apr  8 21:41 example_example-structure_2018-04-08.sql (structure)

(None are compressed, and the old server doesn't seem to support that.)

Ah, when both are selected, the result is truncated somewhere in the middle of the whole file: there's no smf_members setup in it, for example, and it ends with setting up the smf_log_topics table.


Offline SomeoneElse

  • Semi-Newbie
  • *
  • Posts: 16
Re: "Specified key was too long" error when importing structure
« Reply #2 on: April 08, 2018, 05:44:50 PM »
Ah2, the data one ends with

"<b>Fatal error</b>:  Out of memory (allocated 33816576) (tried to allocate 26738643 bytes) in <b>/home/user/public_html/main/Sources/DbExtra-mysql.php</b> on line <b>324</b><br />"

.. so that's probably not complete either.

Offline GigaWatt

  • The Smiley Guy
  • Support Specialist
  • SMF Hero
  • *
  • Posts: 1,971
  • Gender: Male
    • Macedonian electronics forum
Re: "Specified key was too long" error when importing structure
« Reply #3 on: April 08, 2018, 07:30:48 PM »
I've had the same problem. It's probably host related. Didn't really bother to find out what the problem was. Just export the database from phpMyAdmin. And if it's truncated again, try enabling compression. On my host, if I don't compress the database before I export it, once again, it's truncated ::).
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."