Modify ↓

Opened 3 years ago

Closed 3 years ago

#2441 closed Bug/Fehler (fixed)

Importieren bei Artikeleigenschaften führt zu Error und Warnings

Reported by: noRiddle Owned by: somebody
Priority: hoch Milestone: modified-shop-2.0.8.0
Component: Admin Version: 2.0.7.2
Keywords: Cc:
Blocked By: Blocking:

Description

Beim Importieren von Artikeleigenschaften, wenn man dabei "Optionswerte als Eigenschaften-Werte importieren?" aktiviert, kommt es zu folgendem Error und den folgenden Warnings:

[2023-02-06 11:03:58]	[error]	[modified]	[pid:4007071]	ERROR found for URL: /admin/products_tags.php?page=1&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:58]	[error]	[modified]	[pid:4007071]	Duplicate entry '1-1' for key 'PRIMARY' in File: PATH/inc/db_functions_mysqli.inc.php on Line: 174	{}	{}

[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	WARNING found for URL: /admin/products_tags.php?page=0&action=import_attributes	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	Undefined variable $oInfo in File: PATH/admin/products_tags.php on Line: 744	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	WARNING found for URL: /admin/products_tags.php?page=0&action=import_attributes	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	Attempt to read property "options_id" on null in File: PATH/admin/products_tags.php on Line: 744	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	WARNING found for URL: /admin/products_tags.php?page=0&action=import_attributes	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	Undefined variable $oInfo in File: PATH/admin/products_tags.php on Line: 753	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	WARNING found for URL: /admin/products_tags.php?page=0&action=import_attributes	{}	{}
[2023-02-06 11:02:50]	[warning]	[modified]	[pid:4007071]	Attempt to read property "options_id" on null in File: PATH/admin/products_tags.php on Line: 753	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007072]	WARNING found for URL: /admin/products_tags.php?page=0&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007072]	Undefined variable $hidden_fields in File: PATH/admin/products_tags.php on Line: 421	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007072]	WARNING found for URL: /admin/products_tags.php?page=0&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007072]	Undefined variable $oInfo in File: PATH/admin/products_tags.php on Line: 759	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007072]	WARNING found for URL: /admin/products_tags.php?page=0&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007072]	Attempt to read property "options_id" on null in File: PATH/admin/products_tags.php on Line: 759	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007070]	WARNING found for URL: /admin/products_tags.php?page=1&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007070]	Undefined variable $hidden_fields in File: PATH/admin/products_tags.php on Line: 421	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007070]	WARNING found for URL: /admin/products_tags.php?page=1&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007070]	Undefined variable $oInfo in File: PATH/admin/products_tags.php on Line: 759	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007070]	WARNING found for URL: /admin/products_tags.php?page=1&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:57]	[warning]	[modified]	[pid:4007070]	Attempt to read property "options_id" on null in File: PATH/admin/products_tags.php on Line: 759	{}	{}
[2023-02-06 11:03:58]	[warning]	[modified]	[pid:4007071]	WARNING found for URL: /admin/products_tags.php?page=1&oID=&action=import_confirm	{}	{}
[2023-02-06 11:03:58]	[warning]	[modified]	[pid:4007071]	1062 - Duplicate entry '1-1' for key 'PRIMARY'<br/><br/>INSERT INTO products_tags_values (values_id, options_id, values_name, languages_id, date_added, products_options_values_id) VALUES ('1', '1', 'black', '1', now(), '1') in File: PATH/inc/db_functions_mysqli.inc.php on Line: 161	{}	{}
[2023-02-06 11:03:58]	[warning]	[modified]	[pid:4007071]	Backtrace #0 - PATH/inc/db_functions.inc.php called at Line 76	{}	{}
[2023-02-06 11:03:58]	[warning]	[modified]	[pid:4007071]	Backtrace #1 - PATH/admin/products_tags.php called at Line 364	{}	{}

Wenn man vor Import bereits Artikeleigenschaften angelegt hatte ist der "Duplicate entry" dann natürlich bei der ersten nächsten eingetragenen values_id.

Im Dev-Shop getestet und bestätigt.

Habe aus Zeitgründen den Code nicht zu Ende analysiert. Ein testweises 

$values_id = $check['values_id'] + 1;

in Zeile 350 änderte nichts an den Fehlern.

Gruß,
noRiddle

Attachments (0)

Change History (3)

comment:1 by noRiddle, 3 years ago

