Error when using an entity from Propel

U02P7B6K2LC

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.

UKEP86J66

Should the key property also be set before you save?

U02P7B6K2LC

Thank you for looking at this Rick! 🙂

I have set the key with:

$storageEntity->setKey("test");

But still have the error…

U02P7B6K2LC

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

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

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

U02P7B6K2LC

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

UKEP86J66

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

U02P7B6K2LC

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

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

UKEP86J66

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

U02P7B6K2LC

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

but I’m not sure about MariaDB setups

UKEP86J66

are there it is 🙂

UKEP86J66

can you run that directly in the db shell?

UKEP86J66

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

UKEP86J66

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

$storageEntity->setData(['foo' => 'bar']);

U02P7B6K2LC

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

U02P7B6K2LC

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

UKEP86J66

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

U02P7B6K2LC

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

UKEP86J66

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

UKEP86J66

I mean do you have any rows in spy_hello_world_message_storage

UKEP86J66

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

No other rows in spy_hello_world_message_storage yet

U02P7B6K2LC

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

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

UKEP86J66

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

Basically getting the same…

U02P7B6K2LC

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

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

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