# Generating from COMPRESSED table with mysql 5.1 not possible.



## parcival (Oct 28, 2010)

Hi,
I use FreeBSD 8.1.
Has installed mysql 5.1 successfully, however if there is a problem with COMPRESSED table.
This SQL String isn't successful.


```
ALTER TABLE `events` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
ALTER TABLE `history` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
```

The Table format is always COMPACT.
What is missing here? InnoDB plugin? Some variables in my.cf ?

Thank you for every help.

Stefan


----------



## SirDice (Oct 28, 2010)

Try doing a 
	
	



```
FLUSH TABLE events;
```
 and check again.


----------



## parcival (Oct 28, 2010)

Hi SirDice,
it's the same:


```
Zeilenstatistik Angaben 	Wert
Format 	        Compact
Optionen 	row_format=COMPRESSED KEY_BLOCK_SIZE=4
Kollation 	latin1_swedish_ci
Erzeugt am 	28. Oktober 2010 um 09:54
```

Interestingly enough the "Option" is OK, however not the format.
Which switch / parameter is missing here?
With the version 5.5 I did not have a problem, however are still beta.

Possibly an idea?


----------



## SirDice (Oct 28, 2010)

This looks similar: http://bugs.mysql.com/bug.php?id=21094


----------



## parcival (Oct 28, 2010)

OK,
That is a good hint.
However I cannot likewise select COMPRESSED in phpmyadmin, it is missing here (in 5.5 OK).
Do I have to load the InnoDB plugin, if how?


----------



## SirDice (Oct 28, 2010)

It seems the *ALTER TABLE ... ROW_FORMAT=COMPRESSED* is allowed but doesn't work.

It's suggested to use myisampackfor this.

Another way would to be create a second table with the correct ROW_FORMAT and *SELECT * FROM ... INTO ...* to copy the data.


----------



## parcival (Oct 28, 2010)

OK, sorry
myisampack for InnoDB Tables ?


----------



## SirDice (Oct 28, 2010)

parcival said:
			
		

> myisampack for InnoDB Tables ?


Obviously, that's for MyISAM tables.


----------



## parcival (Oct 28, 2010)

SireDice,
The solution found.
Thanks for your assistance. These parameters are necessary.


```
[mysqld]
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;
	innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so
#	innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;
#	innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

innodb_file_per_table=1
innodb_file_format=barracuda
```

Stefan


----------

