[Done 1.6.0] Insertion of an additional language does not copy existing category/prod

For ZC1.54 there are new function calls to log admin activity scattered about the admin files.

In languages.php there is one after the insertion of a new language.

But, on the following line, the code uses the last insert id as the new language id to auto-populate the new categories and products texts. So now it's using the last insert of the admin activity log instead the languages table!

So although the category/product descriptions are created, they are linked to a language id of "whatever" ("64" in my test case) and so never display any text.

      > $db->Execute("insert into " . TABLE_LANGUAGES . "

                    (name, code, image, directory, sort_order)
                    values ('" . zen_db_input($name) . "', '" . zen_db_input($code) . "',
                            '" . zen_db_input($image) . "', '" . zen_db_input($directory) . "',
                            '" . zen_db_input($sort_order) . "')");

zen_record_admin_activity('Language [' . $code . '] added', 'info');

      $insert_id = $db->Insert_ID();

and subsequently

      while (!$categories->EOF) {
        $db->Execute("insert into " . TABLE_CATEGORIES_DESCRIPTION . "
                      (categories_id, language_id, categories_name,
                      categories_description)
                      values ('" . (int)$categories->fields['categories_id'] . "', '" . **(int)$insert_id** . "',
                              '" . zen_db_input($categories->fields['categories_name']) . "',
                              '" . zen_db_input($categories->fields['categories_description']) . "')");          
        $categories->MoveNext();
      }

Correct.

The solution is to reverse these 2 lines:

/admin/languages.php, at line 30:

          zen_record_admin_activity('Language [' . $code . '] added', 'info');


          $insert_id = $db->Insert_ID();
```becomes:

      $insert_id = $db->Insert_ID();

      zen_record_admin_activity('Language [' . $code . '] added', 'info');

I confirm when installing a new language in ZC-v1.5.4-12302014, the language_id reported in order_status, category_description,... is incorrect. Need to publish a small 1.5.4 update as it is an annoying bug.

gob33:

I confirm when installing a new language in ZC-v1.5.4-12302014, the language_id reported in order_status, category_description,... is incorrect. Need to publish a small 1.5.4 update as it is an annoying bug.

Does this problem occur after applying the fix posted above by Dr. Byte? Meaning, apply fix, install language, make purchase, does problem persist?

You cannot apply the fix and try again, you have to revert to the prior database backup, apply the fix and try again.

As this sort of bug is pretty unexpected at this stage of the game I would imagine that people find this thread after the damage has been been done.
So while its fine to sit on your high horse (as I usually do) and say, "ok just use the backup you did prior to installing the language" I think it also would be a good thing to offer an sql repair patch to put things back as they were prior to the faulty insert, if that is possible, for those that did not have a recent backup,
or, probably easier, a patch to set the new language id everywhere to what it should be.

Remember, for many people the additional language is not additional, its default and essential and this bug is a real show stopper.

torvista:

You cannot apply the fix and try again, you have to revert to the prior database backup, apply the fix and try again.

As this sort of bug is pretty unexpected at this stage of the game I would imagine that people find this thread after the damage has been been done.
So while its fine to sit on your high horse (as I usually do) and say, "ok just use the backup you did prior to installing the language" I think it also would be a good thing to offer an sql repair patch to put things back as they were prior to the faulty insert, if that is possible, for those that did not have a recent backup,
or, probably easier, a patch to set the new language id everywhere to what it should be.

Remember, for many people the additional language is not additional, its default and essential and this bug is a real show stopper.

Agree, which is/should be the next step assuming that this thread was "found" in the sequence described... The poster was not clear about the condition of the code when the problem was identified, so was/am unsure if this is the same problem carried through or a new/unrelated problem.

REPAIR PATCH to CLEAN UP DAMAGED DATA

torvista:

I think it also would be a good thing to offer an sql repair patch
I've considered 2 strategies for that:
a) Fix the data to what it should have been. But, the downside to this is it that if someone has attempted multiple "adds", then there's junk left that can't be cleaned up so easily.
b) Just remove anything that doesn't have a matching valid languages_id, and then remove the now-orphaned language entry, effectively resetting it to before any failed attempt to add a language.

While I have a set of queries for the first one, I've opted for the second one as I think it's cleaner.

Please test this on your various sites to confirm whether it resets the damage.
It can be run in phpMyAdmin or the SQL Patch Tool:
*(Be sure to first select a valid language for your current Admin session, and NOT the one you're trying to delete/clean.)
*```
DELETE FROM products_description where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM categories_description where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM meta_tags_products_description where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM meta_tags_categories_description where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM products_options where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM products_options_values where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM manufacturers_info where languages_id not in (
SELECT languages_id
FROM languages );
DELETE FROM orders_status where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM coupons_description where language_id not in (
SELECT languages_id
FROM languages );
DELETE FROM languages where languages_id not in (
SELECT language_id
FROM orders_status );
ALTER TABLE languages AUTO_INCREMENT = 1;

... and then you can re-install the language you were wanting to install, after applying the patch from my previous post.

This patch worked ok, comparing databases.

1. Is there no way to reset the auto-increment on the languages table?
   Otherwise this will leave the languages ids as 1 and 3.

2. I found one oddity in this.
   If you add the language eg. spanish then change the admin language to spanish, then run the sql patch, and re-add spanish, the product descriptions are not generated for the (now) 3rd id.
   So, make sure you are working in english in the admin.

Best is to restore a backup of course!

Good points.
I've updated my post above with an ALTER TABLE for auto-increment and a note to choose a valid language before running it.

The ALTER TABLE query is based on the assumption that you're using MyISAM format for the MySQL table (since that's what Zen Cart uses for that table by default). If you've changed the schema to use InnoDB instead, you'll need to change the 1 to the desired number since InnoDB can't autocalculate the next value. One could use a max() calculation too, but there's no need since there's a 0.00001% chance this is even relevant.

DrByte:

Good points.
I've updated my post above with an ALTER TABLE for auto-increment and a note to choose a valid language before running it.

The ALTER TABLE query is based on the assumption that you're using MyISAM format for the MySQL table (since that's what Zen Cart uses for that table by default). If you've changed the schema to use InnoDB instead, you'll need to change the 1 to the desired number since InnoDB can't autocalculate the next value. One could use a max() calculation too, but there's no need since there's a 0.00001% chance this is even relevant.

thank you very much, it help me