create table error

create table error

Post by Daniel Ada » Sun, 25 Mar 2001 09:37:49



Can someone please tell me what the problem is below. All I am trying to
do is pipe a backup created with mysqldump into mysql and it gives me
this error:

ERROR 1071 at line 1291: Specified key was too long. Max key length is
500

Here is the query where the error is:

CREATE TABLE list (
  list_id mediumint(8) unsigned NOT NULL auto_increment,
  name varchar(255) DEFAULT '' NOT NULL,
  description varchar(255),
  email varchar(100) DEFAULT '' NOT NULL,
  message_count mediumint(8) unsigned DEFAULT '0' NOT NULL,
  category_id mediumint(8) unsigned DEFAULT '0' NOT NULL,
  type tinyint(3) unsigned DEFAULT '0' NOT NULL,
  day_count smallint(5) unsigned DEFAULT '0' NOT NULL,
  num_days mediumint(8) unsigned DEFAULT '0' NOT NULL,
  day_ave smallint(5) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (list_id),
  KEY type (type),
  KEY category_id (category_id),
  KEY name (name,description)
)

Thanks you in advance.
        - Dan

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

 
 
 

create table error

Post by Thalis A. Kalfigopoulo » Sun, 25 Mar 2001 11:33:29



> Can someone please tell me what the problem is below. All I am trying to
> do is pipe a backup created with mysqldump into mysql and it gives me
> this error:

> ERROR 1071 at line 1291: Specified key was too long. Max key length is
> 500

It is excactly what it says. Mysql cannot create keys whith length greater than 500 bytes. In your case name+description=255+255=510 bytes.
Try changing the last line to:
KEY name (name(250),description(250))

It'll probably work, but still it'd be better if you truncated the 2 fields even more. For example it is highly unlikely you'll need more that the first 15 characters to discriminate between 2 names.

regards,
thalis

> Here is the query where the error is:

> CREATE TABLE list (
>   list_id mediumint(8) unsigned NOT NULL auto_increment,
>   name varchar(255) DEFAULT '' NOT NULL,
>   description varchar(255),
>   email varchar(100) DEFAULT '' NOT NULL,
>   message_count mediumint(8) unsigned DEFAULT '0' NOT NULL,
>   category_id mediumint(8) unsigned DEFAULT '0' NOT NULL,
>   type tinyint(3) unsigned DEFAULT '0' NOT NULL,
>   day_count smallint(5) unsigned DEFAULT '0' NOT NULL,
>   num_days mediumint(8) unsigned DEFAULT '0' NOT NULL,
>   day_ave smallint(5) unsigned DEFAULT '0' NOT NULL,
>   PRIMARY KEY (list_id),
>   KEY type (type),
>   KEY category_id (category_id),
>   KEY name (name,description)
> )

> Thanks you in advance.
>    - Dan

> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)



> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

 
 
 

1. float in PROCEDURE ANALYSE() / misleading CREATE TABLE error

Hi,

I think there is

1) a problem with FLOAT recommendations in PROCEDURE ANALYSE
2) a minor problem with CREATE TABLE(f FLOAT(<negative_value>,...))

Please correct me if I'm wrong.

With MySQL 4.0.17, the query

SELECT * FROM my_table PROCEDURE ANALYSE();

gives me the following result for a DOUBLE NOT NULL column xxx:

-- 8<
              Field_name: xxx
               Min_value: 0.002
               Max_value: 2800
              Min_length: 1
              Max_length: 7
        Empties_or_zeros: 45
                   Nulls: 0
Avg_value_or_avg_length: 63.10753644525
                     Std: 81.175363704985
       Optimal_fieldtype: FLOAT(-25,1) NOT NULL
-- 8<

The suggested field type FLOAT(-25,1) seems not to be a valid column
type. Also, 1 decimal would be a bad choice when there is a value 0.002.

mysql> create table test.foo (f float(-25,1) not null);
ERROR 1074: Too big column length for column 'f' (max = 255). Use BLOB
instead

This happens for many (or all) DOUBLE columns, so PROCEDURE ANALYSE's
advice is probably broken here.

The CREATE TABLE statement gives the misleading error message "Too big"
while the value is actually "too small". Probably -25 is read into an
'unsigned int' or similar internally and then interpreted as a very
large integer like 2^32-25. At sql/sql_yacc.yy:1099, precision is
defined as '(' NUM ',' NUM ')' and according to sql_lex.cc:345, NUM can
be a negative value. Maybe in one of these locations an error could be
reported if NUM is negative, but that's just a guess.

Hans-Peter

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

2. Keystroke aliases don't echo to screen

3. Create Table error with MySQL version 3.23.39-max-nt, Access 2000, and

4. hALO ONLINE Xbox connector

5. Create Table Error 1071

6. How to hilit my mail buffer?

7. Create Table error convert from DB2 to MySQL with Java?

8. Klondike AGA problems

9. InnoDB create table error 150

10. MySQL Create Table Error

11. Create Table Error 1071

12. mysql create table error

13. Create table error