*[Done v1.6.0] Attributes controller fatal error after upgrade

Following upgrade to v1.51 I get the following error:

PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, 1, 'TEXT')' at line 1 :: INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, 1, 'TEXT') in /home/myserver/public_html/mydomain.com/includes/classes/db/mysql/query_factory.php on line 120

when accessing either:

attributes controller
option names manager
option values manager

If I temporarily use the v1.50 php files for these 3 sources the problem does not occur. Looking at the difference between the two versions I think that v1.51 is checking for and finding errors in the database that v1.50 could not.

Any clue to where I should be looking is appreciated.

Did you upgrade the database also?

Darn it, no.

That'll teach me not to read the upgrade instructions - I'll try it now

Thank you

That should get it

Make the following changes:

Line 40:
/your_secret_admin_dir/attributes controller.php

    if ((int)$languages[$i]['id'] > 0 && !in_array((int)$languages[$i]['id'], $ary)) {
//      $db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, " . (int)$languages[$i]['id'] . ", 'TEXT')");
      $db->Execute("INSERT INTO " . TABLE_PRODUCTS_OPTIONS_VALUES . " (products_options_values_id, language_id, products_options_values_name) VALUES (" . (int)PRODUCTS_OPTIONS_VALUES_TEXT_ID . ", " . (int)$languages[$i]['id'] . ", 'TEXT')");
    }

line 23:
/your_secret_admin_dir/option names manager.php

    if ((int)$languages[$i]['id'] > 0 && !in_array((int)$languages[$i]['id'], $ary)) {
//      $db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, " . (int)$languages[$i]['id'] . ", 'TEXT')");
      $db->Execute("INSERT INTO " . TABLE_PRODUCTS_OPTIONS_VALUES . " (products_options_values_id, language_id, products_options_values_name) VALUES (" . (int)PRODUCTS_OPTIONS_VALUES_TEXT_ID . ", " . (int)$languages[$i]['id'] . ", 'TEXT')");
    }

line 28:
/your_secret_admin_dir/option values manager.php

    if ((int)$languages[$i]['id'] > 0 && !in_array((int)$languages[$i]['id'], $ary)) {
//      $db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, " . (int)$languages[$i]['id'] . ", 'TEXT')");
      $db->Execute("INSERT INTO " . TABLE_PRODUCTS_OPTIONS_VALUES . " (products_options_values_id, language_id, products_options_values_name) VALUES (" . (int)PRODUCTS_OPTIONS_VALUES_TEXT_ID . ", " . (int)$languages[$i]['id'] . ", 'TEXT')");
    }

NOTE: Moved to Bug Reports

EDIT: just seen Ajeh's post, so ignore the text below

Ok, I've upgraded the dbase as per upgrade instructions. I'm still getting the same error.

As part of my customisation I had added a new column to my database to products_options_values table. Now, I did this with little SQL knowledge so I wonder whether that could be what's causing the problem?

That's it, all seems ok now.

Cheers

Thanks for the update that this worked for you ...

It is one of those little typos on a built in fix to the database for a check of a missing record, and this bug will not affect very many many people at all, as they have no reason for this fix to run ...

Glad that you are set now ... :smile:

Works for me - just in time too!

i did this and now i get this

[14-Oct-2012 18:23:34] PHP Fatal error: 1062uplicate entry '0-1' for key 'PRIMARY' :: INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES (0, 1, 'TEXT') /public_html/includes/classes/db/mysql/query_factory.php on line 120

In theory, that probably means the table:
products_options_values

has no records ...

Using phpMyAdmin, please confirm that this table has no records in it ...

Then, if it is empty, run the following in the SQL:

DROP TABLE IF EXISTS products_options_values;
CREATE TABLE products_options_values (
  products_options_values_id int(11) NOT NULL default '0',
  language_id int(11) NOT NULL default '1',
  products_options_values_name varchar(64) NOT NULL default '',
  products_options_values_sort_order int(11) NOT NULL default '0',
  PRIMARY KEY (products_options_values_id,language_id),
  KEY idx_products_options_values_name_zen (products_options_values_name),
  KEY idx_products_options_values_sort_order_zen (products_options_values_sort_order)
) ENGINE=MyISAM;

