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

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;

This question is related to mysql sql auto-increment ddl

The answer is


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. )


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


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)
 );

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;

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to auto-increment

How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME? How to make MySQL table primary key auto increment with some prefix Get the new record primary key ID from MySQL insert query? How to generate auto increment field in select query Get current AUTO_INCREMENT value for any table Add Auto-Increment ID to existing table? How to AUTO_INCREMENT in db2? How to create id with AUTO_INCREMENT on Oracle? auto increment ID in H2 database ERROR: permission denied for sequence cities_id_seq using Postgres

Examples related to ddl

How does spring.jpa.hibernate.ddl-auto property exactly work in Spring? How can I avoid getting this MySQL error Incorrect column specifier for column COLUMN NAME? MySQL: ALTER TABLE if column not exists Give all permissions to a user on a PostgreSQL database Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL? Adding multiple columns AFTER a specific column in MySQL Create a temporary table in MySQL with an index from a select How to delete a column from a table in MySQL SQL Column definition : default value and not null redundant? How to generate entire DDL of an Oracle schema (scriptable)?