Jump to content
Sign in to follow this  
vyoufinder

1067 - Invalid default value for 'qbi_config_added'

Recommended Posts

After updating to be able to use mySQL5 and php5, I get the following error when I try to restore my database through the osC control panel. I will get the same error if I take the backup .sql file from /admin/backup/ and try to import through phpMyAdmin. So I am pretty sure it's exporting incorrectly, rather than it being an import problem. I don't know what is wrong. I searched all my files for "qbi_config_added" and find nothing, anywhere. Any help out there?

 

 

 

1067 - Invalid default value for 'qbi_config_added'

 

create table qbi_config ( qbi_config_id smallint(5) unsigned not null auto_increment, qbi_config_ver decimal(6,2) unsigned default '0.00' not null , qbi_qb_ver smallint(5) unsigned default '2003' not null , qbi_dl_iif tinyint(2) unsigned default '1' not null , qbi_prod_rows smallint(5) unsigned default '5' not null , qbi_log tinyint(2) unsigned default '0' not null , qbi_status_update tinyint(2) unsigned default '0' not null , qbi_cc_status_select tinyint(3) unsigned default '1' not null , qbi_mo_status_select tinyint(3) unsigned default '1' not null , qbi_email_send tinyint(2) unsigned default '0' not null , qbi_cc_clear tinyint(2) unsigned default '0' not null , orders_status_import int(11) default '1' not null , orders_docnum varchar(36) default '%I' not null , orders_ponum varchar(36) default '%I' not null , cust_nameb varchar(41) default '�0W-%I' not null , cust_namer varchar(41) default '%L10W-%I' not null , cust_limit int(10) unsigned default '0' not null , cust_type varchar(48) not null , cust_state tinyint(2) unsigned default '1' not null , cust_country tinyint(2) unsigned default '0' not null , cust_compcon tinyint(2) unsigned default '1' not null , cust_phone tinyint(2) unsigned default '0' not null , invoice_acct varchar(30) default 'Accounts Receivable' not null , invoice_salesacct varchar(30) default 'Undeposited Funds' not null , invoice_toprint tinyint(2) unsigned default '1' not null , invoice_pmt tinyint(2) unsigned default '0' not null , invoice_termscc varchar(30) not null , invoice_terms varchar(30) not null , invoice_rep varchar(41) not null , invoice_fob varchar(13) not null , invoice_comments tinyint(2) unsigned default '1' not null , invoice_message varchar(128) not null , invoice_memo varchar(128) not null , item_acct varchar(30) not null , item_asset_acct varchar(30) default 'Inventory Asset' not null , item_class varchar(30) not null , item_cog_acct varchar(30) default 'Cost of Goods Sold' not null , item_osc_lang tinyint(2) unsigned default '0' not null , item_match_inv tinyint(2) unsigned default '1' not null , item_match_noninv tinyint(2) unsigned default '0' not null , item_match_serv tinyint(2) unsigned default '0' not null , item_default tinyint(2) unsigned default '0' not null , item_default_name varchar(40) not null , item_import_type tinyint(2) unsigned default '0' not null , item_active tinyint(2) unsigned default '0' not null , ship_acct varchar(30) not null , ship_name varchar(30) not null , ship_desc varchar(36) not null , ship_class varchar(30) not null , ship_tax tinyint(2) unsigned default '0' not null , tax_on tinyint(2) unsigned default '0' not null , tax_lookup tinyint(2) unsigned default '0' not null , tax_name varchar(30) not null , tax_agency varchar(30) not null , tax_rate float default '0' not null , pmts_memo varchar(128) not null , prods_sort tinyint(2) unsigned default '0' not null , prods_width smallint(5) unsigned default '48' not null , qbi_config_active tinyint(2) default '0' not null , qbi_config_added timestamp default 'CURRENT_TIMESTAMP' not null , PRIMARY KEY (qbi_config_id) )

Edited by vyoufinder

"Your focus is your reality"

Share this post


Link to post
Share on other sites
I get the following error when I try to restore my database through the osC control panel.

 

