Duplicate entry when trying to add product with two level category neither existing

[brittainmark]

Zen cart 1.5.8 Apache/2.4.41 (Ubuntu) Database client version: libmysql - mysqlnd 8.0.20 PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation PHP version: 8.0.20 Database Server version: 8.0.29-0ubuntu0.20.04.3 - (Ubuntu)

Trying to add new product with new two tier product categories. "Ty Rhos Jewellery^Pendants" the first category "Ty Rhos Jewellery" is added. The second category fails with duplicate entry

log file

MySQLi error 1062: Duplicate entry '65' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                65, '', 65, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '65-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 65,
                    language_id     = 1,
                    categories_name = 'Pendants',
                    categories_description = ''
MySQLi error 1062: Duplicate entry '65' for key 'categories.PRIMARY'
When executing:
INSERT INTO categories(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                65, '', 65, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )
MySQLi error 1062: Duplicate entry '65-1' for key 'categories_description.PRIMARY'
When executing:
INSERT INTO categories_description SET
                    categories_id   = 65,
                    language_id     = 1,
                    categories_name = 'Pendants',
                    categories_description = ''

the categories table has Next autoindex =65

This was used for the first category.

The issue appears to be that the Autoindex does not get incremented if you supply the index key.

Not sure if this is an issue with mysql.

I get a similar issue if I add two products having created the categories first.

for categories easypopulate_4_import.php 1087-1103

              $sql = "SHOW TABLE STATUS LIKE '" . TABLE_CATEGORIES . "'";
              $result = ep_4_query($sql);
              unset($sql);
              $row = $ep_4_fetch_array($result);
              unset($result);
              $max_category_id = $row['Auto_increment'];
              // if database is empty, start at 1
              if (!isset($max_category_id) || !is_numeric($max_category_id) || $max_category_id == 0) {
                $max_category_id = 1;
              }
              // TABLE_CATEGORIES has 1 entry per categories_id
              $sql = "INSERT INTO " . TABLE_CATEGORIES . "(categories_id, categories_image, parent_id, sort_order, date_added, last_modified
                ) VALUES (
                :categories_id:, '', :parent_id:, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                )";
              $sql = $db->bindVars($sql, ':categories_id:', $max_category_id, 'integer');
              $sql = $db->bindVars($sql, ':parent_id:', $theparent_id, 'integer');

Could either add in check for max value "SELECT max(categories_id) AS max_id FROM " . TABLE_CATEGORIES and compare with $max_category_id and use the biggest. Or remove the categories_id from the insert and get the insert id from the insert query. $current_category_id = ($ep_uses_mysqli ? mysqli_insert_id($db->link) : mysql_insert_id());

I don't know if this is an issue with my database? If it is, I don't know how to solve it. The database was created using the default data from zc_install.

[brittainmark]

Just created a new database. Same issue.

[mc12345678]

Entering some notes here to potentially further support what has been found to be a mySQL 8.x "feature".

I had looked at this issue upon notification and had begun making changes to resolve; however, don't recall having found the below.

First, yes I agree retrieving the insert_id likely would be best, the problem/concern I have is if in running the EP4 query if additional code exists to insert data to a table, then the retrieved insert_id would be against a different insert. The query code could also internally obtain that information and pass it back. I'm not sure that I want to go that path though. Even if may be a viable solution.

As far as other notes, the implementation uses data that gets cached and therefore not updated as expected. This is discussed here: https://stackoverflow.com/questions/52705477/mysql-show-table-status-auto-increment-is-not-correct

Considering resetting information_schema_status_expiry to 0 at the entire beginning of processing all code and then returning to whatever value was originally set.

Another thought to the two or three you had would be to do the insert without categories_id and then retrieve the largest categories_id, but to that also, I'm trying not to cause another database query where possible.

In my recent/previous testing, just returning to always checking for the max categories_id and adding 1 was working. As identified in the comments though, this runs the possibility of that particular id matching up with some other table's data if when deleting the categories_id that table wasn't properly touched. This happens if the largest categories_id is the one deleted, as then the largest value may be that one or it could be something much smaller...