WampServer

Windows version used - Windows 7 64bit
2 - Version WampServer - version 3 64bit
3 - Apache Version - 2.4.17
4 - PHP Version - 5.6.15
5 - MySQL Version - 5.7.9 - MySQL Community Server
6 - What color is the WampServer icon (in the notification area of the taskbar - Green

My problem may not be related to WAMP but to my PHP code. But I want to rule this out while I hunt for a php issue. I can log into the website I'm testing which is installed on WAMP. I have a registration page. When I test the login and hit the submit button I get this error:

There was an error running the query [Incorrect datetime value: '0000-00-00 00:00:00' for column 'last_visit_date' at row 1]

I have the same code on a live system (not on WAMP) and there are no issues there. Can you advise if this is in fact a WAMP issue.

thanks

Hi,

It is a MySQL issue for versions 5.7.x

In wamp/bin/mysql/mysql5.7.9/my.ini file, comment (add # at the beginning) the line :

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

But warning, in future release of mysql, these modes will be included by default.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Thanks, but no joy. Did as you suggested and still getting the same error. This is what I see in the my.ini file

# Set the SQL mode to strict
#sql-mode="STRICT_ALL_TABLES"
#sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

(no line break after #sql-)

I also see a couple of lines above these two that says -

#To avoid warning messages
secure_file_priv="C:/wamp/tmp"
skip-ssl

explicit_defaults_for_timestamp=true

What happens if i make timestamp=false

Hi,

Did you have restart Wampserver after modifying my.ini file ?

One should not modify a configuration file to chance!
You have to go see the relevant documentation. The links on them are in my signature, that of RiggsFolly and on the home page of Wampserver "localhost".

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Having simlar problem with Mysql 5.7.9 in Wamp 3.0
On Insert setting a timestamp or datetime field to null as documented in the manual causes a error "cannot be null" when doing the insert. Tried setting sql-mode as defined above but no joy. My quick workaround in Wamp 3 was to copy over Mysql 5.6.12 from my prior Wamp installation.

If I fix my code and DB structure in 5.7.9 the logic fails in older <5.7.? versions of Mysql since they only allow one default field value set to "current datetime" . I need to have code that works with versions 5.4 thru 5.7.9 .

Hopefully someone has an easy MySQL my.ini workaround. I don't want to change my code in 40+ scripts to set the timestamp before doing the insert.

Thank you

PS Yes I'm aware I should change timestamp to datetime to avoid the 2038 problem.

Hi,

in my.ini file set

sql-mode=""

If there is no line sql-mode="" modes are default modes.

Il you want to put null or 0000-00-00 or 2015-00-00 in a date field without modifiying your scripts you can also ALTER date type by char(10). This is that I did.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons



Edited 1 time(s). Last edit at 04/08/2016 07:07PM by Otomatic.

Please see

[dev.mysql.com]

And

[dev.mysql.com]

These are MYSQL changes and nothing specifically to do with WAMPServer

---------------------------------------------------------------------------------------------
(Windows 10 Pro 64bit) (Wampserver 3.3.4 64bit) Aestan Tray Menu 3.2.5.4
<Apache versions MULTIPE> <PHP versions MULTIPLE> <MySQL Versions MULTIPLE>
<MariaDB versions MULTIPLE> <phpMyAdmin versions MULTIPLE> <MySQL Workbench 8.0.23>
Read The Manuals Apache -- MySQL -- PHP -- phpMyAdmin
Get your Apache/MySQL/mariaDB/PHP ADDONs here from the WAMPServer alternate Repo
-X-X-X- Backup your databases regularly Here is How dont regret it later! Yes even when developing -X-X-X-

Thank you for the responses. I was aware this is a MySQL issue, however there are some very smart people on this forum and I thought perhaps someone may have the magic settings for my.ini allowing my code to function without change. I've done a lot of tedious reading about the MYSql timestamp changes, but no matter what I tried, I get an insert sql error in 5.7.9 stating that the field cannot be null.

So although the MYSQL documentation for 5.7 (and older) states a timestamp field set to null on an insert or update statement sets a timestamp, and this worked until at least 5.6.12, my easiest solution for now is to change my code:
from
timestamp_field=NULL
to
timestamp_field=NOW()

This works in 5.6.12, 5.7.9 and hopefully all future and prior releases. It also requires no database structure changes, but does involve going through a lot of 8 year old code.
.
This a Mysql bug report about this issue
[bugs.mysql.com]

From [dev.mysql.com]
"In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values."

Update
Tested much of my code and although most of the other tables have an identical timestamp structure (added and last update) to the original failing table, inserts worked normally with 5.7.9



Edited 1 time(s). Last edit at 04/09/2016 08:10PM by arnb.