Need help with ERROR 1170
David Coppit
Greetings...
I have a table containing information about some software modules. It has two
primary keys -- "Name" and "Version". When I try to alter a column like this:
alter table 1_18 change Random_Column Random_Column DECIMAL(3,2);
I get the following error:
ERROR 1170: BLOB column 'Name' used in key specification without a key length
"Name" is a TINYTEXT field
"Version" is a DECIMAL(6,4)
"Random_Column" is a DECIMAL(12,10) (I want it to be 3,2)
I've searched everywhere I can think of for info on key lengths, and ERROR
1170. Can anyone lend some expertise?
Thanks,
David
_________________________________________________________________________
David Coppit - Graduate Student da...@coppit.org
The University of Virginia http://coppit.org/
"Yes," said Piglet, "Rabbit has Brain." There was a long silence.
"I suppose," said Pooh, "that that's why he never understands anything."
--
---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-th...@lists.mysql.com
To unsubscribe, send a message to:
<mysql-unsubscribe-myodbc=freebsd.csie...@lists.mysql.com>
sa...@mysql.com
>
> Greetings...
>
> I have a table containing information about some software modules. It has two
> primary keys -- "Name" and "Version". When I try to alter a column like this:
>
> alter table 1_18 change Random_Column Random_Column DECIMAL(3,2);
>
> I get the following error:
>
> ERROR 1170: BLOB column 'Name' used in key specification without a key length
>
> "Name" is a TINYTEXT field
> "Version" is a DECIMAL(6,4)
> "Random_Column" is a DECIMAL(12,10) (I want it to be 3,2)
>
> I've searched everywhere I can think of for info on key lengths, and ERROR
> 1170. Can anyone lend some expertise?
The error is rather confusing - it should say BLOB/TEXT instead of BLOB...
The problem is that MySQL can only reliably index the first N bytes of a
blob/text column, and wants you to say how many you want.
Another problem is that it lets you somehow create a table with an incorrect
specification of a primary key, but it will not let you alter it -- a bug. Could
you repeat your steps in creating that table - I have tried it, and it would not
let me, but you have succeeded somehow, which means that it is possible, and it
should not be...
See if you can drop the primary key, and then alter table table_1_18 add primary
key name(name(20));
--
Sasha Pachev
+------------------------------------------------------------------+
| TcX ____ __ _____ _____ ___ == my...@tcx.se |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sasha Pachev |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sa...@mysql.com |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+------------------------------------------------------------------+
David Coppit
> > ERROR 1170: BLOB column 'Name' used in key specification without a key length
> The problem is that MySQL can only reliably index the first N bytes of a
> blob/text column, and wants you to say how many you want.
>
> Another problem is that it lets you somehow create a table with an incorrect
> specification of a primary key, but it will not let you alter it -- a bug.
> Could you repeat your steps in creating that table - I have tried it, and it
> would not let me, but you have succeeded somehow, which means that it is
> possible, and it should not be...
Okay, if I understand correctly, the table was created even though it should
not have been because I didn't specify a key length for the primary key, which
was a text/blob.
I've been using phpMyAdmin, but I don't think that matters. Basically, I
created a DB without primary keys (oops), then went back and added both the
Name and the Version as primary keys. If I understand you right, mySQL should
have complained when I tried to make Name a primary without a key length.
(Bug!)
> See if you can drop the primary key, and then alter table table_1_18 add
> primary key name(name(20));
I'd rather not drop the primary key "Name", since it contains the names! Also,
I can't just drop that column because I will have non-unique Version number
problems. Will this work?
1) Create a new primary:
> alter table 1_18 add primary key Name2 tinytext(20);
2) Copy column data from Name to Name2:
> <don't know the SQL>
3) Drop the old broken primary:
> alter table 1_18 drop Name;
4) Rename the new primary:
> alter table 1_18 Name2 Name tinytext(20);
Thanks a lot,
David
_________________________________________________________________________
David Coppit - Graduate Student da...@coppit.org
The University of Virginia http://coppit.org/
"Yes," said Piglet, "Rabbit has Brain." There was a long silence.
"I suppose," said Pooh, "that that's why he never understands anything."
sa...@mysql.com
> Okay, if I understand correctly, the table was created even though it should
> not have been because I didn't specify a key length for the primary key, which
> was a text/blob.
yes
>
> I've been using phpMyAdmin, but I don't think that matters. Basically, I
> created a DB without primary keys (oops), then went back and added both the
> Name and the Version as primary keys. If I understand you right, mySQL should
> have complained when I tried to make Name a primary without a key length.
> (Bug!)
Try to remember what you did, and repeat it using the command line client, so
you can have a test case handy
>
> > See if you can drop the primary key, and then alter table table_1_18 add
> > primary key name(name(20));
>
> I'd rather not drop the primary key "Name", since it contains the names! Also,
> I can't just drop that column because I will have non-unique Version number
> problems. Will this work?
dropping the key is not the same as dropping the column (!). You data will still
be there, it will just not be indexed after the drop until you create a new
index.
--
Sasha Pachev
+------------------------------------------------------------------+
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sasha Pachev |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sa...@mysql.com |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+------------------------------------------------------------------+
--
Michael Widenius
Hi!
>>>>> "David" == David Coppit <da...@coppit.org> writes:
David> On Tue, 2 May 2000 sa...@mysql.com wrote:
>> > ERROR 1170: BLOB column 'Name' used in key specification without a key length
>> The problem is that MySQL can only reliably index the first N bytes of a
>> blob/text column, and wants you to say how many you want.
>>
>> Another problem is that it lets you somehow create a table with an incorrect
>> specification of a primary key, but it will not let you alter it -- a bug.
>> Could you repeat your steps in creating that table - I have tried it, and it
>> would not let me, but you have succeeded somehow, which means that it is
>> possible, and it should not be...
David> Okay, if I understand correctly, the table was created even though it should
David> not have been because I didn't specify a key length for the primary key, which
David> was a text/blob.
David> I've been using phpMyAdmin, but I don't think that matters. Basically, I
David> created a DB without primary keys (oops), then went back and added both the
David> Name and the Version as primary keys. If I understand you right, mySQL should
David> have complained when I tried to make Name a primary without a key length.
David> (Bug!)
Any change you can from a log check exactly which queries was issued?
I tried to repeat this, but I didn't succeed:
mysql> create table t1 (name TINYTEXT, version decimal(6,4));
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t1 add primary key (name);
ERROR 1170: BLOB column 'name' used in key specification without a key length
>> See if you can drop the primary key, and then alter table table_1_18 add
>> primary key name(name(20));
David> I'd rather not drop the primary key "Name", since it contains the names! Also,
David> I can't just drop that column because I will have non-unique Version number
David> problems. Will this work?
David> 1) Create a new primary:
>> alter table 1_18 add primary key Name2 tinytext(20);
David> 2) Copy column data from Name to Name2:
>> <don't know the SQL>
David> 3) Drop the old broken primary:
>> alter table 1_18 drop Name;
David> 4) Rename the new primary:
>> alter table 1_18 Name2 Name tinytext(20);
2) can be done as follows:
insert into 1_18 select * from old_table;
Regards,
Monty
David Coppit
> See if you can drop the primary key, and then
> alter table table_1_18 add primary key name(name(20));
Okay, I did:
mysql> alter table 1_18 drop primary key;
Query OK, 327 rows affected (0.18 sec)
Records: 327 Duplicates: 0 Warnings: 0
mysql> alter table 1_18 add primary key Name(Name(20));
Query OK, 327 rows affected (0.46 sec)
Records: 327 Duplicates: 0 Warnings: 0
but when I tried to add my second key, I got this:
mysql> alter table 1_18 add primary key (Version);
ERROR 1170: BLOB column 'Name' used in key specification without a key length
So I then tried adding both keys at the same time:
mysql> alter table 1_18 drop primary key;
Query OK, 327 rows affected (0.18 sec)
Records: 327 Duplicates: 0 Warnings: 0
mysql> alter table 1_18 add primary key (Name(20),Version);
Query OK, 327 rows affected (0.23 sec)
Records: 327 Duplicates: 0 Warnings: 0
mysql> alter table 1_18 change Random_Column Random_Column DECIMAL(3,2);
ERROR 1170: BLOB column 'Name' used in key specification without a key length
Ug. Back to square one. Any suggestions?
Thanks,
David
Michael Widenius
David> On Tue, 2 May 2000 sa...@mysql.com wrote:
>> See if you can drop the primary key, and then
>> alter table table_1_18 add primary key name(name(20));
David> Okay, I did:
mysql> alter table 1_18 drop primary key;
David> Query OK, 327 rows affected (0.18 sec)
David> Records: 327 Duplicates: 0 Warnings: 0
mysql> alter table 1_18 add primary key Name(Name(20));
David> Query OK, 327 rows affected (0.46 sec)
David> Records: 327 Duplicates: 0 Warnings: 0
David> but when I tried to add my second key, I got this:
mysql> alter table 1_18 add primary key (Version);
David> ERROR 1170: BLOB column 'Name' used in key specification without a key length
David> So I then tried adding both keys at the same time:
mysql> alter table 1_18 drop primary key;
David> Query OK, 327 rows affected (0.18 sec)
David> Records: 327 Duplicates: 0 Warnings: 0
mysql> alter table 1_18 add primary key (Name(20),Version);
David> Query OK, 327 rows affected (0.23 sec)
David> Records: 327 Duplicates: 0 Warnings: 0
mysql> alter table 1_18 change Random_Column Random_Column DECIMAL(3,2);
David> ERROR 1170: BLOB column 'Name' used in key specification without a key length
David> Ug. Back to square one. Any suggestions?
Hi!
I tested this with the upcoming MySQL 3.23.15:
mysql> create table t1 (name TINYTEXT not null, version decimal(6,4) not null, random_column decimal(3,2) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t1 add primary key Name(Name(20),version);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 change Random_Column Random_Column DECIMAL(3,2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 drop primary key ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add primary key (Version);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 drop primary key ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add primary key (Name(20),Version);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add primary key (Version);
ERROR 1068: Multiple primary key defined
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
As you see, in this MySQL version this works perfectly. I hope you
can try to do an upgrade when we release this!
Regards,
Monty
David Coppit
> I tested this with the upcoming MySQL 3.23.15:
>
> mysql> create table t1 (name TINYTEXT not null, version decimal(6,4) not null, random_column decimal(3,2) not null);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> alter table t1 add primary key Name(Name(20),version);
> Query OK, 0 rows affected (0.00 sec)
> Records: 0 Duplicates: 0 Warnings: 0
>
> mysql> alter table t1 change Random_Column Random_Column DECIMAL(3,2);
> Query OK, 0 rows affected (0.01 sec)
> Records: 0 Duplicates: 0 Warnings: 0
Yep. My version croaks right here. For the record, it's version
3.23.2-alpha-log, according to the mysql blurb at startup.
> As you see, in this MySQL version this works perfectly. I hope you
> can try to do an upgrade when we release this!
Will do. Am I out of luck until then?
Thanks,
David
_________________________________________________________________________
David Coppit - Graduate Student da...@coppit.org
The University of Virginia http://coppit.org/
"Yes," said Piglet, "Rabbit has Brain." There was a long silence.
"I suppose," said Pooh, "that that's why he never understands anything."
David Coppit
> > As you see, in this MySQL version this works perfectly. I hope you
> > can try to do an upgrade when we release this!
>
> Will do. Am I out of luck until then?
Ah! I figured out that I could just remove the primary keys, do any table
alters, and then add them back in.
Regards,