Incorrect string value for column 'database'.'searchindex'.'keywords' at row 1

[Saboteur777]

Describe the bug

Updating search indexes is never completed, because the swimmer emoji in the sendout subject.

2019-12-11 09:30:02 [-][-][-][profile end][yii\db\Command::execute] INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, 'subject', 0, 1, ' 🏊 nyilt uszasorak december 12 17 kozott ')
2019-12-11 09:30:02 [-][-][-][error][craft\queue\QueueLogBehavior::afterError]  [28539] Updating search indexes (attempt: 2) - Error (time: 0.014s): SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1
The SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, 'subject', 0, 1, ' 🏊 nyilt uszasorak december 12 17 kozott ')
2019-12-11 09:30:02 [-][-][-][error][yii\db\Exception] PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1 in /var/www/vendor/yiisoft/yii2/db/Command.php:1290
Stack trace:
#0 /var/www/vendor/yiisoft/yii2/db/Command.php(1290): PDOStatement->execute()
#1 /var/www/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('INSERT INTO `se...')
#2 /var/www/vendor/craftcms/cms/src/services/Search.php(399): yii\db\Command->execute()
#3 /var/www/vendor/craftcms/cms/src/services/Search.php(122): craft\services\Search->_indexElementKeywords(6146, 'subject', '0', 1, '\xF0\x9F\x8F\x8A Ny\xC3\xADlt \xC3\xBAs...')
#4 /var/www/vendor/craftcms/cms/src/queue/jobs/UpdateSearchIndex.php(55): craft\services\Search->indexElementAttributes(Object(putyourlightson\campaign\elements\SendoutElement))
#5 /var/www/vendor/yiisoft/yii2-queue/src/Queue.php(214): craft\queue\jobs\UpdateSearchIndex->execute(Object(craft\queue\Queue))
#6 /var/www/vendor/yiisoft/yii2-queue/src/cli/Queue.php(147): yii\queue\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#7 /var/www/vendor/craftcms/cms/src/queue/Queue.php(96): yii\queue\cli\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#8 /var/www/vendor/putyourlightson/craft-campaign/src/console/controllers/SendoutsController.php(38): craft\queue\Queue->run()
#9 [internal function]: putyourlightson\campaign\console\controllers\SendoutsController->actionRunPendingSendouts()
#10 /var/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /var/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /var/www/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('run-pending-sen...', Array)
#13 /var/www/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('run-pending-sen...', Array)
#14 /var/www/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('campaign/sendou...', Array)
#15 /var/www/vendor/craftcms/cms/src/console/Application.php(93): yii\console\Application->runAction('campaign/sendou...', Array)
#16 /var/www/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('campaign/sendou...', Array)
#17 /var/www/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#18 /var/www/craft(22): yii\base\Application->run()
#19 {main}

Next yii\db\Exception: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1
The SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, 'subject', 0, 1, ' 🏊 nyilt uszasorak december 12 17 kozott ') in /var/www/vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 /var/www/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `se...')
#1 /var/www/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('INSERT INTO `se...')
#2 /var/www/vendor/craftcms/cms/src/services/Search.php(399): yii\db\Command->execute()
#3 /var/www/vendor/craftcms/cms/src/services/Search.php(122): craft\services\Search->_indexElementKeywords(6146, 'subject', '0', 1, '\xF0\x9F\x8F\x8A Ny\xC3\xADlt \xC3\xBAs...')
#4 /var/www/vendor/craftcms/cms/src/queue/jobs/UpdateSearchIndex.php(55): craft\services\Search->indexElementAttributes(Object(putyourlightson\campaign\elements\SendoutElement))
#5 /var/www/vendor/yiisoft/yii2-queue/src/Queue.php(214): craft\queue\jobs\UpdateSearchIndex->execute(Object(craft\queue\Queue))
#6 /var/www/vendor/yiisoft/yii2-queue/src/cli/Queue.php(147): yii\queue\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#7 /var/www/vendor/craftcms/cms/src/queue/Queue.php(96): yii\queue\cli\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#8 /var/www/vendor/putyourlightson/craft-campaign/src/console/controllers/SendoutsController.php(38): craft\queue\Queue->run()
#9 [internal function]: putyourlightson\campaign\console\controllers\SendoutsController->actionRunPendingSendouts()
#10 /var/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#11 /var/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#12 /var/www/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('run-pending-sen...', Array)
#13 /var/www/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('run-pending-sen...', Array)
#14 /var/www/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('campaign/sendou...', Array)
#15 /var/www/vendor/craftcms/cms/src/console/Application.php(93): yii\console\Application->runAction('campaign/sendou...', Array)
#16 /var/www/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('campaign/sendou...', Array)
#17 /var/www/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#18 /var/www/craft(22): yii\base\Application->run()
#19 {main}
Additional Information:
Array
(
    [0] => 22007
    [1] => 1366
    [2] => Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1
)

