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 trigger being activated after the insert statement completes.
Here is the trigger:
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: insertuser
Event: INSERT
Table: z
Statement: begin
call triggerproc(sysdate(),user(),(select data from z where a = last_insert_id()));
end
Timing: AFTER
Created: NULL
sql_mode: NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
And the Procedure:
mysql> show create procedure triggerproc\G
*************************** 1. row ***************************
Procedure: triggerproc
sql_mode: NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `triggerproc`(in a datetime, in b char(30), in c char(30))
begin
insert into record (t,u,data) values(a,b,c);
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
Just for good measure I will include the definition for the record table as well.
mysql> desc record;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| t | datetime | NO | | NULL | |
| u | char(30) | NO | | NULL | |
| data | char(30) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
I have looked through the MySQL reference manual for anything that could be of use, however it doesn’t seem to have any details on this other than the standard error and to check that your column is not defined as not null… or I missed it…
In any case I would be greatly appreciative if anyone can help me out with finding out either the reason for this error or how I can go about finding the reason.
Thanks in advance.
EDIT: My question was answered wonderfully by TheConstructor he informed me that to grab new information from a column that was just inserted through a trigger that the NEW.column operator may be used. Furthermore he followed up with documentation that helps to understand this issue located at Trigger Syntax.
I only wonder why the trigger that I had wouldn’t work with the insert statement even though it should activate after the previous statement, which makes me believe that it should (theoretically) work.
If you’re getting error 1048 that reads something like “Column ‘ColumnName’ cannot be null” (where ColumnName is the name of a column you’re trying to insert data into), then it’s probably because you’re trying to insert a NULL
value into that column, but the column has a NOT NULL
constraint (which prevents NULL
values from being inserted).
We have a few options when it comes to fixing this issue. The most obvious is to ensure we provide a non-NULL
value for the column. Alternatively, if the column should be able to accept NULL
values, then we can remove the NOT NULL
constraint from the column. Another option is to use the IGNORE
keyword to ignore the error. And another way to deal with the error is to disable strict mode.
Example of Error
Here’s an example of code that produces the error:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Hammer', NULL );
Result:
ERROR 1048 (23000): Column 'ProductDescription' cannot be null
We get an error because we tried to insert NULL
into the ProductDescription
column, but that column doesn’t allow NULL
values. We know this because the error message tells us that the ProductDescription
column cannot be NULL
.
We can use the SHOW CREATE TABLE
statement to check the table’s definition:
SHOW CREATE TABLE Products;
Result:
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Products | CREATE TABLE `Products` ( `ProductName` varchar(255) NOT NULL, `ProductDescription` varchar(1000) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We can see that this table contains two columns, both of which have NOT NULL
constraints.
Solution 1 – Provide a non-NULL
Value
The most obvious solution is to provide a non-NULL
value for the column.
Example:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Hammer', 'Left handed edition' );
Result:
Query OK, 1 row affected (0.00 sec)
Providing a non-NULL
value resolved our issue.
Solution 2 – Remove the NOT NULL
Constraint
If we determine that the column really shouldn’t have a NOT NULL
constraint, we can remove it before inserting data.
Example:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000) NULL;
Result:
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
This message indicates that we successfully removed the NOT NULL
constraint. Or perhaps more accurately, it indicates that we modified the column to allow NULL
values.
We could also have omitted the NULL
keyword, and the column would have been redefined without the NOT NULL
constraint, like this:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000);
Now let’s try inserting NULL
into that column again:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Monkey Wrench', NULL );
Result:
Query OK, 1 row affected (0.00 sec)
This time it was successful.
Let’s take a look at the data in our table:
SELECT * FROM Products;
Result:
+---------------+---------------------+ | ProductName | ProductDescription | +---------------+---------------------+ | Hammer | Left handed edition | | Monkey Wrench | NULL | +---------------+---------------------+
We can see that both rows from our examples have been inserted successfully.
Solution 3 – Use the IGNORE
Clause
Another way to deal with the issue is to use the IGNORE
clause in our INSERT
statement.
Let’s drop the table and start again:
DROP TABLE Products;
CREATE TABLE Products (
ProductName varchar(255) NOT NULL,
ProductDescription varchar(1000) NOT NULL
);
Now let’s try to insert some values that contain NULL
:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES
( 'Hammer', 'Left handed edition' ),
( 'Saw', NULL ),
( 'Wrench', NULL ),
( 'Screw Driver', 'Right handed edition' );
Result:
ERROR 1048 (23000): Column 'ProductDescription' cannot be null
As expected, we get the error.
Now let’s try again, but with the IGNORE
keyword:
INSERT IGNORE INTO Products ( ProductName, ProductDescription )
VALUES
( 'Hammer', 'Left handed edition' ),
( 'Saw', NULL ),
( 'Wrench', NULL ),
( 'Screw Driver', 'Right handed edition' );
Result:
Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1
This time the data was inserted successfully.
However, we did get a warning. Let’s check it:
SHOW WARNINGS;
Result:
+---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1048 | Column 'ProductDescription' cannot be null | +---------+------+--------------------------------------------+
So the IGNORE
keyword downgraded the error to a warning.
Let’s check the contents of the table:
SELECT
*,
ISNULL(ProductDescription)
FROM Products;
Result:
+--------------+----------------------+----------------------------+ | ProductName | ProductDescription | ISNULL(ProductDescription) | +--------------+----------------------+----------------------------+ | Hammer | Left handed edition | 0 | | Saw | | 0 | | Wrench | | 0 | | Screw Driver | Right handed edition | 0 | +--------------+----------------------+----------------------------+
I added the third column to show that MySQL doesn’t actually insert NULL
into the column when we use the IGNORE
keyword. Instead, it inserts the implicit default value for the data type. In our case, the data type is a string, and so it inserted the empty string.
Solution 4 – Disable Strict SQL Mode
The whole reason we get an error is because we have strict SQL mode enabled. When we used the IGNORE
keyword, we were basically just overcoming the error we’d get due to the fact that we have strict mode enabled.
So another way to deal with the error is to disable strict mode altogether.
Here’s what my SQL mode looks like:
SELECT @@SESSION.sql_mode;
Result:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Strict SQL mode is in effect if either STRICT_ALL_TABLES
or STRICT_TRANS_TABLES
is enabled. My SQL mode contains STRICT_TRANS_TABLES
, which means I have strict SQL mode enabled. It also contains related modes NO_ZERO_IN_DATE
, NO_ZERO_DATE
, and ERROR_FOR_DIVISION_BY_ZERO
. Depending on your MySQL implementation, you may or may not have these other modes.
Let’s disable strict mode:
SET @@sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO');
SELECT @@SESSION.sql_mode;
Result:
ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
We can see that our SQL mode no longer contains STRICT_TRANS_TABLES
. I also removed NO_ZERO_IN_DATE
, NO_ZERO_DATE
, and ERROR_FOR_DIVISION_BY_ZERO
.
Let’s drop the table and start again:
DROP TABLE Products;
CREATE TABLE Products (
ProductName varchar(255) NOT NULL,
ProductDescription varchar(1000) NOT NULL
);
Now let’s try to insert some values that contain NULL
:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES
( 'Hammer', 'Left handed edition' ),
( 'Saw', NULL ),
( 'Wrench', NULL ),
( 'Screw Driver', 'Right handed edition' );
Result:
Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1
We get the same result that we got when using the IGNORE
keyword.
Let’s check the warnings:
SHOW WARNINGS;
Result:
+---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1048 | Column 'ProductDescription' cannot be null | +---------+------+--------------------------------------------+
As expected, the warning tells us that the column can’t be NULL
.
And let’s check the data:
SELECT
*,
ISNULL(ProductDescription)
FROM Products;
Result:
+--------------+----------------------+----------------------------+ | ProductName | ProductDescription | ISNULL(ProductDescription) | +--------------+----------------------+----------------------------+ | Hammer | Left handed edition | 0 | | Saw | | 0 | | Wrench | | 0 | | Screw Driver | Right handed edition | 0 | +--------------+----------------------+----------------------------+
Again, same result as with the IGNORE
keyword.
So, disabling strict SQL mode allowed us to insert NULL
values without getting an error. Or more accurately, it allowed us to attempt to insert NULL
values without error. As stated, using this method doesn’t actually insert NULL
. It inserts the implicit default value for the data type.
When it comes to using IGNORE
vs disabling strict SQL mode, there are a few nuances to consider. The MySQL documentation contains a comparison of IGNORE
and strict SQL mode that can be helpful when deciding which option to use.
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (925217, ‘null’, 0, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (1, ‘[]’, 0, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (915156, ‘[97167]’, 97167, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913286, ‘[5]’, 5, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913313, ‘[6]’, 6, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912923, ‘[8]’, 8, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913435, ‘[10]’, 10, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912915, ‘[11]’, 11, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913200, ‘[12]’, 12, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912910, ‘[14]’, 14, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913507, ‘[16]’, 16, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912956, ‘[17]’, 17, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916658, ‘[18]’, 18, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913113, ‘[19]’, 19, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912943, ‘[20]’, 20, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913177, ‘[24]’, 24, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913292, ‘[26]’, 26, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913556, ‘[29]’, 29, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913070, ‘[30]’, 30, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914218, ‘[33]’, 33, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913132, ‘[36]’, 36, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (915191, ‘[37]’, 37, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912922, ‘[39]’, 39, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (918295, ‘[40]’, 40, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914929, ‘[1]’, 1, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913088, ‘[56]’, 56, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913621, ‘[97210]’, 97210, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (927564, ‘[97147]’, 97147, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914501, ‘[5, 14]’, 5, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912947, ‘[11, 14]’, 11, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912906, ‘[11, 24]’, 11, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (919620, ‘[12, 14]’, 12, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914603, ‘[14, 11]’, 14, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (920099, ‘[14, 12]’, 14, 12, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (924294, ‘[14, 16]’, 14, 16, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913985, ‘[14, 24]’, 14, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914347, ‘[14, 5]’, 14, 5, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916604, ‘[17, 24]’, 17, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (921085, ‘[16, 14]’, 16, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916110, ‘[16, 24]’, 16, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913074, ‘[24, 11]’, 24, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914512, ‘[24, 14]’, 24, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913394, ‘[24, 17]’, 24, 17, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (935064, ‘[18, 37]’, 18, 37, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (920771, ‘[39, 17]’, 39, 17, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (920074, ‘[37, 18]’, 37, 18, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937287, ‘[14, 24, 0]’, 14, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938159, ‘[1, 0, 0]’, 1, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941325, ‘[40, 0, 0]’, 40, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937426, ‘[19, 0, 0]’, 19, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937185, ‘[11, 24, 0]’, 11, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937349, ‘[56, 0, 0]’, 56, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937558, ‘[14, 11, 5]’, 14, 11, 5);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916608, ‘[14, 11, 24]’, 14, 11, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (946805, ‘[97147, 0, 0]’, 97147, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937160, ‘[24, 11, 0]’, 24, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (947617, ‘[14, 24, 11]’, 14, 24, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937161, ‘[11, 0, 0]’, 11, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937718, ‘[97210, 0, 0]’, 97210, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937659, ‘[33, 0, 0]’, 33, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937916, ‘[14, 5, 0]’, 14, 5, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937484, ‘[24, 14, 0]’, 24, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938895, ‘[17, 24, 0]’, 17, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (930430, ‘[97147, 24, 11]’, 97147, 24, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937198, ‘[24, 17, 0]’, 24, 17, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938854, ‘[5, 14, 0]’, 5, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938250, ‘[26, 0, 0]’, 26, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941167, ‘[24, 19, 0]’, 24, 19, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937209, ‘[30, 0, 0]’, 30, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937301, ‘[5, 0, 0]’, 5, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937169, ‘[36, 0, 0]’, 36, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938038, ‘[6, 0, 0]’, 6, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913324, ‘[24, 14, 11]’, 24, 14, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914887, ‘[24, 11, 14]’, 24, 11, 14);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916501, ‘[24, 17, 11]’, 24, 17, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (930774, ‘[24, 19, 11]’, 24, 19, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (946722, ‘[24, 19, 17]’, 24, 19, 17);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913486, ‘[24, 16, 11]’, 24, 16, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937158, ‘[39, 0, 0]’, 39, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937159, ‘[17, 0, 0]’, 17, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937157, ‘[8, 0, 0]’, 8, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (939049, ‘[16, 14, 0]’, 16, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941216, ‘[14, 11, 0]’, 14, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937482, ‘[10, 0, 0]’, 10, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937248, ‘[0, 0, 0]’, 0, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937210, ‘[12, 0, 0]’, 12, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937514, ‘[29, 0, 0]’, 29, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937183, ‘[14, 0, 0]’, 14, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (940628, ‘[37, 0, 0]’, 37, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937219, ‘[16, 0, 0]’, 16, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913445, ‘[11, 14, 24]’, 11, 14, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (940199, ‘[11, 17, 24]’, 11, 17, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (924258, ‘[11, 16, 24]’, 11, 16, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937449, ‘[11, 14, 0]’, 11, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937170, ‘[24, 0, 0]’, 24, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938459, ‘[97167, 0, 0]’, 97167, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941402, ‘[24, 14, 11, 5]’, 24, 14, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (932540, ‘[24, 16, 14, 11]’, 24, 16, 14);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (930755, ‘[24, 19, 11, 5]’, 24, 19, 11);
Aeliot 176 / 61 / 3 Регистрация: 17.11.2011 Сообщений: 318 |
||||
1 |
||||
04.03.2013, 12:31. Показов 7002. Ответов 8 Метки нет (Все метки)
Добрый день, возникла странная проблема… Не могу понять, это глюк или проблема в отличии дистрибутивов MySQL На сервере MySQL 5.0 , а на локалке MySQL 5.5 Запрос такой:
0 |
601 / 468 / 73 Регистрация: 22.01.2009 Сообщений: 1,180 Записей в блоге: 1 |
|
04.03.2013, 12:52 |
2 |
возможно, различия в версиях… у какой-то версии более строгие правила для вложенных запросов, наверное. мне кажется, судя по ошибке, что вам поможет установка NOT NULL для поля jid.
1 |
176 / 61 / 3 Регистрация: 17.11.2011 Сообщений: 318 |
|
04.03.2013, 13:30 [ТС] |
3 |
мне кажется, судя по ошибке, что вам поможет установка NOT NULL для поля jid. Именно так и стояло.
0 |
601 / 468 / 73 Регистрация: 22.01.2009 Сообщений: 1,180 Записей в блоге: 1 |
|
04.03.2013, 13:42 |
4 |
так. стоп. значит наоборот, NULL образуется в этом поле, навреное, во вложенном запросе (блин, всегда стараюсь их избегать, и вроде как пока неплохо получается). не эксперт в данном вопросе, тем более что тут важны данные. попробуйте выполнить вложенный запрос отдельно, мб там где этот NULL проявится.
WHERE j.id = 159 GROUP BY j.id хоть я и не эксперт, но разве в такой конструкции есть тайный философский смысл?
0 |
813 / 796 / 201 Регистрация: 21.09.2012 Сообщений: 2,656 |
|
04.03.2013, 14:12 |
5 |
А ошибку точно этот запрос выдает? Случайно нет на это же страницы INSERTa?
0 |
Aeliot 176 / 61 / 3 Регистрация: 17.11.2011 Сообщений: 318 |
||||
04.03.2013, 15:00 [ТС] |
6 |
|||
А ошибку точно этот запрос выдает? Точно он. Добавлено через 10 минут
так. стоп. значит наоборот, NULL образуется в этом поле, навреное, во вложенном запросе Так и есть. В том суть LEFT JOIN и состоит, что он выбирает из второй таблицы связанные данные, а там где связанных данных нет, должен выдавать NULL. Это стандартная конструкция. Добавлено через 8 минут
хоть я и не эксперт, но разве в такой конструкции есть тайный философский смысл? Всё просто. Во второй таблице есть несколько записей (неопределённое число), связанных с конкретной записью из первой таблицы и мне нужно, чтобы вспомогательный запрос выдавал не «набор записей», а одно значение, полученное по определённым условиям. В случае, когда нет записей, удовлетворяющих условиям выборки он должен возвращать NULL.
0 |
Dolphin 813 / 796 / 201 Регистрация: 21.09.2012 Сообщений: 2,656 |
||||
04.03.2013, 15:23 |
7 |
|||
Может так будет проще? если понял смысл запроса
1 |
601 / 468 / 73 Регистрация: 22.01.2009 Сообщений: 1,180 Записей в блоге: 1 |
|
04.03.2013, 23:56 |
8 |
Dolphin, плюсадын!!!
Всё просто. Во второй таблице есть несколько записей (неопределённое число), связанных с конкретной записью из первой таблицы и мне нужно, чтобы вспомогательный запрос выдавал не «набор записей», а одно значение, полученное по определённым условиям. В случае, когда нет записей, удовлетворяющих условиям выборки он должен возвращать NULL. для этого и придумали несколько JOIN-ов… разве нет? в любом случае, меня ужасно смутила именно группировка по единственному полю, когда запись будет в выборке только одна (id — это ж PRIMARY KEY, как я понимаю) при этом еще и без агрегирующих функций. Кликните здесь для просмотра всего текста Впрочем, как я уже говорил, вложенные запросы знаю очень плохо, отчасти потому что все время забываю, когда они ведут к квадратичной сложности, поэтому не применяю их, да и сильной нужды в них не было… Поэтому, могу предположить, что эта группировка как-то может воздействовать на вложенный запрос…
0 |
176 / 61 / 3 Регистрация: 17.11.2011 Сообщений: 318 |
|
05.03.2013, 10:04 [ТС] |
9 |
Может так будет проще? Спасибо. Добавлено через 8 минут
меня ужасно смутила именно группировка по единственному полю, когда запись будет в выборке только одна (id — это ж PRIMARY KEY, как я понимаю) при этом еще и без агрегирующих функций. Спасибо. Это я «недосмотрел»
0 |
Solution 1:[1]
I am assuming that you are using laravel. To get value of input you can try this code for your name and type field.
$myproduct->name=$request->name;
$myproduct->type=$request->type;
Solution 2:[2]
i had the same problem, but problem the name of the table was not the same as the name on the form…
$todo->todo = $request->todo;
Solution 3:[3]
yea I faced the same issue with laravel,I had to change my database to nullable then the request went through….going back to my code and adding dd($article),it shows that the form field was empty. I inspected the form field and noticed that the names are not matching with that of the table during migration.
Solution 4:[4]
if you used laravel then clear the catch using PHP artisan optimize: clear then re-run the project, because some catch create such a problem
Solution 5:[5]
One of the reason is that when you declare the variable in inverted comma then check that you are giving space in the declaration as shown in the example
‘ name’ then it will show error:
$profileName = $request->input(' name');
? you have seen that I have given space in variable declare, so, therefore, I have face such a problem whenever you declare variable make sure you should not give space by mistake