3

I have two phpmyadmin both are version 4.2.7 and PHP version is PHP Version 5.4.24 one of phpmyadmin is allowing null values and another not accepting the null values

Phpmyadmin - 1

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Phpmyadmin - 2

In the second table, I didn't give the values for column3 its error like

I got the error:

General error: 1364 Field 'column3' doesn't have a default value.

Even though i didn't set the null for phpmyadmin-1 but its working fine. How can I solve this ? Any suggestion ?

INSERT INTO table_name (column1,column2,...)
VALUES (value1,value2,...);
Improve this question
6
  • Do you have the field in your DB with ALLOW NULL option enabled? Commented Mar 27, 2015 at 4:25
  • are you asking for the particular field right ? Commented Mar 27, 2015 at 4:25
  • In the INFORMATION_SCHEMA.COLUMNS table there is a IS_NULLABLE column. Query it. Commented Mar 27, 2015 at 4:32
  • can you give some example for it ? Commented Mar 27, 2015 at 4:32
  • Can you edit your question to add the table schema from each database? Possibly using the Export tab and selecting "Structure only" will help with that. Commented Aug 13, 2015 at 3:45

3 Answers 3

Reset to default
5

Check your code is like this

when you create the database you should apply this option as well.image marked in red

And in your code assign Your value3=NULL.

Then add the above function as same.

INSERT INTO table_name (column1,column2,column3,...)
    VALUES (value1,value2,value3,...);

Using MySQL commands (Update 2023/07/20)

With Default value

ALTER TABLE table_name 
MODIFY column3 datatype DEFAULT default_value;

With allowing nullable

ALTER TABLE table_name
MODIFY column3 datatype NULL;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

yes i got it but i dont want to define for all fields how can i fix for the whole db ? and read my question please
I have given the same for phpmyadmin -1 its working fine without assigning the null values
INSERT INTO table_name SET column1 = 'value1', column2= 'value2'. try this out
0

Use this query:

SELECT `COLUMN_NAME`,`IS_NULLABLE` FROM `INFORMATION_SCHEMA`.COLUMNS
WHERE `TABLE_NAME` = 'table_name'

Here table_name is the name of your table, the only change.

Run this query and you will get a table with COLUMN_NAME and IS_NULLABLE as two columns.

Check it and make sure that you have the field with IS_NULLABLE = 'YES'

Improve this answer

2 Comments

without nullable it was inserting in phpmadmin-1 got it ?
your answer not helped :(
0

You have two copies of the same database that you can reach via two known connections using PHPMyAdmin.

The issue is that a certain field is nullable in one of the databases and not in the other.

In order to solve this problem, open the two separate PHPMyAdmin connections in different browsers and run the following:

SHOW CREATE TABLE table_name;

review carefully the differences that you find. column3 is very likely nullable in one of the connection and not in the other.

If you see a NOT NULL specified for this field in a connection and not in the other, then you have this exact discrepancy.

In order to sync the field type, you can either alter it to allow null where it was not allowed formerly, by running:

ALTER TABLE table_name MODIFY mycolumn <the type here>;

or, you can make it not nullable where it was previously nullable via

ALTER TABLE table_name MODIFY mycolumn <the type here> NOT NULL;

But be careful, because if you want to alter a field to be nullable, but you already have null values for that field, then you will need to update this field for this table first to specify a not null value where it was null, or use a default:

ALTER TABLE table_name MODIFY mycolumn <the type here> NOT NULL DEFAULT <your default value here>;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.