• From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Topic: Install Errors

Doing a new install, I'm getting these errors:

Debug mode database warning:
error code : 1067
error message : Invalid default value for 'tran_date'
sql that failed was : CREATE TABLE IF NOT EXISTS `0_attachments` ( `id` int(11) unsigned NOT NULL auto_increment, `description` varchar(60) NOT NULL default '', `type_no` int(11) NOT NULL default '0', `trans_no` int(11) NOT NULL default '0', `unique_name` varchar(60) NOT NULL default '', `tran_date` date NOT NULL default '0000-00-00', `filename` varchar(60) NOT NULL default '', `filesize` int(11) NOT NULL default '0', `filetype` varchar(60) NOT NULL default '', PRIMARY KEY (`id`), KEY `type_no` (`type_no`,`trans_no`) ) ENGINE=MyISAM AUTO_INCREMENT=1



Debug mode database warning:
error code : 1067
error message : Invalid default value for 'gl_date'
sql that failed was : CREATE TABLE IF NOT EXISTS `0_audit_trail` ( `id` int(11) NOT NULL auto_increment, `type` smallint(6) unsigned NOT NULL default '0', `trans_no` int(11) unsigned NOT NULL default '0', `user` smallint(6) unsigned NOT NULL default '0', `stamp` timestamp NOT NULL, `description` varchar(60) default NULL, `fiscal_year` int(11) NOT NULL, `gl_date` date NOT NULL default '0000-00-00', `gl_seq` int(11) unsigned default NULL, PRIMARY KEY (`id`), KEY `Seq` (`fiscal_year`,`gl_date`,`gl_seq`), KEY `Type_and_Number` (`type`,`trans_no`) ) ENGINE=InnoDB AUTO_INCREMENT=1


and so on for every table including a default date value of '0000-00-00'......


SQL script execution failed in line 42:

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

Newer versions of MySql don't like the value '0000-00-00' as default values. We are looking into this.
In the meantime you can load your account sql file into an editor. Make a replace of all the '0000-00-00' values with '1001-01-01'. Save your sql file and make another try. It should now work.

Joe

  • From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Re: Install Errors

Where do I find the account.sql file?

  • From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Re: Install Errors

I edited the en_us-demo.sql and en_us-new.sql and now I'm getting this result:

Debug mode database warning:
error code : 1067
error message : Invalid default value for 'last_reconciled_date'
sql that failed was : CREATE TABLE `0_bank_accounts` ( `account_code` varchar(15) NOT NULL default '', `account_type` smallint(6) NOT NULL default '0', `bank_account_name` varchar(60) NOT NULL default '', `bank_account_number` varchar(100) NOT NULL default '', `bank_name` varchar(60) NOT NULL default '', `bank_address` tinytext, `bank_curr_code` char(3) NOT NULL default '', `dflt_curr_act` tinyint(1) NOT NULL default '0', `id` smallint(6) NOT NULL auto_increment, `last_reconciled_date` timestamp NOT NULL default '1001-01-01 01:01:01', `ending_reconcile_balance` double NOT NULL default '0', `inactive` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), KEY `bank_account_name` (`bank_account_name`), KEY `bank_account_number` (`bank_account_number`), KEY `account_code` (`account_code`) ) ENGINE=MyISAM AUTO_INCREMENT=3



Debug mode database warning:
error code : 1146
error message : Table 'acct.0_bank_accounts' doesn't exist
sql that failed was : INSERT INTO `0_bank_accounts` VALUES ('11000', '0', 'Current account', 'N/A', 'N/A', NULL, 'USD', '0', '1', '1001-01-01 01:01:01', '0', '0')



Debug mode database warning:
error code : 1146
error message : Table 'acct.0_bank_accounts' doesn't exist
sql that failed was : INSERT INTO `0_bank_accounts` VALUES ('10200', '0', 'Petty Cash account', 'N/A', 'N/A', NULL, 'USD', '0', '2', '1001-01-01 01:01:01', '0', '0')



SQL script execution failed in line 79:

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

It seemed not to be a good idea to change the '0000-00-00' to '1001-01-01'. Please replace the value back to '0000-00-00' again.

Then go into phpMyAdmin for your database and enter (in the SQL form):

SET sql_mode = '';

using SELECT @@sql_mode; should report an empty value.

Then retry your operation and see if this helps.

Please report back. Thanks in advance.

/Joe

  • From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Re: Install Errors

after using the two SQL Queries I get:







Number of rows: 2550100250500 



+ Options
































@@sql_mode 



STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_...

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

I guess you are using a MySql release greater than 5.6. These versions have been more restrict in handling SQL.
Please try to downgrade your MySql to 5.5 or less.

We are investigating this in the meantime.

Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

@crspenser

Please download the file, /includes/db/connect_db.inc and replace this file on your FA installation.
After doing that, you should do your operations without problems.
Please report back.

/Joe

Post's attachments

connect_db.inc 5.5 kb, 15 downloads since 2014-05-02 

You don't have the permssions to download the attachments of this post.
  • From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Re: Install Errors

Now when I install, it finishes with a blank blue screen.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

Please try to help a little more. Look into your /tmp/error.log.
Also look into the mysql server log.

There must be something else wrong in your setup.

Joe

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

Hello again.

I have now created a new test Environment

MySql 5.6.17
PHP 5.5.11
Server Type Microsoft-IIS/8.5
Server System WINNT
Frontaccounting 2.3.20

I have tested around and I found no problems at all. Especially your issues Went without problems.

/Joe

  • From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Re: Install Errors

It creates all the tables in the database, but ends with a blank white screen in IE.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Install Errors

I am sorry, I cannot help you any more. I don't know where to go from here.
Have you tried to make a fresh install from the download Place at sourceforge?

Have any other had a similar problem?

/Joe

  • From: USA, Ohio
  • Registered: 09/10/2013
  • Posts: 36

Re: Install Errors

Tried the install a few more times, it worked on the 3rd try.  No clue why.

  • Registered: 08/24/2016
  • Posts: 2

Re: Install Errors

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

i have this message on Step 5: Company Settings
I am a beginner please help me with this i tried everything.

16 Reply by apmuthu (edited by apmuthu 08/24/2016 02:05:25 pm)

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

Re: Install Errors

You are using a newer version of MySQL where the 0000-00-00 (and it's timestamp equivalent) is not considered as valid anymore by default. Run MySQL with strict mode turned off and all will be well. This issue has been discussed in the forum more than once if I remember right.

Alternatively, edit your chart of accounts to replace all instances of "0000-00-00" to "0001-01-01" and then install your company.

apmuthu's Website

  • Registered: 08/24/2016
  • Posts: 2

Re: Install Errors

yes for disabling strict mode i run just this command

SET @@global.sql_mode= '';

and it worked for me ..

Thanks.

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

Re: Install Errors

@sheik_salman: Thanks for the feedback.

The attachment in the 8th post in this thread suggests: 

SET @@global.sql_mode= 'TRADITIONAL';

.

apmuthu's Website