Solved

Need help configuring MySQL PHPMyAdmin 5.7.11

Posted on 2016-08-25
26
28 Views
Last Modified: 2016-09-03
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
0
Comment
Question by:lenamtl
  • 13
  • 13
26 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41770938
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41771557
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41771709
How have you defined your table?
0
 
LVL 24

Author Comment

by:lenamtl
ID: 41771794
Not sure what you mean by defined table
InnoDB
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41771807
You have a table you are trying to insert data into - what does that look like - can you post the CREATE for it.
0
 
LVL 24

Accepted Solution

by:
lenamtl earned 0 total points
ID: 41771850
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41771998
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41772036
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41772262
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41772357
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41772416
What happens if you restore it to what it was?

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

Author Comment

by:lenamtl
ID: 41772862
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41772868
Ok so this is a server you setup yourself.

Was this part of the default install - is what I am trying to establish?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 24

Author Comment

by:lenamtl
ID: 41772890
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41772905
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41772935
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41772958
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41772962
yes correct
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41773004
Ok, let me see if I can replicate.

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

Author Comment

by:lenamtl
ID: 41773419
Standard 64 bit wampserver.com
0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 500 total points
ID: 41774717
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41774721
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41774915
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
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 41775073
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
 
LVL 24

Author Comment

by:lenamtl
ID: 41775127
Don't worry the field is set to NULL ;)
0
 
LVL 24

Author Closing Comment

by:lenamtl
ID: 41782784
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now