Storing serialized objects in the database cache doesn't work on SQLServer

[MyBestPro-Stephane]

• Laravel Version: 5.7
• PHP Version: 7.2
• Database Driver & Version: SQLServer 2012

Storing serialized objects in the database cache doesn't work on SQLServer:

The database cache uses serialize() to serialize the value. When try saving Eloquent Collections to cache, Iget an SQL exception "syntax error" truncates the string 'O:29:"Illuminate\Support\Collection":1:{s:8:"'.

As @staudenmeir report in the issue #25530 serializing data in base 64 resolve the problem.

Do you thing guys encoding every cache database data would be a solution ?

For now I have extend the database store like this :

class DatabaseStore extends \Illuminate\Cache\DatabaseStore
{
    /**
     * Serialize the given value.
     *
     * @param  mixed  $value
     * @return string
     */
    protected function serialize($value)
    {
        return base64_encode(serialize($value));
    }

    /**
     * Unserialize the given value.
     *
     * @param  string  $value
     * @return mixed
     */
    protected function unserialize($value)
    {
        // backward compatibility
        if (base64_decode($value, true)) {
            $value = base64_decode($value, true);
        }
        return unserialize($value);
    }
}

I don't see other issue for now with this.

Thx

[staudenmeir]

I can't reproduce this on SQL Server 2017. So they either changed the behavior between 2012 and 2017 or we are using different settings.

What does your cache table migration look like?

@sisve Do you have a SQL Server 2012 installation you can test this on?

[MyBestPro-Stephane]

My migration is like this (maybe relatively old) :

    Schema::create('cache', function (Blueprint $table) {
        $table->string('key')->unique();
        $table->mediumText('value');
        $table->integer('expiration');
    });

[staudenmeir]

I'm using $table->text('value'), but that creates the same column.

[sisve]

This seems identical to the postgresql problem; null values truncates strings. This seems to occur during the read of the data. This happens in Management Studio, so it isn't a bug in the mssql pdo integration.

Since we treat the values as binary data (which is how php treats strings...), a workaround would be to use a binary field instead.

CREATE TABLE #tmp ( str varchar(50) );
INSERT INTO #tmp ( str ) VALUES ( CONCAT('A', CHAR(0), 'B') );

-- str="A"
-- CAST(str AS VARBINARY)=0x410042
-- LEN(str)=3
SELECT str, CAST(str AS VARBINARY), LEN(str) FROM #tmp

[mfn]

Since we treat the values as binary data (which is how php treats strings...), a workaround would be to use a binary field instead.

I remember this approach is what I initially suggested in #25466 too, before we had the base64 encoding.

[driesvints]

Closing this issue because it's inactive, already solved, old or not relevant anymore. Feel free to reply if you're still experiencing this issue and we'll re-open this issue.