Need help configuring MySQL PHPMyAdmin 5.7.11

Why do you need to change / set the mode?

You can read more in the 5.7 FAQ here https://dev.mysql.com/doc/refman/5.7/en/faqs-sql-modes.html

Because  it save all empty value for INT as 0
and empty date with 0 and this can't save NULL values even if null is enable

and if I try to set an INT manually (phpmyadmin) to empty or NULL it's give me INT format error

*Now I am able to save manually empty INT
by setting the sql-mode to this

sql-mode="NO_AUTO_CREATE_USER"

Select allOpen in new window

But still save empty as 0 not NULL
I guess I can live with that but this is not correct to me

I'm still not able to save empty date manually in phpmyadmin
and not able to save it as 0000-00-00 too I've got invalid date format
it request a valid date format when empty or 0!

This is important because those fields can be save as empty and this will required the validation to be changed let say I want to check if the field is empty now I have to check if equal 0 or not.

I'm using wampserver on windows 7 64bit.

Anyone is facing the same problem?

How have you defined your table?

Not sure what you mean by defined table
InnoDB

You have a table you are trying to insert data into - what does that look like - can you post the CREATE for it.

What do you get when you run these queries

SELECT @@sql_mode;

Select allOpen in new window

SHOW VARIABLES LIKE "%version%";

Select allOpen in new window

NO_AUTO_CREATE_USER

innodb_version
5.7.11
protocol_version
10
slave_type_conversions
tls_version
TLSv1,TLSv1.1
version
5.7.11
version_comment
MySQL Community Server (GPL)
version_compile_machine
x86_64
version_compile_os
Win64

Was this the bit that came out for sql_mode?

NO_AUTO_CREATE_USER

I have 5.6 but will install 5.7 and try to replicate.

Yes

the default values was
sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

and I kept only NO_AUTO_CREATE_USER
after I have read some info on the web

What happens if you restore it to what it was?

Did you install the server or is this on a hosted server.

I'm using Wampserver
Before it was the 32 bit version, now I need to use the 64bit, I cannot switch back to 32 bit.

That is certainly a  way to fixt that, I'm not the only one that need to save empty values ;)

Ok so this is a server you setup yourself.

Was this part of the default install - is what I am trying to establish?

I'm using Wampserver http://www.wampserver.com/en

I just tested on 32 bit version and previous PHPmyAdmin 5.6.17
and same problem occurred.

INT only save INT 0 , no NULL, no empty,
Warning: #1366 Incorrect integer value

for date I got an error if date is empty
Warning: #1265 Data truncated for column

Both are set to NULL

I am trying to find out if in either / both of these situations you are dealing with the default (out of the box) install or was some configuration change made after installation.

I am using x86_64 5.6.20 - and I do not have the problems you are having. My sql_mode is empty when I query it as per the above instructions.

To move forward we need to be comparing apples with apples. If I want to replicate your installation I am still in the dark as to how to do that as you have not answered my questions with respect to default setup.

I already gave you the default values was

sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

Select allOpen in new window

I have tried

 sql-mode=" " 

Select allOpen in new window

and now I'm using this

 sql-mode=" NO_AUTO_CREATE_USER" 

Select allOpen in new window

I have also tried to commented

 #sql-mode=""

Select allOpen in new window

What else do you need I haven't change anything else?

We are at cross purposes.

I just need to confirm

a) You downloaded a WAMP package
b) You installed that
c) You did not change anything else - you used the default installation

You are experiencing the issue based on the above - is that correct?

yes correct

Ok, let me see if I can replicate.

Did you use the standard WAMP distribution or one of the VM's (Bitnami etc)

Standard 64 bit wampserver.com

Having been through that I am confused - your table structure is specifically configured with all fields being NOT NULL - how are those fields ever supposed to get a NULL value?

No the fields of my table fields are really set to NULL.

After a lot of Google search (Mysql strict mode problem)
 I have found a lot of people having the same problem and their solution was to set MySQL

sql-mode=""

Select allOpen in new window

then restart the services .

Which I have done with no luck, it was still not allow NULL values from PHPMyADMIN.
Then I have completely shut down my PC and restart and now it is working...

What have realized is when you restart the services this won't end the PHPMyADMIN session...
So I guess this was the culprit.

So to fix it
set my.ini sql-mode to empty

sql-mode=""

Select allOpen in new window

Stop the services
Restart the computer (make sure  PHPMyADMIN session is ended).

Otherwise it kept the strict mode in  PHPMyADMIN and won't allow NULL for INT and DATE  even if the field is set to NULL in DB

Thanks for your patience.

You are welcome.

Still trying to understand how you manage to insert NULL into a field that was created NOT NULL - but glad it is working.

Don't worry the field is set to NULL ;)

sql-mode="" need to be empty
I have found that the PC need to be restarted to take the new PHPMyADMIN mysql configuration value.
Only restart the service was not enough.