Need help configuring MySQL PHPMyAdmin 5.7.11

Hi,

I have a new PHPMyAdmin installation and having hard time with sql-mode
MySQL version 5.7.11

Here is the default settings
sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

Open in new window

and this is putting zero value everywhere
I do prefer not having zero when field is empty.

I have tried
 sql-mode="" 

Open in new window

and restart the service and still cannot save empty date or INT

I do not want to use STRICT MODE

I'm trying to find out but I don't know what to choose
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

I'm using PDO & InnoDB


Thanks
LVL 28
lenamtlAsked:
Who is Participating?
 
lenamtlConnect With a Mentor Author Commented:
Hi,

here is a sample

contact_owner_id and contact_expiration can be empty

I have tried both

CREATE TABLE `as_contact` (
  `contact_id` int(11) UNSIGNED NOT NULL,
  `contact_owner_id` int(11) UNSIGNED DEFAULT NULL,
  `contact_expiration` date DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


and

CREATE TABLE `as_contact` (
  `contact_id` int(11) UNSIGNED NOT NULL,
  `contact_owner_id` int(11) UNSIGNED NOT NULL,
  `contact_expiration` date NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window

0
 
Julian HansenCommented:
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
0
 
lenamtlAuthor Commented:
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"

Open 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?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Julian HansenCommented:
How have you defined your table?
0
 
lenamtlAuthor Commented:
Not sure what you mean by defined table
InnoDB
0
 
Julian HansenCommented:
You have a table you are trying to insert data into - what does that look like - can you post the CREATE for it.
0
 
Julian HansenCommented:
What do you get when you run these queries

SELECT @@sql_mode;

Open in new window

SHOW VARIABLES LIKE "%version%";

Open in new window

0
 
lenamtlAuthor Commented:
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
0
 
Julian HansenCommented:
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.
0
 
lenamtlAuthor Commented:
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
0
 
Julian HansenCommented:
What happens if you restore it to what it was?

Did you install the server or is this on a hosted server.
0
 
lenamtlAuthor Commented:
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 ;)
0
 
Julian HansenCommented:
Ok so this is a server you setup yourself.

Was this part of the default install - is what I am trying to establish?
0
 
lenamtlAuthor Commented:
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
0
 
Julian HansenCommented:
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.
0
 
lenamtlAuthor Commented:
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"

Open in new window


I have tried
 sql-mode=" " 

Open in new window



and now I'm using this

 sql-mode=" NO_AUTO_CREATE_USER" 

Open in new window


I have also tried to commented
 #sql-mode=""

Open in new window


What else do you need I haven't change anything else?
0
 
Julian HansenCommented:
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?
0
 
lenamtlAuthor Commented:
yes correct
0
 
Julian HansenCommented:
Ok, let me see if I can replicate.

Did you use the standard WAMP distribution or one of the VM's (Bitnami etc)
0
 
lenamtlAuthor Commented:
Standard 64 bit wampserver.com
0
 
Julian HansenConnect With a Mentor Commented:
Here are my findings

Environment
Windows 2012 clean install
Installed VC Runtimes for 2008,2010,2012,2013
Installed KB2999226
Installed VC Runtime for 2015
Installed WAMP (wampserver3.0.4_x64_apache2.4.18_mysql5.7.11_php5.6.19-7.0.4.exe)
Installed Apache service and started it
Browsed to localhost
Selected PHP MyAdmin
Created a Database [test]
Selected [test]
Opend SQL tab and pasted following code  [corrected - removed extra ,]  from this post
CREATE TABLE `as_contact` (
  `contact_id` int(11) UNSIGNED NOT NULL,
  `contact_owner_id` int(11) UNSIGNED NOT NULL,
  `contact_expiration` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window

Modified Structure on the above to allow NULL's
Selected SQL tab
Ran this Query
INSERT INTO `as_contact`(`contact_id`) VALUES (1)

Open in new window

Result - see image below.
How do I replicate what you are getting?
ss58.jpg
0
 
Julian HansenCommented:
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?
0
 
lenamtlAuthor Commented:
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=""

Open 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=""

Open 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.
0
 
Julian HansenCommented:
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.
0
 
lenamtlAuthor Commented:
Don't worry the field is set to NULL ;)
0
 
lenamtlAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.