"Specified key was too long" error when importing structure

[SomeoneElse]

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( 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( 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."

[SomeoneElse]

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.

[SomeoneElse]

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.

[GigaWatt]

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 .

[Illori]

https://wiki.simplemachines.org/smf/Backup#Backing_up_your_Database