1067 - Invalid default value for 'qbi_config_added'

If you edit your backup with a text editor you should remove the quotes around CURRENT_TIME_STAMP in the create table statement of the table qbi_config (the one you posted):

qbi_config_added timestamp default CURRENT_TIMESTAMP not null ,

Share this post


Link to post
Share on other sites
If you edit your backup with a text editor you should remove the quotes around CURRENT_TIME_STAMP in the create table statement of the table qbi_config (the one you posted):

qbi_config_added timestamp default CURRENT_TIMESTAMP not null ,

 

Thanks for your prompt reply! However, I've already managed to restore the database.. I am trying to make it work for future use since I'm not the one managing the catalog, etc. From what you posted, I gether that my export command is adding those single quotes and is causing the problem.. but there's another problem, I can't even find anywhere in the script the string of characters: qbi_config_added and CURRENT_TIME_STAMP, CURRENT_TIMESTAMP and the only one found is CURRENT_TIMESTAMP, which is in EasyPopulate only. so I don't know where to check and see about making it not add the quotes. Any ideas?

Edited by vyoufinder

"Your focus is your reality"

Share this post


Link to post
Share on other sites

It is in the last line of your first post.


Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Share this post


Link to post
Share on other sites
It is in the last line of your first post.

 

Yes, I see but what file is generating this error, which contains that last line? I can't figure out where it's coming from. I guess another way of asking is how do I make my tools>backup not generate the quotes around that CURRENT_TIMESTAMP?

 

I need to be able to backup AND restore through the control panel without using phpMyAdmin every time an deleting quotes..

Edited by vyoufinder

"Your focus is your reality"

Share this post


Link to post
Share on other sites
Yes, I see but what file is generating this error, which contains that last line? I can't figure out where it's coming from.

It is in the create table statement created by the osC backup manager that adds quotes around the default value. Unfortunately, that causes an error when the CURRENT_TIMESTAMP is the default (line 57 in admin/backup.php):

			if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';

If you replace lines 47 to 100 in backup.php:

		  $schema = 'drop table if exists ' . $table . ';' . "\n" .
				'create table ' . $table . ' (' . "\n";

	  $table_list = array();
	  $fields_query = tep_db_query("show fields from " . $table);
	  while ($fields = tep_db_fetch_array($fields_query)) {
		$table_list[] = $fields['Field'];

		$schema .= '  ' . $fields['Field'] . ' ' . $fields['Type'];

		if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';

		if ($fields['Null'] != 'YES') $schema .= ' not null';

		if (isset($fields['Extra'])) $schema .= ' ' . $fields['Extra'];

		$schema .= ',' . "\n";
	  }

	  $schema = ereg_replace(",\n$", '', $schema);

// add the keys
	  $index = array();
	  $keys_query = tep_db_query("show keys from " . $table);
	  while ($keys = tep_db_fetch_array($keys_query)) {
		$kname = $keys['Key_name'];

		if (!isset($index[$kname])) {
		  $index[$kname] = array('unique' => !$keys['Non_unique'],
								 'fulltext' => ($keys['Index_type'] == 'FULLTEXT' ? '1' : '0'),
								 'columns' => array());
		}

		$index[$kname]['columns'][] = $keys['Column_name'];
	  }

	  while (list($kname, $info) = each($index)) {
		$schema .= ',' . "\n";

		$columns = implode($info['columns'], ', ');

		if ($kname == 'PRIMARY') {
		  $schema .= '  PRIMARY KEY (' . $columns . ')';
		} elseif ( $info['fulltext'] == '1' ) {
		  $schema .= '  FULLTEXT ' . $kname . ' (' . $columns . ')';
		} elseif ($info['unique']) {
		  $schema .= '  UNIQUE ' . $kname . ' (' . $columns . ')';
		} else {
		  $schema .= '  KEY ' . $kname . ' (' . $columns . ')';
		}
	  }

	  $schema .= "\n" . ');' . "\n\n";
	  fputs($fp, $schema);

