ERROR 1030: Got error 127 from table handler

[jw]

>Description:
        I got the above error while trying to change a table definition.
I was originally executing commands from a script using `mysql DB < script`
but got the same problem when executing the SQL statement on the mysql client
command line.

>How-To-Repeat:

This was the definition of the table I wanted to change:

CREATE TABLE lk_category_sub (
  id int(6) DEFAULT '0' NOT NULL,
  category_id int(6) DEFAULT '0' NOT NULL,
  description varchar(50) DEFAULT '' NOT NULL,
  node_code char(2),
  node_index int(6) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY category_id (category_id),
  UNIQUE description (description)
)/*! type=MyISAM pack_keys=1 */;

This was the session output including the commands I was trying to run to
change the table:

mysql> ALTER TABLE lk_category_sub
    ->   MODIFY id smallint(6) AUTO_INCREMENT NOT NULL;
ERROR 1030: Got error 127 from table handler
mysql> ALTER TABLE lk_category_sub
    ->   MODIFY id smallint(6)  NOT NULL;
ERROR 1030: Got error 127 from table handler

I tried this many times on the cmd line and from the script, with no luck,
but later on, having had to restart mysqld I discovered that the same SQL
ran fine.

I have since dropped the table and recreated it with the original definition,
and run the ALTER TABLE command again, and repeated this several times with
no problems.

The only change was that the server had been stopped with `mysqladmin shutdown`
and restarted with `safe_mysqld -O ... -O ... -O ... &` where the -O params
were the same before and after restarting.


>Fix:
The fix seems to have been to restart the server.


>Submitter-Id:	jw
>Originator:	jw
>Organization:  
 
>MySQL support: none
>Synopsis:	Unhelpful error message for valid SQL, works later
>Severity:	non-critical
>Priority:	low
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.23.15-alpha (Source distribution)
>Server: /home/mysql/bin/mysqladmin  Ver 8.3 Distrib 3.23.15-alpha, for
> unknown-freebsdelf3.4 on i386
TCX Datakonsult AB, by Monty

Server version		3.23.15-alpha
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			2 days 20 hours 46 min 49 sec

Threads: 7  Questions: 27301  Slow queries: 47  Opens: 675  Flush tables: 1  Open tables:
130
>Environment:

N.B. the gcc version shown below is not the one used to compile the server,
version 2.8.1 was used.

System: FreeBSD marge.mintel.co.uk 3.4-RELEASE FreeBSD 3.4-RELEASE #2: Wed Apr 19 14:40:41
BST 2000     jason@stripped:/usr/src/sys/compile/MARGE  i386


Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: gcc version 2.7.2.3
Compilation info: CC='gcc28'  CFLAGS=''  CXX='gcc28'  CXXFLAGS=''  LDFLAGS=''
LIBC: 
-r--r--r--  1 root  wheel  1051788 Dec 20  1999 /usr/lib/libc.a
lrwxrwxrwx  1 root  wheel  9 Feb 15 14:26 /usr/lib/libc.so -> libc.so.3
-r--r--r--  1 root  wheel  520040 Dec 20  1999 /usr/lib/libc.so.3
Configure command: ./configure  --prefix=/usr/local/mysql --with-mysql-user=mysql
--with-named-thread-libs=-pthread --without-docs --with-mysqld-ldflags=-all-static
--without-bench
Perl: This is perl, version 5.005_03 built for i386-freebsd