This will remake this table ...

Now see if the fix works for your site ...

You will know the fix worked if:

1 you get no error message

2 if you browse that table again, after going to the:
Option Name Manager
Option Value Manager
Attributes Controller

you get no errors and you see in there a row, per language that you are using, the setting for the:
products_options_values_name

is set to TEXT ...

it has options in it because i have working attributes on our website

Just wanted to add that Ajeh's original fixes for the 3 files worked for me.

gwynwyffar:

Just wanted to add that Ajeh's original fixes for the 3 files worked for me.

yep i made those changes and im still having issues.

attributes_controller.php

$chk_option_values->MoveNext();

}
$languages = zen_get_languages();
for ($i=0, $n=sizeof($languages); $i<$n; $i ++) {
if ((int)$languages[$i]['id'] > 0 && !in_array((int)$languages[$i]['id'], $ary)) {
// $db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, " . (int)$languages[$i]['id'] . ", 'TEXT')");
$db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES (" . (int)PRODUCTS_OPTIONS_VALUES_TEXT_ID . ", " . (int)$languages[$i]['id'] . ", 'TEXT')");
}
}

require(DIR_WS_CLASSES . 'currencies.php');

options_name_manager.php

$chk_option_values->MoveNext();

}
for ($i=0, $n=sizeof($languages); $i<$n; $i ++) {
if ((int)$languages[$i]['id'] > 0 && !in_array((int)$languages[$i]['id'], $ary)) {
// $db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, " . (int)$languages[$i]['id'] . ", 'TEXT')");
$db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES (" . (int)PRODUCTS_OPTIONS_VALUES_TEXT_ID . ", " . (int)$languages[$i]['id'] . ", 'TEXT')");
}
}

$action = (isset($_GET['action']) ? $_GET['action'] : '');

options_values_manager.php

PRODUCTS_OPTIONS_VALUES_TEXT_ID);
while (!$chk_option_values->EOF) {
$ary[] = $chk_option_values->fields['language_id'];
$chk_option_values->MoveNext();
}
$languages = zen_get_languages();
for ($i=0, $n=sizeof($languages); $i<$n; $i ++) {
if ((int)$languages[$i]['id'] > 0 && !in_array((int)$languages[$i]['id'], $ary)) {
// $db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES ((int)PRODUCTS_OPTIONS_VALUES_TEXT_ID, " . (int)$languages[$i]['id'] . ", 'TEXT')");
$db->Execute("INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES (" . (int)PRODUCTS_OPTIONS_VALUES_TEXT_ID . ", " . (int)$languages[$i]['id'] . ", 'TEXT')");
}
}

$action = (isset($_GET['action']) ? $_GET['action'] : '');

let me know if i did something wrong

I want to bring something up i have a prefix zen_ so does that mean this file is looking in the wrong section??

Using $db->Execute instead of plain native PHP db functions should take the table prefix into account. Alternatively, using the ZC constants for table names automatically includes the prefix in the table name.

In any case, you should not need to edit core ZC code for db table name interactions unless Ajeh or DrByte say so.

lucidlee:

Works for me - just in time too!

ok so where did i go wrong?

did i mess up the input of the code as shown above or is all that correct?

How many languages do you use?

Are you getting the same error messages debug files or different ones after making those changes?

Ajeh:

How many languages do you use?

Are you getting the same error messages debug files or different ones after making those changes?

Just english.

same error message
[16-Oct-2012 13:48:30] PHP Fatal error: 1062:Duplicate entry '0-1' for key 'PRIMARY' :: INSERT INTO products_options_values (products_options_values_id, language_id, products_options_values_name) VALUES (0, 1, 'TEXT') in /home2/pwcforum/public_html/includes/classes/db/mysql/query_factory.php on line 120

Argh.

There's another bug. The original fix didn't take table-prefixes into account.

The fix is now included in Ajeh's post earlier: http://www.zen-cart.com/showthread.php?200551-OPEN-CHANGE-352-Attributes-controller-fatal-error-after-upgrade&p=1153132#post1153132

This situation didn't show up in initial testing because the testing database didn't have two sets of data in it like Dashizna's does. (you should clean that up, BTW)