with:

		  $schema = 'drop table if exists ' . $table . ';' . "\n";
	  $create_table_query = tep_db_query("show create table " . $table);
	  $create_table_result = mysql_fetch_array($create_table_query, MYSQL_NUM);
	  $create_table = $create_table_result[1];
	  $create_table_str_length = strlen($create_table);
	  $pos_closing_bracket = strrpos($create_table, ')');
// remove " ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1" like stuff
	  $create_table = substr($create_table, 0, $pos_closing_bracket+1);
	  $schema .= $create_table . ';' . "\n\n";
	  fputs($fp, $schema);

You shouldn't have that problem (although IMHO it is advisable to make backups through cPanel (if available) or phpMyAdmin instead of the osC admin).

Edited by Jan Zonjee

Share this post


Link to post
Share on other sites

Although I did not do exactly what you said.. I did manage to get it working. I could not find the section of code you pointed out to change but I could find because mine was slightly different and was missing two lines from what you said to change FROM. So the first thing I did was merge the differences and try it, before replacing with your siuggested code. Bam! It works!

 

Here was my solution, though I am not sure which solution is the best, yours or the one I used that worked. I have not tried yours yet.

 

Replace:

 

$schema = 'drop table if exists ' . $table . ';' . "\n" .
				'create table ' . $table . ' (' . "\n";

	  $table_list = array();
	  $fields_query = tep_db_query("show fields from " . $table);
	  while ($fields = tep_db_fetch_array($fields_query)) {
		$table_list[] = $fields['Field'];

		$schema .= '  ' . $fields['Field'] . ' ' . $fields['Type'];

		if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';

		if ($fields['Null'] != 'YES') $schema .= ' not null';

		if (isset($fields['Extra'])) $schema .= ' ' . $fields['Extra'];

		$schema .= ',' . "\n";
	  }

	  $schema = ereg_replace(",\n$", '', $schema);

// add the keys
	  $index = array();
	  $keys_query = tep_db_query("show keys from " . $table);
	  while ($keys = tep_db_fetch_array($keys_query)) {
		$kname = $keys['Key_name'];

		if (!isset($index[$kname])) {
		  $index[$kname] = array('unique' => !$keys['Non_unique'],
								 'columns' => array());
		}

		$index[$kname]['columns'][] = $keys['Column_name'];
	  }

	  while (list($kname, $info) = each($index)) {
		$schema .= ',' . "\n";

		$columns = implode($info['columns'], ', ');

		if ($kname == 'PRIMARY') {
		  $schema .= '  PRIMARY KEY (' . $columns . ')';
		} elseif ($info['unique']) {
		  $schema .= '  UNIQUE ' . $kname . ' (' . $columns . ')';
		} else {
		  $schema .= '  KEY ' . $kname . ' (' . $columns . ')';
		}
	  }

	  $schema .= "\n" . ');' . "\n\n";
	  fputs($fp, $schema);

 

With:

 

$schema = 'drop table if exists ' . $table . ';' . "\n" .
				'create table ' . $table . ' (' . "\n";

	  $table_list = array();
	  $fields_query = tep_db_query("show fields from " . $table);
	  while ($fields = tep_db_fetch_array($fields_query)) {
		$table_list[] = $fields['Field'];

		$schema .= '  ' . $fields['Field'] . ' ' . $fields['Type'];

		if (strlen($fields['Default']) > 0) $schema .= ' default \'' . $fields['Default'] . '\'';

		if ($fields['Null'] != 'YES') $schema .= ' not null';

		if (isset($fields['Extra'])) $schema .= ' ' . $fields['Extra'];

		$schema .= ',' . "\n";
	  }

	  $schema = ereg_replace(",\n$", '', $schema);

