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:
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.
Should the key property also be set before you save?
Thank you for looking at this Rick! 🙂
I have set the key with:
$storageEntity->setKey("test");
But still have the error…
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
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
Why does the created_at
field have 'message:...'
🤔
Agh I think that’s just the error message being truncated…
Maybe the propel log will show you more. It does for postgres, not sure about MariaDB
I’ve enabled the Propel log but can’t see where it outputs…
Can’t see anything in http://spryker.local/logs either.
You could try find . -name "propel.log"
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') [] []
but I’m not sure about MariaDB setups
are there it is 🙂
can you run that directly in the db shell?
it looks ok, but I’ve not seen alias_keys
before
Another crazy thing to try. The data is usually a json object, so you could try
$storageEntity->setData(['foo' => 'bar']);
I’ve been able to connect with TablePlus and run queries… it works fine.
I’ve also tested lots of arrays etc in “setData” but no luck.
Do you have any rows in this table? Just wondering if fk_hello_world_message
has a unique index
I thought that as well… yes I made sure that the FK actually exists.
but very weird - if it runs directly in the db but not via code
I mean do you have any rows in spy_hello_world_message_storage
If fk_hello_world_message
has a unique index you are only allowed one, but I would expect a nicer error message for that
No other rows in spy_hello_world_message_storage
yet
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?
I think it would just create them in the wrong place, but maybe
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')
Basically getting the same…
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 INTO
eu-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!
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?
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