ERROR 1054 (42S22): Unknown column 'marks' in 'field list'

Question

This is a very simple MySQL query.

INSERT INTO users_questions (user_id, question_id, mcopt_id,timestamp) 
VALUES (50053, 875, 3092, '2015-08-22 18:01:44');

When I use it I get

ERROR 1054 (42S22): Unknown column 'marks' in 'field list'

marks is a column in the same table whose default value is set to NULL and in the above query I don't even use the column name marks.

So why exactly am i getting the error?

Structure of table:

+-------------+-----------+------+-----+-------------------+-------+
| Field       | Type      | Null | Key | Default           | Extra |
+-------------+-----------+------+-----+-------------------+-------+
| user_id     | int(11)   | NO   | PRI | NULL              |       |
| question_id | int(11)   | NO   | PRI | NULL              |       |
| mcopt_id    | int(11)   | NO   |     | NULL              |       |
| timestamp   | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
| marks       | int(11)   | NO   |     | NULL              |       |
+-------------+-----------+------+-----+-------------------+-------+

Just to make it clear I also get the error when I provide the value of marks

 INSERT INTO users_questions (user_id, question_id, mcopt_id, timestamp, marks) VALUES (50053, 875, 3094, '2015-08-22 19:15:07', 1)

`

Answer 1

Sometimes, when you implement wrong trigger, it happens. So just drop your trigger by using:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

and it actually worked in my Mysql case. Maybe helpful for some of you.

Answer 2

A:

create table users_questions2
(   user_id int not null,
    question_id int not null,
    mcopt_id int not null,
    timestamp timestamp not null,
    marks int not null
);
describe  users_questions2;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| user_id     | int(11)   | NO   |     | NULL              |                             |
| question_id | int(11)   | NO   |     | NULL              |                             |
| mcopt_id    | int(11)   | NO   |     | NULL              |                             |
| timestamp   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| marks       | int(11)   | NO   |     | NULL              |                             |
+-------------+-----------+------+-----+-------------------+-----------------------------+

INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp) VALUES (50053, 875, 3092, '2015-08-22 18:01:44');

Error Code: 1364. Field 'marks' doesn't have a default value    0.047 sec

INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp,marks) VALUES (50053, 875, 3092, '2015-08-22 18:01:44',1);
-- 1 row(s) affected

INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp,marks) VALUES (50053, 875, 3092, '2015-08-22 18:01:44',null);
Error Code: 1048. Column 'marks' cannot be null 0.000 sec

B:

drop table users_questions2;

create table users_questions2
(   user_id int  null,
    question_id int  null,
    mcopt_id int  null,
    timestamp timestamp  null,
    marks int  null
);
describe  users_questions2;
+-------------+-----------+------+-----+---------+-------+
| Field       | Type      | Null | Key | Default | Extra |
+-------------+-----------+------+-----+---------+-------+
| user_id     | int(11)   | YES  |     | NULL    |       |
| question_id | int(11)   | YES  |     | NULL    |       |
| mcopt_id    | int(11)   | YES  |     | NULL    |       |
| timestamp   | timestamp | YES  |     | NULL    |       |
| marks       | int(11)   | YES  |     | NULL    |       |
+-------------+-----------+------+-----+---------+-------+
INSERT INTO users_questions2 (user_id, question_id, mcopt_id, timestamp) VALUES (50053, 875, 3092, '2015-08-22 18:01:44');

1 row(s) affected

So the only way I can get my describe table to look like yours is if they are not null columns (section A above). Which means your columns do not accept nulls.

Edit:

show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.24                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.24-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+