Bug #2977 Optimize table crashes mysqld!
Submitted: 26 Feb 2004 12:13 Modified: 27 Feb 2004 6:03
Reporter: Arto Hakola Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.17/4.0.18 OS:Windows (Windows 2000 Server)
Assigned to: CPU Architecture:Any

[26 Feb 2004 12:13] Arto Hakola
Description:
When you try to start "optimize table..." command from the MySqlCC or from the command line client, Mysqld daemon dies?

How to repeat:
start mysql commandline utility and select a database and enter "optimize table..." command.
Voila, your database should have been killed!

Suggested fix:
Make this work asap! I need this feature now!
[26 Feb 2004 12:23] Arto Hakola
Also repair command does the same thing?
Database is in the 3.23.xx format. Does this make the db engine fail?
Otherwise db is working but these management commands doesn't work at all?
[26 Feb 2004 12:24] MySQL Verification Team
I wasn't able to repeat:

mysql> optimize table vevents;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| test.vevents | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.09 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.18-max-debug |
+------------------+
1 row in set (0.01 sec)

Could you please provide a test case ?
[27 Feb 2004 5:23] Arto Hakola
What kind of test case you mean?

Here's my current server configuration:

[WinMySQLadmin]
Server=D:/mysql/bin/mysqld-max-nt.exe

# The MySQL server
[mysqld]
basedir=d:/mysql
datadir=d:/mysql/data
port=3306
skip-locking
skip-innodb
set-variable	= query_cache_limit=48M
set-variable	= query_cache_size=64M
set-variable	= myisam_repair_threads=2
set-variable	= thread_cache=8
# Try number of CPU's*2 for thread_concurrency
#set-variable	= thread_concurrency=4
server-id	= 1
set-variable	= key_buffer=84M
set-variable	= max_allowed_packet=64M
set-variable	= table_cache=100
set-variable	= sort_buffer=4M
set-variable	= net_buffer_length=8K

[mysqldump]
quick
set-variable	= max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable	= key_buffer=128M
set-variable	= sort_buffer=128M
set-variable	= read_buffer=2M
set-variable	= write_buffer=2M

[myisamchk]
set-variable	= key_buffer=128M
set-variable	= sort_buffer=128M
set-variable	= read_buffer=2M
set-variable	= write_buffer=2M

[mysqlhotcopy]
interactive-timeout
[27 Feb 2004 6:03] MySQL Verification Team
I meant if you have a particular table, columns type, etc
because with the simple optimize table ... command I
wasn't able to repeat either with 3.23.58 tables or
4.0.18 tables.
[27 Feb 2004 6:09] Arto Hakola
Here's a table definition I try to optimize:

CREATE TABLE `test` (
  `lic_key` varchar(32) NOT NULL default '',
  `key_status` int(11) default '0',
  `custno` varchar(20) default '<?>',
  `key_type` int(11) NOT NULL default '0',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `activated` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`lic_key`),
  KEY `pex_license_key_pool_idx1` (`key_status`,`key_type`,`custno`)
) TYPE=MyISAM; 

Table has ~3 000 000 rows.