Hab's mir mal genauer angeschaut.
Die Query

      } elseif (isset($_POST['attributes_value']) && $_POST['attributes_value'] == '1') {
        $attributes_values_query = xtc_db_query("SELECT pov.products_options_values_id,
                                                        pov.products_options_values_name,	
                                                        pov.language_id,
                                                        pov2po.products_options_id,
                                                        pto.options_id
                                                   FROM ".TABLE_PRODUCTS_OPTIONS_VALUES." pov
                                                   JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." pov2po
                                                        ON pov2po.products_options_values_id = pov.products_options_values_id
                                                   JOIN ".TABLE_PRODUCTS_TAGS_OPTIONS." pto
                                                        ON pto.products_options_id = pov2po.products_options_id
                                                  WHERE NOT EXISTS (SELECT 1
                                                                      FROM ".TABLE_PRODUCTS_TAGS_VALUES." ptv
                                                                     WHERE ptv.languages_id = pov.language_id
                                                                       AND ptv.products_options_values_id = pov.products_options_values_id)
                                                  LIMIT ".IMPORT_LIMIT);

benötigt ein DISTINCT, da der JOIN auf TABLE_PRODUCTS_TAGS_OPTIONS die Ergebnisse doppelt, wodurch es dann zu "Duplicate entry for key PRIMARY" kommt.

Allerdings würde ich noch versuchen das Ganze zu optimieren, damit die Queries im while-Loop nicht nötig sind.
Mein Vorschlag wäre die vorhandenen Sprachen zu zählen und die $values_id abhängig von der Anzahl der Sprachen zu definieren:

      } elseif (isset($_POST['attributes_value']) && $_POST['attributes_value'] == '1') {
        $attributes_values_query = xtc_db_query("SELECT DISTINCT pov.products_options_values_id,
                                                        pov.products_options_values_name,	
                                                        pov.language_id,
                                                        (SELECT COUNT(DISTINCT language_id) FROM ".TABLE_PRODUCTS_OPTIONS_VALUES.") as how_many_langs,
                                                        pov2po.products_options_id,
                                                        pto.options_id
                                                   FROM ".TABLE_PRODUCTS_OPTIONS_VALUES." pov
                                                   JOIN ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." pov2po
                                                        ON pov2po.products_options_values_id = pov.products_options_values_id
                                                   JOIN ".TABLE_PRODUCTS_TAGS_OPTIONS." pto
                                                        ON pto.products_options_id = pov2po.products_options_id
                                                  WHERE NOT EXISTS (SELECT 1
                                                                      FROM ".TABLE_PRODUCTS_TAGS_VALUES." ptv
                                                                     WHERE ptv.languages_id = pov.language_id
                                                                       AND ptv.products_options_values_id = pov.products_options_values_id)
                                                  LIMIT ".IMPORT_LIMIT);
        if (xtc_db_num_rows($attributes_values_query) > 0) {
          $tags_vals_cntr = 0;
          while ($attributes_values = xtc_db_fetch_array($attributes_values_query)) {
            $tags_vals_cntr++;
            if($tags_vals_cntr == 1) {
              $next_id_query = xtc_db_query("SELECT max(values_id) as values_id 
                                               FROM ".TABLE_PRODUCTS_TAGS_VALUES);
              $next_id = xtc_db_fetch_array($next_id_query);
              $values_id = $next_id['values_id'] + 1;
            } else {
              if($tags_vals_cntr % $attributes_values['how_many_langs'] == 0) {
                $values_id = $values_id;
              } else {
                $values_id += 1;
              }
            }
            

            $sql_data_array = array('values_id' => $values_id,
                                    'options_id' => $attributes_values['options_id'],
                                    'values_name' => $attributes_values['products_options_values_name'],
                                    'languages_id' => $attributes_values['language_id'],
                                    'date_added' => 'now()',
                                    'products_options_values_id' => $attributes_values['products_options_values_id'],
                                    );
            xtc_db_perform(TABLE_PRODUCTS_TAGS_VALUES, $sql_data_array);
          }
        } else {
          $_POST['attributes_value'] = '0';
        }
      }

Gruß,
noRiddle

comment:2 by noRiddle, 3 years ago

Die COUNT-Query für die Sprachen sollte man vielleicht in eine Extra-Query auslagern, damit sie nicht innerhalb des JOINs der Haupt-Query mehrfach ausgeführt wird.
Die $check_query und die $next_id_query im while-Loop kann man sich mit meinem Vorschlag jedenfalls sparen, bzw. bedarf es lediglich einer einzigen Query im ersten Loop.

Um es um der Übersicht willen nochmals klar zu machen:
Der Grund für den Error ist das fehlende DISTINCT, der Rest in meinem Vorschlag ist Performance-Optimierung.

Gruß,
noRiddle

comment:3 by Gerhard Waldemair, 3 years ago

Resolution: → fixed
Status: new → closed

In 15252:

fix #2441 - prevent duplicate entries

Modify Ticket

Action
as closed The owner will remain somebody.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment
E-mail address and name can be saved in the Preferences .
Attachments ↑ Description ↑
 
Note: See TracTickets for help on using tickets.