Yii2 session table data column varbinary(BLOB) type not possible

[nvgandhi]

Hello,

We have a strange problem, we are using MSSQL 2019 database for our project with YII2 latest version and storing session in database. Due to huge data in session, we can not use ntext, nvarchar(MAX) etc for the data column in the session table.

Yii2 recommend BLOB data type, but in the latest SQL server there no blob data type they have varbinary(max). When I set this YII2 gives SQL error Implicit conversion from data type nvarchar to varbinary(max) is not allowed

What should we do now ? as there is large data in session if we use nvarchar/ntext etc the data is truncated and we lost data in session.

Any help would appriciate.

Thanks

Additional info

Q	A
Yii version	2.0.?
PHP version	8.0
Operating system	Windows

[nvgandhi]

@WinterSilence

So After I update YII2 latest version and change session table -- "data" column to "varbinary" will this issue solved?

Thanks

[WinterSilence]

You can be moved huge data to other table(s)/storage(s), then data column contained only id(s), define $writeCallback and $readCallback properties to load/save extra data.

You can move huge data to another table(s) or storage(s), then session.data will contains only id(s). Define $writeCallback and $readCallback properties to load/save huge data.

Due to huge data in session, we can not use ntext, nvarchar(MAX) etc for the data column in the session table.

nvarchar(max):

max indicates that the maximum storage size is 2^30-1 characters (2 GB)

When I set this YII2 gives SQL error Implicit conversion from data type nvarchar to varbinary(max) is not allowed

If you want keep stored data, then dump table data, re-create table and load dumped data,
else, truncate/clean table and re-create column.

[WinterSilence]

to "varbinary(max)", but I think better solution is separate huge data, then you can lazy-loads complex data.

[nvgandhi]

@WinterSilence I have tried varbinary(max) with truncate/clean table and re-create column. Still getting below error.

PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. in E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\db\Command.php:1302
Stack trace:
#0 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\db\Command.php(1302): PDOStatement->execute()
#1 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute('MERGE [session]...')
#2 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\web\DbSession.php(222): yii\db\Command->execute()
#3 [internal function]: yii\web\DbSession->writeSession('nic9hcnfkkshuo8...', '__flash|a:0:{}_...')
#4 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\web\DbSession.php(166): session_write_close()
#5 [internal function]: yii\web\DbSession->close()
#6 {main}

Next yii\db\Exception: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
The SQL being executed was: MERGE [session] WITH (HOLDLOCK) USING (VALUES (NULL, '', '', '', '__flash|a:0:{}__returnUrl|s:1:"/";', 'nic9hcnfkkshuo898pbn5g1c6r', 1665138746)) AS [EXCLUDED] ([userid], [firstname], [lastname], [username], [data], [id], [expire]) ON ([session].[id]=[EXCLUDED].[id]) WHEN MATCHED THEN UPDATE SET [userid]=[EXCLUDED].[userid], [firstname]=[EXCLUDED].[firstname], [lastname]=[EXCLUDED].[lastname], [username]=[EXCLUDED].[username], [data]=[EXCLUDED].[data], [expire]=[EXCLUDED].[expire] WHEN NOT MATCHED THEN INSERT ([userid], [firstname], [lastname], [username], [data], [id], [expire]) VALUES ([EXCLUDED].[userid], [EXCLUDED].[firstname], [EXCLUDED].[lastname], [EXCLUDED].[username], [EXCLUDED].[data], [EXCLUDED].[id], [EXCLUDED].[expire]); in E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\db\Schema.php:676
Stack trace:
#0 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\db\Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'MERGE [session]...')
#1 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\db\Command.php(1102): yii\db\Command->internalExecute('MERGE [session]...')
#2 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\web\DbSession.php(222): yii\db\Command->execute()
#3 [internal function]: yii\web\DbSession->writeSession('nic9hcnfkkshuo8...', '__flash|a:0:{}_...')
#4 E:\Projects\sa\phpprojects\php8011.test\Ascriptica\vendor\yiisoft\yii2\web\DbSession.php(166): session_write_close()
#5 [internal function]: yii\web\DbSession->close()
#6 {main}
Additional Information:
Array
(
[0] => 42000
[1] => 257
[2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
[3] => 42000
[4] => 8180
[5] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
)

[WinterSilence]

re-create

I mean, drop column and add again with new type

[nvgandhi]

Yes, I did the same but no effect it throw the same error.