Bug Beta8: Error when trying to import xml on install

[bzcomputers]

Getting this error when trying to import xml for beta8:

Database error in vBulletin 4.2.0:

Invalid SQL:

CREATE TABLE IF NOT EXISTS `dbtech_dbseo_urlhistory` (
`setting` VARCHAR(100) NOT NULL DEFAULT '',
`regexpformat` VARCHAR(256) NOT NULL DEFAULT '',
`rawformat` VARCHAR(256) NOT NULL DEFAULT '',
PRIMARY KEY (`setting`, `regexpformat`, `rawformat`)
) ENGINE=MyISAM;

MySQL Error : Specified key was too long; max key length is 1000 bytes
Error Number : 1071
Request Date : Monday, December 9th 2013 @ 08:24:46 PM
Error Date : Monday, December 9th 2013 @ 08:24:50 PM
Script : http://www.cruisin.me/forum/xxx/plugin.php?do=productimport
Referrer : http://www.cruisin.me/forum/xxx/plugin.php?do=productadd
IP Address : xxx.xxx.xxx.xx
Username : Greg
Classname : vB_Database
MySQL Version : 5.5.32-cll

 

[Fillip H.]

Is your database UTF-8?

 

[bzcomputers]

yes

 

[Fillip H.]

I made a couple updates, can you re-download and re-upload the files, then try the re-import?

 

[bzcomputers]

Getting same error after redownload/upload/import

 

[Fillip H.]

Took care of it on your site, hopefully 100 characters is enough. Thanks

 

[bzcomputers]

Thanks. While you were fixing it I came across this explanation:

This is solely a MySQL issue -

MySQL has different engines - MyISAM, InnoDB, Memory...

MySQL has different limits on the amount of space you can use to define indexes on column(s) - for MyISAM it's 1,000 bytes; it's 767 for InnoDB. And the data type of those columns matters - for VARCHAR, it's 3x so an index on a VARCHAR(100) will take 300 of those bytes (because 100 characters * 3 = 300).

To accommodate some indexing when you hit the ceiling value, you can define the index with regard to portions of the column data type:

CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))
Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

Something new to me.

 

[Fillip H.]

Yeah, the thing is I use the Primary Key in order to ensure only one entry from each combination is inserted. It should be fine, if not I'll come up with a less efficient way of storing it in a future version

 

[Spadman]

Took care of it on your site, hopefully 100 characters is enough. Thanks

Would you mind sharing the solution? I am getting the same error (1071) for 'dbtech_dbseo_urlhistory' when attempting to install SEO.

 

[Fillip H.]

This thread is almost exactly one year old, I hardly remember the solution

Reading about it, I would imagine the solution is to change the number of characters for those SQL columns in the install file to 100.