How to handle blank entries in MySQL

Question

I have been struggling with entering blank values in MySQL. For example, the code:

INSERT INTO `tablename` (column1, column2) VALUES ('value1', '');

will not work. I always get the following error:

#1265 - Data truncated for column 'column2' at row 1

However, when I run either of the two statements:

INSERT INTO `tablename` (column1, column2) VALUES ('value1', 'value2');

or

INSERT INTO `tablename` (column1, column2) VALUES ('value1', NULL);

the query works just fine. The default value for column2 has been set as "0", still no luck.

I'm facing this issue with all columns in all tables. My application is such that there will be blank entries (cannot be changed). This feature was working fine earlier, I started getting this error when I shifted the application code to another server.

MySQL server version: 8.0.22 - MySQL Community Server - GPL

Table structure is given below:

I'm facing the error in all columns. Any help would be much appreciated!

Table schema:

CREATE TABLE `recordnew` (
 `id` int NOT NULL AUTO_INCREMENT,
 `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `course_id` int DEFAULT NULL,
 `subject_id` int DEFAULT NULL,
 `teacher_id` int DEFAULT NULL,
 `student_id` int DEFAULT NULL,
 `month` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `attendance` int DEFAULT NULL,
 `totalclasses` int DEFAULT NULL,
 `tutorialattendance` int DEFAULT NULL,
 `tutorialclassestotal` int DEFAULT NULL,
 `practicalattendance` int DEFAULT NULL,
 `practicalclassestotal` int DEFAULT NULL,
 `testmarks` double(11,2) DEFAULT '0.00',
 `assignmentmarks` double(11,2) DEFAULT '0.00',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=68612 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Attendance & Internal Assessment Record'

My actual query:

INSERT INTO `recordnew` 
            (`course_id`, 
             `subject_id`, 
             `teacher_id`, 
             `student_id`, 
             `month`, 
             `attendance`, 
             `totalclasses`, 
             `testmarks`, 
             `assignmentmarks`, 
             `tutorialattendance`, 
             `tutorialclassestotal`, 
             `practicalattendance`, 
             `practicalclassestotal`) 
VALUES      ('13', 
             '17', 
             '10', 
             '1704', 
             'Marks', 
             '0', 
             '0', 
             '15', 
             '', 
             '0', 
             '0', 
             '0', 
             '0')

Error for the above query:

#1265 - Data truncated for column 'assignmentmarks' at row 1

Other SQL query:

INSERT INTO `recordnew` 
            (`course_id`, 
             `subject_id`, 
             `teacher_id`, 
             `student_id`, 
             `month`, 
             `attendance`, 
             `totalclasses`, 
             `testmarks`, 
             `assignmentmarks`, 
             `tutorialattendance`, 
             `tutorialclassestotal`, 
             `practicalattendance`, 
             `practicalclassestotal`) 
VALUES      ('13', 
             '17', 
             '10', 
             '1704', 
             '', 
             '0', 
             '0', 
             '15', 
             '0', 
             '0', 
             '0', 
             '0', 
             '0')

Error for above query:

#1265 - Data truncated for column 'month' at row 1

I am no expert in SQL, but I think adding your table schema would really help anyone who might know the answer. — ashu, Dec 04 '20 at 06:01
What is datatype for column1, column2? eg. varchar, int, text ... — Siddharth Rathod, Dec 04 '20 at 06:10
I've attached the table schema to the question. This is just one of the tables, I think solving it for one table will solve it for all tables. — Vidul Talwar, Dec 04 '20 at 06:19
Table schema is the textual output of `SHOW CREATE TABLE tablename`, not a picture. PS. there is no `column2` column in shown picture. — Akina, Dec 04 '20 at 06:22
*I'm facing the error in columns 8 - 15.* Empty string cannot be correctly converted to numeric datatype - this is error cause. Set zero instead empty string, and never assign a string to the numeric column. — Akina, Dec 04 '20 at 06:24
@Akina column2 is not an actual column - I used it to explain the question properly. My query is a lot more complex than what I've explained here. — Vidul Talwar, Dec 04 '20 at 06:26
Query complicity does not affect. I explain the source of a problem already: "Empty string cannot be correctly converted to numeric datatype". — Akina, Dec 04 '20 at 06:32
@Akina I'm facing this issue with the 'month' column with data type varchar(255) as well. I suspect that it's something to do with the MySQL settings since everything was working fine on a different server (I just shifted the server for this application). Any idea on how I can find out what's going on here? — Vidul Talwar, Dec 04 '20 at 07:44
`month` column have no default value. Add it (DEFAULT NULL, for example). — Akina, Dec 04 '20 at 08:08
@Akina Hi, thanks for the help. I'm still getting the error after setting a default NULL value. — Vidul Talwar, Dec 04 '20 at 08:24
I won't give you any more advice until table's complete CREATE TABLE and full problematic query text published. My crystal ball is faulty. — Akina, Dec 04 '20 at 08:27
Well. I see table's structure. Now publish the query code and the error message produced during its execution. — Akina, Dec 04 '20 at 08:35
Does https://stackoverflow.com/questions/18089240/data-truncated-for-column help? — Nico Haase, Dec 04 '20 at 08:37
Or does https://stackoverflow.com/questions/14764080/error-1265-data-truncated-for-column-when-trying-to-load-data-from-txt-file help? — Nico Haase, Dec 04 '20 at 08:37
@Akina The actual query and updated errors have also been attached. — Vidul Talwar, Dec 04 '20 at 09:40
@NicoHaase I saw the questions - they seem to be because of another problem (the characters limit wasn't properly defined - I'm not having that problem). Hope this makes sense. — Vidul Talwar, Dec 04 '20 at 09:42
*`#1265 - Data truncated for column 'assignmentmarks' at row 1`* :facepalm: Well, one more time: "Empty string cannot be correctly converted to numeric datatype". *`#1265 - Data truncated for column 'month' at row 1`* Not reproduced: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a081dd83b181af365fa4004930fda8c2 — Akina, Dec 04 '20 at 09:49
What do you mean by "another problem"? `assignmentmarks` is not a string, so you cannot insert a string there, that's what the other questions also tell you (that the data inserted should have a format according to the column you insert to) — Nico Haase, Dec 04 '20 at 16:26

score 1 · Accepted Answer · answered Dec 04 '20 at 09:38

Most likely, column2 is of a numeric datatype, not of string-like datatype. The empty string is not a valid numeric value, hence the warning that you are getting.

Guidelines:

If you want to store an empty string, then use a string datatype (varchar, char etc).
Don't store numbers as strings!
If you want to represent the absence of data, use NULL

How to handle blank entries in MySQL

1 Answers1