How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?

Question

How can I avoid getting this MySQL error Incorrect column specifier for column topic_id ?

MySQL Error...

#1063 - Incorrect column specifier for column 'topic_id'

SQL Schema...

CREATE TABLE discussion_topics (
    topic_id char(36) NOT NULL AUTO_INCREMENT,
    project_id char(36) NOT NULL,
    topic_subject VARCHAR(255) NOT NULL,
    topic_content TEXT default NULL,
    date_created DATETIME NOT NULL,
    date_last_post DATETIME NOT NULL,
    created_by_user_id char(36) NOT NULL,
    last_post_user_id char(36) NOT NULL,
    posts_count char(36) default NULL,
    PRIMARY KEY (topic_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

score 59 · Accepted Answer · edited Oct 01 '14 at 06:00

59

To use AUTO_INCREMENT you need to deifne column as INT or floating-point types, not CHAR.

AUTO_INCREMENT use only unsigned value, so it's good to use UNSIGNED as well;

CREATE TABLE discussion_topics (

     topic_id INT NOT NULL unsigned AUTO_INCREMENT,
     project_id char(36) NOT NULL,
     topic_subject VARCHAR(255) NOT NULL,
     topic_content TEXT default NULL,
     date_created DATETIME NOT NULL,
     date_last_post DATETIME NOT NULL,
     created_by_user_id char(36) NOT NULL,
     last_post_user_id char(36) NOT NULL,
     posts_count char(36) default NULL,
     PRIMARY KEY (topic_id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

edited Oct 01 '14 at 06:00

Barmar

741,623
53
500
612

answered Oct 01 '14 at 05:57

M.Svrcek

5,485
4
25
33

Maybe i'm being lazy but is there an easy 1-2 sentence way to define the difference in dummy terms as to `UNSIGNED` vs `SIGNED` I have no idea what that means unfortunately and I don't have time to read for an hour right now? – JasonDavis Oct 01 '14 at 06:14
2

Well, when you define classic INT, it is signed, values from -2147483648 to 2147483647, but autoincemented values can be only plus signed, so when you use unsigned, mysql expects numbers from 0 to 4294967295, son in same, INT is 4kb and in that same 4kb, you can use two times more entries :) hope you understand – M.Svrcek Oct 01 '14 at 06:21
"signed" means "can be negative". The "sign" is simply the minus symbol in front of the number. – Abraham Brookes Jul 14 '19 at 21:42

score 7 · Answer 2 · answered Oct 01 '14 at 05:57

The auto_increment property only works for numeric columns (integer and floating point), not char columns:

CREATE TABLE discussion_topics (
    topic_id INT NOT NULL AUTO_INCREMENT,
    project_id char(36) NOT NULL,
    topic_subject VARCHAR(255) NOT NULL,
    topic_content TEXT default NULL,
    date_created DATETIME NOT NULL,
    date_last_post DATETIME NOT NULL,
    created_by_user_id char(36) NOT NULL,
    last_post_user_id char(36) NOT NULL,
    posts_count char(36) default NULL,
    PRIMARY KEY (topic_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

raina77ow · Answer 3 · 2014-10-01T06:07:30.287

Quoting the doc:

Some attributes do not apply to all data types. AUTO_INCREMENT applies only to integer and floating-point types. DEFAULT does not apply to the BLOB or TEXT types.

In your case, you're trying to apply AUTO_INCREMENT modifier to char column. To solve this, either drop AUTO_INCREMENT altogether (that means you'll have to generate a unique id on the application level) or just change topic_id type to the relevant integer one.

As a sidenote, it makes little sense using char(36) to store the posts count, so that column's type probably has to be changed as well. It looks like you're going this way to prevent integer overflow - but if you're dealing with more than 18446744073709551615 posts (the biggest number that can be stored in BIGINT UNSIGNED column) in a single topic, you have far bigger problem on your side probably. )

I knew it! Well I thought that might be the problem but skimming over the Docs I missed this, thanks for pointing it out in the Docs too — JasonDavis, Oct 01 '14 at 06:15

codebot · Answer 4 · 2014-10-01T06:30:06.570

You cannot auto increment the char values. It should be int or long(integers or floating points). Try with this,

CREATE TABLE discussion_topics (
    topic_id int(5) NOT NULL AUTO_INCREMENT,
    project_id char(36) NOT NULL,
    topic_subject VARCHAR(255) NOT NULL,
    topic_content TEXT default NULL,
    date_created DATETIME NOT NULL,
    date_last_post DATETIME NOT NULL,
    created_by_user_id char(36) NOT NULL,
    last_post_user_id char(36) NOT NULL,
    posts_count char(36) default NULL,
    PRIMARY KEY (`topic_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Hope this helps

That was a mistake. Thank you M.Svrcek – codebot Oct 01 '14 at 06:30 — codebot, Oct 01 '14 at 06:30

score 3 · Answer 5 · answered Jul 28 '16 at 09:22

3

I was having the same problem, but using Long type. I changed for INT and it worked for me.

CREATE TABLE lists (
 id INT NOT NULL AUTO_INCREMENT,
 desc varchar(30),
 owner varchar(20),
 visibility boolean,
 PRIMARY KEY (id)
 );

answered Jul 28 '16 at 09:22

J C

731
7
11

How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME?

5 Answers5

Linked