Error creating tables (MySQL Error #1071)

[LeeThompson]

Having trouble creating the tables (note, I'm using MariaDB fork of MySQL.)

The sitebar_link table specifically (There may be others).:

CREATE TABLE `sitebar_link` (
  `lid` int(10) unsigned NOT NULL auto_increment,
  `nid` int(10) unsigned NOT NULL default '0',
  `url` text NOT NULL,
  `name` varchar(255) NOT NULL default '',
  `private` tinyint(1) default '0',
  `comment` longtext,
  `favicon` text,
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  `visited` datetime NOT NULL default '0000-00-00 00:00:00',
  `tested` datetime NOT NULL default '0000-00-00 00:00:00',
  `deleted_by` int(10) unsigned default NULL,
  `is_dead` tinyint(1) NOT NULL default '0',
  `is_feed` tinyint(1) NOT NULL default '0',
  `is_sidebar` tinyint(1) NOT NULL default '0',
  `hits` int(10) unsigned NOT NULL default '0',
  `validate` tinyint(1) NOT NULL default '1',
  `target` varchar(32),
  `type` varchar(10) DEFAULT '',
  PRIMARY KEY  (`lid`),
  UNIQUE KEY `name` (`nid`,`name`)
)
COMMENT='Each link must belong to a node.';

#1071 - Specified key was too long; max key length is 767 bytes 

What's odd is none of the keys are that long...

MariaDB: 5.5.43 (x64)

[LeeThompson]

I think I see the problem, it was my collation utf8mb4_unicode_ci makes name 1020 bytes internally. Might want to make sure collation is set with create database in config

[brablc]

I'm not sure if I can help here, or you solved your problem by using shorter collation. I guess standard utf-8 should expand up to three characters so the limit would not be exceeded.

[timfan3939]

Hi, sorry for responding to the closed issue.
I just installed sitebar today and found similar issue

Here is the error messages.

Specified key was too long; max key length is 767 bytes [ CREATE TABLE sitebar_link ( lid int(10) unsigned NOT NULL auto_increment, nid int(10) unsigned NOT NULL default '0', url text NOT NULL, name varchar(255) NOT NULL default '', private tinyint(1) default '0', comment longtext, favicon text, added datetime NOT NULL default CURRENT_TIMESTAMP, changed datetime, visited datetime, tested datetime, deleted_by int(10) unsigned default NULL, is_dead tinyint(1) NOT NULL default '0', is_feed tinyint(1) NOT NULL default '0', is_sidebar tinyint(1) NOT NULL default '0', hits int(10) unsigned NOT NULL default '0', validate tinyint(1) NOT NULL default '1', target varchar(32), type varchar(10) DEFAULT '', PRIMARY KEY (lid), UNIQUE KEY name (nid,name) ) COMMENT='Each link must belong to a node.']

Specified key was too long; max key length is 767 bytes [ CREATE TABLE sitebar_node ( nid int(10) unsigned NOT NULL auto_increment, nid_parent int(10) unsigned NOT NULL DEFAULT '0', name varchar(255) NOT NULL DEFAULT '', comment text, sort_mode char(10) DEFAULT 'user', custom_order text, type varchar(10) DEFAULT '', deleted_by int(10) unsigned DEFAULT NULL, PRIMARY KEY (nid), UNIQUE KEY name (nid_parent,name), KEY pnid (nid_parent) ) COMMENT='Node contains other nodes and links.']

Specified key was too long; max key length is 767 bytes [ CREATE TABLE sitebar_cache ( type varchar(10) NOT NULL, ckey varchar(255) NOT NULL, cvalue LONGBLOB NOT NULL, created datetime NOT NULL default CURRENT_TIMESTAMP, expires datetime, PRIMARY KEY (type, ckey) ) COMMENT='Contains multipurpose cache.']

Specified key was too long; max key length is 767 bytes [ CREATE TABLE sitebar_data ( type varchar(10) NOT NULL, dkey varchar(255) NOT NULL, dvalue LONGBLOB NOT NULL, PRIMARY KEY (type, dkey) ) COMMENT='Offers multipurpose data storage space.']

Specified key was too long; max key length is 767 bytes [ CREATE TABLE sitebar_user_data ( type varchar(10) NOT NULL, uid int(10) unsigned NOT NULL, dkey varchar(255) NOT NULL, dvalue LONGBLOB NOT NULL, PRIMARY KEY (type, uid, dkey) ) COMMENT='Offers multipurpose user data storage space.']

Table 'sitebar.sitebar_node' doesn't exist [ INSERT INTO sitebar_node (nid,nid_parent,name,comment) VALUES (1, 0, 'Admin Bookmarks', 'Bookmarks of SiteBar Administrators')]

Table 'sitebar.sitebar_node' doesn't exist [ INSERT INTO sitebar_node (nid,nid_parent,name,comment) VALUES (2, 0, 'Public Bookmarks', NULL)]

Table 'sitebar.sitebar_node' doesn't exist [ INSERT INTO sitebar_node (nid,nid_parent,name,comment, sort_mode) VALUES (3, 2, 'SiteBar Project', 'Bookmarks related to the SiteBar open source project.', 'custom')]

Table 'sitebar.sitebar_node' doesn't exist [ INSERT INTO sitebar_node (nid,nid_parent,name,comment, sort_mode) VALUES (4, 2, 'Web Search', 'Type a search term in the toolbar and click on one of the links to search this term using that search engine. Using the SiteBar Sidebar Extension for Mozilla Firefox you can middle click on the folder to search all engines each in its own tab.', 'custom')]

The solution would be adding "ENGINE=InnoDB DEFAULT CHARSET=UTF8" to the end of the "CREATE TABLE" statements

I hope this would help.