Error when using an entity from Propel

U02P7B6K2LC
U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet
edited June 2023 in Propel ORM

Hey Sprykers!

I’m getting an error when using an entity from Propel:

Unable to execute INSERT statement [INSERT INTO spy_hello_world_message_storage (id_hello_world_message_storage, fk_hello_world_message, data, key, created_at, updated_at) VALUES (:p0, :p1, :p2, :p3, :p4, :p5)]

FYI: I’m going through this Tutorial:

https://docs.spryker.com/docs/scos/dev/back-end-development/data-manipulation/data-publishing/handle-data-with-publish-and-synchronization.html

So I’m using the storageEntity from propel like this:

$storageEntity = new SpyHelloWorldMessageStorage();
$storageEntity->setFkHelloWorldMessage(2);
$storageEntity->setData(array('test'));
$storageEntity->save();

The schema file looks like this:

<?xml version="1.0"?>
<database xmlns:xsi="<http://www.w3.org/2001/XMLSchema-instance>" name="zed"
          xsi:noNamespaceSchemaLocation="<http://static.spryker.com/schema-01.xsd>"
          namespace="Orm\Zed\AntelopeSearch\Persistence" package="src.Orm.Zed.AntelopeSearch.Persistence">
    <table name="spy_hello_world_message_storage" idMethod="native" allowPkInsert="true">
        <column name="id_hello_world_message_storage" type="BIGINT" autoIncrement="true" primaryKey="true"/>
        <column name="fk_hello_world_message" type="INTEGER" required="true"/>
        <index name="spy_hello_world_message_storage-fk_hello_world_message">
            <index-column name="fk_hello_world_message"/>
        </index>
        <behavior name="synchronization">
            <parameter name="resource" value="message"/>
            <parameter name="key_suffix_column" value="fk_hello_world_message"/>
            <parameter name="queue_group" value="sync.storage.hello"/>
        </behavior>
        <behavior name="timestampable"/>
        <id-method-parameter value="spy_hello_world_message_storage_pk_seq"/>
    </table>

</database>

I’ve run console propel:install etc. and the entityORM classes are there and don’t show any errors etc.

Tagged:
«1

Comments

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    Should the key property also be set before you save?

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet
    edited November 2022

    Thank you for looking at this Rick! 🙂

    I have set the key with:

    $storageEntity->setKey("test");
    

    But still have the error…

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    More detail of the error:

    SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, created_at, updated_at) VALUES (NULL, 1, '[\"test\"]', 'test', 'message:...' at line 1

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    Maybe you can see the exact SQL and more detailed error in the propel log. You can enable it with

    $config[PropelConstants::PROPEL_DEBUG] = true;
    

    inside the config, eg config_local.php

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    Why does the created_at field have 'message:...' 🤔

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    Agh I think that’s just the error message being truncated…

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    Maybe the propel log will show you more. It does for postgres, not sure about MariaDB

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    I’ve enabled the Propel log but can’t see where it outputs…

    Can’t see anything in http://spryker.local/logs either.

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    You could try find . -name "propel.log"

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    Getting this in log.io:

    [2022-11-09T16:37:21.937790+00:00] [defaultLogger.INFO](http://defaultLogger.INFO): INSERT INTO spy_hello_world_message_storage (id_hello_world_message_storage, fk_hello_world_message, data, alias_keys, key, created_at, updated_at) VALUES (NULL, 1, '["test"]', 'test', 'message:1', '2022-11-09 16:37:21.937461', '2022-11-09 16:37:21.937461') [] []

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    but I’m not sure about MariaDB setups

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    are there it is 🙂

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    can you run that directly in the db shell?

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    it looks ok, but I’ve not seen alias_keys before

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    Another crazy thing to try. The data is usually a json object, so you could try

    $storageEntity->setData(['foo' => 'bar']);
    
  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    I’ve been able to connect with TablePlus and run queries… it works fine.

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    I’ve also tested lots of arrays etc in “setData” but no luck.

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    edited November 2022

    Do you have any rows in this table? Just wondering if fk_hello_world_message has a unique index

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    I thought that as well… yes I made sure that the FK actually exists.

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    but very weird - if it runs directly in the db but not via code

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    I mean do you have any rows in spy_hello_world_message_storage

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    If fk_hello_world_message has a unique index you are only allowed one, but I would expect a nicer error message for that

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    No other rows in spy_hello_world_message_storage yet

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet
    edited November 2022

    Ok I found something…

    The database namespace was wrong in the schema XML:

    <database xmlns:xsi="<http://www.w3.org/2001/XMLSchema-instance>" name="zed"
              xsi:noNamespaceSchemaLocation="<http://static.spryker.com/schema-01.xsd>"
              namespace="Orm\Zed\AntelopeSearch\Persistence" package="src.Orm.Zed.AntelopeSearch.Persistence">
    

    (I had copied it from the other tutorial…)

    Would this break the Propel classes being created?

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    I think it would just create them in the wrong place, but maybe

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet

    Did you try running this directly in the MariaDB shell?

    INSERT INTO spy_hello_world_message_storage (id_hello_world_message_storage, fk_hello_world_message, data, alias_keys, key, created_at, updated_at) VALUES (NULL, 1, '["test"]', 'test', 'message:1', '2022-11-09 16:37:21.937461', '2022-11-09 16:37:21.937461')
    
  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    Basically getting the same…

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet
    edited November 2022

    Playing around with this last night and I managed to get some CLI commands to work… simple ones like:

    INSERT INTO eu-docker.spy_hello_world_message_storage (id_hello_world_message_storage, fk_hello_world_message, data) VALUES (0, 1, 'test');

    This worked on the CLI…

    But the Propel Entity code is still erroring…

    I think I narrowed the error down to the alias_keys column… since the CLI commands broke as soon as I added alias_keys to the command:

    INSERT INTOeu-docker.spy_hello_world_message_storage(id_hello_world_message_storage, fk_hello_world_message, data, alias_keys) VALUES (0, 1, 'test', 'test');

    This caused an error…

    I’ve looked at the generated Propel Entity code in:

    src/Orm/Zed/HelloWorld/Persistence/Base/SpyHelloWorldMessageStorage.php

    …and (obviously, since it’s generated code) there seems to be nothing out of place.

    I reckon there’s a problem with the XML and this is generating bad Entity code somehow. Just a hunch!

  • U02P7B6K2LC
    U02P7B6K2LC Posts: 121 🧑🏻‍🚀 - Cadet

    QQ… does console propel:install replace all the generated code or does it skip over anything already in place?

    I ask because I’m wondering if I am able to replace all generated code it might help?

  • fsmeier
    fsmeier Senior Software Engineer & Developer Enablement Advocate Sprykee Posts: 1,081 ⚖️ - Guardians (admin)

    Can you please try to use

    <table name="<name>" identifierQuoting="true">
    

    ? - PGSQL did not care but mariaDB is bitching about reserved keywords as far as i remember. “key” column could be the problem here