1063 ошибка mysql

#1063 - Incorrect column specifier for column 'topic_id'

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;

0

Как я могу избежать получения этой ошибки MySQL Неверный спецификатор столбца для столбца topic_id?

Ошибка MySQL…

#1063 - Incorrect column specifier for column 'topic_id'

Схема SQL…

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;

Чтобы использовать AUTO_INCREMENT, вам нужно использовать deifne column как INT или типы с плавающей запятой, а не CHAR.

AUTO_INCREMENT используйте только значение без знака, поэтому хорошо использовать UNSIGNED,

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;

Свойство auto_increment работает только для числовых столбцов (целых и с плавающей запятой), а не char:

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;

Цитирование doc:

Некоторые атрибуты не относятся ко всем типам данных. AUTO_INCREMENT применяется только для целых чисел и типов с плавающей точкой. DEFAULT не применяется к типы BLOB или TEXT.

В вашем случае вы пытаетесь применить модификатор AUTO_INCREMENT к столбцу char. Чтобы решить эту проблему, либо полностью отбросьте AUTO_INCREMENT (это означает, что вам нужно будет создать уникальный идентификатор на уровне приложения), либо просто измените тип topic_id на соответствующий целочисленный.

В качестве побочного элемента мало смысла использовать char(36) для хранения счетчиков сообщений, поэтому, вероятно, необходимо изменить тип столбца. Похоже, вы идете таким образом, чтобы предотвратить переполнение целых чисел, но если вы имеете дело с более чем 18446744073709551615 сообщениями (наибольшее число, которое может быть сохранено в столбце BIGINT UNSIGNED) в одной теме, у вас гораздо больше проблема на вашей стороне, вероятно. )

Вы не можете автоматически увеличивать значения char. Он должен быть int или long (целые числа или плавающие точки).
Попробуйте с этим,

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;

Надеюсь, что это поможет

У меня была та же проблема, но с использованием типа Long. Я изменился для INT, и это сработало для меня.

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

#1063 - Incorrect column specifier for column 'topic_id'

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

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;

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;

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

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

Questions with mysql tag:

• 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
• php mysqli_connect: authentication method unknown to the client [caching_sha2_password]
• phpMyAdmin on MySQL 8.0
• Authentication plugin ‘caching_sha2_password’ cannot be loaded
• Error loading MySQLdb Module ‘Did you install mysqlclient or MySQL-python?’
• select rows in sql with latest date for each ID repeated multiple times
• How to find MySQL process list and to kill those processes?
• Access denied; you need (at least one of) the SUPER privilege(s) for this operation
• Import data.sql MySQL Docker Container
• PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
• Hibernate Error executing DDL via JDBC Statement
• Your password does not satisfy the current policy requirements
• MySql ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
• Laravel: PDOException: could not find driver
• Default password of mysql in ubuntu server 16.04
• #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
• Job for mysqld.service failed See «systemctl status mysqld.service»
• Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
• mysqld_safe Directory ‘/var/run/mysqld’ for UNIX socket file don’t exists
• SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
• MySQL Error: : ‘Access denied for user ‘root’@’localhost’
• Unable to start the mysql server in ubuntu
• How to turn on/off MySQL strict mode in localhost (xampp)?
• How to store Emoji Character in MySQL Database
• ERROR 1698 (28000): Access denied for user ‘root’@’localhost’
• What is the meaning of <> in mysql query?
• The Response content must be a string or object implementing __toString(), «boolean» given after move to psql
• Xampp-mysql — «Table doesn’t exist in engine» #1932
• #1055 — Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by
• MySQL fails on: mysql «ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded»
• How to insert TIMESTAMP into my MySQL table?
• How to create a foreign key in phpmyadmin
• JPA Hibernate Persistence exception [PersistenceUnit: default] Unable to build Hibernate SessionFactory
• PHP: Inserting Values from the Form into MySQL
• #1292 — Incorrect date value: ‘0000-00-00’
• WooCommerce: Finding the products in database
• ERROR 1067 (42000): Invalid default value for ‘created_at’
• SQLSTATE[HY000] [1698] Access denied for user ‘root’@’localhost’
• SQL query to check if a name begins and ends with a vowel
• MySQL: When is Flush Privileges in MySQL really needed?
• Error in MySQL when setting default value for DATE or DATETIME

