SQL manager and quirks

[the.rampage.rado]

Recently I started playing with the embedded SQL manager to make queries that are useful in shop maintanance.

I noticed that my products contain information for language ids that are not currently installed (or active). I can't remember if I used to have additional languages in this shop instance before.

Currently I have:


But this query:

 

SELECT pl.id_product, pl.id_lang, pl.description_short, LENGTH(pl.description_short) as short_description_length
FROM tb_product_lang pl
WHERE LENGTH(pl.description_short) < 50;

pulls the following table:



My question is: as I don't have lang ID 3 and 5 could I safely delete all those records (and also every other record, even if larger than 50 characters) for those IDs? Could I use this query for doing so: 

DELETE pl
FROM tb_product_lang pl
LEFT JOIN tb_lang l ON pl.id_lang = l.id_lang
WHERE l.active = 0 OR l.id_lang IS NULL;

If I understand it correctly even if I had let's say Spanish as ID=3 and I remove it and I add it again it will get the next unused langID, not return to 3? In this case why deleting the language is also not deleting all records associated with this ID (products, image descriptions, features, etc.)?

[Yabber]

In thirtybees, the delete() method in the Language class has an bug and does not delete in the database the records of the deleted language.
Below is the properly working code from PS 8:

// Database translations deletion
$result = Db::getInstance()->executeS('SHOW TABLES FROM `' . _DB_NAME_ . '`');
$tableNameKey = 'Tables_in_' . _DB_NAME_;
foreach ($result as $row) {
    if (empty($row[$tableNameKey]) || !preg_match('/_lang$/', $row[$tableNameKey])) {
        continue;
    }
    $columns = Db::getInstance()->executeS('SHOW COLUMNS FROM `' . $row[$tableNameKey] . '`');
    $idLangColumnExists = false;
    foreach ($columns as $column) {
        if ($column['Field'] == 'id_lang') {
            $idLangColumnExists = true;
        }
    }
    if ($idLangColumnExists === false) {
        continue;
    }
    Db::getInstance()->execute('DELETE FROM `' . $row[$tableNameKey] . '` WHERE `id_lang` = ' . (int) $this->id);
}
Db::getInstance()->execute('DELETE FROM ' . _DB_PREFIX_ . 'tag WHERE id_lang = ' . (int) $this->id);
Db::getInstance()->execute('DELETE FROM ' . _DB_PREFIX_ . 'search_word WHERE id_lang = ' . (int) $this->id);

If you already deleted a language, then repairing the method will not help anything anymore. You have to manually clear the database.

1. Open your SSH terminal application and login to your server.

2. Ensure you use your actual credentials for the following variables:
hostname - your mysql hostname (for example: localhost)
user - your database username
database - your database name
password - your database username's password

3. Run the following command:
mysql -h hostname -u user -p database

4. Run this query to get lang tables (change "database" with your database name):

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database' AND table_name LIKE '%_lang';

5. Copy the table names into a text editor.

6. Run this query to delete unused language data:

DELETE FROM tb_attachment_lang WHERE id_lang = 3 OR id_lang = 5;
DELETE FROM tb_attribute_lang WHERE id_lang = 3 OR id_lang = 5;
DELETE FROM tb_attribute_group_lang WHERE id_lang = 3 OR id_lang = 5;
DELETE FROM tb_cart_rule_lang WHERE id_lang = 3 OR id_lang = 5;
DELETE FROM tb_cms_lang WHERE id_lang = 3 OR id_lang = 5;

(and so on the other tables)