[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Need help configuring MySQL PHPMyAdmin 5.7.11

Posted on 2016-08-25
26
Medium Priority
?
65 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 13
26 Comments
 
LVL 59

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 26

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 59

Expert Comment

by:Julian Hansen
ID: 41771709
How have you defined your table?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 26

Author Comment

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

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 26

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 59

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 26

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 59

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 26

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 59

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 26

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 59

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
 
LVL 26

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 59

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 26

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 59

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 26

Author Comment

by:lenamtl
ID: 41772962
yes correct
0
 
LVL 59

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 26

Author Comment

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

Assisted Solution

by:Julian Hansen
Julian Hansen earned 2000 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 59

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 26

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 59

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 26

Author Comment

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

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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