Questions with sql tag:

• 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
• Export result set on Dbeaver to CSV
• How to create temp table using Create statement in SQL Server?
• SQL Query Where Date = Today Minus 7 Days
• How do I pass a list as a parameter in a stored procedure?
• #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
• MySQL Error: : ‘Access denied for user ‘root’@’localhost’
• SQL Server IF EXISTS THEN 1 ELSE 2
• How to add a boolean datatype column to an existing table in sql?
• Presto SQL — Converting a date string to date format
• What is the meaning of <> in mysql query?
• Change Date Format(DD/MM/YYYY) in SQL SELECT Statement
• Convert timestamp to date in Oracle SQL
• #1292 — Incorrect date value: ‘0000-00-00’
• Postgresql tables exists, but getting «relation does not exist» when querying
• SQL query to check if a name begins and ends with a vowel
• Find the number of employees in each department — SQL Oracle
• Error in MySQL when setting default value for DATE or DATETIME
• Drop view if exists
• Could not find server ‘server name’ in sys.servers. SQL Server 2014
• How to create a Date in SQL Server given the Day, Month and Year as Integers
• TypeError: tuple indices must be integers, not str
• Select Rows with id having even number
• SELECT list is not in GROUP BY clause and contains nonaggregated column
• IN vs ANY operator in PostgreSQL
• How to insert date values into table
• Error related to only_full_group_by when executing a query in MySql
• How to select the first row of each group?
• Connecting to Microsoft SQL server using Python
• eloquent laravel: How to get a row count from a ->get()
• How to execute raw queries with Laravel 5.1?
• In Oracle SQL: How do you insert the current date + time into a table?
• Extract number from string with Oracle function
• Rebuild all indexes in a Database
• SQL: Two select statements in one query
• DB2 SQL error sqlcode=-104 sqlstate=42601
• What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types
• How to run .sql file in Oracle SQL developer tool to import database?
• Concatenate columns in Apache Spark DataFrame
• How Stuff and ‘For Xml Path’ work in SQL Server?
• Fatal error: Call to a member function query() on null

Questions with auto-increment tag:

• 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
• How to reset AUTO_INCREMENT in MySQL?
• Auto increment in MongoDB to store sequence of Unique User ID
• MySQL: #1075 — Incorrect table definition; autoincrement vs another key?
• How to set auto increment primary key in PostgreSQL?
• PHP mySQL — Insert new record into table with auto-increment on primary key
• How do I add a auto_increment primary key in SQL Server database?
• SQL Server, How to set auto increment after creating a table without data loss?
• Reset auto increment counter in postgres
• How to add AUTO_INCREMENT to an existing column?
• How to insert new row to database with AUTO_INCREMENT column without specifying column names?
• Create autoincrement key in Java DB using NetBeans IDE
• How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
• Auto Increment after delete in MySQL
• How to retrieve the last autoincremented ID from a SQLite table?
• Hibernate Auto Increment ID
• How to get last inserted row ID from WordPress database?
• How to set initial value and auto increment in MySQL?
• PostgreSQL Autoincrement
• MSSQL Select statement with incremental integer column… not from a table
• Reset AutoIncrement in SQL Server after Delete
• make an ID in a mysql table auto_increment (after the fact)

Questions with ddl tag:

• 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)?
• There can be only one auto column
• dropping a global temporary table
• Delete column from SQLite table
• Why use multiple columns as primary keys (composite primary key)
• What are DDL and DML?
• How do I get column datatype in Oracle with PL-SQL with low privileges?
• How do I add a foreign key to an existing SQLite table?
• PostgreSQL create table if not exists
• How do I use CREATE OR REPLACE?
• Alter Table Add Column Syntax
• Sqlite primary key on multiple columns
• Truncating a table in a stored procedure
• Using ALTER to drop a column if it exists in MySQL