librelist archives

« back to archive

"Mysql::Error: Specified key was too long; max key length is 1000 bytes" error when database charset is UTF8

"Mysql::Error: Specified key was too long; max key length is 1000 bytes" error when database charset is UTF8

From:
Arun Thampi
Date:
2010-01-28 @ 08:37
Hi guys - Been using Friendly for the last couple of weeks and really liking
it, but hit a roadblock yesterday which will delay us pushing our Friendly
app to production. Basically, Friendly.create_tables! fails with this
error "Mysql::Error:
Specified key was too long; max key length is 1000 bytes" when creating
indexes for more than one 'text' field is involved, and the charset is set
to UTF-8.
 This is because MySQL has an arbitrary index-length of 1000 bytes, so for
two fields which are varchar(255), the worst-case length calculated by MySQL
for the index will be (255 + 255) * 3 ( > 1000). (3-bytes since it's UTF-8)
The bug-listing is here: http://bugs.mysql.com/bug.php?id=4541

I've tried to come up with a work-around by removing the "primary_key
table.fields.flatten + [:id]" generator in TableCreator#create_index_table
and then creating the index by hand by doing something like this:
db.execute("ALTER TABLE #{table.table_name} ADD PRIMARY KEY(permalink(164),
location(164), id);") but it feels very hackish.

Are there other ways to do this, or any other workaround? Appreciate any
pointers. I've raised an issue on GitHub.

Thanks in advance.

Cheers,
Arun

-- 
It's better to be a pirate than join the Navy - Steve Jobs
http://mclov.in

Re: "Mysql::Error: Specified key was too long; max key length is 1000 bytes" error when database charset is UTF8

From:
Arun Thampi
Date:
2010-01-28 @ 10:39
Just an update - I've managed to work around this for now, by defining one
of my indexed fields as Integer. Will be awesome to be able to index on two
string fields in UTF-8 for the future. Will try to dig up more on an elegant
solution.

Cheers,
Arun

On Thu, Jan 28, 2010 at 4:37 PM, Arun Thampi <arun.thampi@gmail.com> wrote:

> Hi guys - Been using Friendly for the last couple of weeks and really
> liking it, but hit a roadblock yesterday which will delay us pushing our
> Friendly app to production. Basically, Friendly.create_tables! fails with
> this error "Mysql::Error: Specified key was too long; max key length is
> 1000 bytes" when creating indexes for more than one 'text' field is
> involved, and the charset is set to UTF-8.
>  This is because MySQL has an arbitrary index-length of 1000 bytes, so for
> two fields which are varchar(255), the worst-case length calculated by MySQL
> for the index will be (255 + 255) * 3 ( > 1000). (3-bytes since it's UTF-8)
> The bug-listing is here: http://bugs.mysql.com/bug.php?id=4541
>
> I've tried to come up with a work-around by removing the "primary_key
> table.fields.flatten + [:id]" generator in TableCreator#create_index_table
> and then creating the index by hand by doing something like this:
> db.execute("ALTER TABLE #{table.table_name} ADD PRIMARY KEY(permalink(164),
> location(164), id);") but it feels very hackish.
>
> Are there other ways to do this, or any other workaround? Appreciate any
> pointers. I've raised an issue on GitHub.
>
> Thanks in advance.
>
> Cheers,
> Arun
>
> --
> It's better to be a pirate than join the Navy - Steve Jobs
> http://mclov.in
>



-- 
It's better to be a pirate than join the Navy - Steve Jobs
http://mclov.in

Re: [friendly] Re: "Mysql::Error: Specified key was too long; max key length is 1000 bytes" error when database charset is UTF8

From:
James Golick
Date:
2010-01-28 @ 16:36
I'm not sure I'm following. It's not possible to index two varchar fields at
all with MySQL? I'm 99% sure I've done it in the past...

On Thu, Jan 28, 2010 at 2:39 AM, Arun Thampi <arun.thampi@gmail.com> wrote:

> Just an update - I've managed to work around this for now, by defining one
> of my indexed fields as Integer. Will be awesome to be able to index on two
> string fields in UTF-8 for the future. Will try to dig up more on an elegant
> solution.
>
> Cheers,
> Arun
>
>
> On Thu, Jan 28, 2010 at 4:37 PM, Arun Thampi <arun.thampi@gmail.com>wrote:
>
>> Hi guys - Been using Friendly for the last couple of weeks and really
>> liking it, but hit a roadblock yesterday which will delay us pushing our
>> Friendly app to production. Basically, Friendly.create_tables! fails with
>> this error "Mysql::Error: Specified key was too long; max key length is
>> 1000 bytes" when creating indexes for more than one 'text' field is
>> involved, and the charset is set to UTF-8.
>>  This is because MySQL has an arbitrary index-length of 1000 bytes, so
>> for two fields which are varchar(255), the worst-case length calculated by
>> MySQL for the index will be (255 + 255) * 3 ( > 1000). (3-bytes since it's
>> UTF-8) The bug-listing is here: http://bugs.mysql.com/bug.php?id=4541
>>
>> I've tried to come up with a work-around by removing the "primary_key
>> table.fields.flatten + [:id]" generator in TableCreator#create_index_table
>> and then creating the index by hand by doing something like this:
>> db.execute("ALTER TABLE #{table.table_name} ADD PRIMARY KEY(permalink(164),
>> location(164), id);") but it feels very hackish.
>>
>> Are there other ways to do this, or any other workaround? Appreciate any
>> pointers. I've raised an issue on GitHub.
>>
>> Thanks in advance.
>>
>> Cheers,
>> Arun
>>
>> --
>> It's better to be a pirate than join the Navy - Steve Jobs
>> http://mclov.in
>>
>
>
>
> --
> It's better to be a pirate than join the Navy - Steve Jobs
> http://mclov.in
>

Re: [friendly] Re: "Mysql::Error: Specified key was too long; max key length is 1000 bytes" error when database charset is UTF8

From:
Arun Thampi
Date:
2010-01-29 @ 02:45
Hi James - Yes it's not possible to index two varchar fields (each of length
255) in MySQL when the charset is set to UTF-8.

To reproduce:

1. Set the charset of your database to UTF8.

alter database properties character set utf8;

2. Declare indexes on two text fields in a  Friendly document:

class Property
  include Friendly::Document

  attribute :name
  attribute :location

  indexes :name, :location
end

3. Property.create_tables! will fail because in UTF-8, the space allocated
for each varchar field is 255 * 3. So for two varchar fields, the space
allocated will exceed 1000 bytes and thus fail (while setting the primary
key).


Cheers,
Arun

On Fri, Jan 29, 2010 at 12:36 AM, James Golick <jamesgolick@gmail.com>wrote:

> I'm not sure I'm following. It's not possible to index two varchar fields
> at all with MySQL? I'm 99% sure I've done it in the past...
>
>
> On Thu, Jan 28, 2010 at 2:39 AM, Arun Thampi <arun.thampi@gmail.com>wrote:
>
>> Just an update - I've managed to work around this for now, by defining one
>> of my indexed fields as Integer. Will be awesome to be able to index on two
>> string fields in UTF-8 for the future. Will try to dig up more on an elegant
>> solution.
>>
>> Cheers,
>> Arun
>>
>>
>> On Thu, Jan 28, 2010 at 4:37 PM, Arun Thampi <arun.thampi@gmail.com>wrote:
>>
>>> Hi guys - Been using Friendly for the last couple of weeks and really
>>> liking it, but hit a roadblock yesterday which will delay us pushing our
>>> Friendly app to production. Basically, Friendly.create_tables! fails with
>>> this error "Mysql::Error: Specified key was too long; max key length is
>>> 1000 bytes" when creating indexes for more than one 'text' field is
>>> involved, and the charset is set to UTF-8.
>>>  This is because MySQL has an arbitrary index-length of 1000 bytes, so
>>> for two fields which are varchar(255), the worst-case length calculated by
>>> MySQL for the index will be (255 + 255) * 3 ( > 1000). (3-bytes since it's
>>> UTF-8) The bug-listing is here: http://bugs.mysql.com/bug.php?id=4541
>>>
>>> I've tried to come up with a work-around by removing the "primary_key
>>> table.fields.flatten + [:id]" generator in TableCreator#create_index_table
>>> and then creating the index by hand by doing something like this:
>>> db.execute("ALTER TABLE #{table.table_name} ADD PRIMARY KEY(permalink(164),
>>> location(164), id);") but it feels very hackish.
>>>
>>> Are there other ways to do this, or any other workaround? Appreciate any
>>> pointers. I've raised an issue on GitHub.
>>>
>>> Thanks in advance.
>>>
>>> Cheers,
>>> Arun
>>>
>>> --
>>> It's better to be a pirate than join the Navy - Steve Jobs
>>> http://mclov.in
>>>
>>
>>
>>
>> --
>> It's better to be a pirate than join the Navy - Steve Jobs
>> http://mclov.in
>>
>
>


-- 
It's better to be a pirate than join the Navy - Steve Jobs
http://mclov.in

Re: [friendly] Re: "Mysql::Error: Specified key was too long; max key length is 1000 bytes" error when database charset is UTF8

From:
James Golick
Date:
2010-01-31 @ 04:49
Ah, I just realized what the problem is. It's not that it isn't possible to
*index* those fields. It's not possible to put them in a primary key. In its
index tables, Friendly uses a composite primary key that consists of all the
index fields, ending in id.

A possible work around would be to hack the table creator (or just create
your tables manually) to make the primary key some other field, like an
auto-increment that you just ignore.

- James

On Thu, Jan 28, 2010 at 6:45 PM, Arun Thampi <arun.thampi@gmail.com> wrote:

> Hi James - Yes it's not possible to index two varchar fields (each of
> length 255) in MySQL when the charset is set to UTF-8.
>
> To reproduce:
>
> 1. Set the charset of your database to UTF8.
>
> alter database properties character set utf8;
>
> 2. Declare indexes on two text fields in a  Friendly document:
>
> class Property
>   include Friendly::Document
>
>   attribute :name
>   attribute :location
>
>   indexes :name, :location
> end
>
> 3. Property.create_tables! will fail because in UTF-8, the space allocated
> for each varchar field is 255 * 3. So for two varchar fields, the space
> allocated will exceed 1000 bytes and thus fail (while setting the primary
> key).
>
>
> Cheers,
> Arun
>
>
> On Fri, Jan 29, 2010 at 12:36 AM, James Golick <jamesgolick@gmail.com>wrote:
>
>> I'm not sure I'm following. It's not possible to index two varchar fields
>> at all with MySQL? I'm 99% sure I've done it in the past...
>>
>>
>> On Thu, Jan 28, 2010 at 2:39 AM, Arun Thampi <arun.thampi@gmail.com>wrote:
>>
>>> Just an update - I've managed to work around this for now, by defining
>>> one of my indexed fields as Integer. Will be awesome to be able to index on
>>> two string fields in UTF-8 for the future. Will try to dig up more on an
>>> elegant solution.
>>>
>>> Cheers,
>>> Arun
>>>
>>>
>>> On Thu, Jan 28, 2010 at 4:37 PM, Arun Thampi <arun.thampi@gmail.com>wrote:
>>>
>>>> Hi guys - Been using Friendly for the last couple of weeks and really
>>>> liking it, but hit a roadblock yesterday which will delay us pushing our
>>>> Friendly app to production. Basically, Friendly.create_tables! fails with
>>>> this error "Mysql::Error: Specified key was too long; max key length is
>>>> 1000 bytes" when creating indexes for more than one 'text' field is
>>>> involved, and the charset is set to UTF-8.
>>>>  This is because MySQL has an arbitrary index-length of 1000 bytes, so
>>>> for two fields which are varchar(255), the worst-case length calculated by
>>>> MySQL for the index will be (255 + 255) * 3 ( > 1000). (3-bytes since it's
>>>> UTF-8) The bug-listing is here: http://bugs.mysql.com/bug.php?id=4541
>>>>
>>>> I've tried to come up with a work-around by removing the "primary_key
>>>> table.fields.flatten + [:id]" generator in TableCreator#create_index_table
>>>> and then creating the index by hand by doing something like this:
>>>> db.execute("ALTER TABLE #{table.table_name} ADD PRIMARY KEY(permalink(164),
>>>> location(164), id);") but it feels very hackish.
>>>>
>>>> Are there other ways to do this, or any other workaround? Appreciate any
>>>> pointers. I've raised an issue on GitHub.
>>>>
>>>> Thanks in advance.
>>>>
>>>> Cheers,
>>>> Arun
>>>>
>>>> --
>>>> It's better to be a pirate than join the Navy - Steve Jobs
>>>> http://mclov.in
>>>>
>>>
>>>
>>>
>>> --
>>> It's better to be a pirate than join the Navy - Steve Jobs
>>> http://mclov.in
>>>
>>
>>
>
>
> --
> It's better to be a pirate than join the Navy - Steve Jobs
> http://mclov.in
>