2019-12-11 09:30:02 [-][-][-][info][yii\db\Command::execute] UPDATE `queue` SET `fail`=1, `dateFailed`='2019-12-11 08:30:02', `error`='SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: \'\\xF0\\x9F\\x8F\\x8A n...\' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1\nThe SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, \'subject\', 0, 1, \' 🏊 nyilt uszasorak december 12 17 kozott \')' WHERE `id`='28539'
2019-12-11 09:30:02 [-][-][-][profile begin][yii\db\Command::execute] UPDATE `queue` SET `fail`=1, `dateFailed`='2019-12-11 08:30:02', `error`='SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: \'\\xF0\\x9F\\x8F\\x8A n...\' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1\nThe SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, \'subject\', 0, 1, \' 🏊 nyilt uszasorak december 12 17 kozott \')' WHERE `id`='28539'
2019-12-11 09:30:02 [-][-][-][profile end][yii\db\Command::execute] UPDATE `queue` SET `fail`=1, `dateFailed`='2019-12-11 08:30:02', `error`='SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: \'\\xF0\\x9F\\x8F\\x8A n...\' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1\nThe SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, \'subject\', 0, 1, \' 🏊 nyilt uszasorak december 12 17 kozott \')' WHERE `id`='28539'
2019-12-11 09:30:02 [-][-][-][error][yii\db\Exception] PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`queue`.`error` at row 1 in /var/www/vendor/yiisoft/yii2/db/Command.php:1290
Stack trace:
#0 /var/www/vendor/yiisoft/yii2/db/Command.php(1290): PDOStatement->execute()
#1 /var/www/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('UPDATE `queue` ...')
#2 /var/www/vendor/craftcms/cms/src/queue/Queue.php(378): yii\db\Command->execute()
#3 /var/www/vendor/yiisoft/yii2-queue/src/Queue.php(216): craft\queue\Queue->handleError('28539', Object(craft\queue\jobs\UpdateSearchIndex), '300', 2, Object(yii\db\Exception))
#4 /var/www/vendor/yiisoft/yii2-queue/src/cli/Queue.php(147): yii\queue\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#5 /var/www/vendor/craftcms/cms/src/queue/Queue.php(96): yii\queue\cli\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#6 /var/www/vendor/putyourlightson/craft-campaign/src/console/controllers/SendoutsController.php(38): craft\queue\Queue->run()
#7 [internal function]: putyourlightson\campaign\console\controllers\SendoutsController->actionRunPendingSendouts()
#8 /var/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /var/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 /var/www/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('run-pending-sen...', Array)
#11 /var/www/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('run-pending-sen...', Array)
#12 /var/www/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('campaign/sendou...', Array)
#13 /var/www/vendor/craftcms/cms/src/console/Application.php(93): yii\console\Application->runAction('campaign/sendou...', Array)
#14 /var/www/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('campaign/sendou...', Array)
#15 /var/www/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#16 /var/www/craft(22): yii\base\Application->run()
#17 {main}

Next yii\db\Exception: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`queue`.`error` at row 1
The SQL being executed was: UPDATE `queue` SET `fail`=1, `dateFailed`='2019-12-11 08:30:02', `error`='SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: \'\\xF0\\x9F\\x8F\\x8A n...\' for column `nyarigyerektabor_database`.`searchindex`.`keywords` at row 1\nThe SQL being executed was: INSERT INTO `searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (6146, \'subject\', 0, 1, \' 🏊 nyilt uszasorak december 12 17 kozott \')' WHERE `id`='28539' in /var/www/vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 /var/www/vendor/yiisoft/yii2/db/Command.php(1295): yii\db\Schema->convertException(Object(PDOException), 'UPDATE `queue` ...')
#1 /var/www/vendor/yiisoft/yii2/db/Command.php(1091): yii\db\Command->internalExecute('UPDATE `queue` ...')
#2 /var/www/vendor/craftcms/cms/src/queue/Queue.php(378): yii\db\Command->execute()
#3 /var/www/vendor/yiisoft/yii2-queue/src/Queue.php(216): craft\queue\Queue->handleError('28539', Object(craft\queue\jobs\UpdateSearchIndex), '300', 2, Object(yii\db\Exception))
#4 /var/www/vendor/yiisoft/yii2-queue/src/cli/Queue.php(147): yii\queue\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#5 /var/www/vendor/craftcms/cms/src/queue/Queue.php(96): yii\queue\cli\Queue->handleMessage('28539', 'O:34:"craft\\que...', '300', 2)
#6 /var/www/vendor/putyourlightson/craft-campaign/src/console/controllers/SendoutsController.php(38): craft\queue\Queue->run()
#7 [internal function]: putyourlightson\campaign\console\controllers\SendoutsController->actionRunPendingSendouts()
#8 /var/www/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /var/www/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 /var/www/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('run-pending-sen...', Array)
#11 /var/www/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('run-pending-sen...', Array)
#12 /var/www/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('campaign/sendou...', Array)
#13 /var/www/vendor/craftcms/cms/src/console/Application.php(93): yii\console\Application->runAction('campaign/sendou...', Array)
#14 /var/www/vendor/yiisoft/yii2/console/Application.php(147): craft\console\Application->runAction('campaign/sendou...', Array)
#15 /var/www/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(craft\console\Request))
#16 /var/www/craft(22): yii\base\Application->run()
#17 {main}
Additional Information:
Array
(
    [0] => 22007
    [1] => 1366
    [2] => Incorrect string value: '\xF0\x9F\x8F\x8A n...' for column `nyarigyerektabor_database`.`queue`.`error` at row 1
)

To reproduce

Steps to reproduce the behaviour:

1. Create a new sendout.
2. Use swimmer emoji in Subject.
3. See error message above.

Versions

• Plugin version: 1.12.1
• Craft version: 3.3.13

[bencroker]

Thanks, fixed in 22b72ab and will release this in today's update.

[Saboteur777]

Thanks a lot for the quick fix!

…

On Wed, Dec 11, 2019, 16:30 Ben Croker ***@***.***> wrote: Thanks, fixed in 22b72ab <22b72ab> and will release this in today's update. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#101?email_source=notifications&email_token=ACFELIZWRSKBMEVJNIRX3QTQYEBPXA5CNFSM4JZLHZS2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEGTRD7I#issuecomment-564597245>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACFELIYJ4X42WJAU32RNH5LQYEBPXANCNFSM4JZLHZSQ> .