Possible bugs in message command when saving to DB

[atrandafir]

What steps will reproduce the problem?

First part - Set up messages and make them save to DB

https://www.yiiframework.com/doc/api/2.0/yii-i18n-dbmessagesource

common/config/messages.php

<?php

return [
	...
    'languages' => array_keys([
		'es' => 'Español',
		'ca' => 'Català',
		'en' => 'English',
	]),
    'removeUnused' => true,
    'markUnused' => true,
    ...
    // 'db' output format is for saving messages to database.
    'format' => 'db',
    ...
];

Also make sure you create the required tables by running the migration as instructed in API link.

Now add some translations inside some php files in your application:

<p><?php echo Yii::t('app', 'This is my first translation string'); ?></p>

Then run the messages command with your configuration file:

yii message common/config/messages.php

Until here, everything should have worked as expected and the translations are also working fine.

If you look into the DB you have both the source messages and the translation messages (NULL) rows inserted. You just have to edit the translations rows and add your translations.

Second part

This is where the bugs occur.

Add a new language into your configuration file:

<?php

return [
	...
    'languages' => array_keys([
		'es' => 'Español',
		'ca' => 'Català',
		'en' => 'English',
                'af' => 'Afrikaans', // <-- new language here
	]),
    ...
];

And also add a new translation string somewhere inside your application:

<p><?php echo Yii::t('app', 'This is a new translation string'); ?></p>

Now run the messages command again:

yii message common/config/messages.php

What is the expected result?

• I expect the command to insert the new string in the source table
• I expect the command to insert the NULL translation into the message table
• I expect the command to not break
• I expect the command, if run at a later time, to check and rebuild all the missing rows if the data is incomplete
• I expect the command to consider obsolete the translations of a language that is no longer used and mark/delete them

What do you get instead?

• The command inserts the new source message
• The command inserts the NULL translation ONCE
• The command then tries to insert another NULL translation for the same key (id-lang) because it considers the new added language as a missing language
• I get a MySQL FK error and the remaining queries get cancelled and never get the chance to run so the messages table becomes incomplete
• Also if I remove a language and run the command, the rows in the messages table are not removed

2023-07-31 12:43:37 [-][-][-][error][yii\db\IntegrityException] PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '366-af' for key 'PRIMARY' in /var/>
Stack trace:
#0 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute()
#1 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute()
#2 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/console/controllers/MessageController.php(421): yii\db\Command->execute()
#3 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/console/controllers/MessageController.php(331): yii\console\controllers\MessageController->saveMessagesToDb()
#4 [internal function]: yii\console\controllers\MessageController->actionExtract()
#5 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array()
#6 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams()
#7 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/console/Controller.php(180): yii\base\Controller->runAction()
#8 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/base/Module.php(552): yii\console\Controller->runAction()
#9 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction()
#10 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction()
#11 /var/www/vhosts/example.com/httpdocs/vendor/yiisoft/yii2/base/Application.php(384): yii\console\Application->handleRequest()
#12 /var/www/vhosts/example.com/httpdocs/yii(23): yii\base\Application->run()
#13 {main}

Additional info

Q	A
Yii version	2.0.48.1
PHP version	8.1.19
Operating system	Windows 11

[samdark]

Do you have a fix for it?

[atrandafir]

@samdark yes I have created this PR #19934

Let me know what you think, the code has been reorganized into a different structure but keeping "the good" and adapting to fix the bugs and be more flexible.

[OndrejVasicek]

Hi @atrandafir ,
it seems it's the same bug as I started here #17532
I haven't balls to fix it, it seems you have. Thank you.
Is it going to be merged?
I looked through your new code, I understand it and it seems right to me, but I'm not confident enough saying that there isn't any hidden bug.
I also tested it on my broken translations and it worked

UPDATING DB

SOURCE MESSAGE TABLE

Inserting new messages...1 saved.
Updating obsoleted messages...1 updated.


MESSAGE TABLE

Generating missing rows...1 for cs, 3 for sk, 2 for en, 1 for de, 2 for pl, 2 for it, 2 for es, 2 for sv.
Dropping unused languages...Nothing to do.

[atrandafir]

Hi @OndrejVasicek indeed it is the same bug as your issue.

And indeed the code block was a bit hairy to understand and fix, and also the logic involved.

But I think the way it works right now should be good and we shouldn't get any surprise.

I tried to think of most of the use cases so hopefully we should have everything covered.

But maybe in the future if this is ever rewritten, listing all the use cases and maybe do some testing would be a good idea in order make sure no new bugs appear.