Re: '0000-00-00' is an invalid date

From: bill <william_at_TechServSys.com>
Date: Tue, 6 Feb 2018 06:50:20 -0500
Message-ID: <p5c4pt$17da$1_at_gioia.aioe.org>

On 2/5/2018 8:19 AM, The Natural Philosopher wrote:
> On 05/02/18 12:56, bill wrote:

>> On 2/4/2018 9:52 PM, Peter H. Coffin wrote:
>>> On Sun, 4 Feb 2018 15:27:40 -0500, bill wrote:
>>>
>>>> On 2/4/2018 10:52 AM, Peter H. Coffin wrote:
>>>>
>>>>> Two routes:
>>>>>
>>>>> a) Turn zero dates on in your SQL Mode then turn them back 
>>>>> off again
>>>>> when you've fixed this. (If you want them off -- leaving 
>>>>> them on will
>>>>> PROBABLY get you the behavior you were expecting in the 
>>>>> beginning.
>>>>> But it's your database, so you're the one that has to live 
>>>>> with any
>>>>> unexpected consequences of a permanent mode change now.)
>>>>
>>>> It appears that in the OS upgrade I went from 5.5.59 to 
>>>> 5.7.21 which
>>>> changed the mode so that NO_ZERO_DATE AND NO_ZERO_IN_DATE 
>>>> were set.
>>>>
>>>> As you suggested I want to set the mode to turn off 
>>>> NO_ZERO_DATE, but
>>>> can't see how. doing SET GLOBAL sql_mode ='' would probably 
>>>> cause lots
>>>> of changes I don't want. How does one set just one mode off?
>>>
>>> All that stuff, what the defaults are, how to turn the stuff 
>>> on and off
>>> dynamically while the server is running, how to determine what 
>>> modes
>>> are at in effect at the time are all at the top of the 
>>> reference manual
>>> pages for 5.7. The suggestion I made to read through that 
>>> whole section
>>> wasn't just me being snarky; it actually contains the 
>>> information you
>>> needed to go down the path.
>>>
>>> https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
>>>
>> I know your aren't.  I did read the whole section, but I can't 
>> figure out how to turn _one_ mode off.  I suppose I could turn 
>> off all modes and turn back on the others.
>>
>> I would actually like to put the changes in to the my.conf 
>> (where the section says they can be put, but the format of the 
>> my.conf entry is not clear.
>>
>> Any assistance would be appreciated.
>>

> https://stackoverflow.com/questions/2317650/setting-global-sql-mode-in-mysql
>
>
> Mysql manual is always totally impenetrable. It says what is, not
> how to get there :-)
>
> Looks like /etc/mysql/* is where its easiest to hit it
>
> My very recent installation shows no sql_mode set at all
>
> Exmining the table information_schema.routines shows that
> sql_mode is a massive empty varchar so presumably it takes a
> comma sperated list.
>
> Best to read it edit it and update it I guess, or set the thing
> in a master mysql.cnf file
I will play with it. My recent installation also has an empty my.cnf.

-bill Received on Tue Feb 06 2018 - 12:50:20 CET