Hello With Error Result From Query

[gaspower]

Hello,

I am to use the following query statement,

ALTER TABLE `configuration` CHANGE `set_function` `set_function` VARCHAR( 555 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

But I get this error in the results screen,

Error Code : 1074

Too big column length for column 'set_function' (max = 255). Use BLOB instead

(0 ms taken)

How can I get around this error?

Thanks JR

[peterlaursen]

Actually this is a silly/a wrong error message! it should read 'use TEXT instead' (and not 'use BLOB instead').

The error message is a MySQL server error – not a SQLyog error. Only from version 5.0 MySQL supports varchars longer than 255 characters. With MySQL 5.0 it is 64 K byte (number of characters depends on the character set and the language as characters may occupy 1-3 bytes each depending on character set and language)

What is the MySQL server version? As you use a COLLATE statement I would guess it is 4.1.x!

The solution is to

1) upgrade MySQL to 5.x or 6.x

2) or use TEXT types for character data longer than 255 characters

[gaspower]

Hello,

Thank you very much for the reply. I appreciate the explanation, but it goes way over my head… I believe it is 3.23, old. Is there another statement I can use to complete the same job, or another way around the problem.

Thanks again for your time,

JR

[peterlaursen]

To create the column as TEXT statement would be like

ALTER TABLE `configuration` CHANGE `set_function` `set_function` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

With very early 3.23 TEXT may not be supported – then you will have to use BLOB. it does not make much difference with such early server versions either!

but …. you cannot use 'COLLATE … ' if version is not 4.1 or higher!

To get the server version just execute “SELECT version();”

It isn't just a typo that you write '555' and not '255' ?? 🙄

Now did you consider using SQLyog GUI 'ALTER TABLE' (right click the table and select from context menu). What SQL is generated you can see in HISTORY tab.

[gaspower]

Hello,

Thank you,

I do have the gui open, and have the alter table open for configuration. If I understand you, I can goto set_function field name and change the (len) from 255 to 555. Also the default is empty which is the same as Null?

Thanks JR

[gaspower]

Hello Again,

Well I did what you said and used the alter command in the gui, but I get the same error? Too big column length for column set?

Thanks JR

[peterlaursen]

YES ..

With MySQL up to 4.1 a varchar can be 255 characters .. not 555 characters. I you need a variable longer than teh max. value for varchars you will have to use s TECT (or BLO:cool:.

This is a server issue and no client can change that!

[Author]

Posts