Hi,
I try to convert a varchar to a char, but it doesn't seems to work.
show create table sessions;
CREATE TABLE `sessions` (
`id` varchar(32) NT NULL default '',
`user_id` int(6) NT NULL default '0',
`ip` varchar(8) NT NULL default '0',
`lastseen` timestamp NT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`expire` timestamp NT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `user_id2` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE sessions MDIFY ip char(8) NT NULL DEFAULT '0';
ALTER TABLE sessions MDIFY id char(32) NT NULL DEFAULT '';
show create table sessions;
CREATE TABLE `sessions` (
`id` varchar(32) NT NULL default '',
`user_id` int(6) NT NULL default '0',
`ip` varchar(8) NT NULL default '0',
`lastseen` timestamp NT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`expire` timestamp NT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `user_id2` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
What am I doing wrong ?

Pooly wrote:
Hi,
I try to convert a varchar to a char, but it doesn't seems to work.
From the manual: "all CHAR columns longer than three characters are
changed to VARCHAR columns."
<URL: >

Damnit !
Thanks for pointing it, I forgot these things.
But it's a bit more subtle :
If any column in a table has a variable length, the entire row becomes
variable-length as a result. Therefore, if a table contains any
variable-length columns (VARCHAR, TEXT, or BLB), all CHAR columns
longer than three characters are changed to VARCHAR columns.

I get it working with :
ALTER TABLE sessions MDIFY id char(32) NT NULL DEFAULT '', MDIFY ip
char(8) NT NULL DEFAULT '0';
show create table sessions;
CREATE TABLE `sessions` (
`id` char(32) NT NULL default '',
`user_id` int(6) NT NULL default '0',
`ip` char(8) NT NULL default '0',
`lastseen` timestamp NT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`expire` timestamp NT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `user_id2` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and now I've got fixed-length rows !
Thanks

2005/8/13, Roger Baklund <roger (AT) charlott (DOT) no>:
Pooly wrote:
Hi,

I try to convert a varchar to a char, but it doesn't seems to work.

From the manual: "all CHAR columns longer than three characters are
changed to VARCHAR columns."

<URL: >

Sat, Aug 13, 2005 at 04:01:38PM +0100, Pooly wrote:

ALTER TABLE sessions MDIFY ip char(8) NT NULL DEFAULT '0';
ALTER TABLE sessions MDIFY id char(32) NT NULL DEFAULT '';

Hello,

Since you have two varchar columns, I don't think there's any way to
convert them both to char without dropping one first.

MySQL will always silently convert a char into a varchar if the table
is already a dynamic-row-length type (which it is, because the other
varchar makes it that way). So neither statement actually does
anything, they're both null operations.

The only way you can do this would be to move all data from, say, ip,
into another table temporarily, then drop that column, then change id
into a char, then create ip as a char, and import it all back.

This reveals a bit of a shortcoming in alter table that you can't
atomically modify two columns at once, which might get around this
problem.

Sat, Aug 13, 2005 at 05:37:56PM +0100, Pooly wrote:
Damnit !
Thanks for pointing it, I forgot these things.
But it's a bit more subtle :

[snip]

Bah, should have waited another 5 minutes before I bothered posting my
last long-winded ramble ;)

ALTER TABLE sessions MDIFY id char(32) NT NULL DEFAULT '', MDIFY ip
char(8) NT NULL DEFAULT '0';

Cool, I didn't know you could do this though. Ta :)

Yeah, for example the last statement ALTER, ,
is the only way sometimes to make things working.
anyway, it's worth knowing it.

2005/8/13, Chris Elsworth <chris (AT) shagged (DOT) org>:
Sat, Aug 13, 2005 at 05:37:56PM +0100, Pooly wrote:
Damnit !
Thanks for pointing it, I forgot these things.
But it's a bit more subtle :

[snip]

Bah, should have waited another 5 minutes before I bothered posting my
last long-winded ramble ;)

ALTER TABLE sessions MDIFY id char(32) NT NULL DEFAULT '', MDIFY ip
char(8) NT NULL DEFAULT '0';

Cool, I didn't know you could do this though. Ta :)

Pooly wrote:
Damnit !
Thanks for pointing it, I forgot these things.
But it's a bit more subtle :
If any column in a table has a variable length, the entire row becomes
variable-length as a result. Therefore, if a table contains any
variable-length columns (VARCHAR, TEXT, or BLB), all CHAR columns
longer than three characters are changed to VARCHAR columns.

I get it working with :
ALTER TABLE sessions MDIFY id char(32) NT NULL DEFAULT '', MDIFY ip
char(8) NT NULL DEFAULT '0';
show create table sessions;
CREATE TABLE `sessions` (
`id` char(32) NT NULL default '',
`user_id` int(6) NT NULL default '0',
`ip` char(8) NT NULL default '0',
`lastseen` timestamp NT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`expire` timestamp NT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `user_id2` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and now I've got fixed-length rows !

or you could have just done:

alter table sessions row_format=fixed;
-jsd-