• aRTx
  • Member
  • Offline
  • From: Albania
  • Registered: 10/29/2014
  • Posts: 47

Topic: I try to install new copy of FA2.3.25, there is a bug!

I try to install new copy of FrontAccounting 2.3.25 in local server WAMP 3.0
but there is an error and stop installing on step 5.

It shows it:

SQL script execution failed in line 42: Invalid default value for 'tran_date'

2 Reply by apmuthu (edited by apmuthu 05/16/2016 05:19:30 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: I try to install new copy of FA2.3.25, there is a bug!

When posting information on an error you should post the choices made in the preceeding form. Assuming you chose the en_US-new.sql Chart of Accounts in the previous form, line 42 in sql/en_US-new.sql is:

  `tran_date` date NOT NULL default '0000-00-00',

Some recent versions of MySQL do not allow '0000-00-00' as a valid date field value. Try removing all such constructs (default '0000-00-00') from the said sql file and re-try the installation after emptying the db. Alternatively set strict mode off in MySQL.

The following links provide further info:
https://dev.mysql.com/doc/refman/5.6/en/datetime.html
http://stackoverflow.com/questions/25349126/how-can-i-set-the-default-value-of-a-field-as-0000-00-00-000000
http://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime

The error is because of the sql mode which can be strict mode as per latest MYSQL 5.7 documentation

MySQL Documentation 5.7 says:

    Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

To Check MYSQL mode

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

There are a total of 31 places in the en_US-new.sql file where the said construct is there.

There are many places in the php codebase where the equivalent of the following constructs are available:

if ($date == 0) $date = '0000-00-00';

and

        $sql .= " tran_date > IF(ctype>0 AND ctype<".CL_INCOME.", '0000-00-00', '$from_date') AND";

Hence it is better to turn the strict mode off in the mysql and not tamper with the Charts sql.

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: I try to install new copy of FA2.3.25, there is a bug!

Disable Strict Mode in MySQL 5.7 and beyond:

https://support.kayako.com/article/472-how-do-i-disable-mysql-strict-mode-on-the-server
https://mattstauffer.co/blog/how-to-disable-mysql-strict-mode-on-laravel-forge-ubuntu

MySQL actually looks five different places for configuration files, so you can make the change I'm about to recommend several places. It'll look in

/etc/my.cnf,
/etc/mysql/my.cnf,
SYSCONFDIR/my.cnf,
$MYSQL_HOME/my.cnf, and
~/my.cnf.

The last one above is user-specific, and the third and fourth options rely on specifics from your environment.

In the my.cnf file, use the following:

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then restart MySQL - in Debian / Ubuntu it is:

/etc/init.d/mysql restart

apmuthu's Website