Notice: unserialize(): Error at offset 473 of 578 bytes while executing doctrine query

Question

We are trying to execute the below doctrine query and its throwing unserialize(): Error at offset 473 of 578 bytes.

Doctrine Query:

SELECT p FROM MyCompanyBundle:Person p 
WHERE p.dateOfExit IS NULL 
AND (p.passportCountry IS NULL OR p.passportCountry <> :india OR 
p.invitationId IS NOT NULL) 
AND (p.category <> :category_cl AND 
p.category <> :category_staff AND p.category <> :category_sev AND 
p.category <> :category_visitor)

As part of the above query its trying to fetch the below data and its throwing error.But the same is working as mysql query.

Mysql Table Output

a:3:{s:19:"volunteering-local0";a:3:{s:8:"activity";s:23:"Mystic eye kolkata 2017";s:11:"description";s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";s:8:"location";s:7:"Kolkata";}s:19:"volunteering-local1";a:3:{s:8:"activity";s:17:"Inner Engineering";s:11:"description";s:104:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too

Detailed Error Description:

Symfony\Component\Debug\Exception\ ContextErrorException in vendor/doctrine/dbal/lib/Doctrine/DBAL/Types/ArrayType.php (line 58) ArrayType->convertToPHPValue('a:3:{s:19:"volunteering-local0";a:3:{s:8:"activity";s:23:"Mystic eye kolkata 2017";s:11:"description";s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";s:8:"location";s:7:"Kolkata";}s:19:"volunteering-local1";a:3:{s:8:"activity";s:17:"Inner Engineering";s:11:"description";s:104:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too', object(MySQL57Platform)) in vendor/doctrine/orm/lib/Doctrine/ORM/Internal/Hydration/AbstractHydrator.php (line 316)

please suggest me any ideas to resolve this doctrine issue.

Answer 1

Before you blame Doctrine ORM about your error, let's examine your serialized array first.

Serialized array (based on your MySQL table output):

a:3:{s:19:"volunteering-local0";a:3:{s:8:"activity";s:23:"Mystic eye kolkata 2017";s:11:"description";s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";s:8:"location";s:7:"Kolkata";}s:19:"volunteering-local1";a:3:{s:8:"activity";s:17:"Inner Engineering";s:11:"description";s:104:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too

Wait, it's a bit messy to examine the serialized array. Let's make it "readable":

a:3:{
    s:19:"volunteering-local0";
    a:3:{
        s:8:"activity";
        s:23:"Mystic eye kolkata 2017";
        s:11:"description";
        s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";
        s:8:"location";
        s:7:"Kolkata";
    }
    s:19:"volunteering-local1";
    a:3:{
        s:8:"activity";
        s:17:"Inner Engineering";
        s:11:"description";
        s:104:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too

By making the serialized array "readable", you can see invalid serialized object structure there.

a:3:{
    s:19:"volunteering-local0";
    a:3:{
        s:8:"activity";
        s:23:"Mystic eye kolkata 2017";
        s:11:"description";
        s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";
        s:8:"location";
        s:7:"Kolkata";
    }
    s:19:"volunteering-local1";
    a:3:{
        s:8:"activity";
        s:17:"Inner Engineering";
        s:11:"description";
        s:104:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too"; // you miss the closing double quote and semicolon
    } // you miss the closing curly bracket
} // you miss the closing curly bracket

After fixing the structure, now you have trouble in offset errors.

• Line 1 says "a:3:value". It means you have an array containing exactly 3 items inside. But the value says you only have 2 items inside (with keys "volunteering-local0" and "volunteering-local1"). So line 1 should be "a:2:value".

• Line 16 says "s:104:value". It means you have a string containing exactly 104 characters. But the value says you only have 100 characters of string.

• There are still some invalid structures if you examine it deeper.

The serialized array is broken, or let's say "invalid". That's why Doctrine throws an exception when it tries to unserialize the given serialized array to PHP array by calling "Doctrine\DBAL\Types\ArrayType::convertToPHPValue($serializedValue)". Even when you try to unserialize it using PHP internal function "unserialize($serializedValue)", you will be caught in an error, too. It will say "unserialize(): Error at offset 5 of 771 bytes".

If I have to correct it, what I imagine of the original PHP array based on your serialized array would be:

array(
    array(
        'volunteering-local0' => array(
            'activity' => 'Mystic eye kolkata 2017',
            'description' => 'I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.',
            'location' => 'Kolkata',
        ),
    ),
    array(
        'volunteering-local1' => [
            'activity' => 'Inner Engineering',
            'description' => 'I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too',
        ],
    ],
]

And if I want the serialized array of that PHP array, it would be:

a:2:{
    i:0;
    a:1:{
        s:19:"volunteering-local0";
        a:3:{
            s:8:"activity";
            s:23:"Mystic eye kolkata 2017";
            s:11:"description";
            s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";
            s:8:"location";
            s:7:"Kolkata";
        }
    }
    i:1;
    a:1:{
        s:19:"volunteering-local1";
        a:2:{
            s:8:"activity";s:17:"Inner Engineering";
            s:11:"description";
            s:100:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too";
        }
    }
}

And if I want a single line of that serialized array, it would be:

a:2:{i:0;a:1:{s:19:"volunteering-local0";a:3:{s:8:"activity";s:23:"Mystic eye kolkata 2017";s:11:"description";s:239:"I was in ushering team and also in the flying squad consisting of only 5 odd people including me. We were told to fly around and lend our hand to anyone who needs it in the spot. And mostly the menial jobs were our responsibility 1st hand.";s:8:"location";s:7:"Kolkata";}}i:1;a:1:{s:19:"volunteering-local1";a:2:{s:8:"activity";s:17:"Inner Engineering";s:11:"description";s:100:"I have volunteered in the initiation day . I had the oppurtunity to be part of the kitchen works too";}}}