Re: [Phpwiki-talk] column type BLOB not supported for HEAP/MEMORY type tables of MyS QL

[Reini U. <ru...@x-...>]

Scheider, Hendrik schrieb:
> I noticed that the session table contains a column type not supported by
> MySQL when choosing the table type HEAP or MEMORY as suggested in the
> mysql.sql schema.
> 
> What shall I set the column type to instead?

http://dev.mysql.com/doc/mysql/en/memory-storage-engine.html

we allow only max 4000 chars, so varchar(4000) should be enough.
(session limitation, see also max_allowed_packet)
and mysql doesn't support that large heap tables to my knowledge yet. 
max char(255).
I never tested heap tables for sessions.

max heap table size =  16777216

DROP TABLE `session` IF EXIST;
CREATE TABLE `session` (
   `sess_id` char(32) character set latin1 NOT NULL default '',
   `sess_data` char(4000) collate latin1_bin,
   `sess_date` int(10) unsigned NOT NULL default '0',
   `sess_ip` char(15) character set latin1 NOT NULL default '',
   PRIMARY KEY  (`sess_id`),
   KEY `sess_date` (`sess_date`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

=> The used table type doesn't support BLOB/TEXT columns

So the next best version would be a TEMPORARY table, which are kept in 
memory until tmp_table_size is exceeded and then flushed to disc.
But for TEMPORARY table you'd need persistent connections, which will 
not work fine on overloaded servers.
And a db hook in the connection init to create it.

DROP TABLE `session` IF EXISTS;
CREATE TEMPORARY TABLE `session` (
   `sess_id` char(32) character set latin1 NOT NULL default '',
   `sess_data` BLOB collate latin1_bin,
   `sess_date` int(10) unsigned NOT NULL default '0',
   `sess_ip` char(15) character set latin1 NOT NULL default '',
   PRIMARY KEY  (`sess_id`),
   KEY `sess_date` (`sess_date`)
) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

So MyIsam/InnoDB look like the best mysql solution.
Or use one of the specialized php session modules.
-- 
Reini Urban
http://phpwiki.org/