Mysql Error Code 1048. Column Cannot Be Null
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ERROR 1048 (23000) Column cannot be NULL, however I am inserting valid data up vote 5 down vote favorite There are tons of these posts on Stack Overflow, however from the 20 or so that I looked at they were either coding errors faced when interfacing with MySQL (which I am not trying to do) or simply wanted null values but had their table defined incorrectly. I am seeing an error in MySQL 5.6.19 where I have a column that is not allowed to have a null value. This is fine as it shouldn't have a null value. Here is the table desc below. mysql> describe z; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | a | int(11) | NO | PRI | NULL | auto_increment | | data | char(30) | NO | | NULL | | | t | datetime | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) My problem is that I am inserting valid data.... mysql> insert into z (data, t) values('helloworld', sysdate()); ERROR 1048 (23000): Column 'data' cannot be null There is one other piece of information that might be of some concern... or may not be. I have a trigger and procedure that execute upon the implementation of inserts into this column. However I don't see that it should be a problem due to the tri
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions http://stackoverflow.com/questions/25154105/error-1048-23000-column-cannot-be-null-however-i-am-inserting-valid-data Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Error Code: 1048: Column cannot http://stackoverflow.com/questions/37994365/mysql-error-code-1048-column-cannot-be-null be null up vote 0 down vote favorite I have a query: SELECT COUNT(*) FROM (SELECT vendors_id, merchants_id, SUM(amount) AS amount, SUM(commission_amount) AS commission_amount FROM (SELECT vendors_id, merchants_id, amount, commission_amount FROM (SELECT vendors.id AS vendors_id, merchants_id, SUM(transactions_cash.amount) AS amount, SUM( transactions_cash.commission_amount ) AS commission_amount FROM ibaserver.transactions_cash, ibaserver.vendors, ibaserver.merchants WHERE transactions_cash.vendors_id = vendors.id AND TIME > 1466680920208 AND TIME <= 1466681880067 AND merchants_id = merchants.id GROUP BY transactions_cash.merchants_id ORDER BY transactions_cash.merchants_id) a UNION ALL SELECT vendors_id, merchants_id, amount, commission_amount FROM (SELECT vendors.id AS vendors_id, merchants_id, SUM( transactions_cash_archive.amount ) AS amount, SUM( transactions_cash_archive.commission_amount ) AS commission_amount FROM ibaserver.transactions_cash_archive, ibaserver.vendors, ibaserver.merchants WHERE transactions_cash_archive.vendors_id = vendors.id AND TIME > 1466680920208 AND TIME <= 1466681880067 AND merchants_id = merchants.id GROUP BY transactions_cash_archive.merchants_id ORDER BY transactions_cash_archive.merchants_id) b) s) q And when the inner query SELECT vendor
2014 10:02 Reporter: Veera Kona Email Updates: Status: Not a Bug Impact on me: None Category:MySQL Server: Data Types Severity:S3 (Non-critical) Version:5.6 OS:Any Assigned to: Tags: datetime, default value not inserted View Add Comment Files Developer https://bugs.mysql.com/bug.php?id=72628 Edit Submission View Progress Log Contributions [13 May 2014 7:56] Veera Kona Description: While inserting null value for any columns which doesn't accept null value, MYSQL used to insert a default value if specified any. But this is not happening for datetime columns. However datetime column doesn't accept default values till MYSQL 5.5, it is fixed in 5.6 (I can say this is only partial fix). Here we need to specify DEFAULT key mysql error instead of NULL in order to achieve the old functionality. Because of this our applications are failing when we try to migrate all timestamp columns to datetime type (Migration is because of 2038 limitation of timestamp datatype). How to repeat: 1. create a table with datetime and timestamp fields CREATE TABLE `sample_table`( `id` INT NOT NULL AUTO_INCREMENT , `datetime_field` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00.000000' , `timestamp_field` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , mysql error code PRIMARY KEY (`id`) ); 2. Insert a row with value NULL for all fields INSERT INTO `sample_table`(`id`, `datetime_field`,`timestamp_field`,) VALUES (NULL, NULL, NULL); Error Code : 1048 Column 'datetime_field' cannot be null 3. Insert a row with value NULL for columns other than datetime_field INSERT INTO `sample_table`(`id`, `datetime_field`,`timestamp_field`,) VALUES (CURRENT_TIMESTAMP, NULL, NULL); SUCCESS: (1 row(s) affected) 4. Insert a row with explicitly specifying DEFAULT for datetime column INSERT INTO `sample_table`(`id`, `datetime_field`,`timestamp_field`,) VALUES (DEFAULT, NULL, NULL); SUCCESS: (1 row(s) affected) Suggested fix: The datetime column should be able to insert default value when a default value is specified, not null constraint is applied and null value is passed. [13 May 2014 11:12] Peter Laursen What is your SQL_mode? -- Peter -- Not a MySQL/Oracle person8 [13 May 2014 12:44] Veera Kona Does it matter here, Because with the same mode when I try to insert null to timestamp columns default value is being inserted. [30 Jul 2014 10:02] Umesh Umesh Thank you for the report. Imho, this is a documented behavior and not a bug. Per manual "Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the