Script doesn't observe sub-part indexes

[scottharman]

When creating the patch script, we have a number of unique indexes based on the first 255 characters in a table, but the script doesn't include those values

ALTER TABLE dbservers MODIFY COLUMN serverid int(10) unsigned NOT NULL FIRST, MODIFY COLUMN zonenumber int(10) unsigned NOT NULL AFTER serverid, ADD UNIQUE INDEX u_dnsname (dnsname(255)) USING BTREE, ADD INDEX f_zonenumber (zonenumber) USING BTREE, DROP INDEX dnsname, DROP INDEX zonenumber;

• i've manually added the index length to dnsname above (which is tinytext) else the following error is thrown by mysql ERROR 1170 (42000): BLOB/TEXT column 'dnsname' used in key specification without a key length

[mmatuson]

It should honor the sub-part of the index. See https://github.com/mmatuson/SchemaObject/blob/master/schemaobject/index.py#L147

What version of MySQL are you using and what does the create table statement look like for this table?

[scottharman]

MySQL version is 5.1.47

Create table is below: mysql> show create table dbservers \G *** 1. row *** Table: dbservers Create Table: CREATE TABLE dbservers ( serverid int(10) unsigned NOT NULL, zonenumber int(10) unsigned NOT NULL, dnsname tinytext NOT NULL, PRIMARY KEY (serverid), UNIQUE KEY u_dnsname (dnsname(255)), KEY f_zonenumber (zonenumber) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

Cheers, Scott

On Sat, Apr 9, 2011 at 3:11 AM, mmatuson < reply@reply.github.com>wrote:

It should honor the sub-part of the index. See https://github.com/mmatuson/SchemaObject/blob/master/schemaobject/index.py#L147

What version of MySQL are you using and what does the create table statement look like for this table?

Reply to this email directly or view it on GitHub: https://github.com/mmatuson/SchemaSync/issues/11#comment_974174

[mmatuson]

OK, this issue is in the SchemaObject library and happening because the formatter was expecting a number, but a string was always provided. I'll fix it and post here when it has been committed.

[mmatuson]

OK, the fix is in SchemaObject 0.5.4. It will be available with SchemaSync in the next release but until then you can checkout a copy of the development version

To upgrade your copy of SchemaObject, remove the old one and install the version you just checked out using the instructions in the README.

Let me know if you have any issues.

[scottharman]

Downloaded that now - I'll let you know how it goes Many thanks

On Sat, Apr 9, 2011 at 1:12 PM, mmatuson < reply@reply.github.com>wrote:

OK, the fix is in SchemaObject 0.5.4. It will be available with SchemaSync in the next release but until then you can checkout a copy of the development version

To upgrade your copy of SchemaObject, remove the old one and install the version you just checked out using the instructions in the README.

Let me know if you have any issues.

Reply to this email directly or view it on GitHub: https://github.com/mmatuson/SchemaSync/issues/11#comment_976469