// add the keys
	  $index = array();
	  $keys_query = tep_db_query("show keys from " . $table);
	  while ($keys = tep_db_fetch_array($keys_query)) {
		$kname = $keys['Key_name'];

		if (!isset($index[$kname])) {
		  $index[$kname] = array('unique' => !$keys['Non_unique'],
								 'fulltext' => ($keys['Index_type'] == 'FULLTEXT' ? '1' : '0'),
								 'columns' => array());
		}

		$index[$kname]['columns'][] = $keys['Column_name'];
	  }

	  while (list($kname, $info) = each($index)) {
		$schema .= ',' . "\n";

		$columns = implode($info['columns'], ', ');

		if ($kname == 'PRIMARY') {
		  $schema .= '  PRIMARY KEY (' . $columns . ')';
		} elseif ( $info['fulltext'] == '1' ) {
		  $schema .= '  FULLTEXT ' . $kname . ' (' . $columns . ')';
		} elseif ($info['unique']) {
		  $schema .= '  UNIQUE ' . $kname . ' (' . $columns . ')';
		} else {
		  $schema .= '  KEY ' . $kname . ' (' . $columns . ')';
		}
	  }

	  $schema .= "\n" . ');' . "\n\n";
	  fputs($fp, $schema);

 

I am guessing I should try to stick with what's closest to the default osc code, so thinking my solution is actually preferred... but if you know differently that your suggestion will be better for me and for anyone else having this problem, please say so. I searched online and found several people having the same error, but no solutions posted so I am thinking a lot of troubleshooters will read this post.

Edited by vyoufinder

"Your focus is your reality"

Share this post


Link to post
Share on other sites
I am guessing I should try to stick with what's closest to the default osc code, so thinking my solution is actually preferred...

I see absolutely zero difference between your "solution" and the standard osC code, so I guess you made a mistake in your post.

However, if you feel Harald knows better how to write a create table statement than your mysql database itself then that's fine with me.

Share this post


Link to post
Share on other sites
I see absolutely zero difference between your "solution" and the standard osC code, so I guess you made a mistake in your post.

However, if you feel Harald knows better how to write a create table statement than your mysql database itself then that's fine with me.

 

 

There actually IS a difference and I will attempt to make it clearer for anyone:

 

$index[$kname] = array('unique' => !$keys['Non_unique'],

'columns' => array());

}

 

$index[$kname]['columns'][] = $keys['Column_name'];

}

 

while (list($kname, $info) = each($index)) {

$schema .= ',' . "\n";

 

$columns = implode($info['columns'], ', ');

 

if ($kname == 'PRIMARY') {

$schema .= ' PRIMARY KEY (' . $columns . ')';

} elseif ($info['unique']) {

$schema .= ' UNIQUE ' . $kname . ' (' . $columns . ')';

} else {

$schema .= ' KEY ' . $kname . ' (' . $columns . ')';

}

}

 

$schema .= "\n" . ');' . "\n\n";

fputs($fp, $schema);

 

 

 

 

 

 

Gets changed to:

 

 

 

 

 

$index[$kname] = array('unique' => !$keys['Non_unique'],

'fulltext' => ($keys['Index_type'] == 'FULLTEXT' ? '1' : '0'),

'columns' => array());

}

 

$index[$kname]['columns'][] = $keys['Column_name'];

}

 

while (list($kname, $info) = each($index)) {

$schema .= ',' . "\n";

 

$columns = implode($info['columns'], ', ');

 

if ($kname == 'PRIMARY') {

$schema .= ' PRIMARY KEY (' . $columns . ')';

} elseif ( $info['fulltext'] == '1' ) {

$schema .= ' FULLTEXT ' . $kname . ' (' . $columns . ')';

} elseif ($info['unique']) {

$schema .= ' UNIQUE ' . $kname . ' (' . $columns . ')';

} else {

$schema .= ' KEY ' . $kname . ' (' . $columns . ')';

}

}

 

$schema .= "\n" . ');' . "\n\n";

fputs($fp, $schema);

 

Bold is what I added.

Edited by vyoufinder

"Your focus is your reality"

Share this post


Link to post
Share on other sites
There actually IS a difference and I will attempt to make it clearer for anyone:

 

Bold is what I added.

Interesting. That is exactly what Harald added in revision 1274 and is at least in RC1, RC2, and RC2a.

 

I fail to see the connection with your problem but I guess you have given this more thought than I did.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×