CODEWITHSUNDEEP
mysqlruby-on-railsdate
legendary_rob
legendary_rob

Reputation: 13002

MySQL allowing invalid dates to be saved

i am writing a rails app into which i'll save dates. right now i am trying to replicate a bug where false dates are being saved. but whenever i tried saving then i'd get the following error,

Incorrect date value: '10256-05-05' for column 'transaction_date' at row 1

so i figured that my MySQL has something different to it. i am running Server version: 5.6.11 and then came across this tutorial and it mentioned about the MySQL setting ALLOW_INVALID_DATES. and then i found this question with some similar error here. so i opened MySQL and ran 

SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

to which i get this 

mysql> SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

but when i exit i and open sequel pro and try saving an incorrect date it gives me the middle finger again. i have created a my.cnf file in /etc/

but i have no idea what goes in there. i think thats where i should stick something like ALLOW_INVALID_DATES so that it perpetuates. but i am scared of messing with these files. ?? help please :)

Upvotes: 2

Views: 5637

Answers (2)

Douglas A. Seifert
Douglas A. Seifert

Reputation: 51

If you are on OS X and using brew's plist for launchctl (basically, if you have done ln -sf /usr/local/opt/mysql/homebrew.mxcl.mysql.plist ~/Library/LaunchAgents) to start the mysql server, I had to edit the plist file like the below to make it start the mysql server process with sql_mode set to a blank. I tried to change various my.cnf files to take the sql_mode variable setting, but it never worked for me.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>KeepAlive</key>
  <true/>
  <key>Label</key>
  <string>homebrew.mxcl.mysql</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/opt/mysql/bin/mysqld_safe</string>
    <string>--bind-address=127.0.0.1</string>
    <string>--sql-mode= </string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>WorkingDirectory</key>
  <string>/usr/local/var</string>
</dict>
</plist>

Upvotes: 0

legendary_rob
legendary_rob

Reputation: 13002

I tried a few things i eventually reinstalled the mysql2 gem and then reinstalled the latest mySQL via home brew so that it was a clean install. i then opened sql and 

SET sql_mode = '';

Set it as this ^

Then re-opened it and changed the mode to this.

set global sql_mode="NO_ENGINE_SUBSTITUTION";

I can now save invalid dates. These will appear as 0000-00-00 within the db, with no exceptions being raised.

Upvotes: 4

Related Questions