Yii2: ActiveRecord save error for emoji.

[serhatozles]

I was trying to update a string value with emoji, and I noticed something. It's passing all string value(as i expected) then giving the error for next column.

Query:

UPDATE `example` SET `list`='asdasd\r\n😭😭😭 hahaha', `update_time`='2018-05-20 13:31:02' WHERE `id`=5

Error:

PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\xAD\xF0\x9F...' for column 'list' at row 1 in

Model Rule:

[['list'], 'string'],

SQL Table:

CREATE TABLE `example`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '',
  `list` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL COMMENT '',
  `create_time` timestamp(0) NULL DEFAULT NULL COMMENT '',
  `update_time` timestamp(0) NULL DEFAULT NULL COMMENT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Compact;

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.16.dev
| PHP version | 7.1.16
| Operating system | CentOS 7.4.1708

Update

When I try to directly on Navicat, is perfectly working. I'm thinking it's all about those characters; xF0x9Fx98xADxF0x9F.

[SilverFire]

PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\xAD\xF0\x9F...' for column 'list' at row 1 in

Ehh... Why does it try to cast your string to datetime?

[exploitfate]

utf8mb4

[serhatozles]

@SilverFire I'm not sure why is giving like that issue but seems SQL Injections.
@exploitfate I tried, doesn't work "utf8mb4".

[exploitfate]

You should convert db and tables charset, see https://dba.stackexchange.com/a/21684
and update db component charset too.

[serhatozles]

@exploitfate Thanks your suggestion. But as I told you at my last comment, I converted charsets and I tried. It didn't work.

[yii-bot]

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

_This is an automated comment, triggered by adding the label status:need more info._

[samdark]

What's the SQL generated?

[serhatozles]

When does the issue occur?
When I am trying to update any record with emoji string.

What do you see?
SQL "Incorrect string value" error

What was the expected result?
saved string with emoji: "asdasd 😭😭😭 hahaha "

Can you supply us with a stacktrace?

PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\xAD\xF0\x9F...' for column 'list' at row 1 in .../vendor/yiisoft/yii2/db/Command.php:1258
Stack trace:
#0 .../vendor/yiisoft/yii2/db/Command.php(1258): PDOStatement->execute()
#1 .../vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('INSERT INTO `ex...')
#2 .../vendor/yiisoft/yii2/db/Schema.php(433): yii\db\Command->execute()
#3 .../vendor/yiisoft/yii2/db/ActiveRecord.php(549): yii\db\Schema->insert('test', Array)
#4 .../vendor/yiisoft/yii2/db/ActiveRecord.php(515): yii\db\ActiveRecord->insertInternal(NULL)
#5 .../vendor/yiisoft/yii2/db/BaseActiveRecord.php(678): yii\db\ActiveRecord->insert(true, NULL)
#6 .../vendor/yiisoft/yii2/rest/CreateAction.php(52): yii\db\BaseActiveRecord->save()
#7 [internal function]: yii\rest\CreateAction->run()
#8 .../vendor/yiisoft/yii2/base/Action.php(94): call_user_func_array(Array, Array)
#9 .../vendor/yiisoft/yii2/base/Controller.php(157): yii\base\Action->runWithParams(Array)
#10 .../vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('create', Array)
#11 .../vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('app/list/c...', Array)
#12 .../vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#13 .../index.php(67): yii\base\Application->run()
#14 {main}

Next yii\db\Exception: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x98\xAD\xF0\x9F...' for column 'list' at row 1
The SQL being executed was: INSERT INTO `example` (`title`, `list`, `create_time`, `update_time`) VALUES ('list 1', 'asdasd 😭😭😭 asd', '2018-05-21 02:36:40', '2018-05-21 02:36:40') in .../vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:
#0 .../vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO `ex...')
#1 .../vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('INSERT INTO `ex...')
#2 .../vendor/yiisoft/yii2/db/Schema.php(433): yii\db\Command->execute()
#3 .../vendor/yiisoft/yii2/db/ActiveRecord.php(549): yii\db\Schema->insert('test', Array)
#4 .../vendor/yiisoft/yii2/db/ActiveRecord.php(515): yii\db\ActiveRecord->insertInternal(NULL)
#5 .../vendor/yiisoft/yii2/db/BaseActiveRecord.php(678): yii\db\ActiveRecord->insert(true, NULL)
#6 .../vendor/yiisoft/yii2/rest/CreateAction.php(52): yii\db\BaseActiveRecord->save()
#7 [internal function]: yii\rest\CreateAction->run()
#8 .../vendor/yiisoft/yii2/base/Action.php(94): call_user_func_array(Array, Array)
#9 .../vendor/yiisoft/yii2/base/Controller.php(157): yii\base\Action->runWithParams(Array)
#10 .../vendor/yiisoft/yii2/base/Module.php(528): yii\base\Controller->runAction('create', Array)
#11 .../vendor/yiisoft/yii2/web/Application.php(103): yii\base\Module->runAction('app/list/c...', Array)
#12 .../vendor/yiisoft/yii2/base/Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#13 .../index.php(67): yii\base\Application->run()
#14 {main}
Additional Information:
Array
(
    [0] => 22007
    [1] => 1366
    [2] => Incorrect string value: '\xF0\x9F\x98\xAD\xF0\x9F...' for column 'list' at row 1
)

[larryli]

ALTER TABLE `example` CHANGE `list` `list` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

[alex-code]

What about the connection charset?
https://www.yiiframework.com/doc/guide/2.0/en/db-dao#creating-db-connections

[jakim]

@serhatozles
You definitely need utf8mb4,
create such a database and set charset in the connection.

for example (mysql):
CREATE DATABASE `ig_monitoring` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
and connection like this:
https://github.com/jakim/ig-monitoring/blob/master/config/db.php.dist

[serhatozles]

@larryli I already did.
@Alex-Code @jakim absolutely your right. It's working now. Thanks a lot.