Database error "Specified key was too long; max key length is 767 bytes" when upgrading from 4.2.2 to 5.1.5 [Solved]

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • qpo
    New Member
    • May 2013
    • 3
    • 4.2.X

    Database error "Specified key was too long; max key length is 767 bytes" when upgrading from 4.2.2 to 5.1.5 [Solved]

    I am migrating my forum from vbulletin 4.2.2 to vbulletin connect 5.1.5 and had encountered a problem

    Code:
    Specified key was too long; max key length is 767 bytes
    This particular error occurs when you have the coincidence of two conditions:
    • Forum uses UTF-8 encoding
    • MySQL supports InnoDB format
    The upgrade script tries to upgrade using the InnoDB format if it possible, because it gives a number of advantages against MyISAM format (row-level locking rather than table etc)

    This problem was described on the MySQL site and due to the fact that the default InnoDB index key for one column can be up to 767 bytes. Given that the UTF8 characters can take up to 3 bytes, the length of the indexed columns can not be more than ~255 bytes. Solution of this problem can be performed in several ways: to include configuration parameter innodb_large_prefix and declare the format strings as DYNAMIC or COMPRESSED. Or force to declare problem tables as MyISAM (there are only two problem tables)

    For this purpose, the file /core/install/includes/class_upgrade_500a1.php needs to make two changes:

    Line 141 and line 384

    PHP Code:
    ENGINE ". $this->hightrafficengine . " 
    replaced by
    PHP Code:
    ENGINE MyISAM 
    This solution will bypass the problem and allow to upgrade database. In the future, you can manually change the format of these tables to InnoDB, making appropriate changes to the MySQL server that I mentioned above

    In addition in big forums while upgrade process may occur errors like

    Code:
    MySQL server has gone away
    There simply increase the connection timeout in my.cnf or in a script line in 1985 after /core/install/includes/class_upgrade.php

    PHP Code:
    protected function run_query ($ message, $ query, $ ignorable_errors = array ())
             { 
    put
    PHP Code:
    this-> db-> query_write ("SET wait_timeout = 3600"); 
    Hope this will help you when you upgrade

Related Topics

Collapse

Working...