Re: [WWdevel] database upgrade problem

[P. G. L. <gl...@um...>]

Hi John,

I can't see anywhere in the WeBWorK code that the key length is defined. 
However, if the database has some funny definition it would throw an error 
whenever you bumped into it.  The only place I can see a length of 500 is 
in the location_addresses table, so I'd be tempted to try and make sure 
that there isn't still a residual error with that.  My solution would be 
the "heavy club" method, of dropping the tables I think might be offending 
and then recreating them, e.g.,
    mysql> use webwork;
    mysql> drop table locations;
    mysql> drop table location_addresses;
    mysql> create table locations (location_id tinyblob not null,
 	> description text, primary_key (location_id(255)));
    mysql> create table location_addresses ((location_id tinyblob not null,
 	> ip_mask tinyblob not null, primary key (location_id(255),
 	> ip_mask(255)));

Gavin

-- 
P Gavin LaRose, PhD  |  gl...@um... | 734.764.6454  |  ...you have
Program Manager, Instructional Technology |   to respect someone who can
Mathematics Dept,  University of Michigan | spell  Tuesday, even if they
http://www.math.lsa.umich.edu/~glarose/   | can't spell it right. -Milne

On 2007-07-17 [11:45] John Jones wrote:

> Hi,
>
> I tried the change below and have a partially working system.  I still get 
> errors in the course, so I decided to create a new course.  When I do that 
> (using the command line utility), I get an error
>
> DBD::mysql::db do failed: Specified key was too long. Max key length is 500 
> at /opt/devel/webwork-modperl/lib/WeBWorK/DB/Schema/NewSQL/Std.pm line 111.
>
> Are these key lengths specified inside webwork (so that I can reduce them for 
> this system)?
>
> John
>
>
> P. Gavin LaRose wrote:
>> Hi John,
>> 
>> I'm running MySQL 4.1.20 on my servers, and think the script ran fine 
>> there.  The offending line is
>>     $dbh->do("CREATE TABLE locations (location_id TINYBLOB NOT NULL,
>>  	     description TEXT, PRIMARY KEY (location_id(1000)))");
>> My bet from the error message is that the assignment of a prefix of 1000 
>> bytes is causing the error; I wonder if the implementation of TINYBLOB is 
>> having trouble with the 1000 byte value on your machine, or in MySQL 4.0.
>> 
>> In fact, the MySQL 4.0/4.1 documentation indicates that "Before MySQL 
>> 4.1.2, the limit is 255 bytes for all tables."  So that sounds like it's 
>> likely to be the problem.  If you change the prefix value to 255 it should 
>> work for you, and we might want to document somewhere that the assumption 
>> is that users are running MySQL 4.1.2 or better (or change the 1000 to 
>> 255).
>> 
>> Gavin
>>
>> 
>
>
>
>