create table problem with TEXT col type

[Anon]

Hi all
I have been pulling my hair out trying to do the following. I've narrowed it down to a problem with the TEXT field. I've checked the manaul and just can't see whats wrong?

CREATE TABLE bookmarks_links(id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL, cat_id SMALLINT UNSIGNED NOT
NULL, title VARCHAR(255) NOT NULL, desc TEXT, url VARCHAR(255) NOT NULL, PRIMARY KEY(id, cat_id))

Any help greatly appreciated!

Nick

[[deleted]]

First, what are we supposed to help you with if you don't tell us what the problem is?

Second: forget about SMALLINT etc, just use normal INT types. SMALLINT will onlt give you trouble lateron. Also forget about SIGNED/UNSIGNED, the normal INT has more than enough values to suit your needs.

[Anon]

The problem was stated in the subject line.

"create table problem with TEXT col type"

As I said, the problem appears to be with with the text field. ie. It wont create the table.

[[deleted]]

And the error you got was....

[Anon]

just the usual "You have an error in your sql statement near'TEXT NOT NULL.......'

If I take out the text col there's no problem at all. I just can't see anything wrong with it?

On anther note, why do you suggest that I not use SMALLINT UNSIGNED? Surely those types are there for a reason and choosing the one that best fits it's intended use can't be wrong can it?

Nick

[[deleted]]

'desc' is a reserved word.

The fact that something exists does not make it good.

TINYINT, SMALLINT and MEDIUMINT are space-savers, saving 3,2 and 1 byte per row compared to a normal INTEGER. But the number of possible values is not nearly enough when using an auto_increment function. After 65k inserts, your SMALLINT runs out. That means MySQL will refuse to insert new records.
65k sounds like a lot, but it's absolute peanuts. An unsigned INTEGER can take 4294967295 inserts. That means that at a rate of over 100 inserts per second you can keep running for over a year. Using SMALLINT it wouldn't last a month.

And harddisk space is hardly an issue with today's cheap disks.

[Anon]

Hmmm..... certainly food for thought. Thanks for the reserved word pointer, it's always simple once you know.

Nick

[[deleted]]

That's why I needed the error message, the error was after the 'desc' so 'desc' must have been wrong. and it was because

SELECT * FROM table ORDER BY something DESC;