Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help configuring MySQL PHPMyAdmin 5.7.11

Posted on 2016-08-25
26
Medium Priority
?
85 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 61

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 27

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 61

Expert Comment

by:Julian Hansen
ID: 41771709
How have you defined your table?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 27

Author Comment

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

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 27

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 61

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 27

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 61

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 27

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 61

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 27

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 61

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 27

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 61

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 27

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 61

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 27

Author Comment

by:lenamtl
ID: 41772962
yes correct
0
 
LVL 61

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 27

Author Comment

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

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 61

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 27

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 61

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 27

Author Comment

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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
Course of the Month14 days, 13 hours left to enroll

578 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